-- ****************** SqlDBM: Microsoft SQL Server ******************
-- ******************************************************************

DROP TABLE [dbo].[document];
GO


DROP TABLE [dbo].[ptv_id];
GO


DROP TABLE [dbo].[route_detail];
GO


DROP TABLE [dbo].[cotg];
GO


DROP TABLE [dbo].[summon_stamp];
GO


DROP TABLE [dbo].[planetpress_control];
GO


DROP TABLE [dbo].[summon_control];
GO


DROP TABLE [dbo].[summon_cost];
GO


DROP TABLE [dbo].[summon_casenumber];
GO


DROP TABLE [dbo].[summon_metadata];
GO


DROP TABLE [dbo].[subset];
GO


DROP TABLE [dbo].[cotg_category_description];
GO


DROP TABLE [dbo].[cotg_item_description];
GO


DROP TABLE [dbo].[control_description];
GO


DROP TABLE [dbo].[route];
GO


DROP TABLE [dbo].[summon];
GO


--************************************** [dbo].[cotg_category_description]

CREATE TABLE [dbo].[cotg_category_description]
(
 [id]          INT IDENTITY (1, 1) NOT NULL ,
 [name]        VARCHAR(50) NOT NULL ,
 [description] VARCHAR(800) NOT NULL ,

 CONSTRAINT [pk_cotg_category_description] PRIMARY KEY CLUSTERED ([id] ASC),
 CONSTRAINT [ux_cotg_category_name] UNIQUE NONCLUSTERED ([name] ASC)
);
GO



--************************************** [dbo].[cotg_item_description]

CREATE TABLE [dbo].[cotg_item_description]
(
 [id]          INT IDENTITY (1, 1) NOT NULL ,
 [name]        VARCHAR(50) NOT NULL ,
 [description] VARCHAR(800) NOT NULL ,

 CONSTRAINT [pk_cotg_item_description] PRIMARY KEY CLUSTERED ([id] ASC),
 CONSTRAINT [ux_cotg_item_name] UNIQUE NONCLUSTERED ([name] ASC)
);
GO



--************************************** [dbo].[control_description]

CREATE TABLE [dbo].[control_description]
(
 [id]          INT NOT NULL ,
 [name]        VARCHAR(30) NOT NULL ,
 [description] VARCHAR(800) NOT NULL ,

 CONSTRAINT [pk_control_description] PRIMARY KEY CLUSTERED ([id] ASC)
);
GO



--************************************** [dbo].[route]

CREATE TABLE [dbo].[route]
(
 [id]          INT IDENTITY (1, 1) NOT NULL ,
 [NUMBER]      VARCHAR(50) NOT NULL ,
 [bailiff]     INT NOT NULL ,
 [drive_date]  DATE NOT NULL ,
 [costs]       DECIMAL(18,2) NOT NULL ,
 [LENGTH]      INT NOT NULL ,
 [index_count] INT NOT NULL CONSTRAINT [DF_route_index_count] DEFAULT 1 ,
 [ptv_id]      INT NOT NULL ,

 CONSTRAINT [pk_route] PRIMARY KEY CLUSTERED ([id] ASC)
);
GO



--************************************** [dbo].[summon]

CREATE TABLE [dbo].[summon]
(
 [id]          INT IDENTITY (1, 1) NOT NULL ,
 [name]        VARCHAR(50) NULL ,
 [TYPE]        VARCHAR(50) NULL ,
 [action_code] VARCHAR(50) NULL ,
 [action_id]   INT NULL ,
 [location]    VARCHAR(500) NULL ,

 CONSTRAINT [pk_summon] PRIMARY KEY CLUSTERED ([id] ASC)
);
GO



--************************************** [dbo].[ptv_id]

CREATE TABLE [dbo].[ptv_id]
(
 [summon_id] INT NOT NULL ,
 [id]        INT NOT NULL ,
 [DTS]       DATETIME NOT NULL ,

 CONSTRAINT [pk_link_id] PRIMARY KEY CLUSTERED ([summon_id] ASC),
 CONSTRAINT [fk_summon_id_ptv_link] FOREIGN KEY ([summon_id])
  REFERENCES [dbo].[summon]([id])
);
GO


CREATE NONCLUSTERED INDEX [ix_link_summon_id] ON [dbo].[ptv_id]
 (
  [summon_id] ASC
 )

GO


--************************************** [dbo].[route_detail]

