time-cafe-desktop-app/sqlscripts/drop-create/drop-create-Database.sql

USE [master];

IF EXISTS (SELECT 1 FROM sys.databases WHERE [name] = N'Database')
BEGIN
    EXECUTE sp_executesql
    @stmt =
      N'USE [Database];
      ALTER DATABASE [Database]
        SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
      USE [tempdb];
      DROP DATABASE [Database];'
END;
GO

CREATE DATABASE [Database];
GO

USE [Database];
GO

CREATE SCHEMA [Tables] AUTHORIZATION [dbo];
GO 

CREATE SCHEMA [Views] AUTHORIZATION [dbo];
GO

CREATE SCHEMA [Functions] AUTHORIZATION [dbo];
GO

CREATE SCHEMA [Procedures] AUTHORIZATION [dbo];
GO

CREATE TABLE [Tables].[product_types](
  [id]        INT NOT NULL IDENTITY(1, 1),
  [parent_id] INT DEFAULT(NULL),
  [name]      NVARCHAR(128) NOT NULL,
  CONSTRAINT PK_product_types
    PRIMARY KEY([id]),
  CONSTRAINT FK_product_types FOREIGN KEY([parent_id])
    REFERENCES [Tables].[product_types]([id])
);

CREATE TABLE [Tables].[products](
  [id]              INT NOT NULL IDENTITY(1, 1),
  [barcode]         BIGINT NOT NULL UNIQUE,
  [name]            NVARCHAR(128) NOT NULL UNIQUE,
  [product_type_id] INT NOT NULL,
  CONSTRAINT PK_products
    PRIMARY KEY([id]),
  CONSTRAINT CHECK_products
    CHECK([barcode] > -1 AND [barcode] < 10000000000000),
  CONSTRAINT FK_product_type_id FOREIGN KEY([product_type_id])
    REFERENCES [Tables].[product_types]([id])
);

CREATE TABLE [Tables].[suppliers](
  [id]               INT NOT NULL IDENTITY(1, 1),
  [name]             NVARCHAR(128) NOT NULL UNIQUE,
  [address]          NVARCHAR(128) NOT NULL,
  [telephone_number] NVARCHAR(128) NOT NULL,
  CONSTRAINT PK_suppliers
    PRIMARY KEY([id])
);

--CREATE TABLE [Tables].[additional_variables](
--  [id]    INT NOT NULL IDENTITY(1, 1),
--  [name]  NVARCHAR(128) NOT NULL UNIQUE,
--  [value] NVARCHAR(128) NOT NULL
--  CONSTRAINT PK_additionalVariables
--    PRIMARY KEY([id])
--);

CREATE TABLE [Tables].[attributes](
  [id]   INT NOT NULL IDENTITY(1, 1),
  [name] NVARCHAR(128) NOT NULL UNIQUE,
  CONSTRAINT PK_attributes
    PRIMARY KEY([id])
);

CREATE TABLE [Tables].[product_attributes](
  [product_id]   INT NOT NULL,
  [attribute_id] INT NOT NULL,
  [description] NVARCHAR(128)
  CONSTRAINT PK_product_attributes
    PRIMARY KEY([product_id], [attribute_id]),
  CONSTRAINT FK_product_attributes_product_id FOREIGN KEY([product_id])
    REFERENCES [Tables].[products]([id]),
  CONSTRAINT FK_product_attributes_attribute_id FOREIGN KEY([attribute_id])
    REFERENCES [Tables].[attributes]([id])
);

CREATE TABLE [Tables].[product_values](
  [id]           INT NOT NULL IDENTITY(1, 1),
  [product_id]   INT NOT NULL,
  [attribute_id] INT NOT NULL,
  [value]        NVARCHAR(128) NOT NULL,
  CONSTRAINT PK_product_values
    PRIMARY KEY([id]),
  CONSTRAINT FK_product_values FOREIGN KEY([product_id], [attribute_id])
    REFERENCES [Tables].[product_attributes]([product_id], [attribute_id])
);

