-- Drop table
/*

DROP TABLE master.dbo.ENTITY
GO

*/

CREATE TABLE master.dbo.ENTITY (
    entity NVARCHAR(255) NOT NULL

    CONSTRAINT ENTITY_PK PRIMARY KEY (entity)
)
GO

/***********************************************************************/

-- Drop table
/*

DROP TABLE master.dbo.[SOURCE]
GO

*/

CREATE TABLE master.dbo.[SOURCE] (
    [SOURCE] NVARCHAR(255) NOT NULL

    CONSTRAINT SOURCE_PK PRIMARY KEY ([SOURCE])
)
GO

/***********************************************************************/

-- Drop table
/*

DROP TABLE master.dbo.XREF
GO

ALTER TABLE master.dbo.XREF
DROP CONSTRAINT XREF_CHK_FLAG
GO

*/

CREATE TABLE master.dbo.XREF (
    entity NVARCHAR(255) NOT NULL,
    [SOURCE] NVARCHAR(255) NOT NULL,
    [VALUE] NVARCHAR(255) NOT NULL,
    linkID NVARCHAR(255) NOT NULL,
    flag BIT NOT NULL,
    updated_at datetime DEFAULT getdate() NOT NULL,
    created_at datetime DEFAULT getdate() NOT NULL

    CONSTRAINT XREF_PK PRIMARY KEY (entity,[SOURCE],[VALUE]),
    CONSTRAINT XREF_ENTITY_FK1 FOREIGN KEY (entity) REFERENCES master.dbo.ENTITY(entity),
    CONSTRAINT XREF_SOURCE_FK1 FOREIGN KEY ([SOURCE]) REFERENCES master.dbo.[SOURCE]([SOURCE]),
    CONSTRAINT XREF_CHK_FLAG CHECK (flag=1 OR flag=0)
)
GO

CREATE INDEX XREF_linkID_IDX ON master.dbo.XREF (linkID)
GO

/***********************************************************************/

-- Drop table
/*

DROP TABLE master.dbo.ENTITY_CONFIG
GO

ALTER TABLE master.dbo.ENTITY_CONFIG
DROP CONF_ENTITY_FK1
GO

*/

CREATE TABLE master.dbo.ENTITY_CONFIG (
    entity NVARCHAR(255) NOT NULL,
    configName NVARCHAR(255) NOT NULL,
    [VALUE] NVARCHAR(255) NOT NULL

    CONSTRAINT CONF_PK PRIMARY KEY (entity,configName),
    CONSTRAINT CONF_ENTITY_FK1 FOREIGN KEY (entity) REFERENCES master.dbo.ENTITY(entity),
)
GO

/***********************************************************************/

-- Drop table
/*

DROP TABLE master.dbo.ENTITY_DEFAULT_VALUES
GO

ALTER TABLE master.dbo.ENTITY_DEFAULT_VALUES
DROP DEF_VAL_ENTITY_FK1
GO

*/

CREATE TABLE master.dbo.ENTITY_DEFAULT_VALUES (
    entity NVARCHAR(255) NOT NULL,
    [SOURCE] NVARCHAR(255) NOT NULL,
    [VALUE] NVARCHAR(255) NOT NULL

    CONSTRAINT DEF_VAL_PK PRIMARY KEY (entity,[SOURCE]),
    CONSTRAINT DEF_VAL_ENTITY_FK1 FOREIGN KEY (entity) REFERENCES master.dbo.ENTITY(entity),
    CONSTRAINT DEF_VAL_ENTITY_FK1 FOREIGN KEY ([SOURCE]) REFERENCES master.dbo.[SOURCE]([SOURCE])
)
GO

/***********************************************************************/