-- ============================================================
-- STOCK MANAGEMENT APPLICATION - DATABASE SCHEMA
-- ============================================================
CREATE DATABASE IF NOT EXISTS stockapp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE stockapp;

-- ============================================================
-- 1. USERS (for billing "marked by" tracking)
-- ============================================================
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    role ENUM('admin','staff') DEFAULT 'staff',
    tab_permissions TEXT NULL,             -- JSON array of allowed tab keys (staff only, admin has full access)
    status TINYINT DEFAULT 1,
    active_session_token VARCHAR(64) NULL, -- current "live" session token (single-device login)
    last_login_at DATETIME NULL,
    last_login_ip VARCHAR(45) NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- ============================================================
-- 2. PURCHASE VENDORS (suppliers we buy from)
-- ============================================================
CREATE TABLE purchase_vendors (
    id INT AUTO_INCREMENT PRIMARY KEY,
    vendor_code VARCHAR(20) UNIQUE NOT NULL,
    vendor_name VARCHAR(150) NOT NULL,
    contact_person VARCHAR(100),
    phone VARCHAR(20),
    alt_phone VARCHAR(20),
    email VARCHAR(150),
    gst_number VARCHAR(20),
    address TEXT,
    city VARCHAR(100),
    state VARCHAR(100),
    pincode VARCHAR(10),
    bank_name VARCHAR(100),
    bank_account_no VARCHAR(50),
    bank_ifsc VARCHAR(20),
    opening_balance DECIMAL(12,2) DEFAULT 0,
    status TINYINT DEFAULT 1,           -- 1=active, 0=inactive
    notes TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- ============================================================
-- 3. SALE VENDORS / CUSTOMERS (we sell to them)
-- ============================================================
CREATE TABLE sale_vendors (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_code VARCHAR(20) UNIQUE NOT NULL,
    customer_name VARCHAR(150) NOT NULL,
    contact_person VARCHAR(100),
    phone VARCHAR(20) NOT NULL,
    alt_phone VARCHAR(20),
    email VARCHAR(150) NOT NULL,
    gst_number VARCHAR(20),
    billing_address TEXT,
    shipping_address TEXT,
    city VARCHAR(100),
    state VARCHAR(100),
    pincode VARCHAR(10),
    credit_limit DECIMAL(12,2) DEFAULT 0,
    opening_balance DECIMAL(12,2) DEFAULT 0,
    status TINYINT DEFAULT 1,
    notes TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- ============================================================
-- 4. ITEM CATEGORIES & ITEMS (Stock Master)
-- ============================================================
CREATE TABLE categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category_name VARCHAR(100) NOT NULL,
    status TINYINT DEFAULT 1
);

CREATE TABLE items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    item_code VARCHAR(30) UNIQUE NOT NULL,
    item_name VARCHAR(200) NOT NULL,
    category_id INT,
    brand_id INT NULL,
    model_id INT NULL,
    unit VARCHAR(20) DEFAULT 'Nos',       -- Nos, Box, Kg etc
    hsn_code VARCHAR(20),
    gst_percent DECIMAL(5,2) DEFAULT 18.00,  -- total gst (9+9 split applied at txn)
    sale_price DECIMAL(12,2) DEFAULT 0,      -- default selling price
    min_stock_level INT DEFAULT 0,
    description TEXT,
    status TINYINT DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id)
);

-- ============================================================
-- 5. STOCK LEDGER (real-time quantity tracking per item)
--    available_qty  = purchased - reserved(freeze) - sold
--    freeze_qty     = quantity blocked for pending deliveries
--    sold_qty       = quantity fully sold (paid)
-- ============================================================
CREATE TABLE stock_ledger (
    id INT AUTO_INCREMENT PRIMARY KEY,
    item_id INT NOT NULL UNIQUE,
    purchased_qty INT DEFAULT 0,
    available_qty INT DEFAULT 0,
    freeze_qty INT DEFAULT 0,
    sold_qty INT DEFAULT 0,
    avg_purchase_price DECIMAL(12,2) DEFAULT 0,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (item_id) REFERENCES items(id)
);

