const database = require('./../database');
const genders = require('./genders');





class Division {
    constructor(id, name, gender, sportID) {
        this.id = id;
        this.name = name;
        this.gender = gender;
        this.sportID = sportID;
    }
}



function getGenderID(gender) {
    return (gender == genders.MALE) ? "M" : "F";
}

function getGenderFromID(genderID) {
    return (genderID == "F") ? genders.FEMALE : genders.MALE;
}



async function add(name, gender, sportID) {
    const query = `INSERT INTO scores.divisions(division_name,gender,sport_id)
            VALUES($1,$2,$3)
            RETURNING division_id;`;
    const genderID = getGenderID(gender);
    const id = (await database.executeQuery(query, [name, genderID, sportID]))[0][0];
    return new Division(id, name);
}

async function rename(id, name) {
    const query = `UPDATE scores.divisions
    SET division_name = $2
    WHERE division_id = $1;`;
    await database.executeQuery(query, [id, name]);
    return new Division(id, name);
}

async function remove(id) {
    const query = `DELETE FROM scores.divisions
    WHERE division_id = $1
    RETURNING division_name;`;
    const name = (await database.executeQuery(query, [id]))[0][0];
    return new Division(id, name);
}

async function retrieve(sportID = undefined, gender = undefined) {
    let table;

    if(sportID && gender) {
        const query = `SELECT division_id, division_name, gender, sport_id
            FROM scores.divisions
            WHERE sport_id = $1 AND gender = $2
            ORDER BY division_name;`;
        const genderID = getGenderID(gender);
        table = await database.executeQuery(query, [sportID, genderID]);
    }
    else {
        const query = `SELECT division_id, division_name, gender, sport_id
            FROM scores.divisions
            ORDER BY sport_id,
                    division_name,
                    gender;`;
        table = await database.executeQuery(query);
    }

    const divisionsList = [];
    table.forEach((row) => {
        divisionsList.push(new Division(row[0], row[1], getGenderFromID(row[2]), row[3]));
    });
    return divisionsList;
}

async function getFromID(id) {
    const query = `SELECT division_id, division_name, gender, sport_id
        FROM scores.divisions
        WHERE division_id = $1;`;
    const row = (await database.executeQuery(query, [id]))[0];


    return new Division(id, row[1], getGenderFromID(row[2]), row[3]);
}





exports.add = add;
exports.rename = rename;
exports.remove = remove;
exports.retrieve = retrieve;
exports.getFromID = getFromID;