-- ============================================================
-- 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',
    status TINYINT DEFAULT 1,
    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 VARCHAR(100),
    model VARCHAR(100),
    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
);

-- ============================================================
-- 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');

-- 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 UNIQUE,
    status TINYINT DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

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');
