-- =====================================================================
-- ENTERPRISE UPGRADE MIGRATION
-- System: Hizen Pharma POS + eCommerce
-- Run ONCE on the `iqommerce` database.
-- SAFE: uses IF NOT EXISTS / IF EXISTS – idempotent, no data loss.
-- =====================================================================

SET FOREIGN_KEY_CHECKS = 0;
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";

-- =====================================================================
-- PHASE 1 – TENANTS (Multi-Tenant SaaS Foundation)
-- =====================================================================

CREATE TABLE IF NOT EXISTS `tenants` (
  `id`           TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name`         VARCHAR(100) NOT NULL,
  `slug`         VARCHAR(100) NOT NULL,
  `email`        VARCHAR(100) DEFAULT NULL,
  `phone`        VARCHAR(25)  DEFAULT NULL,
  `address`      TEXT         DEFAULT NULL,
  `logo`         VARCHAR(255) DEFAULT NULL,
  `plan`         ENUM('starter','professional','enterprise') DEFAULT 'starter',
  `status`       ENUM('active','suspended','cancelled')      DEFAULT 'active',
  `trial_ends_at` DATE        DEFAULT NULL,
  `created_at`   TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Seed default tenant (existing business)
INSERT IGNORE INTO `tenants` (`id`, `name`, `slug`, `email`, `plan`, `status`)
VALUES (1, 'Hizen Pharma', 'hizen-pharma', 'admin@hizenpharma.com', 'enterprise', 'active');

-- Add tenant_id to existing core tables (all existing rows default to tenant 1)
ALTER TABLE `admins`       ADD COLUMN IF NOT EXISTS `tenant_id` TINYINT UNSIGNED NOT NULL DEFAULT 1 AFTER `id`;
ALTER TABLE `orders`       ADD COLUMN IF NOT EXISTS `tenant_id` TINYINT UNSIGNED NOT NULL DEFAULT 1 AFTER `id`;
ALTER TABLE `products`     ADD COLUMN IF NOT EXISTS `tenant_id` TINYINT UNSIGNED NOT NULL DEFAULT 1 AFTER `id`;
ALTER TABLE `users`        ADD COLUMN IF NOT EXISTS `tenant_id` TINYINT UNSIGNED NOT NULL DEFAULT 1 AFTER `id`;
ALTER TABLE `categories`   ADD COLUMN IF NOT EXISTS `tenant_id` TINYINT UNSIGNED NOT NULL DEFAULT 1 AFTER `id`;
ALTER TABLE `coupons`      ADD COLUMN IF NOT EXISTS `tenant_id` TINYINT UNSIGNED NOT NULL DEFAULT 1 AFTER `id`;
ALTER TABLE `settings`     ADD COLUMN IF NOT EXISTS `tenant_id` TINYINT UNSIGNED NOT NULL DEFAULT 1 AFTER `id`;

-- Add login_attempts column to admins for brute-force protection
ALTER TABLE `admins` ADD COLUMN IF NOT EXISTS `login_attempts`  TINYINT UNSIGNED NOT NULL DEFAULT 0 AFTER `last_login`;
ALTER TABLE `admins` ADD COLUMN IF NOT EXISTS `locked_until`    DATETIME DEFAULT NULL AFTER `login_attempts`;

-- =====================================================================
-- PHASE 2 – ROLES & PERMISSIONS (RBAC)
-- =====================================================================

CREATE TABLE IF NOT EXISTS `roles` (
  `id`          SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id`   TINYINT UNSIGNED  NOT NULL DEFAULT 1,
  `name`        VARCHAR(60)  NOT NULL,
  `slug`        VARCHAR(60)  NOT NULL,
  `description` VARCHAR(255) DEFAULT NULL,
  `is_system`   TINYINT(1)   DEFAULT 0 COMMENT 'System roles cannot be deleted',
  `created_at`  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_role_tenant` (`tenant_id`, `slug`),
  KEY `tenant_id` (`tenant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `permissions` (
  `id`     SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `module` VARCHAR(50) NOT NULL,
  `action` VARCHAR(50) NOT NULL,
  `label`  VARCHAR(100) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_perm` (`module`, `action`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `role_permissions` (
  `role_id`       SMALLINT UNSIGNED NOT NULL,
  `permission_id` SMALLINT UNSIGNED NOT NULL,
  PRIMARY KEY (`role_id`, `permission_id`),
  KEY `permission_id` (`permission_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `admin_roles` (
  `admin_id`  INT UNSIGNED     NOT NULL,
  `role_id`   SMALLINT UNSIGNED NOT NULL,
  `tenant_id` TINYINT UNSIGNED  NOT NULL DEFAULT 1,
  PRIMARY KEY (`admin_id`, `role_id`),
  KEY `role_id` (`role_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Seed system roles
INSERT IGNORE INTO `roles` (`id`,`tenant_id`,`name`,`slug`,`description`,`is_system`) VALUES
(1, 1, 'Super Admin',  'super_admin',  'Full unrestricted system access', 1),
(2, 1, 'Tenant Admin', 'tenant_admin', 'Full access within tenant',       1),
(3, 1, 'Manager',      'manager',      'Orders, products, reports',        1),
(4, 1, 'Cashier',      'cashier',      'POS terminal only',                1),
(5, 1, 'Accountant',   'accountant',   'Finance, expenses, accounting',    1),
(6, 1, 'Inventory',    'inventory',    'Warehouse and stock management',    1);

-- Seed granular permissions
INSERT IGNORE INTO `permissions` (`module`,`action`,`label`) VALUES
-- POS
('pos','access','Access POS Terminal'),
('pos','hold','Hold Orders'),
('pos','refund','Process Refunds'),
('pos','discount','Apply Discounts'),
-- Orders
('orders','view','View Orders'),
('orders','edit','Edit Orders'),
('orders','delete','Delete Orders'),
('orders','export','Export Orders'),
('orders','refund','Refund Orders'),
-- Products
('products','view','View Products'),
('products','create','Create Products'),
('products','edit','Edit Products'),
('products','delete','Delete Products'),
('products','import','Import Products'),
-- Categories
('categories','manage','Manage Categories'),
-- Customers
('customers','view','View Customers'),
('customers','edit','Edit Customers'),
('customers','delete','Delete Customers'),
-- Coupons
('coupons','manage','Manage Coupons'),
-- Inventory
('inventory','view','View Stock'),
('inventory','adjust','Adjust Stock'),
('inventory','warehouse','Manage Warehouses'),
('inventory','purchase_orders','Manage Purchase Orders'),
('inventory','receive','Receive Stock'),
-- Suppliers
('suppliers','view','View Suppliers'),
('suppliers','create','Create Suppliers'),
('suppliers','edit','Edit Suppliers'),
('suppliers','payment','Record Supplier Payments'),
('suppliers','ledger','View Supplier Ledger'),
-- Accounting
('accounting','view','View Chart of Accounts'),
('accounting','journal','Create Journal Entries'),
('accounting','post','Post Journal Entries'),
('accounting','reports','View Accounting Reports'),
-- Expenses
('expenses','view','View Expenses'),
('expenses','create','Create Expenses'),
('expenses','approve','Approve Expenses'),
('expenses','delete','Delete Expenses'),
-- Reports
('reports','sales','Sales Reports'),
('reports','inventory','Inventory Reports'),
('reports','financial','Financial Reports'),
('reports','tax','Tax Reports'),
('reports','custom','Custom Reports'),
-- Admin
('admin','users','Manage Admin Users'),
('admin','roles','Manage Roles & Permissions'),
('admin','settings','Manage Settings'),
('admin','tenants','Manage Tenants'),
('admin','audit_log','View Audit Log'),
('admin','backup','Backup & Restore');

-- Grant ALL permissions to Super Admin role (id=1)
INSERT IGNORE INTO `role_permissions` (`role_id`, `permission_id`)
SELECT 1, `id` FROM `permissions`;

-- Assign Super Admin role to admin user (id=1)
INSERT IGNORE INTO `admin_roles` (`admin_id`,`role_id`,`tenant_id`) VALUES (1, 1, 1);
-- Assign Cashier role to cashier user (id=2)
INSERT IGNORE INTO `admin_roles` (`admin_id`,`role_id`,`tenant_id`) VALUES (2, 4, 1);

-- =====================================================================
-- PHASE 3 – DOUBLE-ENTRY ACCOUNTING
-- =====================================================================

CREATE TABLE IF NOT EXISTS `chart_of_accounts` (
  `id`              INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id`       TINYINT UNSIGNED NOT NULL DEFAULT 1,
  `account_code`    VARCHAR(20)  NOT NULL,
  `name`            VARCHAR(150) NOT NULL,
  `type`            ENUM('asset','liability','equity','revenue','expense') NOT NULL,
  `sub_type`        VARCHAR(60)  DEFAULT NULL COMMENT 'current, fixed, cogs, operating, etc.',
  `parent_id`       INT UNSIGNED DEFAULT NULL,
  `description`     TEXT         DEFAULT NULL,
  `is_system`       TINYINT(1)   NOT NULL DEFAULT 0 COMMENT 'System accounts cannot be deleted',
  `is_active`       TINYINT(1)   NOT NULL DEFAULT 1,
  `opening_balance` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  `created_at`      TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_code_tenant` (`tenant_id`, `account_code`),
  KEY `tenant_id` (`tenant_id`),
  KEY `type` (`type`),
  KEY `parent_id` (`parent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `journal_entries` (
  `id`             INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id`      TINYINT UNSIGNED NOT NULL DEFAULT 1,
  `entry_number`   VARCHAR(30) NOT NULL,
  `reference_type` ENUM('order','payment','expense','supplier_payment','purchase_order','adjustment','opening','other') NOT NULL DEFAULT 'other',
  `reference_id`   INT UNSIGNED DEFAULT NULL,
  `description`    VARCHAR(255) NOT NULL,
  `entry_date`     DATE NOT NULL,
  `status`         ENUM('draft','posted','voided') NOT NULL DEFAULT 'draft',
  `created_by`     INT UNSIGNED NOT NULL,
  `approved_by`    INT UNSIGNED DEFAULT NULL,
  `voided_by`      INT UNSIGNED DEFAULT NULL,
  `voided_at`      DATETIME     DEFAULT NULL,
  `void_reason`    VARCHAR(255) DEFAULT NULL,
  `created_at`     TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`     TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_entry_num` (`tenant_id`, `entry_number`),
  KEY `tenant_id` (`tenant_id`),
  KEY `reference` (`reference_type`, `reference_id`),
  KEY `entry_date` (`entry_date`),
  KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `journal_items` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `entry_id`    INT UNSIGNED NOT NULL,
  `account_id`  INT UNSIGNED NOT NULL,
  `description` VARCHAR(255) DEFAULT NULL,
  `debit`       DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  `credit`      DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  PRIMARY KEY (`id`),
  KEY `entry_id`   (`entry_id`),
  KEY `account_id` (`account_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Standard Chart of Accounts (Pakistani Rupee context)
INSERT IGNORE INTO `chart_of_accounts` (`tenant_id`,`account_code`,`name`,`type`,`sub_type`,`is_system`) VALUES
-- Assets
(1,'1000','Cash & Bank',              'asset','current',   1),
(1,'1010','Cash in Hand',             'asset','current',   1),
(1,'1020','Bank Account',             'asset','current',   1),
(1,'1100','Accounts Receivable',      'asset','current',   1),
(1,'1200','Inventory / Stock',        'asset','current',   1),
(1,'1300','Prepaid Expenses',         'asset','current',   1),
(1,'1400','Fixed Assets',             'asset','fixed',     1),
-- Liabilities
(1,'2000','Accounts Payable',         'liability','current',1),
(1,'2100','Tax Payable (GST/VAT)',    'liability','current',1),
(1,'2200','Accrued Expenses',         'liability','current',1),
(1,'2300','Bank Loan',                'liability','long_term',1),
-- Equity
(1,'3000','Owner Equity',             'equity','equity',   1),
(1,'3100','Retained Earnings',        'equity','equity',   1),
(1,'3200','Owner Drawings',           'equity','equity',   1),
-- Revenue
(1,'4000','Sales Revenue',            'revenue','operating',1),
(1,'4100','POS Sales',                'revenue','operating',1),
(1,'4200','Online Sales',             'revenue','operating',1),
(1,'4300','Shipping Income',          'revenue','other',    1),
(1,'4400','Discount Received',        'revenue','other',    1),
(1,'4500','Other Income',             'revenue','other',    1),
-- Expenses
(1,'5000','Cost of Goods Sold',       'expense','cogs',     1),
(1,'5100','Salaries & Wages',         'expense','operating',1),
(1,'5200','Rent & Utilities',         'expense','operating',1),
(1,'5300','Marketing & Advertising',  'expense','operating',1),
(1,'5400','Supplies & Stationery',    'expense','operating',1),
(1,'5500','Transportation',           'expense','operating',1),
(1,'5600','Repairs & Maintenance',    'expense','operating',1),
(1,'5700','Insurance',                'expense','operating',1),
(1,'5800','Depreciation',             'expense','operating',1),
(1,'5900','Discount Given',           'expense','operating',1),
(1,'6000','Bank Charges',             'expense','finance',  1),
(1,'6100','Miscellaneous Expenses',   'expense','operating',1);

-- =====================================================================
-- PHASE 4 – INVENTORY UPGRADE (Warehouses + Stock)
-- =====================================================================

CREATE TABLE IF NOT EXISTS `warehouses` (
  `id`         SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id`  TINYINT UNSIGNED  NOT NULL DEFAULT 1,
  `name`       VARCHAR(100) NOT NULL,
  `code`       VARCHAR(20)  NOT NULL,
  `address`    TEXT         DEFAULT NULL,
  `manager_id` INT UNSIGNED DEFAULT NULL,
  `is_default` TINYINT(1)   NOT NULL DEFAULT 0,
  `is_active`  TINYINT(1)   NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_wh_code` (`tenant_id`, `code`),
  KEY `tenant_id` (`tenant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO `warehouses` (`id`,`tenant_id`,`name`,`code`,`is_default`,`is_active`)
VALUES (1, 1, 'Main Warehouse', 'WH-001', 1, 1);

-- Per-warehouse stock (decouples products from single stock_quantity)
CREATE TABLE IF NOT EXISTS `product_warehouse_stock` (
  `product_id`   INT UNSIGNED     NOT NULL,
  `warehouse_id` SMALLINT UNSIGNED NOT NULL,
  `quantity`     INT NOT NULL DEFAULT 0,
  `reserved_qty` INT NOT NULL DEFAULT 0 COMMENT 'Qty reserved for pending orders',
  `last_updated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`product_id`, `warehouse_id`),
  KEY `warehouse_id` (`warehouse_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Sync existing product stock into main warehouse
INSERT IGNORE INTO `product_warehouse_stock` (`product_id`, `warehouse_id`, `quantity`)
SELECT `id`, 1, `stock_quantity` FROM `products`;

-- Full movement/transaction log for every stock change
CREATE TABLE IF NOT EXISTS `stock_movements` (
  `id`              INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id`       TINYINT UNSIGNED NOT NULL DEFAULT 1,
  `product_id`      INT UNSIGNED NOT NULL,
  `warehouse_id`    SMALLINT UNSIGNED NOT NULL DEFAULT 1,
  `type`            ENUM('purchase','sale','adjustment','transfer_in','transfer_out','return','opening','damage') NOT NULL,
  `quantity`        INT NOT NULL COMMENT 'Positive=IN, Negative=OUT',
  `quantity_before` INT NOT NULL DEFAULT 0,
  `quantity_after`  INT NOT NULL DEFAULT 0,
  `unit_cost`       DECIMAL(10,2) DEFAULT NULL,
  `reference_type`  VARCHAR(40)   DEFAULT NULL COMMENT 'order|purchase_order|manual|etc',
  `reference_id`    INT UNSIGNED  DEFAULT NULL,
  `note`            VARCHAR(255)  DEFAULT NULL,
  `created_by`      INT UNSIGNED  DEFAULT NULL,
  `created_at`      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `product_warehouse` (`product_id`, `warehouse_id`),
  KEY `tenant_id`   (`tenant_id`),
  KEY `type`        (`type`),
  KEY `created_at`  (`created_at`),
  KEY `reference`   (`reference_type`, `reference_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Purchase Orders (from suppliers)
CREATE TABLE IF NOT EXISTS `purchase_orders` (
  `id`            INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id`     TINYINT UNSIGNED NOT NULL DEFAULT 1,
  `supplier_id`   INT UNSIGNED NOT NULL,
  `warehouse_id`  SMALLINT UNSIGNED NOT NULL DEFAULT 1,
  `po_number`     VARCHAR(30) NOT NULL,
  `status`        ENUM('draft','sent','partial','received','cancelled') NOT NULL DEFAULT 'draft',
  `order_date`    DATE NOT NULL,
  `expected_date` DATE         DEFAULT NULL,
  `received_date` DATE         DEFAULT NULL,
  `subtotal`      DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `tax_amount`    DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `total_amount`  DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `amount_paid`   DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `notes`         TEXT         DEFAULT NULL,
  `created_by`    INT UNSIGNED NOT NULL,
  `approved_by`   INT UNSIGNED DEFAULT NULL,
  `created_at`    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_po_number` (`tenant_id`, `po_number`),
  KEY `supplier_id`  (`supplier_id`),
  KEY `tenant_id`    (`tenant_id`),
  KEY `status`       (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `purchase_order_items` (
  `id`           INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `po_id`        INT UNSIGNED NOT NULL,
  `product_id`   INT UNSIGNED NOT NULL,
  `product_name` VARCHAR(200) NOT NULL,
  `qty_ordered`  INT         NOT NULL,
  `qty_received` INT         NOT NULL DEFAULT 0,
  `unit_cost`    DECIMAL(10,2) NOT NULL,
  `total_cost`   DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  PRIMARY KEY (`id`),
  KEY `po_id`      (`po_id`),
  KEY `product_id` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Extend products with fields needed for enterprise inventory
ALTER TABLE `products` ADD COLUMN IF NOT EXISTS `cost_price`   DECIMAL(10,2) DEFAULT NULL       AFTER `price`;
ALTER TABLE `products` ADD COLUMN IF NOT EXISTS `barcode`      VARCHAR(60)   DEFAULT NULL       AFTER `sku`;
ALTER TABLE `products` ADD COLUMN IF NOT EXISTS `reorder_point` INT UNSIGNED  DEFAULT 5         AFTER `low_stock_alert`;
ALTER TABLE `products` ADD COLUMN IF NOT EXISTS `unit`         VARCHAR(20)   DEFAULT 'piece'    AFTER `reorder_point`;

-- =====================================================================
-- PHASE 5 – SUPPLIERS / VENDORS
-- =====================================================================

CREATE TABLE IF NOT EXISTS `suppliers` (
  `id`              INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id`       TINYINT UNSIGNED NOT NULL DEFAULT 1,
  `name`            VARCHAR(150) NOT NULL,
  `company`         VARCHAR(150) DEFAULT NULL,
  `contact_person`  VARCHAR(100) DEFAULT NULL,
  `phone`           VARCHAR(25)  DEFAULT NULL,
  `email`           VARCHAR(100) DEFAULT NULL,
  `address`         TEXT         DEFAULT NULL,
  `city`            VARCHAR(60)  DEFAULT NULL,
  `ntn`             VARCHAR(30)  DEFAULT NULL COMMENT 'National Tax Number',
  `opening_balance` DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT 'Positive = we owe them',
  `current_balance` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `credit_limit`    DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `payment_terms`   TINYINT UNSIGNED DEFAULT 30 COMMENT 'Days for payment',
  `notes`           TEXT DEFAULT NULL,
  `is_active`       TINYINT(1) NOT NULL DEFAULT 1,
  `created_at`      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `tenant_id` (`tenant_id`),
  KEY `name`      (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `supplier_transactions` (
  `id`               INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id`        TINYINT UNSIGNED NOT NULL DEFAULT 1,
  `supplier_id`      INT UNSIGNED NOT NULL,
  `type`             ENUM('purchase','payment','credit_note','debit_note','opening') NOT NULL,
  `reference_type`   VARCHAR(40)   DEFAULT NULL COMMENT 'purchase_order|manual|etc',
  `reference_id`     INT UNSIGNED  DEFAULT NULL,
  `amount`           DECIMAL(12,2) NOT NULL,
  `description`      VARCHAR(255)  DEFAULT NULL,
  `transaction_date` DATE          NOT NULL,
  `created_by`       INT UNSIGNED  NOT NULL,
  `created_at`       TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `supplier_id`      (`supplier_id`),
  KEY `tenant_id`        (`tenant_id`),
  KEY `type`             (`type`),
  KEY `transaction_date` (`transaction_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Running ledger (denormalised for fast balance queries)
CREATE TABLE IF NOT EXISTS `supplier_ledger` (
  `id`             INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id`      TINYINT UNSIGNED NOT NULL DEFAULT 1,
  `supplier_id`    INT UNSIGNED NOT NULL,
  `transaction_id` INT UNSIGNED NOT NULL,
  `date`           DATE         NOT NULL,
  `description`    VARCHAR(255) NOT NULL,
  `debit`          DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT 'We paid them',
  `credit`         DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT 'We owe them (purchase)',
  `balance`        DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  PRIMARY KEY (`id`),
  KEY `supplier_id` (`supplier_id`),
  KEY `date`        (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- PHASE 6 – EXPENSES & INCOME TRACKING
-- =====================================================================

CREATE TABLE IF NOT EXISTS `expense_categories` (
  `id`         SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id`  TINYINT UNSIGNED  NOT NULL DEFAULT 1,
  `name`       VARCHAR(100) NOT NULL,
  `parent_id`  SMALLINT UNSIGNED DEFAULT NULL,
  `account_id` INT UNSIGNED      DEFAULT NULL COMMENT 'Links to chart_of_accounts',
  `is_active`  TINYINT(1)   NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`),
  KEY `tenant_id` (`tenant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO `expense_categories` (`id`,`tenant_id`,`name`,`is_active`) VALUES
(1, 1, 'Salaries & Wages',        1),
(2, 1, 'Rent & Utilities',        1),
(3, 1, 'Marketing & Advertising', 1),
(4, 1, 'Supplies & Stationery',   1),
(5, 1, 'Transportation',          1),
(6, 1, 'Repairs & Maintenance',   1),
(7, 1, 'Insurance',               1),
(8, 1, 'Bank Charges',            1),
(9, 1, 'Miscellaneous',           1);

CREATE TABLE IF NOT EXISTS `expenses` (
  `id`             INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id`      TINYINT UNSIGNED NOT NULL DEFAULT 1,
  `category_id`    SMALLINT UNSIGNED NOT NULL,
  `expense_number` VARCHAR(30)   NOT NULL,
  `amount`         DECIMAL(12,2) NOT NULL,
  `description`    VARCHAR(255)  NOT NULL,
  `expense_date`   DATE          NOT NULL,
  `payment_method` ENUM('cash','bank','card','other') NOT NULL DEFAULT 'cash',
  `reference`      VARCHAR(100)  DEFAULT NULL COMMENT 'Receipt or bill number',
  `receipt_image`  VARCHAR(255)  DEFAULT NULL,
  `status`         ENUM('pending','approved','rejected','paid') NOT NULL DEFAULT 'pending',
  `created_by`     INT UNSIGNED  NOT NULL,
  `approved_by`    INT UNSIGNED  DEFAULT NULL,
  `approved_at`    DATETIME      DEFAULT NULL,
  `account_id`     INT UNSIGNED  DEFAULT NULL,
  `created_at`     TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`     TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_exp_num` (`tenant_id`, `expense_number`),
  KEY `tenant_id`    (`tenant_id`),
  KEY `category_id`  (`category_id`),
  KEY `expense_date` (`expense_date`),
  KEY `status`       (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `income_records` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id`   TINYINT UNSIGNED NOT NULL DEFAULT 1,
  `category`    ENUM('sales','other_income','investment','refund_received','commission') NOT NULL DEFAULT 'other_income',
  `amount`      DECIMAL(12,2) NOT NULL,
  `description` VARCHAR(255)  NOT NULL,
  `income_date` DATE          NOT NULL,
  `reference`   VARCHAR(100)  DEFAULT NULL,
  `account_id`  INT UNSIGNED  DEFAULT NULL,
  `created_by`  INT UNSIGNED  NOT NULL,
  `created_at`  TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `tenant_id`   (`tenant_id`),
  KEY `income_date` (`income_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- PHASE 7 – AUDIT LOGS & LOGIN HISTORY
-- =====================================================================

CREATE TABLE IF NOT EXISTS `audit_logs` (
  `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id`   TINYINT UNSIGNED NOT NULL DEFAULT 1,
  `user_type`   ENUM('admin','customer','system') NOT NULL DEFAULT 'admin',
  `user_id`     INT UNSIGNED   DEFAULT NULL,
  `user_name`   VARCHAR(100)   DEFAULT NULL,
  `action`      VARCHAR(60)    NOT NULL COMMENT 'create|update|delete|login|logout|approve|void',
  `module`      VARCHAR(50)    NOT NULL COMMENT 'orders|products|expenses|accounting|etc',
  `record_id`   INT UNSIGNED   DEFAULT NULL,
  `description` VARCHAR(500)   DEFAULT NULL,
  `old_values`  LONGTEXT       DEFAULT NULL COMMENT 'JSON',
  `new_values`  LONGTEXT       DEFAULT NULL COMMENT 'JSON',
  `ip_address`  VARCHAR(45)    DEFAULT NULL,
  `user_agent`  VARCHAR(300)   DEFAULT NULL,
  `created_at`  TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `tenant_id`    (`tenant_id`),
  KEY `user_id`      (`user_id`),
  KEY `module_action`(`module`, `action`),
  KEY `created_at`   (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `login_history` (
  `id`         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id`  TINYINT UNSIGNED NOT NULL DEFAULT 1,
  `user_type`  ENUM('admin','customer') NOT NULL DEFAULT 'admin',
  `user_id`    INT UNSIGNED  NOT NULL,
  `ip_address` VARCHAR(45)   DEFAULT NULL,
  `user_agent` VARCHAR(300)  DEFAULT NULL,
  `status`     ENUM('success','failed','locked') NOT NULL DEFAULT 'success',
  `created_at` TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id`    (`user_id`),
  KEY `tenant_id`  (`tenant_id`),
  KEY `created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- PHASE 8 – PERFORMANCE INDEXES
-- =====================================================================

-- Orders
CREATE INDEX IF NOT EXISTS `idx_orders_tenant_date`   ON `orders` (`tenant_id`, `created_at`);
CREATE INDEX IF NOT EXISTS `idx_orders_tenant_status` ON `orders` (`tenant_id`, `order_status`);
CREATE INDEX IF NOT EXISTS `idx_orders_channel_date`  ON `orders` (`sale_channel`, `created_at`);

-- Order items
CREATE INDEX IF NOT EXISTS `idx_oi_product_date`  ON `order_items` (`product_id`, `created_at`);

-- Products
CREATE INDEX IF NOT EXISTS `idx_products_tenant_active` ON `products` (`tenant_id`, `is_active`);
CREATE INDEX IF NOT EXISTS `idx_products_barcode`       ON `products` (`barcode`);

-- Users
CREATE INDEX IF NOT EXISTS `idx_users_tenant_active` ON `users` (`tenant_id`, `is_active`);

-- Stock movements
CREATE INDEX IF NOT EXISTS `idx_sm_product_wh` ON `stock_movements` (`product_id`, `warehouse_id`);

-- Journal items
CREATE INDEX IF NOT EXISTS `idx_ji_account_entry` ON `journal_items` (`account_id`, `entry_id`);

-- =====================================================================
-- PHASE 9 – SETTINGS FOR NEW MODULES
-- =====================================================================

INSERT IGNORE INTO `settings` (`setting_key`,`setting_value`,`setting_type`,`description`) VALUES
('accounting_enabled',    '1',    'boolean', 'Enable double-entry accounting module'),
('inventory_multi_wh',    '0',    'boolean', 'Enable multi-warehouse inventory'),
('default_tax_rate',      '17',   'number',  'Default sales tax rate (GST 17%)'),
('tax_included_in_price', '0',    'boolean', 'Are prices tax-inclusive?'),
('auto_journal_on_sale',  '1',    'boolean', 'Auto-create journal entry on each sale'),
('auto_journal_on_expense','1',   'boolean', 'Auto-create journal entry on expense approval'),
('expense_approval_required','1', 'boolean', 'Expenses require manager approval'),
('fiscal_year_start',     '07-01','text',    'Fiscal year start (MM-DD)'),
('company_ntn',           '',     'text',    'Company National Tax Number'),
('company_reg',           '',     'text',    'Company registration number');

SET FOREIGN_KEY_CHECKS = 1;

-- =====================================================================
-- VERIFICATION QUERIES (run manually to confirm)
-- =====================================================================
-- SELECT TABLE_NAME, TABLE_ROWS FROM information_schema.TABLES
--   WHERE TABLE_SCHEMA = 'iqommerce'
--   ORDER BY TABLE_NAME;
--
-- SELECT r.name AS role, p.module, p.action
--   FROM role_permissions rp
--   JOIN roles r ON r.id = rp.role_id
--   JOIN permissions p ON p.id = rp.permission_id
--   WHERE r.id = 1
--   ORDER BY p.module, p.action;