<?php
require __DIR__ . '/bootstrap.php';
require __DIR__ . '/_walk_access.php';

use App\Core\Auth;
use App\Core\Csrf;
use App\Core\Database;
use App\Core\Response;
use App\Core\Security;

$user = Auth::requireUser();
if (!Auth::hasPermission('payments.manage', $user)) {
    Response::error('You do not have permission to manage walk payments.', 403);
}

$pdo = Database::connection();
$input = request_input();
$method = $_SERVER['REQUEST_METHOD'];
$action = $input['action'] ?? 'overview';

function pyrk_payments_user_can_see_all_paid_walks(?array $user): bool
{
    return $user && (
        Auth::hasPermission('payments.manage', $user)
        || Auth::hasPermission('walks.view_all', $user)
        || Auth::hasPermission('walks.manage', $user)
        || (($user['role_name'] ?? '') === 'super_admin')
    );
}


function pyrk_payments_table_exists(PDO $pdo, string $table): bool
{
    try {
        $stmt = $pdo->prepare('SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = ?');
        $stmt->execute([$table]);
        return (int)$stmt->fetchColumn() > 0;
    } catch (Throwable) {
        return false;
    }
}

function pyrk_payments_column_exists(PDO $pdo, string $table, string $column): bool
{
    static $cache = [];
    $key = $table . '.' . $column;
    if (array_key_exists($key, $cache)) return $cache[$key];

    try {
        $stmt = $pdo->prepare('SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = ? AND column_name = ?');
        $stmt->execute([$table, $column]);
        return $cache[$key] = ((int)$stmt->fetchColumn() > 0);
    } catch (Throwable) {
        return $cache[$key] = false;
    }
}

function pyrk_payments_visible_walk_sql(PDO $pdo, string $alias = 'w'): string
{
    $a = preg_replace('/[^a-zA-Z0-9_]/', '', $alias) ?: 'w';

    $blocked = ['deleted','archived','removed','remove','trash','trashed','cancelled','canceled','inactive'];
    $quoted = implode(',', array_map(static fn($s) => '"' . $s . '"', $blocked));

    $parts = [
        'LOWER(TRIM(COALESCE(' . $a . '.status, ""))) NOT IN (' . $quoted . ')',
    ];

    foreach (['deleted_at','removed_at','archived_at','cancelled_at','canceled_at'] as $col) {
        if (pyrk_payments_column_exists($pdo, 'walks', $col)) $parts[] = $a . '.' . $col . ' IS NULL';
    }

    foreach (['is_deleted','deleted','is_archived','archived','is_removed'] as $col) {
        if (pyrk_payments_column_exists($pdo, 'walks', $col)) $parts[] = 'COALESCE(' . $a . '.' . $col . ', 0) = 0';
    }

    foreach (['deleted_by','removed_by','archived_by'] as $col) {
        if (pyrk_payments_column_exists($pdo, 'walks', $col)) $parts[] = $a . '.' . $col . ' IS NULL';
    }

    // Payment managers must be able to see all paid walks in the payment module.
    // Walk assignment restrictions still apply on walk pages, but not inside payment administration.
    if (!pyrk_payments_user_can_see_all_paid_walks(Auth::user())) {
        $parts[] = pyrk_walk_access_scope_sql($pdo, $a, Auth::user());
    }

    // Same ID-reuse protection as api/walks.php:
    // an old walk_deleted audit log must not hide a new active paid walk that reused the same id.
    if (pyrk_payments_table_exists($pdo, 'audit_logs')) {
        $auditTimeGuard = '';
        if (pyrk_payments_column_exists($pdo, 'audit_logs', 'created_at') && pyrk_payments_column_exists($pdo, 'walks', 'created_at')) {
            $auditTimeGuard = ' AND COALESCE(al_deleted_walk.created_at, "1000-01-01 00:00:00") >= COALESCE(' . $a . '.created_at, "1000-01-01 00:00:00")';
        }

        $parts[] = 'NOT EXISTS (
            SELECT 1 FROM audit_logs al_deleted_walk
            WHERE al_deleted_walk.entity_type = "walks"
              AND al_deleted_walk.entity_id = ' . $a . '.id
              AND al_deleted_walk.action IN ("walk_deleted", "walk_removed", "walk_archived")
              ' . $auditTimeGuard . '
        )';
    }

    return '(' . implode(' AND ', $parts) . ')';
}


