-- ============================================================
-- SEDERCORE - LIMITED EDITION DATABASE SCHEMA
-- (Purchase Vendors + Stock/Item Master module removed.
--  Quotation items are typed in manually instead of being
--  picked from an Item Master.)
--
-- Use this file ONLY for a brand-new install. If you already
-- have a live "Full Edition" database with real data in it,
-- do NOT run this — run migration_remove_stock_module.sql on
-- your existing database instead, so your old data is kept.
-- ============================================================
CREATE DATABASE IF NOT EXISTS stockapp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE stockapp;

-- ============================================================
-- 1. USERS
-- ============================================================
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. SALE VENDORS / CUSTOMERS (we sell to them)
-- ============================================================
CREATE TABLE sale_vendors (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_code VARCHAR(20) UNIQUE NOT NULL,
    company_name VARCHAR(150) DEFAULT 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
);

-- ============================================================
-- 3. QUOTATIONS
--    Items are typed in manually (item_name/hsn_code/unit are
--    free text) — there is no Item Master to pick from.
--
--    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 NULL,                        -- always NULL in this edition (no Item Master); kept so the
                                              -- same code also works on Full Edition companies
    item_name VARCHAR(200) NOT NULL,         -- typed manually
    hsn_code VARCHAR(20) NULL,               -- typed manually
    unit VARCHAR(20) NOT NULL DEFAULT 'Nos', -- typed manually
    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
);

-- ============================================================
-- 3b. ITEMS (stub table — kept empty on purpose)
--    This Limited Edition has no Item Master screen; items are
--    always typed in manually on the quotation itself. This
--    table exists only so the app's `LEFT JOIN items` fallback
--    queries (written to also support the Full Edition, where
--    items ARE picked from a real Item Master) don't error out
--    on a missing table. It is never written to.
-- ============================================================
CREATE TABLE items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    item_name VARCHAR(200),
    hsn_code VARCHAR(20),
    unit VARCHAR(20)
);

-- ============================================================
-- 4. DELIVERY NOTES (created once quotation accepted)
--    Each delivery_note_items row is a full standalone copy of
--    the quotation line (name, hsn, unit, rate, tax, amount) so
--    it never needs to look anything up again.
-- ============================================================
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,
    delivered_datetime DATETIME NULL,
    delivery_location VARCHAR(255) NULL,
    delivery_notes_text TEXT NULL,
    sent_to_customer TINYINT DEFAULT 0,
    sent_to_customer_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,
    quotation_item_id INT NULL,              -- the quotation line this came from
    item_id INT NULL,                        -- always NULL in this edition (no Item Master)
    qty INT NOT NULL,
    item_name VARCHAR(200) NULL,
    hsn_code VARCHAR(20) NULL,
    unit VARCHAR(20) NULL DEFAULT 'Nos',
    unit_price DECIMAL(12,2) NULL DEFAULT 0,
    cgst_percent DECIMAL(5,2) NULL DEFAULT 9,
    sgst_percent DECIMAL(5,2) NULL DEFAULT 9,
    line_subtotal DECIMAL(12,2) NULL DEFAULT 0,
    line_cgst DECIMAL(12,2) NULL DEFAULT 0,
    line_sgst DECIMAL(12,2) NULL DEFAULT 0,
    line_total_before_discount DECIMAL(12,2) NULL DEFAULT 0,
    line_discount_amount DECIMAL(12,2) NULL DEFAULT 0,
    line_final_amount DECIMAL(12,2) NULL DEFAULT 0,
    FOREIGN KEY (delivery_note_id) REFERENCES delivery_notes(id) ON DELETE CASCADE,
    FOREIGN KEY (quotation_item_id) REFERENCES quotation_items(id)
);

-- ============================================================
-- 5. TAX INVOICES (generated after delivery, or manually)
-- ============================================================
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,
    tds_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,                      -- always NULL in this edition (no Item Master)
    description VARCHAR(255),              -- item / service name (typed manually)
    hsn_code VARCHAR(20) NULL,
    unit VARCHAR(20) NULL DEFAULT 'Nos',
    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
);

-- ============================================================
-- 6. 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
    reference_type ENUM('invoice') 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
);

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

-- ============================================================
-- 8. 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'),
('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');

-- ============================================================
-- NOTE: The following tables from the earlier "Full Edition"
-- have been removed in this Limited Edition, since their pages
-- (Purchase Vendors, Item Master, Purchase Entry, Purchase
-- Payments, Stock Overview) no longer exist in the app:
--   purchase_vendors, vendor_documents, categories, items,
--   item_brands, item_models, stock_ledger, stock_movements,
--   purchases, purchase_items
-- ============================================================
