<?php

declare(strict_types=1);

namespace App\Repositories;

use PDO;

final class PartyRepository
{
    public function __construct(private PDO $pdo)
    {
    }

    public function findById(int $id): array|false
    {
        $st = $this->pdo->prepare('SELECT * FROM parties WHERE id=? LIMIT 1');
        $st->execute([$id]);
        return $st->fetch();
    }

    public function create(array $data): int
    {
        $st = $this->pdo->prepare("INSERT INTO parties (code, name, phone, phone2, email, address, tax_no, notes, opening_balance, credit_limit, is_customer, is_supplier, is_active, created_by)
            VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
        $st->execute([
            $data['code'],
            $data['name'],
            $data['phone'],
            $data['phone2'],
            $data['email'],
            $data['address'],
            $data['tax_no'],
            $data['notes'],
            $data['opening_balance'],
            $data['credit_limit'],
            $data['is_customer'],
            $data['is_supplier'],
            $data['is_active'],
            $data['created_by'],
        ]);

        return (int) $this->pdo->lastInsertId();
    }

    public function update(int $id, array $data): void
    {
        $st = $this->pdo->prepare("UPDATE parties SET code=?, name=?, phone=?, phone2=?, email=?, address=?, tax_no=?, notes=?, opening_balance=?, credit_limit=?, is_customer=?, is_supplier=?, is_active=? WHERE id=?");
        $st->execute([
            $data['code'],
            $data['name'],
            $data['phone'],
            $data['phone2'],
            $data['email'],
            $data['address'],
            $data['tax_no'],
            $data['notes'],
            $data['opening_balance'],
            $data['credit_limit'],
            $data['is_customer'],
            $data['is_supplier'],
            $data['is_active'],
            $id,
        ]);
    }

    public function countFiltered(string $type, string $q): int
    {
        [$whereSql, $params] = $this->buildFilter($type, $q);
        $st = $this->pdo->prepare("SELECT COUNT(*) FROM parties {$whereSql}");
        $st->execute($params);
        return (int) $st->fetchColumn();
    }

    public function paginateFiltered(string $type, string $q, int $limit, int $offset): array
    {
        [$whereSql, $params] = $this->buildFilter($type, $q);
        $sql = "SELECT id, COALESCE(code, CONCAT('P',LPAD(id,5,'0'))) code, name, phone, phone2, email, address, tax_no, notes, is_active, opening_balance, credit_limit, is_customer, is_supplier
                FROM parties
                {$whereSql}
                ORDER BY id DESC
                LIMIT {$limit} OFFSET {$offset}";
        $st = $this->pdo->prepare($sql);
        $st->execute($params);
        return $st->fetchAll() ?: [];
    }


    public function delete(int $id): void
    {
        $st = $this->pdo->prepare('DELETE FROM parties WHERE id=?');
        $st->execute([$id]);
    }

    public function usageSummary(int $id): array
    {
        $checks = [
            'sales_invoices' => ['table' => 'sales_invoices', 'column' => 'party_id'],
            'purchase_invoices' => ['table' => 'purchase_invoices', 'column' => 'party_id'],
            'sales_returns' => ['table' => 'sales_returns', 'column' => 'party_id'],
            'purchase_returns' => ['table' => 'purchase_returns', 'column' => 'party_id'],
            'party_ledger' => ['table' => 'party_ledger', 'column' => 'party_id'],
            'customer_receipts' => ['table' => 'customer_receipts', 'column' => 'party_id'],
            'supplier_payments' => ['table' => 'supplier_payments', 'column' => 'party_id'],
            'checks_drawer' => ['table' => 'checks', 'column' => 'drawer_party_id'],
            'checks_holder' => ['table' => 'checks', 'column' => 'current_holder_party_id'],
            'checks_endorsed' => ['table' => 'checks', 'column' => 'last_endorsed_party_id'],
            'check_movements' => ['table' => 'check_movements', 'column' => 'party_id'],
            'quotations' => ['table' => 'quotations', 'column' => 'party_id'],
            'items_default_supplier' => ['table' => 'items', 'column' => 'default_supplier_id'],
        ];

        $summary = [];
        foreach ($checks as $key => $meta) {
            if (!table_exists($this->pdo, $meta['table']) || !table_has_column($this->pdo, $meta['table'], $meta['column'])) {
                continue;
            }
            $sql = sprintf('SELECT COUNT(*) FROM %s WHERE %s = ?', $meta['table'], $meta['column']);
            $st = $this->pdo->prepare($sql);
            $st->execute([$id]);
            $count = (int)($st->fetchColumn() ?: 0);
            if ($count > 0) {
                $summary[$key] = $count;
            }
        }

        return $summary;
    }

    private function buildFilter(string $type, string $q): array
    {
        $where = [];
        $params = [];

        if ($type === 'customer') {
            $where[] = 'is_customer=1';
        } elseif ($type === 'supplier') {
            $where[] = 'is_supplier=1';
        } elseif ($type === 'both') {
            $where[] = 'is_customer=1 AND is_supplier=1';
        }

        if ($q !== '') {
            $where[] = '(name LIKE ? OR phone LIKE ? OR phone2 LIKE ? OR code LIKE ?)';
            $like = '%' . $q . '%';
            $params = [$like, $like, $like, $like];
        }

        $whereSql = $where ? ('WHERE ' . implode(' AND ', $where)) : '';
        return [$whereSql, $params];
    }
}
