<?php
if (session_status() === PHP_SESSION_NONE) { session_start(); }
require_once __DIR__ . '/includes/db.php';
require_once __DIR__ . '/includes/functions.php';

header('Content-Type: application/json');

if (!isset($_SESSION['user_id'])) {
    echo json_encode(['success' => false, 'error' => 'Unauthorized']);
    exit;
}

$clientId = isset($_GET['client_id']) ? (int)$_GET['client_id'] : 0;
if ($clientId <= 0) {
    echo json_encode(['success' => false, 'error' => 'Invalid client']);
    exit;
}

$companyId = function_exists('getCurrentCompanyId') ? (int)getCurrentCompanyId() : (int)($_SESSION['company_id'] ?? 0);

try {
    $hasDeals = $pdo->query("SHOW TABLES LIKE 'deals_submit'")->rowCount() > 0;
    if (!$hasDeals) {
        $hasDeals = false;
    }

    $extractDealDetails = static function (array $deal): array {
        $meta = [];
        if (!empty($deal['meta_json'])) {
            $decoded = json_decode((string)$deal['meta_json'], true);
            if (is_array($decoded)) {
                $meta = $decoded;
            }
        }
        $projectLabel = trim((string)($deal['project_desc'] ?? ''));
        if ($projectLabel === '') {
            $projectLabel = trim((string)($deal['project_name'] ?? ''));
        }
        if ($projectLabel === '' && isset($meta['project_desc'])) {
            $projectLabel = trim((string)$meta['project_desc']);
        }
        if ($projectLabel === '' && isset($meta['project_name'])) {
            $projectLabel = trim((string)$meta['project_name']);
        }
        $sqm = trim((string)($deal['sqm'] ?? ''));
        $estateName = trim((string)($deal['estate_name'] ?? ''));
        if ($estateName === '' && preg_match('/^\s*([\d.,]+\s*(?:SQM|M2|SQ\.?\s*M|SQ\s*METERS?))\s+OF\s+(.+)\s*$/i', $projectLabel, $matches)) {
            if ($sqm === '') {
                $sqm = trim($matches[1]);
            }
            $estateName = trim($matches[2]);
        }
        if ($estateName === '') {
            $estateName = $projectLabel;
        }
        return [
            'sqm' => $sqm,
            'estate_name' => $estateName,
            'project_label' => $projectLabel,
        ];
    };
    $computeTotalPaid = static function (int $clientId, int $dealId = 0, int $companyId = 0): float {
        $totalPaid = 0.0;
        try {
            if ($GLOBALS['pdo']->query("SHOW TABLES LIKE 'payments'")->rowCount() <= 0) return 0.0;
            if (!(function_exists('tableHasColumn') && tableHasColumn('payments', 'status'))) return 0.0;
            $finalStatuses = function_exists('kpiPaymentFinalizedStatuses') ? (array)kpiPaymentFinalizedStatuses() : ['verified','approved','paid','completed','success'];
            $finalSetSql = function_exists('kpiSqlList') ? kpiSqlList($finalStatuses) : "('verified','approved','paid','completed','success')";

            $hasPayType = function_exists('tableHasColumn') && tableHasColumn('payments','payment_type');
            $excludeChargeClause = '';
            if ($hasPayType) {
                $excludeChargeClause = " AND (payment_type IS NULL OR TRIM(payment_type) = '' OR LOWER(TRIM(payment_type)) NOT IN ('infrastructure','infrastructure_fee','service_charge','service','excavation','excavation_fee','vat','approval_fee','application_form_fee','supervision','construction_supervision'))";
            }

            $companyClause = '';
            $companyParams = [];
            if ($companyId > 0 && function_exists('tableHasColumn') && tableHasColumn('payments','company_id')) {
                $companyClause = " AND (company_id = ? OR company_id IS NULL)";
                $companyParams[] = $companyId;
            }

            $sumExpr = "COALESCE(SUM(CAST(REPLACE(amount, ',', '') AS DECIMAL(18,2))), 0)";
            $dealPaid = 0.0;
            if ($dealId > 0 && function_exists('tableHasColumn') && tableHasColumn('payments', 'deal_id')) {
                $sql = "SELECT {$sumExpr} FROM payments WHERE deal_id = ? AND LOWER(TRIM(status)) IN {$finalSetSql}{$excludeChargeClause}{$companyClause}";
                $paidStmt = $GLOBALS['pdo']->prepare($sql);
                $paidStmt->execute(array_merge([$dealId], $companyParams));
                $dealPaid = (float)($paidStmt->fetchColumn() ?: 0.0);
            }

            $allocPaid = 0.0;
            if ($dealId > 0
                && $GLOBALS['pdo']->query("SHOW TABLES LIKE 'allocations'")->rowCount() > 0
                && function_exists('tableHasColumn')
                && tableHasColumn('allocations','deal_id')
                && tableHasColumn('allocations','user_id')
                && tableHasColumn('payments','allocation_id')
            ) {
                $allocId = 0;
                try {
                    $sa = $GLOBALS['pdo']->prepare("SELECT id FROM allocations WHERE user_id = ? AND deal_id = ? ORDER BY id DESC LIMIT 1");
                    $sa->execute([$clientId, $dealId]);
                    $allocId = (int)($sa->fetchColumn() ?: 0);
                } catch (Throwable $e) { $allocId = 0; }
                if ($allocId > 0) {
                    $sql = "SELECT {$sumExpr} FROM payments WHERE allocation_id = ? AND LOWER(TRIM(status)) IN {$finalSetSql}{$excludeChargeClause}{$companyClause}";
                    $paidStmt = $GLOBALS['pdo']->prepare($sql);
                    $paidStmt->execute(array_merge([$allocId], $companyParams));
                    $allocPaid = (float)($paidStmt->fetchColumn() ?: 0.0);
                }
            }

            $totalPaid = max($dealPaid, $allocPaid);
            if ($totalPaid <= 0) {
                $parts = [];
                $paidParams = [];
                if (function_exists('tableHasColumn') && tableHasColumn('payments', 'user_id')) { $parts[] = 'user_id = ?'; $paidParams[] = $clientId; }
                if (function_exists('tableHasColumn') && tableHasColumn('payments', 'client_id')) { $parts[] = 'client_id = ?'; $paidParams[] = $clientId; }
                if ($parts) {
                    $sql = "SELECT {$sumExpr} FROM payments WHERE (" . implode(' OR ', $parts) . ") AND LOWER(TRIM(status)) IN {$finalSetSql}{$excludeChargeClause}{$companyClause}";
                    $paidStmt = $GLOBALS['pdo']->prepare($sql);
                    $paidStmt->execute(array_merge($paidParams, $companyParams));
                    $totalPaid = (float)$paidStmt->fetchColumn();
                }
            }
        } catch (Throwable $e) { $totalPaid = 0.0; }
        $totalPaid = is_numeric($totalPaid) ? round((float)$totalPaid, 2) : 0.0;
        return $totalPaid;
    };
    $parseMoney = static function ($raw): float {
        if ($raw === null) return 0.0;
        if (is_int($raw) || is_float($raw)) return (float)$raw;
        $s = trim((string)$raw);
        if ($s === '') return 0.0;
        $s = preg_replace('/[^\d.\-]/', '', $s);
        if ($s === '' || $s === '-' || $s === '.' || $s === '-.') return 0.0;
        return (float)$s;
    };
    $resolvePropertyIdFromDeal = static function (array $deal): int {
        $propertyId = (int)($deal['property_id'] ?? 0);
        if ($propertyId <= 0 && !empty($deal['meta_json'])) {
            $mj = json_decode((string)$deal['meta_json'], true);
            if (is_array($mj)) {
                $propertyId = (int)($mj['property_id'] ?? ($mj['propertyId'] ?? ($mj['property'] ?? 0)));
            }
        }
        if ($propertyId <= 0 && !empty($deal['project_id'])) {
            $projId = (int)$deal['project_id'];
            try {
                $hasProjects = $projId > 0 && $GLOBALS['pdo']->query("SHOW TABLES LIKE 'projects'")->rowCount() > 0;
                if ($hasProjects && function_exists('tableHasColumn') && tableHasColumn('projects','ref_table') && tableHasColumn('projects','ref_id')) {
                    $sp = $GLOBALS['pdo']->prepare("SELECT ref_id FROM projects WHERE id = ? AND ref_table = 'properties' LIMIT 1");
                    $sp->execute([$projId]);
                    $propertyId = (int)($sp->fetchColumn() ?: 0);
                }
            } catch (Throwable $e) {}
        }
        return $propertyId;
    };
    $resolvePropertyPrice = static function (int $propertyId) use ($parseMoney): float {
        if ($propertyId <= 0) return 0.0;
        try {
            $hasProps = $GLOBALS['pdo']->query("SHOW TABLES LIKE 'properties'")->rowCount() > 0;
            if (!$hasProps) return 0.0;
            $col = function_exists('tableHasColumn') && tableHasColumn('properties','price') ? 'price' : (function_exists('tableHasColumn') && tableHasColumn('properties','amount') ? 'amount' : null);
            if (!$col) return 0.0;
            $sp = $GLOBALS['pdo']->prepare("SELECT {$col} FROM properties WHERE id = ? LIMIT 1");
            $sp->execute([$propertyId]);
            $raw = $sp->fetchColumn();
            return $parseMoney($raw);
        } catch (Throwable $e) {
            return 0.0;
        }
    };
    $extractAmountOffered = static function (array $deal) use ($parseMoney): float {
        $meta = [];
        if (!empty($deal['meta_json'])) {
            $decoded = json_decode((string)$deal['meta_json'], true);
            if (is_array($decoded)) {
                $meta = $decoded;
            }
        }
        $rawAmount = $deal['amount_offered'] ?? ($deal['offer_amount'] ?? ($meta['amount_offered'] ?? ($meta['offer_amount'] ?? 0)));
        return $parseMoney($rawAmount);
    };

    $dealLinkConditions = [];
    $params = [];
    if (function_exists('tableHasColumn') && tableHasColumn('deals_submit', 'user_id')) {
        $dealLinkConditions[] = 'user_id = ?';
        $params[] = $clientId;
    }
    if (function_exists('tableHasColumn') && tableHasColumn('deals_submit', 'client_id')) {
        $dealLinkConditions[] = 'client_id = ?';
        $params[] = $clientId;
    }
    if (!$dealLinkConditions) {
        $totalPaidFallback = $computeTotalPaid($clientId, 0, $companyId);
        echo json_encode([
            'success' => true,
            'deal_found' => false,
            'message' => 'No deal found',
            'estate_name' => '',
            'estate_id' => 0,
            'sqm' => '',
            'amount' => 0,
            'amount_offered' => 0,
            'deal_id' => 0,
            'total_paid' => $totalPaidFallback,
            'required' => 0,
            'eligible' => false,
            'full_paid' => false,
            'house_type' => ''
        ]);
        exit;
    }

    $sql = "SELECT * FROM deals_submit WHERE (" . implode(' OR ', $dealLinkConditions) . ")";
    if ($companyId > 0 && function_exists('tableHasColumn') && tableHasColumn('deals_submit', 'company_id')) {
        $sql .= " AND (company_id = ? OR company_id IS NULL)";
        $params[] = $companyId;
    }
    $sql .= " ORDER BY id DESC LIMIT 20";

    $deals = [];
    if ($hasDeals) {
        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        $deals = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
    }

    $deal = null;
    $dealDetails = null;
    $estateId = 0;
    $estateName = '';
    $amountOffered = 0.0;
    $amountSource = 'deal';
    if ($deals) {
    foreach ($deals as $candidateDeal) {
        $candidateDetails = $extractDealDetails($candidateDeal);
        if (isset($candidateDeal['plot_size']) && !isset($candidateDeal['sqm']) && $candidateDetails['sqm'] === '') {
            $candidateDetails['sqm'] = trim((string)$candidateDeal['plot_size']);
        }

        $candidateEstateId = isset($candidateDeal['estate_id']) ? (int)$candidateDeal['estate_id'] : 0;
        $candidateEstateName = $candidateDetails['estate_name'];
        if ($candidateEstateId > 0 && function_exists('tableHasColumn') && tableHasColumn('estates', 'name')) {
            $estateStmt = $pdo->prepare("SELECT name FROM estates WHERE id = ? LIMIT 1");
            $estateStmt->execute([$candidateEstateId]);
            $candidateEstateName = (string)($estateStmt->fetchColumn() ?: $candidateEstateName);
        } elseif ($candidateEstateName !== '' && function_exists('tableHasColumn') && tableHasColumn('estates', 'name')) {
            $estateIdStmt = $pdo->prepare("SELECT id FROM estates WHERE name = ? LIMIT 1");
            $estateIdStmt->execute([$candidateEstateName]);
            $candidateEstateId = (int)($estateIdStmt->fetchColumn() ?: 0);
        }

        $candidateAmountOffered = round($extractAmountOffered($candidateDeal), 2);
        $candidateSource = 'deal';
        if ($candidateAmountOffered <= 0) {
            $pid = $resolvePropertyIdFromDeal($candidateDeal);
            $candidateAmountOffered = round($resolvePropertyPrice($pid), 2);
            $candidateSource = $candidateAmountOffered > 0 ? 'property' : 'deal';
        }
        $hasValidDeal = $candidateAmountOffered > 0 && ($candidateEstateId > 0 || trim((string)$candidateEstateName) !== '');
        if ($hasValidDeal) {
            $deal = $candidateDeal;
            $dealDetails = $candidateDetails;
            $estateId = $candidateEstateId;
            $estateName = $candidateEstateName;
            $amountOffered = $candidateAmountOffered;
            $amountSource = $candidateSource;
            break;
        }
    }
    }

    if (!$deal || !$dealDetails) {
        $totalPaidFallback = $computeTotalPaid($clientId, 0, $companyId);

        $fallbackEstateName = '';
        $fallbackEstateId = 0;
        $fallbackSqm = '';
        $fallbackAmountOffered = 0.0;
        $fallbackDealId = 0;
        $fallbackHouseType = '';
        $fallbackTotalPaid = $totalPaidFallback;

        try {
            $hasAllocTbl = $pdo->query("SHOW TABLES LIKE 'allocations'")->rowCount() > 0;
            if ($hasAllocTbl && function_exists('tableHasColumn')) {
                $w = [];
                $pp = [];
                if (tableHasColumn('allocations','user_id')) { $w[] = 'user_id = ?'; $pp[] = $clientId; }
                if (tableHasColumn('allocations','client_id')) { $w[] = 'client_id = ?'; $pp[] = $clientId; }
                if (!empty($w)) {
                    $sqlA = "SELECT * FROM allocations WHERE (" . implode(' OR ', $w) . ")";
                    if ($companyId > 0 && tableHasColumn('allocations','company_id')) { $sqlA .= " AND (company_id = ? OR company_id IS NULL)"; $pp[] = $companyId; }
                    $sqlA .= " ORDER BY id DESC LIMIT 10";
                    $stA = $pdo->prepare($sqlA);
                    $stA->execute($pp);
                    $allocs = $stA->fetchAll(PDO::FETCH_ASSOC) ?: [];
                    foreach ($allocs as $a) {
                        $allocId = (int)($a['id'] ?? 0);
                        if ($allocId <= 0) { continue; }

                        $fallbackDealId = (int)($a['deal_id'] ?? 0);
                        $fallbackEstateId = (int)($a['estate_id'] ?? 0);
                        $fallbackEstateName = trim((string)($a['estate_name'] ?? ($a['property_title'] ?? '')));
                        $fallbackSqm = trim((string)($a['sqm'] ?? ($a['plot_size'] ?? ($a['space_size'] ?? ''))));
                        $fallbackHouseType = trim((string)($a['house_type'] ?? ''));

                        $sumDue = 0.0;
                        if ($pdo->query("SHOW TABLES LIKE 'installments'")->rowCount() > 0 && tableHasColumn('installments','allocation_id')) {
                            $amtCol = tableHasColumn('installments','amount_due') ? 'amount_due' : (tableHasColumn('installments','amount') ? 'amount' : null);
                            if ($amtCol) {
                                $si = $pdo->prepare("SELECT COALESCE(SUM(CAST(REPLACE($amtCol, ',', '') AS DECIMAL(18,2))),0) FROM installments WHERE allocation_id = ?");
                                $si->execute([$allocId]);
                                $sumDue = (float)($si->fetchColumn() ?: 0.0);
                            }
                        }
                        $fallbackAmountOffered = $sumDue;
                        if ($fallbackAmountOffered <= 0) {
                            $fallbackAmountOffered = $parseMoney($a['property_price'] ?? 0);
                        }
                        if ($fallbackAmountOffered <= 0) {
                            $pid = (int)($a['property_id'] ?? 0);
                            $fallbackAmountOffered = round($resolvePropertyPrice($pid), 2);
                        }

                        if ($fallbackAmountOffered > 0) {
                            $finalStatuses = function_exists('kpiPaymentFinalizedStatuses') ? (array)kpiPaymentFinalizedStatuses() : ['verified','approved','paid','completed','success'];
                            $finalSetSql = function_exists('kpiSqlList') ? kpiSqlList($finalStatuses) : "('verified','approved','paid','completed','success')";
                            $hasPayType = function_exists('tableHasColumn') && tableHasColumn('payments','payment_type');
                            $excludeChargeClause = '';
                            if ($hasPayType) {
                                $excludeChargeClause = " AND (payment_type IS NULL OR TRIM(payment_type) = '' OR LOWER(TRIM(payment_type)) NOT IN ('infrastructure','infrastructure_fee','service_charge','service','excavation','excavation_fee','vat','approval_fee','application_form_fee','supervision','construction_supervision'))";
                            }
                            $companyClause = '';
                            $companyParams = [];
                            if ($companyId > 0 && function_exists('tableHasColumn') && tableHasColumn('payments','company_id')) {
                                $companyClause = " AND (company_id = ? OR company_id IS NULL)";
                                $companyParams[] = $companyId;
                            }
                            if ($pdo->query("SHOW TABLES LIKE 'payments'")->rowCount() > 0 && tableHasColumn('payments','allocation_id')) {
                                $sumExpr = "COALESCE(SUM(CAST(REPLACE(amount, ',', '') AS DECIMAL(18,2))), 0)";
                                $sp = $pdo->prepare("SELECT {$sumExpr} FROM payments WHERE allocation_id = ? AND LOWER(TRIM(status)) IN {$finalSetSql}{$excludeChargeClause}{$companyClause}");
                                $sp->execute(array_merge([$allocId], $companyParams));
                                $fallbackTotalPaid = (float)($sp->fetchColumn() ?: $fallbackTotalPaid);
                            }
                            break;
                        }
                    }
                }
            }
        } catch (Throwable $e) {}

        if ($fallbackHouseType === '') {
            try {
                $stf = $pdo->prepare("SELECT form_data FROM client_forms WHERE client_id = ? ORDER BY updated_at DESC, created_at DESC LIMIT 1");
                $stf->execute([$clientId]);
                $frow = $stf->fetch(PDO::FETCH_ASSOC);
                if ($frow && !empty($frow['form_data'])) {
                    $fdata = json_decode($frow['form_data'], true);
                    if (is_array($fdata)) {
                        $fallbackHouseType = (string)($fdata['preferred_property'] ?? '');
                    }
                }
            } catch (Throwable $e) {}
        }

        $required = round($fallbackAmountOffered * 0.5, 2);
        $eligible = $required > 0 ? ($fallbackTotalPaid >= $required) : false;
        $fullPaid = ($fallbackAmountOffered > 0) && ($fallbackTotalPaid >= ($fallbackAmountOffered * 0.999));

        echo json_encode([
            'success' => true,
            'deal_found' => ($fallbackAmountOffered > 0),
            'message' => ($fallbackAmountOffered > 0) ? 'Using allocation/payment data' : 'No deal found',
            'estate_name' => $fallbackEstateName,
            'estate_id' => $fallbackEstateId,
            'sqm' => $fallbackSqm,
            'amount' => $fallbackAmountOffered,
            'amount_offered' => $fallbackAmountOffered,
            'deal_id' => $fallbackDealId,
            'total_paid' => $fallbackTotalPaid,
            'required' => $required,
            'eligible' => $eligible,
            'full_paid' => $fullPaid,
            'house_type' => $fallbackHouseType
        ]);
        exit;
    }

    $totalPaid = $computeTotalPaid($clientId, (int)($deal['id'] ?? 0), $companyId);
    if ($amountOffered > 0 && $totalPaid > 0 && $totalPaid > $amountOffered) {
        $amountOffered = $totalPaid;
        $amountSource = 'paid_fallback';
    }
    $required = round($amountOffered * 0.5, 2);
    $eligible = $required > 0 ? ($totalPaid >= $required) : false;
    $fullPaid = ($amountSource !== 'paid_fallback') && ($amountOffered > 0) && ($totalPaid >= ($amountOffered * 0.999));

    $houseType = '';
    try {
        $stf = $pdo->prepare("SELECT form_data FROM client_forms WHERE client_id = ? ORDER BY updated_at DESC, created_at DESC LIMIT 1");
        $stf->execute([$clientId]);
        $frow = $stf->fetch(PDO::FETCH_ASSOC);
        if ($frow && !empty($frow['form_data'])) {
            $fdata = json_decode($frow['form_data'], true);
            if (is_array($fdata)) {
                $houseType = $fdata['preferred_property'] ?? '';
            }
        }
    } catch (Throwable $e) {}

    echo json_encode([
        'success' => true,
        'deal_found' => true,
        'estate_name' => $estateName,
        'estate_id' => $estateId,
        'sqm' => $dealDetails['sqm'],
        'amount' => $amountOffered,
        'amount_offered' => $amountOffered,
        'deal_id' => (int)$deal['id'],
        'total_paid' => $totalPaid,
        'required' => $required,
        'eligible' => $eligible,
        'full_paid' => $fullPaid,
        'house_type' => $houseType
    ]);
} catch (Throwable $e) {
    echo json_encode(['success' => false, 'error' => 'Server error']);
}