import pool from "../db"
import { Course, CourseAttendee, CourseAttendeeRole, CourseEventDetails, CourseEventSummary, RawAttendeeRow } from "@app/shared/types/course"
import { toDateTime } from "@app/shared/utils/time";
export async function getAllCourses(): Promise<Course[]> {
    const sql = "SELECT * FROM courses WHERE deleted = false ORDER BY name ASC;"

    const res: Course[] = await pool.query(sql);

    return res;
}

export async function getCourseByID(id: number): Promise<Course> {
    const sql = "SELECT * FROM courses WHERE id = ?;"
    const res: Course[] = await pool.query(sql, [id]);
    return res[0];
}

function buildAttendee(row: RawAttendeeRow): CourseAttendee {
    return {
        passed_bookwork: !!row.passed_bookwork,
        passed_qual: !!row.passed_qual,
        attendee_id: row.attendee_id,
        course_event_id: row.course_event_id,
        created_at: new Date(row.created_at),
        updated_at: new Date(row.updated_at),
        remarks: row.remarks,
        attendee_role_id: row.attendee_role_id,
        attendee_name: row.attendee_name,
        role: row.role_id
            ? {
                id: row.role_id,
                name: row.role_name,
                description: row.role_description,
                deleted: !!row.role_deleted,
                created_at: new Date(row.role_created_at),
                updated_at: new Date(row.role_updated_at),
            }
            : null
    };
}

export async function getCourseEventAttendees(id: number): Promise<CourseAttendee[]> {
    const sql = `SELECT
                ca.*,
                mem.name AS attendee_name,
                ar.id AS role_id,
                ar.name AS role_name,
                ar.description AS role_description,
                ar.deleted AS role_deleted,
                ar.created_at AS role_created_at,
                ar.updated_at AS role_updated_at
                FROM course_attendees ca
                LEFT JOIN course_attendee_roles ar ON ar.id = ca.attendee_role_id
                LEFT JOIN members mem ON ca.attendee_id = mem.id
                WHERE ca.course_event_id = ?;`;

    const res: RawAttendeeRow[] = await pool.query(sql, [id]);

    return res.map((row) => buildAttendee(row))
}

export async function getCourseEventDetails(id: number): Promise<CourseEventDetails> {
    const sql = `SELECT 
                    E.*,
                    M.name AS created_by_name,
                    C.name AS course_name
                FROM course_events AS E
                LEFT JOIN courses AS C 
                    ON E.course_id = C.id
                LEFT JOIN members AS M
                    ON E.created_by = M.id
                WHERE E.id = ?;
                `;
    let rows: CourseEventDetails[] = await pool.query(sql, [id]);
    let event = rows[0];
    event.attendees = await getCourseEventAttendees(id);
    event.course = await getCourseByID(event.course_id);
    return event;
}

export async function insertCourseEvent(event: CourseEventDetails): Promise<number> {
    console.log(event);
    const con = await pool.getConnection();
    try {
        await con.beginTransaction();
        const res = await con.query("INSERT INTO course_events (course_id, event_date, remarks, created_by) VALUES (?, ?, ?, ?);", [event.course_id, toDateTime(event.event_date), event.remarks, event.created_by]);
        var eventID: number = res.insertId;

        for (const attendee of event.attendees) {
            await con.query(`INSERT INTO course_attendees (
                        attendee_id,
                        course_event_id,
                        attendee_role_id,
                        passed_bookwork,
                        passed_qual,
                        remarks
                        )
                        VALUES (?, ?, ?, ?, ?, ?);`, [attendee.attendee_id, eventID, attendee.attendee_role_id, attendee.passed_bookwork, attendee.passed_qual, attendee.remarks]);
        }
        await con.commit();
        await con.release();
        return Number(eventID);
    } catch (error) {
        await con.rollback();
        await con.release();
        throw error;
    }
}

export async function getCourseEvents(sortDir: string, search: string = ""): Promise<CourseEventSummary[]> {

    let params = [];
    let searchString = "";
    if (search !== "") {
        searchString = `WHERE (C.name LIKE ? OR
                     C.short_name LIKE ? OR
                     M.name LIKE ?) `;
        const p = `%${search}%`;
        params.push(p, p, p);
    }

    const sql = `SELECT 
                    E.id AS event_id,
                    E.course_id,
                    E.event_date AS date,
                    E.created_by,
                    C.name AS course_name,
                    C.short_name AS course_shortname,
                    M.name AS created_by_name
                FROM course_events AS E
                LEFT JOIN courses AS C 
                    ON E.course_id = C.id
                LEFT JOIN members AS M
                    ON E.created_by = M.id
                ${searchString}
                ORDER BY E.event_date ${sortDir};`;
    console.log(sql)
    console.log(params)
    let events: CourseEventSummary[] = await pool.query(sql, params);
    return events;
}

export async function getCourseEventRoles(): Promise<CourseAttendeeRole[]> {
    const sql = "SELECT * FROM course_attendee_roles;"
    const roles: CourseAttendeeRole[] = await pool.query(sql);
    return roles;
}