import { randomUUID } from "node:crypto";
import type pg from "pg";
import { getPool } from "../../db/pg-pool.js";
import type {
  CreateFormInput,
  FormRecord,
  FormRepository,
  FormVersionRecord,
  FormWithVersions,
  PublishVersionInput,
  SubmissionFilter,
  SubmissionInput,
  SubmissionPage,
  SubmissionRecord,
  UpdateFormInput,
} from "./domain.js";

// ─── Row mappers ──────────────────────────────────────────────────────────────

function toFormRecord(row: Record<string, unknown>): FormRecord {
  return {
    id:               row["id"] as string,
    code:             row["code"] as string,
    displayName:      row["display_name"] as string,
    description:      (row["description"] as string | null) ?? null,
    isActive:         row["is_active"] as boolean,
    currentVersionId: (row["current_version_id"] as string | null) ?? null,
    createdAt:        (row["created_at"] as Date).toISOString(),
    updatedAt:        (row["updated_at"] as Date).toISOString(),
  };
}

function toVersionRecord(row: Record<string, unknown>): FormVersionRecord {
  return {
    id:             row["id"] as string,
    formId:         row["form_id"] as string,
    semver:         row["semver"] as string,
    schemaChecksum: row["schema_checksum"] as string,
    surveyJson:     row["survey_json"] as Record<string, unknown>,
    questionIndex:  (row["question_index"] as Record<string, unknown>) ?? {},
    isPublished:    row["is_published"] as boolean,
    publishedAt:    row["published_at"] != null
                      ? (row["published_at"] as Date).toISOString()
                      : null,
    createdAt:      (row["created_at"] as Date).toISOString(),
  };
}

function toSubmissionRecord(row: Record<string, unknown>): SubmissionRecord {
  return {
    id:              row["id"] as string,
    submissionCode:  row["submission_code"] as string,
    formId:          row["form_id"] as string,
    formVersionId:   row["form_version_id"] as string,
    moduleCode:      row["module_code"] as string,
    submittedBy:     (row["submitted_by"] as string | null) ?? null,
    status:          row["status"] as string,
    answers:         (row["answers"] as Record<string, unknown>) ?? {},
    submittedAt:     (row["submitted_at"] as Date).toISOString(),
    createdAt:       (row["created_at"] as Date).toISOString(),
  };
}

// ─── Repository ───────────────────────────────────────────────────────────────

export class PgFormRepository implements FormRepository {
  private readonly pool: pg.Pool;

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

  async listForms(): Promise<FormRecord[]> {
    const result = await this.pool.query<Record<string, unknown>>(
      `SELECT id, code, display_name, description, is_active, current_version_id,
              created_at, updated_at
       FROM forms.form
       WHERE deleted_at IS NULL
       ORDER BY created_at DESC`
    );
    return result.rows.map(toFormRecord);
  }

  async getFormByCode(code: string): Promise<FormWithVersions | null> {
    const formResult = await this.pool.query<Record<string, unknown>>(
      `SELECT id, code, display_name, description, is_active, current_version_id,
              created_at, updated_at
       FROM forms.form
       WHERE code = $1 AND deleted_at IS NULL`,
      [code]
    );
    if (formResult.rowCount === 0) return null;
    const formRow = formResult.rows[0]!;
    const form = toFormRecord(formRow);

    const versionsResult = await this.pool.query<Record<string, unknown>>(
      `SELECT id, form_id, semver, schema_checksum, survey_json, question_index,
              is_published, published_at, created_at
       FROM forms.form_version
       WHERE form_id = $1
       ORDER BY created_at DESC`,
      [form.id]
    );
    const versions = versionsResult.rows.map(toVersionRecord);

    // Live version = most-recently published
    const liveVersion =
      versions
        .filter((v) => v.isPublished)
        .sort((a, b) => {
          const aTime = a.publishedAt ? new Date(a.publishedAt).getTime() : 0;
          const bTime = b.publishedAt ? new Date(b.publishedAt).getTime() : 0;
          return bTime - aTime;
        })[0] ?? null;

    return { ...form, versions, liveVersion };
  }

  async getFormVersion(formId: string, semver: string): Promise<FormVersionRecord | null> {
    const result = await this.pool.query<Record<string, unknown>>(
      `SELECT id, form_id, semver, schema_checksum, survey_json, question_index,
              is_published, published_at, created_at
       FROM forms.form_version
       WHERE form_id = $1 AND semver = $2`,
      [formId, semver]
    );
    if (result.rowCount === 0) return null;
    return toVersionRecord(result.rows[0]!);
  }

