USE master;
GO

DROP DATABASE sunny_erp;
GO

/* Database: sunny_erp
 *
 * Sunny ERP Database
 *
 */
CREATE DATABASE sunny_erp ON PRIMARY (
    NAME = sunny_erp_DATA,
    FILENAME = "C:\Databases\sunny_erp_DATA.mdf",
    SIZE = 10MB,
    FILEGROWTH = 10%
) LOG ON (
    NAME = sunny_erp_LOG,
    FILENAME = "C:\Databases\sunny_erp_DATA.ldf",
    SIZE = 5MB,
    FILEGROWTH = 10%
);
GO

USE sunny_erp;

-- EXEC sp_addlogin '${owner}', 'asdasd', 'sunny_erp';
EXEC sp_adduser '${owner}','${owner}', 'db_owner';










/* Table: evolved_configs
 *
 * Generic configurations registry.
 *
 * Primary key:
 *
 *  (int) - PK_evolved_configs_id
 *
 * Indexes:
 *
 *  (clustered) - PK_evolved_configs_id
 *
 * Defaults:
 *
 *  (smalldatetime) - DF_evolved_configs_dtIns. Defaults to GETDATE()
 *  (smalldatetime) - DF_evolved_configs_dtUpd. Defaults to GETDATE()
 *  (nvarchar)      - DF_evolved_configs_owner. Defaults to "unknown"
 *
 */
CREATE TABLE [${owner}].[evolved_configs] (
    [id] nvarchar(32) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL CONSTRAINT PK_evolved_configs_id PRIMARY KEY CLUSTERED,
    [config] ntext COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,
    [dtIns] smalldatetime NOT NULL CONSTRAINT DF_evolved_configs_dtIns DEFAULT getdate(),
    [dtUpd] smalldatetime NOT NULL CONSTRAINT DF_evolved_configs_dtUpd DEFAULT getdate(),
    [owner] nvarchar(32) NOT NULL CONSTRAINT DF_evolved_configs_owner DEFAULT 'unknown'
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];
GO

/* Stored Procedure: SP_evolved_config_create
 *
 * Creates an evolved_config.
 *
 * Parameters:
 *
 *  (nvarchar) - config identifier
 *  (ntext)    - config
 *  (nvarchar) - owner (optional, defaults to "unknown")
 *
 */
CREATE PROC [${owner}].[SP_evolved_config_create]
    @id nvarchar(32) = NULL,
    @config ntext = NULL,
    @owner nvarchar(32) = 'unknown'
AS
    IF @id is NULL OR @config is NULL BEGIN
        PRINT 'This stored procedure syntax is: EXEC [${owner}].[SP_evolved_config_create] (nvarchar)@id, (ntext)@config[, (nvarchar)@owner]'
        RETURN
    END

    INSERT INTO [${owner}].[evolved_configs](id, config, owner)
    VALUES (@id, @config, @owner)
GO

/* Stored Procedure: SP_evolved_config_retrieve
 *
 * Retrieves an evolved_config.
 *
 * Parameters:
 *
 *  (nvarchar) - config identifier
 *
 */
CREATE PROC [${owner}].[SP_evolved_config_retrieve]
    @id nvarchar(32) = NULL
AS
    IF @id is NULL BEGIN
        PRINT 'This stored procedure syntax is: EXEC [${owner}].[SP_evolved_config_retrieve] (nvarchar)@id'
        RETURN
    END

    SELECT config
    FROM [${owner}].[evolved_configs]
    WHERE id = @id
GO

/* Stored Procedure: SP_evolved_config_update
 *
 * Updates an evolved_config.
 *
 * Parameters:
 *
 *  (nvarchar) - config identifier
 *  (ntext)    - config
 *  (nvarchar) - owner (optional, defaults to "unknown")
 *
 */
CREATE PROC [${owner}].[SP_evolved_config_update]
    @id nvarchar(32) = NULL,
    @config ntext = NULL,
    @owner nvarchar(32) = 'unknown'
AS
    IF @id is NULL OR @config is NULL BEGIN
        PRINT 'This stored procedure syntax is: EXEC [${owner}].[SP_evolved_config_update] (nvarchar)@id (ntext)@config[, (nvarchar)@owner]'
        RETURN
    END

    UPDATE [${owner}].[evolved_configs]
    SET
        config = @config,
        dtUpd  = getdate(),
        owner  = @owner
    WHERE id = @id
GO

/* Stored Procedure: SP_evolved_config_delete
 *
 * Deletes an evolved_config.
 *
 * Parameters:
 *
 *  (nvarchar) - config identifier
 *
 */