-- Stock movement log (audit trail for every +/- change)
CREATE TABLE stock_movements (
    id INT AUTO_INCREMENT PRIMARY KEY,
    item_id INT NOT NULL,
    movement_type ENUM('PURCHASE','FREEZE','UNFREEZE','SOLD','ADJUSTMENT') NOT NULL,
    qty INT NOT NULL,                     -- +ve or -ve
    reference_type VARCHAR(50),           -- 'purchase','delivery_note','invoice','adjustment'
    reference_id INT,
    remarks VARCHAR(255),
    created_by INT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (item_id) REFERENCES items(id)
);

-- ============================================================
-- 6. PURCHASES (Purchase Orders / Bills from purchase vendors)
-- ============================================================
CREATE TABLE purchases (
    id INT AUTO_INCREMENT PRIMARY KEY,
    purchase_no VARCHAR(30) UNIQUE NOT NULL,
    vendor_id INT NOT NULL,
    purchase_date DATE NOT NULL,
    invoice_no VARCHAR(50),               -- vendor's invoice number
    total_amount DECIMAL(14,2) DEFAULT 0,
    gst_amount DECIMAL(14,2) DEFAULT 0,
    grand_total DECIMAL(14,2) DEFAULT 0,
    payment_status ENUM('unpaid','partial','paid') DEFAULT 'unpaid',
    paid_amount DECIMAL(14,2) DEFAULT 0,
    notes TEXT,
    created_by INT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (vendor_id) REFERENCES purchase_vendors(id)
);

CREATE TABLE purchase_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    purchase_id INT NOT NULL,
    item_id INT NOT NULL,
    qty INT NOT NULL,
    purchase_price DECIMAL(12,2) NOT NULL,   -- per unit
    gst_percent DECIMAL(5,2) DEFAULT 18.00,
    gst_amount DECIMAL(12,2) DEFAULT 0,
    line_total DECIMAL(12,2) DEFAULT 0,
    FOREIGN KEY (purchase_id) REFERENCES purchases(id) ON DELETE CASCADE,
    FOREIGN KEY (item_id) REFERENCES items(id)
);

-- ============================================================
-- 7. QUOTATIONS
--    Two amount sets stored:
--      - internal: shows discount % and discount amount separately (for our records)
--      - customer: only final adjusted amount + gst, NO discount % shown
-- ============================================================
CREATE TABLE quotations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    quotation_no VARCHAR(30) UNIQUE NOT NULL,
    customer_id INT NOT NULL,
    quotation_date DATE NOT NULL,
    valid_until DATE,
    subtotal DECIMAL(14,2) DEFAULT 0,        -- sum of (price*qty) before gst
    cgst_amount DECIMAL(14,2) DEFAULT 0,     -- 9%
    sgst_amount DECIMAL(14,2) DEFAULT 0,     -- 9%
    total_before_discount DECIMAL(14,2) DEFAULT 0,
    discount_percent DECIMAL(5,2) DEFAULT 0, -- internal only
    discount_amount DECIMAL(14,2) DEFAULT 0, -- internal only
    grand_total DECIMAL(14,2) DEFAULT 0,     -- final amount sent to customer
    status ENUM('draft','sent','accepted','rejected','expired') DEFAULT 'draft',
    email_sent TINYINT DEFAULT 0,
    email_sent_at DATETIME NULL,
    notes TEXT,
    created_by INT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES sale_vendors(id)
);

