const pg = require('pg');

class Database {
    constructor(connectionStr) {
        this.pool = new pg.Pool({connectionString: connectionStr});
    }

    async checkAndRegisterUser(id, name) {
        let client = await this.pool.connect();
        try {
            let result = await client.query("SELECT * FROM qr_users WHERE user_id = $1", [id]);
            if (result.rowCount === 0) {
                await client.query("INSERT INTO qr_users (user_id, name) VALUES ($1, $2)", [id, name]);
            }
        } finally {
            client.release();
        }
    }

    async codeExists(code) {
        let client = await this.pool.connect();
        try {
            let result = await client.query("SELECT * FROM qr_codes WHERE id = $1", [code]);
            return result.rowCount === 1;
        } finally {
            client.release();
        }
    }

    async useCode(code, user) {
        let client = await this.pool.connect();
        try {
            let result = await client.query("SELECT * FROM qr_codes WHERE id = $1", [code]);
            if (result.rowCount === 1) {
                result = await client.query("SELECT * FROM qr_gots WHERE code_id = $1 AND user_id = $2", [code, user]);
                if (result.rowCount === 0) {
                    await client.query("INSERT INTO qr_gots (code_id, user_id) VALUES ($1, $2)", [code, user]);
                    return true;
                } else {
                    return false;
                }
            } else {
                return false;
            }
        } finally {
            client.release();
        }
    }

    async getUserCodes(user) {
        let client = await this.pool.connect();
        try {
            let result = await client.query("SELECT * FROM qr_gots WHERE user_id = $1", [user]);
            return result.rows.length;
        } finally {
            client.release();
        }
    }

    async getTotalCodes() {
        let client = await this.pool.connect();
        try {
            let result = await client.query("SELECT * FROM qr_codes");
            return result.rowCount;
        } finally {
            client.release();
        }
    }

    async getTop() {
        let client = await this.pool.connect();
        try {
            let result = await client.query("SELECT qr_users.name as name, COUNT(qr_gots.user_id) AS count, qr_users.user_id as id FROM qr_gots INNER JOIN qr_users ON qr_gots.user_id = qr_users.user_id GROUP BY qr_users.user_id ORDER BY count DESC LIMIT 10");
            return result.rows;
        } finally {
            client.release();
        }
    }
}

module.exports = Database;