<?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\Services\PhotoVotingService;

$user = Auth::requireRole(['super_admin','event_manager']);
$pdo = Database::connection();
$input = request_input();
$action = $input['action'] ?? 'list';
$svc = new PhotoVotingService();

function pyrk_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_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_visible_walk_sql(PDO $pdo, string $alias = 'w'): string
{
    $a = preg_replace('/[^a-zA-Z0-9_]/', '', $alias) ?: 'w';

    $blockedStatuses = [
        'deleted', 'archived', 'removed', 'remove', 'trash', 'trashed',
        'cancelled', 'canceled', 'inactive'
    ];

    $quoted = implode(',', array_map(static fn($s) => '"' . $s . '"', $blockedStatuses));

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

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

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

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

    // Extra protection for installs where deleted walks were logged but not removed.
    if (pyrk_table_exists($pdo, 'audit_logs')) {
        $parts[] = 'NOT EXISTS (
            SELECT 1 FROM audit_logs al
            WHERE al.entity_type = "walks"
              AND al.entity_id = ' . $a . '.id
              AND al.action IN ("walk_deleted", "walk_removed", "walk_archived", "walk_cancelled", "walk_canceled", "walk_hidden")
        )';
    }

    // Community/Sunrise extra legacy guard: some old databases keep deleted names as normal rows.
    // This only applies when the audit table exists or deletion flags exist, but still keeps old cached rows out.

    // Keep the existing future-walk photo workspace, but do not remove a walk
    // merely because its date passed while its operational status is still Open.
    // Closed/Completed past walks remain outside this active command center.
    if (pyrk_column_exists($pdo, 'walks', 'walk_date')) {
        $parts[] = '(' . $a . '.walk_date >= CURDATE() OR LOWER(TRIM(COALESCE(' . $a . '.status, ""))) = "open")';
    }

    $legacySeed = [];
    if (pyrk_column_exists($pdo, 'walks', 'checkin_token')) {
        $legacySeed[] = 'LOWER(COALESCE(' . $a . '.checkin_token, "")) LIKE "pyrk-walk-sunrise-%"';
        $legacySeed[] = 'LOWER(COALESCE(' . $a . '.checkin_token, "")) LIKE "pyrk-walk-park-%"';
    }
    $legacySeed[] = 'LOWER(COALESCE(' . $a . '.title, "")) LIKE "%sunrise waterfront%"';
    $legacySeed[] = 'LOWER(COALESCE(' . $a . '.title, "")) LIKE "%community park%"';
    $parts[] = 'NOT (' . implode(' OR ', $legacySeed) . ')';
    $parts[] = pyrk_walk_access_scope_sql($pdo, $a, Auth::user());

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

function pyrk_walk_is_visible(PDO $pdo, int $walkId): bool
{
    if ($walkId <= 0) return false;
    $sql = 'SELECT COUNT(*) FROM walks w WHERE w.id = ? AND ' . pyrk_visible_walk_sql($pdo, 'w');
    $stmt = $pdo->prepare($sql);
    $stmt->execute([$walkId]);
    return (int)$stmt->fetchColumn() > 0;
}


