<?php
require_once __DIR__ . '/../includes/payment_options.php';

class Suppliers {
    private PDO $conn;

    public function __construct(PDO $conn) {
        $this->conn = $conn;
        $this->ensureSchema();
    }

    public function ensureSchema(): void {
        $this->conn->exec("CREATE TABLE IF NOT EXISTS suppliers (
            supplier_id INT NOT NULL AUTO_INCREMENT,
            supplier_code VARCHAR(40) DEFAULT NULL,
            supplier_name VARCHAR(255) NOT NULL,
            contact_person VARCHAR(255) DEFAULT NULL,
            phone VARCHAR(30) DEFAULT NULL,
            email VARCHAR(100) DEFAULT NULL,
            address TEXT DEFAULT NULL,
            notes VARCHAR(255) DEFAULT NULL,
            total_invoiced DECIMAL(14,2) NOT NULL DEFAULT 0.00,
            total_paid DECIMAL(14,2) NOT NULL DEFAULT 0.00,
            balance DECIMAL(14,2) NOT NULL DEFAULT 0.00,
            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 (supplier_id),
            KEY idx_suppliers_status (status),
            KEY idx_suppliers_name (supplier_name(60))
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci");

        $this->ensureEisSupplierIdColumn();

        $this->conn->exec("CREATE TABLE IF NOT EXISTS supplier_invoices (
            invoice_id INT NOT NULL AUTO_INCREMENT,
            supplier_id INT NOT NULL,
            invoice_number VARCHAR(100) NOT NULL,
            invoice_date DATE NOT NULL,
            due_date DATE DEFAULT NULL,
            description TEXT DEFAULT NULL,
            amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
            amount_paid DECIMAL(14,2) NOT NULL DEFAULT 0.00,
            balance DECIMAL(14,2) NOT NULL DEFAULT 0.00,
            status ENUM('unpaid','partial','paid') NOT NULL DEFAULT 'unpaid',
            recorded_by INT NOT NULL,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (invoice_id),
            KEY idx_si_supplier (supplier_id),
            KEY idx_si_date (invoice_date),
            KEY idx_si_status (status)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci");

        $this->conn->exec("CREATE TABLE IF NOT EXISTS supplier_payments (
            payment_id INT NOT NULL AUTO_INCREMENT,
            supplier_id INT NOT NULL,
            invoice_id INT DEFAULT NULL,
            voucher_number VARCHAR(100) DEFAULT NULL,
            payment_date DATE NOT NULL,
            amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
            payment_method VARCHAR(40) NOT NULL DEFAULT 'cash',
            narration TEXT DEFAULT NULL,
            recorded_by INT NOT NULL,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (payment_id),
            KEY idx_sp_supplier (supplier_id),
            KEY idx_sp_invoice (invoice_id),
            KEY idx_sp_date (payment_date)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci");
    }

    private function ensureEisSupplierIdColumn(): void {
        $stmt = $this->conn->prepare("SHOW COLUMNS FROM suppliers LIKE 'eis_supplier_id'");
        $stmt->execute();
        if (!$stmt->fetch(PDO::FETCH_ASSOC)) {
            $this->conn->exec("ALTER TABLE suppliers ADD COLUMN eis_supplier_id INT DEFAULT NULL AFTER supplier_code");
        }
        foreach ([
            'eis_supplier_tin' => 'VARCHAR(30) DEFAULT NULL AFTER eis_supplier_id',
            'eis_synced_at' => 'TIMESTAMP NULL DEFAULT NULL AFTER eis_supplier_tin',
        ] as $column => $definition) {
            $stmt = $this->conn->prepare('SHOW COLUMNS FROM suppliers LIKE :column');
            $stmt->execute([':column' => $column]);
            if (!$stmt->fetch(PDO::FETCH_ASSOC)) {
                $this->conn->exec('ALTER TABLE suppliers ADD COLUMN `' . $column . '` ' . $definition);
            }
        }
    }

    /**
     * Upserts MRA EIS's registered supplier list (from /stock/get-suppliers) into the
     * local suppliers table: links to an existing supplier of the same name if one
     * isn't already tied to that eis_supplier_id, otherwise creates a new local record.
     * This is what keeps the informal-purchase supplier picker's eis_supplier_id
     * trustworthy instead of relying on someone typing it in from memory.
     *
     * @param array $eisSuppliers Raw SupplierDto[] from MRA: supplierId, supplierName, supplierTin, isActive
     * @return array ['total' => int, 'created' => int, 'updated' => int]
     */
    public function upsertFromEis(array $eisSuppliers): array {
        $created = 0;
        $updated = 0;

        foreach ($eisSuppliers as $row) {
            if (!is_array($row)) {
                continue;
            }
            $eisId = (int)($row['supplierId'] ?? $row['SupplierId'] ?? 0);
            $name = trim((string)($row['supplierName'] ?? $row['SupplierName'] ?? ''));
            if ($eisId <= 0 || $name === '') {
                continue;
            }
            $tin = trim((string)($row['supplierTin'] ?? $row['SupplierTin'] ?? '')) ?: null;
            $isActive = array_key_exists('isActive', $row) ? (bool)$row['isActive'] : (array_key_exists('IsActive', $row) ? (bool)$row['IsActive'] : true);
            $status = $isActive ? 'active' : 'inactive';

            $stmt = $this->conn->prepare('SELECT supplier_id FROM suppliers WHERE eis_supplier_id = :eis_id LIMIT 1');
            $stmt->execute([':eis_id' => $eisId]);
            $existingId = $stmt->fetchColumn();

            if (!$existingId) {
                // Not yet linked by ID - avoid creating a duplicate if a local supplier
                // with the same name already exists but has no EIS ID set.
                $stmt = $this->conn->prepare('SELECT supplier_id FROM suppliers WHERE eis_supplier_id IS NULL AND LOWER(supplier_name) = LOWER(:name) LIMIT 1');
                $stmt->execute([':name' => $name]);
                $existingId = $stmt->fetchColumn();
            }

            if ($existingId) {
                $this->conn->prepare('
                    UPDATE suppliers
                    SET eis_supplier_id = :eis_id, eis_supplier_tin = :tin, supplier_name = :name, status = :status, eis_synced_at = NOW()
                    WHERE supplier_id = :id
                ')->execute([
                    ':eis_id' => $eisId,
                    ':tin' => $tin,
                    ':name' => $name,
                    ':status' => $status,
                    ':id' => $existingId,
                ]);
                $updated++;
            } else {
                $this->conn->prepare('
                    INSERT INTO suppliers (eis_supplier_id, eis_supplier_tin, supplier_name, status, eis_synced_at)
                    VALUES (:eis_id, :tin, :name, :status, NOW())
                ')->execute([
                    ':eis_id' => $eisId,
                    ':tin' => $tin,
                    ':name' => $name,
                    ':status' => $status,
                ]);
                $created++;
            }
        }

        return ['total' => $created + $updated, 'created' => $created, 'updated' => $updated];
    }

    public function createSupplier(array $data, int $createdBy): int {
        $name = trim($data['supplier_name'] ?? '');
        if ($name === '') throw new InvalidArgumentException('Supplier name is required.');

        $stmt = $this->conn->prepare("
            INSERT INTO suppliers (supplier_code, eis_supplier_id, supplier_name, contact_person, phone, email, address, notes, created_by)
            VALUES (:code, :eis_id, :name, :contact, :phone, :email, :address, :notes, :by)
        ");
        $stmt->execute([
            ':code'    => trim($data['supplier_code']   ?? '') ?: null,
            ':eis_id'  => !empty($data['eis_supplier_id']) ? (int)$data['eis_supplier_id'] : null,
            ':name'    => $name,
            ':contact' => trim($data['contact_person']  ?? '') ?: null,
            ':phone'   => trim($data['phone']           ?? '') ?: null,
            ':email'   => trim($data['email']           ?? '') ?: null,
            ':address' => trim($data['address']         ?? '') ?: null,
            ':notes'   => trim($data['notes']           ?? '') ?: null,
            ':by'      => $createdBy,
        ]);
        return (int)$this->conn->lastInsertId();
    }

    public function updateSupplier(int $supplierId, array $data): bool {
        $name = trim($data['supplier_name'] ?? '');
        if ($name === '') throw new InvalidArgumentException('Supplier name is required.');

        $stmt = $this->conn->prepare("
            UPDATE suppliers SET
                supplier_code = :code, eis_supplier_id = :eis_id, supplier_name = :name, contact_person = :contact,
                phone = :phone, email = :email, address = :address, notes = :notes, status = :status
            WHERE supplier_id = :id
        ");
        $stmt->execute([
            ':code'    => trim($data['supplier_code']  ?? '') ?: null,
            ':eis_id'  => !empty($data['eis_supplier_id']) ? (int)$data['eis_supplier_id'] : null,
            ':name'    => $name,
            ':contact' => trim($data['contact_person'] ?? '') ?: null,
            ':phone'   => trim($data['phone']          ?? '') ?: null,
            ':email'   => trim($data['email']          ?? '') ?: null,
            ':address' => trim($data['address']        ?? '') ?: null,
            ':notes'   => trim($data['notes']          ?? '') ?: null,
            ':status'  => in_array($data['status'] ?? 'active', ['active', 'inactive'], true) ? $data['status'] : 'active',
            ':id'      => $supplierId,
        ]);
        return $stmt->rowCount() > 0;
    }

    public function getSupplierById(int $id): ?array {
        $stmt = $this->conn->prepare("SELECT s.*, u.username AS created_by_name FROM suppliers s LEFT JOIN users u ON u.user_id = s.created_by WHERE s.supplier_id = :id");
        $stmt->execute([':id' => $id]);
        return $stmt->fetch(PDO::FETCH_ASSOC) ?: null;
    }

    public function getSuppliers(?string $status = null, ?string $search = null, int $limit = 300): array {
        $where  = [];
        $params = [];
        if ($status) {
            $where[] = 's.status = :status';
            $params[':status'] = $status;
        }
        if ($search !== null && $search !== '') {
            $where[] = '(s.supplier_name LIKE :q OR s.supplier_code LIKE :q OR s.phone LIKE :q OR s.contact_person LIKE :q)';
            $params[':q'] = '%' . $search . '%';
        }
        $sql = "SELECT s.*, u.username AS created_by_name FROM suppliers s LEFT JOIN users u ON u.user_id = s.created_by";
        if ($where) $sql .= ' WHERE ' . implode(' AND ', $where);
        $sql .= ' ORDER BY s.supplier_name ASC LIMIT ' . max(1, $limit);
        $stmt = $this->conn->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function recordInvoice(int $supplierId, array $data, int $recordedBy): int {
        if (!$this->getSupplierById($supplierId)) throw new RuntimeException('Supplier not found.');
        $amount = round((float)($data['amount'] ?? 0), 2);
        if ($amount <= 0) throw new InvalidArgumentException('Invoice amount must be greater than zero.');

        $invNum = trim($data['invoice_number'] ?? '') ?: ('INV-' . date('Ymd') . '-' . strtoupper(substr(uniqid(), -4)));
        $stmt = $this->conn->prepare("
            INSERT INTO supplier_invoices (supplier_id, invoice_number, invoice_date, due_date, description, amount, balance, recorded_by)
            VALUES (:sid, :num, :date, :due, :desc, :amount, :balance, :by)
        ");
        $stmt->execute([
            ':sid'     => $supplierId,
            ':num'     => $invNum,
            ':date'    => $data['invoice_date'] ?? date('Y-m-d'),
            ':due'     => !empty($data['due_date']) ? $data['due_date'] : null,
            ':desc'    => trim($data['description'] ?? '') ?: null,
            ':amount'  => $amount,
            ':balance' => $amount,
            ':by'      => $recordedBy,
        ]);
        $id = (int)$this->conn->lastInsertId();
        $this->recalculateSupplier($supplierId);
        return $id;
    }

    public function recordPayment(int $supplierId, array $data, int $recordedBy): int {
        $supplier = $this->getSupplierById($supplierId);
        if (!$supplier) throw new RuntimeException('Supplier not found.');
        $amount = round((float)($data['amount'] ?? 0), 2);
        if ($amount <= 0) throw new InvalidArgumentException('Payment amount must be greater than zero.');
        if ($amount > (float)$supplier['balance'] + 0.01) throw new InvalidArgumentException('Payment exceeds outstanding balance of MWK ' . number_format((float)$supplier['balance'], 2) . '.');

        $invoiceId = !empty($data['invoice_id']) ? (int)$data['invoice_id'] : null;
        $stmt = $this->conn->prepare("
            INSERT INTO supplier_payments (supplier_id, invoice_id, voucher_number, payment_date, amount, payment_method, narration, recorded_by)
            VALUES (:sid, :inv, :voucher, :date, :amount, :method, :narration, :by)
        ");
        $stmt->execute([
            ':sid'      => $supplierId,
            ':inv'      => $invoiceId,
            ':voucher'  => trim($data['voucher_number'] ?? '') ?: null,
            ':date'     => $data['payment_date'] ?? date('Y-m-d'),
            ':amount'   => $amount,
            ':method'   => normalizePaymentMethodValue($data['payment_method'] ?? 'cash'),
            ':narration'=> trim($data['narration'] ?? '') ?: null,
            ':by'       => $recordedBy,
        ]);
        $id = (int)$this->conn->lastInsertId();

        if ($invoiceId) {
            $inv = $this->conn->prepare("SELECT amount, amount_paid FROM supplier_invoices WHERE invoice_id = :id AND supplier_id = :sid");
            $inv->execute([':id' => $invoiceId, ':sid' => $supplierId]);
            $invRow = $inv->fetch(PDO::FETCH_ASSOC);
            if ($invRow) {
                $newPaid   = min((float)$invRow['amount'], round((float)$invRow['amount_paid'] + $amount, 2));
                $newBal    = max(0, round((float)$invRow['amount'] - $newPaid, 2));
                $newStatus = $newBal <= 0 ? 'paid' : ($newPaid > 0 ? 'partial' : 'unpaid');
                $this->conn->prepare("UPDATE supplier_invoices SET amount_paid = :p, balance = :b, status = :s WHERE invoice_id = :id")
                    ->execute([':p' => $newPaid, ':b' => $newBal, ':s' => $newStatus, ':id' => $invoiceId]);
            }
        }

        $this->recalculateSupplier($supplierId);
        return $id;
    }

    public function recalculateSupplier(int $supplierId): void {
        $r = $this->conn->prepare("SELECT COALESCE(SUM(amount),0) FROM supplier_invoices WHERE supplier_id = :id");
        $r->execute([':id' => $supplierId]);
        $invoiced = (float)$r->fetchColumn();

        $r = $this->conn->prepare("SELECT COALESCE(SUM(amount),0) FROM supplier_payments WHERE supplier_id = :id");
        $r->execute([':id' => $supplierId]);
        $paid = (float)$r->fetchColumn();

        $this->conn->prepare("UPDATE suppliers SET total_invoiced = :i, total_paid = :p, balance = :b WHERE supplier_id = :id")
            ->execute([':i' => $invoiced, ':p' => $paid, ':b' => max(0, round($invoiced - $paid, 2)), ':id' => $supplierId]);
    }

    public function getInvoices(int $supplierId): array {
        $stmt = $this->conn->prepare("
            SELECT si.*, u.username AS recorded_by_name
            FROM supplier_invoices si
            LEFT JOIN users u ON u.user_id = si.recorded_by
            WHERE si.supplier_id = :id
            ORDER BY si.invoice_date DESC, si.invoice_id DESC
        ");
        $stmt->execute([':id' => $supplierId]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function getPayments(int $supplierId): array {
        $stmt = $this->conn->prepare("
            SELECT sp.*, u.username AS recorded_by_name, si.invoice_number
            FROM supplier_payments sp
            LEFT JOIN users u ON u.user_id = sp.recorded_by
            LEFT JOIN supplier_invoices si ON si.invoice_id = sp.invoice_id
            WHERE sp.supplier_id = :id
            ORDER BY sp.payment_date DESC, sp.payment_id DESC
        ");
        $stmt->execute([':id' => $supplierId]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function getUnpaidInvoices(int $supplierId): array {
        $stmt = $this->conn->prepare("
            SELECT invoice_id, invoice_number, invoice_date, amount, balance
            FROM supplier_invoices
            WHERE supplier_id = :id AND status != 'paid'
            ORDER BY invoice_date ASC
        ");
        $stmt->execute([':id' => $supplierId]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function getSummary(): array {
        $row = $this->conn->query("
            SELECT COUNT(*) AS total_suppliers,
                   SUM(status = 'active') AS active_suppliers,
                   COALESCE(SUM(total_invoiced), 0) AS total_invoiced,
                   COALESCE(SUM(total_paid), 0) AS total_paid,
                   COALESCE(SUM(balance), 0) AS total_balance
            FROM suppliers
        ")->fetch(PDO::FETCH_ASSOC);
        return $row ?: ['total_suppliers' => 0, 'active_suppliers' => 0, 'total_invoiced' => 0, 'total_paid' => 0, 'total_balance' => 0];
    }
}