function pyrk_payments_default_methods(): array
{
    return [
        'cash' => 'Cash',
        'card' => 'Card',
        'bank_transfer' => 'Bank transfer',
        'online' => 'Online',
        'whish' => 'Whish / mobile transfer',
        'other' => 'Other',
    ];
}

function pyrk_payments_payment_methods(PDO $pdo, bool $activeOnly = true): array
{
    if (!pyrk_payments_table_exists($pdo, 'payment_methods')) {
        return pyrk_payments_default_methods();
    }

    try {
        $sql = 'SELECT code, name FROM payment_methods ' . ($activeOnly ? 'WHERE status = "active" ' : '') . 'ORDER BY sort_order ASC, name ASC';
        $rows = $pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);
        if (!$rows) return pyrk_payments_default_methods();

        $out = [];
        foreach ($rows as $row) {
            $code = strtolower(trim((string)($row['code'] ?? '')));
            if ($code === '') continue;
            $out[$code] = (string)($row['name'] ?? $code);
        }
        return $out ?: pyrk_payments_default_methods();
    } catch (Throwable) {
        return pyrk_payments_default_methods();
    }
}


function pyrk_payments_money(float $value): float
{
    return round(max(0, $value), 2);
}

function pyrk_payments_payment_status(float $due, float $paid, string $rawStatus = ''): string
{
    $rawStatus = strtolower(trim($rawStatus));
    if (in_array($rawStatus, ['refunded', 'cancelled'], true)) return $rawStatus;
    if ($due <= 0 && $paid <= 0) return 'unpaid';
    if ($paid <= 0) return 'unpaid';
    if ($paid + 0.00001 >= $due) return 'paid';
    return 'partial';
}

function pyrk_payments_paid_walks(PDO $pdo): array
{
    if (!pyrk_payments_column_exists($pdo, 'walks', 'payment_model')) return [];

    $sql = 'SELECT w.id, w.title, w.walk_date, w.start_time, w.status,
                   COALESCE(w.payment_model, "free") AS payment_model,
                   COALESCE(w.payment_amount, 0) AS payment_amount,
                   COALESCE(NULLIF(w.payment_currency, ""), "LBP") AS payment_currency,
                   r.route_name,
                   COUNT(DISTINCT wr.user_id) AS registrant_count,
                   COALESCE(SUM(CASE WHEN wp.payment_status IN ("paid","partial") THEN wp.amount_paid ELSE 0 END), 0) AS total_paid,
                   SUM(CASE WHEN wp.payment_status = "paid" THEN 1 ELSE 0 END) AS paid_count,
                   SUM(CASE WHEN wp.payment_status = "partial" THEN 1 ELSE 0 END) AS partial_count
            FROM walks w
            JOIN routes r ON r.id = w.route_id
            LEFT JOIN walk_registrations wr ON wr.walk_id = w.id AND wr.registration_status IN ("registered","approved","waitlisted")
            LEFT JOIN walk_payments wp ON wp.walk_id = w.id AND wp.user_id = wr.user_id
            WHERE COALESCE(w.payment_model, "free") = "paid"
              AND COALESCE(w.payment_amount, 0) > 0
              AND ' . pyrk_payments_visible_walk_sql($pdo, 'w') . '
            GROUP BY w.id
            ORDER BY w.walk_date DESC, w.start_time DESC, w.id DESC
            LIMIT 300';
    return $pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);
}