function pyrk_group_list(PDO $pdo): array
{
    if (!pyrk_table_exists($pdo, 'member_groups')) return [];
    $stmt = $pdo->query('SELECT g.id, g.group_name, g.description, g.status, (SELECT COUNT(*) FROM member_group_members mgm WHERE mgm.group_id = g.id) AS member_count FROM member_groups g ORDER BY g.status = "active" DESC, g.group_name ASC');
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function pyrk_walk_list(PDO $pdo): array
{
    $hasContests = pyrk_table_exists($pdo, 'photo_contests');
    $sql = 'SELECT w.id, w.title, w.walk_date, w.start_time, w.status, w.voting_status AS legacy_voting_status, w.voting_deadline AS legacy_voting_deadline,
                   r.route_name,
                   (SELECT COUNT(*) FROM walk_photos p WHERE p.walk_id = w.id) AS photo_count,
                   (SELECT COUNT(*) FROM walk_photos p WHERE p.walk_id = w.id AND p.status = "pending") AS pending_count,
                   (SELECT COALESCE(SUM(p.votes_count),0) FROM walk_photos p WHERE p.walk_id = w.id) AS vote_count';
    if ($hasContests) {
        $photoLimitExpr = pyrk_column_exists($pdo, 'photo_contests', 'max_photos_per_participant') ? 'pc.max_photos_per_participant' : '1';
        $sql .= ', pc.voting_status AS contest_voting_status, pc.voting_permission, pc.vote_mode, pc.allow_self_vote, pc.voting_deadline AS contest_deadline, ' . $photoLimitExpr . ' AS max_photos_per_participant';
    } else {
        $sql .= ', NULL AS contest_voting_status, NULL AS voting_permission, NULL AS vote_mode, NULL AS allow_self_vote, NULL AS contest_deadline, 1 AS max_photos_per_participant';
    }
    $sql .= ' FROM walks w JOIN routes r ON r.id = w.route_id';
    if ($hasContests) $sql .= ' LEFT JOIN photo_contests pc ON pc.walk_id = w.id';
    $sql .= ' WHERE ' . pyrk_visible_walk_sql($pdo, 'w');
    $sql .= ' ORDER BY w.walk_date DESC, w.start_time DESC LIMIT 300';
    return $pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);
}

