CREATE LANGUAGE plpgsql;

create table info2 (transaction varchar primary key, datestamp timestamp, protocol varchar, isnew boolean, local varchar, remote varchar, door varchar, owner varchar, file varchar, pool_error numeric, door_error numeric, connectiontime numeric, transfersize numeric, storageclass varchar);
grant ALL on info2 to srmdcache;

create index info2_datestamp_idx on info2 (datestamp);
create index info2_file_idx on info2 (file);
create index info2_owner_idx on info2 (owner);

create table info_errors (transaction varchar, pool_message text, door_message text);
grant ALL on info_error to srmdcache;

create UNIQUE index info_errors_pkey on info_errors(transaction);

create table transfer_summary (datestamp timestamp, protocol varchar, isnew boolean, local varchar, remote varchar, door varchar, owner varchar, error_count int, transfer_count int, connectiontime numeric, transfersize numeric, storageclass varchar);
grant ALL on transfer_summary to srmdcache;

create index transfer_summary_datestamp on transfer_summary(datestamp);
create unique index transfer_summary_uniq on transfer_summary(datestamp, protocol, isnew, local, remote, door, owner, storageclass);

CREATE OR REPLACE FUNCTION sp_billinginfo_insert() RETURNS TRIGGER AS $$
BEGIN -- Assume that pool record comes first (usually true)
RAISE DEBUG 'Hit the billinginfo trigger';
IF (TG_OP = 'INSERT' OR TG_OP='UPDATE') THEN
  -- Make the record in the error table
  
  IF NEW.errormessage IS NOT NULL AND char_length(NEW.errormessage)>0 THEN
    BEGIN
      insert into info_errors values (NEW.initiator,NEW.errormessage,null);
    EXCEPTION 
    WHEN UNIQUE_VIOLATION THEN
      update info_errors set pool_message=NEW.errormessage where
        transaction=NEW.initiator;
    END;
  END IF;
BEGIN
  -- Insert partial values from billinginfo
  insert into info2 values(
    NEW.initiator, NEW.datestamp, NEW.protocol, NEW.isnew,
    split_part(NEW.cellname, '@', 1),
    CASE
      WHEN NEW.protocol='DCap-3.0' THEN NEW.client
      WHEN NEW.protocol='RemoteGsiftpTransfer-1.1' THEN NEW.client
      ELSE null
    END,
    null, null, NEW.pnfsid, NEW.errorcode, null, NEW.connectiontime,
    NEW.transfersize, NEW.storageclass
  );
  RAISE INFO 'Created a new info2 for (%), time (%) from billinginfo', NEW.initiator, NEW.datestamp;
EXCEPTION
WHEN UNIQUE_VIOLATION THEN
  -- Insert
  update info2 set
    datestamp=NEW.datestamp, protocol=NEW.protocol, isnew=NEW.isnew,
    file=CASE WHEN file='Unknown' then COALESCE(NEW.pnfsid, file)
         ELSE COALESCE(file, NEW.pnfsid) END,
    local=split_part(NEW.cellname, '@', 1), pool_error=NEW.errorcode,
    remote=CASE WHEN NEW.protocol='GFtp-1.0' THEN remote ELSE
           NEW.client END,
    connectiontime=NEW.connectiontime, transfersize=NEW.transfersize,
    storageclass=NEW.storageclass
  where transaction=NEW.initiator;
  RAISE INFO 'Updated info2 for (%) from billinginfo', NEW.initiator;
END;
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION sp_doorinfo_insert() RETURNS TRIGGER AS $$
BEGIN
IF (TG_OP = 'INSERT' OR TG_OP='UPDATE') THEN
RAISE DEBUG 'Hit the doorinfo trigger';
-- We assume that the doorinfo came second to start with.
BEGIN
  IF NEW.errormessage IS NOT NULL AND char_length(NEW.errormessage)>0 THEN
    -- Update the errors table.
    update info_errors set door_message=NEW.errormessage where 
      transaction=NEW.transaction;
    IF NOT FOUND THEN
      -- Create the row.
      insert into info_errors values (NEW.transaction, null,
        NEW.errormessage);
    END IF;
  END IF;