function pyrk_payments_selected_walk(PDO $pdo, int $walkId): ?array
{
    if ($walkId <= 0) return null;
    $stmt = $pdo->prepare('SELECT w.*, r.route_name
        FROM walks w
        JOIN routes r ON r.id = w.route_id
        WHERE w.id = ?
          AND COALESCE(w.payment_model, "free") = "paid"
          AND COALESCE(w.payment_amount, 0) > 0
          AND ' . pyrk_payments_visible_walk_sql($pdo, 'w') . '
        LIMIT 1');
    $stmt->execute([$walkId]);
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    return $row ?: null;
}

function pyrk_payments_rows(PDO $pdo, int $walkId): array
{
    $hasCoupons = pyrk_payments_table_exists($pdo, 'coupon_redemptions');

    $couponSelect = $hasCoupons
        ? ', cr.discount_amount, cr.final_amount AS coupon_final_amount'
        : ', NULL AS discount_amount, NULL AS coupon_final_amount';

    $couponJoin = $hasCoupons
        ? 'LEFT JOIN coupon_redemptions cr ON cr.walk_id = wr.walk_id AND cr.user_id = wr.user_id'
        : '';

    $sql = "SELECT
                wr.id AS registration_id,
                wr.registration_status,
                wr.registered_at,
                u.id AS user_id,
                u.full_name,
                u.email,
                u.mobile,
                u.profile_photo_path,
                w.payment_amount AS walk_amount,
                w.payment_currency AS walk_currency,
                wp.id AS payment_id,
                wp.amount_due,
                wp.amount_paid,
                wp.currency AS payment_currency,
                wp.payment_type,
                wp.payment_status,
                wp.reference_no,
                wp.notes,
                wp.paid_at,
                wp.updated_at,
                recorded.full_name AS recorded_by_name
                {$couponSelect}
            FROM walk_registrations wr
            JOIN walks w ON w.id = wr.walk_id
            JOIN users u ON u.id = wr.user_id
            LEFT JOIN walk_payments wp ON wp.walk_id = wr.walk_id AND wp.user_id = wr.user_id
            LEFT JOIN users recorded ON recorded.id = wp.recorded_by
            {$couponJoin}
            WHERE wr.walk_id = ?
              AND wr.registration_status IN ('registered','approved','waitlisted')
            ORDER BY
              CASE
                WHEN COALESCE(wp.payment_status, 'unpaid') = 'paid' THEN 3
                WHEN COALESCE(wp.payment_status, 'unpaid') = 'partial' THEN 2
                ELSE 1
              END ASC,
              wr.registered_at DESC,
              u.full_name ASC";

    $stmt = $pdo->prepare($sql);
    $stmt->execute([$walkId]);

    $rows = [];
    foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
        $due = (float)($row['coupon_final_amount'] ?? $row['amount_due'] ?? $row['walk_amount'] ?? 0);
        if ($due <= 0) $due = (float)($row['amount_due'] ?? $row['walk_amount'] ?? 0);
        $paid = (float)($row['amount_paid'] ?? 0);
        $status = pyrk_payments_payment_status($due, $paid, (string)($row['payment_status'] ?? ''));

        $row['amount_due_effective'] = pyrk_payments_money($due);
        $row['amount_paid_effective'] = pyrk_payments_money($paid);
        $row['balance_due'] = pyrk_payments_money(max(0, $due - $paid));
        $row['payment_status_effective'] = $status;
        $row['currency_effective'] = $row['payment_currency'] ?: ($row['walk_currency'] ?: 'LBP');
        $rows[] = $row;
    }

    return $rows;
}

function pyrk_payments_summary(array $rows): array
{
    $totalDue = 0.0;
    $totalPaid = 0.0;
    $paidCount = 0;
    $partialCount = 0;
    $unpaidCount = 0;

    foreach ($rows as $row) {
        $totalDue += (float)$row['amount_due_effective'];
        $totalPaid += (float)$row['amount_paid_effective'];
        $status = (string)$row['payment_status_effective'];
        if ($status === 'paid') $paidCount++;
        elseif ($status === 'partial') $partialCount++;
        else $unpaidCount++;
    }

    return [
        'registrants' => count($rows),
        'paid_count' => $paidCount,
        'partial_count' => $partialCount,
        'unpaid_count' => $unpaidCount,
        'total_due' => pyrk_payments_money($totalDue),
        'total_paid' => pyrk_payments_money($totalPaid),
        'balance_due' => pyrk_payments_money(max(0, $totalDue - $totalPaid)),
    ];
}

if ($method === 'GET' && $action === 'overview') {
    if (!pyrk_payments_table_exists($pdo, 'walk_payments')) {
        Response::error('Payments table is not installed. Run database/migrations/033_paid_walk_payments.sql');
    }

    $walks = pyrk_payments_paid_walks($pdo);
    $requestedWalkId = (int)($input['walk_id'] ?? 0);
    $selectedWalkId = $requestedWalkId > 0 ? $requestedWalkId : (int)($walks[0]['id'] ?? 0);
    $selectedWalk = $selectedWalkId > 0 ? pyrk_payments_selected_walk($pdo, $selectedWalkId) : null;

    $rows = $selectedWalk ? pyrk_payments_rows($pdo, (int)$selectedWalk['id']) : [];
    $summary = pyrk_payments_summary($rows);

    Response::ok([
        'walks' => $walks,
        'selected_walk' => $selectedWalk,
        'rows' => $rows,
        'summary' => $summary,
        'payment_types' => pyrk_payments_payment_methods($pdo),
    ]);
}