if ($_SERVER['REQUEST_METHOD'] === 'GET') {
    if ($action === 'meta') {
        $walkId = (int)($input['walk_id'] ?? 0);
        $settings = ($walkId > 0 && pyrk_walk_is_visible($pdo, $walkId)) ? $svc->contestSettings($walkId) : null;
        Response::ok([
            'walks' => pyrk_walk_list($pdo),
            'groups' => pyrk_group_list($pdo),
            'settings' => $settings,
            'tables_ready' => pyrk_table_exists($pdo, 'photo_contests'),
        ]);
    }

    if ($action === 'settings') {
        $walkId = (int)($input['walk_id'] ?? 0);
        if ($walkId <= 0) Response::error('walk_id is required.');
        if (!pyrk_walk_is_visible($pdo, $walkId)) Response::error('This walk is deleted or archived and cannot be used for photo contest settings.', 404);
        Response::ok(['settings' => $svc->contestSettings($walkId)]);
    }

    $where = pyrk_visible_walk_sql($pdo, 'w');
    $params = [];

    if (!empty($input['walk_id'])) {
        $requestedWalkId = (int)$input['walk_id'];
        if (!pyrk_walk_is_visible($pdo, $requestedWalkId)) {
            Response::ok([
                'photos' => [],
                'stats' => [
                    'total' => 0,
                    'pending' => 0,
                    'approved' => 0,
                    'rejected' => 0,
                    'winner' => 0,
                    'total_votes' => 0,
                    'walk_count' => 0,
                    'participant_count' => 0,
                ],
            ]);
        }
        $where .= ' AND p.walk_id = ?';
        $params[] = $requestedWalkId;
    }

    if (!empty($input['group_id']) && pyrk_table_exists($pdo, 'photo_contests') && pyrk_table_exists($pdo, 'photo_contest_groups')) {
        $where .= ' AND EXISTS (SELECT 1 FROM photo_contests pcx JOIN photo_contest_groups pcgx ON pcgx.contest_id = pcx.id WHERE pcx.walk_id = p.walk_id AND pcgx.group_id = ?)';
        $params[] = (int)$input['group_id'];
    }

    if (!empty($input['status'])) {
        $where .= ' AND p.status = ?';
        $params[] = (string)$input['status'];
    }

    if (!empty($input['contest_status'])) {
        if (pyrk_table_exists($pdo, 'photo_contests')) {
            $where .= ' AND COALESCE(pc.voting_status, w.voting_status) = ?';
        } else {
            $where .= ' AND w.voting_status = ?';
        }
        $params[] = (string)$input['contest_status'];
    }

    if (!empty($input['permission']) && pyrk_table_exists($pdo, 'photo_contests')) {
        $where .= ' AND pc.voting_permission = ?';
        $params[] = (string)$input['permission'];
    }

    if (!empty($input['q'])) {
        $where .= ' AND (p.title LIKE ? OR p.caption LIKE ? OR u.full_name LIKE ? OR u.email LIKE ? OR u.mobile LIKE ? OR w.title LIKE ? OR r.route_name LIKE ?)';
        $q = '%' . trim((string)$input['q']) . '%';
        array_push($params, $q, $q, $q, $q, $q, $q, $q);
    }

    $sort = (string)($input['sort'] ?? 'newest');
    $orderBy = 'p.uploaded_at DESC';
    if ($sort === 'votes_desc') {
        $orderBy = 'p.votes_count DESC, p.uploaded_at DESC';
    } elseif ($sort === 'pending_first') {
        $orderBy = "CASE p.status WHEN 'pending' THEN 0 WHEN 'approved' THEN 1 WHEN 'winner' THEN 2 WHEN 'rejected' THEN 3 ELSE 4 END, p.uploaded_at DESC";
    } elseif ($sort === 'member_az') {
        $orderBy = 'u.full_name ASC, p.uploaded_at DESC';
    } elseif ($sort === 'walk_date') {
        $orderBy = 'w.walk_date DESC, w.start_time DESC, p.votes_count DESC';
    }

    $hasContests = pyrk_table_exists($pdo, 'photo_contests');
    $joinContest = $hasContests ? 'LEFT JOIN photo_contests pc ON pc.walk_id = p.walk_id' : '';
    $photoLimitExpr = ($hasContests && pyrk_column_exists($pdo, 'photo_contests', 'max_photos_per_participant')) ? 'pc.max_photos_per_participant' : '1';
    $contestFields = $hasContests
        ? 'pc.voting_status AS contest_voting_status, pc.voting_permission, pc.vote_mode, pc.allow_self_vote, pc.voting_deadline AS contest_deadline, ' . $photoLimitExpr . ' AS max_photos_per_participant, (SELECT GROUP_CONCAT(g.group_name ORDER BY g.group_name SEPARATOR ", ") FROM photo_contest_groups pcg JOIN member_groups g ON g.id = pcg.group_id WHERE pcg.contest_id = pc.id) AS contest_groups'
        : 'w.voting_status AS contest_voting_status, "walk_registered" AS voting_permission, "one_per_contest" AS vote_mode, 1 AS allow_self_vote, w.voting_deadline AS contest_deadline, 1 AS max_photos_per_participant, NULL AS contest_groups';
    $photoLocationFields = implode(', ', array_map(
        static fn($col) => pyrk_column_exists($pdo, 'walk_photos', $col) ? ('p.' . $col) : ('NULL AS ' . $col),
        ['photo_latitude', 'photo_longitude', 'photo_location_accuracy', 'photo_location_source', 'photo_location_captured_at']
    ));

    $sql = "SELECT
                p.id, p.walk_id, p.user_id, p.title, p.caption, p.file_path, p.thumbnail_path,
                p.status, p.votes_count, p.uploaded_at, p.approved_at,
                {$photoLocationFields},
                u.full_name AS uploaded_by, u.email AS uploaded_email, u.mobile AS uploaded_mobile,
                u.profile_photo_path AS uploaded_avatar,
                w.title AS walk_title, w.walk_date, w.start_time, w.voting_status,
                r.route_name,
                {$contestFields}
            FROM walk_photos p
            JOIN users u ON u.id = p.user_id
            JOIN walks w ON w.id = p.walk_id
            JOIN routes r ON r.id = w.route_id
            {$joinContest}
            WHERE {$where}
            ORDER BY {$orderBy}";
    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    $photos = $stmt->fetchAll(PDO::FETCH_ASSOC);

    $statsStmt = $pdo->prepare("SELECT
            COUNT(*) AS total,
            SUM(CASE WHEN p.status = 'pending' THEN 1 ELSE 0 END) AS pending,
            SUM(CASE WHEN p.status = 'approved' THEN 1 ELSE 0 END) AS approved,
            SUM(CASE WHEN p.status = 'rejected' THEN 1 ELSE 0 END) AS rejected,
            SUM(CASE WHEN p.status = 'winner' THEN 1 ELSE 0 END) AS winner,
            COALESCE(SUM(p.votes_count),0) AS total_votes,
            COUNT(DISTINCT p.walk_id) AS walk_count,
            COUNT(DISTINCT p.user_id) AS participant_count
        FROM walk_photos p
        JOIN users u ON u.id = p.user_id
        JOIN walks w ON w.id = p.walk_id
        JOIN routes r ON r.id = w.route_id
        {$joinContest}
        WHERE {$where}");
    $statsStmt->execute($params);
    $stats = $statsStmt->fetch(PDO::FETCH_ASSOC) ?: [];

    Response::ok([
        'photos' => $photos,
        'stats' => [
            'total' => (int)($stats['total'] ?? 0),
            'pending' => (int)($stats['pending'] ?? 0),
            'approved' => (int)($stats['approved'] ?? 0),
            'rejected' => (int)($stats['rejected'] ?? 0),
            'winner' => (int)($stats['winner'] ?? 0),
            'total_votes' => (int)($stats['total_votes'] ?? 0),
            'walk_count' => (int)($stats['walk_count'] ?? 0),
            'participant_count' => (int)($stats['participant_count'] ?? 0),
        ],
    ]);
}

if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    Csrf::validateFromRequest();
    try {
        if ($action === 'approve' || $action === 'reject') {
            $photoId = (int)($input['photo_id'] ?? 0);
            if ($photoId <= 0) Response::error('Missing photo ID.');
            $photoWalkStmt = $pdo->prepare('SELECT walk_id FROM walk_photos WHERE id = ? LIMIT 1');
            $photoWalkStmt->execute([$photoId]);
            $photoWalkId = (int)($photoWalkStmt->fetchColumn() ?: 0);
            pyrk_walk_access_require($pdo, $photoWalkId, $user, 'You are not assigned to this walk photo contest.');
            $svc->setStatus($photoId, $action === 'approve' ? 'approved' : 'rejected', (int)$user['id']);
            Response::ok([], 'Photo ' . ($action === 'approve' ? 'approved' : 'rejected') . '.');
        }

        if ($action === 'delete') {
            $photoId = (int)($input['photo_id'] ?? 0);
            if ($photoId <= 0) Response::error('Missing photo ID.');
            $photoWalkStmt = $pdo->prepare('SELECT walk_id FROM walk_photos WHERE id = ? LIMIT 1');
            $photoWalkStmt->execute([$photoId]);
            $photoWalkId = (int)($photoWalkStmt->fetchColumn() ?: 0);
            pyrk_walk_access_require($pdo, $photoWalkId, $user, 'You are not assigned to this walk photo contest.');
            $deleted = $svc->deletePhoto($photoId);
            Response::ok(['deleted_photo' => $deleted], 'Photo deleted successfully.');
        }

        if ($action === 'save_settings') {
            $walkId = (int)($input['walk_id'] ?? 0);
            if ($walkId <= 0) Response::error('Choose a walk before saving voting rules.');
            if (!pyrk_walk_is_visible($pdo, $walkId)) Response::error('This walk is deleted or archived and cannot be used for photo contest settings.', 404);
            $settings = $svc->saveContestSettings($walkId, $input, (int)$user['id']);
            Response::ok(['settings' => $settings], 'Photo contest voting rules saved.');
        }

        if ($action === 'close_contest') {
            $walkId = (int)($input['walk_id'] ?? 0);
            if ($walkId <= 0) Response::error('Choose a walk before closing the contest.');
            if (!pyrk_walk_is_visible($pdo, $walkId)) Response::error('This walk is deleted or archived and cannot be used for photo contest settings.', 404);
            $winner = $svc->closeContest($walkId);
            Response::ok(['winner' => $winner, 'settings' => $svc->contestSettings($walkId)], $winner ? 'Contest closed and winner selected.' : 'Contest closed. No approved photos were available.');
        }

        if ($action === 'reopen_contest') {
            $walkId = (int)($input['walk_id'] ?? 0);
            if ($walkId <= 0) Response::error('Choose a walk before reopening the contest.');
            if (!pyrk_walk_is_visible($pdo, $walkId)) Response::error('This walk is deleted or archived and cannot be used for photo contest settings.', 404);
            $svc->reopenContest($walkId);
            Response::ok(['settings' => $svc->contestSettings($walkId)], 'Contest reopened.');
        }
    } catch (Throwable $e) {
        Response::error($e->getMessage());
    }
}

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