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

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

    price VARCHAR2(200) NOT NULL ,
    price_rur NUMBER(19) NOT NULL,
    price_duration_type VARCHAR2(200) NOT NULL ,

    periodic_payment VARCHAR2(200) NOT NULL ,
    periodic_payment_price NUMBER(19),
    periodic_payment_type VARCHAR2(200) NOT NULL ,

    deleted NUMBER(1) 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 (CASE WHEN deleted = 0 THEN project_id END, CASE WHEN deleted = 0 THEN lower(name) END);



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

CREATE TABLE client_products(
    client_id NUMBER(19) NOT NULL references clients(id),
    product_id NUMBER(19) NOT NULL references products(id),
    ticket_id NUMBER(19) 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);
