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

use App\Core\Auth;
use App\Core\Csrf;
use App\Core\Database;
use App\Core\Response;
use App\Core\Security;
use App\Services\GoogleRoutesService;
use App\Services\OpenStreetMapRoutesService;

use App\Services\NotificationService;
$pdo = Database::connection();
$input = request_input();
$method = $_SERVER['REQUEST_METHOD'];
$action = $input['action'] ?? '';

function pyrk_save_route_points(PDO $pdo, int $routeId, array $points): void
{
    $pdo->prepare('DELETE FROM route_points WHERE route_id = ?')->execute([$routeId]);

    $hasRadius = pyrk_routes_column_exists($pdo, 'route_points', 'checkin_radius_meters');
    $hasNavigationNote = pyrk_routes_column_exists($pdo, 'route_points', 'navigation_note');
    $hasNavigationStop = pyrk_routes_column_exists($pdo, 'route_points', 'is_navigation_stop');

    $columns = ['route_id', 'point_type', 'title', 'description', 'latitude', 'longitude', 'sort_order', 'is_required_checkpoint'];
    if ($hasRadius) $columns[] = 'checkin_radius_meters';
    if ($hasNavigationNote) $columns[] = 'navigation_note';
    if ($hasNavigationStop) $columns[] = 'is_navigation_stop';
    $columns[] = 'created_at';
    $columns[] = 'updated_at';

    $placeholders = array_fill(0, count($columns), '?');
    $insertPoint = $pdo->prepare('INSERT INTO route_points (' . implode(', ', $columns) . ') VALUES (' . implode(', ', $placeholders) . ')');

    $sort = 1;
    foreach (array_values($points) as $point) {
        if (empty($point['title'])) continue;

        $pointType = $point['point_type'] ?? 'checkpoint';
        $radius = isset($point['checkin_radius_meters']) && (int)$point['checkin_radius_meters'] > 0 ? min(500, max(10, (int)$point['checkin_radius_meters'])) : 60;
        $canRequireCheckin = in_array($pointType, ['checkpoint', 'station', 'water_station', 'medical_station', 'rest_station'], true);
        $isNavigationStop = array_key_exists('is_navigation_stop', $point) ? (!empty($point['is_navigation_stop']) ? 1 : 0) : (in_array($pointType, ['start','checkpoint','end'], true) ? 1 : 0);

        // The database keeps the official point number in sort_order.
        // We intentionally save it from the array order so the map, JSON, and table stay consistent.
        $values = [
            $routeId,
            $pointType,
            trim((string)$point['title']),
            trim((string)($point['description'] ?? '')),
            isset($point['latitude']) && $point['latitude'] !== '' ? (float)$point['latitude'] : null,
            isset($point['longitude']) && $point['longitude'] !== '' ? (float)$point['longitude'] : null,
            $sort++,
            $canRequireCheckin && !empty($point['is_required_checkpoint']) ? 1 : 0
        ];

        if ($hasRadius) $values[] = $radius;
        if ($hasNavigationNote) $values[] = trim((string)($point['navigation_note'] ?? ''));
        if ($hasNavigationStop) $values[] = $isNavigationStop;
        $values[] = date('Y-m-d H:i:s');
        $values[] = date('Y-m-d H:i:s');

        $insertPoint->execute($values);
    }
}

function pyrk_validate_route_input(array $input, array $points): void
{
    foreach (['route_name','start_address','end_address','total_km'] as $field) {
        if (($input[$field] ?? '') === '') Response::error("Missing field: {$field}");
    }
    if (count($points) < 2) Response::error('Add at least a start and end route point.');
}


