<?php
// api/get_plots.php?areaId=1

ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

header('Content-Type: application/json');
header('Access-Control-Allow-Origin: *');

require_once __DIR__ . '/../../config.php';

$areaId = isset($_GET['areaId']) ? (int)$_GET['areaId'] : 0;
if ($areaId <= 0) {
    http_response_code(400);
    echo json_encode(['error' => 'Missing or invalid areaId']);
    exit;
}

try {
    /*
     * Schema:
     * - Plots: Id, AreaId, Code, FullPrice, Status, BuyerName, Notes, ClientId
     * - Clients: Id, ClientId (code), FullName, ...
     * - ClientContracts: Id, ContractId (code), PlotId, FinalPrice, PlanType, PlanMonths, MonthlyAmount, Discount*, CreatedAt
     * - ClientPayments: Id, ContractId, Amount
     * - Areas: Id, Name, BasePricePerPlot
     */

    // 1) Load plots for the area, including ClientId and area info
    $sql = "
        SELECT 
            p.Id,
            p.AreaId,
            p.Code,
            p.FullPrice,
            p.Status,
            p.BuyerName,
            p.Notes,
            p.ClientId,
            a.Name AS AreaName,
            a.BasePricePerPlot AS AreaBasePrice
        FROM Plots p
        JOIN Areas a ON a.Id = p.AreaId
        WHERE p.AreaId = :AreaId
        ORDER BY p.Id ASC
    ";

    $stmt = $pdo->prepare($sql);
    $stmt->execute([':AreaId' => $areaId]);
    $plots = $stmt->fetchAll(PDO::FETCH_ASSOC);

    if (!$plots) {
        echo json_encode([]);
        exit;
    }

    // 2) Preload client names + client codes for all ClientIds used in these plots
    $clientIds = [];
    foreach ($plots as $row) {
        if (!empty($row['ClientId'])) {
            $clientIds[] = (int)$row['ClientId'];
        }
    }
    $clientIds = array_values(array_unique($clientIds));

    $clientsById = [];
    if ($clientIds) {
        $inClients = implode(',', array_fill(0, count($clientIds), '?'));
        $sqlClients = "
            SELECT Id, ClientId AS ClientCode, FullName
            FROM Clients
            WHERE Id IN ($inClients)
        ";
        $stmtClients = $pdo->prepare($sqlClients);
        $stmtClients->execute($clientIds);
        $clients = $stmtClients->fetchAll(PDO::FETCH_ASSOC);

        foreach ($clients as $c) {
            $id = (int)$c['Id'];
            $clientsById[$id] = [
                'FullName'   => $c['FullName'],
                'ClientCode' => $c['ClientCode']
            ];
        }
    }

    // 3) Load latest contract per plot (includes CreatedAt)
    $plotIds = array_column($plots, 'Id');
    $inClause = implode(',', array_fill(0, count($plotIds), '?'));

    $contractSql = "
        SELECT c.*
        FROM ClientContracts c
        INNER JOIN (
            SELECT PlotId, MAX(Id) AS LatestId
            FROM ClientContracts
            WHERE PlotId IN ($inClause)
            GROUP BY PlotId
        ) x ON x.LatestId = c.Id
    ";
    $contractStmt = $pdo->prepare($contractSql);
    $contractStmt->execute($plotIds);
    $contracts = $contractStmt->fetchAll(PDO::FETCH_ASSOC);

    $contractsByPlot = [];
    foreach ($contracts as $c) {
        $contractsByPlot[$c['PlotId']] = $c;
    }

    // 4) Sum payments per contract
    $contractIds = array_column($contracts, 'Id');
    $paymentsByContract = [];
    if ($contractIds) {
        $inClauseContracts = implode(',', array_fill(0, count($contractIds), '?'));
        $paySql = "
            SELECT ContractId, IFNULL(SUM(Amount), 0) AS TotalPaid
            FROM ClientPayments
            WHERE ContractId IN ($inClauseContracts)
            GROUP BY ContractId
        ";
        $payStmt = $pdo->prepare($paySql);
        $payStmt->execute($contractIds);
        $payments = $payStmt->fetchAll(PDO::FETCH_ASSOC);

        foreach ($payments as $pRow) {
            $paymentsByContract[$pRow['ContractId']] = (float)$pRow['TotalPaid'];
        }
    }

    // 5) Build final payload
    $result = [];
    foreach ($plots as $row) {
        $plotId    = (int)$row['Id'];
        $fullPrice = (float)$row['FullPrice'];

        $contract         = $contractsByPlot[$plotId] ?? null;
        $contractId       = $contract ? (int)$contract['Id'] : null;
        $contractCode     = $contract && !empty($contract['ContractId']) ? $contract['ContractId'] : null;
        $contractCreatedAt = $contract && !empty($contract['CreatedAt']) ? $contract['CreatedAt'] : null;

        $finalPrice    = $fullPrice;
        $planType      = '-';
        $planMonths    = 0;
        $monthlyAmount = 0.0;
        $nextDue       = null;

        // discount values
        $discountType   = null;
        $discountValue  = null;
        $discountAmount = 0.0;

        if ($contract) {
            if ($contract['FinalPrice'] !== null) {
                $finalPrice = (float)$contract['FinalPrice'];
            }
            if ($contract['PlanType'] !== null && $contract['PlanType'] !== '') {
                $planType = $contract['PlanType'];
            }
            if ($contract['PlanMonths'] !== null) {
                $planMonths = (int)$contract['PlanMonths'];
            }
            if ($contract['MonthlyAmount'] !== null) {
                $monthlyAmount = (float)$contract['MonthlyAmount'];
            }

            // discount fields
            if (!empty($contract['DiscountType'])) {
                $discountType = $contract['DiscountType'];
            }
            if ($contract['DiscountValue'] !== null) {
                $discountValue = (float)$contract['DiscountValue'];
            }
            if ($contract['DiscountAmount'] !== null) {
                $discountAmount = (float)$contract['DiscountAmount'];
            }

            // NextDue = CreatedAt + PlanMonths
            if (!empty($contract['CreatedAt']) && $planMonths > 0) {
                try {
                    $createdAtDt = new DateTime($contract['CreatedAt']);
                    $createdAtDt->modify('+' . $planMonths . ' months');
                    $nextDue = $createdAtDt->format('Y-m-d');
                } catch (Exception $e) {
                    $nextDue = null;
                }
            }
        }

        $totalPaid = 0.0;
        if ($contractId && isset($paymentsByContract[$contractId])) {
            $totalPaid = (float)$paymentsByContract[$contractId];
        }

        $outstanding = $finalPrice - $totalPaid;
        if ($outstanding < 0) {
            $outstanding = 0;
        }

        // Resolve buyer and client code
        $clientId   = !empty($row['ClientId']) ? (int)$row['ClientId'] : null;
        $buyerName  = 'Not Assigned';
        $clientCode = null;

        if ($clientId && isset($clientsById[$clientId])) {
            $buyerName  = $clientsById[$clientId]['FullName'];
            $clientCode = $clientsById[$clientId]['ClientCode'];
        } elseif (!empty($row['BuyerName'])) {
            $buyerName = $row['BuyerName'];
        }

        // HasDiscount flag & human summary
        $hasDiscount   = $discountAmount > 0;
        $discountLabel = 'No';
        if ($hasDiscount) {
            if ($discountType === 'PERCENTAGE' && $discountValue !== null) {
                $discountLabel = sprintf(
                    'Yes (%d%%, ₦%s off)',
                    (int)$discountValue,
                    number_format($discountAmount, 2)
                );
            } else {
                $discountLabel = 'Yes (₦' . number_format($discountAmount, 2) . ' off)';
            }
        }

        $result[] = [
            'Id'                   => $plotId,
            'AreaId'               => (int)$row['AreaId'],
            'Code'                 => $row['Code'],
            'FullPrice'            => $fullPrice,
            'Status'               => $row['Status'],
            'BuyerName'            => $buyerName,
            'Notes'                => $row['Notes'],

            'AreaName'             => $row['AreaName'],
            'AreaBasePrice'        => (float)$row['AreaBasePrice'],

            'ContractPrice'        => $finalPrice,
            'PlanType'             => $planType,
            'PlanDurationMonths'   => $planMonths,
            'MonthlyAmount'        => $monthlyAmount,
            'TotalPaid'            => $totalPaid,
            'OutstandingAmount'    => $outstanding,

            'HasDiscount'          => $hasDiscount ? 1 : 0,
            'DiscountAmount'       => $discountAmount,
            'DiscountType'         => $discountType,
            'DiscountValue'        => $discountValue,
            'DiscountLabel'        => $discountLabel,

            'NextDue'              => $nextDue,

            // NEW for desktop detail
            'ClientId'             => $clientId,
            'ClientCode'           => $clientCode,
            'ContractId'           => $contractId,
            'ContractCode'         => $contractCode,
            'ContractCreatedAt'    => $contractCreatedAt,
        ];
    }

    echo json_encode($result);
} catch (Exception $e) {
    http_response_code(500);
    echo json_encode([
        'error'   => 'Failed to load plots',
        'details' => $e->getMessage()
    ]);
}
