<?php

class Customers {
    private PDO $conn;

    public function __construct(PDO $conn, bool $skipSchema = false) {
        $this->conn = $conn;
        if (!$skipSchema) {
            $this->ensureSchema();
        }
    }

    public function ensureSchema(): void {
        $this->conn->exec("CREATE TABLE IF NOT EXISTS customers (
            customer_id INT NOT NULL AUTO_INCREMENT,
            customer_code VARCHAR(40) NOT NULL,
            customer_name VARCHAR(255) NOT NULL,
            customer_phone VARCHAR(30) DEFAULT NULL,
            customer_email VARCHAR(120) DEFAULT NULL,
            customer_address VARCHAR(255) DEFAULT NULL,
            buyer_tin VARCHAR(30) DEFAULT NULL,
            status ENUM('active','inactive') NOT NULL DEFAULT 'active',
            created_by INT DEFAULT NULL,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (customer_id),
            UNIQUE KEY uq_customer_code (customer_code),
            KEY idx_customers_name (customer_name),
            KEY idx_customers_phone (customer_phone)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci");

        $this->conn->exec("CREATE TABLE IF NOT EXISTS customer_ledger (
            ledger_id INT NOT NULL AUTO_INCREMENT,
            customer_id INT NOT NULL,
            branch_id INT DEFAULT NULL,
            invoice_type ENUM('sale','credit_sale') NOT NULL DEFAULT 'sale',
            invoice_number VARCHAR(120) NOT NULL,
            transaction_id VARCHAR(120) DEFAULT NULL,
            credit_id INT DEFAULT NULL,
            sale_id INT DEFAULT NULL,
            entry_type ENUM('invoice','payment','adjustment') NOT NULL,
            debit DECIMAL(14,2) NOT NULL DEFAULT 0.00,
            credit DECIMAL(14,2) NOT NULL DEFAULT 0.00,
            balance_after DECIMAL(14,2) NOT NULL DEFAULT 0.00,
            payment_method VARCHAR(40) DEFAULT NULL,
            notes VARCHAR(255) DEFAULT NULL,
            entry_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            recorded_by INT DEFAULT NULL,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (ledger_id),
            KEY idx_customer_ledger_customer (customer_id, entry_date),
            KEY idx_customer_ledger_invoice (invoice_type, invoice_number),
            KEY idx_customer_ledger_credit (credit_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci");

        $this->ensureColumn('sold_items', 'customer_id', 'INT DEFAULT NULL');
        $this->ensureColumn('credit_accounts', 'customer_id', 'INT DEFAULT NULL');
    }

    private function ensureColumn(string $table, string $column, string $definition): void {
        if (!$this->tableExists($table)) {
            return;
        }
        $stmt = $this->conn->query("SHOW COLUMNS FROM `{$table}` LIKE " . $this->conn->quote($column));
        if (!$stmt->fetchColumn()) {
            $this->conn->exec("ALTER TABLE `{$table}` ADD COLUMN `{$column}` {$definition}");
        }
    }

    private function tableExists(string $table): bool {
        $stmt = $this->conn->prepare('SHOW TABLES LIKE :table_name');
        $stmt->execute([':table_name' => $table]);
        return (bool)$stmt->fetchColumn();
    }

    public function findOrCreate(?int $customerId, string $name, ?string $phone, ?string $buyerTin, ?int $userId): int {
        $name = trim($name);
        $phone = trim((string)$phone) ?: null;
        $buyerTin = trim((string)$buyerTin) ?: null;
        if ($customerId && $this->getById($customerId)) {
            $this->updateCustomer($customerId, $name, $phone, $buyerTin);
            return $customerId;
        }
        if ($phone) {
            $stmt = $this->conn->prepare('SELECT customer_id FROM customers WHERE customer_phone = :phone LIMIT 1');
            $stmt->execute([':phone' => $phone]);
            $existing = (int)$stmt->fetchColumn();
            if ($existing > 0) {
                $this->updateCustomer($existing, $name, $phone, $buyerTin);
                return $existing;
            }
        }
        $stmt = $this->conn->prepare('SELECT customer_id FROM customers WHERE customer_name = :name AND COALESCE(customer_phone, "") = COALESCE(:phone, "") LIMIT 1');
        $stmt->execute([':name' => $name, ':phone' => $phone]);
        $existing = (int)$stmt->fetchColumn();
        if ($existing > 0) {
            $this->updateCustomer($existing, $name, $phone, $buyerTin);
            return $existing;
        }

        $code = $this->generateCustomerCode();
        $insert = $this->conn->prepare('INSERT INTO customers (customer_code, customer_name, customer_phone, buyer_tin, created_by) VALUES (:code, :name, :phone, :tin, :created_by)');
        $insert->execute([
            ':code' => $code,
            ':name' => $name,
            ':phone' => $phone,
            ':tin' => $buyerTin,
            ':created_by' => $userId,
        ]);
        return (int)$this->conn->lastInsertId();
    }

    private function updateCustomer(int $customerId, string $name, ?string $phone, ?string $buyerTin): void {
        $stmt = $this->conn->prepare('UPDATE customers SET customer_name = :name, customer_phone = :phone, buyer_tin = COALESCE(:tin, buyer_tin) WHERE customer_id = :id');
        $stmt->execute([':name' => $name, ':phone' => $phone, ':tin' => $buyerTin, ':id' => $customerId]);
    }

    public function getById(int $customerId): ?array {
        $stmt = $this->conn->prepare('SELECT * FROM customers WHERE customer_id = :id');
        $stmt->execute([':id' => $customerId]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return $row ?: null;
    }

    public function search(string $term, int $limit = 12): array {
        $term = trim($term);
        if ($term === '') {
            return [];
        }
        $cap = max(1, min(50, (int)$limit));
        // Rank prefix matches first (index-friendly), then mid-word matches
        $stmt = $this->conn->prepare("
            SELECT customer_id, customer_code, customer_name, customer_phone, buyer_tin,
                CASE WHEN customer_name LIKE ? THEN 0 ELSE 1 END AS _rank
            FROM customers
            WHERE customer_name LIKE ? OR customer_phone LIKE ? OR customer_code LIKE ?
            ORDER BY _rank ASC, customer_name ASC
            LIMIT {$cap}
        ");
        $prefix = $term . '%';
        $anywhere = '%' . $term . '%';
        $stmt->execute([$prefix, $anywhere, $anywhere, $anywhere]);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
        foreach ($rows as &$row) {
            unset($row['_rank']);
        }
        return $rows;
    }

    public function recordInvoice(int $customerId, ?int $branchId, string $invoiceType, string $invoiceNumber, ?string $transactionId, ?int $creditId, ?int $saleId, float $amount, ?int $userId, string $notes = 'Invoice'): void {
        $this->addLedgerEntry($customerId, $branchId, $invoiceType, $invoiceNumber, $transactionId, $creditId, $saleId, 'invoice', $amount, 0, null, $notes, $userId);
    }

    public function recordPayment(int $customerId, ?int $branchId, string $invoiceType, string $invoiceNumber, ?string $transactionId, ?int $creditId, ?int $saleId, float $amount, ?string $paymentMethod, ?int $userId, string $notes = 'Payment'): void {
        $this->addLedgerEntry($customerId, $branchId, $invoiceType, $invoiceNumber, $transactionId, $creditId, $saleId, 'payment', 0, $amount, $paymentMethod, $notes, $userId);
    }

    private function addLedgerEntry(int $customerId, ?int $branchId, string $invoiceType, string $invoiceNumber, ?string $transactionId, ?int $creditId, ?int $saleId, string $entryType, float $debit, float $credit, ?string $paymentMethod, string $notes, ?int $userId): void {
        $balance = $this->getInvoiceBalance($customerId, $invoiceType, $invoiceNumber) + round($debit, 2) - round($credit, 2);
        $stmt = $this->conn->prepare('INSERT INTO customer_ledger (customer_id, branch_id, invoice_type, invoice_number, transaction_id, credit_id, sale_id, entry_type, debit, credit, balance_after, payment_method, notes, recorded_by) VALUES (:customer_id, :branch_id, :invoice_type, :invoice_number, :transaction_id, :credit_id, :sale_id, :entry_type, :debit, :credit, :balance_after, :payment_method, :notes, :recorded_by)');
        $stmt->execute([
            ':customer_id' => $customerId,
            ':branch_id' => $branchId,
            ':invoice_type' => $invoiceType,
            ':invoice_number' => $invoiceNumber,
            ':transaction_id' => $transactionId,
            ':credit_id' => $creditId,
            ':sale_id' => $saleId,
            ':entry_type' => $entryType,
            ':debit' => round($debit, 2),
            ':credit' => round($credit, 2),
            ':balance_after' => max(0, round($balance, 2)),
            ':payment_method' => $paymentMethod,
            ':notes' => $notes,
            ':recorded_by' => $userId,
        ]);
    }

    private function getInvoiceBalance(int $customerId, string $invoiceType, string $invoiceNumber): float {
        $stmt = $this->conn->prepare('SELECT COALESCE(SUM(debit - credit), 0) FROM customer_ledger WHERE customer_id = :customer_id AND invoice_type = :invoice_type AND invoice_number = :invoice_number');
        $stmt->execute([':customer_id' => $customerId, ':invoice_type' => $invoiceType, ':invoice_number' => $invoiceNumber]);
        return (float)$stmt->fetchColumn();
    }

    public function getLedger(int $customerId): array {
        $stmt = $this->conn->prepare('SELECT cl.*, b.branch_name FROM customer_ledger cl LEFT JOIN branches b ON b.branch_id = cl.branch_id WHERE cl.customer_id = :customer_id ORDER BY cl.entry_date DESC, cl.ledger_id DESC');
        $stmt->execute([':customer_id' => $customerId]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    private function generateCustomerCode(): string {
        do {
            $code = 'CUST-' . date('ymd') . '-' . random_int(1000, 9999);
            $stmt = $this->conn->prepare('SELECT COUNT(*) FROM customers WHERE customer_code = :code');
            $stmt->execute([':code' => $code]);
        } while ((int)$stmt->fetchColumn() > 0);
        return $code;
    }
}