-- ============================================================
-- Al Ahmad Fans (Pvt) Ltd. — Accounts & Production ERP v2.0
-- Complete MySQL Database Schema for cPanel & Production Hosting
-- Compatible with MySQL 5.7+, MySQL 8.0+, MariaDB 10.3+
-- Full UTF-8 MB4 Support for Urdu Nastaliq Titling
-- ============================================================

-- Tables are created directly inside the active cPanel database (smigroup_alahmaddb)


-- ------------------------------------------------------------
-- 1. System Users & Access Control
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `users` (
  `id` VARCHAR(100) NOT NULL PRIMARY KEY,
  `username` VARCHAR(100) NOT NULL UNIQUE,
  `password_hash` VARCHAR(255) NOT NULL,
  `full_name` VARCHAR(150) NOT NULL DEFAULT 'Administrator',
  `role` ENUM('admin', 'accountant', 'sales', 'store', 'viewer') NOT NULL DEFAULT 'admin',
  `is_active` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_users_username` (`username`),
  INDEX `idx_users_role` (`role`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 2. Chart of Accounts Master (frmChart / mnuchart)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `accounts` (
  `ac_no` INT NOT NULL PRIMARY KEY,
  `ac_name` VARCHAR(200) NOT NULL,
  `urdu_name` VARCHAR(250) DEFAULT NULL,
  `status` ENUM('Customer', 'Vendor', 'Bank', 'Expense', 'Capital', 'Employee', 'Income') NOT NULL DEFAULT 'Customer',
  `category` VARCHAR(50) DEFAULT 'Distributor',
  `type` ENUM('Local', 'Export', 'Bad Debts', 'Misc') NOT NULL DEFAULT 'Local',
  `city` VARCHAR(100) DEFAULT 'Gujranwala',
  `phone` VARCHAR(50) DEFAULT NULL,
  `mobile` VARCHAR(50) DEFAULT NULL,
  `address` TEXT DEFAULT NULL,
  `credit_limit` DECIMAL(15, 2) DEFAULT 0.00,
  `balance` DECIMAL(15, 2) DEFAULT 0.00,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_accounts_status` (`status`),
  INDEX `idx_accounts_name` (`ac_name`),
  INDEX `idx_accounts_city` (`city`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 3. Authorized Managers / Contact Persons per Account
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `account_managers` (
  `id` VARCHAR(100) NOT NULL PRIMARY KEY,
  `ac_no` INT NOT NULL,
  `name` VARCHAR(150) NOT NULL,
  `phone` VARCHAR(50) NOT NULL,
  `designation` VARCHAR(100) DEFAULT 'Manager',
  `email` VARCHAR(150) DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_mgr_ac_no` (`ac_no`),
  CONSTRAINT `fk_mgr_account` FOREIGN KEY (`ac_no`) REFERENCES `accounts` (`ac_no`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 4. Product Catalog & Materials Master (frmProduct / mnuprod)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `products` (
  `p_code` VARCHAR(50) NOT NULL PRIMARY KEY,
  `p_name` VARCHAR(200) NOT NULL,
  `urdu_name` VARCHAR(250) DEFAULT NULL,
  `category` ENUM('Finished Goods', 'Raw Material', 'Semi Finish', 'Store') NOT NULL DEFAULT 'Finished Goods',
  `unit` ENUM('Pcs', 'Kg', 'Set', 'Doz', 'Mtr') NOT NULL DEFAULT 'Pcs',
  `s_rate` DECIMAL(12, 2) NOT NULL DEFAULT 0.00,
  `avg_price` DECIMAL(12, 2) NOT NULL DEFAULT 0.00,
  `last_rate` DECIMAL(12, 2) NOT NULL DEFAULT 0.00,
  `p_cost` DECIMAL(12, 2) NOT NULL DEFAULT 0.00,
  `min_stock` INT NOT NULL DEFAULT 50,
  `current_stock` INT NOT NULL DEFAULT 0,
  `deptt` VARCHAR(50) NOT NULL DEFAULT 'Store',
  `weight` DECIMAL(8, 3) DEFAULT 0.000,
  `purchase_unit` VARCHAR(20) DEFAULT 'Pcs',
  `consume_unit` VARCHAR(20) DEFAULT 'Pcs',
  `item_class` VARCHAR(50) DEFAULT 'Finished Fan',
  `size` VARCHAR(50) DEFAULT '56"',
  `purchase_rate` DECIMAL(12, 2) DEFAULT 0.00,
  `weight_packing` VARCHAR(100) DEFAULT '1 Carton / 2 Pcs',
  `weight_per_pcs` VARCHAR(50) DEFAULT '4.8 Kg',
  `reorder_qty` INT DEFAULT 100,
  `model` VARCHAR(100) DEFAULT 'Deluxe 2026',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_products_cat` (`category`),
  INDEX `idx_products_deptt` (`deptt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 5. General Ledger (Double-Entry Financial Book)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `ledger_entries` (
  `id` VARCHAR(100) NOT NULL PRIMARY KEY,
  `date` DATE NOT NULL,
  `ac_no` INT NOT NULL,
  `ac_name` VARCHAR(200) NOT NULL,
  `dr` DECIMAL(15, 2) NOT NULL DEFAULT 0.00,
  `cr` DECIMAL(15, 2) NOT NULL DEFAULT 0.00,
  `description` TEXT NOT NULL,
  `db_entry_no` INT DEFAULT NULL,
  `bnk_entry_no` INT DEFAULT NULL,
  `jv_no` INT DEFAULT NULL,
  `s_inv_no` INT DEFAULT NULL,
  `ls_inv_no` INT DEFAULT NULL,
  `sr_inv_no` INT DEFAULT NULL,
  `p_inv_no` INT DEFAULT NULL,
  `pr_inv_no` INT DEFAULT NULL,
  `e_inv_no` INT DEFAULT NULL,
  `op_bal_no` INT DEFAULT NULL,
  `position` VARCHAR(20) NOT NULL DEFAULT 'Regular',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_ledger_date` (`date`),
  INDEX `idx_ledger_ac_no` (`ac_no`),
  INDEX `idx_ledger_inv` (`s_inv_no`, `p_inv_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 6. Sales Invoices (Dealer & Local Cash Counter)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `invoices` (
  `inv_no` INT NOT NULL PRIMARY KEY,
  `date` DATE NOT NULL,
  `ac_no` INT NOT NULL,
  `ac_name` VARCHAR(200) NOT NULL,
  `inv_type` ENUM('Dealer Sale', 'Local Counter', 'Sales Return', 'Export') NOT NULL DEFAULT 'Dealer Sale',
  `total_qty` INT NOT NULL DEFAULT 0,
  `gross_amount` DECIMAL(15, 2) NOT NULL DEFAULT 0.00,
  `discount` DECIMAL(15, 2) NOT NULL DEFAULT 0.00,
  `net_amount` DECIMAL(15, 2) NOT NULL DEFAULT 0.00,
  `payment_mode` VARCHAR(50) DEFAULT 'Credit',
  `remarks` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_invoices_date` (`date`),
  INDEX `idx_invoices_ac` (`ac_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 7. Invoice Detail Line Items
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `invoice_items` (
  `id` VARCHAR(100) NOT NULL PRIMARY KEY,
  `inv_no` INT NOT NULL,
  `p_code` VARCHAR(50) NOT NULL,
  `p_name` VARCHAR(200) NOT NULL,
  `urdu_name` VARCHAR(250) DEFAULT NULL,
  `qty` INT NOT NULL,
  `rate` DECIMAL(12, 2) NOT NULL,
  `amount` DECIMAL(15, 2) NOT NULL,
  INDEX `idx_item_inv` (`inv_no`),
  CONSTRAINT `fk_item_invoice` FOREIGN KEY (`inv_no`) REFERENCES `invoices` (`inv_no`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 8. Warehouse Stock Vouchers (MRV Inward / MIV Outward)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `stock_entries` (
  `id` VARCHAR(100) NOT NULL PRIMARY KEY,
  `date` DATE NOT NULL,
  `p_code` VARCHAR(50) NOT NULL,
  `p_name` VARCHAR(200) NOT NULL,
  `deptt` VARCHAR(50) NOT NULL,
  `qty_in` INT NOT NULL DEFAULT 0,
  `qty_out` INT NOT NULL DEFAULT 0,
  `rate` DECIMAL(12, 2) DEFAULT 0.00,
  `vch_type` VARCHAR(20) NOT NULL DEFAULT 'MRV',
  `vch_no` INT DEFAULT NULL,
  `remarks` TEXT DEFAULT NULL,
  `position` VARCHAR(20) DEFAULT 'Regular',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_stock_pcode` (`p_code`),
  INDEX `idx_stock_date` (`date`),
  INDEX `idx_stock_vch` (`vch_type`, `vch_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 9. Weighbridge Weight Scale Records (frmWeight)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `weighbridge_records` (
  `slip_no` INT NOT NULL PRIMARY KEY,
  `date` DATE NOT NULL,
  `vehicle_no` VARCHAR(50) NOT NULL,
  `party_name` VARCHAR(200) NOT NULL,
  `item_desc` VARCHAR(200) NOT NULL,
  `gross_weight` DECIMAL(10, 2) NOT NULL,
  `tare_weight` DECIMAL(10, 2) NOT NULL,
  `net_weight` DECIMAL(10, 2) NOT NULL,
  `driver_name` VARCHAR(100) DEFAULT NULL,
  `charges` DECIMAL(8, 2) DEFAULT 150.00,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_scale_date` (`date`),
  INDEX `idx_scale_veh` (`vehicle_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 10. Fan Assembling Bill of Materials (BOM Recipes)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `fan_bom` (
  `id` VARCHAR(100) NOT NULL PRIMARY KEY,
  `fan_p_code` VARCHAR(50) NOT NULL,
  `raw_p_code` VARCHAR(50) NOT NULL,
  `raw_p_name` VARCHAR(200) NOT NULL,
  `qty_required` DECIMAL(10, 3) NOT NULL,
  `unit` VARCHAR(20) NOT NULL,
  INDEX `idx_bom_fan` (`fan_p_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 11. Bi-Directional Offline Sync Mutation Journal
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `sync_log` (
  `id` VARCHAR(100) NOT NULL PRIMARY KEY,
  `client_mutation_id` VARCHAR(100) NOT NULL,
  `table_name` VARCHAR(50) NOT NULL,
  `action` ENUM('INSERT', 'UPDATE', 'DELETE') NOT NULL,
  `payload` LONGTEXT NOT NULL,
  `synced_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_sync_table` (`table_name`),
  INDEX `idx_sync_client_id` (`client_mutation_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- SEED DATA INITIALIZATION
-- ============================================================

-- Seed Default Admin User (admin / admin123)
INSERT INTO `users` (`id`, `username`, `password_hash`, `full_name`, `role`)
VALUES ('USR-001', 'admin', '$2a$10$wK1mJz3B9sF8x8y0k4r7yeFqP7i8qP8q8y0k4r7yeFqP7i8qP8q8y', 'Al-Ahmad ERP Administrator', 'admin')
ON DUPLICATE KEY UPDATE `full_name` = VALUES(`full_name`);