CREATE PROC [${owner}].[SP_evolved_config_delete]
    @id nvarchar(32) = NULL
AS
    IF @id is NULL BEGIN
        PRINT 'This stored procedure syntax is: EXEC [${owner}].[SP_evolved_config_delete] (nvarchar)@id'
        RETURN
    END

    DELETE FROM [${owner}].[evolved_configs]
    WHERE id = @id
GO

/* Stored Procedure: SP_evolved_config_load
 *
 * Shortcut to CRUD retrieve.
 *
 * Parameters:
 *
 *  (nvarchar) - config identifier
 *
 */
CREATE PROC [${owner}].[SP_evolved_config_load]
    @id nvarchar(32) = NULL
AS
    IF @id is NULL BEGIN
        PRINT 'This stored procedure syntax is: EXEC [${owner}].[SP_evolved_config_load] (nvarchar)@id'
        RETURN
    END

    EXEC [${owner}].[SP_evolved_config_retrieve] @id
GO

/* Stored Procedure: SP_evolved_config_save
 *
 * Shortcut to CRUD create, update.
 *
 * Parameters:
 *
 *  (nvarchar) - config identifier
 *  (ntext)    - config
 *  (nvarchar) - owner (optional, defaults to "unknown")
 *
 */
CREATE PROC [${owner}].[SP_evolved_config_save]
    @id nvarchar(32) = NULL,
    @config ntext = NULL,
    @owner nvarchar(32) = 'unknown'
AS
    IF @id is NULL OR @config is NULL BEGIN
        PRINT 'This stored procedure syntax is: EXEC [${owner}].[SP_evolved_config_save] (nvarchar)@id (ntext)@config[, (nvarchar)@owner]'
        RETURN
    END

    EXEC [${owner}].[SP_evolved_config_retrieve] @id
    IF @@ROWCOUNT > 0
        BEGIN
            EXEC [${owner}].[SP_evolved_config_update] @id, @config, @owner
        END
    ELSE
        BEGIN
            EXEC [${owner}].[SP_evolved_config_create] @id, @config, @owner
        END
GO










/* Table: evolved_types
 *
 * Orderly definitions of type fields.
 *
 * Primary key:
 *
 *  (int) - PK_evolved_types_id
 *
 * Indexes:
 *
 *  (clustered)     - PK_evolved_types_id
 *
 * Defaults:
 *
 *  (smalldatetime) - DF_evolved_types_dtIns. Defaults to GETDATE()
 *  (smalldatetime) - DF_evolved_types_dtUpd. Defaults to GETDATE()
 *  (nvarchar)      - DF_evolved_types_owner. Defaults to "unknown"
 *
 */
CREATE TABLE [${owner}].[evolved_types] (
    [id] int IDENTITY(1, 1) NOT NULL CONSTRAINT PK_evolved_types_id PRIMARY KEY CLUSTERED,
    [name] nvarchar(32) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,
    [orderly] ntext COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
    [dtIns] smalldatetime NOT NULL CONSTRAINT DF_evolved_types_dtIns DEFAULT getdate(),
    [dtUpd] smalldatetime NOT NULL CONSTRAINT DF_evolved_types_dtUpd DEFAULT getdate(),
    [owner] nvarchar(32) NOT NULL CONSTRAINT DF_evolved_types_owner DEFAULT 'unknown'
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];
GO

/* Stored Procedure: SP_evolved_type_create
 *
 * Creates a new evolved_type.
 *
 * Parameters:
 *
 *  (nvarchar) - type identifier
 *  (ntext)    - orderly
 *  (nvarchar) - owner (optional, defaults to "unknown")
 *
 */
CREATE PROC [${owner}].[SP_evolved_type_create]
    @name nvarchar(32) = NULL,
    @orderly ntext = NULL,
    @owner nvarchar(32) = 'unknown'
AS
    IF @name is NULL BEGIN
        PRINT 'This stored procedure syntax is: EXEC [${owner}].[SP_evolved_type_create] (nvarchar)@name[, (ntext)@orderly, (nvarchar)@owner]'
        RETURN
    END

    INSERT INTO [${owner}].[evolved_types](name, orderly, owner)
    VALUES (@name, @orderly, @owner)
GO

/* Stored Procedure: SP_evolved_type_retrieve
 *
 * Retrieves an evolved_type.
 *
 * Parameters:
 *
 *  (nvarchar) - type identifier
 *
 */
CREATE PROC [${owner}].[SP_evolved_type_retrieve]
    @name nvarchar(32) = NULL
AS
    IF @name is NULL BEGIN
        PRINT 'This stored procedure syntax is: EXEC [${owner}].[SP_evolved_type_retrieve] (nvarchar)@name'
        RETURN
    END

    SELECT id, orderly
    FROM [${owner}].[evolved_types]
    WHERE name = @name
