/**
 * QueryBuilder — Fluent SQL query builder for Tina4 Node.js.
 *
 * Usage:
 *   // Standalone
 *   const result = QueryBuilder.fromTable("users", db)
 *     .select("id", "name")
 *     .where("active = ?", [1])
 *     .orderBy("name ASC")
 *     .limit(10)
 *     .get();
 *
 *   // From ORM model
 *   const result = User.query()
 *     .where("age > ?", [18])
 *     .orderBy("name")
 *     .get();
 */

import type { DatabaseAdapter } from "./types.js";
import { getAdapter, adapterFetch, adapterFetchOne, probeTotal } from "./database.js";
import { DatabaseResult } from "./databaseResult.js";
import { Point, DEFAULT_SRID } from "./point.js";
import { SQLTranslator } from "./sqlTranslator.js";

export class QueryBuilder {
  private table: string;
  private db: DatabaseAdapter | undefined;
  private columns: string[] = ["*"];
  private selectParams: unknown[] = [];
  private wheres: [string, string][] = [];
  private params: unknown[] = [];
  private joinClauses: string[] = [];
  private groupByCols: string[] = [];
  private havings: string[] = [];
  private havingParams: unknown[] = [];
  private orderByCols: string[] = [];
  private orderByParams: unknown[] = [];
  private primaryKey: string | undefined;
  private limitVal: number | undefined;
  private offsetVal: number | undefined;

  /**
   * Private constructor — use static factory methods.
   */
  private constructor(table: string, db?: DatabaseAdapter, primaryKey?: string) {
    this.table = table;
    this.db = db;
    this.primaryKey = primaryKey;
  }

  /**
   * Create a QueryBuilder for a table.
   *
   * @param tableName - Table name.
   * @param db - Optional database adapter.
   * @returns A new QueryBuilder instance.
   */
  static fromTable(tableName: string, db?: DatabaseAdapter, primaryKey?: string): QueryBuilder {
    return new QueryBuilder(tableName, db, primaryKey);
  }

  /**
   * Set the columns to select.
   *
   * @param cols - Column names.
   * @returns this for chaining.
   */
  select(...cols: string[]): QueryBuilder {
    if (cols.length > 0) {
      this.columns = cols;
      this.selectParams = [];
    }
    return this;
  }

  /**
   * Add a WHERE condition (AND).
   *
   * @param condition - SQL condition with ? placeholders.
   * @param params - Parameter values.
   * @returns this for chaining.
   */
  where(condition: string, params: unknown[] = []): QueryBuilder {
    this.wheres.push(["AND", condition]);
    this.params.push(...params);
    return this;
  }

  /**
   * Add a WHERE condition (OR).
   *
   * @param condition - SQL condition with ? placeholders.
   * @param params - Parameter values.
   * @returns this for chaining.
   */
  orWhere(condition: string, params: unknown[] = []): QueryBuilder {
    this.wheres.push(["OR", condition]);
    this.params.push(...params);
    return this;
  }

  /**
   * Add an INNER JOIN.
   *
   * @param table - Table to join.
   * @param onClause - Join condition.
   * @returns this for chaining.
   */
  join(table: string, onClause: string): QueryBuilder {
    this.joinClauses.push(`INNER JOIN ${table} ON ${onClause}`);
    return this;
  }

  /**
   * Add a LEFT JOIN.
   *
   * @param table - Table to join.
   * @param onClause - Join condition.
   * @returns this for chaining.
   */
  leftJoin(table: string, onClause: string): QueryBuilder {
    this.joinClauses.push(`LEFT JOIN ${table} ON ${onClause}`);
    return this;
  }

  /**
   * Add a GROUP BY column.
   *
   * @param column - Column name.
   * @returns this for chaining.
   */
  groupBy(column: string): QueryBuilder {
    this.groupByCols.push(column);
    return this;
  }