END;
BEGIN
-- Update the info table
update info2 set
  door=COALESCE(split_part(split_part(split_part(NEW.cellname, '@', 1), '-Unknown', 1), '-unknow', 1), 'Unknown'),
  owner = NEW.owner,
  file = CASE WHEN NEW.path='Unknown' THEN COALESCE(file, NEW.pnfsid) ELSE 
         COALESCE(NEW.path, file) END,
  door_error=NEW.errorcode,
  remote=CASE WHEN protocol='GFtp-1.0' THEN NEW.client ELSE remote END
where transaction=NEW.transaction;
-- The case where there was nothing to update.
IF NOT FOUND THEN
insert into info2 values(
  NEW.transaction,
  NEW.datestamp,
  null,
  null,
  null,
  NEW.client,
  COALESCE(split_part(split_part(split_part(NEW.cellname, '@', 1), '-Unknown', 1), '-unknow', 1), 'Unknown'),
  NEW.owner,
  CASE WHEN NEW.path='Unknown' THEN NEW.pnfsid ELSE NEW.path END,
  null,
  NEW.errorcode,
  NEW.connectiontime,
  null,
  null
);
RAISE DEBUG 'Created a new info2 for (%), time (%) for doorinfo', NEW.transaction, NEW.datestamp;
ELSE
RAISE DEBUG 'Updated info2 for (%), time (%) for doorinfo', NEW.transaction, NEW.datestamp;
END IF;
END;
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION sp_summary_insert() RETURNS TRIGGER AS $$
DECLARE
  my_datestamp timestamp;
BEGIN
RAISE DEBUG 'Hit summary trigger';
IF (TG_OP = 'INSERT' OR TG_OP='UPDATE') THEN
BEGIN
my_datestamp = date_trunc('hour', NEW.datestamp);
IF (NEW.remote is not null) AND (NEW.protocol is not null) AND
   (NEW.local is not null) AND (NEW.owner is not null) AND
   (NEW.door is not null) AND  (NEW.storageclass is not null) AND 
   ((NEW.isnew is not null) OR (NEW.owner is not null)) THEN
-- The update finished the transfer description, insert into summary table
-- Assume that we are the first entry in the table
insert into transfer_summary values(
  my_datestamp,
  NEW.protocol,
  NEW.isnew,
  NEW.local,
  NEW.remote,
  NEW.door,
  NEW.owner,
  CASE WHEN NEW.pool_error!=0 OR NEW.door_error!=0 THEN 1 ELSE 0 END,
  1,
  NEW.connectiontime,
  NEW.transfersize,
  NEW.storageclass);
RAISE DEBUG 'Created new summary information for time (%), local (%)', my_datestamp, NEW.local;
END IF;
EXCEPTION
WHEN UNIQUE_VIOLATION THEN
  update transfer_summary set
    transfer_count = transfer_count + 1,
    connectiontime = connectiontime + NEW.connectiontime,
    transfersize = transfersize + NEW.transfersize,
    error_count = error_count + (CASE WHEN NEW.pool_error != 0 or
      NEW.door_error != 0 THEN 1 ELSE 0 END)
  where
    datestamp=my_datestamp and
    remote=NEW.remote and protocol=NEW.protocol and
    isnew=NEW.isnew and
    local=NEW.local and
    door=NEW.door and
    owner=NEW.owner and
    storageclass=NEW.storageclass;
  RAISE DEBUG 'Updated summary information for time (%), local (%)', my_datestamp, NEW.local;
END;
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER tr_billinginfo 
AFTER INSERT OR UPDATE ON billinginfo
FOR EACH ROW EXECUTE PROCEDURE sp_billinginfo_insert();

CREATE TRIGGER tr_doorinfo 
AFTER INSERT OR UPDATE ON doorinfo
FOR EACH ROW EXECUTE PROCEDURE sp_doorinfo_insert();

CREATE TRIGGER tr_summary
AFTER INSERT OR UPDATE ON info2
FOR EACH ROW EXECUTE PROCEDURE sp_summary_insert();