<?php

declare(strict_types=1);

namespace App\Shared\Schema;

use App\Core\Auth;
use PDO;
use Throwable;

final class PartySchemaSupport
{
    public static function ensureCustomersSchema(PDO $pdo): void
    {
        if (SchemaSupport::tableExists($pdo, 'customers')) return;
        $pdo->exec("CREATE TABLE IF NOT EXISTS customers (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            code VARCHAR(40) NULL,
            name VARCHAR(160) NOT NULL,
            phone VARCHAR(40) NULL,
            phone2 VARCHAR(40) NULL,
            email VARCHAR(160) NULL,
            address VARCHAR(255) NULL,
            tax_no VARCHAR(80) NULL,
            notes VARCHAR(255) NULL,
            opening_balance DECIMAL(14,2) NOT NULL DEFAULT 0,
            is_active TINYINT(1) NOT NULL DEFAULT 1,
            created_by BIGINT UNSIGNED NULL,
            created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY uq_customers_code (code),
            KEY idx_customers_name (name),
            KEY idx_customers_phone (phone)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    }

    public static function findExistingCustomerForSupplier(PDO $pdo, array $supplierRow): ?array
    {
        if (!SchemaSupport::tableExists($pdo, 'customers')) return null;
        $phone = trim((string) ($supplierRow['phone'] ?? ''));
        $name = trim((string) ($supplierRow['name'] ?? ''));
        if ($phone !== '') {
            $st = $pdo->prepare("SELECT * FROM customers WHERE phone = ? OR phone2 = ? LIMIT 1");
            $st->execute([$phone, $phone]);
            $row = $st->fetch();
            if ($row) return $row;
        }
        if ($name !== '') {
            $st = $pdo->prepare("SELECT * FROM customers WHERE name = ? LIMIT 1");
            $st->execute([$name]);
            $row = $st->fetch();
            if ($row) return $row;
        }
        return null;
    }

    public static function createCustomerFromSupplier(PDO $pdo, array $supplierRow): int
    {
        self::ensureCustomersSchema($pdo);
        $name = trim((string) ($supplierRow['name'] ?? ''));
        if ($name === '') $name = 'عميل';
        $phone = trim((string) ($supplierRow['phone'] ?? '')) ?: null;
        $phone2 = trim((string) ($supplierRow['phone2'] ?? '')) ?: null;
        $email = trim((string) ($supplierRow['email'] ?? '')) ?: null;
        $address = trim((string) ($supplierRow['address'] ?? '')) ?: null;
        $tax_no = trim((string) ($supplierRow['tax_no'] ?? '')) ?: null;
        $notes = trim((string) ($supplierRow['notes'] ?? '')) ?: null;
        $st = $pdo->prepare("INSERT INTO customers (code, name, phone, phone2, email, address, tax_no, notes, opening_balance, is_active, created_by)
                             VALUES (?,?,?,?,?,?,?,?,?,?,?)");
        $st->execute([null, $name, $phone, $phone2, $email, $address, $tax_no, $notes, 0, 1, (int) (Auth::id() ?? 0) ?: null]);
        $newId = (int) $pdo->lastInsertId();
        $auto = 'C' . str_pad((string) $newId, 5, '0', STR_PAD_LEFT);
        $pdo->prepare("UPDATE customers SET code=? WHERE id=? AND (code IS NULL OR code='')")->execute([$auto, $newId]);
        return $newId;
    }

    public static function linkSupplierToCustomer(PDO $pdo, int $supplierId, int $customerId): void
    {
        if ($supplierId <= 0 || $customerId <= 0) return;
        if (!SchemaSupport::tableExists($pdo, 'suppliers') || !SchemaSupport::tableExists($pdo, 'customers')) return;
        SchemaSupport::ensureColumn($pdo, 'suppliers', 'linked_customer_id', "BIGINT UNSIGNED NULL AFTER `created_by`");
        SchemaSupport::ensureColumn($pdo, 'customers', 'linked_supplier_id', "BIGINT UNSIGNED NULL AFTER `created_by`");
        $pdo->prepare("UPDATE suppliers SET linked_customer_id=? WHERE id=?")->execute([$customerId, $supplierId]);
        $pdo->prepare("UPDATE customers SET linked_supplier_id=? WHERE id=?")->execute([$supplierId, $customerId]);
    }

    public static function unlinkSupplierCustomer(PDO $pdo, int $supplierId): void
    {
        if ($supplierId <= 0) return;
        if (!SchemaSupport::tableExists($pdo, 'suppliers') || !SchemaSupport::tableExists($pdo, 'customers')) return;
        $customerId = 0;
        try {
            $st = $pdo->prepare("SELECT linked_customer_id FROM suppliers WHERE id=? LIMIT 1");
            $st->execute([$supplierId]);
            $row = $st->fetch();
            $customerId = $row && isset($row['linked_customer_id']) ? (int) $row['linked_customer_id'] : 0;
        } catch (Throwable $e) { $customerId = 0; }
        try {
            if ($customerId > 0) {
                $pdo->prepare("UPDATE customers SET linked_supplier_id=NULL WHERE id=?")->execute([$customerId]);
            }
            $pdo->prepare("UPDATE suppliers SET linked_customer_id=NULL WHERE id=?")->execute([$supplierId]);
        } catch (Throwable $e) {}
    }

    public static function ensurePartiesSchema(PDO $pdo): void
    {
        if (!SchemaSupport::tableExists($pdo, 'parties')) {
            $pdo->exec("CREATE TABLE IF NOT EXISTS parties (
                id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                code VARCHAR(40) NULL,
                name VARCHAR(160) NOT NULL,
                phone VARCHAR(40) NULL,
                phone2 VARCHAR(40) NULL,
                email VARCHAR(160) NULL,
                address VARCHAR(255) NULL,
                tax_no VARCHAR(80) NULL,
                notes VARCHAR(255) NULL,
                opening_balance DECIMAL(14,2) NOT NULL DEFAULT 0,
                credit_limit DECIMAL(14,2) NOT NULL DEFAULT 0,
                is_customer TINYINT(1) NOT NULL DEFAULT 0,
                is_supplier TINYINT(1) NOT NULL DEFAULT 0,
                is_active TINYINT(1) NOT NULL DEFAULT 1,
                created_by BIGINT UNSIGNED NULL,
                created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY (id),
                UNIQUE KEY uq_parties_code (code),
                KEY idx_parties_name (name),
                KEY idx_parties_phone (phone),
                KEY idx_parties_roles (is_customer, is_supplier)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
        }
        try { SchemaSupport::ensureColumn($pdo, 'parties', 'credit_limit', "DECIMAL(14,2) NOT NULL DEFAULT 0 AFTER `opening_balance`"); } catch (Throwable $e) {}
    }

    public static function ensurePartyLedgerSchema(PDO $pdo): void
    {
        if (SchemaSupport::tableExists($pdo, 'party_ledger')) return;
        self::ensurePartiesSchema($pdo);
        $pdo->exec("CREATE TABLE IF NOT EXISTS party_ledger (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            party_id BIGINT UNSIGNED NOT NULL,
            doc_type VARCHAR(20) NOT NULL,
            doc_id BIGINT UNSIGNED NOT NULL,
            doc_no VARCHAR(40) NULL,
            doc_date DATE NOT NULL,
            debit DECIMAL(14,2) NOT NULL DEFAULT 0,
            credit DECIMAL(14,2) NOT NULL DEFAULT 0,
            notes VARCHAR(255) NULL,
            created_by BIGINT UNSIGNED NULL,
            created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_pl_party_date (party_id, doc_date),
            KEY idx_pl_doc (doc_type, doc_id),
            CONSTRAINT fk_pl_party FOREIGN KEY (party_id) REFERENCES parties(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    }

    public static function ensurePurchaseInvoicesPartySchema(PDO $pdo): void
    {
        if (!SchemaSupport::tableExists($pdo, 'purchase_invoices')) return;
        self::ensurePartiesSchema($pdo);
        try { SchemaSupport::ensureColumn($pdo, 'purchase_invoices', 'party_id', "BIGINT UNSIGNED NULL AFTER `supplier_id`"); } catch (Throwable $e) {}
        try { $pdo->exec("ALTER TABLE `purchase_invoices` MODIFY `supplier_id` BIGINT UNSIGNED NULL"); } catch (Throwable $e) {}
        try {
            $dbName = $pdo->query("SELECT DATABASE()")->fetchColumn();
            $st = $pdo->prepare("SELECT CONSTRAINT_NAME FROM information_schema.KEY_COLUMN_USAGE
                                 WHERE TABLE_SCHEMA = ? AND TABLE_NAME = 'purchase_invoices'
                                   AND REFERENCED_TABLE_NAME = 'suppliers' AND CONSTRAINT_NAME <> 'PRIMARY' LIMIT 5");
            $st->execute([$dbName]);
            $rows = $st->fetchAll() ?: [];
            foreach ($rows as $r) {
                $fk = $r['CONSTRAINT_NAME'] ?? null;
                if ($fk) { try { $pdo->exec("ALTER TABLE `purchase_invoices` DROP FOREIGN KEY `$fk`"); } catch (Throwable $e2) {} }
            }
        } catch (Throwable $e) {}
        try {
            try { $pdo->exec("ALTER TABLE `purchase_invoices` ADD INDEX `idx_pi_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 = 'purchase_invoices' AND CONSTRAINT_NAME = 'fk_pi_party' LIMIT 1");
            $st2->execute([$dbName]);
            if (!(bool) $st2->fetchColumn()) {
                $pdo->exec("ALTER TABLE `purchase_invoices` ADD CONSTRAINT `fk_pi_party` FOREIGN KEY (`party_id`) REFERENCES `parties`(`id`)");
            }
        } catch (Throwable $e) {}
    }

    public static function dropForeignKeysReferencing(PDO $pdo, string $table, string $referencedTable): void
    {
        try {
            $dbName = $pdo->query("SELECT DATABASE()")->fetchColumn();
            if (!$dbName) return;
            $st = $pdo->prepare("SELECT CONSTRAINT_NAME FROM information_schema.KEY_COLUMN_USAGE
                                 WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ? AND REFERENCED_TABLE_NAME = ? AND CONSTRAINT_NAME <> 'PRIMARY'");
            $st->execute([$dbName, $table, $referencedTable]);
            $rows = $st->fetchAll() ?: [];
            foreach ($rows as $r) {
                $fk = $r['CONSTRAINT_NAME'] ?? null;
                if ($fk) { try { $pdo->exec("ALTER TABLE `{$table}` DROP FOREIGN KEY `{$fk}`"); } catch (Throwable $e2) {} }
            }
        } catch (Throwable $e) {}
    }

    public static function ensurePurchaseReturnsPartySchema(PDO $pdo): void
    {
        if (!SchemaSupport::tableExists($pdo, 'purchase_returns')) return;
        self::ensurePartiesSchema($pdo);
        try { SchemaSupport::ensureColumn($pdo, 'purchase_returns', 'party_id', "BIGINT UNSIGNED NULL AFTER `supplier_id`"); } catch (Throwable $e) {}
        try { $pdo->exec("ALTER TABLE `purchase_returns` MODIFY `supplier_id` BIGINT UNSIGNED NULL"); } catch (Throwable $e) {}
        self::dropForeignKeysReferencing($pdo, 'purchase_returns', 'suppliers');
        try {
            try { $pdo->exec("ALTER TABLE `purchase_returns` ADD INDEX `idx_pr_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='purchase_returns' AND CONSTRAINT_NAME='fk_pr_party' LIMIT 1");
            $st2->execute([$dbName]);
            if (!(bool) $st2->fetchColumn()) {
                $pdo->exec("ALTER TABLE `purchase_returns` ADD CONSTRAINT `fk_pr_party` FOREIGN KEY (`party_id`) REFERENCES `parties`(`id`)");
            }
        } catch (Throwable $e) {}
    }

    public static function ensureSalesInvoicesPartySchema(PDO $pdo): void
    {
        if (!SchemaSupport::tableExists($pdo, 'sales_invoices')) return;
        self::ensurePartiesSchema($pdo);
        try { SchemaSupport::ensureColumn($pdo, 'sales_invoices', 'party_id', "BIGINT UNSIGNED NULL AFTER `customer_id`"); } catch (Throwable $e) {}
        try { $pdo->exec("ALTER TABLE `sales_invoices` MODIFY `customer_id` BIGINT UNSIGNED NULL"); } catch (Throwable $e) {}
        self::dropForeignKeysReferencing($pdo, 'sales_invoices', 'customers');
        try {
            try { $pdo->exec("ALTER TABLE `sales_invoices` ADD INDEX `idx_si_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='sales_invoices' AND CONSTRAINT_NAME='fk_si_party' LIMIT 1");
            $st2->execute([$dbName]);
            if (!(bool) $st2->fetchColumn()) {
                $pdo->exec("ALTER TABLE `sales_invoices` ADD CONSTRAINT `fk_si_party` FOREIGN KEY (`party_id`) REFERENCES `parties`(`id`)");
            }
        } catch (Throwable $e) {}
    }

    public static function ensureItemsPartySchema(PDO $pdo): void
    {
        if (!SchemaSupport::tableExists($pdo, 'items')) return;
        self::dropForeignKeysReferencing($pdo, 'items', 'suppliers');
        try { $pdo->exec("ALTER TABLE `items` MODIFY `default_supplier_id` BIGINT UNSIGNED NULL"); } catch (Throwable $e) {}
    }

    public static function ensurePartiesFullSchema(PDO $pdo): void
    {
        self::ensurePartiesSchema($pdo);
        self::ensurePartyLedgerSchema($pdo);
        self::ensureSalesInvoicesPartySchema($pdo);
        self::ensurePurchaseInvoicesPartySchema($pdo);
        self::ensurePurchaseReturnsPartySchema($pdo);
        FinanceSchemaSupport::ensureCustomerReceiptsPartySchema($pdo);
        FinanceSchemaSupport::ensureCashPaymentsPartySchema($pdo);
        self::ensureItemsPartySchema($pdo);
    }

    public static function ensurePartiesMigrateFromCustomersSuppliers(PDO $pdo): void
    {
        self::ensurePartiesSchema($pdo);
        if (SchemaSupport::tableExists($pdo, 'customers')) {
            $rows = $pdo->query("SELECT * FROM customers ORDER BY id ASC")->fetchAll() ?: [];
            foreach ($rows as $c) {
                $phone = trim((string) ($c['phone'] ?? ''));
                $name = trim((string) ($c['name'] ?? ''));
                $code = trim((string) ($c['code'] ?? ''));
                $party = null;
                if ($code !== '') { $st = $pdo->prepare("SELECT * FROM parties WHERE code=? LIMIT 1"); $st->execute([$code]); $party = $st->fetch(); }
                if (!$party && $phone !== '') { $st = $pdo->prepare("SELECT * FROM parties WHERE phone=? OR phone2=? LIMIT 1"); $st->execute([$phone, $phone]); $party = $st->fetch(); }
                if (!$party && $name !== '') { $st = $pdo->prepare("SELECT * FROM parties WHERE name=? LIMIT 1"); $st->execute([$name]); $party = $st->fetch(); }
                if ($party) {
                    $pdo->prepare("UPDATE parties SET is_customer=1 WHERE id=?")->execute([(int) $party['id']]);
                } else {
                    $st = $pdo->prepare("INSERT INTO parties (code,name,phone,phone2,email,address,tax_no,notes,opening_balance,is_customer,is_supplier,is_active,created_by)
                                         VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)");
                    $st->execute([$c['code'] ?? null, $c['name'], $c['phone'] ?? null, $c['phone2'] ?? null, $c['email'] ?? null, $c['address'] ?? null, $c['tax_no'] ?? null, $c['notes'] ?? null, (float) ($c['opening_balance'] ?? 0), 1, 0, (int) ($c['is_active'] ?? 1), $c['created_by'] ?? null]);
                    $pid = (int) $pdo->lastInsertId();
                    party_auto_code($pdo, $pid);
                }
            }
        }
        if (SchemaSupport::tableExists($pdo, 'suppliers')) {
            $rows = $pdo->query("SELECT * FROM suppliers ORDER BY id ASC")->fetchAll() ?: [];
            foreach ($rows as $s) {
                $phone = trim((string) ($s['phone'] ?? ''));
                $name = trim((string) ($s['name'] ?? ''));
                $code = trim((string) ($s['code'] ?? ''));
                $party = null;
                if ($code !== '') { $st = $pdo->prepare("SELECT * FROM parties WHERE code=? LIMIT 1"); $st->execute([$code]); $party = $st->fetch(); }
                if (!$party && $phone !== '') { $st = $pdo->prepare("SELECT * FROM parties WHERE phone=? OR phone2=? LIMIT 1"); $st->execute([$phone, $phone]); $party = $st->fetch(); }
                if (!$party && $name !== '') { $st = $pdo->prepare("SELECT * FROM parties WHERE name=? LIMIT 1"); $st->execute([$name]); $party = $st->fetch(); }
                if ($party) {
                    $pdo->prepare("UPDATE parties SET is_supplier=1 WHERE id=?")->execute([(int) $party['id']]);
                } else {
                    $st = $pdo->prepare("INSERT INTO parties (code,name,phone,phone2,email,address,tax_no,notes,opening_balance,is_customer,is_supplier,is_active,created_by)
                                         VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)");
                    $st->execute([$s['code'] ?? null, $s['name'], $s['phone'] ?? null, $s['phone2'] ?? null, $s['email'] ?? null, $s['address'] ?? null, $s['tax_no'] ?? null, $s['notes'] ?? null, (float) ($s['opening_balance'] ?? 0), 0, 1, (int) ($s['is_active'] ?? 1), $s['created_by'] ?? null]);
                    $pid = (int) $pdo->lastInsertId();
                    party_auto_code($pdo, $pid);
                }
            }
        }
    }
}