  async createForm(data: CreateFormInput): Promise<FormRecord> {
    // Resolve module_id (NOT NULL constraint) before opening the transaction
    const moduleCode = data.moduleCode ?? "audit";
    const moduleRow = await this.pool.query<{ id: string }>(
      `SELECT id FROM platform.module WHERE code = $1 AND is_active = TRUE LIMIT 1`,
      [moduleCode]
    );
    if (moduleRow.rowCount === 0) {
      throw new Error(`Module "${moduleCode}" not found in platform.module`);
    }
    const moduleId = moduleRow.rows[0]!.id;

    const client = await this.pool.connect();
    try {
      await client.query("BEGIN");

      // 1. Insert form
      const formResult = await client.query<Record<string, unknown>>(
        `INSERT INTO forms.form (module_id, code, display_name, description, is_active)
         VALUES ($1, $2, $3, $4, TRUE)
         RETURNING id, code, display_name, description, is_active,
                   current_version_id, created_at, updated_at`,
        [moduleId, data.code, data.displayName, data.description ?? null]
      );
      const formRow = formResult.rows[0]!;
      const formId = formRow["id"] as string;
      const semver = data.semver ?? "1.0.0";

      // 2. Insert initial version
      const versionResult = await client.query<Record<string, unknown>>(
        `INSERT INTO forms.form_version
           (form_id, semver, survey_json, question_index, schema_checksum,
            is_published, published_at)
         VALUES ($1, $2, $3, $4, $5, TRUE, NOW())
         RETURNING id`,
        [
          formId,
          semver,
          JSON.stringify(data.surveyJson),
          "{}",
          data.schemaChecksum,
        ]
      );
      const versionId = versionResult.rows[0]!["id"] as string;

      // 3. Link current_version_id
      await client.query(
        `UPDATE forms.form
         SET current_version_id = $1, updated_at = NOW()
         WHERE id = $2`,
        [versionId, formId]
      );

      await client.query("COMMIT");

      // Return fresh form row with current_version_id set
      const refreshed = await this.pool.query<Record<string, unknown>>(
        `SELECT id, code, display_name, description, is_active, current_version_id,
                created_at, updated_at
         FROM forms.form WHERE id = $1`,
        [formId]
      );
      return toFormRecord(refreshed.rows[0]!);
    } catch (err) {
      await client.query("ROLLBACK");
      throw err;
    } finally {
      client.release();
    }
  }

  async updateForm(code: string, data: UpdateFormInput): Promise<FormWithVersions | null> {
    const client = await this.pool.connect();
    try {
      await client.query("BEGIN");

      // 1. Update form metadata (only fields provided)
      const sets: string[] = ["updated_at = NOW()"];
      const vals: unknown[] = [];
      const push = (col: string, v: unknown) => { vals.push(v); sets.push(`${col} = $${vals.length}`); };

      if (data.displayName !== undefined) push("display_name", data.displayName);
      if (data.description  !== undefined) push("description",  data.description);
      if (data.isActive     !== undefined) push("is_active",    data.isActive);

      vals.push(code);
      const formResult = await client.query<Record<string, unknown>>(
        `UPDATE forms.form SET ${sets.join(", ")}
         WHERE code = $${vals.length} AND deleted_at IS NULL
         RETURNING id, code, display_name, description, is_active,
                   current_version_id, created_at, updated_at`,
        vals
      );
      if ((formResult.rowCount ?? 0) === 0) { await client.query("ROLLBACK"); return null; }
      const formRow = formResult.rows[0]!;
      const formId  = formRow["id"] as string;

      // 2. If surveyJson provided — upsert draft version
      if (data.surveyJson !== undefined) {
        const checksum = data.schemaChecksum ?? "";
        // Find existing unpublished draft
        const draftResult = await client.query<{ id: string; semver: string }>(
          `SELECT id, semver FROM forms.form_version
           WHERE form_id = $1 AND is_published = FALSE
           ORDER BY created_at DESC LIMIT 1`,
          [formId]
        );

        if (draftResult.rowCount! > 0) {
          // Update existing draft in-place
          await client.query(
            `UPDATE forms.form_version
             SET survey_json = $1, schema_checksum = $2,
                 semver = COALESCE($3, semver)
             WHERE id = $4`,
            [JSON.stringify(data.surveyJson), checksum, data.semver ?? null, draftResult.rows[0]!.id]
          );
        } else {
          // No draft — insert new draft version
          const semver = data.semver ?? "draft";
          const newVer = await client.query<{ id: string }>(
            `INSERT INTO forms.form_version
               (form_id, semver, survey_json, question_index, schema_checksum, is_published)
             VALUES ($1, $2, $3, '{}', $4, FALSE)
             RETURNING id`,
            [formId, semver, JSON.stringify(data.surveyJson), checksum]
          );
          // Point current_version_id at draft only if form has no live version yet
          if (!formRow["current_version_id"]) {
            await client.query(
              `UPDATE forms.form SET current_version_id = $1 WHERE id = $2`,
              [newVer.rows[0]!.id, formId]
            );
          }
        }
      }

      await client.query("COMMIT");
    } catch (err) {
      await client.query("ROLLBACK");
      throw err;
    } finally {
      client.release();
    }

    return this.getFormByCode(code);
  }