CREATE TABLE [Tables].[price_history_records](
  [id]         INT NOT NULL IDENTITY(1, 1),
  [product_id] INT NOT NULL,
  [datetime]   DATETIME NOT NULL DEFAULT(SYSDATETIME()),
  [price]      MONEY NOT NULL,
  CONSTRAINT PK_price_history_records
    PRIMARY KEY([id]),
  CONSTRAINT FK_price_history_records_product_id FOREIGN KEY([product_id])
    REFERENCES [Tables].[products]([id]),
  CONSTRAINT CHECK_price_history_records
    CHECK([price] >= 0)
);

-- CREATE TABLE [Tables].[barcode_history]...

CREATE TABLE [Tables].[product_outflow_records](
  [id]         INT NOT NULL IDENTITY(1, 1),
  [product_id] INT NOT NULL, -- without reference (temp)
  [barcode]    BIGINT NOT NULL,
  [count]      INT NOT NULL,
  [datetime]   DATETIME NOT NULL DEFAULT(SYSDATETIME())
  CONSTRAINT PK_product_outflow_records
    PRIMARY KEY([id]),
  CONSTRAINT CHECK_product_outflow_records
    CHECK([count] > 0),
  CONSTRAINT FK_product_outflow_records FOREIGN KEY([product_id])
    REFERENCES [Tables].[products]([id])
);

CREATE TABLE [Tables].[product_inflow_records](
  [id]          INT NOT NULL IDENTITY(1, 1),
  [product_id]  INT NOT NULL,
  [barcode]     BIGINT NOT NULL, -- without reference (temp)
  [supplier_id] INT NOT NULL,
  [count]       INT NOT NULL,
  [full_price]  MONEY NOT NULL,
  [datetime]    DATETIME NOT NULL DEFAULT(SYSDATETIME())
  CONSTRAINT PK_product_inflow_records
    PRIMARY KEY([id])
  CONSTRAINT FK_product_inflow_records_supplier_id FOREIGN KEY([supplier_id])
    REFERENCES [Tables].[suppliers]([id]),
  CONSTRAINT CHECK_product_inflow_records
    CHECK([count] > 0 AND [full_price] >= 0),
  CONSTRAINT FK_product_inflow_records FOREIGN KEY([product_id])
    REFERENCES [Tables].[products]([id])
);

CREATE TABLE [Tables].[write_off_reasons](
  [id]   INT NOT NULL IDENTITY(1, 1),
  [name] NVARCHAR(128) NOT NULL,
  CONSTRAINT PK_write_off_reasons
    PRIMARY KEY([id]),
);

CREATE TABLE [Tables].[product_write_off_records](
  [id]         INT NOT NULL IDENTITY(1, 1),
  [product_id] INT NOT NULL,
  [barcode]    BIGINT NOT NULL, -- without reference (temp)
  [reason_id]  INT NOT NULL,
  [count]      INT NOT NULL,
  [datetime]   DATETIME NOT NULL DEFAULT(SYSDATETIME())
  CONSTRAINT PK_product_write_off_records
    PRIMARY KEY([id])
  CONSTRAINT FK_product_write_off_records_reason_id FOREIGN KEY([reason_id])
    REFERENCES [Tables].[write_off_reasons]([id]),
  CONSTRAINT CHECK_product_write_off_records
    CHECK([count] > 0),
  CONSTRAINT FK_product_write_off_records FOREIGN KEY([product_id])
    REFERENCES [Tables].[products]([id])
);
GO

CREATE VIEW [Views].[product_properties]
WITH SCHEMABINDING
AS
  SELECT [PA].[product_id], [PA].[attribute_id], [A].[name], [PV].[value]
  FROM [Tables].[product_attributes] AS [PA]
  LEFT OUTER JOIN [Tables].[product_values] AS [PV]
  ON [PV].[product_id] = [PA].[product_id]
     AND [PV].[attribute_id] = [PA].[attribute_id]
  JOIN [Tables].[attributes] AS [A]
  ON [A].[id] = [PA].[attribute_id];
GO

CREATE VIEW [Views].[available_product_types]
WITH SCHEMABINDING
AS
  SELECT [B].[id], [B].[parent_id], [B].[name]
  FROM [Tables].[product_types] AS [A]
  RIGHT JOIN [Tables].[product_types] AS [B]
    ON [B].[id] = [A].[parent_id]
  WHERE [A].[id] IS NULL;
GO