-- ABS-46192/ ABS-49200

---DATABASESCRIPT table---
ALTER TABLE DATABASESCRIPT ADD (FILENAMEPREFIX VARCHAR2(20) NULL,
                                FILENAMEEXTENSION VARCHAR2(3) NULL,
                                STORELOCATION NUMBER DEFAULT 0 NOT NULL CONSTRAINT STORELOCATION_VALID1
                                              CHECK (STORELOCATION IN (0, 1)),
                                SPECIFICLOCATION VARCHAR2(100));
 
COMMENT ON COLUMN DATABASESCRIPT.USAGE
  IS '0=Incidental
1=Scheduled
2=Pre/post process
3=User defined query
4=Invoice Script
';
COMMENT ON COLUMN DATABASESCRIPT.FILENAMEPREFIX
  IS 'The filename for creating an export file for the Invoice script consists of Prefix + ‘_’ + InvoiceNumber + ‘.’ + Extension.
Only filled for Usage = 4
';
COMMENT ON COLUMN DATABASESCRIPT.FILENAMEEXTENSION
  IS 'Only filled for Usage = 4';
COMMENT ON COLUMN DATABASESCRIPT.STORELOCATION
  IS '0 = Not applicable / Email Export
1 = Specific Location. Only filled for Usage = 4
';
COMMENT ON COLUMN DATABASESCRIPT.SPECIFICLOCATION
  IS 'Only filled for Usage = 4 and StoreLocation = 1';

ALTER TABLE DATABASESCRIPT
  DROP CONSTRAINT DATABASESCRIPTUSAGE_VALID;
ALTER TABLE DATABASESCRIPT
  ADD CONSTRAINT DATABASESCRIPTUSAGE_VALID
  CHECK (USAGE IN (0, 1, 2, 3, 4));

---DATABASESCRIPTEXECUTION table---
ALTER TABLE DATABASESCRIPTEXECUTION ADD (CUSTOMER_ID NUMBER NULL,
                                         INVOICEHEADER_ID NUMBER NULL,
                                         STATUSMESSAGE VARCHAR2(1000) NULL);

COMMENT ON COLUMN DATABASESCRIPTEXECUTION.CUSTOMER_ID
  IS 'Only filled for Invoice Scripts.';
COMMENT ON COLUMN DATABASESCRIPTEXECUTION.INVOICEHEADER_ID
  IS 'Only filled for Invoice Scripts.';
COMMENT ON COLUMN DATABASESCRIPTEXECUTION.STATUSMESSAGE
  IS 'Y	Can be filled with a message returned by the script (invoice scripts only)';


CREATE INDEX XIF2DATABASESCRIPTEXECUTION ON DATABASESCRIPTEXECUTION (CUSTOMER_ID ASC);
CREATE INDEX XIF3DATABASESCRIPTEXECUTION ON DATABASESCRIPTEXECUTION (INVOICEHEADER_ID ASC);

ALTER TABLE DATABASESCRIPTEXECUTION
  ADD CONSTRAINT DATABASESCRIPTEXECUTION_CU FOREIGN KEY (CUSTOMER_ID)
  REFERENCES CUSTOMER (CUSTOMER_ID) ON DELETE SET NULL;
  
ALTER TABLE DATABASESCRIPTEXECUTION
  ADD CONSTRAINT DATABASESCRIPTEXECUTION_IH FOREIGN KEY (INVOICEHEADER_ID)
  REFERENCES INVOICEHEADER (INVOICEHEADER_ID) ON DELETE SET NULL;

---DATABASESCRIPTCUSTOMER table---
CREATE TABLE DATABASESCRIPTCUSTOMER
(
  DATABASESCRIPT_ID NUMBER NOT NULL,
  CUSTOMER_ID       NUMBER NOT NULL,
  SYSTEMUSER_ID     NUMBER NOT NULL,
  TIMESTAMP         DATE NOT NULL
);

ALTER TABLE DATABASESCRIPTCUSTOMER
  ADD CONSTRAINT XPKDATABASESCRIPTCUSTOMER PRIMARY KEY (DATABASESCRIPT_ID, CUSTOMER_ID);
  
CREATE INDEX XIF1DATABASESCRIPTCUSTOMER ON DATABASESCRIPTCUSTOMER (DATABASESCRIPT_ID ASC);
CREATE INDEX XIF2DATABASESCRIPTCUSTOMER ON DATABASESCRIPTCUSTOMER (CUSTOMER_ID ASC);

ALTER TABLE DATABASESCRIPTCUSTOMER
  ADD CONSTRAINT DATABASESCRIPTCUSTOMER_DBS FOREIGN KEY (DATABASESCRIPT_ID)
  REFERENCES DATABASESCRIPT (DATABASESCRIPT_ID) ON DELETE CASCADE;
  
ALTER TABLE DATABASESCRIPTCUSTOMER
  ADD CONSTRAINT DATABASESCRIPTCUSTOMER_CU FOREIGN KEY (CUSTOMER_ID)
  REFERENCES CUSTOMER (CUSTOMER_ID) ON DELETE CASCADE;