-- 1. create backup tables for DIALOGS and DIALOGS_DESC tables

DECLARE
BEGIN
	EXECUTE IMMEDIATE 'DROP TABLE DIALOGS_BCK';
EXCEPTION
	WHEN OTHERS THEN
		NULL;
END;
/

DECLARE
BEGIN
	EXECUTE IMMEDIATE 'CREATE TABLE DIALOGS_BCK AS  (SELECT * FROM DIALOGS)';
END;
/

DECLARE
BEGIN
	EXECUTE IMMEDIATE 'DROP TABLE DIALOGS_DESC_BCK';
EXCEPTION
	WHEN OTHERS THEN
		NULL;
END;
/
DECLARE
BEGIN
	EXECUTE IMMEDIATE 'CREATE TABLE DIALOGS_DESC_BCK AS (SELECT * FROM DIALOGS_DESC)';
END;
/
-- 2*. create LICENSE_LANGUAGES table and corresponding sequence for generating unique identifiers

CREATE SEQUENCE SEQ_LICENSE_LANGUAGES
 START WITH 1
 INCREMENT BY 1;


CREATE TABLE LICENSE_LANGUAGES
(
  LICENSE_LANGUAGE_ID NUMBER NOT NULL,
  CODE VARCHAR2(5) NOT NULL,
  DESCRIPTION VARCHAR2(40) NOT NULL,
  SYSTEMUSER_ID NUMBER NOT NULL,
  TIMESTAMP DATE NOT NULL
);

ALTER TABLE LICENSE_LANGUAGES ADD CONSTRAINT XPKLICENSE_LANGUAGES PRIMARY KEY (LICENSE_LANGUAGE_ID);

CREATE UNIQUE INDEX XAK1LICENSE_LANGUAGES ON  LICENSE_LANGUAGES (CODE ASC);



-- 3. populate LICENSE_LANGUAGES table with the 6 predefined languages (EN_US, NL_NL, DE_DE, FR_FR, DA_DK, ES_ES) 

INSERT INTO LICENSE_LANGUAGES
  (LICENSE_LANGUAGE_ID, CODE, DESCRIPTION, SYSTEMUSER_ID, TIMESTAMP)
  SELECT SEQ_LICENSE_LANGUAGES.NEXTVAL,
         'EN_US',
         'English (United States)',
        GETMINSYSTEMUSER(),
         SYSDATE
    FROM DUAL;

COMMIT;

INSERT INTO LICENSE_LANGUAGES
  (LICENSE_LANGUAGE_ID, CODE, DESCRIPTION, SYSTEMUSER_ID, TIMESTAMP)
  SELECT SEQ_LICENSE_LANGUAGES.NEXTVAL,
         'NL_NL',
         'Nederlands (Nederland)',
         GETMINSYSTEMUSER(),
         SYSDATE
    FROM DUAL;

COMMIT;

INSERT INTO LICENSE_LANGUAGES
  (LICENSE_LANGUAGE_ID, CODE, DESCRIPTION, SYSTEMUSER_ID, TIMESTAMP)
  SELECT SEQ_LICENSE_LANGUAGES.NEXTVAL,
         'DE_DE',
         'Deutsch (Deutschland)',
        GETMINSYSTEMUSER(),
         SYSDATE
    FROM DUAL;

COMMIT;

INSERT INTO LICENSE_LANGUAGES
  (LICENSE_LANGUAGE_ID, CODE, DESCRIPTION, SYSTEMUSER_ID, TIMESTAMP)
  SELECT SEQ_LICENSE_LANGUAGES.NEXTVAL,
         'FR_FR',
         'Français (France)',
         GETMINSYSTEMUSER(),
         SYSDATE
    FROM DUAL;

COMMIT;

INSERT INTO LICENSE_LANGUAGES
  (LICENSE_LANGUAGE_ID, CODE, DESCRIPTION, SYSTEMUSER_ID, TIMESTAMP)
  SELECT SEQ_LICENSE_LANGUAGES.NEXTVAL,
         'DA_DK',
         'Dansk (Danmark)',
         GETMINSYSTEMUSER(),
         SYSDATE
    FROM DUAL;

COMMIT;

INSERT INTO LICENSE_LANGUAGES
  (LICENSE_LANGUAGE_ID, CODE, DESCRIPTION, SYSTEMUSER_ID, TIMESTAMP)
  SELECT SEQ_LICENSE_LANGUAGES.NEXTVAL,
         'ES_ES',
         'Español (España)',
        GETMINSYSTEMUSER(),
         SYSDATE
    FROM DUAL;

COMMIT;

-- 4. insert into LICENSE_LANGUAGES table those missing languages from the LANGUAGES table

INSERT INTO LICENSE_LANGUAGES
  (LICENSE_LANGUAGE_ID, CODE, DESCRIPTION, SYSTEMUSER_ID, TIMESTAMP)
  SELECT SEQ_LICENSE_LANGUAGES.NEXTVAL,
         L.CODE,
         L.DESCRIPTION,
          GETMINSYSTEMUSER(),
         SYSDATE
    FROM LANGUAGES L
   WHERE L.CODE NOT IN (SELECT DISTINCT  LL.CODE FROM LICENSE_LANGUAGES LL);
     
COMMIT;

-- 5*. add column LICENSE_LANGUAGE_ID to table DIALOGS_DESC

ALTER TABLE DIALOGS_DESC
  ADD (LICENSE_LANGUAGE_ID NUMBER NULL);
  
-- 6*. make LICENSE_LANGUAGE_ID foreign key to LICENSE_LANGUAGES
DROP INDEX XIF2DIALOG_DESC;