  /**
   * Add a HAVING clause.
   *
   * @param expression - HAVING expression with ? placeholders.
   * @param params - Parameter values.
   * @returns this for chaining.
   */
  having(expression: string, params: unknown[] = []): QueryBuilder {
    this.havings.push(expression);
    this.havingParams.push(...params);
    return this;
  }

  /**
   * Add an ORDER BY clause.
   *
   * @param expression - Column and direction (e.g. "name ASC").
   * @returns this for chaining.
   */
  orderBy(expression: string): QueryBuilder {
    this.orderByCols.push(expression);
    return this;
  }

  withinDistance(column: string, pointValue: unknown, radiusMetres: number, srid = DEFAULT_SRID): QueryBuilder {
    const radius = Number(radiusMetres);
    if (!Number.isFinite(radius) || radius < 0) throw new RangeError("Spatial radius must be finite and greater than or equal to zero");
    const point = Point.parse(pointValue, srid);
    return this.where(SQLTranslator.withinDistance(this.engine(), column, point.srid), [point.lon, point.lat, radius]);
  }

  intersects(column: string, geometry: unknown, srid = DEFAULT_SRID): QueryBuilder {
    const [bound, form] = Point.geometryBinding(geometry, srid);
    return this.where(SQLTranslator.intersects(this.engine(), column, form, srid), [bound]);
  }

  bbox(column: string, minLon: unknown, minLat: unknown, maxLon: unknown, maxLat: unknown, srid = DEFAULT_SRID): QueryBuilder {
    const values = [minLon, minLat, maxLon, maxLat].map(Number);
    if (!values.every(Number.isFinite)) throw new TypeError("Bounding-box coordinates must be finite numbers");
    const [west, south, east, north] = values;
    new Point(west, south, srid);
    new Point(east, north, srid);
    if (west > east || south > north) throw new RangeError("Bounding box must be ordered west, south, east, north");
    return this.where(SQLTranslator.bbox(this.engine(), column, srid), values);
  }

  selectDistance(column: string, pointValue: unknown, alias = "distance", srid = DEFAULT_SRID): QueryBuilder {
    const point = Point.parse(pointValue, srid);
    this.columns.push(SQLTranslator.distanceAs(this.engine(), column, alias, point.srid));
    this.selectParams.push(point.lon, point.lat);
    return this;
  }

  orderByDistance(column: string, pointValue: unknown, direction: "ASC" | "DESC" = "ASC", srid = DEFAULT_SRID): QueryBuilder {
    const order = direction.toUpperCase();
    if (order !== "ASC" && order !== "DESC") throw new TypeError("Distance order direction must be ASC or DESC");
    if (!this.primaryKey) throw new Error("Stable spatial ordering needs a primary key; use BaseModel.query() or pass one to fromTable()");
    const point = Point.parse(pointValue, srid);
    this.orderByCols.push(`${SQLTranslator.distance(this.engine(), column, point.srid)} ${order}`);
    this.orderByParams.push(point.lon, point.lat);
    this.orderByCols.push(`${SQLTranslator.spatialIdentifier(this.primaryKey, "primary key")} ASC`);
    return this;
  }

  /**
   * Set LIMIT and optional OFFSET.
   *
   * @param count - Maximum rows to return.
   * @param offset - Number of rows to skip.
   * @returns this for chaining.
   */
  limit(count: number, offset?: number): QueryBuilder {
    this.limitVal = count;
    if (offset !== undefined) {
      this.offsetVal = offset;
    }
    return this;
  }

  /**
   * Build and return the SQL string without executing.
   *
   * @returns The constructed SQL query.
   */
  toSql(): string {
    let sql = `SELECT ${this.columns.join(", ")} FROM ${this.table}`;

    if (this.joinClauses.length > 0) {
      sql += " " + this.joinClauses.join(" ");
    }

    if (this.wheres.length > 0) {
      sql += " WHERE " + this.buildWhere();
    }

    if (this.groupByCols.length > 0) {
      sql += " GROUP BY " + this.groupByCols.join(", ");
    }

    if (this.havings.length > 0) {
      sql += " HAVING " + this.havings.join(" AND ");
    }

    if (this.orderByCols.length > 0) {
      sql += " ORDER BY " + this.orderByCols.join(", ");
    }

    return sql;
  }

