-- ============================================================
--  MediCore — Full Database Schema
--  File: medicore_schema.sql
--  Run once:  mysql -u root -p medicore < medicore_schema.sql
-- ============================================================

CREATE DATABASE IF NOT EXISTS medicore
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE medicore;

-- ── 1. SETTINGS ─────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS settings (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    setting_key   VARCHAR(60)   NOT NULL UNIQUE,
    setting_value TEXT          NOT NULL DEFAULT '',
    updated_at    TIMESTAMP     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Seed default settings
INSERT IGNORE INTO settings (setting_key, setting_value) VALUES
('shop_name',      'Noor Pharma & Dept. Store'),
('address',        '123 Medical Road, Dhaka-1200'),
('phone',          '01700-000000'),
('email',          'info@noorpharma.com'),
('reg_no',         'DL-2024-00123'),
('logo',           ''),
('vat_rate',       '17.4'),
('currency',       '৳'),
('invoice_note',   'Thank you for your purchase.'),
('inv_prefix',     'INV'),
('po_prefix',      'PO'),
('req_prefix',     'REQ'),
('theme_color',    '#2d3a8c'),
('low_stock_days', '30');

-- ── 2. USERS ─────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS users (
    id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name         VARCHAR(100) NOT NULL,
    username     VARCHAR(60)  NOT NULL UNIQUE,
    password     VARCHAR(255) NOT NULL,              -- bcrypt
    role         ENUM('admin','manager','cashier','warehouse','staff') DEFAULT 'cashier',
    avatar       VARCHAR(10)  DEFAULT '',             -- initials
    is_active    TINYINT(1)   DEFAULT 1,
    created_at   TIMESTAMP    DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ── MIGRATION (run this if users table already exists with the
--    old admin/cashier-only ENUM) ───────────────────────────────
-- ALTER TABLE users
--   MODIFY COLUMN role ENUM('admin','manager','cashier','warehouse','staff')
--   DEFAULT 'cashier';

-- Default admin: username=admin  password=admin123  (change after first login)
INSERT IGNORE INTO users (name, username, password, role, avatar)
VALUES ('Administrator', 'admin',
        '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi',
        'admin', 'AD');

-- ── 3. COMPANIES ─────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS companies (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name       VARCHAR(150) NOT NULL UNIQUE,
    country    VARCHAR(60)  DEFAULT 'Bangladesh',
    created_at TIMESTAMP    DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

INSERT IGNORE INTO companies (name) VALUES
('Beximco Pharmaceuticals Ltd.'),('Square Pharmaceuticals Plc'),
('Incepta Pharma'),('Opsonin Pharma'),('ACI Limited'),
('Renata Pharma'),('Drug International'),('GlaxoSmithKline Plc'),
('Jenpher Bangladesh Ltd.');

-- ── 4. GENERICS ──────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS generics (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name       VARCHAR(150) NOT NULL UNIQUE,
    created_at TIMESTAMP    DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

INSERT IGNORE INTO generics (name) VALUES
('Paracetamol'),('Vitamin B1, B6, B12'),('Amlodipine Besylate'),
('Omeprazole'),('Azithromycin'),('Pantoprazole'),
('Cetirizine HCl'),('Atorvastatin'),('Metformin HCl'),
('Amoxicillin'),('Aluminium Hydroxide');

-- ── 5. PRODUCTS ──────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS products (
    id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name         VARCHAR(150) NOT NULL,
    form         VARCHAR(60)  DEFAULT '',           -- TABLET, CAPSULE, SYRUP…
    strength     VARCHAR(100) DEFAULT '',
    generic_id   INT UNSIGNED,
    company_id   INT UNSIGNED,
    barcode      VARCHAR(60)  DEFAULT '' UNIQUE,
    rack         VARCHAR(20)  DEFAULT '',
    qty_in_box   INT UNSIGNED DEFAULT 1,            -- pcs per box
    min_qty      INT UNSIGNED DEFAULT 1,
    btp          DECIMAL(10,2) DEFAULT 0.00,        -- base trade price (per box)
    vat          DECIMAL(10,2) DEFAULT 0.00,        -- VAT per box
    btp_vat      DECIMAL(10,2) DEFAULT 0.00,        -- btp + vat
    sale_price   DECIMAL(10,2) DEFAULT 0.00,        -- per piece
    stock_pcs    INT          DEFAULT 0,            -- current stock in pieces
    expiry       VARCHAR(10)  DEFAULT '',           -- last known expiry YYYY-MM
    is_active    TINYINT(1)   DEFAULT 1,
    created_at   TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    updated_at   TIMESTAMP    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (generic_id) REFERENCES generics(id) ON DELETE SET NULL,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE SET NULL,
    INDEX idx_name    (name),
    INDEX idx_barcode (barcode),
    INDEX idx_stock   (stock_pcs)
) ENGINE=InnoDB;

-- ── 6. SUPPLIERS ─────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS suppliers (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name       VARCHAR(150) NOT NULL,
    contact    VARCHAR(100) DEFAULT '',
    phone      VARCHAR(30)  DEFAULT '',
    address    TEXT         DEFAULT '',
    balance    DECIMAL(12,2) DEFAULT 0.00,          -- amount owed TO supplier
    created_at TIMESTAMP    DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ── 7. CUSTOMERS ─────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS customers (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name       VARCHAR(100) NOT NULL,
    phone      VARCHAR(30)  DEFAULT '',
    address    TEXT         DEFAULT '',
    due        DECIMAL(12,2) DEFAULT 0.00,
    advance    DECIMAL(12,2) DEFAULT 0.00,
    created_at TIMESTAMP    DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Walk-in customer (no account)
INSERT IGNORE INTO customers (id, name, phone) VALUES (1, 'Walk-in Customer', '');

-- ── 8. PURCHASES (header) ────────────────────────────────────
CREATE TABLE IF NOT EXISTS purchases (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    invoice       VARCHAR(30)  NOT NULL UNIQUE,
    supplier_id   INT UNSIGNED,
    supplier_name VARCHAR(150) DEFAULT '',
    inv_no        VARCHAR(60)  DEFAULT '',          -- supplier's own invoice no
    date          DATE         NOT NULL,
    mode          ENUM('unit_trvat','net_value') DEFAULT 'unit_trvat',
    discount_pct  DECIMAL(5,2)  DEFAULT 0.00,
    total_btp     DECIMAL(12,2) DEFAULT 0.00,
    total_vat     DECIMAL(12,2) DEFAULT 0.00,
    final_price   DECIMAL(12,2) DEFAULT 0.00,
    paid          DECIMAL(12,2) DEFAULT 0.00,
    due           DECIMAL(12,2) DEFAULT 0.00,
    notes         TEXT,
    created_at    TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (supplier_id) REFERENCES suppliers(id) ON DELETE SET NULL,
    INDEX idx_date (date)
) ENGINE=InnoDB;

-- ── 9. PURCHASE ITEMS ────────────────────────────────────────
CREATE TABLE IF NOT EXISTS purchase_items (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    purchase_id INT UNSIGNED NOT NULL,
    product_id  INT UNSIGNED,
    product_name VARCHAR(150) DEFAULT '',
    boxes       INT UNSIGNED DEFAULT 0,
    qty_in_box  INT UNSIGNED DEFAULT 1,
    qty_pcs     INT UNSIGNED DEFAULT 0,             -- boxes * qty_in_box
    btp         DECIMAL(10,2) DEFAULT 0.00,
    vat         DECIMAL(10,2) DEFAULT 0.00,
    btp_vat     DECIMAL(10,2) DEFAULT 0.00,
    sale_price  DECIMAL(10,2) DEFAULT 0.00,
    free        INT UNSIGNED DEFAULT 0,
    exp         VARCHAR(10)  DEFAULT '',            -- YYYY-MM
    total_price DECIMAL(12,2) DEFAULT 0.00,
    FOREIGN KEY (purchase_id) REFERENCES purchases(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id)  REFERENCES products(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ── 10. SALES (header) ───────────────────────────────────────
CREATE TABLE IF NOT EXISTS sales (
    id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    invoice        VARCHAR(30)  NOT NULL UNIQUE,
    customer_id    INT UNSIGNED,
    customer_name  VARCHAR(100) DEFAULT 'Walk-in Customer',
    date           DATE         NOT NULL,
    sub_total      DECIMAL(12,2) DEFAULT 0.00,
    discount_pct   DECIMAL(5,2)  DEFAULT 0.00,
    discount_amt   DECIMAL(12,2) DEFAULT 0.00,
    total_price    DECIMAL(12,2) DEFAULT 0.00,
    final_price    DECIMAL(12,2) DEFAULT 0.00,
    paid           DECIMAL(12,2) DEFAULT 0.00,
    due            DECIMAL(12,2) DEFAULT 0.00,
    approx_profit  DECIMAL(12,2) DEFAULT 0.00,
    payment_type   ENUM('Cash','Card','Bank','MFS') DEFAULT 'Cash',
    status         ENUM('Paid','Partial','Due')      DEFAULT 'Paid',
    notes          TEXT,
    created_by     INT UNSIGNED,
    created_at     TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL,
    FOREIGN KEY (created_by)  REFERENCES users(id)     ON DELETE SET NULL,
    INDEX idx_date        (date),
    INDEX idx_customer_id (customer_id)
) ENGINE=InnoDB;

-- ── 11. SALE ITEMS ───────────────────────────────────────────
CREATE TABLE IF NOT EXISTS sale_items (
    id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    sale_id      INT UNSIGNED NOT NULL,
    product_id   INT UNSIGNED,
    product_name VARCHAR(150) DEFAULT '',
    qty          INT UNSIGNED DEFAULT 1,
    price        DECIMAL(10,2) DEFAULT 0.00,        -- sale price per pc
    cost         DECIMAL(10,2) DEFAULT 0.00,        -- cost per pc at time of sale
    discount_pct DECIMAL(5,2)  DEFAULT 0.00,
    total        DECIMAL(12,2) DEFAULT 0.00,        -- qty * price
    after_disc   DECIMAL(12,2) DEFAULT 0.00,        -- after item discount
    FOREIGN KEY (sale_id)    REFERENCES sales(id)    ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ── 12. CASH LEDGER ──────────────────────────────────────────
CREATE TABLE IF NOT EXISTS cash_ledger (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    date        DATE         NOT NULL,
    description VARCHAR(255) NOT NULL,
    account     ENUM('Cash','Card','Bank','MFS') DEFAULT 'Cash',
    type        ENUM('in','out')                 NOT NULL,
    amount      DECIMAL(12,2) DEFAULT 0.00,
    ref         VARCHAR(60)   DEFAULT '',        -- invoice or reference no
    created_at  TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_date    (date),
    INDEX idx_account (account)
) ENGINE=InnoDB;

-- Opening balances (edit amounts to match your actual opening)
INSERT IGNORE INTO cash_ledger (date, description, account, type, amount, ref) VALUES
('2025-01-01', 'Opening Balance - Cash',  'Cash', 'in', 50000, 'OPEN'),
('2025-01-01', 'Opening Balance - Bank',  'Bank', 'in', 200000,'OPEN'),
('2025-01-01', 'Opening Balance - MFS',   'MFS',  'in', 10000, 'OPEN');

-- ── 13. INVESTMENTS ──────────────────────────────────────────
CREATE TABLE IF NOT EXISTS investments (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    partner    VARCHAR(100) NOT NULL,
    type       ENUM('deposit','withdraw') NOT NULL,
    amount     DECIMAL(12,2) DEFAULT 0.00,
    date       DATE          NOT NULL,
    note       TEXT,
    created_at TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_partner (partner)
) ENGINE=InnoDB;

-- ── 14. EXPENSES ─────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS expenses (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    category   VARCHAR(60)   DEFAULT 'Misc',
    description VARCHAR(255) NOT NULL,
    amount     DECIMAL(12,2) DEFAULT 0.00,
    account    ENUM('Cash','Card','Bank','MFS') DEFAULT 'Cash',
    date       DATE          NOT NULL,
    ref        VARCHAR(60)   DEFAULT '',
    created_at TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_date (date)
) ENGINE=InnoDB;

-- ── 15. INCOMES ──────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS incomes (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    category    VARCHAR(60)   DEFAULT 'Other',
    description VARCHAR(255)  NOT NULL,
    amount      DECIMAL(12,2) DEFAULT 0.00,
    account     ENUM('Cash','Card','Bank','MFS') DEFAULT 'Cash',
    date        DATE          NOT NULL,
    ref         VARCHAR(60)   DEFAULT '',
    created_at  TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_date (date)
) ENGINE=InnoDB;

-- ── 16. REQUISITIONS (header) ────────────────────────────────
CREATE TABLE IF NOT EXISTS requisitions (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ref           VARCHAR(30)  NOT NULL UNIQUE,
    supplier_id   INT UNSIGNED,
    supplier_name VARCHAR(150) DEFAULT '',
    date          DATETIME     NOT NULL,
    status        ENUM('active','ordered','received','cancelled') DEFAULT 'active',
    total_price   DECIMAL(12,2) DEFAULT 0.00,
    notes         TEXT,
    created_at    TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (supplier_id) REFERENCES suppliers(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ── 17. REQUISITION ITEMS ────────────────────────────────────
CREATE TABLE IF NOT EXISTS requisition_items (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    requisition_id  INT UNSIGNED NOT NULL,
    product_id      INT UNSIGNED,
    product_name    VARCHAR(150) DEFAULT '',
    qty_box         INT UNSIGNED DEFAULT 1,
    btp_vat         DECIMAL(10,2) DEFAULT 0.00,
    total_price     DECIMAL(12,2) DEFAULT 0.00,
    FOREIGN KEY (requisition_id) REFERENCES requisitions(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id)     REFERENCES products(id)     ON DELETE SET NULL
) ENGINE=InnoDB;

-- ── 18. AI CACHE (saves free-tier tokens) ────────────────────
CREATE TABLE IF NOT EXISTS ai_cache (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    cache_key   VARCHAR(64)  NOT NULL UNIQUE,    -- MD5 of the input
    feature     VARCHAR(30)  NOT NULL,           -- 'interaction'|'requisition'|'summary'
    prompt_hint VARCHAR(255) DEFAULT '',         -- human-readable description
    response    TEXT         NOT NULL,
    expires_at  DATETIME     NOT NULL,           -- auto-expire stale results
    created_at  TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_expires (expires_at)
) ENGINE=InnoDB;

-- ── Done ─────────────────────────────────────────────────────
SELECT 'MediCore schema installed successfully.' AS status;