CREATE TABLE quotation_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    quotation_id INT NOT NULL,
    item_id INT NOT NULL,
    qty INT NOT NULL,
    unit_price DECIMAL(12,2) NOT NULL,       -- sale price per unit (before discount)
    cgst_percent DECIMAL(5,2) DEFAULT 9.00,
    sgst_percent DECIMAL(5,2) DEFAULT 9.00,
    line_subtotal DECIMAL(12,2) DEFAULT 0,   -- qty*unit_price
    line_cgst DECIMAL(12,2) DEFAULT 0,
    line_sgst DECIMAL(12,2) DEFAULT 0,
    line_total_before_discount DECIMAL(12,2) DEFAULT 0,
    line_discount_amount DECIMAL(12,2) DEFAULT 0,  -- proportional share (internal)
    line_final_amount DECIMAL(12,2) DEFAULT 0,     -- amount shown to customer
    FOREIGN KEY (quotation_id) REFERENCES quotations(id) ON DELETE CASCADE,
    FOREIGN KEY (item_id) REFERENCES items(id)
);

-- ============================================================
-- 8. DELIVERY NOTES (created once quotation accepted)
--    On creation -> stock moves from available to freeze
-- ============================================================
CREATE TABLE delivery_notes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    delivery_no VARCHAR(30) UNIQUE NOT NULL,
    quotation_id INT NOT NULL,
    customer_id INT NOT NULL,
    delivery_date DATE NOT NULL,
    status ENUM('pending','delivered') DEFAULT 'pending',
    delivered_at DATETIME NULL,
    email_sent TINYINT DEFAULT 0,
    email_sent_at DATETIME NULL,
    notes TEXT,
    created_by INT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (quotation_id) REFERENCES quotations(id),
    FOREIGN KEY (customer_id) REFERENCES sale_vendors(id)
);

CREATE TABLE delivery_note_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    delivery_note_id INT NOT NULL,
    item_id INT NOT NULL,
    qty INT NOT NULL,
    FOREIGN KEY (delivery_note_id) REFERENCES delivery_notes(id) ON DELETE CASCADE,
    FOREIGN KEY (item_id) REFERENCES items(id)
);

-- ============================================================
-- 9. TAX INVOICES (generated after delivery)
--    On full payment received -> stock moves from freeze to sold
-- ============================================================
CREATE TABLE invoices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    invoice_no VARCHAR(30) UNIQUE NOT NULL,
    invoice_type ENUM('sale','manual') DEFAULT 'sale',
    quotation_id INT NULL,                 -- null for manual/service invoices
    delivery_note_id INT NULL,
    customer_id INT NOT NULL,
    invoice_date DATE NOT NULL,
    subtotal DECIMAL(14,2) DEFAULT 0,
    cgst_amount DECIMAL(14,2) DEFAULT 0,
    sgst_amount DECIMAL(14,2) DEFAULT 0,
    discount_amount DECIMAL(14,2) DEFAULT 0,
    grand_total DECIMAL(14,2) DEFAULT 0,
    payment_status ENUM('pending','partial','paid') DEFAULT 'pending',
    paid_amount DECIMAL(14,2) DEFAULT 0,
    payment_date DATETIME NULL,
    payment_mode VARCHAR(50) NULL,         -- cash/bank/upi/cheque
    payment_marked_by INT NULL,
    email_sent TINYINT DEFAULT 0,
    email_sent_at DATETIME NULL,
    notes TEXT,
    created_by INT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES sale_vendors(id),
    FOREIGN KEY (quotation_id) REFERENCES quotations(id),
    FOREIGN KEY (delivery_note_id) REFERENCES delivery_notes(id)
);

CREATE TABLE invoice_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    invoice_id INT NOT NULL,
    item_id INT NULL,                      -- NULL for manual/service line items
    description VARCHAR(255),              -- used for manual billing service desc
    qty DECIMAL(10,2) NOT NULL DEFAULT 1,
    unit_price DECIMAL(12,2) NOT NULL,
    cgst_percent DECIMAL(5,2) DEFAULT 9.00,
    sgst_percent DECIMAL(5,2) DEFAULT 9.00,
    line_subtotal DECIMAL(12,2) DEFAULT 0,
    line_cgst DECIMAL(12,2) DEFAULT 0,
    line_sgst DECIMAL(12,2) DEFAULT 0,
    line_total DECIMAL(12,2) DEFAULT 0,
    FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE,
    FOREIGN KEY (item_id) REFERENCES items(id)
);

