import type pg from "pg";
import { getPool } from "../../db/pg-pool.js";
import {
  resolveDomain,
  type CreateMasterInput,
  type DomainConfig,
  type MasterFilter,
  type MasterPage,
  type MasterRecord,
  type MasterRepository,
  type UpdateMasterInput,
} from "./domain.js";

// ─── WhereBuilder ─────────────────────────────────────────────────────────────

class WhereBuilder {
  private conditions: string[] = [];
  private params: unknown[] = [];

  add(condition: string, ...values: unknown[]): this {
    // Replace positional placeholders $? with the next $N values
    let idx = this.params.length;
    const rewritten = condition.replace(/\?\?/g, () => {
      idx++;
      return `$${idx}`;
    });
    this.conditions.push(rewritten);
    this.params.push(...values);
    return this;
  }

  /** Append a raw condition with explicit $N references already set relative to current param count */
  addRaw(condition: string, ...values: unknown[]): this {
    this.conditions.push(condition);
    this.params.push(...values);
    return this;
  }

  build(): { clause: string; params: unknown[] } {
    const clause =
      this.conditions.length > 0
        ? "WHERE " + this.conditions.join(" AND ")
        : "";
    return { clause, params: [...this.params] };
  }

  clone(): WhereBuilder {
    const cloned = new WhereBuilder();
    cloned.conditions = [...this.conditions];
    cloned.params = [...this.params];
    return cloned;
  }
}

// ─── Row mapping ──────────────────────────────────────────────────────────────

function toRecord(row: Record<string, unknown>, cfg: DomainConfig): MasterRecord {
  const rec: MasterRecord = {
    id:           row["id"] as string,
    name:         row["name"] as string,
    code:         (row["code"] as string | null) ?? null,
    description:  (row["description"] as string | null) ?? null,
    isActive:     cfg.noIsActive ? true : (row["is_active"] as boolean),
    sortOrder:    (row["sort_order"] as number) ?? 0,
    metadata:     (row["metadata"] as Record<string, unknown>) ?? {},
    effectiveFrom:(row["effective_from"] as Date).toISOString(),
    effectiveTo:  row["effective_to"] != null ? (row["effective_to"] as Date).toISOString() : null,
    createdAt:    (row["created_at"] as Date).toISOString(),
    createdBy:    (row["created_by"] as string | null) ?? null,
    updatedAt:    cfg.noUpdatedAt ? null : (row["updated_at"] != null ? (row["updated_at"] as Date).toISOString() : null),
    updatedBy:    (row["updated_by"] as string | null) ?? null,
  };

  if (cfg.hierarchical || cfg.extraCols?.includes("parent_id")) {
    rec.parentId = (row["parent_id"] as string | null) ?? null;
  }
  if (cfg.extraCols?.includes("path")) {
    rec.path = (row["path"] as string | null) ?? null;
  }
  if (cfg.extraCols?.includes("depth")) {
    rec.depth = (row["depth"] as number | null) ?? null;
  }
  if (cfg.extraCols?.includes("area_type")) {
    rec.areaType = (row["area_type"] as string | null) ?? null;
  }
  if (cfg.extraCols?.includes("owner_id")) {
    rec.ownerId = (row["owner_id"] as string | null) ?? null;
  }
  if (cfg.extraCols?.includes("group_id")) {
    rec.groupId = (row["group_id"] as string | null) ?? null;
  }

  return rec;
}

function buildSelectCols(cfg: DomainConfig): string {
  const nameExpr = cfg.nameCol && cfg.nameCol !== "name"
    ? `${cfg.nameCol} AS name`
    : "name";
  const isActiveCol = cfg.noIsActive ? [] : ["is_active"];
  const auditCols   = cfg.noAuditCols  ? [] : ["created_by", "updated_by"];
  const updatedAtCol = cfg.noUpdatedAt ? [] : ["updated_at"];
  const base = [
    "id", nameExpr, "code", "description", ...isActiveCol, "sort_order",
    "metadata", "effective_from", "effective_to",
    "created_at", ...auditCols, ...updatedAtCol,
  ];
  const extra = cfg.extraCols ? [...cfg.extraCols] : [];
  if (cfg.hierarchical && !extra.includes("parent_id")) {
    extra.push("parent_id");
  }
  return [...base, ...extra].join(", ");
}

