--vd20190826: New table for languages
--Reference: en.wikipedia.org/wiki/List_of_ISO_639-1_codes

--To remove table:
--ALTER TABLE Customer DROP CONSTRAINT FK_Customer_Language
--ALTER TABLE Customer DROP COLUMN CustomerLanguage
--ALTER TABLE ReservationAddress DROP CONSTRAINT FK_Reservation_Language
--ALTER TABLE ReservationAddress DROP COLUMN ReservationLanguage
--DROP TABLE SpokenLanguage

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WITH (NOLOCK) 
               WHERE 
                 id = object_id(N'[dbo].[SpokenLanguage]') AND 
                 OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN

  CREATE TABLE dbo.SpokenLanguage(
    ID INTEGER IDENTITY(1, 1) NOT NULL PRIMARY KEY,
	ISO_Name VARCHAR(50) NOT NULL,
	ISO_639_1 VARCHAR(10) NOT NULL,
	Active BIT NOT NULL DEFAULT 1,
	Notes NVARCHAR(MAX) NULL,
	Parent INTEGER NULL)

  ALTER TABLE SpokenLanguage ADD CONSTRAINT FK_Language_CountryFK
    FOREIGN KEY (Parent)
    REFERENCES Countries (CountryNumber) 

  IF NOT EXISTS
  (
    SELECT SysColumns.Name 
    FROM SysObjects WITH (NOLOCK) 
      INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
      AND SysColumns.Name = 'CustomerLanguage'
      WHERE SysObjects.Name = 'Customer'
  )
  BEGIN
    ALTER TABLE Customer ADD CustomerLanguage INTEGER NULL
    ALTER TABLE Customer ADD CONSTRAINT FK_Customer_Language
      FOREIGN KEY (CustomerLanguage) REFERENCES SpokenLanguage (ID)
  END

  IF NOT EXISTS
  (
    SELECT SysColumns.Name 
    FROM SysObjects WITH (NOLOCK) 
      INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
      AND SysColumns.Name = 'ReservationLanguage'
      WHERE SysObjects.Name = 'ReservationAddress'
  )
  BEGIN
    ALTER TABLE ReservationAddress ADD ReservationLanguage INTEGER NULL
    ALTER TABLE ReservationAddress ADD CONSTRAINT FK_Reservation_Language
      FOREIGN KEY (ReservationLanguage) REFERENCES SpokenLanguage (ID)
  END

END

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'Displayed_Name'
    WHERE SysObjects.Name = 'SpokenLanguage'
)
  ALTER TABLE SpokenLanguage ADD Displayed_Name VARCHAR(50) NULL 
GO

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'LanguageNumber'
    WHERE SysObjects.Name = 'Templates'
)
BEGIN
  ALTER TABLE Templates ADD LanguageNumber INTEGER NULL

  ALTER TABLE Templates ADD CONSTRAINT FK_Templates_LanguageNumber
    FOREIGN KEY (LanguageNumber)
    REFERENCES SpokenLanguage (ID)
END
GO
IF NOT EXISTS (SELECT 1 FROM SPOKENLANGUAGE (NOLOCK))
BEGIN
  INSERT INTO SpokenLanguage (ISO_Name, ISO_639_1)
    VALUES ('Danish', 'da')
  INSERT INTO SpokenLanguage (ISO_Name, ISO_639_1)
    VALUES ('English', 'en')
  INSERT INTO SpokenLanguage (ISO_Name, ISO_639_1)
    VALUES ('Croatian', 'hr')
  INSERT INTO SpokenLanguage (ISO_Name, ISO_639_1)
    VALUES ('Dutch', 'nl')
  INSERT INTO SpokenLanguage (ISO_Name, ISO_639_1)
    VALUES ('Estonian', 'et')
  INSERT INTO SpokenLanguage (ISO_Name, ISO_639_1)
    VALUES ('Faroese', 'fo')
  INSERT INTO SpokenLanguage (ISO_Name, ISO_639_1)
    VALUES ('Finnish', 'fi')
  INSERT INTO SpokenLanguage (ISO_Name, ISO_639_1)
    VALUES ('French', 'fr')
  INSERT INTO SpokenLanguage (ISO_Name, ISO_639_1)
    VALUES ('German', 'de')
  INSERT INTO SpokenLanguage (ISO_Name, ISO_639_1)
    VALUES ('Greenlandic', 'kl')
  INSERT INTO SpokenLanguage (ISO_Name, ISO_639_1)
    VALUES ('Italian', 'it')
  INSERT INTO SpokenLanguage (ISO_Name, ISO_639_1)
    VALUES ('Lithuanian', 'lt')
  INSERT INTO SpokenLanguage (ISO_Name, ISO_639_1)
    VALUES ('Latvian', 'lv')
  INSERT INTO SpokenLanguage (ISO_Name, ISO_639_1)
    VALUES ('Norwegian', 'no')
  INSERT INTO SpokenLanguage (ISO_Name, ISO_639_1)
    VALUES ('Polish', 'pl')
  INSERT INTO SpokenLanguage (ISO_Name, ISO_639_1)
    VALUES ('Portuguese', 'pt')
  INSERT INTO SpokenLanguage (ISO_Name, ISO_639_1)
    VALUES ('Russian', 'ru')
  INSERT INTO SpokenLanguage (ISO_Name, ISO_639_1)
    VALUES ('Serbian', 'sr')
  INSERT INTO SpokenLanguage (ISO_Name, ISO_639_1)
    VALUES ('Spanish', 'es')
  INSERT INTO SpokenLanguage (ISO_Name, ISO_639_1)
    VALUES ('Swedish', 'sv')
END
ELSE
BEGIN
	UPDATE SpokenLanguage SET ISO_639_1='sv' WHERE ISO_Name='Swedish'
END
