<?php
/**
 * Database Migration Script for Medical Store & Multi-Building Pharmacy Application
 * All tables use 'mst_' prefix to ensure ZERO disturbance to legacy pharmacy tables.
 */

$db = new mysqli('localhost', 'root', '', 'hms_data_ci4');
if ($db->connect_error) {
    die("Database connection failed: " . $db->connect_error . "\n");
}

$db->set_charset('utf8mb4');

echo "=== Initializing Medical Store (mst_*) Database Schema ===\n";

$queries = [
    // 1. Stores Master (Multi-Building / Counters with Indian Drug Licenses & GST)
    "CREATE TABLE IF NOT EXISTS `mst_stores` (
        `store_id` INT AUTO_INCREMENT PRIMARY KEY,
        `store_code` VARCHAR(50) NOT NULL UNIQUE,
        `store_name` VARCHAR(150) NOT NULL,
        `building_name` VARCHAR(100) DEFAULT NULL,
        `floor_no` VARCHAR(50) DEFAULT NULL,
        `room_no` VARCHAR(50) DEFAULT NULL,
        `is_main_store` TINYINT(1) DEFAULT 0,
        `drug_license_no_20b` VARCHAR(100) DEFAULT NULL COMMENT 'Form 20-B (Retail Allopathic)',
        `drug_license_no_21b` VARCHAR(100) DEFAULT NULL COMMENT 'Form 21-B (Schedule C & C1)',
        `drug_license_no_20f_x` VARCHAR(100) DEFAULT NULL COMMENT 'Form 20-F / Schedule X Narcotics',
        `gstin` VARCHAR(20) DEFAULT NULL,
        `pan_no` VARCHAR(20) DEFAULT NULL,
        `fssai_no` VARCHAR(30) DEFAULT NULL,
        `state_code` VARCHAR(5) DEFAULT '07' COMMENT '2-digit Indian State Code for GST',
        `state_name` VARCHAR(100) DEFAULT 'Delhi',
        `registered_pharmacist_name` VARCHAR(120) DEFAULT NULL,
        `pharmacist_reg_no` VARCHAR(100) DEFAULT NULL,
        `contact_phone` VARCHAR(50) DEFAULT NULL,
        `contact_email` VARCHAR(100) DEFAULT NULL,
        `address` TEXT DEFAULT NULL,
        `invoice_prefix` VARCHAR(20) DEFAULT 'INV/',
        `next_invoice_no` INT DEFAULT 1001,
        `bank_name` VARCHAR(100) DEFAULT NULL,
        `bank_account_no` VARCHAR(50) DEFAULT NULL,
        `bank_ifsc` VARCHAR(25) DEFAULT NULL,
        `upi_id` VARCHAR(100) DEFAULT NULL,
        `terms_conditions` TEXT DEFAULT NULL,
        `is_active` TINYINT(1) DEFAULT 1,
        `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
        `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;",

    // 2. Store Staff / User Mapping
    "CREATE TABLE IF NOT EXISTS `mst_store_users` (
        `id` INT AUTO_INCREMENT PRIMARY KEY,
        `store_id` INT NOT NULL,
        `user_id` INT NOT NULL,
        `user_role` VARCHAR(50) DEFAULT 'pharmacist' COMMENT 'manager, cashier, pharmacist',
        `is_active` TINYINT(1) DEFAULT 1,
        `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
        UNIQUE KEY `uniq_store_user` (`store_id`, `user_id`),
        INDEX `idx_store` (`store_id`),
        INDEX `idx_user` (`user_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;",

    // 3. Drug / Product Master Catalog (Shared or Store-specific)
    "CREATE TABLE IF NOT EXISTS `mst_items` (
        `item_id` INT AUTO_INCREMENT PRIMARY KEY,
        `item_name` VARCHAR(200) NOT NULL,
        `generic_name` VARCHAR(250) DEFAULT NULL,
        `category` VARCHAR(100) DEFAULT 'Tablet' COMMENT 'Tablet, Capsule, Syrup, Injection, Ointment, Surgical, Consumable, Food',
        `hsn_code` VARCHAR(20) DEFAULT '3004',
        `gst_rate` DECIMAL(5,2) DEFAULT 12.00,
        `unit_pack` VARCHAR(50) DEFAULT '10 Tablets' COMMENT 'e.g. 10 Tablets, 100ml, 1 Vial',
        `units_per_pack` INT DEFAULT 10 COMMENT 'Basic sellable loose units in 1 pack',
        `drug_schedule` VARCHAR(30) DEFAULT 'Schedule H' COMMENT 'OTC, Schedule H, Schedule H1, Schedule X, Narcotic',
        `manufacturer_name` VARCHAR(150) DEFAULT NULL,
        `barcode` VARCHAR(100) DEFAULT NULL,
        `min_reorder_level` INT DEFAULT 10,
        `is_active` TINYINT(1) DEFAULT 1,
        `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
        `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        INDEX `idx_item_name` (`item_name`),
        INDEX `idx_generic` (`generic_name`),
        INDEX `idx_barcode` (`barcode`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;",

    // 4. Batch Inventory Master
    "CREATE TABLE IF NOT EXISTS `mst_batches` (
        `batch_id` INT AUTO_INCREMENT PRIMARY KEY,
        `store_id` INT NOT NULL,
        `item_id` INT NOT NULL,
        `batch_no` VARCHAR(100) NOT NULL,
        `mfg_date` DATE DEFAULT NULL,
        `expiry_date` DATE NOT NULL COMMENT 'Used for FEFO ordering and expired stock locks',
        `mrp` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
        `ptr` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 'Price to Retailer / Purchase rate before GST',
        `purchase_rate_net` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 'Net landing cost after GST and discount',
        `gst_rate` DECIMAL(5,2) DEFAULT 12.00,
        `barcode` VARCHAR(100) DEFAULT NULL,
        `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
        INDEX `idx_store_item` (`store_id`, `item_id`),
        INDEX `idx_batch_no` (`batch_no`),
        INDEX `idx_expiry` (`expiry_date`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;",

    // 5. Store-wise Current Stock Level
    "CREATE TABLE IF NOT EXISTS `mst_stock` (
        `stock_id` INT AUTO_INCREMENT PRIMARY KEY,
        `store_id` INT NOT NULL,
        `item_id` INT NOT NULL,
        `batch_id` INT NOT NULL,
        `current_qty` INT NOT NULL DEFAULT 0 COMMENT 'Stock in basic units (e.g. loose tablets or bottles)',
        `reserved_qty` INT NOT NULL DEFAULT 0,
        `last_updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        UNIQUE KEY `uniq_store_item_batch` (`store_id`, `item_id`, `batch_id`),
        INDEX `idx_store` (`store_id`),
        INDEX `idx_item` (`item_id`),
        INDEX `idx_batch` (`batch_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;",

    // 6. Suppliers / Distributors Master
    "CREATE TABLE IF NOT EXISTS `mst_suppliers` (
        `supplier_id` INT AUTO_INCREMENT PRIMARY KEY,
        `supplier_name` VARCHAR(150) NOT NULL,
        `dl_no_20b` VARCHAR(100) DEFAULT NULL,
        `dl_no_21b` VARCHAR(100) DEFAULT NULL,
        `gstin` VARCHAR(20) DEFAULT NULL,
        `pan_no` VARCHAR(20) DEFAULT NULL,
        `contact_person` VARCHAR(100) DEFAULT NULL,
        `phone` VARCHAR(50) DEFAULT NULL,
        `email` VARCHAR(100) DEFAULT NULL,
        `address` TEXT DEFAULT NULL,
        `state_code` VARCHAR(5) DEFAULT '07',
        `credit_days` INT DEFAULT 30,
        `bank_details` TEXT DEFAULT NULL,
        `is_active` TINYINT(1) DEFAULT 1,
        `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
        INDEX `idx_supplier_name` (`supplier_name`),
        INDEX `idx_gstin` (`gstin`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;",

    // 7. Purchase Inward Master
    "CREATE TABLE IF NOT EXISTS `mst_purchases` (
        `purchase_id` INT AUTO_INCREMENT PRIMARY KEY,
        `store_id` INT NOT NULL,
        `supplier_id` INT NOT NULL,
        `supplier_invoice_no` VARCHAR(100) NOT NULL,
        `invoice_date` DATE NOT NULL,
        `received_date` DATE NOT NULL,
        `due_date` DATE DEFAULT NULL,
        `taxable_amount` DECIMAL(12,2) DEFAULT 0.00,
        `cgst_amount` DECIMAL(10,2) DEFAULT 0.00,
        `sgst_amount` DECIMAL(10,2) DEFAULT 0.00,
        `igst_amount` DECIMAL(10,2) DEFAULT 0.00,
        `discount_amount` DECIMAL(10,2) DEFAULT 0.00,
        `round_off` DECIMAL(5,2) DEFAULT 0.00,
        `net_amount` DECIMAL(12,2) DEFAULT 0.00,
        `paid_amount` DECIMAL(12,2) DEFAULT 0.00,
        `payment_status` VARCHAR(20) DEFAULT 'unpaid' COMMENT 'unpaid, partial, paid',
        `remarks` TEXT DEFAULT NULL,
        `created_by` INT DEFAULT NULL,
        `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
        INDEX `idx_store` (`store_id`),
        INDEX `idx_supplier` (`supplier_id`),
        INDEX `idx_invoice_date` (`invoice_date`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;",

    // 8. Purchase Inward Items
    "CREATE TABLE IF NOT EXISTS `mst_purchase_items` (
        `purchase_item_id` INT AUTO_INCREMENT PRIMARY KEY,
        `purchase_id` INT NOT NULL,
        `item_id` INT NOT NULL,
        `batch_no` VARCHAR(100) NOT NULL,
        `expiry_date` DATE NOT NULL,
        `qty_packs` INT NOT NULL DEFAULT 1,
        `free_qty_packs` INT DEFAULT 0 COMMENT 'e.g. 10 + 1 scheme',
        `units_per_pack` INT DEFAULT 10,
        `total_units` INT NOT NULL,
        `mrp` DECIMAL(10,2) NOT NULL,
        `ptr` DECIMAL(10,2) NOT NULL,
        `discount_pct` DECIMAL(5,2) DEFAULT 0.00,
        `hsn_code` VARCHAR(20) DEFAULT '3004',
        `gst_rate` DECIMAL(5,2) DEFAULT 12.00,
        `taxable_value` DECIMAL(10,2) NOT NULL,
        `cgst_amount` DECIMAL(10,2) DEFAULT 0.00,
        `sgst_amount` DECIMAL(10,2) DEFAULT 0.00,
        `igst_amount` DECIMAL(10,2) DEFAULT 0.00,
        `total_amount` DECIMAL(10,2) NOT NULL,
        `net_unit_landing_cost` DECIMAL(10,2) NOT NULL,
        INDEX `idx_purchase` (`purchase_id`),
        INDEX `idx_item` (`item_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;",

    // 9. Sales Invoices Master (POS & Dispensing)
    "CREATE TABLE IF NOT EXISTS `mst_sales` (
        `sale_id` INT AUTO_INCREMENT PRIMARY KEY,
        `store_id` INT NOT NULL,
        `invoice_no` VARCHAR(100) NOT NULL UNIQUE,
        `sale_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        `patient_type` VARCHAR(30) DEFAULT 'Walk-in' COMMENT 'Walk-in, OPD, IPD',
        `uhid` VARCHAR(50) DEFAULT NULL COMMENT 'patient_master.p_code',
        `patient_id` INT DEFAULT NULL COMMENT 'patient_master.id',
        `opd_id` INT DEFAULT NULL COMMENT 'opd_master.opd_id',
        `ipd_id` INT DEFAULT NULL COMMENT 'ipd_master.id',
        `patient_name` VARCHAR(150) NOT NULL,
        `patient_mobile` VARCHAR(50) DEFAULT NULL,
        `patient_address` TEXT DEFAULT NULL,
        `age` VARCHAR(30) DEFAULT NULL,
        `gender` VARCHAR(20) DEFAULT NULL,
        `doctor_id` INT DEFAULT NULL,
        `doctor_name` VARCHAR(150) DEFAULT NULL,
        `doctor_reg_no` VARCHAR(100) DEFAULT NULL COMMENT 'Mandatory for Schedule H/H1 drugs',
        `ward_name` VARCHAR(100) DEFAULT NULL,
        `bed_no` VARCHAR(50) DEFAULT NULL,
        `gross_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
        `discount_amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
        `taxable_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
        `cgst_amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
        `sgst_amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
        `igst_amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
        `round_off` DECIMAL(5,2) NOT NULL DEFAULT 0.00,
        `net_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
        `payment_mode` VARCHAR(30) DEFAULT 'Cash' COMMENT 'Cash, UPI, Card, Mixed, IPD_Credit',
        `cash_paid` DECIMAL(10,2) DEFAULT 0.00,
        `upi_paid` DECIMAL(10,2) DEFAULT 0.00,
        `card_paid` DECIMAL(10,2) DEFAULT 0.00,
        `credit_amount` DECIMAL(10,2) DEFAULT 0.00,
        `payment_reference` VARCHAR(100) DEFAULT NULL,
        `ipd_charge_id` INT DEFAULT NULL COMMENT 'Reference in ipd_invoice_item if billed to IPD running bill',
        `schedule_h1_flag` TINYINT(1) DEFAULT 0,
        `status` VARCHAR(30) DEFAULT 'completed' COMMENT 'completed, cancelled, refunded',
        `created_by` INT DEFAULT NULL,
        `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
        INDEX `idx_store_date` (`store_id`, `sale_date`),
        INDEX `idx_uhid` (`uhid`),
        INDEX `idx_opd` (`opd_id`),
        INDEX `idx_ipd` (`ipd_id`),
        INDEX `idx_invoice` (`invoice_no`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;",

    // 10. Sales Items Breakdown
    "CREATE TABLE IF NOT EXISTS `mst_sales_items` (
        `sale_item_id` INT AUTO_INCREMENT PRIMARY KEY,
        `sale_id` INT NOT NULL,
        `item_id` INT NOT NULL,
        `batch_id` INT NOT NULL,
        `batch_no` VARCHAR(100) NOT NULL,
        `expiry_date` DATE NOT NULL,
        `qty` INT NOT NULL DEFAULT 1 COMMENT 'Quantity in basic loose units dispensed',
        `unit_mrp` DECIMAL(10,2) NOT NULL,
        `discount_pct` DECIMAL(5,2) DEFAULT 0.00,
        `discount_amount` DECIMAL(10,2) DEFAULT 0.00,
        `hsn_code` VARCHAR(20) DEFAULT '3004',
        `gst_rate` DECIMAL(5,2) DEFAULT 12.00,
        `taxable_value` DECIMAL(10,2) NOT NULL,
        `cgst_amount` DECIMAL(10,2) DEFAULT 0.00,
        `sgst_amount` DECIMAL(10,2) DEFAULT 0.00,
        `igst_amount` DECIMAL(10,2) DEFAULT 0.00,
        `total_amount` DECIMAL(10,2) NOT NULL,
        INDEX `idx_sale` (`sale_id`),
        INDEX `idx_item` (`item_id`),
        INDEX `idx_batch` (`batch_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;",

    // 11. Multi-Building Inter-Store Transfers & Indents
    "CREATE TABLE IF NOT EXISTS `mst_transfers` (
        `transfer_id` INT AUTO_INCREMENT PRIMARY KEY,
        `transfer_no` VARCHAR(100) NOT NULL UNIQUE,
        `from_store_id` INT NOT NULL,
        `to_store_id` INT NOT NULL,
        `status` VARCHAR(30) DEFAULT 'requested' COMMENT 'requested, approved, dispatched, received, rejected',
        `requested_by` INT DEFAULT NULL,
        `dispatched_by` INT DEFAULT NULL,
        `received_by` INT DEFAULT NULL,
        `request_date` DATETIME DEFAULT CURRENT_TIMESTAMP,
        `dispatch_date` DATETIME DEFAULT NULL,
        `receive_date` DATETIME DEFAULT NULL,
        `remarks` TEXT DEFAULT NULL,
        INDEX `idx_from_to` (`from_store_id`, `to_store_id`),
        INDEX `idx_status` (`status`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;",

    // 12. Transfer Items
    "CREATE TABLE IF NOT EXISTS `mst_transfer_items` (
        `transfer_item_id` INT AUTO_INCREMENT PRIMARY KEY,
        `transfer_id` INT NOT NULL,
        `item_id` INT NOT NULL,
        `batch_id` INT DEFAULT NULL,
        `requested_qty` INT NOT NULL,
        `dispatched_qty` INT DEFAULT 0,
        `received_qty` INT DEFAULT 0,
        INDEX `idx_transfer` (`transfer_id`),
        INDEX `idx_item` (`item_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;",

    // 13. Chart of Accounts for Indian Pharmacy Double-Entry
    "CREATE TABLE IF NOT EXISTS `mst_account_heads` (
        `head_id` INT AUTO_INCREMENT PRIMARY KEY,
        `head_code` VARCHAR(50) NOT NULL UNIQUE,
        `head_name` VARCHAR(150) NOT NULL,
        `head_type` VARCHAR(50) NOT NULL COMMENT 'Asset, Liability, Equity, Income, Expense',
        `parent_id` INT DEFAULT NULL,
        `is_system` TINYINT(1) DEFAULT 1,
        `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;",

    // 14. Ledger Entries / Journal Vouchers
    "CREATE TABLE IF NOT EXISTS `mst_ledger_entries` (
        `entry_id` INT AUTO_INCREMENT PRIMARY KEY,
        `store_id` INT NOT NULL,
        `voucher_no` VARCHAR(100) NOT NULL,
        `voucher_type` VARCHAR(50) NOT NULL COMMENT 'SALE, PURCHASE, PAYMENT, RECEIPT, CONTRA, ADJUSTMENT',
        `voucher_date` DATE NOT NULL,
        `account_head_id` INT NOT NULL,
        `debit_amount` DECIMAL(12,2) DEFAULT 0.00,
        `credit_amount` DECIMAL(12,2) DEFAULT 0.00,
        `reference_type` VARCHAR(50) DEFAULT NULL COMMENT 'sales_invoice, purchase_invoice, supplier_payment, patient_credit',
        `reference_id` INT DEFAULT NULL,
        `narration` TEXT DEFAULT NULL,
        `created_by` INT DEFAULT NULL,
        `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
        INDEX `idx_store_date` (`store_id`, `voucher_date`),
        INDEX `idx_head` (`account_head_id`),
        INDEX `idx_voucher` (`voucher_no`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;",

    // 15. Stock Audit & Adjustments (Physical Verification, Expiry Dump, Opening Stock)
    "CREATE TABLE IF NOT EXISTS `mst_stock_audit` (
        `audit_id` INT AUTO_INCREMENT PRIMARY KEY,
        `store_id` INT NOT NULL,
        `item_id` INT NOT NULL,
        `batch_id` INT NOT NULL,
        `audit_type` VARCHAR(50) NOT NULL COMMENT 'OPENING_STOCK, DAMAGE, BREAKAGE, EXPIRY_DUMP, PHYSICAL_VARIATION',
        `system_qty` INT NOT NULL DEFAULT 0,
        `physical_qty` INT NOT NULL DEFAULT 0,
        `variation_qty` INT NOT NULL DEFAULT 0,
        `rate` DECIMAL(10,2) DEFAULT 0.00,
        `total_value` DECIMAL(12,2) DEFAULT 0.00,
        `remarks` TEXT DEFAULT NULL,
        `conducted_by` INT DEFAULT NULL,
        `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
        INDEX `idx_store_audit` (`store_id`, `audit_type`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;"
];

foreach ($queries as $q) {
    if (!$db->query($q)) {
        echo "Error executing table creation: " . $db->error . "\n";
        exit(1);
    }
}
echo "All 15 Medical Store (mst_*) tables created successfully.\n";

// Seed System Account Heads
$systemHeads = [
    ['1001', 'Cash in Hand', 'Asset'],
    ['1002', 'Bank Account / UPI Clearing', 'Asset'],
    ['1003', 'Stock in Hand / Inventory Valuation', 'Asset'],
    ['1004', 'Sundry Debtors / Patient Receivables', 'Asset'],
    ['1005', 'IPD Credit Hospital Running Ledger', 'Asset'],
    ['2001', 'Sundry Creditors / Supplier Accounts', 'Liability'],
    ['2002', 'Output CGST Payable', 'Liability'],
    ['2003', 'Output SGST Payable', 'Liability'],
    ['2004', 'Output IGST Payable', 'Liability'],
    ['1006', 'Input CGST Credit', 'Asset'],
    ['1007', 'Input SGST Credit', 'Asset'],
    ['1008', 'Input IGST Credit', 'Asset'],
    ['3001', 'Pharmacy Sales Account (Taxable)', 'Income'],
    ['3002', 'Pharmacy Sales Account (Exempt)', 'Income'],
    ['4001', 'Cost of Goods Sold / Purchase Inward', 'Expense'],
    ['4002', 'Discounts Allowed on Sales', 'Expense'],
    ['3003', 'Discounts Received on Purchases', 'Income'],
    ['4003', 'Medicine Expiry & Breakage Loss', 'Expense'],
    ['3004', 'Opening Stock Capital Balance', 'Equity']
];

$headStmt = $db->prepare("INSERT IGNORE INTO `mst_account_heads` (`head_code`, `head_name`, `head_type`, `is_system`) VALUES (?, ?, ?, 1)");
foreach ($systemHeads as $h) {
    $headStmt->bind_param("sss", $h[0], $h[1], $h[2]);
    $headStmt->execute();
}
echo "System Chart of Accounts seeded.\n";

// Seed Default Stores (Central Pharmacy + OPD Building Counter)
$checkStore = $db->query("SELECT COUNT(*) as cnt FROM `mst_stores`");
$storeCount = $checkStore->fetch_assoc()['cnt'];

if ($storeCount == 0) {
    $store1 = [
        'store_code' => 'ST-MAIN',
        'store_name' => 'City Hospital Central Pharmacy',
        'building_name' => 'Main Hospital Block A',
        'floor_no' => 'Ground Floor',
        'room_no' => 'Room 101',
        'is_main_store' => 1,
        'drug_license_no_20b' => 'DL-20B-DEL-10928',
        'drug_license_no_21b' => 'DL-21B-DEL-10929',
        'gstin' => '07AAAAA0000A1Z5',
        'pan_no' => 'AAAAA0000A',
        'fssai_no' => '10020011000123',
        'state_code' => '07',
        'state_name' => 'Delhi',
        'registered_pharmacist_name' => 'Rajesh Kumar, B.Pharm',
        'pharmacist_reg_no' => 'DPC-Reg-45892',
        'contact_phone' => '+91 9876543210',
        'contact_email' => 'pharmacy@cityhospital.com',
        'address' => 'Plot 12, Medical Enclave, Main Hospital Block, Delhi',
        'invoice_prefix' => 'MAIN/26-27/',
        'next_invoice_no' => 1001,
        'upi_id' => 'cityhospital@upi',
        'terms_conditions' => '1. Goods once sold will be returned as per Drug Rules within 7 days with bill.\n2. Keep medicines stored below 25°C away from direct sunlight.'
    ];

    $store2 = [
        'store_code' => 'ST-OPD-B2',
        'store_name' => 'City Medicos - OPD Building Counter',
        'building_name' => 'OPD & Diagnostic Block B',
        'floor_no' => '1st Floor',
        'room_no' => 'Counter 2',
        'is_main_store' => 0,
        'drug_license_no_20b' => 'DL-20B-DEL-11450',
        'drug_license_no_21b' => 'DL-21B-DEL-11451',
        'gstin' => '07BBBBB1111B2Z8',
        'pan_no' => 'BBBBB1111B',
        'fssai_no' => '10020011000456',
        'state_code' => '07',
        'state_name' => 'Delhi',
        'registered_pharmacist_name' => 'Sunil Sharma, D.Pharm',
        'pharmacist_reg_no' => 'DPC-Reg-67210',
        'contact_phone' => '+91 9811223344',
        'contact_email' => 'opdpharmacy@cityhospital.com',
        'address' => 'OPD Wing, Block B, 1st Floor, Medical Enclave, Delhi',
        'invoice_prefix' => 'OPD/26-27/',
        'next_invoice_no' => 5001,
        'upi_id' => 'citymedicos@upi',
        'terms_conditions' => '1. Check expiry and batch at counter.\n2. No exchange on Schedule H/H1 drugs without valid prescription.'
    ];

    $insertStore = $db->prepare("INSERT INTO `mst_stores` (
        `store_code`, `store_name`, `building_name`, `floor_no`, `room_no`, `is_main_store`,
        `drug_license_no_20b`, `drug_license_no_21b`, `gstin`, `pan_no`, `fssai_no`,
        `state_code`, `state_name`, `registered_pharmacist_name`, `pharmacist_reg_no`,
        `contact_phone`, `contact_email`, `address`, `invoice_prefix`, `next_invoice_no`, `upi_id`, `terms_conditions`
    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

    foreach ([$store1, $store2] as $s) {
        $insertStore->bind_param(
            "sssssisssssssssssssiss",
            $s['store_code'], $s['store_name'], $s['building_name'], $s['floor_no'], $s['room_no'], $s['is_main_store'],
            $s['drug_license_no_20b'], $s['drug_license_no_21b'], $s['gstin'], $s['pan_no'], $s['fssai_no'],
            $s['state_code'], $s['state_name'], $s['registered_pharmacist_name'], $s['pharmacist_reg_no'],
            $s['contact_phone'], $s['contact_email'], $s['address'], $s['invoice_prefix'], $s['next_invoice_no'],
            $s['upi_id'], $s['terms_conditions']
        );
        $insertStore->execute();
    }
    echo "Default Stores (Central Pharmacy + OPD Counter) seeded.\n";
}

// Seed sample standard Indian medicines into mst_items & batches
$checkItems = $db->query("SELECT COUNT(*) as cnt FROM `mst_items`");
if ($checkItems->fetch_assoc()['cnt'] == 0) {
    $sampleItems = [
        ['Augmentin 625 Duo Tablet', 'Amoxycillin (500mg) + Clavulanic Acid (125mg)', 'Tablet', '3004', 12.00, '10 Tablets', 10, 'Schedule H1', 'GSK Pharmaceuticals', '890103000001', 20],
        ['Pan 40 Tablet', 'Pantoprazole (40mg)', 'Tablet', '3004', 12.00, '15 Tablets', 15, 'Schedule H', 'Alkem Laboratories', '890103000002', 30],
        ['Dolo 650 Tablet', 'Paracetamol (650mg)', 'Tablet', '3004', 12.00, '15 Tablets', 15, 'OTC', 'Micro Labs Ltd', '890103000003', 50],
        ['Azithral 500 Tablet', 'Azithromycin (500mg)', 'Tablet', '3004', 12.00, '5 Tablets', 5, 'Schedule H1', 'Alembic Pharmaceuticals', '890103000004', 15],
        ['Monocef 1g Injection', 'Ceftriaxone (1000mg)', 'Injection', '3004', 12.00, '1 Vial', 1, 'Schedule H1', 'Aristo Pharmaceuticals', '890103000005', 25],
        ['Betadine 10% Ointment', 'Povidone Iodine (10% w/w)', 'Ointment', '3004', 12.00, '20 gm Tube', 1, 'OTC', 'Win-Medicare', '890103000006', 15],
        ['Ascoril LS Syrup', 'Levosalbutamol + Ambroxol + Guaifenesin', 'Syrup', '3004', 12.00, '100 ml Bottle', 1, 'Schedule H', 'Glenmark Pharmaceuticals', '890103000007', 20],
        ['Telma 40 Tablet', 'Telmisartan (40mg)', 'Tablet', '3004', 12.00, '30 Tablets', 30, 'Schedule H', 'Glenmark Pharmaceuticals', '890103000008', 25],
        ['Normal Saline 0.9% IV', 'Sodium Chloride (0.9% w/v)', 'Consumable', '3004', 12.00, '500 ml Bottle', 1, 'Schedule H', 'Aculife Healthcare', '890103000009', 40]
    ];

    $itemStmt = $db->prepare("INSERT INTO `mst_items` (`item_name`, `generic_name`, `category`, `hsn_code`, `gst_rate`, `unit_pack`, `units_per_pack`, `drug_schedule`, `manufacturer_name`, `barcode`, `min_reorder_level`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
    foreach ($sampleItems as $it) {
        $itemStmt->bind_param("ssssdsisssi", $it[0], $it[1], $it[2], $it[3], $it[4], $it[5], $it[6], $it[7], $it[8], $it[9], $it[10]);
        $itemStmt->execute();
    }
    echo "Sample Indian Medicines Catalog seeded.\n";

    // Seed Batches and Stock for Store 1 and Store 2
    // Batch 1: Augmentin 625 (FEFO test: Batch A expiring sooner, Batch B expiring later)
    $db->query("INSERT INTO `mst_batches` (`store_id`, `item_id`, `batch_no`, `mfg_date`, `expiry_date`, `mrp`, `ptr`, `purchase_rate_net`, `gst_rate`) VALUES
        (1, 1, 'AUG-2401', '2024-01-01', '2026-10-31', 204.50, 155.00, 173.60, 12.00),
        (1, 1, 'AUG-2409', '2024-09-01', '2027-04-30', 204.50, 155.00, 173.60, 12.00),
        (1, 2, 'PAN-5512', '2024-03-01', '2027-02-28', 162.00, 115.00, 128.80, 12.00),
        (1, 3, 'DOL-9901', '2024-06-01', '2027-06-30', 34.00, 22.50, 25.20, 12.00),
        (1, 4, 'AZI-3321', '2024-02-01', '2026-12-31', 125.00, 89.00, 99.68, 12.00),
        (1, 5, 'MON-8810', '2024-05-01', '2026-11-30', 68.00, 48.00, 53.76, 12.00),
        (2, 1, 'AUG-2401', '2024-01-01', '2026-10-31', 204.50, 155.00, 173.60, 12.00),
        (2, 2, 'PAN-5512', '2024-03-01', '2027-02-28', 162.00, 115.00, 128.80, 12.00),
        (2, 3, 'DOL-9901', '2024-06-01', '2027-06-30', 34.00, 22.50, 25.20, 12.00)
    ");

    // Stock quantities in basic units
    $db->query("INSERT INTO `mst_stock` (`store_id`, `item_id`, `batch_id`, `current_qty`) VALUES
        (1, 1, 1, 150),
        (1, 1, 2, 200),
        (1, 2, 3, 300),
        (1, 3, 4, 450),
        (1, 4, 5, 120),
        (1, 5, 6, 80),
        (2, 1, 7, 50),
        (2, 2, 8, 90),
        (2, 3, 9, 150)
    ");
    echo "Initial Sample Batches & Stock seeded.\n";
}

// Seed Sample Supplier
$checkSup = $db->query("SELECT COUNT(*) as cnt FROM `mst_suppliers`");
if ($checkSup->fetch_assoc()['cnt'] == 0) {
    $db->query("INSERT INTO `mst_suppliers` (
        `supplier_name`, `dl_no_20b`, `dl_no_21b`, `gstin`, `pan_no`, `contact_person`,
        `phone`, `email`, `address`, `state_code`, `credit_days`
    ) VALUES (
        'Apex Healthcare Distributors', 'DL-20B-DEL-98441', 'DL-21B-DEL-98442', '07AACCA1234F1Z9',
        'AACCA1234F', 'Ramesh Gupta', '+91 9811002233', 'sales@apexhealth.in',
        '45, Wholesale Medicine Market, Bhagirath Palace, Chandni Chowk, Delhi', '07', 30
    )");
    echo "Sample Supplier seeded.\n";
}

$db->close();
echo "=== Medical Store Migration Completed Successfully ===\n";