<?php
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';

function parseDateOrNull($value) {
    if (!$value) return null;
    $time = strtotime($value);
    return $time ? date('Y-m-d', $time) : null;
}

function buildContractFilters(array $input, string $dateColumn = 'cc.CreatedAt', array $extraSearchColumns = []) {
    $where = [];
    $params = [];

    if (!empty($input['from'])) {
        $where[] = "$dateColumn >= :fromDate";
        $params[':fromDate'] = $input['from'] . ($dateColumn === 'cc.CreatedAt' ? ' 00:00:00' : '');
    }

    if (!empty($input['to'])) {
        $where[] = "$dateColumn <= :toDate";
        $params[':toDate'] = $input['to'] . ($dateColumn === 'cc.CreatedAt' ? ' 23:59:59' : '');
    }

    if (!empty($input['areaId'])) {
        $where[] = 'a.Id = :areaId';
        $params[':areaId'] = $input['areaId'];
    }

    if (!empty($input['status'])) {
        $where[] = 'cc.Status = :status';
        $params[':status'] = $input['status'];
    }

    if (!empty($input['search'])) {
        $columns = array_merge(['cc.ContractId', 'c.FullName', 'c.ClientId', 'a.Name', 'pl.Code'], $extraSearchColumns);
        $searchParts = array_map(function ($column) {
            return $column . ' LIKE :search';
        }, $columns);
        $where[] = '(' . implode(' OR ', $searchParts) . ')';
        $params[':search'] = '%' . $input['search'] . '%';
    }

    return [$where, $params];
}

$filters = [
    'from' => isset($_GET['from']) ? parseDateOrNull($_GET['from']) : null,
    'to' => isset($_GET['to']) ? parseDateOrNull($_GET['to']) : null,
    'areaId' => isset($_GET['areaId']) && ctype_digit((string)$_GET['areaId']) ? (int)$_GET['areaId'] : 0,
    'status' => isset($_GET['status']) ? trim((string)$_GET['status']) : '',
    'search' => isset($_GET['search']) ? trim((string)$_GET['search']) : '',
];