  async softDeleteForm(code: string): Promise<boolean> {
    const result = await this.pool.query(
      `UPDATE forms.form SET deleted_at = NOW(), updated_at = NOW()
       WHERE code = $1 AND deleted_at IS NULL`,
      [code]
    );
    return (result.rowCount ?? 0) > 0;
  }

  async publishVersion(formId: string, data: PublishVersionInput): Promise<FormVersionRecord> {
    const client = await this.pool.connect();
    try {
      await client.query("BEGIN");

      // 1. Insert new version
      let versionResult: pg.QueryResult<Record<string, unknown>>;
      try {
        versionResult = await client.query<Record<string, unknown>>(
          `INSERT INTO forms.form_version
             (form_id, semver, survey_json, question_index, schema_checksum,
              is_published, published_at, published_by)
           VALUES ($1, $2, $3, $4, $5, TRUE, NOW(), $6)
           RETURNING id, form_id, semver, schema_checksum, survey_json,
                     question_index, is_published, published_at, created_at`,
          [
            formId,
            data.semver,
            JSON.stringify(data.surveyJson),
            JSON.stringify(data.questionIndex),
            data.schemaChecksum,
            data.publishedBy ?? null,
          ]
        );
      } catch (err: unknown) {
        // Unique constraint violation on (form_id, semver)
        const pgErr = err as { code?: string };
        if (pgErr.code === "23505") {
          throw new Error(
            `Version ${data.semver} already exists for this form. Choose a different semver.`
          );
        }
        throw err;
      }
      const version = toVersionRecord(versionResult.rows[0]!);

      // 2. Update form's current_version_id
      await client.query(
        `UPDATE forms.form
         SET current_version_id = $1, updated_at = NOW()
         WHERE id = $2`,
        [version.id, formId]
      );

      await client.query("COMMIT");
      return version;
    } catch (err) {
      await client.query("ROLLBACK");
      throw err;
    } finally {
      client.release();
    }
  }

  async saveSubmission(data: SubmissionInput): Promise<SubmissionRecord> {
    const submissionCode = `SUB_${randomUUID().replace(/-/g, "").slice(0, 16).toUpperCase()}`;

    const result = await this.pool.query<Record<string, unknown>>(
      `INSERT INTO forms.submission
         (submitted_at, form_id, form_version_id, schema_checksum, module_code,
          submitted_by, submission_code, status, answers)
       VALUES (NOW(), $1, $2, $3, $4, $5, $6, 'draft', $7)
       RETURNING id, submission_code, form_id, form_version_id, module_code,
                 submitted_by, status, answers, submitted_at, created_at`,
      [
        data.formId,
        data.formVersionId,
        data.schemaChecksum,
        data.moduleCode,
        data.submittedBy ?? null,
        submissionCode,
        JSON.stringify(data.answers),
      ]
    );
    return toSubmissionRecord(result.rows[0]!);
  }

  async listSubmissions(formId: string, filter: SubmissionFilter): Promise<SubmissionPage> {
    const conditions: string[] = ["form_id = $1", "deleted_at IS NULL"];
    const params: unknown[] = [formId];

    if (filter.status) {
      params.push(filter.status);
      conditions.push(`status = $${params.length}`);
    }

    const whereClause = "WHERE " + conditions.join(" AND ");

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

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

    const dataResult = await this.pool.query<Record<string, unknown>>(
      `SELECT id, submission_code, form_id, form_version_id, module_code,
              submitted_by, status, answers, submitted_at, created_at
       FROM forms.submission
       ${whereClause}
       ORDER BY submitted_at DESC
       LIMIT $${params.length + 1} OFFSET $${params.length + 2}`,
      [...params, limit, offset]
    );

    return {
      items: dataResult.rows.map(toSubmissionRecord),
      total,
      page,
      limit,
    };
  }
}
