-- ABS-86229 / ABS-87919

-------------- LOSTITEMSHISTORY ----------------

CREATE SEQUENCE SEQ_LOSTITEMSINHISTORY
START WITH 1
INCREMENT BY 1;

CREATE TABLE LOSTITEMSHISTORY (LOSTITEMSHISTORY_ID NUMBER NOT NULL,
                               WEARER_ID NUMBER NOT NULL,
                               STARTDATE DATE DEFAULT SYSDATE NOT NULL,
                               CLOSEDDATE DATE NULL,
                               LEFTIN VARCHAR2(30) NULL,
                               LOSTITEMFLAG_ID NUMBER NOT NULL,
                               FREETEXT VARCHAR2(80) NULL);
                               
COMMENT ON COLUMN LOSTITEMSHISTORY.STARTDATE
IS 'Date when item was registered as lost';

COMMENT ON COLUMN LOSTITEMSHISTORY.CLOSEDDATE
IS 'Date when item is found';

COMMENT ON COLUMN LOSTITEMSHISTORY.LEFTIN
IS 'The garment where the item was lost. When filled it will be the product description';

ALTER TABLE LOSTITEMSHISTORY ADD CONSTRAINT PKLOSTITEMSHISTORY PRIMARY KEY(LOSTITEMSHISTORY_ID);
CREATE INDEX XIF1LOSTITEMSHISTORY ON LOSTITEMSHISTORY(WEARER_ID ASC);
CREATE INDEX XIF2LOSTITEMSHISTORY ON LOSTITEMSHISTORY(LOSTITEMFLAG_ID ASC);

ALTER TABLE LOSTITEMSHISTORY ADD CONSTRAINT FK_LOSTITEMSHISTORY_WEARER_ID FOREIGN KEY(WEARER_ID)
REFERENCES WEARER(WEARER_ID);  
ALTER TABLE LOSTITEMSHISTORY ADD CONSTRAINT FK_LOSTITEMSHISTORY_LOSTITFLAG FOREIGN KEY(WEARER_ID)
REFERENCES FLAG(FLAG_ID);

------------------ AUTOMATICFLAG -------------------
DELETE FROM AUTOMATICFLAG A WHERE A.EVENT='Lost And Found';
COMMIT;

------------------- MUASETTINGS ---------------
UPDATE MUASETTINGS M
SET M.NAME='SECONDARYUNIQUEIDUSED'
WHERE M.NAME='SECONDAYUNIQUEIDUSED';
COMMIT;

DELETE FROM MUASETTINGS M
WHERE M.NAME='PROFILETILE';
COMMIT;                  