'use strict';

/**
 * In-memory database stub for tests.
 * Replaces the real Knex/PostgreSQL connection with a lightweight
 * object that simulates table operations using plain JS Maps.
 *
 * Usage: const db = createTestDb(); setDb(db);
 */

function makeTable(rows = []) {
  return [...rows];
}

/**
 * Creates a minimal Knex-like query builder stub backed by in-memory arrays.
 * Supports: insert, where, whereRaw, whereIn, whereNull, whereNotNull,
 *           first, select, update, delete, returning, orderBy,
 *           leftJoin, groupBy, raw, transaction.
 */
function createTestDb(initialData = {}) {
  const tables = {};

  function getTable(name) {
    if (!tables[name]) tables[name] = [];
    return tables[name];
  }

  function seedTable(name, rows) {
    tables[name] = [...rows];
  }

  // Seed initial data
  for (const [name, rows] of Object.entries(initialData)) {
    seedTable(name, rows);
  }

  // ── Query Builder ────────────────────────────────────────────────────────────
  function queryBuilder(tableName) {
    let _rows = null; // lazy — resolved on terminal op
    let _filters = [];
    let _insertData = null;
    let _updateData = null;
    let _doDelete = false;
    let _returning = false;
    let _orderByField = null;
    let _orderByDir = 'asc';
    let _selectFields = null;
    let _limit1 = false;
    let _joins = [];
    let _groupByField = null;
    let _rawSelects = [];

    function getRows() {
      if (_rows !== null) return _rows;
      return getTable(tableName);
    }

    function applyFilters(rows) {
      return rows.filter((row) => {
        for (const f of _filters) {
          if (!f(row)) return false;
        }
        return true;
      });
    }

    const qb = {
      where(condOrField, val) {
        if (typeof condOrField === 'object') {
          for (const [k, v] of Object.entries(condOrField)) {
            _filters.push((row) => row[k] === v);
          }
        } else {
          _filters.push((row) => row[condOrField] === val);
        }
        return qb;
      },

      whereRaw(expr, params) {
        // Support: 'LOWER(email) = ?' and 'item_name_lower = LOWER(?)'
        const lowerEmailMatch = expr.match(/LOWER\((\w+)\)\s*=\s*\?/i);
        const lowerFieldMatch = expr.match(/(\w+)\s*=\s*LOWER\(\?\)/i);
        if (lowerEmailMatch) {
          const field = lowerEmailMatch[1];
          const val = params[0].toLowerCase();
          _filters.push((row) => (row[field] || '').toLowerCase() === val);
        } else if (lowerFieldMatch) {
          const field = lowerFieldMatch[1];
          const val = params[0].toLowerCase();
          _filters.push((row) => (row[field] || '') === val);
        }
        return qb;
      },

      whereIn(field, vals) {
        _filters.push((row) => vals.includes(row[field]));
        return qb;
      },

      whereNull(field) {
        _filters.push((row) => row[field] == null);
        return qb;
      },

      whereNotNull(field) {
        _filters.push((row) => row[field] != null);
        return qb;
      },

      select(...fields) {
        _selectFields = fields.flat();
        return qb;
      },

      orderBy(field, dir = 'asc') {
        _orderByField = field;
        _orderByDir = dir;
        return qb;
      },

      groupBy() {
        _groupByField = true;
        return qb;
      },

      leftJoin(otherTable, leftKey, rightKey) {
        _joins.push({ otherTable, leftKey, rightKey });
        return qb;
      },

      raw(sql) {
        _rawSelects.push(sql);
        return sql; // knex.raw returns the string in our stub
      },

      insert(data) {
        _insertData = Array.isArray(data) ? data : [data];
        return qb;
      },

      update(data) {
        _updateData = data;
        return qb;
      },

      delete() {
        _doDelete = true;
        return qb;
      },

      returning() {
        _returning = true;
        return qb;
      },

      first() {
        _limit1 = true;
        return qb;
      },

      // Terminal: await qb
      then(resolve, reject) {
        try {
          resolve(execute());
        } catch (e) {
          reject(e);
        }
      },
    };

    function execute() {
      const table = getTable(tableName);

      if (_insertData) {
        const inserted = _insertData.map((d) => {
          const row = {
            id: d.id || require('uuid').v4(),
            ...d,
            created_at: d.created_at || new Date().toISOString(),
            updated_at: d.updated_at || new Date().toISOString(),
            last_modified: d.last_modified || new Date().toISOString(),
          };
          // Compute generated columns
          if (row.item_name !== undefined) {
            row.item_name_lower = row.item_name.toLowerCase();
          }
          table.push(row);
          return row;
        });
        return _returning ? inserted : inserted.length;
      }

      if (_doDelete) {
        const before = table.length;
        const toDelete = applyFilters(table);
        for (const row of toDelete) {
          const idx = table.indexOf(row);
          if (idx !== -1) table.splice(idx, 1);
        }
        return _returning ? toDelete : before - table.length;
      }

      if (_updateData) {
        const matched = applyFilters(table);
        const updated = matched.map((row) => {
          Object.assign(row, _updateData, {
            updated_at: new Date().toISOString(),
          });
          if (row.item_name !== undefined) {
            row.item_name_lower = row.item_name.toLowerCase();
          }
          return row;
        });
        return _returning ? updated : updated.length;
      }

      // SELECT
      let rows = applyFilters(table);

      // Apply joins (simplified — just attach joined fields)
      for (const join of _joins) {
        const otherRows = getTable(join.otherTable);
        const [leftTable, leftField] = join.leftKey.split('.');
        const [, rightField] = join.rightKey.split('.');
        rows = rows.map((row) => {
          const matches = otherRows.filter((o) => o[rightField] === row[leftField || 'id']);
          if (matches.length === 0) return { ...row, _joined: [] };
          return matches.map((m) => ({ ...row, ...m, _joined: true }));
        }).flat();
      }

      if (_orderByField) {
        rows = [...rows].sort((a, b) => {
          const av = a[_orderByField] || '';
          const bv = b[_orderByField] || '';
          const cmp = String(av).localeCompare(String(bv));
          return _orderByDir === 'desc' ? -cmp : cmp;
        });
      }

      // Aggregate for groupBy (count)
      if (_groupByField) {
        const grouped = {};
        for (const row of rows) {
          const key = row.id;
          if (!grouped[key]) {
            grouped[key] = { ...row, item_count: 0, checked_count: 0 };
          }
          if (row._joined) {
            grouped[key].item_count++;
            if (row.checked_off) grouped[key].checked_count++;
          }
        }
        rows = Object.values(grouped);
      }

      if (_limit1) return rows[0] || null;
      return rows;
    }

    return qb;
  }

  // ── Transaction stub ─────────────────────────────────────────────────────────
  async function transaction(fn) {
    // Pass the same db proxy as the transaction context
    return fn(proxy);
  }

  // ── Raw stub ─────────────────────────────────────────────────────────────────
  function raw(sql) {
    return sql;
  }

  const proxy = new Proxy(
    { transaction, raw, _tables: tables, seedTable, getTable },
    {
      get(target, prop) {
        if (prop in target) return target[prop];
        return (tableName) => queryBuilder(tableName || prop);
      },
      apply(target, thisArg, args) {
        return queryBuilder(args[0]);
      },
    }
  );

  // Make proxy callable as a function: db('tableName')
  return new Proxy(function () {}, {
    apply(target, thisArg, args) {
      return queryBuilder(args[0]);
    },
    get(target, prop) {
      if (prop === 'transaction') return transaction;
      if (prop === 'raw') return raw;
      if (prop === '_tables') return tables;
      if (prop === 'seedTable') return seedTable;
      if (prop === 'getTable') return getTable;
      return (tableName) => queryBuilder(tableName || prop);
    },
  });
}

module.exports = { createTestDb };