const { pool } = require("../modules/database")
const { DefaultError } = require("../errors/errors")

async function findAllStations() {
    try {
        const sql = "SELECT * FROM stations"
        return await pool.query(sql)
    } catch (error) {
        throw new DefaultError(500, "Internal Server Error", error)
    }
}

async function findStationById(stationId) {
    try {
        const sql = "SELECT * FROM stations WHERE id = ?"
        const rows = await pool.query(sql, [stationId])
        return rows[0] || null
    } catch (error) {
        throw new DefaultError(500, "Internal Server Error", error)
    }
}

// distance = celle du quai le plus proche
async function findStationsByDistance(latitude, longitude, radiusKm) {
    try {
        const sql = `
            SELECT s.*, MIN(ST_Distance_Sphere(p.location, ST_SRID(POINT(?, ?), 4326))) / 1000 AS distance
            FROM stations s
            JOIN platforms p ON p.stationId = s.id
            GROUP BY s.id
            HAVING distance <= ?
            ORDER BY distance
        `
        return await pool.query(sql, [longitude, latitude, radiusKm])
    } catch (error) {
        throw new DefaultError(500, "Internal Server Error", error)
    }
}

// une station est "accessible" si AU MOINS un quai l'est
async function findAccessibleStations(latitude, longitude, radiusKm) {
    try {
        const sql = `
            SELECT s.*, MIN(ST_Distance_Sphere(p.location, ST_SRID(POINT(?, ?), 4326))) / 1000 AS distance
            FROM stations s
            JOIN platforms p ON p.stationId = s.id
            WHERE p.isPRM = TRUE AND p.hasElevator = TRUE
            GROUP BY s.id
            HAVING distance <= ?
            ORDER BY distance
        `
        return await pool.query(sql, [longitude, latitude, radiusKm])
    } catch (error) {
        throw new DefaultError(500, "Internal Server Error", error)
    }
}

async function createStation(name, description) {
    try {
        const sql = "INSERT INTO stations (name, description) VALUES (?, ?)"
        const rows = await pool.query(sql, [name, description])
        return rows.insertId
    } catch (error) {
        throw new DefaultError(500, "Internal Server Error", error)
    }
}

async function updateStation(stationId, updateData) {
    try {
        const fields = Object.keys(updateData)
        const values = Object.values(updateData)
        const setClause = fields.map(f => `${f} = ?`).join(", ")
        const sql = `UPDATE stations SET ${setClause} WHERE id = ?`
        const rows = await pool.query(sql, [...values, stationId])
        return rows.affectedRows > 0
    } catch (error) {
        throw new DefaultError(500, "Internal Server Error", error)
    }
}

async function deleteStation(stationId) {
    try {
        const sql = "DELETE FROM stations WHERE id = ?"
        const rows = await pool.query(sql, [stationId])
        return rows.affectedRows > 0
    } catch (error) {
        throw new DefaultError(500, "Internal Server Error", error)
    }
}

module.exports = {
    findAllStations,
    findStationById,
    findStationsByDistance,
    findAccessibleStations,
    createStation,
    updateStation,
    deleteStation
}