---

PRAGMA foreign_keys = ON;

DROP TABLE IF EXISTS delivery_item;
DROP TABLE IF EXISTS delivery;
DROP TABLE IF EXISTS product;
DROP TABLE IF EXISTS supplier;

CREATE TABLE supplier (
    supplier_id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    contact_person TEXT,
    phone TEXT,
    email TEXT,
    address TEXT,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT uq_supplier_name UNIQUE (name),
    CONSTRAINT uq_supplier_email UNIQUE (email)
);

CREATE TABLE product (
    product_id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    category TEXT,
    unit TEXT NOT NULL DEFAULT 'шт',
    price NUMERIC NOT NULL DEFAULT 0,
    quantity_in_stock INTEGER NOT NULL DEFAULT 0,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT uq_product_name UNIQUE (name),
    CONSTRAINT chk_product_price CHECK (price >= 0),
    CONSTRAINT chk_product_quantity CHECK (quantity_in_stock >= 0)
);

CREATE TABLE delivery (
    delivery_id INTEGER PRIMARY KEY AUTOINCREMENT,
    supplier_id INTEGER NOT NULL,
    delivery_date TEXT NOT NULL DEFAULT CURRENT_DATE,
    document_number TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'created',
    comment TEXT,

    CONSTRAINT uq_delivery_document_number UNIQUE (document_number),

    CONSTRAINT fk_delivery_supplier
        FOREIGN KEY (supplier_id)
        REFERENCES supplier (supplier_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,

    CONSTRAINT chk_delivery_status
        CHECK (status IN ('created', 'received', 'cancelled'))
);

CREATE TABLE delivery_item (
    delivery_item_id INTEGER PRIMARY KEY AUTOINCREMENT,
    delivery_id INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    quantity INTEGER NOT NULL,
    purchase_price NUMERIC NOT NULL,

    CONSTRAINT uq_delivery_product UNIQUE (delivery_id, product_id),

    CONSTRAINT fk_delivery_item_delivery
        FOREIGN KEY (delivery_id)
        REFERENCES delivery (delivery_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,

    CONSTRAINT fk_delivery_item_product
        FOREIGN KEY (product_id)
        REFERENCES product (product_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,

    CONSTRAINT chk_delivery_item_quantity CHECK (quantity > 0),
    CONSTRAINT chk_delivery_item_purchase_price CHECK (purchase_price >= 0)
);

CREATE INDEX idx_delivery_supplier_id
ON delivery (supplier_id);

CREATE INDEX idx_delivery_delivery_date
ON delivery (delivery_date);

CREATE INDEX idx_delivery_item_delivery_id
ON delivery_item (delivery_id);

CREATE INDEX idx_delivery_item_product_id
ON delivery_item (product_id);

CREATE INDEX idx_product_name
ON product (name);

CREATE INDEX idx_supplier_name
ON supplier (name);