function pyrk_routes_table_exists(PDO $pdo, string $table): bool
{
    static $cache = [];
    if (array_key_exists($table, $cache)) return $cache[$table];

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

function pyrk_routes_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_routes_active_walk_where(PDO $pdo, string $alias = 'w'): string
{
    $a = preg_replace('/[^A-Za-z0-9_]/', '', $alias) ?: 'w';

    // These statuses mean the walk is no longer operational and must not block route deletion.
    $parts = [
        'LOWER(TRIM(COALESCE(' . $a . '.status, ""))) NOT IN ("deleted","archived","removed","remove","trash","trashed","cancelled","canceled","inactive","hidden")'
    ];

    if (pyrk_routes_column_exists($pdo, 'walks', 'deleted_at')) {
        $parts[] = $a . '.deleted_at IS NULL';
    }

    // Some upgraded installations used audit logs to hide deleted walks before hard-delete was added.
    if (pyrk_routes_table_exists($pdo, 'audit_logs')) {
        $parts[] = 'NOT EXISTS (
            SELECT 1
            FROM audit_logs al_route_delete
            WHERE al_route_delete.entity_type = "walks"
              AND al_route_delete.entity_id = ' . $a . '.id
              AND al_route_delete.action IN ("walk_deleted","walk_removed","walk_archived","walk_cancelled","walk_canceled","walk_hidden")
        )';
    }

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

function pyrk_routes_deleted_walk_where(PDO $pdo, string $alias = 'w'): string
{
    $a = preg_replace('/[^A-Za-z0-9_]/', '', $alias) ?: 'w';

    $parts = [
        'LOWER(TRIM(COALESCE(' . $a . '.status, ""))) IN ("deleted","archived","removed","remove","trash","trashed","cancelled","canceled","inactive","hidden")'
    ];

    if (pyrk_routes_column_exists($pdo, 'walks', 'deleted_at')) {
        $parts[] = $a . '.deleted_at IS NOT NULL';
    }

    if (pyrk_routes_table_exists($pdo, 'audit_logs')) {
        $parts[] = 'EXISTS (
            SELECT 1
            FROM audit_logs al_route_deleted
            WHERE al_route_deleted.entity_type = "walks"
              AND al_route_deleted.entity_id = ' . $a . '.id
              AND al_route_deleted.action IN ("walk_deleted","walk_removed","walk_archived","walk_cancelled","walk_canceled","walk_hidden")
        )';
    }

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

function pyrk_routes_walk_count_select(PDO $pdo, string $routeRef = 'r.id'): string
{
    return '(SELECT COUNT(*) FROM walks w WHERE w.route_id = ' . $routeRef . ' AND ' . pyrk_routes_active_walk_where($pdo, 'w') . ') AS walks_count,
        (SELECT COUNT(*) FROM walks w_del WHERE w_del.route_id = ' . $routeRef . ' AND ' . pyrk_routes_deleted_walk_where($pdo, 'w_del') . ') AS deleted_walks_count,
        (SELECT COUNT(*) FROM walks w_all WHERE w_all.route_id = ' . $routeRef . ') AS total_walks_count';
}


function pyrk_number_route_points_for_response(array $points): array
{
    $checkpointNumber = 1;
    foreach ($points as $index => &$point) {
        $point['sort_order'] = (int)($point['sort_order'] ?? ($index + 1));
        if (($point['point_type'] ?? '') === 'checkpoint') {
            $point['checkpoint_number'] = $checkpointNumber++;
        }
    }
    unset($point);
    return $points;
}


function pyrk_routes_unique_name(PDO $pdo, string $requestedName): string
{
    $base = trim(preg_replace('/\s+/', ' ', $requestedName));
    if ($base === '') $base = 'Route copy';

    // Keep names within a conservative length even on older installations.
    $base = function_exists('mb_substr') ? mb_substr($base, 0, 180) : substr($base, 0, 180);
    $candidate = $base;
    $suffix = 2;
    $exists = $pdo->prepare('SELECT COUNT(*) FROM routes WHERE LOWER(TRIM(route_name)) = LOWER(TRIM(?))');

    while (true) {
        $exists->execute([$candidate]);
        if ((int)$exists->fetchColumn() === 0) return $candidate;

        $suffixText = ' (' . $suffix++ . ')';
        $maxBaseLength = max(1, 180 - strlen($suffixText));
        $trimmedBase = function_exists('mb_substr') ? mb_substr($base, 0, $maxBaseLength) : substr($base, 0, $maxBaseLength);
        $candidate = rtrim($trimmedBase) . $suffixText;
    }
}

if ($method === 'GET') {
    $viewer = Auth::requireUser();
    if (!Auth::hasPermission('routes.manage', $viewer) && !Auth::hasPermission('routes.delete', $viewer)) Response::error('You do not have permission to view routes.', 403);
    if (!empty($input['id'])) {
        $stmt = $pdo->prepare('SELECT r.*, u.full_name AS created_by_name, ' . pyrk_routes_walk_count_select($pdo, 'r.id') . ' FROM routes r LEFT JOIN users u ON u.id = r.created_by WHERE r.id = ? LIMIT 1');
        $stmt->execute([(int)$input['id']]);
        $route = $stmt->fetch();
        $points = $pdo->prepare('SELECT * FROM route_points WHERE route_id = ? ORDER BY sort_order, id');
        $points->execute([(int)$input['id']]);
        Response::ok(['route' => $route, 'points' => pyrk_number_route_points_for_response($points->fetchAll())]);
    }
    $routes = $pdo->query('SELECT r.*, u.full_name AS created_by_name, (SELECT COUNT(*) FROM route_points rp WHERE rp.route_id = r.id) AS points_count, ' . pyrk_routes_walk_count_select($pdo, 'r.id') . ' FROM routes r LEFT JOIN users u ON u.id = r.created_by ORDER BY r.created_at DESC, r.id DESC')->fetchAll();
    Response::ok(['routes' => $routes, 'google_maps_key_set' => !empty($GLOBALS['config']['google']['maps_browser_key'])]);
}

if ($method === 'POST') {
    $user = Auth::requireUser();
    if (!Auth::hasPermission('routes.manage', $user) && !Auth::hasPermission('routes.delete', $user)) Response::error('You do not have permission to manage routes.', 403);
    Csrf::validateFromRequest();

    if ($action === 'duplicate') {
        if (!Auth::hasPermission('routes.manage', $user)) Response::error('You do not have permission to duplicate routes.', 403);

        $sourceRouteId = (int)($input['id'] ?? 0);
        if (!$sourceRouteId) Response::error('Missing source route id.');

        $sourceStmt = $pdo->prepare('SELECT * FROM routes WHERE id = ? LIMIT 1');
        $sourceStmt->execute([$sourceRouteId]);
        $sourceRoute = $sourceStmt->fetch(PDO::FETCH_ASSOC);
        if (!$sourceRoute) Response::error('Source route not found.', 404);

        $requestedName = trim((string)($input['route_name'] ?? ''));
        if ($requestedName === '') {
            $requestedName = trim((string)($sourceRoute['route_name'] ?? 'Route')) . ' - Copy';
        }
        $duplicateName = pyrk_routes_unique_name($pdo, $requestedName);

        $pointsStmt = $pdo->prepare('SELECT * FROM route_points WHERE route_id = ? ORDER BY sort_order, id');
        $pointsStmt->execute([$sourceRouteId]);
        $sourcePoints = $pointsStmt->fetchAll(PDO::FETCH_ASSOC);

        try {
            $pdo->beginTransaction();
            $insert = $pdo->prepare('INSERT INTO routes (route_name, description, start_address, end_address, total_km, expected_duration_minutes, difficulty, google_polyline, map_bounds_json, status, created_by, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, "inactive", ?, NOW(), NOW())');
            $insert->execute([
                $duplicateName,
                (string)($sourceRoute['description'] ?? ''),
                (string)($sourceRoute['start_address'] ?? ''),
                (string)($sourceRoute['end_address'] ?? ''),
                (float)($sourceRoute['total_km'] ?? 0),
                (int)($sourceRoute['expected_duration_minutes'] ?? 60),
                (string)($sourceRoute['difficulty'] ?? 'easy'),
                (string)($sourceRoute['google_polyline'] ?? ''),
                $sourceRoute['map_bounds_json'] ?? null,
                (int)$user['id'],
            ]);

            $duplicateRouteId = (int)$pdo->lastInsertId();
            pyrk_save_route_points($pdo, $duplicateRouteId, $sourcePoints);

            Security::audit(
                (int)$user['id'],
                'route_duplicated',
                'routes',
                $duplicateRouteId,
                [
                    'source_route_id' => $sourceRouteId,
                    'source_route_name' => $sourceRoute['route_name'] ?? null,
                    'route_name' => $duplicateName,
                    'status' => 'inactive',
                    'points_count' => count($sourcePoints),
                ]
            );

            $pdo->commit();
            Response::ok([
                'route_id' => $duplicateRouteId,
                'source_route_id' => $sourceRouteId,
                'route_name' => $duplicateName,
                'status' => 'inactive',
                'points_count' => count($sourcePoints),
            ], 'Route duplicated safely as “' . $duplicateName . '”. Review it, then change its status to Active when ready.');
        } catch (Throwable $e) {
            if ($pdo->inTransaction()) $pdo->rollBack();
            Response::error('Could not duplicate route: ' . $e->getMessage());
        }
    }

    if ($action === 'compute_route') {
        if (!Auth::hasPermission('routes.manage', $user)) Response::error('You do not have permission to calculate routes.', 403);
        $points = json_decode((string)($input['points_json'] ?? '[]'), true);
        if (!is_array($points)) Response::error('Invalid points_json.');
        try {
            $provider = $GLOBALS['config']['maps']['provider'] ?? 'google';
            if ($provider === 'osm') {
                $result = (new OpenStreetMapRoutesService())->computeWalkingRoute($points);
            } else {
                $result = (new GoogleRoutesService())->computeWalkingRoute($points);
            }
            Response::ok(['route_calc' => $result], $result['message'] ?? 'Route calculated.');
        } catch (Throwable $e) {
            Response::error($e->getMessage());
        }
    }

    if ($action === 'archive') {
        if (!Auth::hasPermission('routes.delete', $user)) Response::error('You do not have permission to archive routes.', 403);
        $id = (int)($input['id'] ?? 0);
        if (!$id) Response::error('Missing route id.');
        $pdo->prepare('UPDATE routes SET status = "archived", updated_at = NOW() WHERE id = ?')->execute([$id]);
        Security::audit((int)$user['id'], 'route_archived', 'routes', $id, []);
        Response::ok([], 'Route archived.');
    }

    if ($action === 'delete') {
        if (!Auth::hasPermission('routes.delete', $user)) Response::error('You do not have permission to delete routes.', 403);
        $id = (int)($input['id'] ?? 0);
        if (!$id) Response::error('Missing route id.');
        // Only active / non-deleted walks block route deletion.
        // Deleted/cancelled/audit-deleted walks remain historical records and should not stop route cleanup.
        $countStmt = $pdo->prepare('SELECT COUNT(*) FROM walks w WHERE w.route_id = ? AND ' . pyrk_routes_active_walk_where($pdo, 'w'));
        $countStmt->execute([$id]);
        $activeWalksCount = (int)$countStmt->fetchColumn();

        if ($activeWalksCount > 0) {
            $namesStmt = $pdo->prepare('SELECT title, walk_date, status FROM walks w WHERE w.route_id = ? AND ' . pyrk_routes_active_walk_where($pdo, 'w') . ' ORDER BY walk_date DESC, id DESC LIMIT 5');
            $namesStmt->execute([$id]);
            $names = array_map(static function ($row) {
                $date = !empty($row['walk_date']) ? ' (' . $row['walk_date'] . ')' : '';
                $status = !empty($row['status']) ? ' [' . $row['status'] . ']' : '';
                return (string)($row['title'] ?? 'Walk') . $date . $status;
            }, $namesStmt->fetchAll(PDO::FETCH_ASSOC));

            Response::error('This route is still linked to ' . $activeWalksCount . ' active/non-deleted walk(s): ' . implode(', ', $names) . '. Delete/cancel those walks first, or archive the route instead.');
        }
        $existing = $pdo->prepare('SELECT * FROM routes WHERE id = ? LIMIT 1');
        $existing->execute([$id]);
        $before = $existing->fetch();
        if (!$before) Response::error('Route not found.', 404);
        try {
            $pdo->beginTransaction();
            $pdo->prepare('DELETE FROM route_points WHERE route_id = ?')->execute([$id]);
            $pdo->prepare('DELETE FROM routes WHERE id = ?')->execute([$id]);
            Security::audit((int)$user['id'], 'route_deleted', 'routes', $id, ['route_name' => $before['route_name'] ?? null], $before);
            $pdo->commit();
            Response::ok([], 'Route deleted.');
        } catch (Throwable $e) {
            if ($pdo->inTransaction()) $pdo->rollBack();
            Response::error('Could not delete route: ' . $e->getMessage());
        }
    }


    $points = json_decode((string)($input['points_json'] ?? '[]'), true);
    if (!is_array($points)) $points = [];
    pyrk_validate_route_input($input, $points);

    if ($action === 'update') {
        if (!Auth::hasPermission('routes.manage', $user)) Response::error('You do not have permission to update routes.', 403);
        $routeId = (int)($input['id'] ?? 0);
        if (!$routeId) Response::error('Missing route id.');
        $pdo->beginTransaction();
        try {
            $stmt = $pdo->prepare('UPDATE routes SET route_name = ?, description = ?, start_address = ?, end_address = ?, total_km = ?, expected_duration_minutes = ?, difficulty = ?, google_polyline = ?, map_bounds_json = ?, status = ?, updated_at = NOW() WHERE id = ?');
            $stmt->execute([
                trim((string)$input['route_name']),
                trim((string)($input['description'] ?? '')),
                trim((string)$input['start_address']),
                trim((string)$input['end_address']),
                (float)$input['total_km'],
                (int)($input['expected_duration_minutes'] ?? 60),
                $input['difficulty'] ?? 'easy',
                $input['google_polyline'] ?? '',
                $input['map_bounds_json'] ?? null,
                $input['status'] ?? 'active',
                $routeId,
            ]);
            pyrk_save_route_points($pdo, $routeId, $points);
            $pdo->commit();
            Security::audit((int)$user['id'], 'route_updated', 'routes', $routeId, ['route_name' => $input['route_name']]);
            Response::ok(['route_id' => $routeId], 'Route updated.');
        } catch (Throwable $e) {
            $pdo->rollBack();
            Response::error('Could not update route: ' . $e->getMessage());
        }
    }

    if (!Auth::hasPermission('routes.manage', $user)) Response::error('You do not have permission to create routes.', 403);

    $pdo->beginTransaction();
    try {
        $stmt = $pdo->prepare('INSERT INTO routes (route_name, description, start_address, end_address, total_km, expected_duration_minutes, difficulty, google_polyline, map_bounds_json, status, created_by, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW())');
        $stmt->execute([
            trim((string)$input['route_name']),
            trim((string)($input['description'] ?? '')),
            trim((string)$input['start_address']),
            trim((string)$input['end_address']),
            (float)$input['total_km'],
            (int)($input['expected_duration_minutes'] ?? 60),
            $input['difficulty'] ?? 'easy',
            $input['google_polyline'] ?? '',
            $input['map_bounds_json'] ?? null,
            $input['status'] ?? 'active',
            (int)$user['id']
        ]);
        $routeId = (int)$pdo->lastInsertId();
        pyrk_save_route_points($pdo, $routeId, $points);
        $pdo->commit();
        Security::audit((int)$user['id'], 'route_created', 'routes', $routeId, ['route_name' => $input['route_name']]);
        (new NotificationService())->notifyRouteCreated($routeId, (int)$user['id']);
        Response::ok(['route_id' => $routeId], 'Route saved.');
    } catch (Throwable $e) {
        $pdo->rollBack();
        Response::error('Could not save route: ' . $e->getMessage());
    }
}

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