<?php
declare(strict_types=1);

function run_migrations(PDO $pdo): void {
    $pdo->exec('CREATE TABLE IF NOT EXISTS schema_migrations (version VARCHAR(80) PRIMARY KEY, applied_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4');
    $applied=function(string $version)use($pdo):bool{$s=$pdo->prepare('SELECT COUNT(*) FROM schema_migrations WHERE version=?');$s->execute([$version]);return (int)$s->fetchColumn()>0;};
    $complete=function(string $version)use($pdo):void{$pdo->prepare('INSERT IGNORE INTO schema_migrations (version) VALUES (?)')->execute([$version]);};

    if(!$applied('20260713_zimbabwe_payments')){
        $pdo->exec("CREATE TABLE IF NOT EXISTS payment_accounts (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(120) NOT NULL,type ENUM('cash','mobile_money','bank','card','online','other') NOT NULL DEFAULT 'other',currency_code CHAR(3) NOT NULL DEFAULT 'USD',details VARCHAR(255) NULL,active TINYINT(1) NOT NULL DEFAULT 1,created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,UNIQUE KEY uq_payment_account (name,currency_code)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
        $columns=['account_id'=>'BIGINT UNSIGNED NULL AFTER invoice_id','received_amount'=>'DECIMAL(15,2) NULL AFTER amount','received_currency'=>'CHAR(3) NULL AFTER received_amount','exchange_rate'=>'DECIMAL(18,6) NOT NULL DEFAULT 1.000000 AFTER received_currency','fee_amount'=>'DECIMAL(13,2) NOT NULL DEFAULT 0.00 AFTER exchange_rate'];
        $columnCheck=$pdo->prepare('SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME="payments" AND COLUMN_NAME=?');foreach($columns as $name=>$definition){$columnCheck->execute([$name]);if(!(int)$columnCheck->fetchColumn())$pdo->exec("ALTER TABLE payments ADD COLUMN {$name} {$definition}");}
        $fk=$pdo->prepare('SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS WHERE CONSTRAINT_SCHEMA=DATABASE() AND TABLE_NAME="payments" AND CONSTRAINT_NAME="fk_payments_account"');$fk->execute();if(!(int)$fk->fetchColumn())$pdo->exec('ALTER TABLE payments ADD CONSTRAINT fk_payments_account FOREIGN KEY (account_id) REFERENCES payment_accounts(id) ON UPDATE CASCADE ON DELETE SET NULL');
        $seed=$pdo->prepare('INSERT IGNORE INTO payment_accounts (name,type,currency_code,details) VALUES (?,?,?,?)');foreach([
            ['Cash USD','cash','USD','Physical cash'],['EcoCash USD','mobile_money','USD','EcoCash merchant or wallet'],['Bank Transfer USD','bank','USD','Nostro / FCA transfer'],['POS / Swipe USD','card','USD','Zimswitch or card terminal'],['Paynow USD','online','USD','Online settlement'],['OneMoney USD','mobile_money','USD','OneMoney wallet'],['InnBucks USD','mobile_money','USD','InnBucks wallet'],["O'mari USD",'mobile_money','USD',"O'mari wallet"],['Cash ZiG','cash','ZWG','Physical ZiG cash'],['EcoCash ZiG','mobile_money','ZWG','EcoCash ZiG wallet'],['Bank Transfer ZiG','bank','ZWG','Local ZiG transfer'],['POS / Swipe ZiG','card','ZWG','Local card terminal']
        ] as $row)$seed->execute($row);
        $complete('20260713_zimbabwe_payments');
    }

    if(!$applied('20260713_suppliers_and_bills')){
        $pdo->exec("CREATE TABLE IF NOT EXISTS suppliers (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(180) NOT NULL,contact_name VARCHAR(160) NULL,email VARCHAR(190) NULL,phone VARCHAR(60) NULL,tax_number VARCHAR(80) NULL,address VARCHAR(255) NULL,notes TEXT NULL,active TINYINT(1) NOT NULL DEFAULT 1,created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,KEY idx_supplier_name (name)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
        $pdo->exec("CREATE TABLE IF NOT EXISTS bills (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,supplier_id BIGINT UNSIGNED NOT NULL,number VARCHAR(100) NOT NULL,bill_date DATE NOT NULL,due_date DATE NOT NULL,description VARCHAR(255) NOT NULL,currency_code CHAR(3) NOT NULL DEFAULT 'USD',total DECIMAL(15,2) NOT NULL DEFAULT 0,status ENUM('unpaid','partial','paid','cancelled') NOT NULL DEFAULT 'unpaid',reference VARCHAR(120) NULL,notes TEXT NULL,created_by BIGINT UNSIGNED NOT NULL,created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,UNIQUE KEY uq_bill_supplier_number (supplier_id,number),KEY idx_bill_due (due_date,status),CONSTRAINT fk_bill_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers(id),CONSTRAINT fk_bill_user FOREIGN KEY (created_by) REFERENCES users(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
        $pdo->exec("CREATE TABLE IF NOT EXISTS bill_payments (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,bill_id BIGINT UNSIGNED NOT NULL,account_id BIGINT UNSIGNED NULL,payment_date DATE NOT NULL,amount DECIMAL(15,2) NOT NULL,reference VARCHAR(120) NULL,notes TEXT NULL,created_by BIGINT UNSIGNED NOT NULL,created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,KEY idx_bill_payment_date (payment_date),CONSTRAINT fk_bill_payment_bill FOREIGN KEY (bill_id) REFERENCES bills(id) ON DELETE CASCADE,CONSTRAINT fk_bill_payment_account FOREIGN KEY (account_id) REFERENCES payment_accounts(id) ON DELETE SET NULL,CONSTRAINT fk_bill_payment_user FOREIGN KEY (created_by) REFERENCES users(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
        $complete('20260713_suppliers_and_bills');
    }

    if(!$applied('20260713_sodazw_operations')){
        $columnCheck=$pdo->prepare('SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=? AND COLUMN_NAME=?');
        $addColumns=function(string $table,array $columns)use($pdo,$columnCheck):void{foreach($columns as $name=>$definition){$columnCheck->execute([$table,$name]);if(!(int)$columnCheck->fetchColumn())$pdo->exec("ALTER TABLE {$table} ADD COLUMN {$name} {$definition}");}};
        $addColumns('products',['category'=>"VARCHAR(80) NOT NULL DEFAULT 'Other' AFTER description",'cost_price'=>'DECIMAL(13,2) NOT NULL DEFAULT 0.00 AFTER unit_price','reorder_level'=>'DECIMAL(13,2) NOT NULL DEFAULT 0.00 AFTER track_stock']);
        $addColumns('documents',['order_source'=>"VARCHAR(40) NOT NULL DEFAULT 'WhatsApp' AFTER terms",'fulfilment_method'=>"VARCHAR(30) NOT NULL DEFAULT 'Pickup' AFTER order_source",'delivery_address'=>'VARCHAR(255) NULL AFTER fulfilment_method','stock_processed_at'=>'DATETIME NULL AFTER delivery_address']);
        $addColumns('document_items',['unit_cost'=>'DECIMAL(13,2) NOT NULL DEFAULT 0.00 AFTER unit_price']);
        $pdo->exec("CREATE TABLE IF NOT EXISTS inventory_movements (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,product_id BIGINT UNSIGNED NOT NULL,movement_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,movement_type ENUM('opening','purchase','sale','return','adjustment') NOT NULL,quantity_change DECIMAL(13,2) NOT NULL,reference_type VARCHAR(40) NULL,reference_id BIGINT UNSIGNED NULL,notes VARCHAR(255) NULL,created_by BIGINT UNSIGNED NOT NULL,created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,KEY idx_inventory_product_date (product_id,movement_date),CONSTRAINT fk_inventory_product FOREIGN KEY (product_id) REFERENCES products(id),CONSTRAINT fk_inventory_user FOREIGN KEY (created_by) REFERENCES users(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
        $pdo->exec("UPDATE products SET category=CASE WHEN sku LIKE 'SZW-%CYL' THEN 'CO2 Cylinders' WHEN sku LIKE 'SZW-SYR-%' THEN 'Flavours & Syrups' WHEN sku LIKE 'SZW-1L-%' OR sku LIKE 'SZW-FG-MINT' THEN 'Bottles' WHEN sku LIKE 'SZW-%' THEN 'Sparkling Water Machines' ELSE category END WHERE category='Other'");
        $complete('20260713_sodazw_operations');
    }

    if(!$applied('20260713_secure_invoice_sharing')){
        $columnCheck=$pdo->prepare('SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME="documents" AND COLUMN_NAME=?');
        foreach(['share_token'=>'CHAR(64) NULL AFTER stock_processed_at','share_enabled'=>'TINYINT(1) NOT NULL DEFAULT 1 AFTER share_token'] as $name=>$definition){$columnCheck->execute([$name]);if(!(int)$columnCheck->fetchColumn())$pdo->exec("ALTER TABLE documents ADD COLUMN {$name} {$definition}");}
        $indexCheck=$pdo->prepare('SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME="documents" AND INDEX_NAME="uq_document_share_token"');$indexCheck->execute();if(!(int)$indexCheck->fetchColumn())$pdo->exec('ALTER TABLE documents ADD UNIQUE KEY uq_document_share_token (share_token)');
        $missing=$pdo->query('SELECT id FROM documents WHERE type="invoice" AND share_token IS NULL')->fetchAll(PDO::FETCH_COLUMN);$set=$pdo->prepare('UPDATE documents SET share_token=? WHERE id=?');foreach($missing as $documentId)$set->execute([bin2hex(random_bytes(32)),$documentId]);
        $complete('20260713_secure_invoice_sharing');
    }

    if(!$applied('20260713_invoice_customer_experience')){
        $columnCheck=$pdo->prepare('SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME="documents" AND COLUMN_NAME=?');
        foreach(['customer_reference'=>'VARCHAR(120) NULL AFTER terms','share_view_count'=>'INT UNSIGNED NOT NULL DEFAULT 0 AFTER share_enabled','share_first_viewed_at'=>'DATETIME NULL AFTER share_view_count','share_last_viewed_at'=>'DATETIME NULL AFTER share_first_viewed_at'] as $name=>$definition){$columnCheck->execute([$name]);if(!(int)$columnCheck->fetchColumn())$pdo->exec("ALTER TABLE documents ADD COLUMN {$name} {$definition}");}
        $complete('20260713_invoice_customer_experience');
    }

    if(!$applied('20260713_gas_refill_product')){
        $pdo->exec("INSERT INTO products (sku,name,description,category,unit_price,cost_price,tax_rate,stock_quantity,track_stock,reorder_level,active) VALUES ('SZW-CO2-REFILL','CO2 Gas Cylinder Refill','Refill service for a compatible customer-supplied empty cylinder','CO2 Cylinders',35.00,0.00,0.00,0.00,0,0.00,1) ON DUPLICATE KEY UPDATE name=VALUES(name),description=VALUES(description),category=VALUES(category),unit_price=VALUES(unit_price),track_stock=0,active=1");
        $complete('20260713_gas_refill_product');
    }

    if(!$applied('20260713_website_order_inbox')){
        $pdo->exec("CREATE TABLE IF NOT EXISTS web_orders (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,order_number VARCHAR(40) NOT NULL UNIQUE,idempotency_key VARCHAR(64) NOT NULL UNIQUE,customer_id BIGINT UNSIGNED NULL,invoice_id BIGINT UNSIGNED NULL,customer_name VARCHAR(160) NOT NULL,phone VARCHAR(50) NOT NULL,email VARCHAR(190) NULL,fulfilment_method ENUM('Pickup','Delivery') NOT NULL DEFAULT 'Pickup',delivery_address VARCHAR(255) NULL,payment_preference VARCHAR(100) NULL,notes TEXT NULL,status ENUM('new','contacted','confirmed','invoiced','fulfilled','cancelled') NOT NULL DEFAULT 'new',currency_code CHAR(3) NOT NULL DEFAULT 'USD',subtotal DECIMAL(15,2) NOT NULL DEFAULT 0.00,delivery_fee DECIMAL(15,2) NOT NULL DEFAULT 0.00,total DECIMAL(15,2) NOT NULL DEFAULT 0.00,client_ip_hash CHAR(64) NULL,user_agent VARCHAR(255) NULL,created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,CONSTRAINT fk_web_order_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL,CONSTRAINT fk_web_order_invoice FOREIGN KEY (invoice_id) REFERENCES documents(id) ON DELETE SET NULL,INDEX idx_web_orders_status_created (status,created_at),INDEX idx_web_orders_phone (phone)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
        $pdo->exec("CREATE TABLE IF NOT EXISTS web_order_items (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,web_order_id BIGINT UNSIGNED NOT NULL,product_id BIGINT UNSIGNED NULL,sku VARCHAR(80) NOT NULL,product_name VARCHAR(160) NOT NULL,quantity DECIMAL(13,2) NOT NULL DEFAULT 1.00,unit_price DECIMAL(13,2) NOT NULL DEFAULT 0.00,line_total DECIMAL(13,2) NOT NULL DEFAULT 0.00,CONSTRAINT fk_web_order_item_order FOREIGN KEY (web_order_id) REFERENCES web_orders(id) ON DELETE CASCADE,CONSTRAINT fk_web_order_item_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE SET NULL,INDEX idx_web_order_items_order (web_order_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
        $complete('20260713_website_order_inbox');
    }

    if(!$applied('20260713_refill_service_terms')){
        $update=$pdo->prepare('UPDATE products SET description=? WHERE sku=?');
        $update->execute(['New snap-in pink-system 60L CO2 cylinder. Delivery is charged according to location.','SZW-QC-CYL']);
        $update->execute(['New threaded blue screw-in 60L CO2 cylinder. Delivery is charged according to location.','SZW-CL-CYL']);
        $update->execute(['Overnight refill service at $35 per approved compatible cylinder. Delivery is charged according to location.','SZW-CO2-REFILL']);
        $complete('20260713_refill_service_terms');
    }

    if(!$applied('20260714_sodazw_crm')){
        $columnCheck=$pdo->prepare('SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=? AND COLUMN_NAME=?');
        $customerColumns=[
            'customer_type'=>"VARCHAR(40) NOT NULL DEFAULT 'Household' AFTER city",
            'area'=>"VARCHAR(100) NULL AFTER customer_type",
            'preferred_payment'=>"VARCHAR(80) NULL AFTER area",
            'marketing_consent'=>"TINYINT(1) NOT NULL DEFAULT 0 AFTER preferred_payment",
            'last_contact_at'=>"DATETIME NULL AFTER marketing_consent",
            'next_refill_due'=>"DATE NULL AFTER last_contact_at"
        ];
        foreach($customerColumns as $name=>$definition){$columnCheck->execute(['customers',$name]);if(!(int)$columnCheck->fetchColumn())$pdo->exec("ALTER TABLE customers ADD COLUMN {$name} {$definition}");}
        $pdo->exec("CREATE TABLE IF NOT EXISTS crm_opportunities (
            id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            customer_id BIGINT UNSIGNED NOT NULL,
            web_order_id BIGINT UNSIGNED NULL,
            document_id BIGINT UNSIGNED NULL,
            title VARCHAR(180) NOT NULL,
            stage VARCHAR(40) NOT NULL DEFAULT 'new_enquiry',
            source VARCHAR(40) NOT NULL DEFAULT 'WhatsApp',
            value DECIMAL(15,2) NOT NULL DEFAULT 0.00,
            currency_code CHAR(3) NOT NULL DEFAULT 'USD',
            expected_close_date DATE NULL,
            next_follow_up_at DATETIME NULL,
            assigned_to BIGINT UNSIGNED NULL,
            notes TEXT NULL,
            lost_reason VARCHAR(255) NULL,
            created_by BIGINT UNSIGNED NOT NULL,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            CONSTRAINT fk_crm_opportunity_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
            CONSTRAINT fk_crm_opportunity_web_order FOREIGN KEY (web_order_id) REFERENCES web_orders(id) ON DELETE SET NULL,
            CONSTRAINT fk_crm_opportunity_document FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE SET NULL,
            CONSTRAINT fk_crm_opportunity_assignee FOREIGN KEY (assigned_to) REFERENCES users(id) ON DELETE SET NULL,
            CONSTRAINT fk_crm_opportunity_creator FOREIGN KEY (created_by) REFERENCES users(id),
            INDEX idx_crm_stage_followup (stage,next_follow_up_at),
            INDEX idx_crm_customer (customer_id),
            UNIQUE KEY uq_crm_web_order (web_order_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
        $pdo->exec("CREATE TABLE IF NOT EXISTS crm_activities (
            id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            opportunity_id BIGINT UNSIGNED NULL,
            customer_id BIGINT UNSIGNED NOT NULL,
            activity_type ENUM('call','whatsapp','email','meeting','task','note') NOT NULL DEFAULT 'task',
            subject VARCHAR(180) NOT NULL,
            due_at DATETIME NULL,
            completed_at DATETIME NULL,
            notes TEXT NULL,
            assigned_to BIGINT UNSIGNED NULL,
            created_by BIGINT UNSIGNED NOT NULL,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            CONSTRAINT fk_crm_activity_opportunity FOREIGN KEY (opportunity_id) REFERENCES crm_opportunities(id) ON DELETE CASCADE,
            CONSTRAINT fk_crm_activity_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
            CONSTRAINT fk_crm_activity_assignee FOREIGN KEY (assigned_to) REFERENCES users(id) ON DELETE SET NULL,
            CONSTRAINT fk_crm_activity_creator FOREIGN KEY (created_by) REFERENCES users(id),
            INDEX idx_crm_activity_due (completed_at,due_at),
            INDEX idx_crm_activity_customer (customer_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
        $pdo->exec("CREATE TABLE IF NOT EXISTS refill_jobs (
            id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            reference VARCHAR(40) NOT NULL UNIQUE,
            customer_id BIGINT UNSIGNED NOT NULL,
            opportunity_id BIGINT UNSIGNED NULL,
            cylinder_type ENUM('pink_quick_connect','blue_screw','compatible_other') NOT NULL,
            service_type ENUM('new_cylinder','refill','exchange') NOT NULL DEFAULT 'refill',
            quantity SMALLINT UNSIGNED NOT NULL DEFAULT 1,
            unit_price DECIMAL(13,2) NOT NULL DEFAULT 35.00,
            fulfilment_method ENUM('Pickup','Delivery') NOT NULL DEFAULT 'Pickup',
            delivery_address VARCHAR(255) NULL,
            delivery_fee DECIMAL(13,2) NOT NULL DEFAULT 0.00,
            status ENUM('received','awaiting_refill','ready','out_for_delivery','completed','cancelled') NOT NULL DEFAULT 'received',
            payment_status ENUM('unpaid','partial','paid') NOT NULL DEFAULT 'unpaid',
            received_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            due_at DATETIME NULL,
            completed_at DATETIME NULL,
            notes TEXT NULL,
            created_by BIGINT UNSIGNED NOT NULL,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            CONSTRAINT fk_refill_customer FOREIGN KEY (customer_id) REFERENCES customers(id),
            CONSTRAINT fk_refill_opportunity FOREIGN KEY (opportunity_id) REFERENCES crm_opportunities(id) ON DELETE SET NULL,
            CONSTRAINT fk_refill_creator FOREIGN KEY (created_by) REFERENCES users(id),
            INDEX idx_refill_status_due (status,due_at),
            INDEX idx_refill_customer (customer_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
        $pdo->exec("CREATE TABLE IF NOT EXISTS deliveries (
            id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            reference VARCHAR(40) NOT NULL UNIQUE,
            customer_id BIGINT UNSIGNED NOT NULL,
            opportunity_id BIGINT UNSIGNED NULL,
            document_id BIGINT UNSIGNED NULL,
            refill_job_id BIGINT UNSIGNED NULL,
            delivery_address VARCHAR(255) NOT NULL,
            area VARCHAR(100) NULL,
            delivery_fee DECIMAL(13,2) NOT NULL DEFAULT 0.00,
            scheduled_for DATETIME NULL,
            driver_name VARCHAR(120) NULL,
            driver_phone VARCHAR(50) NULL,
            status ENUM('pending','scheduled','out_for_delivery','delivered','failed','cancelled') NOT NULL DEFAULT 'pending',
            proof_reference VARCHAR(160) NULL,
            notes TEXT NULL,
            delivered_at DATETIME NULL,
            created_by BIGINT UNSIGNED NOT NULL,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            CONSTRAINT fk_delivery_customer FOREIGN KEY (customer_id) REFERENCES customers(id),
            CONSTRAINT fk_delivery_opportunity FOREIGN KEY (opportunity_id) REFERENCES crm_opportunities(id) ON DELETE SET NULL,
            CONSTRAINT fk_delivery_document FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE SET NULL,
            CONSTRAINT fk_delivery_refill FOREIGN KEY (refill_job_id) REFERENCES refill_jobs(id) ON DELETE SET NULL,
            CONSTRAINT fk_delivery_creator FOREIGN KEY (created_by) REFERENCES users(id),
            INDEX idx_delivery_status_schedule (status,scheduled_for),
            INDEX idx_delivery_customer (customer_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
        $pdo->exec("INSERT IGNORE INTO crm_opportunities (customer_id,web_order_id,document_id,title,stage,source,value,currency_code,expected_close_date,assigned_to,notes,created_by,created_at)
            SELECT w.customer_id,w.id,w.invoice_id,CONCAT('Website order ',w.order_number),
            CASE w.status WHEN 'new' THEN 'new_enquiry' WHEN 'contacted' THEN 'contacted' WHEN 'confirmed' THEN 'order_confirmed' WHEN 'invoiced' THEN 'invoiced' WHEN 'fulfilled' THEN 'completed' ELSE 'lost' END,
            'Website',w.total,w.currency_code,DATE(w.created_at),(SELECT id FROM users WHERE active=1 ORDER BY FIELD(role,'owner','admin','staff'),id LIMIT 1),CONCAT('Imported from website order ',w.order_number),(SELECT id FROM users WHERE active=1 ORDER BY FIELD(role,'owner','admin','staff'),id LIMIT 1),w.created_at
            FROM web_orders w WHERE w.customer_id IS NOT NULL");
        $complete('20260714_sodazw_crm');
    }

    if(!$applied('20260715_whatsapp_level2')){
        $pdo->exec("CREATE TABLE IF NOT EXISTS whatsapp_conversations (
            id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            customer_id BIGINT UNSIGNED NULL,
            phone VARCHAR(30) NOT NULL,
            display_name VARCHAR(180) NULL,
            status ENUM('open','pending','closed') NOT NULL DEFAULT 'open',
            assigned_to BIGINT UNSIGNED NULL,
            unread_count INT UNSIGNED NOT NULL DEFAULT 0,
            last_message_at DATETIME NULL,
            last_customer_message_at DATETIME NULL,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY uq_whatsapp_conversation_phone (phone),
            KEY idx_whatsapp_conversation_queue (status,last_message_at),
            CONSTRAINT fk_whatsapp_conversation_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL,
            CONSTRAINT fk_whatsapp_conversation_assignee FOREIGN KEY (assigned_to) REFERENCES users(id) ON DELETE SET NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
        $pdo->exec("CREATE TABLE IF NOT EXISTS whatsapp_messages (
            id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            conversation_id BIGINT UNSIGNED NOT NULL,
            customer_id BIGINT UNSIGNED NULL,
            meta_message_id VARCHAR(190) NULL,
            direction ENUM('inbound','outbound') NOT NULL,
            message_type VARCHAR(40) NOT NULL DEFAULT 'text',
            body TEXT NULL,
            template_name VARCHAR(180) NULL,
            status ENUM('queued','sent','delivered','read','failed','received') NOT NULL,
            context_message_id VARCHAR(190) NULL,
            error_message VARCHAR(500) NULL,
            sent_by BIGINT UNSIGNED NULL,
            message_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            UNIQUE KEY uq_whatsapp_meta_message (meta_message_id),
            KEY idx_whatsapp_message_conversation (conversation_id,message_at),
            KEY idx_whatsapp_message_status (status,message_at),
            CONSTRAINT fk_whatsapp_message_conversation FOREIGN KEY (conversation_id) REFERENCES whatsapp_conversations(id) ON DELETE CASCADE,
            CONSTRAINT fk_whatsapp_message_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL,
            CONSTRAINT fk_whatsapp_message_user FOREIGN KEY (sent_by) REFERENCES users(id) ON DELETE SET NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
        $complete('20260715_whatsapp_level2');
    }

    if(!$applied('20260716_refill_operations_upgrade')){
        $columnCheck=$pdo->prepare('SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME="refill_jobs" AND COLUMN_NAME=?');
        foreach([
            'document_id'=>'BIGINT UNSIGNED NULL AFTER opportunity_id',
            'cylinder_identifier'=>'VARCHAR(100) NULL AFTER cylinder_type',
            'compatibility_confirmed'=>'TINYINT(1) NOT NULL DEFAULT 0 AFTER cylinder_identifier',
            'condition_received'=>'VARCHAR(160) NULL AFTER compatibility_confirmed'
        ] as $name=>$definition){$columnCheck->execute([$name]);if(!(int)$columnCheck->fetchColumn())$pdo->exec("ALTER TABLE refill_jobs ADD COLUMN {$name} {$definition}");}
        $fkCheck=$pdo->prepare('SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS WHERE CONSTRAINT_SCHEMA=DATABASE() AND TABLE_NAME="refill_jobs" AND CONSTRAINT_NAME="fk_refill_document"');
        $fkCheck->execute();if(!(int)$fkCheck->fetchColumn())$pdo->exec('ALTER TABLE refill_jobs ADD CONSTRAINT fk_refill_document FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE SET NULL');
        $pdo->exec("CREATE TABLE IF NOT EXISTS refill_job_events (
            id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            refill_job_id BIGINT UNSIGNED NOT NULL,
            event_type VARCHAR(50) NOT NULL,
            event_label VARCHAR(180) NOT NULL,
            notes VARCHAR(500) NULL,
            created_by BIGINT UNSIGNED NULL,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            KEY idx_refill_event_job (refill_job_id,created_at),
            CONSTRAINT fk_refill_event_job FOREIGN KEY (refill_job_id) REFERENCES refill_jobs(id) ON DELETE CASCADE,
            CONSTRAINT fk_refill_event_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
        $complete('20260716_refill_operations_upgrade');
    }

    if(!$applied('20260717_complete_sodazw_catalog')){
        $catalogue=[
            ['SZW-QC-WHT','White Quick Connect Starter Kit','White machine, pink Quick Connect 60L cylinder and reusable bottle','Sparkling Water Machines',150.00],
            ['SZW-QC-BLK','Black Quick Connect Starter Kit','Black machine, pink Quick Connect 60L cylinder and reusable bottle','Sparkling Water Machines',150.00],
            ['SZW-QC-CYL','Pink Quick Connect 60L Cylinder','New snap-in pink-system 60L CO2 cylinder. Delivery is charged according to location.','CO2 Cylinders',55.00],
            ['SZW-CL-AQUA','Aqua Classic Starter Kit','Aqua sparkling-water machine, blue screw-in cylinder and reusable bottle','Sparkling Water Machines',150.00],
            ['SZW-CL-WHT','White Classic Starter Kit','White sparkling-water machine, blue screw-in cylinder and reusable bottle','Sparkling Water Machines',150.00],
            ['SZW-CL-BLK','Black Classic Starter Kit','Black sparkling-water machine, blue screw-in cylinder and reusable bottle','Sparkling Water Machines',150.00],
            ['SZW-CL-CYL','Classic Blue 60L Cylinder','New threaded blue screw-in 60L CO2 cylinder. Delivery is charged according to location.','CO2 Cylinders',60.00],
            ['SZW-CO2-REFILL','CO2 Gas Cylinder Refill','Overnight refill service at $35 per approved compatible cylinder. Delivery is charged according to location.','CO2 Cylinders',35.00],
            ['SZW-FG-BUNDLE','White Fizz&Go Bundle','Quick Connect sparkling-water machine, cylinder and Fizz&Go steel bottle','Sparkling Water Machines',190.00],
            ['SZW-FG-MINT','Fizz&Go Mint Steel Bottle','Stainless-steel SodaStream bottle for sparkling water on the go','Bottles',45.00],
            ['SZW-1L-WHT','White-Base 1L Bottle','Reusable 1 litre carbonating bottle with white base','Bottles',20.00],
            ['SZW-1L-BLK','Black-Base 1L Bottle','Reusable 1 litre carbonating bottle with black base','Bottles',20.00],
            ['SZW-SYR-PG','Pink Grapefruit Syrup','Sugar-free pink grapefruit flavour; makes approximately 9 litres','Flavours & Syrups',12.00],
            ['SZW-SYR-TONIC','Tonic Syrup','Classic tonic-flavoured syrup; makes approximately 9 litres','Flavours & Syrups',12.00],
            ['SZW-SYR-LEM','Lemonade Syrup','Classic lemonade-flavoured syrup; makes approximately 9 litres','Flavours & Syrups',12.00],
            ['SZW-SYR-CREAM','Cream Soda Syrup','Classic cream-soda flavoured syrup; makes approximately 9 litres','Flavours & Syrups',12.00],
        ];
        $seed=$pdo->prepare('INSERT INTO products (sku,name,description,category,unit_price,cost_price,tax_rate,stock_quantity,track_stock,reorder_level,active) VALUES (?,?,?,?,?,0,0,0,0,0,1) ON DUPLICATE KEY UPDATE name=VALUES(name),description=VALUES(description),category=VALUES(category),unit_price=VALUES(unit_price),tax_rate=VALUES(tax_rate),active=1');
        foreach($catalogue as $product)$seed->execute($product);
        $complete('20260717_complete_sodazw_catalog');
    }

    if(!$applied('20260717_enable_physical_stock_tracking')){
        $pdo->exec("UPDATE products SET track_stock=1,reorder_level=CASE WHEN reorder_level<=0 THEN 2 ELSE reorder_level END WHERE active=1 AND sku LIKE 'SZW-%' AND sku<>'SZW-CO2-REFILL'");
        $pdo->exec("UPDATE products SET track_stock=0 WHERE sku='SZW-CO2-REFILL'");
        $complete('20260717_enable_physical_stock_tracking');
    }

    if(!$applied('20260718_operations_command_centre')){
        $pdo->exec("CREATE TABLE IF NOT EXISTS cylinder_assets (
            id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            cylinder_code VARCHAR(60) NOT NULL UNIQUE,
            customer_id BIGINT UNSIGNED NULL,
            cylinder_type ENUM('pink_quick_connect','blue_screw','compatible_other') NOT NULL,
            ownership ENUM('soda_zw','customer') NOT NULL DEFAULT 'soda_zw',
            status ENUM('in_stock_full','in_stock_empty','received','refilling','ready','with_customer','damaged','retired') NOT NULL DEFAULT 'in_stock_full',
            location VARCHAR(160) NULL,
            last_refill_at DATETIME NULL,
            next_inspection_due DATE NULL,
            notes TEXT NULL,
            created_by BIGINT UNSIGNED NOT NULL,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            CONSTRAINT fk_cylinder_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL,
            CONSTRAINT fk_cylinder_creator FOREIGN KEY (created_by) REFERENCES users(id),
            INDEX idx_cylinder_status_type (status,cylinder_type),
            INDEX idx_cylinder_customer (customer_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
        $columnCheck=$pdo->prepare('SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=? AND COLUMN_NAME=?');
        $columnCheck->execute(['refill_jobs','cylinder_id']);
        if(!(int)$columnCheck->fetchColumn())$pdo->exec('ALTER TABLE refill_jobs ADD COLUMN cylinder_id BIGINT UNSIGNED NULL AFTER cylinder_identifier, ADD INDEX idx_refill_cylinder (cylinder_id), ADD CONSTRAINT fk_refill_cylinder FOREIGN KEY (cylinder_id) REFERENCES cylinder_assets(id) ON DELETE SET NULL');
        foreach([
            'payment_status'=>"ENUM('unpaid','partial','paid') NOT NULL DEFAULT 'unpaid' AFTER delivery_fee",
            'payment_method'=>"VARCHAR(60) NULL AFTER payment_status",
            'amount_collected'=>"DECIMAL(13,2) NOT NULL DEFAULT 0.00 AFTER payment_method",
            'recipient_name'=>"VARCHAR(160) NULL AFTER proof_reference",
            'failure_reason'=>"VARCHAR(255) NULL AFTER recipient_name"
        ] as $name=>$definition){$columnCheck->execute(['deliveries',$name]);if(!(int)$columnCheck->fetchColumn())$pdo->exec("ALTER TABLE deliveries ADD COLUMN {$name} {$definition}");}
        $pdo->exec("CREATE TABLE IF NOT EXISTS cylinder_events (
            id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            cylinder_id BIGINT UNSIGNED NOT NULL,
            refill_job_id BIGINT UNSIGNED NULL,
            event_type VARCHAR(50) NOT NULL,
            from_status VARCHAR(40) NULL,
            to_status VARCHAR(40) NULL,
            notes VARCHAR(500) NULL,
            created_by BIGINT UNSIGNED NULL,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            CONSTRAINT fk_cylinder_event_asset FOREIGN KEY (cylinder_id) REFERENCES cylinder_assets(id) ON DELETE CASCADE,
            CONSTRAINT fk_cylinder_event_refill FOREIGN KEY (refill_job_id) REFERENCES refill_jobs(id) ON DELETE SET NULL,
            CONSTRAINT fk_cylinder_event_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
            INDEX idx_cylinder_event_asset (cylinder_id,created_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
        $pdo->exec("CREATE TABLE IF NOT EXISTS delivery_events (
            id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            delivery_id BIGINT UNSIGNED NOT NULL,
            event_type VARCHAR(50) NOT NULL,
            event_label VARCHAR(180) NOT NULL,
            notes VARCHAR(500) NULL,
            created_by BIGINT UNSIGNED NULL,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            CONSTRAINT fk_delivery_event_delivery FOREIGN KEY (delivery_id) REFERENCES deliveries(id) ON DELETE CASCADE,
            CONSTRAINT fk_delivery_event_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
            INDEX idx_delivery_event_delivery (delivery_id,created_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
        $complete('20260718_operations_command_centre');
    }

    if(!$applied('20260718_customer_cleanup_centre')){
        $pdo->exec("CREATE TABLE IF NOT EXISTS customer_merge_log (
            id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            action_type VARCHAR(40) NOT NULL,
            master_customer_id BIGINT UNSIGNED NULL,
            source_customer_id BIGINT UNSIGNED NOT NULL,
            master_snapshot LONGTEXT NULL,
            source_snapshot LONGTEXT NOT NULL,
            moved_records LONGTEXT NULL,
            performed_by BIGINT UNSIGNED NULL,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            CONSTRAINT fk_customer_merge_user FOREIGN KEY (performed_by) REFERENCES users(id) ON DELETE SET NULL,
            INDEX idx_customer_merge_master (master_customer_id,created_at),
            INDEX idx_customer_merge_source (source_customer_id,created_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
        $complete('20260718_customer_cleanup_centre');
    }

    if(!$applied('20260718_document_workflow_safety')){
        $columnCheck=$pdo->prepare('SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME="documents" AND COLUMN_NAME=?');
        foreach([
            // Some early cPanel installs predate quotation-to-invoice links.
            // Add this dependency before the backfill query below uses it.
            'source_document_id'=>'BIGINT UNSIGNED NULL AFTER customer_id',
            'sent_at'=>'DATETIME NULL AFTER status',
            'accepted_at'=>'DATETIME NULL AFTER sent_at',
            'rejected_at'=>'DATETIME NULL AFTER accepted_at',
            'cancelled_at'=>'DATETIME NULL AFTER rejected_at',
            'follow_up_date'=>'DATE NULL AFTER cancelled_at',
            'internal_notes'=>'TEXT NULL AFTER follow_up_date',
            'lock_version'=>'INT UNSIGNED NOT NULL DEFAULT 0 AFTER internal_notes'
        ] as $name=>$definition){$columnCheck->execute([$name]);if(!(int)$columnCheck->fetchColumn())$pdo->exec("ALTER TABLE documents ADD COLUMN {$name} {$definition}");}
        $pdo->exec("CREATE TABLE IF NOT EXISTS document_events (
            id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            document_id BIGINT UNSIGNED NOT NULL,
            event_type VARCHAR(60) NOT NULL,
            from_status VARCHAR(40) NULL,
            to_status VARCHAR(40) NULL,
            notes TEXT NULL,
            created_by BIGINT UNSIGNED NULL,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            CONSTRAINT fk_document_event_document FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE CASCADE,
            CONSTRAINT fk_document_event_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
            INDEX idx_document_event_document (document_id,created_at),
            INDEX idx_document_event_type (event_type,created_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
        $pdo->exec("CREATE TABLE IF NOT EXISTS quotation_conversions (
            id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            quotation_id BIGINT UNSIGNED NOT NULL,
            invoice_id BIGINT UNSIGNED NOT NULL,
            created_by BIGINT UNSIGNED NULL,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            UNIQUE KEY uq_quotation_conversion_quote (quotation_id),
            UNIQUE KEY uq_quotation_conversion_invoice (invoice_id),
            CONSTRAINT fk_quotation_conversion_quote FOREIGN KEY (quotation_id) REFERENCES documents(id) ON DELETE RESTRICT,
            CONSTRAINT fk_quotation_conversion_invoice FOREIGN KEY (invoice_id) REFERENCES documents(id) ON DELETE RESTRICT,
            CONSTRAINT fk_quotation_conversion_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
        $pdo->exec('INSERT IGNORE INTO quotation_conversions (quotation_id,invoice_id,created_by,created_at) SELECT q.id,i.id,i.created_by,i.created_at FROM documents i JOIN documents q ON q.id=i.source_document_id AND q.type="quotation" WHERE i.type="invoice" ORDER BY i.id');
        $pdo->exec('UPDATE documents SET sent_at=COALESCE(sent_at,updated_at) WHERE status IN ("sent","partial","paid","overdue")');
        $pdo->exec('UPDATE documents SET accepted_at=COALESCE(accepted_at,updated_at) WHERE status="accepted"');
        $pdo->exec('UPDATE documents SET rejected_at=COALESCE(rejected_at,updated_at) WHERE status="rejected"');
        $pdo->exec('UPDATE documents SET cancelled_at=COALESCE(cancelled_at,updated_at) WHERE status="cancelled"');
        $complete('20260718_document_workflow_safety');
    }

    if(!$applied('20260718_finance_control_centre')){
        $columnCheck=$pdo->prepare('SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=? AND COLUMN_NAME=?');
        // Older Soda ZW installations only allowed quotations and invoices.
        // Keep the migration safe for those live databases before creating credit notes.
        $pdo->exec("ALTER TABLE documents MODIFY COLUMN type ENUM('quotation','invoice','credit_note') NOT NULL");
        $settingsColumns=[
            'credit_prefix'=>"VARCHAR(20) NOT NULL DEFAULT 'CN-' AFTER quote_prefix",
            'next_credit_number'=>'INT UNSIGNED NOT NULL DEFAULT 1 AFTER next_quote_number',
            'auto_invoice_web_orders'=>'TINYINT(1) NOT NULL DEFAULT 1 AFTER default_due_days',
            'auto_reminders'=>'TINYINT(1) NOT NULL DEFAULT 0 AFTER auto_invoice_web_orders',
            'reminder_days_before'=>'SMALLINT UNSIGNED NOT NULL DEFAULT 2 AFTER auto_reminders',
            'reminder_days_overdue'=>'SMALLINT UNSIGNED NOT NULL DEFAULT 3 AFTER reminder_days_before',
            'reminder_template_name'=>'VARCHAR(120) NULL AFTER reminder_days_overdue',
            'cron_token'=>'CHAR(64) NULL AFTER reminder_template_name',
            'last_backup_at'=>'DATETIME NULL AFTER cron_token'
        ];
        foreach($settingsColumns as $name=>$definition){$columnCheck->execute(['settings',$name]);if(!(int)$columnCheck->fetchColumn())$pdo->exec("ALTER TABLE settings ADD COLUMN {$name} {$definition}");}
        $pdo->exec("UPDATE settings SET cron_token=COALESCE(cron_token,SHA2(CONCAT(UUID(),RAND(),NOW()),256)) WHERE id=1");
        $pdo->exec("CREATE TABLE IF NOT EXISTS credit_note_applications (
            id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            credit_note_id BIGINT UNSIGNED NOT NULL,
            invoice_id BIGINT UNSIGNED NOT NULL,
            amount DECIMAL(15,2) NOT NULL,
            reason VARCHAR(255) NULL,
            created_by BIGINT UNSIGNED NULL,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            UNIQUE KEY uq_credit_note_document (credit_note_id),
            KEY idx_credit_invoice (invoice_id,created_at),
            CONSTRAINT fk_credit_note_document FOREIGN KEY (credit_note_id) REFERENCES documents(id) ON DELETE RESTRICT,
            CONSTRAINT fk_credit_invoice_document FOREIGN KEY (invoice_id) REFERENCES documents(id) ON DELETE RESTRICT,
            CONSTRAINT fk_credit_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
        $pdo->exec("CREATE TABLE IF NOT EXISTS payment_reversals (
            id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            payment_id BIGINT UNSIGNED NOT NULL,
            reversal_date DATE NOT NULL,
            amount DECIMAL(15,2) NOT NULL,
            reason VARCHAR(255) NOT NULL,
            reference VARCHAR(120) NULL,
            created_by BIGINT UNSIGNED NULL,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            KEY idx_payment_reversal (payment_id,reversal_date),
            CONSTRAINT fk_reversal_payment FOREIGN KEY (payment_id) REFERENCES payments(id) ON DELETE RESTRICT,
            CONSTRAINT fk_reversal_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
        $pdo->exec("CREATE TABLE IF NOT EXISTS reconciliation_entries (
            id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            account_id BIGINT UNSIGNED NOT NULL,
            transaction_date DATE NOT NULL,
            direction ENUM('in','out') NOT NULL,
            amount DECIMAL(15,2) NOT NULL,
            description VARCHAR(255) NOT NULL,
            reference VARCHAR(120) NULL,
            source VARCHAR(40) NOT NULL DEFAULT 'manual',
            status ENUM('unmatched','matched','ignored') NOT NULL DEFAULT 'unmatched',
            matched_payment_id BIGINT UNSIGNED NULL,
            matched_bill_payment_id BIGINT UNSIGNED NULL,
            matched_expense_id BIGINT UNSIGNED NULL,
            notes VARCHAR(500) NULL,
            created_by BIGINT UNSIGNED NULL,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            KEY idx_reconciliation_status (account_id,status,transaction_date),
            CONSTRAINT fk_reconciliation_account FOREIGN KEY (account_id) REFERENCES payment_accounts(id),
            CONSTRAINT fk_reconciliation_payment FOREIGN KEY (matched_payment_id) REFERENCES payments(id) ON DELETE SET NULL,
            CONSTRAINT fk_reconciliation_bill_payment FOREIGN KEY (matched_bill_payment_id) REFERENCES bill_payments(id) ON DELETE SET NULL,
            CONSTRAINT fk_reconciliation_expense FOREIGN KEY (matched_expense_id) REFERENCES expenses(id) ON DELETE SET NULL,
            CONSTRAINT fk_reconciliation_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
        $pdo->exec("CREATE TABLE IF NOT EXISTS reminder_log (
            id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            document_id BIGINT UNSIGNED NOT NULL,
            customer_id BIGINT UNSIGNED NOT NULL,
            channel ENUM('whatsapp','email','manual') NOT NULL DEFAULT 'manual',
            recipient VARCHAR(190) NULL,
            message TEXT NOT NULL,
            status ENUM('queued','sent','failed','opened') NOT NULL DEFAULT 'queued',
            error_message VARCHAR(500) NULL,
            scheduled_for DATETIME NULL,
            sent_at DATETIME NULL,
            created_by BIGINT UNSIGNED NULL,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            KEY idx_reminder_queue (status,scheduled_for),
            KEY idx_reminder_document (document_id,created_at),
            CONSTRAINT fk_reminder_document FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE CASCADE,
            CONSTRAINT fk_reminder_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
            CONSTRAINT fk_reminder_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
        $pdo->exec("CREATE TABLE IF NOT EXISTS stock_receipts (
            id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            receipt_number VARCHAR(60) NOT NULL UNIQUE,
            supplier_id BIGINT UNSIGNED NULL,
            bill_id BIGINT UNSIGNED NULL,
            receipt_date DATE NOT NULL,
            supplier_reference VARCHAR(120) NULL,
            total_cost DECIMAL(15,2) NOT NULL DEFAULT 0,
            notes VARCHAR(500) NULL,
            created_by BIGINT UNSIGNED NULL,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            CONSTRAINT fk_stock_receipt_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers(id) ON DELETE SET NULL,
            CONSTRAINT fk_stock_receipt_bill FOREIGN KEY (bill_id) REFERENCES bills(id) ON DELETE SET NULL,
            CONSTRAINT fk_stock_receipt_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
            KEY idx_stock_receipt_date (receipt_date)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
        $pdo->exec("CREATE TABLE IF NOT EXISTS stock_receipt_items (
            id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            stock_receipt_id BIGINT UNSIGNED NOT NULL,
            product_id BIGINT UNSIGNED NOT NULL,
            quantity DECIMAL(13,2) NOT NULL,
            unit_cost DECIMAL(13,2) NOT NULL,
            line_total DECIMAL(15,2) NOT NULL,
            CONSTRAINT fk_stock_receipt_item_receipt FOREIGN KEY (stock_receipt_id) REFERENCES stock_receipts(id) ON DELETE CASCADE,
            CONSTRAINT fk_stock_receipt_item_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
        $pdo->exec("CREATE TABLE IF NOT EXISTS user_permissions (
            user_id BIGINT UNSIGNED NOT NULL,
            permission_key VARCHAR(80) NOT NULL,
            allowed TINYINT(1) NOT NULL DEFAULT 0,
            updated_by BIGINT UNSIGNED NULL,
            updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (user_id,permission_key),
            CONSTRAINT fk_user_permission_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
            CONSTRAINT fk_user_permission_updater FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
        $pdo->exec("CREATE TABLE IF NOT EXISTS backup_events (
            id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            backup_type ENUM('download','scheduled') NOT NULL,
            filename VARCHAR(255) NOT NULL,
            file_size BIGINT UNSIGNED NULL,
            status ENUM('completed','failed') NOT NULL,
            notes VARCHAR(500) NULL,
            created_by BIGINT UNSIGNED NULL,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            CONSTRAINT fk_backup_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
            KEY idx_backup_created (created_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
        $complete('20260718_finance_control_centre');
    }

    if (!$applied('20260719_paynow_checkout')) {
        $pdo->exec("CREATE TABLE IF NOT EXISTS online_payment_attempts (
            id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            provider VARCHAR(30) NOT NULL,
            web_order_id BIGINT UNSIGNED NOT NULL,
            invoice_id BIGINT UNSIGNED NULL,
            payment_id BIGINT UNSIGNED NULL,
            merchant_reference VARCHAR(80) NOT NULL,
            merchant_trace VARCHAR(80) NULL,
            provider_reference VARCHAR(120) NULL,
            amount DECIMAL(15,2) NOT NULL,
            currency_code CHAR(3) NOT NULL DEFAULT 'USD',
            browser_url TEXT NULL,
            poll_url TEXT NULL,
            payment_channel VARCHAR(100) NULL,
            status VARCHAR(40) NOT NULL DEFAULT 'created',
            raw_response MEDIUMTEXT NULL,
            error_message VARCHAR(500) NULL,
            paid_at DATETIME NULL,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY uq_online_payment_reference (provider,merchant_reference),
            KEY idx_online_payment_order (web_order_id,created_at),
            KEY idx_online_payment_status (provider,status,updated_at),
            CONSTRAINT fk_online_payment_order FOREIGN KEY (web_order_id) REFERENCES web_orders(id) ON DELETE CASCADE,
            CONSTRAINT fk_online_payment_invoice FOREIGN KEY (invoice_id) REFERENCES documents(id) ON DELETE SET NULL,
            CONSTRAINT fk_online_payment_payment FOREIGN KEY (payment_id) REFERENCES payments(id) ON DELETE SET NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
        $complete('20260719_paynow_checkout');
    }

    if (!$applied('20260721_cranberry_raspberry_zero_syrup')) {
        $pdo->exec("INSERT INTO products (sku,name,description,category,unit_price,cost_price,tax_rate,stock_quantity,track_stock,reorder_level,active) VALUES ('SZW-SYR-CR-ZERO','Cranberry Raspberry Zero Sugar Syrup','Zero-sugar cranberry and raspberry flavour; makes approximately 9 litres','Flavours & Syrups',12.00,0.00,0.00,0.00,1,2.00,1) ON DUPLICATE KEY UPDATE name=VALUES(name),description=VALUES(description),category=VALUES(category),unit_price=VALUES(unit_price),track_stock=1,active=1");
        $complete('20260721_cranberry_raspberry_zero_syrup');
    }

    if (!$applied('20260721_stability_security_v1')) {
        $columnCheck=$pdo->prepare('SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=? AND COLUMN_NAME=?');
        $columnCheck->execute(['users','session_version']);
        if(!(int)$columnCheck->fetchColumn())$pdo->exec('ALTER TABLE users ADD COLUMN session_version INT UNSIGNED NOT NULL DEFAULT 1 AFTER active');
        $columnCheck->execute(['expenses','currency_code']);
        if(!(int)$columnCheck->fetchColumn())$pdo->exec("ALTER TABLE expenses ADD COLUMN currency_code CHAR(3) NOT NULL DEFAULT 'USD' AFTER amount");
        $pdo->exec("UPDATE expenses SET currency_code='USD' WHERE currency_code IS NULL OR currency_code='' OR currency_code NOT REGEXP '^[A-Z]{3}$'");
        $complete('20260721_stability_security_v1');
    }

    if (!$applied('20260722_invoice_paynow_links')) {
        // Standalone billing invoices do not necessarily originate from a
        // website order, so the order relationship must be optional.
        $pdo->exec('ALTER TABLE online_payment_attempts MODIFY web_order_id BIGINT UNSIGNED NULL');
        $index = $pdo->prepare('SELECT 1 FROM information_schema.statistics WHERE table_schema=DATABASE() AND table_name="online_payment_attempts" AND index_name="idx_online_payment_invoice" LIMIT 1');
        $index->execute();
        if (!$index->fetchColumn()) {
            $pdo->exec('CREATE INDEX idx_online_payment_invoice ON online_payment_attempts (invoice_id,created_at)');
        }
        $complete('20260722_invoice_paynow_links');
    }

    if (!$applied('20260722_finance_centre_repair')) {
        // Some incremental cPanel uploads included the Finance Centre page
        // without every table from its original migration. Detect that state,
        // safely replay the idempotent migration, and only then mark this build
        // as current.
        $requiredTables = [
            'payment_accounts',
            'credit_note_applications',
            'payment_reversals',
            'reconciliation_entries',
            'reminder_log',
            'stock_receipts',
            'stock_receipt_items',
            'user_permissions',
            'backup_events',
        ];
        $tableCheck = $pdo->prepare('SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=?');
        $missingTables = [];
        foreach ($requiredTables as $table) {
            $tableCheck->execute([$table]);
            if (!(int)$tableCheck->fetchColumn()) {
                $missingTables[] = $table;
            }
        }

        $requiredSettingsColumns = [
            'credit_prefix',
            'next_credit_number',
            'auto_invoice_web_orders',
            'auto_reminders',
            'reminder_days_before',
            'reminder_days_overdue',
            'reminder_template_name',
            'cron_token',
            'last_backup_at',
        ];
        $columnCheck = $pdo->prepare('SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME="settings" AND COLUMN_NAME=?');
        $missingColumns = [];
        foreach ($requiredSettingsColumns as $column) {
            $columnCheck->execute([$column]);
            if (!(int)$columnCheck->fetchColumn()) {
                $missingColumns[] = $column;
            }
        }

        if ($missingTables || $missingColumns) {
            if (in_array('payment_accounts', $missingTables, true)) {
                $pdo->prepare('DELETE FROM schema_migrations WHERE version=?')->execute(['20260713_zimbabwe_payments']);
            }
            $pdo->prepare('DELETE FROM schema_migrations WHERE version=?')->execute(['20260718_finance_control_centre']);
            run_migrations($pdo);
            return;
        }

        $complete('20260722_finance_centre_repair');
    }
}