  /**
   * Execute the query and return a DatabaseResult.
   *
   * BREAKING (3.13.95, parity): this returned a bare array of rows. The other
   * three frameworks all return the DatabaseResult that `db.fetch()` produces:
   *   Python  get() -> DatabaseResult   (orm/query_builder/__init__.py)
   *   PHP     get(): mixed -> $this->db->fetch(...)
   *   Ruby    get -> @db.fetch(...)
   * Node was the odd one out, so the same builder chain returned a different
   * TYPE per language and portable code could not read `.records`, `.count`,
   * `.limit` or `.offset` off it.
   *
   * MIGRATION: read `.records` for the rows.
   *   before: const rows = await qb.get();          rows.length
   *   after:  const result = await qb.get();        result.records.length
   * DatabaseResult is iterable, so `for (const row of result)` and
   * `[...result]` work unchanged, and `response()`/`res.json()` already
   * auto-serialize it to a JSON array.
   *
   * No default LIMIT is applied when `.limit()` was never called (v3.13.39) --
   * a silent cap here was a data-loss-on-read footgun. That is unchanged.
   *
   * @returns DatabaseResult carrying `.records`, `.count`, `.limit`, `.offset`.
   */
  async get(): Promise<DatabaseResult> {
    this.ensureDb();
    const sql = this.toSql();
    const allParams = [...this.selectParams, ...this.params, ...this.havingParams, ...this.orderByParams];

    const queryParams = allParams.length > 0 ? allParams : undefined;
    const rows = await adapterFetch(
      this.db!,
      sql,
      queryParams,
      this.limitVal,
      this.offsetVal,
    );

    // Constructed exactly as Database._fetchWithLimit does, so a QueryBuilder
    // result and a db.fetch() result are the same object in the same state --
    // INCLUDING `count`, which is the TRUE total for the filter via the shared
    // COUNT probe (ADR-0043), not rows-returned. Python's get() -> db.fetch()
    // and Ruby's get -> @db.fetch() already carried the true total; Node used to
    // leave it at the row count here, so `QueryBuilder.get().toPaginate()`
    // under-reported `total` while `db.fetch().toPaginate()` did not. The probe
    // is best-effort (undefined on any error -> falls back to rows.length) and
    // only runs when a limit was applied, so an unlimited get() is one query.
    const total = await probeTotal(this.db!, sql, queryParams, this.limitVal);
    return new DatabaseResult(
      rows as Record<string, unknown>[],
      undefined,
      total,
      this.limitVal,
      this.offsetVal,
      this.db!,
      sql,
    );
  }

  /**
   * Execute the query and return a single row.
   *
   * @returns A single row object, or null.
   */
  async first<T = Record<string, unknown>>(): Promise<T | null> {
    this.ensureDb();
    const sql = this.toSql();
    const allParams = [...this.selectParams, ...this.params, ...this.havingParams, ...this.orderByParams];

    return adapterFetchOne<T>(
      this.db!,
      sql,
      allParams.length > 0 ? allParams : undefined,
    );
  }

  /**
   * Execute the query and return the row count.
   *
   * @returns Number of matching rows.
   */
  async count(): Promise<number> {
    this.ensureDb();

    // Build a count query by replacing columns
    const original = this.columns;
    const originalSelectParams = this.selectParams;
    const originalOrder = this.orderByCols;
    const originalOrderParams = this.orderByParams;
    this.columns = ["COUNT(*) as cnt"];
    this.selectParams = [];
    this.orderByCols = [];
    this.orderByParams = [];
    const sql = this.toSql();
    this.columns = original;
    this.selectParams = originalSelectParams;
    this.orderByCols = originalOrder;
    this.orderByParams = originalOrderParams;

    const allParams = [...this.params, ...this.havingParams];

    const row = await adapterFetchOne<Record<string, unknown>>(
      this.db!,
      sql,
      allParams.length > 0 ? allParams : undefined,
    );

    if (!row) return 0;

    // Handle case-insensitive column names
    const cnt = row["cnt"] ?? row["CNT"] ?? 0;
    return Number(cnt);
  }