GO

/* Stored Procedure: SP_evolved_type_update
 *
 * Updates an evolved_type.
 *
 * Parameters:
 *
 *  (nvarchar) - type identifier
 *  (ntext)    - orderly
 *  (nvarchar) - owner (optional, defaults to "unknown")
 *
 */
CREATE PROC [${owner}].[SP_evolved_type_update]
    @name nvarchar(32) = NULL,
    @orderly ntext = NULL,
    @owner nvarchar(32) = 'unknown'
AS
    IF @name is NULL BEGIN
        PRINT 'This stored procedure syntax is: EXEC [${owner}].[SP_evolved_type_update] (nvarchar)@name[, (ntext)@orderly, (nvarchar)@owner]'
        RETURN
    END

    UPDATE [${owner}].[evolved_types]
    SET
        orderly = @orderly,
        dtUpd   = getdate(),
        owner   = @owner
    WHERE name = @name
GO

/* Stored Procedure: SP_evolved_type_delete
 *
 * Deletes an evolved_type.
 *
 * Parameters:
 *
 *  (nvarchar) - type identifier
 *
 */
CREATE PROC [${owner}].[SP_evolved_type_delete]
    @name nvarchar(32) = NULL
AS
    IF @name is NULL BEGIN
        PRINT 'This stored procedure syntax is: EXEC [${owner}].[SP_evolved_type_delete] (nvarchar)@name'
        RETURN
    END

    DELETE FROM [${owner}].[evolved_types] WHERE name = @name
GO

/* Stored Procedure: SP_evolved_type_load
 *
 * Shortcut to CRUD retrieve.
 *
 * Parameters:
 *
 *  (nvarchar) - type identifier
 *
 */
CREATE PROC [${owner}].[SP_evolved_type_load]
    @name nvarchar(32) = NULL
AS
    IF @name is NULL BEGIN
        PRINT 'This stored procedure syntax is: EXEC [${owner}].[SP_evolved_type_load] (nvarchar)@name'
        RETURN
    END

    EXEC [${owner}].[SP_evolved_type_retrieve] @name
GO

/* Stored Procedure: SP_evolved_type_save
 *
 * Shortcut to CRUD create, update.
 *
 * Parameters:
 *
 *  (nvarchar) - type identifier
 *  (ntext)    - orderly
 *  (nvarchar) - owner (optional, defaults to "unknown")
 *
 */
CREATE PROC [${owner}].[SP_evolved_type_save]
    @name nvarchar(32) = NULL,
    @orderly ntext = NULL,
    @owner nvarchar(32) = 'unknown'
AS
    IF @name is NULL BEGIN
        PRINT 'This stored procedure syntax is: EXEC [${owner}].[SP_evolved_type_save] (nvarchar)@name[, (ntext)@orderly, (nvarchar)@owner]'
        RETURN
    END

    EXEC [${owner}].[SP_evolved_type_retrieve] @name
    IF @@ROWCOUNT > 0
        BEGIN
            EXEC [${owner}].[SP_evolved_type_update] @name, @orderly, @owner
        END
    ELSE
        BEGIN
            EXEC [${owner}].[SP_evolved_type_create] @name, @orderly, @owner
        END
GO





/* Table: evolved_entities
 *
 * Entities registry.
 *
 * Primary key:
 *
 *  (int) - PK_evolved_entities_id
 *
 * Foreign keys:
 *
 *  (int) - FK_evolved_entities_idType
 *
 * Indexes:
 *
 *  (clustered)     - PK_evolved_entities_id
 *
 * Defaults:
 *
 *  (smalldatetime) - DF_evolved_entities_dtIns. Defaults to GETDATE()
 *  (smalldatetime) - DF_evolved_entities_dtUpd. Defaults to GETDATE()
 *  (nvarchar)      - DF_evolved_entities_owner. Defaults to "unknown"
 *
 */
CREATE TABLE [${owner}].[evolved_entities] (
    [id] int IDENTITY(1, 1) NOT NULL CONSTRAINT PK_evolved_entities_id PRIMARY KEY CLUSTERED,
    [idType] int NOT NULL CONSTRAINT FK_evolved_entities_idType FOREIGN KEY REFERENCES [${owner}].[evolved_types](id) ON DELETE CASCADE,
    [dtIns] smalldatetime NOT NULL CONSTRAINT DF_evolved_entities_dtIns DEFAULT getdate(),
    [dtUpd] smalldatetime NOT NULL CONSTRAINT DF_evolved_entities_dtUpd DEFAULT getdate(),
    [owner] nvarchar(32) NOT NULL CONSTRAINT DF_evolved_entities_owner DEFAULT 'unknown'
) ON [PRIMARY]
GO