CREATE TABLE [dbo].[route_detail]
(
 [id]                 INT NOT NULL ,
 [route_id]           INT NOT NULL ,
 [sub_number]         INT NOT NULL ,
 [address]            VARCHAR(200) NOT NULL ,
 [merge_same_address] BIT NOT NULL ,

 CONSTRAINT [pk_route_detail] PRIMARY KEY CLUSTERED ([id] ASC, [route_id] ASC),
 CONSTRAINT [fk_route_id_route_detail] FOREIGN KEY ([route_id])
  REFERENCES [dbo].[route]([id]),
 CONSTRAINT [fk_summon_id_ptv_link_id] FOREIGN KEY ([id])
  REFERENCES [dbo].[summon]([id])
);
GO


CREATE NONCLUSTERED INDEX [ix_route_id_route_detail] ON [dbo].[route_detail]
 (
  [route_id] ASC
 )

GO

CREATE NONCLUSTERED INDEX [ix_summon_id_link_id] ON [dbo].[route_detail]
 (
  [id] ASC
 )

GO


--************************************** [dbo].[cotg]

CREATE TABLE [dbo].[cotg]
(
 [summon_id]   INT NOT NULL ,
 [category_id] INT NOT NULL ,
 [item_id]     INT NOT NULL ,
 [index_nr]    INT NOT NULL ,
 [DATA]        VARCHAR(400) NOT NULL ,

 CONSTRAINT [ux_summon_category_item_index] UNIQUE NONCLUSTERED ([summon_id] ASC, [index_nr] ASC),
 CONSTRAINT [fk_cotg_item_id] FOREIGN KEY ([item_id])
  REFERENCES [dbo].[cotg_item_description]([id]),
 CONSTRAINT [fk_cotg_category_id] FOREIGN KEY ([category_id])
  REFERENCES [dbo].[cotg_category_description]([id]),
 CONSTRAINT [fk_cotg_summon_id] FOREIGN KEY ([summon_id])
  REFERENCES [dbo].[summon]([id])
);
GO


CREATE CLUSTERED INDEX [pk_cotg_simmon_id] ON [dbo].[cotg]
 (
  [summon_id] ASC
 )

GO


--************************************** [dbo].[summon_stamp]

CREATE TABLE [dbo].[summon_stamp]
(
 [summon_id]     INT NOT NULL ,
 [stamp_type]    VARCHAR(50) NOT NULL ,
 [index_count]   INT NOT NULL CONSTRAINT [DF_summon_stamp_index_count] DEFAULT 1 ,
 [stamp_data]    VARCHAR(MAX) NULL ,
 [y_coordinates] INT NOT NULL ,
 [x_coordinates] INT NOT NULL ,
 [page_nr]       INT NOT NULL ,

 CONSTRAINT [ux_summon_type_index_count] UNIQUE NONCLUSTERED ([summon_id] ASC, [stamp_type] ASC, [index_count] ASC)
);
GO


CREATE NONCLUSTERED INDEX [ix_summon_id_stamp] ON [dbo].[summon_stamp]
 (
  [summon_id] ASC
 )

GO


--************************************** [dbo].[planetpress_control]

CREATE TABLE [dbo].[planetpress_control]
(
 [description_id] INT NOT NULL ,
 [summon_id]      INT NOT NULL ,
 [index_count]    INT NOT NULL CONSTRAINT [DF_planetpress_control_index_count] DEFAULT 1 ,
 [RESULT]         BIT NOT NULL ,
 [dts]            DATETIME NOT NULL ,

 CONSTRAINT [pk_planetpress_control] PRIMARY KEY CLUSTERED),
 CONSTRAINT [fk_summon_id_planetpress_control] FOREIGN KEY ([summon_id])
  REFERENCES [dbo].[summon]([id])
);
GO


CREATE NONCLUSTERED INDEX [ix_summon_id_planetpress_control] ON [dbo].[planetpress_control]
 (
  [summon_id] ASC
 )

GO

CREATE NONCLUSTERED INDEX [ix_control_id_planetpress_control] ON [dbo].[planetpress_control]
 (
  [description_id] ASC
 )

GO


--************************************** [dbo].[summon_control]

CREATE TABLE [dbo].[summon_control]
(
 [id]             INT NOT NULL ,
 [description_id] INT NOT NULL ,
 [index_count]    INT NOT NULL CONSTRAINT [DF_summon_control_index_count] DEFAULT 1 ,
 [RESULT]         BIT NOT NULL ,
 [extra]          VARCHAR(500) NULL ,
 [dts]            DATETIME NOT NULL ,

 CONSTRAINT [pk_summon_control] PRIMARY KEY CLUSTERED ([id] ASC),
 CONSTRAINT [fk_summon_id_control] FOREIGN KEY ([id])
  REFERENCES [dbo].[summon]([id]),
 CONSTRAINT [fk_control_id_control] FOREIGN KEY ([description_id])
  REFERENCES [dbo].[control_description]([id])
);
GO