// ─── Repository implementation ────────────────────────────────────────────────

export class PgMasterRepository implements MasterRepository {
  private readonly pool: pg.Pool;

  constructor() {
    this.pool = getPool();
  }

  async list(domain: string, filter: MasterFilter): Promise<MasterPage> {
    const cfg = resolveDomain(domain);
    const cols = buildSelectCols(cfg);

    const nameCol = cfg.nameCol ?? "name";
    const wb = new WhereBuilder();
    wb.addRaw("deleted_at IS NULL");

    if (!cfg.noIsActive) {
      if (filter.isActive === undefined || filter.isActive === true) {
        wb.addRaw("is_active = TRUE");
      } else if (filter.isActive === false) {
        wb.addRaw("is_active = FALSE");
      }
    }

    if (filter.q) {
      const likeVal = `%${filter.q.toLowerCase()}%`;
      const nextIdx = wb["params"].length + 1;
      wb.addRaw(
        `(LOWER(${nameCol}) LIKE $${nextIdx} OR LOWER(COALESCE(code, '')) LIKE $${nextIdx})`,
        likeVal
      );
    }

    if (filter.parentId && cfg.hierarchical) {
      const nextIdx = wb["params"].length + 1;
      wb.addRaw(`parent_id = $${nextIdx}`, filter.parentId);
    }

    const { clause, params } = wb.build();

    // COUNT
    const countResult = await this.pool.query<{ count: string }>(
      `SELECT COUNT(*) AS count FROM ${cfg.table} ${clause}`,
      params
    );
    const total = parseInt(countResult.rows[0]?.count ?? "0", 10);

    // Pagination params
    const page   = filter.page;
    const limit  = filter.limit;
    const offset = (page - 1) * limit;

    const dataResult = await this.pool.query<Record<string, unknown>>(
      `SELECT ${cols} FROM ${cfg.table} ${clause}
       ORDER BY sort_order ASC, ${nameCol} ASC
       LIMIT $${params.length + 1} OFFSET $${params.length + 2}`,
      [...params, limit, offset]
    );

    return {
      items: dataResult.rows.map((row) => toRecord(row, cfg)),
      total,
      page,
      limit,
    };
  }

  async getById(domain: string, id: string): Promise<MasterRecord | null> {
    const cfg = resolveDomain(domain);
    const cols = buildSelectCols(cfg);

    const result = await this.pool.query<Record<string, unknown>>(
      `SELECT ${cols} FROM ${cfg.table} WHERE id = $1 AND deleted_at IS NULL`,
      [id]
    );
    if (result.rowCount === 0) return null;
    return toRecord(result.rows[0]!, cfg);
  }