/* Table: evolved_entity_string
 *
 * Entity string value.
 *
 * Primary key:
 *
 *  (int) - PK_evolved_entity_string_id
 *
 * Foreign keys:
 *
 *  (int) - FK_evolved_entity_string_e
 *
 * Indexes:
 *
 *  (clustered)    - PK_evolved_entity_string_id
 *  (nonclustered) - UN_evolved_entity_string_e_a
 *  (nonclustered) - IN_evolved_entity_string_a_v
 *
 * Defaults:
 *
 *  (smalldatetime) - DF_evolved_entity_string_dtIns. Defaults to GETDATE()
 *  (smalldatetime) - DF_evolved_entity_string_dtUpd. Defaults to GETDATE()
 *  (nvarchar)      - DF_evolved_entity_string_owner. Defaults to "unknown"
 *
 */
CREATE TABLE [${owner}].[evolved_entity_string] (
    [id] int IDENTITY(1, 1) NOT NULL CONSTRAINT PK_evolved_entity_string_id PRIMARY KEY CLUSTERED,
    [e] int NOT NULL CONSTRAINT FK_evolved_entity_string_e FOREIGN KEY REFERENCES [${owner}].[evolved_entities](id) ON DELETE CASCADE,
    [a] nvarchar(32) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,
    [v] nvarchar(256) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,
    [dtIns] smalldatetime NOT NULL CONSTRAINT DF_evolved_entity_string_dtIns DEFAULT getdate(),
    [dtUpd] smalldatetime NOT NULL CONSTRAINT DF_evolved_entity_string_dtUpd DEFAULT getdate(),
    [owner] nvarchar(32) NOT NULL CONSTRAINT DF_evolved_entity_string_owner DEFAULT 'unknown'
) ON [PRIMARY]
GO

CREATE UNIQUE NONCLUSTERED INDEX UN_evolved_entity_string_e_a ON [${owner}].[evolved_entity_string](e, a) ON [PRIMARY];
CREATE NONCLUSTERED INDEX IN_evolved_entity_string_a_v ON [${owner}].[evolved_entity_string](a, v) ON [PRIMARY];
GO

/* Table: evolved_entity_text
 *
 * Entity huge string value.
 *
 * Primary key:
 *
 *  (int) - PK_evolved_entity_text_id
 *
 * Foreign keys:
 *
 *  (int) - FK_evolved_entity_text_e
 *
 * Indexes:
 *
 *  (clustered)    - PK_evolved_entity_text_id
 *  (nonclustered) - UN_evolved_entity_text_e_a
 *
 * Defaults:
 *
 *  (smalldatetime) - DF_evolved_entity_text_dtIns. Defaults to GETDATE()
 *  (smalldatetime) - DF_evolved_entity_text_dtUpd. Defaults to GETDATE()
 *  (nvarchar)      - DF_evolved_entity_text_owner. Defaults to "unknown"
 *
 */
