-- SupplyFlow ERP sxemi (SQLite + MySQL uyğun). {PK} = avtomatik artan əsas açar.

CREATE TABLE settings (
    skey VARCHAR(100) PRIMARY KEY,
    svalue TEXT
);

CREATE TABLE sequences (
    prefix VARCHAR(20) NOT NULL,
    yr INT NOT NULL,
    last_no INT NOT NULL DEFAULT 0,
    PRIMARY KEY (prefix, yr)
);

CREATE TABLE departments (
    id {PK},
    name VARCHAR(150) NOT NULL,
    head_user_id INT NULL,
    budget_annual DECIMAL(14,2) NOT NULL DEFAULT 0,
    active INT NOT NULL DEFAULT 1
);

CREATE TABLE categories (
    id {PK},
    name VARCHAR(150) NOT NULL,
    route_department_id INT NULL,
    active INT NOT NULL DEFAULT 1
);

CREATE TABLE suppliers (
    id {PK},
    name VARCHAR(200) NOT NULL,
    contact_person VARCHAR(150) NULL,
    email VARCHAR(190) NULL,
    phone VARCHAR(60) NULL,
    address VARCHAR(255) NULL,
    rating DECIMAL(3,1) NOT NULL DEFAULT 3.0,
    active INT NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL
);

CREATE TABLE users (
    id {PK},
    name VARCHAR(150) NOT NULL,
    email VARCHAR(190) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    role VARCHAR(30) NOT NULL,
    department_id INT NULL,
    supplier_id INT NULL,
    phone VARCHAR(60) NULL,
    active INT NOT NULL DEFAULT 1,
    last_login_at DATETIME NULL,
    created_at DATETIME NOT NULL
);

CREATE TABLE items (
    id {PK},
    sku VARCHAR(50) NOT NULL UNIQUE,
    name VARCHAR(200) NOT NULL,
    category_id INT NULL,
    unit VARCHAR(20) NOT NULL DEFAULT 'ədəd',
    barcode VARCHAR(50) NULL UNIQUE,
    min_stock DECIMAL(12,2) NOT NULL DEFAULT 0,
    qty DECIMAL(12,2) NOT NULL DEFAULT 0,
    avg_cost DECIMAL(14,2) NOT NULL DEFAULT 0,
    last_price DECIMAL(14,2) NOT NULL DEFAULT 0,
    location VARCHAR(50) NULL,
    active INT NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL
);

CREATE TABLE stock_movements (
    id {PK},
    item_id INT NOT NULL,
    delta DECIMAL(12,2) NOT NULL,
    balance_after DECIMAL(12,2) NOT NULL,
    unit_cost DECIMAL(14,2) NOT NULL DEFAULT 0,
    ref_type VARCHAR(20) NOT NULL,
    ref_id INT NULL,
    department_id INT NULL,
    category_id INT NULL,
    user_id INT NULL,
    note VARCHAR(255) NULL,
    created_at DATETIME NOT NULL
);

CREATE TABLE requests (
    id {PK},
    number VARCHAR(30) NOT NULL UNIQUE,
    requester_id INT NOT NULL,
    department_id INT NULL,
    category_id INT NULL,
    urgency VARCHAR(10) NOT NULL DEFAULT 'normal',
    status VARCHAR(20) NOT NULL DEFAULT 'draft',
    note TEXT NULL,
    est_total DECIMAL(14,2) NOT NULL DEFAULT 0,
    over_budget INT NOT NULL DEFAULT 0,
    auto_generated INT NOT NULL DEFAULT 0,
    submitted_at DATETIME NULL,
    approved_at DATETIME NULL,
    closed_at DATETIME NULL,
    created_at DATETIME NOT NULL
);

CREATE TABLE request_items (
    id {PK},
    request_id INT NOT NULL,
    item_id INT NULL,
    name VARCHAR(200) NOT NULL,
    unit VARCHAR(20) NOT NULL DEFAULT 'ədəd',
    qty DECIMAL(12,2) NOT NULL,
    est_price DECIMAL(14,2) NOT NULL DEFAULT 0,
    note VARCHAR(255) NULL,
    issued_qty DECIMAL(12,2) NOT NULL DEFAULT 0
);

CREATE TABLE approvals (
    id {PK},
    request_id INT NOT NULL,
    step_no INT NOT NULL,
    role VARCHAR(30) NOT NULL,
    department_id INT NULL,
    label VARCHAR(200) NOT NULL,
    status VARCHAR(12) NOT NULL DEFAULT 'pending',
    approver_id INT NULL,
    approver_name VARCHAR(150) NULL,
    comment VARCHAR(500) NULL,
    decided_at DATETIME NULL
);

CREATE TABLE issue_docs (
    id {PK},
    number VARCHAR(30) NOT NULL UNIQUE,
    request_id INT NOT NULL,
    issued_by INT NOT NULL,
    received_by VARCHAR(150) NULL,
    note VARCHAR(255) NULL,
    total_cost DECIMAL(14,2) NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL
);

CREATE TABLE issue_items (
    id {PK},
    issue_id INT NOT NULL,
    request_item_id INT NULL,
    item_id INT NOT NULL,
    qty DECIMAL(12,2) NOT NULL,
    unit_cost DECIMAL(14,2) NOT NULL DEFAULT 0
);

CREATE TABLE receipts (
    id {PK},
    number VARCHAR(30) NOT NULL UNIQUE,
    supplier_id INT NULL,
    po_id INT NULL,
    doc_ref VARCHAR(100) NULL,
    created_by INT NOT NULL,
    note VARCHAR(255) NULL,
    total_cost DECIMAL(14,2) NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL
);

