<?php
namespace App\Repositories;

use App\Database\Connection;
use App\Models\FinanceApproval;
use PDO;

class FinanceApprovalRepository
{
    private $db;

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

    public function save(FinanceApproval $approval): int
    {
        $pdo = $this->db->getPdo();
        if (!empty($approval->id)) {
            $stmt = $pdo->prepare("
                UPDATE finance_approvals
                SET status = ?, approved_amount = ?, approver_user_id = ?, approved_at = ?, rejection_reason = ?
                WHERE id = ?
            ");
            $stmt->execute([
                strtoupper(trim((string)$approval->status)) ?: 'PENDING_FINANCE_APPROVAL',
                $approval->approved_amount !== null ? (float)$approval->approved_amount : null,
                $approval->approver_user_id ? (int)$approval->approver_user_id : null,
                $approval->approved_at ?: null,
                $approval->rejection_reason ?: null,
                (int)$approval->id,
            ]);
            return (int)$approval->id;
        }

        $stmt = $pdo->prepare("
            INSERT INTO finance_approvals
                (deal_payment_id, deal_didar_id, status, approved_amount, approver_user_id, approved_at, rejection_reason)
            VALUES (?, ?, ?, ?, ?, ?, ?)
        ");
        $stmt->execute([
            (int)$approval->deal_payment_id,
            $approval->deal_didar_id,
            strtoupper(trim((string)$approval->status)) ?: 'PENDING_FINANCE_APPROVAL',
            $approval->approved_amount !== null ? (float)$approval->approved_amount : null,
            $approval->approver_user_id ? (int)$approval->approver_user_id : null,
            $approval->approved_at ?: null,
            $approval->rejection_reason ?: null,
        ]);
        return (int)$pdo->lastInsertId();
    }

    public function getChequeFinanceInbox(string $fromDate = '', string $toDate = ''): array
    {
        $sql = "
            SELECT dp.deal_didar_id,
                   SUM(dp.payment_amount) AS total_cheque_value,
                   COALESCE(d.final_price, d.payable_amount, 0) AS total_deal_value,
                   (SUM(dp.payment_amount) / NULLIF(COALESCE(d.final_price, d.payable_amount, 0), 0)) * 100 AS cheque_percentage_of_deal
            FROM deal_payments dp
            INNER JOIN deals d ON d.didar_deal_id = dp.deal_didar_id
            LEFT JOIN finance_approvals fa ON fa.deal_payment_id = dp.id
            WHERE dp.payment_type = 'CHEQUE'
              AND COALESCE(fa.status, 'PENDING_FINANCE_APPROVAL') IN ('PENDING_FINANCE_APPROVAL', 'APPROVED')
        ";
        $params = [];
        if ($fromDate !== '') {
            $sql .= " AND DATE(COALESCE(dp.receipt_date, dp.created_at)) >= ?";
            $params[] = $fromDate;
        }
        if ($toDate !== '') {
            $sql .= " AND DATE(COALESCE(dp.receipt_date, dp.created_at)) <= ?";
            $params[] = $toDate;
        }
        $sql .= " GROUP BY dp.deal_didar_id";
        $stmt = $this->db->getPdo()->prepare($sql);
        $stmt->execute($params);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];

        foreach ($rows as &$row) {
            $detailStmt = $this->db->getPdo()->prepare("
                SELECT dp.id, dp.payment_amount, dp.receipt_date, dp.cheque_due_date, dp.cheque_cashed_date,
                       COALESCE(fa.status, 'PENDING_FINANCE_APPROVAL') AS finance_status
                FROM deal_payments dp
                LEFT JOIN finance_approvals fa ON fa.deal_payment_id = dp.id
                WHERE dp.deal_didar_id = ? AND dp.payment_type = 'CHEQUE'
                ORDER BY dp.receipt_date ASC, dp.id ASC
            ");
            $detailStmt->execute([(string)$row['deal_didar_id']]);
            $row['cheques'] = $detailStmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
        }
        unset($row);
        return $rows;
    }
}
