const fs = require('fs');
const path = require('path');
require('dotenv').config();
const mysql = require('mysql2/promise');
const sqlite3 = require('sqlite3').verbose();
const dbOptions = require('./dbOptions');

const client = (process.env.DB_CLIENT || 'mysql').toLowerCase();

function normalizeSql(sql) {
  return sql
    .replace(/\s+FOR UPDATE/gi, '')
    .replace(/UPDATE settings SET next_invoice_number = next_invoice_number \+ 1 ORDER BY id ASC LIMIT 1/gi, 'UPDATE settings SET next_invoice_number = next_invoice_number + 1 WHERE id = (SELECT id FROM settings ORDER BY id ASC LIMIT 1)');
}

function createSqliteAdapter() {
  const sqliteFile = path.resolve(process.cwd(), process.env.SQLITE_FILE || './database/ecofina.sqlite');
  fs.mkdirSync(path.dirname(sqliteFile), { recursive: true });
  const database = new sqlite3.Database(sqliteFile);

  function all(sql, params = []) {
    return new Promise((resolve, reject) => {
      database.all(normalizeSql(sql), params, (error, rows) => {
        if (error) return reject(error);
        return resolve([rows]);
      });
    });
  }

  function run(sql, params = []) {
    return new Promise((resolve, reject) => {
      database.run(normalizeSql(sql), params, function onRun(error) {
        if (error) return reject(error);
        return resolve([{ insertId: this.lastID, affectedRows: this.changes }]);
      });
    });
  }

  async function query(sql, params = []) {
    const statement = sql.trim().toLowerCase();
    if (statement.startsWith('select') || statement.startsWith('pragma')) return all(sql, params);
    return run(sql, params);
  }

  function exec(sql) {
    return new Promise((resolve, reject) => {
      database.exec(sql, (error) => {
        if (error) return reject(error);
        return resolve();
      });
    });
  }

  return {
    client,
    isSQLite: true,
    query,
    async init() {
      const schemaPath = path.join(process.cwd(), 'database', 'schema.sqlite.sql');
      await exec(fs.readFileSync(schemaPath, 'utf8'));
    },
    async getConnection() {
      await query('BEGIN IMMEDIATE TRANSACTION');
      return {
        query,
        beginTransaction: async () => {},
        commit: async () => query('COMMIT'),
        rollback: async () => query('ROLLBACK'),
        release: () => {}
      };
    },
    end() {
      return new Promise((resolve, reject) => {
        database.close((error) => {
          if (error) return reject(error);
          return resolve();
        });
      });
    }
  };
}

function createMysqlAdapter() {
  const pool = mysql.createPool({
    ...dbOptions,
    waitForConnections: true,
    connectionLimit: 10,
    queueLimit: 0,
    decimalNumbers: true
  });
  pool.client = client;
  pool.isSQLite = false;
  pool.init = async () => {};
  return pool;
}

module.exports = client === 'sqlite' ? createSqliteAdapter() : createMysqlAdapter();