function pyrk_payments_paid_at_from_input(array $input, string $status): ?string
{
    if ($status === 'unpaid') {
        return null;
    }

    $paidDate = trim((string)($input['paid_date'] ?? ''));
    $paidTime = trim((string)($input['paid_time'] ?? ''));

    if ($paidDate !== '') {
        if (!preg_match('/^(\d{1,2})\/(\d{1,2})\/(\d{4})$/', $paidDate, $m)) {
            Response::error('Payment date must be dd/mm/yyyy.');
        }

        $day = (int)$m[1];
        $month = (int)$m[2];
        $year = (int)$m[3];

        if (!checkdate($month, $day, $year)) {
            Response::error('Payment date is not valid.');
        }

        if ($paidTime === '') {
            $paidTime = '00:00';
        }

        if (!preg_match('/^\d{2}:\d{2}$/', $paidTime)) {
            Response::error('Payment time must be HH:MM or left empty.');
        }

        return sprintf('%04d-%02d-%02d %s:00', $year, $month, $day, $paidTime);
    }

    $paidAt = trim((string)($input['paid_at'] ?? ''));
    if ($paidAt === '') {
        return date('Y-m-d H:i:s');
    }

    $paidAt = trim(str_replace('T', ' ', $paidAt));

    if (preg_match('/^(\d{1,2})\/(\d{1,2})\/(\d{4})(?:\s+(\d{1,2}):(\d{2}))?$/', $paidAt, $m)) {
        $day = (int)$m[1];
        $month = (int)$m[2];
        $year = (int)$m[3];

        if (!checkdate($month, $day, $year)) {
            Response::error('Payment date is not valid.');
        }

        $hour = isset($m[4]) && $m[4] !== '' ? (int)$m[4] : 0;
        $minute = isset($m[5]) && $m[5] !== '' ? (int)$m[5] : 0;
        if ($hour > 23 || $minute > 59) {
            Response::error('Payment time is not valid.');
        }

        return sprintf('%04d-%02d-%02d %02d:%02d:00', $year, $month, $day, $hour, $minute);
    }

    if (preg_match('/^\d{4}-\d{2}-\d{2}$/', $paidAt)) {
        return $paidAt . ' 00:00:00';
    }

    if (preg_match('/^\d{4}-\d{2}-\d{2} \d{2}:\d{2}$/', $paidAt)) {
        return $paidAt . ':00';
    }

    if (preg_match('/^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$/', $paidAt)) {
        return $paidAt;
    }

    Response::error('Payment date must be dd/mm/yyyy.');
}

