<?php
// DEBUG: show PHP errors (remove or lower in production)
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: *");
header("Access-Control-Allow-Methods: GET, POST, OPTIONS");
header("Access-Control-Allow-Headers: Content-Type");

if ($_SERVER['REQUEST_METHOD'] === 'OPTIONS') {
    http_response_code(204);
    echo json_encode(["status" => "ok", "message" => "OPTIONS"]);
    exit;
}

// Include config.php which creates $pdo
require_once __DIR__ . '/../../config.php';

// Simple DB check
try {
    $stmt = $pdo->query("SELECT 1");
    $test = $stmt->fetchColumn();
} catch (Exception $e) {
    http_response_code(500);
    echo json_encode(["error" => "DB test failed", "details" => $e->getMessage()]);
    exit;
}

$method = $_SERVER['REQUEST_METHOD'];
$entity = isset($_GET['entity']) ? strtolower($_GET['entity']) : null;

if (!$entity) {
    http_response_code(400);
    echo json_encode([
        "error"  => "Missing 'entity' parameter.",
        "method" => $method
    ]);
    exit;
}

try {
    // --------------- GET: PULL DATA TO DESKTOP ---------------
    if ($method === 'GET') {
        switch ($entity) {
            case 'clients':
                pullClients($pdo);
                break;
            case 'areas':
                pullAreas($pdo);
                break;
            case 'plots':
                pullPlots($pdo);
                break;
            case 'contracts':
                pullContracts($pdo);
                break;
            case 'users':
                pullUsers($pdo);
                break;
            case 'discounts':
                pullDiscounts($pdo);
                break;
            default:
                http_response_code(400);
                echo json_encode(["error" => "Unknown entity '$entity' for GET."]);
                break;
        }
        exit;
    }

    // --------------- POST: PUSH DATA FROM DESKTOP ---------------
    if ($method === 'POST') {
        $raw  = file_get_contents('php://input');
        $data = json_decode($raw, true);

        if (!is_array($data)) {
            http_response_code(400);
            echo json_encode(["error" => "Invalid JSON payload"]);
            exit;
        }

        switch ($entity) {
            case 'clients':
                syncClients($pdo, $data);
                break;
            case 'areas':
                syncAreas($pdo, $data);
                break;
            case 'users':
                syncUsers($pdo, $data);
                break;
            case 'discounts':
                syncDiscounts($pdo, $data);
                break;
            case 'plots':
                syncPlots($pdo, $data);
                break;
            case 'contracts':
                syncContracts($pdo, $data);
                break;
            case 'payments':
                syncPayments($pdo, $data);
                break;
            default:
                http_response_code(400);
                echo json_encode(["error" => "Unknown entity '$entity'."]);
                break;
        }
        exit;
    }

    http_response_code(405);
    echo json_encode(["error" => "Method not allowed", "method" => $method]);
} catch (Exception $e) {
    http_response_code(500);
    echo json_encode([
        "error"   => "Server error",
        "details" => $e->getMessage()
    ]);
}

// ----------------- GET HELPERS (PULL) -----------------

