
CREATE TABLE products(
    id BIGINT IDENTITY (1, 1) NOT NULL PRIMARY KEY,
    crm_id BIGINT REFERENCES crms(id),
    project_id BIGINT NOT NULL REFERENCES projects(id),
    icon nvarchar(2000),
    type nvarchar(200) NOT NULL,
    name nvarchar(500) NOT NULL,
    description_for_agents nvarchar(4000) NOT NULL ,
    description_for_clients nvarchar(4000) NOT NULL,
    restriction bit not null,

    validity_period nvarchar(200) NOT NULL,
    validity_period_type nvarchar(200) NOT NULL ,
    validity_period_duration int,

    price nvarchar(200) NOT NULL ,
    price_rur bigint NOT NULL,
    price_duration_type nvarchar(200) NOT NULL ,

    periodic_payment nvarchar(200) NOT NULL ,
    periodic_payment_price bigint,
    periodic_payment_type nvarchar(200) NOT NULL ,

    deleted bit NOT NULL ,

    created_at DATETIMEOFFSET NOT NULL ,
    updated_at DATETIMEOFFSET NOT NULL ,
    deleted_at DATETIMEOFFSET
);

CREATE INDEX products_project_id_ix
    ON products (project_id);
CREATE UNIQUE INDEX products_project_id_name_nondeleted_uniq
    ON products (project_id, name)
    WHERE deleted = 0;


CREATE TABLE product_linked_products(
    product_id BIGINT NOT NULL references products(id) ,
    linked_product_id BIGINT NOT NULL references products(id)
);

CREATE TABLE client_products(
    client_id BIGINT NOT NULL references clients(id),
    product_id BIGINT NOT NULL references products(id),
    ticket_id BIGINT NOT NULL references tickets(id),
    bought_at DATETIMEOFFSET NOT NULL,
    expires_at DATETIMEOFFSET
);

CREATE UNIQUE INDEX client_products_client_id_product_id on client_products(client_id, product_id);