try {
    [$contractWhere, $contractParams] = buildContractFilters($filters);
    $contractWhereSql = $contractWhere ? ('WHERE ' . implode(' AND ', $contractWhere)) : '';

    $paidSubquery = "
        SELECT ContractId, IFNULL(SUM(Amount), 0) AS AmountPaid
        FROM ClientPayments
        WHERE IsDeleted IS NULL OR IsDeleted = 0
        GROUP BY ContractId
    ";

    $salesSql = "
        SELECT
            cc.Id,
            cc.ContractId AS ContractCode,
            c.FullName AS ClientName,
            pl.Code AS PlotCode,
            a.Name AS AreaName,
            IFNULL(cc.OriginalPrice, 0) AS OriginalPrice,
            IFNULL(cc.DiscountAmount, 0) AS DiscountAmount,
            IFNULL(cc.FinalPrice, 0) AS FinalPrice,
            cc.Status,
            cc.CreatedAt,
            IFNULL(paid.AmountPaid, 0) AS AmountPaid
        FROM ClientContracts cc
        INNER JOIN Clients c ON c.Id = cc.ClientId
        INNER JOIN Plots pl ON pl.Id = cc.PlotId
        INNER JOIN Areas a ON a.Id = pl.AreaId
        LEFT JOIN ($paidSubquery) paid ON paid.ContractId = cc.Id
        $contractWhereSql
        ORDER BY cc.CreatedAt DESC, cc.Id DESC
    ";

    $stmt = $pdo->prepare($salesSql);
    $stmt->execute($contractParams);
    $contractRows = $stmt->fetchAll(PDO::FETCH_ASSOC);

    $sales = [];
    $outstanding = [];
    $totalRevenue = 0.0;
    $totalOutstanding = 0.0;
    $plotIds = [];
    $clientIds = [];

    foreach ($contractRows as $row) {
        $finalPrice = (float)$row['FinalPrice'];
        $amountPaid = (float)$row['AmountPaid'];
        $balance = max($finalPrice - $amountPaid, 0.0);
        $status = (string)($row['Status'] ?? '');

        $sales[] = [
            'ContractCode' => $row['ContractCode'] ?: ('CT-' . str_pad((string)$row['Id'], 6, '0', STR_PAD_LEFT)),
            'ClientName' => $row['ClientName'],
            'PlotCode' => $row['PlotCode'],
            'AreaName' => $row['AreaName'],
            'OriginalPrice' => (float)$row['OriginalPrice'],
            'DiscountAmount' => (float)$row['DiscountAmount'],
            'FinalPrice' => $finalPrice,
            'Status' => $status,
            'CreatedAt' => $row['CreatedAt'] ? date('Y-m-d', strtotime($row['CreatedAt'])) : '',
        ];

        if (strcasecmp($status, 'Cancelled') !== 0) {
            $totalRevenue += $finalPrice;
            $totalOutstanding += $balance;
        }

        if ($balance > 0.009 && strcasecmp($status, 'Cancelled') !== 0) {
            $outstanding[] = [
                'ClientName' => $row['ClientName'],
                'PlotCode' => $row['PlotCode'],
                'FinalPrice' => $finalPrice,
                'AmountPaid' => $amountPaid,
                'Outstanding' => $balance,
                'Status' => $status,
            ];
        }
    }

    $identitySql = "
        SELECT cc.ClientId, cc.PlotId
        FROM ClientContracts cc
        INNER JOIN Clients c ON c.Id = cc.ClientId
        INNER JOIN Plots pl ON pl.Id = cc.PlotId
        INNER JOIN Areas a ON a.Id = pl.AreaId
        $contractWhereSql
    ";
    $identityStmt = $pdo->prepare($identitySql);
    $identityStmt->execute($contractParams);
    foreach ($identityStmt->fetchAll(PDO::FETCH_ASSOC) as $identity) {
        $clientIds[(int)$identity['ClientId']] = true;
        $plotIds[(int)$identity['PlotId']] = true;
    }

    [$paymentWhere, $paymentParams] = buildContractFilters($filters, 'p.PaymentDate', ['p.InvoiceNumber', 'p.Reference']);
    $paymentWhere[] = '(p.IsDeleted IS NULL OR p.IsDeleted = 0)';
    $paymentWhereSql = 'WHERE ' . implode(' AND ', $paymentWhere);

    $paymentsSql = "
        SELECT
            p.InvoiceNumber,
            p.PaymentDate,
            p.Amount,
            p.Method,
            p.Reference,
            cc.ContractId AS ContractCode,
            c.FullName AS ClientName
        FROM ClientPayments p
        INNER JOIN ClientContracts cc ON cc.Id = p.ContractId
        INNER JOIN Clients c ON c.Id = cc.ClientId
        INNER JOIN Plots pl ON pl.Id = cc.PlotId
        INNER JOIN Areas a ON a.Id = pl.AreaId
        $paymentWhereSql
        ORDER BY p.PaymentDate DESC, p.Id DESC
    ";

    $paymentStmt = $pdo->prepare($paymentsSql);
    $paymentStmt->execute($paymentParams);
    $paymentRows = $paymentStmt->fetchAll(PDO::FETCH_ASSOC);

    $payments = [];
    $totalCollected = 0.0;
    foreach ($paymentRows as $payment) {
        $amount = (float)$payment['Amount'];
        $totalCollected += $amount;
        $payments[] = [
            'InvoiceNumber' => $payment['InvoiceNumber'],
            'ClientName' => $payment['ClientName'],
            'ContractCode' => $payment['ContractCode'],
            'PaymentDate' => $payment['PaymentDate'] ? date('Y-m-d', strtotime($payment['PaymentDate'])) : '',
            'Amount' => $amount,
            'Method' => $payment['Method'],
            'Reference' => $payment['Reference'],
        ];
    }

    echo json_encode([
        'kpi' => [
            'totalClients' => count($clientIds),
            'totalContracts' => count($sales),
            'totalRevenue' => $totalRevenue,
            'totalCollected' => $totalCollected,
            'outstanding' => $totalOutstanding,
            'plotsSold' => count($plotIds),
        ],
        'sales' => $sales,
        'payments' => $payments,
        'outstanding' => $outstanding,
    ]);
} catch (Exception $e) {
    http_response_code(500);
    echo json_encode([
        'error' => 'Failed to load reports',
        'details' => $e->getMessage(),
    ]);
}