-- ============================================================
-- 10. PAYMENTS LOG (for cash flow & billing reflection)
-- ============================================================
CREATE TABLE payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    payment_type ENUM('inflow','outflow') NOT NULL,  -- inflow=from customer, outflow=to vendor
    reference_type ENUM('invoice','purchase') NOT NULL,
    reference_id INT NOT NULL,
    party_name VARCHAR(150),
    amount DECIMAL(14,2) NOT NULL,
    payment_date DATETIME NOT NULL,
    payment_mode VARCHAR(50),
    remarks VARCHAR(255),
    created_by INT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- ============================================================
-- 11. EMAIL LOG (track all automated emails)
-- ============================================================
CREATE TABLE email_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email_type ENUM('quotation','delivery_note','tax_invoice') NOT NULL,
    reference_id INT NOT NULL,
    to_email VARCHAR(150) NOT NULL,
    subject VARCHAR(255),
    status ENUM('sent','failed') DEFAULT 'sent',
    error_message TEXT NULL,
    sent_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- ============================================================
-- ACCOUNTS & FINANCE MODULE (chart of accounts, journal, expenses, salaries)
-- ============================================================
CREATE TABLE account_groups (
    id INT AUTO_INCREMENT PRIMARY KEY,
    group_name VARCHAR(100) NOT NULL,
    group_type ENUM('asset','liability','equity','income','expense') NOT NULL,
    sort_order INT DEFAULT 0
);

CREATE TABLE accounts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    account_code VARCHAR(20) UNIQUE NOT NULL,
    account_name VARCHAR(150) NOT NULL,
    account_type ENUM('asset','liability','equity','income','expense') NOT NULL,
    account_subtype VARCHAR(30) NULL,
    group_id INT NULL,
    bank_name VARCHAR(100) NULL,
    account_number VARCHAR(50) NULL,
    ifsc_code VARCHAR(20) NULL,
    opening_balance DECIMAL(14,2) DEFAULT 0,
    current_balance DECIMAL(14,2) DEFAULT 0,
    description TEXT NULL,
    is_system TINYINT DEFAULT 0,
    is_active TINYINT DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (group_id) REFERENCES account_groups(id)
);

CREATE TABLE journal_entries (
    id INT AUTO_INCREMENT PRIMARY KEY,
    entry_no VARCHAR(30) UNIQUE NOT NULL,
    entry_date DATE NOT NULL,
    entry_type VARCHAR(30) NOT NULL,
    reference_type VARCHAR(30) NULL,
    reference_id INT NULL,
    narration TEXT NULL,
    total_debit DECIMAL(14,2) DEFAULT 0,
    total_credit DECIMAL(14,2) DEFAULT 0,
    created_by INT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE journal_lines (
    id INT AUTO_INCREMENT PRIMARY KEY,
    journal_id INT NOT NULL,
    account_id INT NOT NULL,
    debit DECIMAL(14,2) DEFAULT 0,
    credit DECIMAL(14,2) DEFAULT 0,
    narration VARCHAR(255) NULL,
    FOREIGN KEY (journal_id) REFERENCES journal_entries(id) ON DELETE CASCADE,
    FOREIGN KEY (account_id) REFERENCES accounts(id)
);

CREATE TABLE expenses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    expense_date DATE NOT NULL,
    category VARCHAR(100) NOT NULL,
    description VARCHAR(255) NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    paid_from INT NOT NULL,
    vendor_name VARCHAR(150) NULL,
    reference_no VARCHAR(50) NULL,
    journal_id INT NULL,
    created_by INT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (paid_from) REFERENCES accounts(id),
    FOREIGN KEY (journal_id) REFERENCES journal_entries(id)
);