CREATE NONCLUSTERED INDEX [ix_summon_id_control] ON [dbo].[summon_control]
 (
  [id] ASC
 )

GO

CREATE NONCLUSTERED INDEX [ix_control_id_control] ON [dbo].[summon_control]
 (
  [description_id] ASC
 )

GO


--************************************** [dbo].[summon_cost]

CREATE TABLE [dbo].[summon_cost]
(
 [summon_id] INT NOT NULL ,
 [TYPE]      VARCHAR(50) NOT NULL ,
 [amount]    DECIMAL(18,2) NOT NULL ,

 CONSTRAINT [ux_summon_id_type] UNIQUE NONCLUSTERED ([summon_id] ASC, [TYPE] ASC)
);
GO


CREATE NONCLUSTERED INDEX [ix_summon_id_cost] ON [dbo].[summon_cost]
 (
  [summon_id] ASC
 )

GO


--************************************** [dbo].[summon_casenumber]

CREATE TABLE [dbo].[summon_casenumber]
(
 [summon_id]  INT NOT NULL ,
 [casenumber] VARCHAR(11) NOT NULL ,

 CONSTRAINT [pk_summon_casenumber] PRIMARY KEY CLUSTERED),
 CONSTRAINT [fk_summon_id_casenumber] FOREIGN KEY ([summon_id])
  REFERENCES [dbo].[summon]([id])
);
GO


CREATE NONCLUSTERED INDEX [ix_summon_id_casenumber] ON [dbo].[summon_casenumber]
 (
  [summon_id] ASC
 )

GO


--************************************** [dbo].[summon_metadata]

CREATE TABLE [dbo].[summon_metadata]
(
 [id]                 INT NOT NULL ,
 [DATE]               DATE NULL ,
 [TYPE]               VARCHAR(5) NULL ,
 [created_by]         VARCHAR(10) NULL ,
 [printed_deleted_by] VARCHAR(10) NULL ,
 [team]               VARCHAR(30) NULL ,
 [vat]                BIT NULL ,
 [latest_info_check]  DATE NULL ,
 [cotg_storage_id]    INT NULL ,
 [cn_storage_id]      INT NULL ,
 [batch_nr]           INT NULL ,

 CONSTRAINT [pk_summon_metadata] PRIMARY KEY CLUSTERED ([id] ASC),
 CONSTRAINT [fk_summon_id_metadata] FOREIGN KEY ([id])
  REFERENCES [dbo].[summon]([id])
);
GO


CREATE NONCLUSTERED INDEX [ix_summon_id_metadata] ON [dbo].[summon_metadata]
 (
  [id] ASC
 )

GO


--************************************** [dbo].[subset]

CREATE TABLE [dbo].[subset]
(
 [id]              INT IDENTITY (1, 1) NOT NULL ,
 [summon_id]       INT NOT NULL ,
 [staple]          BIT NOT NULL ,
 [name]            VARCHAR(50) NULL ,
 [location]        VARCHAR(500) NULL ,
 [duplicate_count] AS 1 ,

 CONSTRAINT [pk_subset] PRIMARY KEY CLUSTERED ([id] ASC)
);
GO


CREATE NONCLUSTERED INDEX [ix_subset_documentset] ON [dbo].[subset]
 (
  [summon_id] ASC
 )

GO


--************************************** [dbo].[document]

CREATE TABLE [dbo].[document]
(
 [id]          INT IDENTITY (1, 1) NULL ,
 [subset_id]   INT NOT NULL ,
 [code]        VARCHAR(50) NOT NULL ,
 [name]        VARCHAR(50) NOT NULL ,
 [duplex]      BIT NOT NULL ,
 [TYPE]        VARCHAR(20) NOT NULL ,
 [media]       VARCHAR(50) NOT NULL ,
 [stamp]       VARCHAR(50) NULL ,
 [page_count]  INT NULL ,
 [action_code] VARCHAR(50) NULL ,
 [action_id]   INT NULL ,
 [location]    VARCHAR(500) NULL ,

 CONSTRAINT [pk_document] PRIMARY KEY CLUSTERED ([id] ASC)
);
GO


CREATE NONCLUSTERED INDEX [ix_subset_document] ON [dbo].[document]
 (
  [subset_id] ASC
 )

GO