  /**
   * Check whether any matching rows exist.
   *
   * @returns True if at least one row matches.
   */
  async exists(): Promise<boolean> {
    return (await this.count()) > 0;
  }

  /**
   * Convert the fluent builder state into a MongoDB-compatible query document.
   *
   * @returns An object with filter, projection, sort, limit, skip (only non-empty keys).
   */
  toMongo(): {
    filter?: Record<string, unknown>;
    projection?: Record<string, number>;
    sort?: Record<string, 1 | -1>;
    limit?: number;
    skip?: number;
  } {
    const result: Record<string, unknown> = {};

    // -- projection --
    if (
      this.columns.length !== 1 ||
      this.columns[0] !== "*"
    ) {
      const projection: Record<string, number> = {};
      for (const col of this.columns) {
        projection[col.trim()] = 1;
      }
      result.projection = projection;
    }

    // -- filter --
    if (this.wheres.length > 0) {
      let paramIndex = 0;
      const andConditions: Record<string, unknown>[] = [];
      const orConditions: Record<string, unknown>[] = [];

      for (let i = 0; i < this.wheres.length; i++) {
        const [connector, condition] = this.wheres[i];
        const [mongoCond, newIndex] = this.parseConditionToMongo(
          condition,
          paramIndex,
        );
        paramIndex = newIndex;
        if (i === 0 || connector === "AND") {
          andConditions.push(mongoCond);
        } else {
          orConditions.push(mongoCond);
        }
      }

      if (orConditions.length > 0) {
        const andMerged = this.mergeMongoConditions(andConditions);
        const allBranches = [andMerged, ...orConditions];
        result.filter = { $or: allBranches };
      } else {
        result.filter = this.mergeMongoConditions(andConditions);
      }
    }

    // -- sort --
    if (this.orderByCols.length > 0) {
      const sort: Record<string, 1 | -1> = {};
      for (const expr of this.orderByCols) {
        const parts = expr.trim().split(/\s+/);
        const field = parts[0];
        const direction: 1 | -1 =
          parts.length > 1 && parts[1].toUpperCase() === "DESC" ? -1 : 1;
        sort[field] = direction;
      }
      result.sort = sort;
    }

    // -- limit / skip --
    if (this.limitVal !== undefined) {
      result.limit = this.limitVal;
    }
    if (this.offsetVal !== undefined) {
      result.skip = this.offsetVal;
    }

    return result as {
      filter?: Record<string, unknown>;
      projection?: Record<string, number>;
      sort?: Record<string, 1 | -1>;
      limit?: number;
      skip?: number;
    };
  }