CREATE INDEX XIF2DIALOG_DESC ON DIALOGS_DESC (LICENSE_LANGUAGE_ID ASC);

ALTER TABLE DIALOGS_DESC
  ADD CONSTRAINT FK_DIALOGS_DESC_LICENSE_LANG FOREIGN KEY (LICENSE_LANGUAGE_ID)
  REFERENCES LICENSE_LANGUAGES (LICENSE_LANGUAGE_ID);
  
-- 7. fill LICENSE_LANGUAGE_ID with the corresponding values by converting the LANGUAGE_ID based on the language code
-- 8. recreate primary key of table DIALOGS_DESC as the combination of (DIALOG_ID, LICENSE_LANGUAGE_ID)
-- 9. drop column LANGUAGE_ID from table DIALOGS_DESC

CREATE OR REPLACE PROCEDURE FILL_LICENSE_LANGUAGE_ID AS
STMT VARCHAR2(5000) := 'UPDATE DIALOGS_DESC DD SET LICENSE_LANGUAGE_ID = (SELECT LL.LICENSE_LANGUAGE_ID FROM LICENSE_LANGUAGES LL WHERE LL.CODE = (SELECT L.CODE FROM LANGUAGES L WHERE L.LANGUAGE_ID = DD.LANGUAGE_ID))';
BEGIN
 EXECUTE IMMEDIATE(STMT);
COMMIT;

EXECUTE IMMEDIATE ('ALTER TABLE DIALOGS_DESC
  DROP CONSTRAINT XPKDIALOG_DESC DROP INDEX');

EXECUTE IMMEDIATE ('ALTER TABLE DIALOGS_DESC
  ADD CONSTRAINT XPKDIALOG_DESC PRIMARY KEY (DIALOG_ID, LICENSE_LANGUAGE_ID)');

EXECUTE IMMEDIATE ('ALTER TABLE DIALOGS_DESC  DROP COLUMN LANGUAGE_ID');

EXCEPTION WHEN OTHERS THEN NULL;
       
END FILL_LICENSE_LANGUAGE_ID;
/

EXECUTE FILL_LICENSE_LANGUAGE_ID;

DROP PROCEDURE FILL_LICENSE_LANGUAGE_ID;

-- 10*. enforce the NOT NULL constraint on the LICENSE_LANGUAGE_ID column on DIALOGS_DESC after it was populated


ALTER TABLE DIALOGS_DESC MODIFY (LICENSE_LANGUAGE_ID NUMBER NOT NULL);

-- 11. move descriptions for standard languages from DIALOGS table to DIALOGS_DESC

CREATE OR REPLACE PROCEDURE MOVE_DESCRIPTION_FOR_LANGUAGE(COLUMNNAME VARCHAR2, LANGCODE VARCHAR2) AS
STMT VARCHAR2(5000) := 'INSERT INTO DIALOGS_DESC  (DIALOG_ID, DESCRIPTION, SYSTEMUSER_ID, TIMESTAMP, LICENSE_LANGUAGE_ID)
  SELECT D.DIALOG_ID,
         D.COLUMNNAME,
         0,
         SYSDATE,
         (SELECT LL.LICENSE_LANGUAGE_ID
            FROM LICENSE_LANGUAGES LL
           WHERE LL.CODE = LANGCODE)
    FROM DIALOGS D
   WHERE NOT EXISTS (SELECT 1
            FROM DIALOGS_DESC DD
           WHERE DD.DIALOG_ID = D.DIALOG_ID
             AND DD.LICENSE_LANGUAGE_ID =
                 (SELECT LL.LICENSE_LANGUAGE_ID
                    FROM LICENSE_LANGUAGES LL
                   WHERE LL.CODE = LANGCODE))';
BEGIN
 EXECUTE IMMEDIATE (REPLACE(REPLACE(STMT, 'LANGCODE ', LANGCODE), 'COLUMNNAME', COLUMNNAME));
COMMIT;

EXCEPTION WHEN OTHERS THEN NULL;
END MOVE_DESCRIPTION_FOR_LANGUAGE;
/

EXECUTE MOVE_DESCRIPTION_FOR_LANGUAGE('ENDESCRIPTION', 'EN_US');
EXECUTE MOVE_DESCRIPTION_FOR_LANGUAGE('NLDESCRIPTION', 'NL_NL');
EXECUTE MOVE_DESCRIPTION_FOR_LANGUAGE('GEDESCRIPTION', 'DE_DE');
EXECUTE MOVE_DESCRIPTION_FOR_LANGUAGE('FRDESCRIPTION', 'FR_FR');
EXECUTE MOVE_DESCRIPTION_FOR_LANGUAGE('DKDESCRIPTION', 'DA_DK');
EXECUTE MOVE_DESCRIPTION_FOR_LANGUAGE('SPDESCRIPTION', 'ES_ES');

DROP PROCEDURE MOVE_DESCRIPTION_FOR_LANGUAGE;

-- 12*. remove columns (ENDESCRIPTION, NLDESCRIPTION, GEDESCRIPTION, FRDESCRIPTION, DKDESCRIPTION, SPDESCRIPTION) from table DIALOGS 

ALTER TABLE DIALOGS 
  DROP COLUMN ENDESCRIPTION;
ALTER TABLE DIALOGS 
  DROP COLUMN NLDESCRIPTION;
ALTER TABLE DIALOGS 
  DROP COLUMN GEDESCRIPTION;
ALTER TABLE DIALOGS 
  DROP COLUMN FRDESCRIPTION;
ALTER TABLE DIALOGS 
  DROP COLUMN  DKDESCRIPTION;
ALTER TABLE DIALOGS 
  DROP COLUMN SPDESCRIPTION;
