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

async function createCheckin(userId, stationId, platformId, latitude, longitude, distance) {
    try {
        const sql = "INSERT INTO `checkins` (userId, stationId, platformId, latitude, longitude, distance) VALUES (?, ?, ?, ?, ?, ?)"
        const rows = await pool.query(sql, [userId, stationId, platformId, latitude, longitude, distance])
        return rows.insertId
    } catch (error) {
        throw new DefaultError(500, "Internal Server Error", error)
    }
}

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

async function findCheckinsByUserId(userId, limit = 50, offset = 0) {
    try {
        const sql = "SELECT * FROM `checkins` WHERE userId = ? ORDER BY checkinAt DESC LIMIT ? OFFSET ?"
        const rows = await pool.query(sql, [userId, limit, offset])
        return rows
    } catch (error) {
        throw new DefaultError(500, "Internal Server Error", error)
    }
}

async function findCheckinsByStation(stationId, limit = 50, offset = 0) {
    try {
        const sql = "SELECT * FROM `checkins` WHERE stationId = ? ORDER BY checkinAt DESC LIMIT ? OFFSET ?"
        const rows = await pool.query(sql, [stationId, limit, offset])
        return rows
    } catch (error) {
        throw new DefaultError(500, "Internal Server Error", error)
    }
}

async function countCheckinsByUser(userId) {
    try {
        const sql = "SELECT COUNT(*) as count FROM `checkins` WHERE userId = ?"
        const rows = await pool.query(sql, [userId])
        return rows[0].count
    } catch (error) {
        throw new DefaultError(500, "Internal Server Error", error)
    }
}

async function countCheckinsByUserAndStation(userId, stationId) {
    try {
        const sql = "SELECT COUNT(*) as count FROM `checkins` WHERE userId = ? AND stationId = ?"
        const rows = await pool.query(sql, [userId, stationId])
        return rows[0].count
    } catch (error) {
        throw new DefaultError(500, "Internal Server Error", error)
    }
}

async function findUniqueStationsByUser(userId) {
    try {
        const sql = "SELECT DISTINCT stationId FROM `checkins` WHERE userId = ? ORDER BY checkinAt DESC"
        const rows = await pool.query(sql, [userId])
        return rows
    } catch (error) {
        throw new DefaultError(500, "Internal Server Error", error)
    }
}

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

module.exports = {
    createCheckin,
    findCheckinById,
    findCheckinsByUserId,
    findCheckinsByStation,
    countCheckinsByUser,
    countCheckinsByUserAndStation,
    findUniqueStationsByUser,
    deleteCheckin
}