  async create(domain: string, data: CreateMasterInput): Promise<MasterRecord> {
    const cfg = resolveDomain(domain);

    const colNames: string[] = ["name"];
    const colValues: unknown[] = [data.name];

    function addCol(col: string, val: unknown): void {
      colNames.push(col);
      colValues.push(val);
    }

    if (data.code         !== undefined) addCol("code",          data.code);
    if (data.description  !== undefined) addCol("description",   data.description);
    if (data.sortOrder    !== undefined) addCol("sort_order",     data.sortOrder);
    if (data.isActive     !== undefined) addCol("is_active",      data.isActive);
    if (data.metadata     !== undefined) addCol("metadata",       JSON.stringify(data.metadata));
    if (data.effectiveFrom!== undefined) addCol("effective_from", data.effectiveFrom);
    if (data.effectiveTo  !== undefined) addCol("effective_to",   data.effectiveTo);

    // Hierarchical / domain-specific
    if (cfg.hierarchical && data.parentId !== undefined) addCol("parent_id", data.parentId);
    if (cfg.extraCols?.includes("owner_id") && data.ownerId !== undefined) addCol("owner_id",   data.ownerId);
    if (cfg.extraCols?.includes("group_id") && data.groupId !== undefined) addCol("group_id",   data.groupId);
    if (cfg.extraCols?.includes("path")     && data.path    !== undefined) addCol("path",       data.path);
    if (cfg.extraCols?.includes("depth")    && data.depth   !== undefined) addCol("depth",      data.depth);
    if (cfg.extraCols?.includes("area_type")&& data.areaType!== undefined) addCol("area_type",  data.areaType);

    const placeholders = colNames.map((_, i) => `$${i + 1}`).join(", ");
    const cols = buildSelectCols(cfg);

    const result = await this.pool.query<Record<string, unknown>>(
      `INSERT INTO ${cfg.table} (${colNames.join(", ")})
       VALUES (${placeholders})
       RETURNING ${cols}`,
      colValues
    );
    return toRecord(result.rows[0]!, cfg);
  }

  async update(domain: string, id: string, data: UpdateMasterInput): Promise<MasterRecord | null> {
    const cfg = resolveDomain(domain);

    const setClauses: string[] = [];
    const setValues: unknown[] = [];

    function addSet(col: string, val: unknown): void {
      setValues.push(val);
      setClauses.push(`${col} = $${setValues.length}`);
    }

    if (data.name         !== undefined) addSet("name",          data.name);
    if (data.code         !== undefined) addSet("code",          data.code);
    if (data.description  !== undefined) addSet("description",   data.description);
    if (data.sortOrder    !== undefined) addSet("sort_order",     data.sortOrder);
    if (data.isActive     !== undefined) addSet("is_active",      data.isActive);
    if (data.metadata     !== undefined) addSet("metadata",       JSON.stringify(data.metadata));
    if (data.effectiveFrom!== undefined) addSet("effective_from", data.effectiveFrom);
    if (data.effectiveTo  !== undefined) addSet("effective_to",   data.effectiveTo);

    if (cfg.hierarchical && data.parentId !== undefined) addSet("parent_id", data.parentId);
    if (cfg.extraCols?.includes("owner_id")  && data.ownerId  !== undefined) addSet("owner_id",  data.ownerId);
    if (cfg.extraCols?.includes("group_id")  && data.groupId  !== undefined) addSet("group_id",  data.groupId);
    if (cfg.extraCols?.includes("path")      && data.path     !== undefined) addSet("path",      data.path);
    if (cfg.extraCols?.includes("depth")     && data.depth    !== undefined) addSet("depth",     data.depth);
    if (cfg.extraCols?.includes("area_type") && data.areaType !== undefined) addSet("area_type", data.areaType);

    if (setClauses.length === 0) {
      // Nothing to update — just return current record
      return this.getById(domain, id);
    }

    // Always bump updated_at
    setClauses.push("updated_at = NOW()");

    const idIdx = setValues.length + 1;
    const cols  = buildSelectCols(cfg);

    const result = await this.pool.query<Record<string, unknown>>(
      `UPDATE ${cfg.table}
       SET ${setClauses.join(", ")}
       WHERE id = $${idIdx} AND deleted_at IS NULL
       RETURNING ${cols}`,
      [...setValues, id]
    );
    if ((result.rowCount ?? 0) === 0) return null;
    return toRecord(result.rows[0]!, cfg);
  }

  async softDelete(domain: string, id: string): Promise<boolean> {
    const cfg = resolveDomain(domain);

    const result = await this.pool.query(
      `UPDATE ${cfg.table}
       SET deleted_at = NOW()
       WHERE id = $1 AND deleted_at IS NULL`,
      [id]
    );
    return (result.rowCount ?? 0) > 0;
  }
}