function pullClients(PDO $pdo)
{
    // Must match ClientDto in desktop
    $stmt = $pdo->query("
        SELECT
            Id,
            ClientId,
            Surname,
            FirstName,
            OtherName,
            FullName,
            Gender,
            DateOfBirth,
            MaritalStatus,
            Nationality,
            StateOfOrigin,
            Lga,
            Address,
            Phone,
            AltPhone,
            Email,
            NokName,
            NokRelationship,
            NokPhone,
            NokAddress,
            CreatedAt
        FROM Clients
        ORDER BY Id
    ");
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    echo json_encode($rows);
}

function pullAreas(PDO $pdo)
{
    $stmt = $pdo->query("
        SELECT
            Id,
            Name,
            Description,
            BasePricePerPlot
        FROM Areas
        ORDER BY Id
    ");
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    echo json_encode($rows);
}

function pullPlots(PDO $pdo)
{
    $stmt = $pdo->query("
        SELECT
            Id,
            AreaId,
            Code,
            FullPrice,
            Status,
            BuyerName,
            Notes,
            ClientId
        FROM Plots
        ORDER BY Id
    ");
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    echo json_encode($rows);
}

function pullContracts(PDO $pdo)
{
    // Matches ContractSyncDto in desktop
    $stmt = $pdo->query("
        SELECT
            Id,
            ClientId,
            PlotId,
            OriginalPrice,
            DiscountCode,
            DiscountType,
            DiscountValue,
            DiscountAmount,
            FinalPrice,
            PlanType,
            PlanMonths,
            MonthlyAmount,
            Status,
            CreatedAt,
            ContractId
        FROM ClientContracts
        ORDER BY Id
    ");
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    echo json_encode($rows);
}

function pullUsers(PDO $pdo)
{
    $stmt = $pdo->query("
        SELECT
            Id,
            FullName,
            Username,
            Email,
            Role,
            AllowWebAccess,
            PasswordHash,
            LastModified
        FROM Users
        ORDER BY Id
    ");
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    echo json_encode($rows);
}

function pullDiscounts(PDO $pdo)
{
    $stmt = $pdo->query("
        SELECT
            Id,
            Name,
            Code,
            Type,
            PercentageValue,
            AmountValue,
            StartDate,
            EndDate,
            MaxUses,
            TimesUsed,
            IsActive
        FROM Discounts
        ORDER BY Id
    ");
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    echo json_encode($rows);
}

// ----------------- POST SYNC FUNCTIONS -----------------

// CLIENTS: already working with ClientSummaryDto
function syncClients(PDO $pdo, array $clients)
{
    $pdo->beginTransaction();

    $stmt = $pdo->prepare("
        INSERT INTO Clients
        (Id, ClientId, FullName, Phone, Email, CreatedAt)
        VALUES
        (:Id, :ClientId, :FullName, :Phone, :Email, :CreatedAt)
        ON DUPLICATE KEY UPDATE
            ClientId  = VALUES(ClientId),
            FullName  = VALUES(FullName),
            Phone     = VALUES(Phone),
            Email     = VALUES(Email),
            CreatedAt = VALUES(CreatedAt)
    ");

    $count = 0;

    foreach ($clients as $c) {
        $id = isset($c['Id']) ? (int)$c['Id'] : null;
        if ($id === null) {
            continue;
        }

        $clientId  = $c['ClientId']  ?? null;
        $fullName  = $c['FullName']  ?? '';
        $phone     = $c['Phone']     ?? null;
        $email     = $c['Email']     ?? null;
        $createdAt = $c['CreatedAt'] ?? date('Y-m-d H:i:s');

        $stmt->execute([
            ':Id'       => $id,
            ':ClientId' => $clientId,
            ':FullName' => $fullName,
            ':Phone'    => $phone,
            ':Email'    => $email,
            ':CreatedAt'=> $createdAt
        ]);

        $count++;
    }

    $pdo->commit();

    echo json_encode([
        "status" => "ok",
        "entity" => "clients",
        "synced" => $count
    ]);
}

// AREAS
function syncAreas(PDO $pdo, array $areas)
{
    $pdo->beginTransaction();

    $stmt = $pdo->prepare("
        INSERT INTO Areas
        (Id, Name, Description, BasePricePerPlot)
        VALUES
        (:Id, :Name, :Description, :BasePricePerPlot)
        ON DUPLICATE KEY UPDATE
            Name             = VALUES(Name),
            Description      = VALUES(Description),
            BasePricePerPlot = VALUES(BasePricePerPlot)
    ");

    $count = 0;

    foreach ($areas as $a) {
        $id = isset($a['Id']) ? (int)$a['Id'] : null;
        if ($id === null) {
            continue;
        }

        $name        = $a['Name'] ?? '';
        $description = $a['Description'] ?? null;
        $basePrice   = isset($a['BasePricePerPlot']) ? (float)$a['BasePricePerPlot'] : null;

        $stmt->execute([
            ':Id'              => $id,
            ':Name'            => $name,
            ':Description'     => $description,
            ':BasePricePerPlot'=> $basePrice
        ]);

        $count++;
    }

    $pdo->commit();

    echo json_encode([
        "status" => "ok",
        "entity" => "areas",
        "synced" => $count
    ]);
}

// USERS – timestamp aware with ISO → MySQL conversion
function syncUsers(PDO $pdo, array $users)
{
    $pdo->beginTransaction();

    // Get current LastModified for an existing user
    $select = $pdo->prepare("SELECT LastModified FROM Users WHERE Id = :Id");

    // Insert new user
    $insert = $pdo->prepare("
        INSERT INTO Users
        (Id, FullName, Username, Email, Role, AllowWebAccess, PasswordHash, LastModified)
        VALUES
        (:Id, :FullName, :Username, :Email, :Role, :AllowWebAccess, :PasswordHash, :LastModified)
    ");

    // Update existing user if desktop is newer
    $update = $pdo->prepare("
        UPDATE Users
        SET FullName       = :FullName,
            Username       = :Username,
            Email          = :Email,
            Role           = :Role,
            AllowWebAccess = :AllowWebAccess,
            PasswordHash   = :PasswordHash,
            LastModified   = :LastModified
        WHERE Id = :Id
    ");

    $count = 0;

foreach ($users as $u) {
    $id = isset($u['Id']) ? (int)$u['Id'] : null;
    if ($id === null) {
        continue;
    }

    $fullName = $u['FullName']    ?? '';
    $username = $u['Username']    ?? '';
    $email    = $u['Email']       ?? '';
    $role     = $u['Role']        ?? '';
    $allow    = !empty($u['AllowWebAccess']) ? 1 : 0;
    $hash     = $u['PasswordHash'] ?? '';

    // Incoming LastModified from desktop (ISO)
    $incomingLast = $u['LastModified'] ?? null;
    if ($incomingLast === null) {
        $incomingLast = gmdate('Y-m-d\TH:i:s\Z');
    }
    $incomingTime = strtotime($incomingLast);
    if ($incomingTime === false) {
        $incomingTime = time();
    }
    $incomingLastMysql = gmdate('Y-m-d H:i:s', $incomingTime);

    // 1) Try find by Id
    $select->execute([':Id' => $id]);
    $row = $select->fetch(PDO::FETCH_ASSOC);

    if (!$row) {
        // 2) If not found by Id, try find by Username
        $selectByUsername = $pdo->prepare("SELECT Id, LastModified FROM Users WHERE Username = :Username");
        $selectByUsername->execute([':Username' => $username]);
        $rowByUser = $selectByUsername->fetch(PDO::FETCH_ASSOC);

        if ($rowByUser) {
            // Treat as same user: adopt server Id and LastModified
            $serverId   = (int)$rowByUser['Id'];
            $serverLast = $rowByUser['LastModified'];

            $serverTime = $serverLast ? strtotime($serverLast) : null;
            if ($serverTime !== null && $incomingTime <= $serverTime) {
                // Server is newer or equal → skip
                continue;
            }

            // Update existing row (by server Id)
            $update->execute([
                ':Id'             => $serverId,
                ':FullName'       => $fullName,
                ':Username'       => $username,
                ':Email'          => $email,
                ':Role'           => $role,
                ':AllowWebAccess' => $allow,
                ':PasswordHash'   => $hash,
                ':LastModified'   => $incomingLastMysql,
            ]);

            $count++;
            continue;
        }

        // 3) Not found by Id or Username → INSERT new user
        $insert->execute([
            ':Id'             => $id,
            ':FullName'       => $fullName,
            ':Username'       => $username,
            ':Email'          => $email,
            ':Role'           => $role,
            ':AllowWebAccess' => $allow,
            ':PasswordHash'   => $hash,
            ':LastModified'   => $incomingLastMysql,
        ]);

        $count++;
        continue;
    }

    // Existing row found by Id → compare LastModified and UPDATE as you already do
    $serverLast = $row['LastModified'];
    if (!empty($serverLast)) {
        $serverTime = strtotime($serverLast);
        if ($incomingTime <= $serverTime) {
            continue;
        }
    }

    $update->execute([
        ':Id'             => $id,
        ':FullName'       => $fullName,
        ':Username'       => $username,
        ':Email'          => $email,
        ':Role'           => $role,
        ':AllowWebAccess' => $allow,
        ':PasswordHash'   => $hash,
        ':LastModified'   => $incomingLastMysql,
    ]);

    $count++;
}


    $pdo->commit();

    echo json_encode([
        "status" => "ok",
        "entity" => "users",
        "synced" => $count
    ]);
}

// DISCOUNTS
function syncDiscounts(PDO $pdo, array $discounts)
{
    $pdo->beginTransaction();

    $stmt = $pdo->prepare("
        INSERT INTO Discounts
        (Id, Name, Code, Type,
         PercentageValue, AmountValue,
         StartDate, EndDate,
         MaxUses, TimesUsed, IsActive)
        VALUES
        (:Id, :Name, :Code, :Type,
         :PercentageValue, :AmountValue,
         :StartDate, :EndDate,
         :MaxUses, :TimesUsed, :IsActive)
        ON DUPLICATE KEY UPDATE
            Name            = VALUES(Name),
            Code            = VALUES(Code),
            Type            = VALUES(Type),
            PercentageValue = VALUES(PercentageValue),
            AmountValue     = VALUES(AmountValue),
            StartDate       = VALUES(StartDate),
            EndDate         = VALUES(EndDate),
            MaxUses         = VALUES(MaxUses),
            TimesUsed       = VALUES(TimesUsed),
            IsActive        = VALUES(IsActive)
    ");

    $count = 0;

    foreach ($discounts as $d) {
        $id = isset($d['Id']) ? (int)$d['Id'] : null;
        if ($id === null) continue;

        $name   = $d['Name'] ?? '';
        $code   = $d['Code'] ?? '';
        $type   = $d['Type'] ?? '';
        $perc   = isset($d['PercentageValue']) ? (float)$d['PercentageValue'] : null;
        $amount = isset($d['AmountValue']) ? (float)$d['AmountValue'] : null;
        $start  = $d['StartDate'] ?? null;
        $end    = $d['EndDate'] ?? null;
        $max    = isset($d['MaxUses']) ? (int)$d['MaxUses'] : null;
        $times  = isset($d['TimesUsed']) ? (int)$d['TimesUsed'] : 0;
        $active = !empty($d['IsActive']) ? 1 : 0;

        $stmt->execute([
            ':Id'              => $id,
            ':Name'            => $name,
            ':Code'            => $code,
            ':Type'            => $type,
            ':PercentageValue' => $perc,
            ':AmountValue'     => $amount,
            ':StartDate'       => $start,
            ':EndDate'         => $end,
            ':MaxUses'         => $max,
            ':TimesUsed'       => $times,
            ':IsActive'        => $active
        ]);

        $count++;
    }

    $pdo->commit();

    echo json_encode([
        "status" => "ok",
        "entity" => "discounts",
        "synced" => $count
    ]);
}

// PLOTS
function syncPlots(PDO $pdo, array $plots)
{
    $pdo->beginTransaction();

    $stmt = $pdo->prepare("
        INSERT INTO Plots
            (Id, AreaId, Code, FullPrice, Status, BuyerName, Notes, ClientId)
        VALUES
            (:Id, :AreaId, :Code, :FullPrice, :Status, :BuyerName, :Notes, :ClientId)
        ON DUPLICATE KEY UPDATE
            AreaId    = VALUES(AreaId),
            Code      = VALUES(Code),
            FullPrice = VALUES(FullPrice),
            Status    = VALUES(Status),
            BuyerName = VALUES(BuyerName),
            Notes     = VALUES(Notes),
            ClientId  = VALUES(ClientId)
    ");

    $count = 0;

    foreach ($plots as $p) {
        $id = isset($p['Id']) ? (int)$p['Id'] : null;
        if ($id === null) continue;

        $areaId    = isset($p['AreaId'])    ? (int)$p['AreaId']    : 0;
        $code      = $p['Code']             ?? '';
        $price     = isset($p['FullPrice']) ? (float)$p['FullPrice']: 0;
        $status    = $p['Status']           ?? 'Available';
        $buyerName = $p['BuyerName']        ?? null;
        $notes     = $p['Notes']            ?? null;
        $clientId  = isset($p['ClientId'])  ? (int)$p['ClientId']  : null;

        $stmt->execute([
            ':Id'        => $id,
            ':AreaId'    => $areaId,
            ':Code'      => $code,
            ':FullPrice' => $price,
            ':Status'    => $status,
            ':BuyerName' => $buyerName,
            ':Notes'     => $notes,
            ':ClientId'  => $clientId
        ]);

        $count++;
    }

    $pdo->commit();

    echo json_encode([
        "status" => "ok",
        "entity" => "plots",
        "synced" => $count
    ]);
}

// CONTRACTS
function syncContracts(PDO $pdo, array $contracts)
{
    $pdo->beginTransaction();

    $stmt = $pdo->prepare("
        INSERT INTO ClientContracts
        (Id, ContractId, ClientId, PlotId,
         OriginalPrice, DiscountCode, DiscountType, DiscountValue, DiscountAmount,
         FinalPrice, PlanType, PlanMonths, MonthlyAmount, Status, CreatedAt)
        VALUES
        (:Id, :ContractId, :ClientId, :PlotId,
         :OriginalPrice, :DiscountCode, :DiscountType, :DiscountValue, :DiscountAmount,
         :FinalPrice, :PlanType, :PlanMonths, :MonthlyAmount, :Status, :CreatedAt)
        ON DUPLICATE KEY UPDATE
            ContractId    = VALUES(ContractId),
            ClientId      = VALUES(ClientId),
            PlotId        = VALUES(PlotId),
            OriginalPrice = VALUES(OriginalPrice),
            DiscountCode  = VALUES(DiscountCode),
            DiscountType  = VALUES(DiscountType),
            DiscountValue = VALUES(DiscountValue),
            DiscountAmount= VALUES(DiscountAmount),
            FinalPrice    = VALUES(FinalPrice),
            PlanType      = VALUES(PlanType),
            PlanMonths    = VALUES(PlanMonths),
            MonthlyAmount = VALUES(MonthlyAmount),
            Status        = VALUES(Status),
            CreatedAt     = VALUES(CreatedAt)
    ");

    // Helpers to check FK existence
    $checkClient = $pdo->prepare("SELECT COUNT(*) FROM Clients WHERE Id = ?");
    $checkPlot   = $pdo->prepare("SELECT COUNT(*) FROM Plots   WHERE Id = ?");

    $count = 0;

    foreach ($contracts as $c) {
        $id = isset($c['Id']) ? (int)$c['Id'] : null;
        if ($id === null) continue;

        $clientId = isset($c['ClientId']) ? (int)$c['ClientId'] : 0;
        $plotId   = isset($c['PlotId'])   ? (int)$c['PlotId']   : 0;

        // Skip if client does not exist
        $checkClient->execute([$clientId]);
        if ($checkClient->fetchColumn() == 0) {
            continue;
        }

        // Skip if plot does not exist
        $checkPlot->execute([$plotId]);
        if ($checkPlot->fetchColumn() == 0) {
            continue;
        }

        $contractId = $c['ContractId'] ?? null;
        $origPrice  = isset($c['OriginalPrice']) ? (float)$c['OriginalPrice'] : 0;
        $discCode   = $c['DiscountCode'] ?? null;
        $discType   = $c['DiscountType'] ?? null;
        $discValue  = isset($c['DiscountValue']) ? (float)$c['DiscountValue'] : null;
        $discAmount = isset($c['DiscountAmount']) ? (float)$c['DiscountAmount'] : 0;
        $finalPrice = isset($c['FinalPrice']) ? (float)$c['FinalPrice'] : 0;
        $planType   = $c['PlanType'] ?? 'Full';
        $planMonths = isset($c['PlanMonths']) ? (int)$c['PlanMonths'] : 0;
        $monthlyAmt = isset($c['MonthlyAmount']) ? (float)$c['MonthlyAmount'] : 0;
        $status     = $c['Status'] ?? 'Active';
        $createdAt  = $c['CreatedAt'] ?? date('Y-m-d H:i:s');

        $stmt->execute([
            ':Id'            => $id,
            ':ContractId'    => $contractId,
            ':ClientId'      => $clientId,
            ':PlotId'        => $plotId,
            ':OriginalPrice' => $origPrice,
            ':DiscountCode'  => $discCode,
            ':DiscountType'  => $discType,
            ':DiscountValue' => $discValue,
            ':DiscountAmount'=> $discAmount,
            ':FinalPrice'    => $finalPrice,
            ':PlanType'      => $planType,
            ':PlanMonths'    => $planMonths,
            ':MonthlyAmount' => $monthlyAmt,
            ':Status'        => $status,
            ':CreatedAt'     => $createdAt
        ]);

        $count++;
    }

    $pdo->commit();

    echo json_encode([
        "status" => "ok",
        "entity" => "contracts",
        "synced" => $count
    ]);
}

// PAYMENTS (push only)
function syncPayments(PDO $pdo, array $payments)
{
    $pdo->beginTransaction();

    $stmt = $pdo->prepare("
        INSERT INTO ClientPayments
        (Id, Uuid, ContractId, InvoiceNumber,
         PaymentDate, Amount, Method, Reference, Notes,
         IsDeleted, LastModified, CreatedAt)
        VALUES
        (:Id, :Uuid, :ContractId, :InvoiceNumber,
         :PaymentDate, :Amount, :Method, :Reference, :Notes,
         :IsDeleted, :LastModified, :CreatedAt)
        ON DUPLICATE KEY UPDATE
            Uuid          = VALUES(Uuid),
            ContractId    = VALUES(ContractId),
            InvoiceNumber = VALUES(InvoiceNumber),
            PaymentDate   = VALUES(PaymentDate),
            Amount        = VALUES(Amount),
            Method        = VALUES(Method),
            Reference     = VALUES(Reference),
            Notes         = VALUES(Notes),
            IsDeleted     = VALUES(IsDeleted),
            LastModified  = VALUES(LastModified),
            CreatedAt     = VALUES(CreatedAt)
    ");

    // Check that referenced contract exists
    $checkContract = $pdo->prepare("SELECT COUNT(*) FROM ClientContracts WHERE Id = ?");

    $count = 0;

    foreach ($payments as $p) {
        $id = isset($p['Id']) ? (int)$p['Id'] : null;
        if ($id === null) continue;

        $contractId = isset($p['ContractId']) ? (int)$p['ContractId'] : 0;

        // Skip if contract does not exist in MySQL
        $checkContract->execute([$contractId]);
        if ($checkContract->fetchColumn() == 0) {
            continue;
        }

        $uuid      = $p['Uuid'] ?? null;
        $invoice   = $p['InvoiceNumber'] ?? null;
        $date      = $p['PaymentDate'] ?? date('Y-m-d');
        $amount    = isset($p['Amount']) ? (float)$p['Amount'] : 0;
        $method    = $p['Method'] ?? null;
        $ref       = $p['Reference'] ?? null;
        $notes     = $p['Notes'] ?? null;
        $isDeleted = !empty($p['IsDeleted']) ? 1 : 0;
        $lastMod   = $p['LastModified'] ?? date('Y-m-d H:i:s');
        $createdAt = $p['CreatedAt'] ?? date('Y-m-d H:i:s');

        $stmt->execute([
            ':Id'           => $id,
            ':Uuid'         => $uuid,
            ':ContractId'   => $contractId,
            ':InvoiceNumber'=> $invoice,
            ':PaymentDate'  => $date,
            ':Amount'       => $amount,
            ':Method'       => $method,
            ':Reference'    => $ref,
            ':Notes'        => $notes,
            ':IsDeleted'    => $isDeleted,
            ':LastModified' => $lastMod,
            ':CreatedAt'    => $createdAt
        ]);

        $count++;
    }

    $pdo->commit();

    echo json_encode([
        "status" => "ok",
        "entity" => "payments",
        "synced" => $count
    ]);
}