CREATE TABLE [${owner}].[evolved_entity_text] (
    [id] int IDENTITY(1, 1) NOT NULL CONSTRAINT PK_evolved_entity_text_id PRIMARY KEY CLUSTERED,
    [e] int NOT NULL CONSTRAINT FK_evolved_entity_text_e FOREIGN KEY REFERENCES [${owner}].[evolved_entities](id) ON DELETE CASCADE,
    [a] nvarchar(32) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,
    [v] ntext COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,
    [dtIns] smalldatetime NOT NULL CONSTRAINT DF_evolved_entity_text_dtIns DEFAULT getdate(),
    [dtUpd] smalldatetime NOT NULL CONSTRAINT DF_evolved_entity_text_dtUpd DEFAULT getdate(),
    [owner] nvarchar(32) NOT NULL CONSTRAINT DF_evolved_entity_text_owner DEFAULT 'unknown'
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE UNIQUE NONCLUSTERED INDEX UN_evolved_entity_text_e_a ON [${owner}].[evolved_entity_text](e, a) ON [PRIMARY];
GO

/* Table: evolved_entity_integer
 *
 * Entity integer value.
 *
 * Primary key:
 *
 *  (int) - PK_evolved_entity_integer_id
 *
 * Foreign keys:
 *
 *  (int) - FK_evolved_entity_integer_e
 *
 * Indexes:
 *
 *  (clustered)    - PK_evolved_entity_integer_id
 *  (nonclustered) - UN_evolved_entity_integer_e_a
 *  (nonclustered) - IN_evolved_entity_integer_a_v
 *
 * Defaults:
 *
 *  (smalldatetime) - DF_evolved_entity_integer_dtIns. Defaults to GETDATE()
 *  (smalldatetime) - DF_evolved_entity_integer_dtUpd. Defaults to GETDATE()
 *  (nvarchar)      - DF_evolved_entity_integer_owner. Defaults to "unknown"
 *
 */
CREATE TABLE [${owner}].[evolved_entity_integer] (
    [id] int IDENTITY(1, 1) NOT NULL CONSTRAINT PK_evolved_entity_integer_id PRIMARY KEY CLUSTERED,
    [e] int NOT NULL CONSTRAINT FK_evolved_entity_integer_e FOREIGN KEY REFERENCES [${owner}].[evolved_entities](id) ON DELETE CASCADE,
    [a] nvarchar(32) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,
    [v] int NOT NULL,
    [dtIns] smalldatetime NOT NULL CONSTRAINT DF_evolved_entity_integer_dtIns DEFAULT getdate(),
    [dtUpd] smalldatetime NOT NULL CONSTRAINT DF_evolved_entity_integer_dtUpd DEFAULT getdate(),
    [owner] nvarchar(32) NOT NULL CONSTRAINT DF_evolved_entity_integer_owner DEFAULT 'unknown'
) ON [PRIMARY]
GO

CREATE UNIQUE NONCLUSTERED INDEX UN_evolved_entity_integer_e_a ON [${owner}].[evolved_entity_integer](e, a) ON [PRIMARY];
CREATE NONCLUSTERED INDEX IN_evolved_entity_integer_a_v ON [${owner}].[evolved_entity_integer](a, v) ON [PRIMARY];
GO

/* Table: evolved_entity_number
 *
 * Entity number value.
 *
 * Primary key:
 *
 *  (int) - PK_evolved_entity_number_id
 *
 * Foreign keys:
 *
 *  (int) - FK_evolved_entity_number_e
 *
 * Indexes:
 *
 *  (clustered)    - PK_evolved_entity_number_id
 *  (nonclustered) - UN_evolved_entity_number_e_a
 *  (nonclustered) - IN_evolved_entity_number_a_v
 *
 * Defaults:
 *
 *  (smalldatetime) - DF_evolved_entity_number_dtIns. Defaults to GETDATE()
 *  (smalldatetime) - DF_evolved_entity_number_dtUpd. Defaults to GETDATE()
 *  (nvarchar)      - DF_evolved_entity_number_owner. Defaults to "unknown"
 *
 */
CREATE TABLE [${owner}].[evolved_entity_number] (
    [id] int IDENTITY(1, 1) NOT NULL CONSTRAINT PK_evolved_entity_number_id PRIMARY KEY CLUSTERED,
    [e] int NOT NULL CONSTRAINT FK_evolved_entity_number_e FOREIGN KEY REFERENCES [${owner}].[evolved_entities](id) ON DELETE CASCADE,
    [a] nvarchar(32) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,
    [v] decimal(18, 4) NOT NULL,
    [dtIns] smalldatetime NOT NULL CONSTRAINT DF_evolved_entity_number_dtIns DEFAULT getdate(),
    [dtUpd] smalldatetime NOT NULL CONSTRAINT DF_evolved_entity_number_dtUpd DEFAULT getdate(),
    [owner] nvarchar(32) NOT NULL CONSTRAINT DF_evolved_entity_number_owner DEFAULT 'unknown'
) ON [PRIMARY]
GO

CREATE UNIQUE NONCLUSTERED INDEX UN_evolved_entity_number_e_a ON [${owner}].[evolved_entity_number](e, a) ON [PRIMARY];
CREATE NONCLUSTERED INDEX IN_evolved_entity_number_a_v ON [${owner}].[evolved_entity_number](a, v) ON [PRIMARY];
GO

/* Table: evolved_entity_boolean
 *
 * Entity boolean value.
 *
 * Primary key:
 *
 *  (int) - PK_evolved_entity_boolean_id
 *
 * Foreign keys:
 *
 *  (int) - FK_evolved_entity_boolean_e
 *
 * Indexes:
 *
 *  (clustered)    - PK_evolved_entity_boolean_id
 *  (nonclustered) - UN_evolved_entity_boolean_e_a
 *  (nonclustered) - IN_evolved_entity_boolean_a_v
 *
 * Defaults:
 *
 *  (smalldatetime) - DF_evolved_entity_boolean_dtIns. Defaults to GETDATE()
 *  (smalldatetime) - DF_evolved_entity_boolean_dtUpd. Defaults to GETDATE()
 *  (nvarchar)      - DF_evolved_entity_boolean_owner. Defaults to "unknown"
 *
 */
CREATE TABLE [${owner}].[evolved_entity_boolean] (
    [id] int IDENTITY(1, 1) NOT NULL CONSTRAINT PK_evolved_entity_boolean_id PRIMARY KEY CLUSTERED,
    [e] int NOT NULL CONSTRAINT FK_evolved_entity_boolean_e FOREIGN KEY REFERENCES [${owner}].[evolved_entities](id) ON DELETE CASCADE,
    [a] nvarchar(32) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,
    [v] bit NOT NULL,
    [dtIns] smalldatetime NOT NULL CONSTRAINT DF_evolved_entity_boolean_dtIns DEFAULT getdate(),
    [dtUpd] smalldatetime NOT NULL CONSTRAINT DF_evolved_entity_boolean_dtUpd DEFAULT getdate(),
    [owner] nvarchar(32) NOT NULL CONSTRAINT DF_evolved_entity_boolean_owner DEFAULT 'unknown'
) ON [PRIMARY]
GO

CREATE UNIQUE NONCLUSTERED INDEX UN_evolved_entity_boolean_e_a ON [${owner}].[evolved_entity_boolean](e, a) ON [PRIMARY];
CREATE NONCLUSTERED INDEX IN_evolved_entity_boolean_a_v ON [${owner}].[evolved_entity_boolean](a, v) ON [PRIMARY];
GO

/* Table: evolved_entity_datetime
 *
 * Entity datetime value.
 *
 * Primary key:
 *
 *  (int) - PK_evolved_entity_datetime_id
 *
 * Foreign keys:
 *
 *  (int) - FK_evolved_entity_datetime_e
 *
 * Indexes:
 *
 *  (clustered)    - PK_evolved_entity_datetime_id
 *  (nonclustered) - UN_evolved_entity_datetime_e_a
 *  (nonclustered) - IN_evolved_entity_datetime_a_v
 *
 * Defaults:
 *
 *  (smalldatetime) - DF_evolved_entity_datetime_dtIns. Defaults to GETDATE()
 *  (smalldatetime) - DF_evolved_entity_datetime_dtUpd. Defaults to GETDATE()
 *  (nvarchar)      - DF_evolved_entity_datetime_owner. Defaults to "unknown"
 *
 */
CREATE TABLE [${owner}].[evolved_entity_datetime] (
    [id] int IDENTITY(1, 1) NOT NULL CONSTRAINT PK_evolved_entity_datetime_id PRIMARY KEY CLUSTERED,
    [e] int NOT NULL CONSTRAINT FK_evolved_entity_datetime_e FOREIGN KEY REFERENCES [${owner}].[evolved_entities](id) ON DELETE CASCADE,
    [a] nvarchar(32) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,
    [v] smalldatetime NOT NULL,
    [dtIns] smalldatetime NOT NULL CONSTRAINT DF_evolved_entity_datetime_dtIns DEFAULT getdate(),
    [dtUpd] smalldatetime NOT NULL CONSTRAINT DF_evolved_entity_datetime_dtUpd DEFAULT getdate(),
    [owner] nvarchar(32) NOT NULL CONSTRAINT DF_evolved_entity_datetime_owner DEFAULT 'unknown'
) ON [PRIMARY]
GO

CREATE UNIQUE NONCLUSTERED INDEX UN_evolved_entity_datetime_e_a ON [${owner}].[evolved_entity_datetime](e, a) ON [PRIMARY];
CREATE NONCLUSTERED INDEX IN_evolved_entity_datetime_a_v ON [${owner}].[evolved_entity_datetime](a, v) ON [PRIMARY];
GO

/* Table: evolved_entity_optionals
 *
 * Entity optional values.
 *
 * Primary key:
 *
 *  (int) - PK_evolved_entity_optionals_id
 *
 * Foreign keys:
 *
 *  (int) - FK_evolved_entity_optionals_e
 *
 * Indexes:
 *
 *  (clustered)    - PK_evolved_entity_optionals_id
 *  (nonclustered) - UN_evolved_entity_optionals_e_a
 *
 * Defaults:
 *
 *  (smalldatetime) - DF_evolved_entity_optionals_dtIns. Defaults to GETDATE()
 *  (smalldatetime) - DF_evolved_entity_optionals_dtUpd. Defaults to GETDATE()
 *  (nvarchar)      - DF_evolved_entity_optionals_owner. Defaults to "unknown"
 *
 */
CREATE TABLE [${owner}].[evolved_entity_optionals] (
    [id] int IDENTITY(1, 1) NOT NULL CONSTRAINT PK_evolved_entity_optionals_id PRIMARY KEY CLUSTERED,
    [e] int NOT NULL CONSTRAINT FK_evolved_entity_optionals_e FOREIGN KEY REFERENCES [${owner}].[evolved_entities](id) ON DELETE CASCADE,
    [a] nvarchar(32) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,
    [v] ntext COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,
    [dtIns] smalldatetime NOT NULL CONSTRAINT DF_evolved_entity_optionals_dtIns DEFAULT getdate(),
    [dtUpd] smalldatetime NOT NULL CONSTRAINT DF_evolved_entity_optionals_dtUpd DEFAULT getdate(),
    [owner] nvarchar(32) NOT NULL CONSTRAINT DF_evolved_entity_optionals_owner DEFAULT 'unknown'
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE UNIQUE NONCLUSTERED INDEX UN_evolved_entity_optionals_e_a ON [${owner}].[evolved_entity_optionals](e, a) ON [PRIMARY];
GO










/* Table: evolved_sets
 *
 * Set definitions.
 *
 * Primary key:
 *
 *  (int) - PK_evolved_sets_id
 *
 * Indexes:
 *
 *  (clustered)    - PK_evolved_sets_id
 *  (nonclustered) - UN_evolved_sets_name
 *
 * Defaults:
 *
 *  (smalldatetime) - DF_evolved_sets_dtIns. Defaults to GETDATE()
 *  (smalldatetime) - DF_evolved_sets_dtUpd. Defaults to GETDATE()
 *  (nvarchar)      - DF_evolved_sets_owner. Defaults to "unknown"
 *
 */
CREATE TABLE [${owner}].[evolved_sets] (
    [id] int IDENTITY(1, 1) NOT NULL CONSTRAINT PK_evolved_sets_id PRIMARY KEY CLUSTERED,
    [name] nvarchar(32) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL CONSTRAINT UN_evolved_sets_name UNIQUE NONCLUSTERED, /* Addresses, Identities etc */
    [dtIns] smalldatetime NOT NULL CONSTRAINT DF_evolved_sets_dtIns DEFAULT getdate(),
    [dtUpd] smalldatetime NOT NULL CONSTRAINT DF_evolved_sets_dtUpd DEFAULT getdate(),
    [owner] nvarchar(32) NOT NULL CONSTRAINT DF_evolved_sets_owner DEFAULT 'unknown'
) ON [PRIMARY];
GO

/* Table: evolved_set_members
 *
 * Members of sets.
 *
 * Primary key:
 *
 *  (int) - PK_evolved_set_members_id
 *
 * Foreign keys:
 *
 *  (int) - FK_evolved_set_members_idSet
 *  (int) - FK_evolved_set_members_idMember
 *
 * Indexes:
 *
 *  (clustered) - PK_evolved_set_members_id
 *
 * Defaults:
 *
 *  (smalldatetime) - DF_evolved_set_members_dtIns. Defaults to GETDATE()
 *  (smalldatetime) - DF_evolved_set_members_dtUpd. Defaults to GETDATE()
 *  (nvarchar)      - DF_evolved_set_members_owner. Defaults to "unknown"
 *
 */
CREATE TABLE [${owner}].[evolved_set_members] (
    [id] int IDENTITY(1, 1) NOT NULL CONSTRAINT PK_evolved_set_members_id PRIMARY KEY CLUSTERED,
    [idSet] int NOT NULL CONSTRAINT FK_evolved_set_members_idSet FOREIGN KEY REFERENCES [${owner}].[evolved_sets](id) ON DELETE CASCADE,
    [idMember] int NOT NULL CONSTRAINT FK_evolved_set_members_idMember FOREIGN KEY REFERENCES [${owner}].[evolved_entities](id) ON DELETE CASCADE,
    [dtIns] smalldatetime NOT NULL CONSTRAINT DF_evolved_set_members_dtIns DEFAULT getdate(),
    [dtUpd] smalldatetime NOT NULL CONSTRAINT DF_evolved_set_members_dtUpd DEFAULT getdate(),
    [owner] nvarchar(32) NOT NULL CONSTRAINT DF_evolved_set_members_owner DEFAULT 'unknown'
) ON [PRIMARY];
GO

/* Stored Procedure: SP_evolved_set_create
 *
 * Creates a new evolved_set.
 *
 * Parameters:
 *
 *  (nvarchar) - set name
 *  (nvarchar) - owner (optional, defaults to "unknown")
 *
 */
CREATE PROC [${owner}].[SP_evolved_set_create]
    @name nvarchar(32) = NULL,
    @owner nvarchar(32) = 'unknown'
AS
    IF @name is NULL BEGIN
        PRINT 'This stored procedure syntax is: EXEC [${owner}].[SP_evolved_set_create] (type)@name[, (nvarchar)@owner]'
        RETURN
    END

    INSERT INTO [${owner}].[evolved_sets](name, owner)
    VALUES (@name, @owner)
GO

/* Stored Procedure: SP_evolved_set_retrieve
 *
 * Retrieves the id of an evolved_set.
 *
 * Parameters:
 *
 *  (nvarchar) - set name
 *
 */
CREATE PROC [${owner}].[SP_evolved_set_retrieve]
    @name nvarchar(32) = NULL
AS
    IF @name is NULL BEGIN
        PRINT 'This stored procedure syntax is: EXEC [${owner}].[SP_evolved_set_retrieve] (nvarchar)@name'
        RETURN
    END

    SELECT idMember
    FROM [${owner}].[evolved_sets]
        INNER JOIN [${owner}].[evolved_set_members] ON [${owner}].[evolved_sets].[id] = [${owner}].[evolved_set_members].[idSet]
    WHERE name = @name
GO

/* Stored Procedure: SP_evolved_set_update
 *
 * Updates an evolved_set name.
 *
 * Parameters:
 *
 *  (int)      - set identifier
 *  (nvarchar) - set name
 *  (nvarchar) - owner (optional, defaults to "unknown")
 *
 */
CREATE PROC [${owner}].[SP_evolved_set_update]
    @old nvarchar(32) = NULL,
    @new nvarchar(32) = NULL,
    @owner nvarchar(32) = 'unknown'
AS
    IF @old is NULL OR @new is NULL BEGIN
        PRINT 'This stored procedure syntax is: EXEC [${owner}].[SP_evolved_set_update] (nvarchar)@old, (nvarchar)@new[, (nvarchar)@owner]'
        RETURN
    END

    UPDATE [${owner}].[evolved_sets]
    SET
        name = @new,
        dtUpd = getdate(),
        owner = @owner
    WHERE name = @old
GO

/* Stored Procedure: SP_evolved_set_delete
 *
 * Deletes an evolved_set.
 *
 * Parameters:
 *
 *  (nvarchar) - set name
 *
 */
CREATE PROC [${owner}].[SP_evolved_set_delete]
    @name nvarchar(32) = NULL
AS
    IF @name is NULL BEGIN
        PRINT 'This stored procedure syntax is: EXEC [${owner}].[SP_evolved_set_delete] (nvarchar)@name'
        RETURN
    END

    DELETE FROM [${owner}].[evolved_sets]
    WHERE name = @name
GO

/* Stored Procedure: SP_evolved_set_assign
 *
 * Assigns an entity instance to a set.
 *
 * Parameters:
 *
 *  (nvarchar) - set name
 *  (int)      - entity id
 *  (nvarchar) - owner (optional, defaults to "unknown")
 *
 */
CREATE PROC [${owner}].[SP_evolved_set_assign]
    @set_name nvarchar(32) = NULL,
    @entity_id int = NULL,
    @owner nvarchar(32) = 'unknown'
AS
    IF @set_name is NULL OR @entity_id is NULL BEGIN
        PRINT 'This stored procedure syntax is: EXEC [${owner}].[SP_evolved_set_assign] (nvarchar)@set_name, (int)@entity_id[, (nvarchar)@owner]'
        RETURN
    END

    DECLARE @set_id int;
    SET @set_id = (SELECT id FROM [${owner}].[evolved_sets] WHERE name = @set_name);

    IF @set_id is NOT NULL BEGIN
        INSERT INTO [${owner}].[evolved_set_members](idSet, idMember, owner)
        VALUES (@set_id, @entity_id, @owner)
    END
GO

/* Stored Procedure: SP_evolved_set_unassign
 *
 * Unassigns an entity instance from a set.
 *
 * Parameters:
 *
 *  (nvarchar) - set name
 *  (nvarchar) - entity name
 *  (int)      - entity id
 *
 */
CREATE PROC [${owner}].[SP_evolved_set_unassign]
    @set_name nvarchar(32) = NULL,
    @entity_id int = NULL
AS
    IF @set_name is NULL OR @entity_id is NULL BEGIN
        PRINT 'This stored procedure syntax is: EXEC [${owner}].[SP_evolved_set_unassign] (nvarchar)@set_name, (int)@entity_id'
        RETURN
    END

    DECLARE @set_id int;
    SET @set_id = (SELECT id FROM [${owner}].[evolved_sets] WHERE name = @set_name);

    IF @set_id is NOT NULL BEGIN
        DELETE FROM [${owner}].[evolved_set_members]
        WHERE (idSet = @set_id) AND (idMember = @entity_id)
    END
GO