if ($method === 'POST' && in_array($action, ['save','record','record_payment','save_payment'], true)) {
    Csrf::validateFromRequest();

    if (!pyrk_payments_table_exists($pdo, 'walk_payments')) {
        Response::error('Payments table is not installed. Run database/migrations/033_paid_walk_payments.sql');
    }

    $walkId = (int)($input['walk_id'] ?? 0);
    $userId = (int)($input['user_id'] ?? 0);
    $registrationId = (int)($input['registration_id'] ?? 0);

    if ($walkId <= 0 || $userId <= 0) Response::error('Walk and member are required.');

    $walk = pyrk_payments_selected_walk($pdo, $walkId);
    if (!$walk) Response::error('Paid walk not found or it is deleted/archived.', 404);

    $regStmt = $pdo->prepare('SELECT id FROM walk_registrations WHERE walk_id = ? AND user_id = ? AND registration_status IN ("registered","approved","waitlisted") LIMIT 1');
    $regStmt->execute([$walkId, $userId]);
    $realRegId = (int)($regStmt->fetchColumn() ?: 0);
    if (!$realRegId) Response::error('This member is not actively registered for the selected walk.', 404);
    if ($registrationId <= 0) $registrationId = $realRegId;

    $currency = strtoupper(trim((string)($input['currency'] ?? $walk['payment_currency'] ?? 'LBP')));
    if (!preg_match('/^[A-Z]{3,8}$/', $currency)) $currency = 'LBP';

    $amountDue = pyrk_payments_money((float)($input['amount_due'] ?? $walk['payment_amount'] ?? 0));
    if ($amountDue <= 0) $amountDue = pyrk_payments_money((float)($walk['payment_amount'] ?? 0));

    $amountPaidRaw = str_replace(',', '', (string)($input['amount_paid'] ?? '0'));
    $amountPaid = is_numeric($amountPaidRaw) ? pyrk_payments_money((float)$amountPaidRaw) : 0.0;

    $status = strtolower(trim((string)($input['payment_status'] ?? '')));
    if (!in_array($status, ['unpaid','partial','paid','refunded','cancelled'], true)) {
        $status = pyrk_payments_payment_status($amountDue, $amountPaid);
    }

    if ($status === 'paid' && $amountPaid <= 0) $amountPaid = $amountDue;
    if ($status === 'unpaid') $amountPaid = 0.0;

    $status = pyrk_payments_payment_status($amountDue, $amountPaid, $status);

    $type = strtolower(trim((string)($input['payment_type'] ?? 'cash')));
    $availableTypes = pyrk_payments_payment_methods($pdo);
    if (!isset($availableTypes[$type])) {
        // Keep historical/custom method codes if they already exist on the current payment row,
        // otherwise fall back to cash. This prevents old receipts from breaking.
        $type = isset($availableTypes['cash']) ? 'cash' : (array_key_first($availableTypes) ?: 'other');
    }

    $reference = trim((string)($input['reference_no'] ?? ''));
    $notes = trim((string)($input['notes'] ?? ''));

    $paidAt = pyrk_payments_paid_at_from_input($input, $status);

    $stmt = $pdo->prepare('INSERT INTO walk_payments
        (walk_id, registration_id, user_id, amount_due, amount_paid, currency, payment_type, payment_status, reference_no, notes, paid_at, recorded_by, created_at, updated_at)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW())
        ON DUPLICATE KEY UPDATE
            registration_id = VALUES(registration_id),
            amount_due = VALUES(amount_due),
            amount_paid = VALUES(amount_paid),
            currency = VALUES(currency),
            payment_type = VALUES(payment_type),
            payment_status = VALUES(payment_status),
            reference_no = VALUES(reference_no),
            notes = VALUES(notes),
            paid_at = VALUES(paid_at),
            recorded_by = VALUES(recorded_by),
            updated_at = NOW()');

    $stmt->execute([
        $walkId,
        $registrationId,
        $userId,
        $amountDue,
        $amountPaid,
        $currency,
        $type,
        $status,
        $reference,
        $notes,
        $paidAt,
        (int)$user['id'],
    ]);

    try {
        Security::audit((int)$user['id'], 'walk_payment_saved', 'walk_payments', $walkId, [
            'walk_id' => $walkId,
            'user_id' => $userId,
            'amount_due' => $amountDue,
            'amount_paid' => $amountPaid,
            'currency' => $currency,
            'payment_type' => $type,
            'payment_status' => $status,
        ]);
    } catch (Throwable) {}

    Response::ok([], 'Payment saved.');
}

if ($method === 'POST' && $action === 'delete') {
    Csrf::validateFromRequest();

    $walkId = (int)($input['walk_id'] ?? 0);
    $userId = (int)($input['user_id'] ?? 0);
    if ($walkId <= 0 || $userId <= 0) Response::error('Walk and member are required.');

    $walk = pyrk_payments_selected_walk($pdo, $walkId);
    if (!$walk) Response::error('Paid walk not found, deleted, archived, or not accessible to your user.', 404);

    $stmt = $pdo->prepare('DELETE FROM walk_payments WHERE walk_id = ? AND user_id = ?');
    $stmt->execute([$walkId, $userId]);

    try {
        Security::audit((int)$user['id'], 'walk_payment_deleted', 'walk_payments', $walkId, [
            'walk_id' => $walkId,
            'user_id' => $userId,
        ]);
    } catch (Throwable) {}

    Response::ok([], 'Payment removed.');
}

Response::error('Unsupported payment action.', 404);
