<?php
namespace App\Repositories;

use App\Database\Connection;
use PDO;

/**
 * C9: durable financial cheque workflow.
 *
 * Status lives in `cheque_reviews` keyed by (deal_id, check_number) so it
 * survives the `transactions` rebuild that happens on every deal save. The
 * listing reads current cheques from `transactions` (method = check) and
 * LEFT JOINs the durable review so unreviewed cheques still appear as pending.
 */
class ChequeReviewRepository
{
    private $db;

    public function __construct(Connection $db)
    {
        $this->db = $db;
    }

    /** Cheque method codes used across the app. */
    private function chequeMethods(): array
    {
        return ['check', 'cheque', 'چک'];
    }

    /**
     * List cheques for the financial screen with deal/contact context and
     * durable review status. Filters: check_status, collection_status, search, limit.
     */
    public function listCheques(array $filters = []): array
    {
        $pdo = $this->db->getPdo();
        $methods = $this->chequeMethods();
        $params = [];

        $where = [];
        if ($this->db->hasColumn('transactions', 'is_cheque_payment')) {
            $in = implode(',', array_fill(0, count($methods), '?'));
            $where[] = "(t.is_cheque_payment = 1 OR t.method IN ($in))";
            $params = $methods;
        } else {
            $in = implode(',', array_fill(0, count($methods), '?'));
            $where[] = "t.method IN ($in)";
            $params = $methods;
        }
        if (!empty($filters['check_status'])) {
            $where[] = "COALESCE(cr.check_status, 'pending') = ?";
            $params[] = $filters['check_status'];
        }
        if (!empty($filters['collection_status'])) {
            $where[] = "COALESCE(cr.collection_status, 'pending') = ?";
            $params[] = $filters['collection_status'];
        }
        if (!empty($filters['search'])) {
            $where[] = "(t.check_number LIKE ? OR p.first_name LIKE ? OR p.last_name LIKE ? OR d.title LIKE ?)";
            $s = '%' . $filters['search'] . '%';
            array_push($params, $s, $s, $s, $s);
        }
        if (!empty($filters['date_from'])) {
            $where[] = "(t.check_due_date >= ? OR (t.check_due_date IS NULL AND t.check_date >= ?))";
            array_push($params, $filters['date_from'], $filters['date_from']);
        }
        if (!empty($filters['date_to'])) {
            $where[] = "(t.check_due_date <= ? OR (t.check_due_date IS NULL AND t.check_date <= ?))";
            array_push($params, $filters['date_to'], $filters['date_to']);
        }
        $ownerExpr = "COALESCE(p.owner_didar_id, d.owner_didar_id)";
        $ownerDidarIds = array_values(array_filter(array_map(static function ($value) {
            return trim((string)$value);
        }, (array)($filters['owner_didar_ids'] ?? [])), static function ($value) {
            return $value !== '';
        }));
        if (!empty($filters['owner_didar_ids_csv']) && empty($ownerDidarIds)) {
            $ownerDidarIds = array_values(array_filter(array_map('trim', explode(',', (string)$filters['owner_didar_ids_csv']))));
        }
        if (!empty($ownerDidarIds)) {
            $where[] = $ownerExpr . " IN (" . implode(',', array_fill(0, count($ownerDidarIds), '?')) . ")";
            foreach ($ownerDidarIds as $ownerDidarId) {
                $params[] = $ownerDidarId;
            }
        } elseif (!empty($filters['owner_didar_id'])) {
            $where[] = $ownerExpr . " = ?";
            $params[] = $filters['owner_didar_id'];
        }
        $limit = isset($filters['limit']) ? max(1, min(200, (int)$filters['limit'])) : 50;
        $offset = isset($filters['offset']) ? max(0, (int)$filters['offset']) : 0;

        $sql = "
            SELECT
                t.id AS transaction_id,
                t.deal_id,
                d.didar_deal_id,
                d.title AS deal_title,
                d.invoice_code,
                t.check_number,
                t.amount,
                t.check_bank_name,
                t.check_owner_name,
                t.check_date,
                t.check_due_date,
                t.sayadi_date,
                TRIM(CONCAT_WS(' ', p.first_name, p.last_name)) AS contact_name,
                p.customer_code,
                p.mobile_phone,
                COALESCE(cr.check_status, 'pending') AS check_status,
                cr.check_status_note,
                cr.reviewed_by,
                cr.reviewed_at,
                COALESCE(cr.collection_status, 'pending') AS collection_status,
                cr.collection_date,
                cr.collection_at
            FROM transactions t
            JOIN deals d ON d.id = t.deal_id
            LEFT JOIN persons p ON p.didar_contact_id = d.contact_didar_id
            LEFT JOIN cheque_reviews cr ON cr.transaction_id = t.id
            WHERE " . implode(' AND ', $where) . "
            ORDER BY (COALESCE(cr.check_status,'pending') = 'pending') DESC,
                     COALESCE(t.check_due_date, t.check_date) ASC,
                     t.id DESC
            LIMIT {$limit} OFFSET {$offset}
        ";
        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
    }

