import pg from 'pg';
import { User } from './user.js';
import { Group } from './group.js';
import { Expense } from './expense.js';
import fs from 'fs';

export class Database {
  constructor() {
    this.client = new pg.Client({
      user: 'postgres',
      host: process.env.DB_HOST || 'localhost',
      database: 'splitfair',
      password: 'password',
      port: 5432,
    });
  }

  async connect(schemaFilePath = undefined) {
    await this.client.connect();

    if (schemaFilePath) {
      await new Promise((resolve, reject) => {
        fs.readFile(schemaFilePath, async (err, data) => {
          if (err) return reject(err);
          await this.client.query(data.toString('utf8'));
          resolve();
        });
      })
    }
  }

  async query(query) {
    await this.client.query(query);
  }

  async disconnect() {
    await this.client.end();
  }

  async getUser(userName) {
    return (await this.getUserWithPassword(userName))?.getWithoutPassword();
  }

  async getUserWithPassword(userName) {
    const response = await this.client.query({
      text: 'SELECT * FROM users WHERE name = $1',
      values: [userName]
    });

    if (response.rowCount === 0) return null;
    return Database.#extractUserFromRow(Database.#extractSingleRow(response));
  }

  async getUsersStartingWith(partialUserName, limit) {
    partialUserName += '%';

    const response = await this.client.query({
      text: `SELECT id, name
             FROM users
             WHERE name LIKE $1
             LIMIT $2;`,
      values: [partialUserName, limit]
    });

    return response.rows.map(row => Database.#extractUserFromRow(row));
  }

  async createUser(user) {
    if (await this.getUser(user.name)) {
      throw new Error('User already exits');
    }

    await this.client.query({
      text: 'INSERT INTO users(name, password, creation_date) VALUES($1, $2, $3)',
      values: [user.name, user.storedPassword, user.creationDate]
    });
  }

  async createGroup(group) {
    await this.client.query({
      text: 'INSERT INTO groups(name, description, currency, creator, closed) VALUES($1, $2, $3, $4, $5)',
      values: [group.name, group.description, group.currency, group.creator.id, group.closed]
    });
    await this.addUserToGroup(group.creator, await this.getGroup(group.name));
  }

  async closeGroup(group) {
    await this.client.query({
      text: 'UPDATE groups SET closed = true WHERE groups.id = $1',
      values: [group.id]
    });
  }

  async createExpense(expense) {
    await this.client.query({
      text: 'INSERT INTO expenses(group_id, creator_id, description, creation_date, amount) VALUES($1, $2, $3, $4, $5)',
      values: [expense.group.id, expense.creator.id, expense.description, expense.creationDate, expense.amount]
    });
  }

  async deleteExpense(expenseId) {
    await this.client.query({
      text: 'DELETE FROM expenses WHERE id = $1',
      values: [expenseId]
    });
  }

  async addUserToGroup(user, group) {
    await this.client.query({
      text: 'INSERT INTO group_members(user_id, group_id, settled) VALUES($1, $2, $3)',
      values: [user.id, group.id, false]
    });
  }

  async getGroupsOfUser(user, closed = undefined) {
    let query;
    if (typeof closed === 'undefined') {
      query = {
        text: `SELECT g.*
               FROM group_members
                        INNER JOIN users u ON (group_members.user_id = u.id)
                        INNER JOIN groups g ON (group_members.group_id = g.id)
               WHERE u.id = $1;`,
        values: [user.id]
      };
    } else if (typeof closed === 'boolean') {
      query = {
        text: `SELECT g.*
               FROM group_members
                        INNER JOIN users u ON (group_members.user_id = u.id)
                        INNER JOIN groups g ON (group_members.group_id = g.id)
               WHERE u.id = $1
                 AND g.closed = $2;`,
        values: [user.id, closed]
      };
    } else throw new Error('Expected closed to be undefined or a boolean type');

    const result = await this.client.query(query);
    return await Promise.all(result.rows.map(async row => await this.#extractGroupFromRow(row)));
  }

  async getUsersOfGroup(group) {
    const result = await (this.client.query({
      text: `SELECT u.id, u.name, u.creation_date
             FROM group_members
                      INNER JOIN users u ON (group_members.user_id = u.id)
                      INNER JOIN groups g ON (group_members.group_id = g.id)
             WHERE g.id = $1;`,
      values: [group.id]
    }));

    return result.rows.map(row => Database.#extractUserFromRow(row));
  }

  async getGroup(groupName) {
    const result = await this.client.query({
      text: 'SELECT * FROM groups WHERE name = $1',
      values: [groupName]
    });

    if (result.rowCount === 0) return null;
    return await this.#extractGroupFromRow(Database.#extractSingleRow(result));
  }

  async getExpensesOfGroup(group) {
    const result = await this.client.query({
      text: 'SELECT * FROM expenses WHERE group_id = $1',
      values: [group.id]
    });

    return await Promise.all(result.rows.map(async row =>
        new Expense(
            group,
            await this.#getUserById(row.creator_id),
            row.description,
            row.amount,
            row.creation_date,
            row.id
        )));
  }

  async #getUserById(id) {
    const response = await this.client.query({
      text: 'SELECT id, name, creation_date FROM users WHERE id = $1',
      values: [id]
    });
    return Database.#extractUserFromRow(Database.#extractSingleRow(response));
  }

  static #extractUserFromRow(row) {
    return new User(row.name, row.password, row.creation_date, row.id);
  }

  async #extractGroupFromRow(row) {
    return new Group(
        row.name,
        row.currency,
        await this.#getUserById(row.creator),
        row.description,
        row.closed,
        row.id
    );
  }

  static #extractSingleRow(response) {
    if (response.rowCount !== 1) {
      throw new Error('Expected rowCount to be 1 but was ' + response.rowCount);
    }

    return response.rows[0];
  }
}
