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

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

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

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

    deleted boolean NOT NULL ,

    created_at timestamp WITH TIME ZONE NOT NULL ,
    updated_at timestamp WITH TIME ZONE NOT NULL ,
    deleted_at timestamp WITH TIME ZONE
);

CREATE SEQUENCE products_id_seq;

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


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

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 timestamp WITH TIME ZONE NOT NULL,
    expires_at timestamp WITH TIME ZONE
);

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