CREATE TABLE salaries (
    id INT AUTO_INCREMENT PRIMARY KEY,
    salary_month VARCHAR(7) NOT NULL,
    employee_name VARCHAR(150) NOT NULL,
    designation VARCHAR(100) NULL,
    basic_salary DECIMAL(12,2) DEFAULT 0,
    allowances DECIMAL(12,2) DEFAULT 0,
    deductions DECIMAL(12,2) DEFAULT 0,
    net_salary DECIMAL(12,2) DEFAULT 0,
    status ENUM('pending','paid') DEFAULT 'pending',
    paid_from INT NULL,
    paid_date DATE NULL,
    payment_mode VARCHAR(30) NULL,
    journal_id INT NULL,
    created_by INT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (paid_from) REFERENCES accounts(id),
    FOREIGN KEY (journal_id) REFERENCES journal_entries(id)
);

-- Seed groups
INSERT INTO account_groups (group_name, group_type, sort_order) VALUES
('Bank Accounts', 'asset', 1),
('Cash in Hand', 'asset', 2),
('Credit Cards', 'liability', 3),
('Receivables', 'asset', 4),
('Inventory', 'asset', 5),
('Payables', 'liability', 6),
('Sales Revenue', 'income', 7),
('Operating Expenses', 'expense', 8),
('Payroll', 'expense', 9);

-- Seed system accounts (referenced by fixed account_code in the app's code - do not change these codes)
INSERT INTO accounts (account_code, account_name, account_type, account_subtype, group_id, is_system, is_active) VALUES
('1100', 'Accounts Receivable', 'asset', 'receivable', (SELECT id FROM account_groups WHERE group_name='Receivables'), 1, 1),
('1200', 'Stock / Inventory', 'asset', 'inventory', (SELECT id FROM account_groups WHERE group_name='Inventory'), 1, 1),
('2100', 'Accounts Payable', 'liability', 'payable', (SELECT id FROM account_groups WHERE group_name='Payables'), 1, 1),
('4001', 'Sales Revenue', 'income', 'income', (SELECT id FROM account_groups WHERE group_name='Sales Revenue'), 1, 1),
('5105', 'Miscellaneous Expense', 'expense', 'expense', (SELECT id FROM account_groups WHERE group_name='Operating Expenses'), 1, 1),
('5200', 'Salary Expense', 'expense', 'expense', (SELECT id FROM account_groups WHERE group_name='Payroll'), 1, 1);

-- ============================================================
-- ITEM CONFIGURATIONS (per-category spec fields on Item Master,
-- e.g. "RAM: 8GB/16GB" for an Electronics category)
-- ============================================================
CREATE TABLE item_configurations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category_id INT NULL,
    config_name VARCHAR(100) NOT NULL,
    status TINYINT DEFAULT 1,
    sort_order INT DEFAULT 0,
    UNIQUE KEY uniq_cat_config (category_id, config_name)
);

CREATE TABLE item_configuration_values (
    id INT AUTO_INCREMENT PRIMARY KEY,
    config_id INT NOT NULL,
    value VARCHAR(150) NOT NULL,
    status TINYINT DEFAULT 1,
    UNIQUE KEY uniq_config_value (config_id, value),
    FOREIGN KEY (config_id) REFERENCES item_configurations(id) ON DELETE CASCADE
);

CREATE TABLE item_specs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    item_id INT NOT NULL,
    config_id INT NOT NULL,
    config_value_id INT NOT NULL,
    UNIQUE KEY uniq_item_config (item_id, config_id),
    FOREIGN KEY (item_id) REFERENCES items(id) ON DELETE CASCADE,
    FOREIGN KEY (config_id) REFERENCES item_configurations(id),
    FOREIGN KEY (config_value_id) REFERENCES item_configuration_values(id)
);

-- ============================================================
-- 12. SETTINGS (company info, gst, email config placeholders)
-- ============================================================
CREATE TABLE settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) UNIQUE NOT NULL,
    setting_value TEXT
);

