src/models/TrackModel.ts

import sql from '../db';
import { Track } from '../types/types';

/**
 * TrackModel - Handles database operations for public ski tracks
 * These are read-only tracks imported from OpenSkiData
 */
export class TrackModel {
    private static buildWhere(conditions: any[]): any {
        if (!conditions.length) return sql``;

        let where = sql`WHERE ${conditions[0]}`;
        for (let i = 1; i < conditions.length; i++) {
            where = sql`${where} AND ${conditions[i]}`;
        }
        return where;
    }

    private static buildFilterConditions(filters: {
        activities?: string[];
        searchQuery?: string;
        difficulty?: string;
    }): any[] {
        const { activities, searchQuery, difficulty } = filters;

        const conditions: any[] = [];

        if (activities && activities.length > 0) {
            console.log(`Filtering by activities: ${activities.join(', ')}`);
            conditions.push(sql`uses && ${sql.array(activities, 25)}`);
        }

        if (searchQuery) {
            const pattern = `%${searchQuery}%`;
            conditions.push(sql`(name ILIKE ${pattern} OR description ILIKE ${pattern})`);
        }

        if (difficulty) {
            conditions.push(sql`difficulty = ${difficulty}`);
        }

        return conditions;
    }

    private static buildLocationConditions(location?: {
        lat: number;
        lng: number;
        radiusKm: number;
    }): any[] {
        if (!location) return [];
        if (!Number.isFinite(location.lat) || !Number.isFinite(location.lng) || !Number.isFinite(location.radiusKm)) return [];

        const conditions: any[] = [];
        const radiusKm = Math.max(0, location.radiusKm);
        const lat = location.lat;
        const lng = location.lng;

        // Cheap pre-filter: bounding box (helps avoid doing trig on every row)
        const deltaLat = radiusKm / 111.0;
        const cosLat = Math.cos((lat * Math.PI) / 180);
        const deltaLng = cosLat === 0 ? 180 : radiusKm / (111.0 * Math.max(0.000001, cosLat));

        const minLat = lat - deltaLat;
        const maxLat = lat + deltaLat;
        const minLng = lng - deltaLng;
        const maxLng = lng + deltaLng;

        // JSONB GeoJSON: first coordinate is [lng, lat]
        conditions.push(sql`((path->'coordinates'->0->>1)::float8 BETWEEN ${minLat} AND ${maxLat})`);
        conditions.push(sql`((path->'coordinates'->0->>0)::float8 BETWEEN ${minLng} AND ${maxLng})`);

        // Exact circle filter using the first coordinate
        conditions.push(sql`
            (
                6371 * 2 * asin(
                    sqrt(
                        power(sin(radians((${lat} - ((path->'coordinates'->0->>1)::float8))) / 2)), 2)
                        + cos(radians(${lat}))
                          * cos(radians(((path->'coordinates'->0->>1)::float8)))
                          * power(sin(radians((${lng} - ((path->'coordinates'->0->>0)::float8))) / 2)), 2)
                    )
                )
            ) <= ${radiusKm}
        `);

        return conditions;
    }

    /**
     * Find all tracks in the database
     */
    static async findAll(): Promise<Track[]> {
        const tracks = await sql<Track[]>`
            SELECT 
                id, 
                external_id,
                name, 
                description, 
                path as geojson, 
                properties, 
                difficulty, 
                difficulty_convention,
                type,
                uses,
                ref,
                oneway,
                gladed,
                patrolled,
                lit,
                grooming,
                status,
                websites,
                wikidata_id,
                elevation_profile,
                created_at
            FROM routes
            ORDER BY created_at DESC
        `;
        return tracks;
    }