    public function countCheques(array $filters = []): int
    {
        $pdo = $this->db->getPdo();
        $methods = $this->chequeMethods();
        $params = [];
        $where = [];
        if ($this->db->hasColumn('transactions', 'is_cheque_payment')) {
            $in = implode(',', array_fill(0, count($methods), '?'));
            $where[] = "(t.is_cheque_payment = 1 OR t.method IN ($in))";
            $params = $methods;
        } else {
            $in = implode(',', array_fill(0, count($methods), '?'));
            $where[] = "t.method IN ($in)";
            $params = $methods;
        }
        if (!empty($filters['check_status'])) {
            $where[] = "COALESCE(cr.check_status, 'pending') = ?";
            $params[] = $filters['check_status'];
        }
        if (!empty($filters['collection_status'])) {
            $where[] = "COALESCE(cr.collection_status, 'pending') = ?";
            $params[] = $filters['collection_status'];
        }
        if (!empty($filters['search'])) {
            $where[] = "(t.check_number LIKE ? OR p.first_name LIKE ? OR p.last_name LIKE ? OR d.title LIKE ?)";
            $s = '%' . $filters['search'] . '%';
            array_push($params, $s, $s, $s, $s);
        }
        if (!empty($filters['date_from'])) {
            $where[] = "(t.check_due_date >= ? OR (t.check_due_date IS NULL AND t.check_date >= ?))";
            array_push($params, $filters['date_from'], $filters['date_from']);
        }
        if (!empty($filters['date_to'])) {
            $where[] = "(t.check_due_date <= ? OR (t.check_due_date IS NULL AND t.check_date <= ?))";
            array_push($params, $filters['date_to'], $filters['date_to']);
        }
        $ownerExpr = "COALESCE(p.owner_didar_id, d.owner_didar_id)";
        $ownerDidarIds = array_values(array_filter(array_map(static function ($value) {
            return trim((string)$value);
        }, (array)($filters['owner_didar_ids'] ?? [])), static function ($value) {
            return $value !== '';
        }));
        if (!empty($filters['owner_didar_ids_csv']) && empty($ownerDidarIds)) {
            $ownerDidarIds = array_values(array_filter(array_map('trim', explode(',', (string)$filters['owner_didar_ids_csv']))));
        }
        if (!empty($ownerDidarIds)) {
            $where[] = $ownerExpr . " IN (" . implode(',', array_fill(0, count($ownerDidarIds), '?')) . ")";
            foreach ($ownerDidarIds as $ownerDidarId) {
                $params[] = $ownerDidarId;
            }
        } elseif (!empty($filters['owner_didar_id'])) {
            $where[] = $ownerExpr . " = ?";
            $params[] = $filters['owner_didar_id'];
        }

        $stmt = $pdo->prepare("
            SELECT COUNT(t.id)
            FROM transactions t
            JOIN deals d ON d.id = t.deal_id
            LEFT JOIN persons p ON p.didar_contact_id = d.contact_didar_id
            LEFT JOIN cheque_reviews cr ON cr.transaction_id = t.id
            WHERE " . implode(' AND ', $where) . "
        ");
        $stmt->execute($params);
        return (int)$stmt->fetchColumn();
    }

    /**
     * Fetch a single cheque transaction row by id (used to resolve deal_id /
     * check_number / amount from the stable transaction identifier).
     */
    public function getChequeTransaction(int $transactionId): ?array
    {
        $methods = $this->chequeMethods();
        $in = implode(',', array_fill(0, count($methods), '?'));
        $stmt = $this->db->getPdo()->prepare(
            "SELECT id, deal_id, check_number, amount FROM transactions WHERE id = ? AND method IN ($in) LIMIT 1"
        );
        $stmt->execute(array_merge([$transactionId], $methods));
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return $row ?: null;
    }

    public function getReview(int $transactionId): ?array
    {
        $stmt = $this->db->getPdo()->prepare("SELECT * FROM cheque_reviews WHERE transaction_id = ? LIMIT 1");
        $stmt->execute([$transactionId]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return $row ?: null;
    }

    /** Set approval status (approved/rejected/pending) + note, keyed by transaction_id. */
    public function upsertStatus(int $transactionId, int $dealId, ?string $checkNumber, string $status, string $note, string $by, float $amount = 0): void
    {
        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("
            INSERT INTO cheque_reviews (transaction_id, deal_id, check_number, amount, check_status, check_status_note, reviewed_by, reviewed_at)
            VALUES (?, ?, ?, ?, ?, ?, ?, NOW())
            ON DUPLICATE KEY UPDATE
                deal_id = VALUES(deal_id),
                check_number = VALUES(check_number),
                amount = VALUES(amount),
                check_status = VALUES(check_status),
                check_status_note = VALUES(check_status_note),
                reviewed_by = VALUES(reviewed_by),
                reviewed_at = NOW()
        ");
        $stmt->execute([$transactionId, $dealId, ($checkNumber !== '' ? $checkNumber : null), $amount, $status, $note, $by]);
        $this->syncToTransactions($transactionId);
    }

    /** Set collection status (collected/returned/blataklif/pending) + date, keyed by transaction_id. */
    public function upsertCollection(int $transactionId, int $dealId, ?string $checkNumber, string $status, ?string $date, string $by, float $amount = 0): void
    {
        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("
            INSERT INTO cheque_reviews (transaction_id, deal_id, check_number, amount, collection_status, collection_date, collection_by, collection_at)
            VALUES (?, ?, ?, ?, ?, ?, ?, NOW())
            ON DUPLICATE KEY UPDATE
                deal_id = VALUES(deal_id),
                check_number = VALUES(check_number),
                amount = VALUES(amount),
                collection_status = VALUES(collection_status),
                collection_date = VALUES(collection_date),
                collection_by = VALUES(collection_by),
                collection_at = NOW()
        ");
        $stmt->execute([$transactionId, $dealId, ($checkNumber !== '' ? $checkNumber : null), $amount, $status, ($date !== '' ? $date : null), $by]);
        $this->syncToTransactions($transactionId);
    }

    /**
     * Project the durable review status back onto the (volatile) transactions
     * rows so reporting/exports reflect financial decisions. Safe no-op if the
     * transactions columns are absent.
     */
    public function syncToTransactions(int $transactionId): void
    {
        if (!$this->db->hasColumn('transactions', 'check_status')) {
            return;
        }
        $pdo = $this->db->getPdo();
        $sql = "
            UPDATE transactions t
            JOIN cheque_reviews cr ON cr.transaction_id = t.id
            SET t.check_status = cr.check_status,
                t.check_status_note = cr.check_status_note,
                t.check_status_at = cr.reviewed_at,
                t.check_status_by = cr.reviewed_by,
                t.collection_status = cr.collection_status,
                t.collection_date = cr.collection_date
            WHERE t.id = ?";
        try {
            $pdo->prepare($sql)->execute([$transactionId]);
        } catch (\PDOException $e) {
            // best-effort projection; durable source remains cheque_reviews
        }
    }
}