CREATE TABLE receipt_items (
    id {PK},
    receipt_id INT NOT NULL,
    item_id INT NOT NULL,
    qty DECIMAL(12,2) NOT NULL,
    unit_cost DECIMAL(14,2) NOT NULL DEFAULT 0
);

CREATE TABLE inventory_counts (
    id {PK},
    number VARCHAR(30) NOT NULL UNIQUE,
    status VARCHAR(12) NOT NULL DEFAULT 'open',
    created_by INT NOT NULL,
    created_at DATETIME NOT NULL,
    applied_at DATETIME NULL
);

CREATE TABLE inventory_count_items (
    id {PK},
    count_id INT NOT NULL,
    item_id INT NOT NULL,
    system_qty DECIMAL(12,2) NOT NULL,
    counted_qty DECIMAL(12,2) NULL
);

CREATE TABLE tenders (
    id {PK},
    number VARCHAR(30) NOT NULL UNIQUE,
    title VARCHAR(250) NOT NULL,
    description TEXT NULL,
    terms TEXT NULL,
    request_id INT NULL,
    status VARCHAR(12) NOT NULL DEFAULT 'draft',
    deadline DATETIME NOT NULL,
    w_price INT NOT NULL DEFAULT 50,
    w_delivery INT NOT NULL DEFAULT 20,
    w_warranty INT NOT NULL DEFAULT 15,
    w_rating INT NOT NULL DEFAULT 15,
    created_by INT NOT NULL,
    created_at DATETIME NOT NULL,
    published_at DATETIME NULL,
    opened_at DATETIME NULL,
    winner_bid_id INT NULL,
    award_reason VARCHAR(500) NULL,
    awarded_at DATETIME NULL,
    awarded_by INT NULL
);

CREATE TABLE tender_items (
    id {PK},
    tender_id INT NOT NULL,
    item_id INT NULL,
    request_item_id INT NULL,
    name VARCHAR(200) NOT NULL,
    unit VARCHAR(20) NOT NULL DEFAULT 'ədəd',
    qty DECIMAL(12,2) NOT NULL
);

CREATE TABLE tender_invites (
    id {PK},
    tender_id INT NOT NULL,
    supplier_id INT NOT NULL,
    invited_at DATETIME NOT NULL,
    UNIQUE (tender_id, supplier_id)
);

CREATE TABLE bids (
    id {PK},
    tender_id INT NOT NULL,
    supplier_id INT NOT NULL,
    total_price DECIMAL(14,2) NOT NULL DEFAULT 0,
    delivery_days INT NOT NULL DEFAULT 0,
    warranty_months INT NOT NULL DEFAULT 0,
    note TEXT NULL,
    file_path VARCHAR(255) NULL,
    file_name VARCHAR(255) NULL,
    score DECIMAL(6,2) NULL,
    rank_no INT NULL,
    submitted_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    UNIQUE (tender_id, supplier_id)
);

CREATE TABLE bid_items (
    id {PK},
    bid_id INT NOT NULL,
    tender_item_id INT NOT NULL,
    unit_price DECIMAL(14,2) NOT NULL
);

CREATE TABLE purchase_orders (
    id {PK},
    number VARCHAR(30) NOT NULL UNIQUE,
    tender_id INT NULL,
    supplier_id INT NOT NULL,
    status VARCHAR(12) NOT NULL DEFAULT 'ordered',
    total DECIMAL(14,2) NOT NULL DEFAULT 0,
    created_by INT NULL,
    created_at DATETIME NOT NULL
);

CREATE TABLE po_items (
    id {PK},
    po_id INT NOT NULL,
    item_id INT NOT NULL,
    name VARCHAR(200) NOT NULL,
    qty DECIMAL(12,2) NOT NULL,
    unit_price DECIMAL(14,2) NOT NULL,
    received_qty DECIMAL(12,2) NOT NULL DEFAULT 0
);

CREATE TABLE audit_log (
    id {PK},
    user_id INT NULL,
    user_name VARCHAR(150) NULL,
    action VARCHAR(60) NOT NULL,
    entity VARCHAR(40) NULL,
    entity_id INT NULL,
    details TEXT NULL,
    ip VARCHAR(45) NULL,
    created_at DATETIME NOT NULL
);

CREATE TABLE notifications (
    id {PK},
    user_id INT NOT NULL,
    title VARCHAR(250) NOT NULL,
    body TEXT NULL,
    link VARCHAR(255) NULL,
    read_at DATETIME NULL,
    created_at DATETIME NOT NULL
);

CREATE TABLE mail_outbox (
    id {PK},
    to_email VARCHAR(190) NOT NULL,
    subject VARCHAR(250) NOT NULL,
    body TEXT NOT NULL,
    status VARCHAR(12) NOT NULL DEFAULT 'queued',
    created_at DATETIME NOT NULL
);

CREATE INDEX idx_mov_item ON stock_movements (item_id);
CREATE INDEX idx_mov_created ON stock_movements (created_at);
CREATE INDEX idx_mov_ref ON stock_movements (ref_type, department_id, category_id);
CREATE INDEX idx_req_status ON requests (status);
CREATE INDEX idx_req_requester ON requests (requester_id);
CREATE INDEX idx_req_dept ON requests (department_id);
CREATE INDEX idx_reqitems_req ON request_items (request_id);
CREATE INDEX idx_appr_req ON approvals (request_id);
CREATE INDEX idx_appr_status ON approvals (status, role);
CREATE INDEX idx_tender_status ON tenders (status, deadline);
CREATE INDEX idx_bids_tender ON bids (tender_id);
CREATE INDEX idx_audit_created ON audit_log (created_at);
CREATE INDEX idx_notif_user ON notifications (user_id, read_at);
CREATE INDEX idx_po_status ON purchase_orders (status);