    /**
     * Find tracks with filters
     * Optimized to filter by activities and search query in the database
     */
    static async findWithFilters(
        filters: {
            activities?: string[];
            searchQuery?: string;
        }
    ): Promise<Track[]> {
        const conditions = TrackModel.buildFilterConditions(filters);
        const where = TrackModel.buildWhere(conditions);

        const tracks = await sql<Track[]>`
            SELECT 
                id, 
                external_id,
                name, 
                description, 
                path as geojson, 
                properties, 
                difficulty, 
                difficulty_convention,
                type,
                uses,
                ref,
                oneway,
                gladed,
                patrolled,
                lit,
                grooming,
                status,
                websites,
                wikidata_id,
                elevation_profile,
                created_at
            FROM routes
            ${where}
            ORDER BY created_at DESC
        `;
        return tracks;
    }

    /**
     * Find tracks with filters and DB-level pagination.
     *
     * IMPORTANT: Pagination must happen in SQL to avoid downloading the full
     * table from Supabase (which counts toward Supabase egress).
     */
    static async findWithFiltersPaginated(
        filters: {
            activities?: string[];
            searchQuery?: string;
            difficulty?: string;
        },
        pagination: {
            limit: number;
            offset: number;
        },
        location?: {
            lat: number;
            lng: number;
            radiusKm: number;
        }
    ): Promise<{ tracks: Track[]; total: number }> {
        const { limit, offset } = pagination;
        const conditions = [
            ...TrackModel.buildFilterConditions(filters),
            ...TrackModel.buildLocationConditions(location),
        ];

        const where = TrackModel.buildWhere(conditions);

        type TrackRow = Track & { total_count: number };

        const rows = await sql<TrackRow[]>`
            SELECT 
                id, 
                external_id,
                name, 
                description, 
                path as geojson, 
                properties, 
                difficulty, 
                difficulty_convention,
                type,
                uses,
                ref,
                oneway,
                gladed,
                patrolled,
                lit,
                grooming,
                status,
                websites,
                wikidata_id,
                elevation_profile,
                created_at,
                COUNT(*) OVER()::int as total_count
            FROM routes
            ${where}
            ORDER BY created_at DESC
            LIMIT ${limit}
            OFFSET ${offset}
        `;

        let total = rows.length > 0 ? rows[0].total_count : 0;
        if (rows.length === 0 && offset > 0) {
            const countRows = await sql<{ total: number }[]>`
                SELECT COUNT(*)::int as total
                FROM routes
                ${where}
            `;
            total = countRows[0]?.total ?? 0;
        }

        const tracks: Track[] = rows.map(({ total_count, ...track }) => track);
        return { tracks, total };
    }

    /**
     * Find a single track by ID
     */
    static async findById(id: number): Promise<Track | null> {
        const tracks = await sql<Track[]>`
            SELECT 
                id, 
                external_id,
                name, 
                description, 
                path as geojson, 
                properties, 
                difficulty, 
                difficulty_convention,
                type,
                uses,
                ref,
                oneway,
                gladed,
                patrolled,
                lit,
                grooming,
                status,
                websites,
                wikidata_id,
                elevation_profile,
                created_at
            FROM routes
            WHERE id = ${id}
        `;
        return tracks.length > 0 ? tracks[0] : null;
    }

    /**
     * Find tracks within a geographic radius
     * Note: This is a simple implementation. For production, consider using PostGIS
     */
    static async findByLocation(
        lat: number,
        lng: number,
        radius: number
    ): Promise<Track[]> {
        // For now, we'll fetch all tracks and filter in the controller
        // In production, you'd want to use PostGIS for efficient spatial queries
        return this.findAll();
    }