INSERT INTO settings (setting_key, setting_value) VALUES
('company_name', 'Your Company Name'),
('company_address', 'Your Address Here'),
('company_gst', '33XXXXXXXXXXXZX'),
('company_phone', '0000000000'),
('company_email', 'info@yourcompany.com'),
('company_logo', ''),
('cgst_percent', '9'),
('sgst_percent', '9'),
('quotation_prefix', 'QTN'),
('delivery_prefix', 'DN'),
('invoice_prefix', 'INV'),
('purchase_prefix', 'PUR'),
('smtp_host', 'smtp.example.com'),
('smtp_port', '587'),
('smtp_username', 'your-email@example.com'),
('smtp_password', 'your-app-password'),
('smtp_from_email', 'your-email@example.com'),
('smtp_from_name', 'Your Company Name'),
('smtp_encryption', 'tls'),
('license_count', '5');

-- Default admin user (password: admin123 -- CHANGE AFTER FIRST LOGIN)
INSERT INTO users (name, email, password, role) VALUES
('Administrator', 'admin@stockapp.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin');

-- Sample category
INSERT INTO categories (category_name) VALUES ('Laptops'), ('Accessories'), ('Components'), ('Services');

-- ============================================================
-- SCHEMA UPDATES (v2) - Run these if upgrading existing install
-- ============================================================

-- Purchase vendors: add company_name, vendor_rating, pan_no, documents
ALTER TABLE purchase_vendors
  ADD COLUMN IF NOT EXISTS company_name VARCHAR(150) DEFAULT NULL AFTER vendor_code,
  ADD COLUMN IF NOT EXISTS vendor_rating TINYINT DEFAULT 3 AFTER notes,
  ADD COLUMN IF NOT EXISTS pan_no VARCHAR(15) DEFAULT NULL AFTER gst_number;

-- Vendor documents table
CREATE TABLE IF NOT EXISTS vendor_documents (
    id INT AUTO_INCREMENT PRIMARY KEY,
    vendor_id INT NOT NULL,
    doc_type ENUM('gstin_certificate','pan_card','other') NOT NULL,
    doc_name VARCHAR(255) NOT NULL,
    file_path VARCHAR(500) NOT NULL,
    uploaded_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (vendor_id) REFERENCES purchase_vendors(id) ON DELETE CASCADE
);

-- Sale vendors: add company_name
ALTER TABLE sale_vendors
  ADD COLUMN IF NOT EXISTS company_name VARCHAR(150) DEFAULT NULL AFTER customer_code;

-- Items: separate brand and model as selectable master tables
CREATE TABLE IF NOT EXISTS item_brands (
    id INT AUTO_INCREMENT PRIMARY KEY,
    brand_name VARCHAR(100) NOT NULL,
    category_id INT NULL,
    status TINYINT DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_brand_category (brand_name, category_id),
    FOREIGN KEY (category_id) REFERENCES categories(id)
);

CREATE TABLE IF NOT EXISTS item_models (
    id INT AUTO_INCREMENT PRIMARY KEY,
    brand_id INT NOT NULL,
    model_name VARCHAR(150) NOT NULL,
    status TINYINT DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (brand_id) REFERENCES item_brands(id) ON DELETE CASCADE
);

-- Seed some sample brands
INSERT IGNORE INTO item_brands (brand_name) VALUES
('Dell'),('HP'),('Lenovo'),('Apple'),('Asus'),('Acer'),('Samsung'),('LG'),('Sony'),('Canon'),('Epson'),('Logitech'),('Microsoft');

-- items.brand_id / items.model_id can only be linked up now that
-- item_brands / item_models exist (they're defined further up in this
-- file, before items - this constraint just needs to come after both).
ALTER TABLE items
    ADD CONSTRAINT fk_items_brand FOREIGN KEY (brand_id) REFERENCES item_brands(id),
    ADD CONSTRAINT fk_items_model FOREIGN KEY (model_id) REFERENCES item_models(id);