--ABSN-7347

--UPDATE WEARER TABLE
ALTER TABLE WEARER DROP CONSTRAINT CHK_WEARER_MYGENDER;
ALTER TABLE WEARER DROP CONSTRAINT CHK_WEARER_SEX;
ALTER TABLE WEARER DROP COLUMN MYGENDER;

UPDATE WEARER W
   SET W.SEX = 'N',
       W.TIMESTAMP = SYSDATE
 WHERE W.SEX = 'S';

COMMIT;

--GENDER TABLE
CREATE SEQUENCE SEQ_GENDER
START WITH 5
INCREMENT BY 1;

CREATE TABLE GENDER(GENDER_ID       NUMBER NOT NULL,
                    CODE            VARCHAR2(1) NOT NULL,
                    ACTIVE          VARCHAR2(1) DEFAULT 'Y' NOT NULL CONSTRAINT CHK_GENDER CHECK (ACTIVE IN ('Y', 'N')),
                    SYSTEMUSER_ID   NUMBER NOT NULL, 
					          TIMESTAMP       DATE NOT NULL);

ALTER TABLE GENDER ADD CONSTRAINT XPKGENDER PRIMARY KEY(GENDER_ID);
ALTER TABLE GENDER ADD CONSTRAINT XUK1GENDER UNIQUE (CODE);

--GENDER_DESC TABLE
CREATE TABLE GENDER_DESC(GENDER_ID      NUMBER NOT NULL,
                         LANGUAGE_ID    NUMBER NOT NULL,
                         DESCRIPTION    VARCHAR2(40) NOT NULL,
                         SYSTEMUSER_ID  NUMBER NOT NULL,
                         TIMESTAMP      DATE NOT NULL);

ALTER TABLE GENDER_DESC ADD CONSTRAINT XPKGENDER_DESC PRIMARY KEY (GENDER_ID, LANGUAGE_ID);

CREATE INDEX XIF1GENDER_DESC ON GENDER_DESC (GENDER_ID ASC);
CREATE INDEX XIF2GENDER_DESC ON GENDER_DESC (LANGUAGE_ID ASC);

ALTER TABLE GENDER_DESC ADD CONSTRAINT FK_GENDER_DESC_GENDER_ID FOREIGN KEY (GENDER_ID) REFERENCES GENDER (GENDER_ID) ON DELETE CASCADE;
ALTER TABLE GENDER_DESC ADD CONSTRAINT FK_GENDER_DESC_LANGUAGE_ID FOREIGN KEY (LANGUAGE_ID) REFERENCES LANGUAGES (LANGUAGE_ID);

INSERT INTO GENDER (GENDER_ID, CODE, ACTIVE, SYSTEMUSER_ID, TIMESTAMP)
            VALUES (1, 'M', 'Y', 1, SYSDATE);
INSERT INTO GENDER (GENDER_ID, CODE, ACTIVE, SYSTEMUSER_ID, TIMESTAMP)
            VALUES (2, 'F', 'Y', 1, SYSDATE);
INSERT INTO GENDER (GENDER_ID, CODE, ACTIVE, SYSTEMUSER_ID, TIMESTAMP)
            VALUES (3, 'N', 'Y', 1, SYSDATE);
COMMIT;
--CLIENT TABLE
UPDATE CLIENT C
   SET C.GENDER = 3
 WHERE C.GENDER = 0;

COMMIT;

COMMENT ON COLUMN CLIENT.GENDER IS '1 = Male 2 = Female 3 = Unknown (default) ';

CREATE INDEX XIF3CLIENT ON CLIENT (GENDER ASC);

ALTER TABLE CLIENT ADD CONSTRAINT FK_CLIENT_GENDER FOREIGN KEY (GENDER) REFERENCES GENDER (GENDER_ID);

--ERP_I_WEARER TABLE
ALTER TABLE ERP_I_WEARER MODIFY (SEX VARCHAR2(1) DEFAULT 'N');
COMMENT ON COLUMN ERP_I_WEARER.SEX IS 'Must be an existing active gender defined(gender.code). Standard available options: M = Male, F = Female, N = Not Known.';

ALTER TABLE ERP_I_CLIENT DROP CONSTRAINT CHK_ERP_I_CLIENT_GENDERCHECK;
ALTER TABLE ERP_I_CLIENT MODIFY (GENDER DEFAULT 3);
ALTER TABLE ERP_I_CLIENT ADD CONSTRAINT CHK_ERP_I_CLIENT_GENDERCHECK CHECK (GENDER IN (1, 2, 3));
COMMENT ON COLUMN erp_i_client.gender IS '1 = Male 2 = Female 3 = Unknown (default) ';

--GDTRANSACTION TABLE
CREATE INDEX XIF5GDTRANSACTION ON GDTRANSACTION (SEX ASC);

UPDATE GDTRANSACTION G
   SET G.SEX = 'N' 
 WHERE G.SEX NOT IN ('M', 'F');

COMMIT;

ALTER TABLE GDTRANSACTION ADD CONSTRAINT FK_GDTRANSACTION_SEX FOREIGN KEY (SEX) REFERENCES GENDER (CODE);

ALTER TABLE GDTRANSACTION DROP COLUMN MYGENDER;

-- CREATE PROCEDURE TO POPULATE THE GENDER TABLE WITH ALL OTHER GENDERS CURRENTLY IN USE APART FROM THE 3 GENDERS MENTIONED ABOVE
create or replace procedure insertgender is
BEGIN

  FOR X IN (SELECT DISTINCT SEX
              FROM WEARER
             WHERE SEX NOT IN ('M', 'F', 'N'))
  LOOP
    BEGIN
      INSERT INTO GENDER
        (GENDER_ID, CODE, ACTIVE, SYSTEMUSER_ID, TIMESTAMP)
      VALUES
        (seq_gender.nextval, X.SEX, 'Y', 1, SYSDATE);
    
     INSERT INTO GENDER_DESC 
       (gender_id, language_id, description, systemuser_id, timestamp)
       select g.gender_id, l.language_id,
       case 
        when g.code = 'X' then 'Non-Binary'
        when g.code = 'T' then 'Transgender'
        when g.code = 'I' then 'Intersex'
        when g.code = 'U' then 'Prefer not to say'
        else 'Dummy description'
       end case, 1, sysdate
       from gender g, languages l
       where l.translatedescription = 'Y'
         and not exists (select 1
                           from gender_desc gd
                          where gd.gender_id = g.gender_id
                            and gd.language_id = l.language_id)
       and g.code not in ('M', 'F', 'N');
    EXCEPTION
      WHEN DUP_VAL_ON_INDEX THEN
        NULL;
    END;
    COMMIT;
  END LOOP;
END insertgender;
execute insertgender;
drop procedure insertgender;

--WEARER TABLE
CREATE INDEX XIF1WEARER ON WEARER (SEX ASC);
ALTER TABLE WEARER ADD CONSTRAINT FK_WEARER_SEX FOREIGN KEY (SEX) REFERENCES GENDER (CODE);

COMMENT ON COLUMN WEARER.SEX IS '';