  /**
   * Parse a single SQL condition string into a MongoDB filter object.
   */
  private parseConditionToMongo(
    condition: string,
    paramIndex: number,
  ): [Record<string, unknown>, number] {
    const cond = condition.trim();

    // IS NOT NULL
    let match = cond.match(/^(\w+)\s+IS\s+NOT\s+NULL$/i);
    if (match) {
      return [{ [match[1]]: { $exists: true, $ne: null } }, paramIndex];
    }

    // IS NULL
    match = cond.match(/^(\w+)\s+IS\s+NULL$/i);
    if (match) {
      return [{ [match[1]]: { $exists: false } }, paramIndex];
    }

    // NOT IN
    match = cond.match(/^(\w+)\s+NOT\s+IN\s*\(\s*\?\s*\)$/i);
    if (match) {
      const val = this.params[paramIndex] ?? [];
      const values = Array.isArray(val) ? val : [val];
      return [{ [match[1]]: { $nin: values } }, paramIndex + 1];
    }

    // IN
    match = cond.match(/^(\w+)\s+IN\s*\(\s*\?\s*\)$/i);
    if (match) {
      const val = this.params[paramIndex] ?? [];
      const values = Array.isArray(val) ? val : [val];
      return [{ [match[1]]: { $in: values } }, paramIndex + 1];
    }

    // LIKE
    match = cond.match(/^(\w+)\s+LIKE\s+\?$/i);
    if (match) {
      const val = String(this.params[paramIndex] ?? "");
      const pattern = val.replace(/%/g, ".*").replace(/_/g, ".");
      return [
        { [match[1]]: { $regex: pattern, $options: "i" } },
        paramIndex + 1,
      ];
    }

    // Comparison operators: >=, <=, <>, !=, >, <, =
    match = cond.match(/^(\w+)\s*(>=|<=|<>|!=|>|<|=)\s*\?$/);
    if (match) {
      const field = match[1];
      const op = match[2];
      const val = this.params[paramIndex] ?? null;

      const opMap: Record<string, string | null> = {
        "=": null,
        "!=": "$ne",
        "<>": "$ne",
        ">": "$gt",
        ">=": "$gte",
        "<": "$lt",
        "<=": "$lte",
      };

      const mongoOp = opMap[op];
      if (mongoOp === null || mongoOp === undefined) {
        return [{ [field]: val }, paramIndex + 1];
      }
      return [{ [field]: { [mongoOp]: val } }, paramIndex + 1];
    }

    // Canonical #5: no silent $where fallback. Previously an unparseable
    // condition was wrapped as `{ $where: <raw condition string> }` — a raw-JS
    // sink that is both injection-shaped (the WHERE string runs as JavaScript
    // on the MongoDB server) and silently different semantics from the SQL the
    // caller wrote. Fail loud instead: name the clause so the caller fixes it
    // rather than shipping a surprise $where.
    throw new Error(
      `QueryBuilder.toMongo(): cannot translate WHERE clause to a MongoDB ` +
        `filter: "${cond}". Supported forms: "<field> <op> ?" ` +
        `(=, !=, <>, >, >=, <, <=), "<field> LIKE ?", ` +
        `"<field> [NOT] IN (?)", "<field> IS [NOT] NULL". Rewrite the ` +
        `condition in one of those forms (toMongo() will not silently emit a ` +
        `raw $where JavaScript expression).`,
    );
  }

  /**
   * Merge multiple single-field mongo condition objects into one.
   * Uses $and if field keys conflict.
   */
  private mergeMongoConditions(
    conditions: Record<string, unknown>[],
  ): Record<string, unknown> {
    if (conditions.length === 1) {
      return conditions[0];
    }

    const merged: Record<string, unknown> = {};
    let hasConflict = false;

    outer: for (const cond of conditions) {
      for (const key of Object.keys(cond)) {
        if (key in merged) {
          hasConflict = true;
          break outer;
        }
        merged[key] = cond[key];
      }
    }

    if (hasConflict) {
      return { $and: conditions };
    }

    return merged;
  }

  /**
   * Build the WHERE clause from accumulated conditions.
   */
  private buildWhere(): string {
    const parts: string[] = [];
    for (let i = 0; i < this.wheres.length; i++) {
      const [connector, condition] = this.wheres[i];
      if (i === 0) {
        parts.push(condition);
      } else {
        parts.push(`${connector} ${condition}`);
      }
    }
    return parts.join(" ");
  }

  /**
   * Ensure a database adapter is available.
   */
  private ensureDb(): void {
    if (!this.db) {
      try {
        this.db = getAdapter();
      } catch {
        throw new Error("QueryBuilder: No database adapter provided.");
      }
    }
  }

  private engine(): string {
    this.ensureDb();
    return this.db!.getDatabaseType();
  }
}