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

// lignes d'une station (dédupliquées, tous quais confondus)
async function findLinesByStation(stationId) {
    try {
        const sql = `
            SELECT DISTINCT l.*
            FROM \`lines\` l
            JOIN platform_lines pl ON pl.lineId = l.id
            JOIN platforms p ON p.id = pl.platformId
            WHERE p.stationId = ?
        `
        return await pool.query(sql, [stationId])
    } catch (error) {
        throw new DefaultError(500, "Internal Server Error", error)
    }
}

async function findLinesByPlatform(platformId) {
    try {
        const sql = `
            SELECT l.*, pl.directionId, pl.orderInLine
            FROM \`lines\` l
            JOIN platform_lines pl ON pl.lineId = l.id
            WHERE pl.platformId = ?
        `
        return await pool.query(sql, [platformId])
    } catch (error) {
        throw new DefaultError(500, "Internal Server Error", error)
    }
}

// parcours ordonné d'une ligne dans un sens donné
async function findStationsByLine(lineId, directionId = 0) {
    try {
        const sql = `
            SELECT s.*, p.id AS platformId, p.name AS platformName, pl.orderInLine
            FROM stations s
            JOIN platforms p ON p.stationId = s.id
            JOIN platform_lines pl ON pl.platformId = p.id
            WHERE pl.lineId = ? AND pl.directionId = ?
            ORDER BY pl.orderInLine
        `
        return await pool.query(sql, [lineId, directionId])
    } catch (error) {
        throw new DefaultError(500, "Internal Server Error", error)
    }
}

async function addLineToPlatform(platformId, lineId, directionId, orderInLine) {
    try {
        const sql = "INSERT INTO platform_lines (platformId, lineId, directionId, orderInLine) VALUES (?, ?, ?, ?)"
        const rows = await pool.query(sql, [platformId, lineId, directionId, orderInLine])
        return rows.insertId
    } catch (error) {
        throw new DefaultError(500, "Internal Server Error", error)
    }
}

async function removeLineFromPlatform(platformId, lineId, directionId) {
    try {
        const sql = "DELETE FROM platform_lines WHERE platformId = ? AND lineId = ? AND directionId = ?"
        const rows = await pool.query(sql, [platformId, lineId, directionId])
        return rows.affectedRows > 0
    } catch (error) {
        throw new DefaultError(500, "Internal Server Error", error)
    }
}

async function updateOrder(platformLineId, orderInLine) {
    try {
        const sql = "UPDATE platform_lines SET orderInLine = ? WHERE id = ?"
        const rows = await pool.query(sql, [orderInLine, platformLineId])
        return rows.affectedRows > 0
    } catch (error) {
        throw new DefaultError(500, "Internal Server Error", error)
    }
}

module.exports = {
    findLinesByStation,
    findLinesByPlatform,
    findStationsByLine,
    addLineToPlatform,
    removeLineFromPlatform,
    updateOrder
}