    /**
     * Ensure the routes table exists with the correct schema
     * This is called during initialization
     */
    static async ensureTable(): Promise<void> {
        await sql`
            CREATE TABLE IF NOT EXISTS routes (
                id SERIAL PRIMARY KEY,
                external_id VARCHAR(255),
                name VARCHAR(255),
                description TEXT,
                path JSONB, 
                properties JSONB,
                difficulty VARCHAR(50),
                difficulty_convention VARCHAR(50),
                type VARCHAR(50),
                uses TEXT[],
                ref VARCHAR(50),
                oneway VARCHAR(50),
                gladed VARCHAR(50),
                patrolled VARCHAR(50),
                lit VARCHAR(50),
                grooming VARCHAR(50),
                status VARCHAR(50),
                websites TEXT[],
                wikidata_id VARCHAR(255),
                elevation_profile JSONB,
                created_at TIMESTAMP DEFAULT NOW()
            );
        `;

        // If the table already existed with a reduced schema (e.g., local seed),
        // make sure required columns exist.
        // This prevents runtime failures like "column external_id does not exist".
        await sql`ALTER TABLE routes ADD COLUMN IF NOT EXISTS external_id VARCHAR(255);`;
        await sql`ALTER TABLE routes ADD COLUMN IF NOT EXISTS name VARCHAR(255);`;
        await sql`ALTER TABLE routes ADD COLUMN IF NOT EXISTS description TEXT;`;
        await sql`ALTER TABLE routes ADD COLUMN IF NOT EXISTS path JSONB;`;
        await sql`ALTER TABLE routes ADD COLUMN IF NOT EXISTS properties JSONB;`;
        await sql`ALTER TABLE routes ADD COLUMN IF NOT EXISTS difficulty VARCHAR(50);`;
        await sql`ALTER TABLE routes ADD COLUMN IF NOT EXISTS difficulty_convention VARCHAR(50);`;
        await sql`ALTER TABLE routes ADD COLUMN IF NOT EXISTS type VARCHAR(50);`;
        await sql`ALTER TABLE routes ADD COLUMN IF NOT EXISTS uses TEXT[];`;
        await sql`ALTER TABLE routes ADD COLUMN IF NOT EXISTS ref VARCHAR(50);`;
        await sql`ALTER TABLE routes ADD COLUMN IF NOT EXISTS oneway VARCHAR(50);`;
        await sql`ALTER TABLE routes ADD COLUMN IF NOT EXISTS gladed VARCHAR(50);`;
        await sql`ALTER TABLE routes ADD COLUMN IF NOT EXISTS patrolled VARCHAR(50);`;
        await sql`ALTER TABLE routes ADD COLUMN IF NOT EXISTS lit VARCHAR(50);`;
        await sql`ALTER TABLE routes ADD COLUMN IF NOT EXISTS grooming VARCHAR(50);`;
        await sql`ALTER TABLE routes ADD COLUMN IF NOT EXISTS status VARCHAR(50);`;
        await sql`ALTER TABLE routes ADD COLUMN IF NOT EXISTS websites TEXT[];`;
        await sql`ALTER TABLE routes ADD COLUMN IF NOT EXISTS wikidata_id VARCHAR(255);`;
        await sql`ALTER TABLE routes ADD COLUMN IF NOT EXISTS elevation_profile JSONB;`;
        await sql`ALTER TABLE routes ADD COLUMN IF NOT EXISTS created_at TIMESTAMP DEFAULT NOW();`;

        // Ensure indexes exist
        try {
            await sql`CREATE INDEX IF NOT EXISTS idx_routes_difficulty ON routes(difficulty);`;
            await sql`CREATE INDEX IF NOT EXISTS idx_routes_type ON routes(type);`;
            await sql`CREATE INDEX IF NOT EXISTS idx_routes_status ON routes(status);`;
            await sql`CREATE INDEX IF NOT EXISTS idx_routes_grooming ON routes(grooming);`;
            await sql`CREATE INDEX IF NOT EXISTS idx_routes_gladed ON routes(gladed);`;
            await sql`CREATE INDEX IF NOT EXISTS idx_routes_patrolled ON routes(patrolled);`;
            await sql`CREATE INDEX IF NOT EXISTS idx_routes_lit ON routes(lit);`;
            await sql`CREATE INDEX IF NOT EXISTS idx_routes_external_id ON routes(external_id);`;
            await sql`CREATE INDEX IF NOT EXISTS idx_routes_uses ON routes USING GIN (uses);`;
        } catch (e) {
            // Ignore errors if indexes already exist
        }
    }
}