<?php

declare(strict_types=1);

namespace App\Shared\Schema;

use App\Shared\Security\RbacSupport;
use PDO;
use Throwable;

final class FinanceSchemaSupport
{
    public static function ensureCustomerReceiptsSchema(PDO $pdo): void
    {
        PartySchemaSupport::ensurePartiesSchema($pdo);
        if (SchemaSupport::tableExists($pdo, 'customer_receipts')) {
            try {
                $pdo->exec("ALTER TABLE customer_receipts MODIFY customer_id BIGINT UNSIGNED NULL");
            } catch (Throwable $e) {}
            SchemaSupport::ensureColumn($pdo, 'customer_receipts', 'party_id', "BIGINT UNSIGNED NULL AFTER `customer_id`");
            try { $pdo->exec("ALTER TABLE customer_receipts ADD INDEX idx_cr_party_date (party_id, receipt_date)"); } catch (Throwable $e) {}
        }

        if (!SchemaSupport::tableExists($pdo, 'customer_receipts')) {
            $pdo->exec("CREATE TABLE IF NOT EXISTS customer_receipts (
                id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                receipt_no VARCHAR(32) NOT NULL,
                receipt_date DATETIME NOT NULL,
                customer_id BIGINT UNSIGNED NULL,
                party_id BIGINT UNSIGNED NULL,
                amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
                method VARCHAR(30) NOT NULL DEFAULT 'CASH',
                cashbox_id BIGINT UNSIGNED DEFAULT NULL,
                ref_no VARCHAR(80) DEFAULT NULL,
                pay_method VARCHAR(20) NOT NULL DEFAULT 'cash',
                cheque_no VARCHAR(50) DEFAULT NULL,
                cheque_due_date DATE DEFAULT NULL,
                beneficiary_name VARCHAR(150) DEFAULT NULL,
                cheque_bank VARCHAR(100) DEFAULT NULL,
                drawer_name VARCHAR(150) DEFAULT NULL,
                bank_name VARCHAR(150) DEFAULT NULL,
                transfer_ref VARCHAR(100) DEFAULT NULL,
                transfer_date DATE DEFAULT NULL,
                sender_name VARCHAR(150) DEFAULT NULL,
                notes VARCHAR(255) DEFAULT NULL,
                status VARCHAR(12) NOT NULL DEFAULT 'POSTED',
                user_id BIGINT UNSIGNED NOT NULL,
                created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY (id),
                UNIQUE KEY uq_customer_receipts_no (receipt_no),
                KEY idx_customer_receipts_customer (customer_id),
                KEY idx_customer_receipts_party (party_id),
                KEY idx_customer_receipts_date (receipt_date),
                KEY idx_customer_receipts_cashbox (cashbox_id)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;");
        } else {
            try {
                SchemaSupport::ensureColumn($pdo, 'customer_receipts', 'cashbox_id', "cashbox_id BIGINT UNSIGNED DEFAULT NULL AFTER method");
                SchemaSupport::ensureColumn($pdo, 'customer_receipts', 'pay_method', "pay_method VARCHAR(20) NOT NULL DEFAULT 'cash' AFTER ref_no");
                SchemaSupport::ensureColumn($pdo, 'customer_receipts', 'cheque_no', "cheque_no VARCHAR(50) DEFAULT NULL AFTER pay_method");
                SchemaSupport::ensureColumn($pdo, 'customer_receipts', 'cheque_due_date', "cheque_due_date DATE DEFAULT NULL AFTER cheque_no");
                SchemaSupport::ensureColumn($pdo, 'customer_receipts', 'beneficiary_name', "beneficiary_name VARCHAR(150) DEFAULT NULL AFTER cheque_due_date");
                SchemaSupport::ensureColumn($pdo, 'customer_receipts', 'cheque_bank', "cheque_bank VARCHAR(100) DEFAULT NULL AFTER beneficiary_name");
                SchemaSupport::ensureColumn($pdo, 'customer_receipts', 'drawer_name', "drawer_name VARCHAR(150) DEFAULT NULL AFTER cheque_bank");
                SchemaSupport::ensureColumn($pdo, 'customer_receipts', 'bank_name', "bank_name VARCHAR(150) DEFAULT NULL AFTER drawer_name");
                SchemaSupport::ensureColumn($pdo, 'customer_receipts', 'transfer_ref', "transfer_ref VARCHAR(100) DEFAULT NULL AFTER bank_name");
                SchemaSupport::ensureColumn($pdo, 'customer_receipts', 'transfer_date', "transfer_date DATE DEFAULT NULL AFTER transfer_ref");
                SchemaSupport::ensureColumn($pdo, 'customer_receipts', 'sender_name', "sender_name VARCHAR(150) DEFAULT NULL AFTER transfer_date");
            } catch (Throwable $e) {}
        }

        self::ensureCashboxesSchema($pdo);
        SchemaSupport::ensureColumn($pdo, 'cash_payments', 'supplier_id', "BIGINT UNSIGNED NULL AFTER cashbox_id");
        RbacSupport::ensurePermissionsSeed($pdo);
    }

    public static function ensureCustomerReceiptsPartySchema(PDO $pdo): void
    {
        if (!SchemaSupport::tableExists($pdo, 'customer_receipts')) return;
        PartySchemaSupport::ensurePartiesSchema($pdo);
        self::ensureCustomerReceiptsSchema($pdo);
        try { $pdo->exec("ALTER TABLE `customer_receipts` MODIFY `customer_id` BIGINT UNSIGNED NULL"); } catch (Throwable $e) {}
        PartySchemaSupport::dropForeignKeysReferencing($pdo, 'customer_receipts', 'customers');
        try {
            try { $pdo->exec("ALTER TABLE `customer_receipts` ADD INDEX `idx_cr_party` (`party_id`)"); } catch (Throwable $e2) {}
            $dbName = $pdo->query("SELECT DATABASE()")->fetchColumn();
            $st2 = $pdo->prepare("SELECT 1 FROM information_schema.REFERENTIAL_CONSTRAINTS
                                  WHERE CONSTRAINT_SCHEMA = ? AND TABLE_NAME='customer_receipts' AND CONSTRAINT_NAME='fk_cr_party' LIMIT 1");
            $st2->execute([$dbName]);
            if (!(bool)$st2->fetchColumn()) {
                $pdo->exec("ALTER TABLE `customer_receipts` ADD CONSTRAINT `fk_cr_party` FOREIGN KEY (`party_id`) REFERENCES `parties`(`id`)");
            }
        } catch (Throwable $e) {}
    }

    public static function ensureCashboxesSchema(PDO $pdo): void
    {
        if (!SchemaSupport::tableExists($pdo, 'cashboxes')) {
            $pdo->exec("CREATE TABLE IF NOT EXISTS cashboxes (
                id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                name VARCHAR(120) NOT NULL,
                opening_balance DECIMAL(14,2) NOT NULL DEFAULT 0.00,
                is_active TINYINT(1) NOT NULL DEFAULT 1,
                created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY (id),
                UNIQUE KEY uq_cashboxes_name (name)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;");
            try { $pdo->exec("INSERT IGNORE INTO cashboxes (id,name,opening_balance,is_active) VALUES (1,'الخزنة الرئيسية',0,1)"); } catch (Throwable $e) {}
        }
        if (!SchemaSupport::tableExists($pdo, 'cashbox_transactions')) {
            $pdo->exec("CREATE TABLE IF NOT EXISTS cashbox_transactions (
                id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                cashbox_id BIGINT UNSIGNED NOT NULL,
                trx_date DATETIME NOT NULL,
                trx_type VARCHAR(12) NOT NULL,
                amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
                ref_type VARCHAR(30) DEFAULT NULL,
                ref_id BIGINT UNSIGNED DEFAULT NULL,
                notes VARCHAR(255) DEFAULT NULL,
                user_id BIGINT UNSIGNED NOT NULL,
                created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY (id),
                KEY idx_cashbox_trx_cashbox (cashbox_id),
                KEY idx_cashbox_trx_date (trx_date)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;");
        }
        SchemaSupport::ensureColumn($pdo, 'cash_payments', 'supplier_id', "BIGINT UNSIGNED NULL AFTER cashbox_id");
        RbacSupport::ensurePermissionsSeed($pdo);
    }

    public static function ensureCashPaymentsSchema(PDO $pdo): void
    {
        PartySchemaSupport::ensurePartiesSchema($pdo);
        if (SchemaSupport::tableExists($pdo, 'cash_payments')) {
            SchemaSupport::ensureColumn($pdo, 'cash_payments', 'party_id', "BIGINT UNSIGNED NULL AFTER `supplier_id`");
            try { $pdo->exec("ALTER TABLE cash_payments ADD INDEX idx_cp_party_date (party_id, pay_date)"); } catch (Throwable $e) {}
        }
        self::ensureCashboxesSchema($pdo);
        if (!SchemaSupport::tableExists($pdo, 'cash_payments')) {
            $pdo->exec("CREATE TABLE IF NOT EXISTS cash_payments (
                id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                voucher_no VARCHAR(32) NOT NULL,
                voucher_date DATETIME NOT NULL,
                cashbox_id BIGINT UNSIGNED NOT NULL,
                supplier_id BIGINT UNSIGNED NULL,
                party_id BIGINT UNSIGNED NULL,
                amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
                pay_method VARCHAR(20) NOT NULL DEFAULT 'cash',
                payee_name VARCHAR(150) NOT NULL,
                purpose VARCHAR(255) DEFAULT NULL,
                ref_no VARCHAR(80) DEFAULT NULL,
                notes VARCHAR(255) DEFAULT NULL,
                status VARCHAR(12) NOT NULL DEFAULT 'POSTED',
                user_id BIGINT UNSIGNED NOT NULL,
                created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY (id),
                UNIQUE KEY uq_cash_payments_no (voucher_no),
                KEY idx_cash_payments_cashbox (cashbox_id),
                KEY idx_cash_payments_supplier (supplier_id),
                KEY idx_cash_payments_party (party_id),
                KEY idx_cash_payments_date (voucher_date)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;");
        }
        SchemaSupport::ensureColumn($pdo, 'cash_payments', 'supplier_id', "BIGINT UNSIGNED NULL AFTER cashbox_id");
        RbacSupport::ensurePermissionsSeed($pdo);
    }

    public static function ensureCashPaymentsPartySchema(PDO $pdo): void
    {
        if (!SchemaSupport::tableExists($pdo, 'cash_payments')) return;
        PartySchemaSupport::ensurePartiesSchema($pdo);
        self::ensureCashPaymentsSchema($pdo);
        try { $pdo->exec("ALTER TABLE `cash_payments` MODIFY `supplier_id` BIGINT UNSIGNED NULL"); } catch (Throwable $e) {}
        try { SchemaSupport::ensureColumn($pdo, 'cash_payments', 'party_id', "BIGINT UNSIGNED NULL AFTER `supplier_id`"); } catch (Throwable $e) {}
        PartySchemaSupport::dropForeignKeysReferencing($pdo, 'cash_payments', 'suppliers');
        try {
            try { $pdo->exec("ALTER TABLE `cash_payments` ADD INDEX `idx_cp_party` (`party_id`)"); } catch (Throwable $e2) {}
            $dbName = $pdo->query("SELECT DATABASE()")->fetchColumn();
            $st2 = $pdo->prepare("SELECT 1 FROM information_schema.REFERENTIAL_CONSTRAINTS
                                  WHERE CONSTRAINT_SCHEMA = ? AND TABLE_NAME='cash_payments' AND CONSTRAINT_NAME='fk_cp_party' LIMIT 1");
            $st2->execute([$dbName]);
            if (!(bool)$st2->fetchColumn()) {
                $pdo->exec("ALTER TABLE `cash_payments` ADD CONSTRAINT `fk_cp_party` FOREIGN KEY (`party_id`) REFERENCES `parties`(`id`)");
            }
        } catch (Throwable $e) {}
    }

    public static function nextReceiptNo(PDO $pdo): string
    {
        $no = 'CR-' . date('Y') . '-' . str_pad((string) rand(1, 99999), 5, '0', STR_PAD_LEFT);
        $tries = 0;
        while ($tries < 10) {
            $chk = $pdo->prepare("SELECT 1 FROM customer_receipts WHERE receipt_no=?");
            $chk->execute([$no]);
            if (!$chk->fetch()) break;
            $no = 'CR-' . date('Y') . '-' . str_pad((string) rand(1, 99999), 5, '0', STR_PAD_LEFT);
            $tries++;
        }
        return $no;
    }

    public static function ensureAppSettingsSchema(PDO $pdo): void
    {
        $pdo->exec("CREATE TABLE IF NOT EXISTS app_settings (setting_key VARCHAR(120) NOT NULL PRIMARY KEY, setting_value LONGTEXT NULL, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
    }

    public static function ensureBankAccountsSchema(PDO $pdo): void
    {
        $pdo->exec("CREATE TABLE IF NOT EXISTS bank_accounts (
            id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(150) NOT NULL,
            bank_name VARCHAR(150) NULL,
            account_no VARCHAR(100) NULL,
            iban VARCHAR(100) NULL,
            opening_balance DECIMAL(18,2) NOT NULL DEFAULT 0.00,
            currency_code VARCHAR(10) NOT NULL DEFAULT 'EGP',
            is_active TINYINT(1) NOT NULL DEFAULT 1,
            notes TEXT NULL,
            created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            KEY idx_bank_accounts_name (name)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
        $pdo->exec("CREATE TABLE IF NOT EXISTS bank_transactions (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            bank_account_id INT(11) NOT NULL,
            trx_date DATETIME NOT NULL,
            trx_type ENUM('in','out') NOT NULL,
            amount DECIMAL(18,2) NOT NULL DEFAULT 0.00,
            source_type VARCHAR(50) NULL,
            source_id INT(11) NULL,
            ref_no VARCHAR(100) NULL,
            notes TEXT NULL,
            user_id INT(11) NULL,
            created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_bank_trx_acc_date (bank_account_id, trx_date)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
    }

    public static function ensureExpensesSchema(PDO $pdo): void
    {
        $pdo->exec("CREATE TABLE IF NOT EXISTS expense_categories (id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(150) NOT NULL, code VARCHAR(50) NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, sort_order INT(11) NOT NULL DEFAULT 0, notes VARCHAR(255) NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
        $pdo->exec("CREATE TABLE IF NOT EXISTS expense_items (id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, category_id INT(11) NOT NULL, name VARCHAR(150) NOT NULL, code VARCHAR(50) NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, sort_order INT(11) NOT NULL DEFAULT 0, notes VARCHAR(255) NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP, INDEX idx_expense_items_cat (category_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
    }

    public static function ensureUsersCashboxSchema(PDO $pdo): void
    {
        if (!SchemaSupport::tableExists($pdo, 'users')) {
            $pdo->exec("CREATE TABLE IF NOT EXISTS users (id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) NOT NULL UNIQUE, password_hash VARCHAR(255) NOT NULL, full_name VARCHAR(120) NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, cashbox_id BIGINT UNSIGNED NULL, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
            return;
        }
        SchemaSupport::ensureColumn($pdo, 'users', 'cashbox_id', 'BIGINT UNSIGNED NULL');
        try { $pdo->exec("ALTER TABLE users ADD INDEX idx_users_cashbox_id (cashbox_id)"); } catch (Throwable $e) {}
    }

    public static function ensurePurchaseInvoicePaymentSchema(PDO $pdo): void
    {
        if (!SchemaSupport::tableExists($pdo, 'purchase_invoices')) return;
        SchemaSupport::ensureColumn($pdo, 'purchase_invoices', 'payment_mode', "VARCHAR(20) NOT NULL DEFAULT 'credit'");
        SchemaSupport::ensureColumn($pdo, 'purchase_invoices', 'paid_amount', 'DECIMAL(18,2) NOT NULL DEFAULT 0.00');
        SchemaSupport::ensureColumn($pdo, 'purchase_invoices', 'due_amount', 'DECIMAL(18,2) NOT NULL DEFAULT 0.00');
        SchemaSupport::ensureColumn($pdo, 'purchase_invoices', 'cashbox_id', 'BIGINT UNSIGNED NULL');
        SchemaSupport::ensureColumn($pdo, 'purchase_invoices', 'payment_note', 'TEXT NULL');
    }

    public static function ensureSalesInvoicePaymentSchema(PDO $pdo): void
    {
        if (!SchemaSupport::tableExists($pdo, 'sales_invoices')) return;
        SchemaSupport::ensureColumn($pdo, 'sales_invoices', 'payment_mode', "VARCHAR(20) NOT NULL DEFAULT 'credit'");
        SchemaSupport::ensureColumn($pdo, 'sales_invoices', 'paid_amount', 'DECIMAL(18,2) NOT NULL DEFAULT 0.00');
        SchemaSupport::ensureColumn($pdo, 'sales_invoices', 'due_amount', 'DECIMAL(18,2) NOT NULL DEFAULT 0.00');
        SchemaSupport::ensureColumn($pdo, 'sales_invoices', 'cashbox_id', 'BIGINT UNSIGNED NULL');
        SchemaSupport::ensureColumn($pdo, 'sales_invoices', 'collection_note', 'TEXT NULL');
    }
}
