/**
 * Search-box.ai
 * Author: Simon-Pierre Boucher
 * Contact: contact@spboucher.ai
 * File: packages/db/src/repositories.ts
 * Description: Data-access repositories for sessions, events and research state.
 */

import type {
  Claim,
  ClaimStatus,
  Contradiction,
  Evidence,
  ResearchSession,
  SessionStatus,
  Source,
  Stance
} from "@search-box/shared";
import { newId } from "@search-box/shared";
import type { ResearchEvent, ResearchEventPayload } from "@search-box/events";
import { getPool } from "./pool.js";

/* ------------------------------- row mappers ------------------------------ */

function rowToSession(r: Record<string, unknown>): ResearchSession {
  return {
    id: r.id as string,
    question: r.question as string,
    status: r.status as SessionStatus,
    answer: (r.answer as string) ?? null,
    error: (r.error as string) ?? null,
    objectives: (r.objectives as string[]) ?? [],
    budgets: (r.budgets as Record<string, number>) ?? {},
    meta: (r.meta as Record<string, unknown>) ?? {},
    createdAt: (r.created_at as Date).toISOString(),
    updatedAt: (r.updated_at as Date).toISOString()
  };
}

function rowToSource(r: Record<string, unknown>): Source {
  return {
    id: r.id as string,
    sessionId: r.session_id as string,
    url: r.url as string,
    title: (r.title as string) ?? null,
    domain: r.domain as string,
    status: r.status as Source["status"],
    citationIndex: (r.citation_index as number) ?? null,
    metadata: (r.metadata as Record<string, unknown>) ?? {},
    createdAt: (r.created_at as Date).toISOString()
  };
}

function rowToClaim(r: Record<string, unknown>): Claim {
  return {
    id: r.id as string,
    sessionId: r.session_id as string,
    text: r.text as string,
    status: r.status as ClaimStatus,
    confidence: r.confidence as number,
    publicReason: (r.public_reason as string) ?? null,
    createdAt: (r.created_at as Date).toISOString(),
    updatedAt: (r.updated_at as Date).toISOString()
  };
}

function rowToEvidence(r: Record<string, unknown>): Evidence {
  return {
    id: r.id as string,
    sessionId: r.session_id as string,
    sourceId: r.source_id as string,
    claimId: (r.claim_id as string) ?? null,
    quote: r.quote as string,
    note: (r.note as string) ?? null,
    stance: r.stance as Stance,
    createdAt: (r.created_at as Date).toISOString()
  };
}

function rowToContradiction(r: Record<string, unknown>): Contradiction {
  return {
    id: r.id as string,
    sessionId: r.session_id as string,
    claimId: r.claim_id as string,
    description: r.description as string,
    evidenceIds: (r.evidence_ids as string[]) ?? [],
    resolution: (r.resolution as string) ?? null,
    createdAt: (r.created_at as Date).toISOString()
  };
}

/* -------------------------------- sessions -------------------------------- */

export const sessions = {
  async create(question: string, budgets: Record<string, number>): Promise<ResearchSession> {
    const id = newId("ses");
    const { rows } = await getPool().query(
      `INSERT INTO research_sessions (id, question, budgets) VALUES ($1, $2, $3) RETURNING *`,
      [id, question, JSON.stringify(budgets)]
    );
    return rowToSession(rows[0]);
  },

  async get(id: string): Promise<ResearchSession | null> {
    const { rows } = await getPool().query(`SELECT * FROM research_sessions WHERE id = $1`, [id]);
    return rows[0] ? rowToSession(rows[0]) : null;
  },

  async list(limit = 30): Promise<ResearchSession[]> {
    const { rows } = await getPool().query(
      `SELECT * FROM research_sessions ORDER BY created_at DESC LIMIT $1`,
      [limit]
    );
    return rows.map(rowToSession);
  },

  async setStatus(id: string, status: SessionStatus): Promise<void> {
    await getPool().query(
      `UPDATE research_sessions SET status = $2, updated_at = now() WHERE id = $1`,
      [id, status]
    );
  },

  async setObjectives(id: string, objectives: string[]): Promise<void> {
    await getPool().query(
      `UPDATE research_sessions SET objectives = $2, updated_at = now() WHERE id = $1`,
      [id, JSON.stringify(objectives)]
    );
  },

  async complete(id: string, answer: string): Promise<void> {
    await getPool().query(
      `UPDATE research_sessions SET status = 'completed', answer = $2, updated_at = now() WHERE id = $1`,
      [id, answer]
    );
  },

  async fail(id: string, error: string): Promise<void> {
    await getPool().query(
      `UPDATE research_sessions SET status = 'failed', error = $2, updated_at = now() WHERE id = $1`,
      [id, error]
    );
  }
};

/* --------------------------------- events --------------------------------- */

export const events = {
  async append(sessionId: string, payload: ResearchEventPayload): Promise<ResearchEvent> {
    const id = newId("evt");
    const { rows } = await getPool().query(
      `INSERT INTO research_events (id, session_id, type, payload)
       VALUES ($1, $2, $3, $4) RETURNING seq, created_at`,
      [id, sessionId, payload.type, JSON.stringify(payload)]
    );
    return {
      id,
      sessionId,
      seq: Number(rows[0].seq),
      createdAt: (rows[0].created_at as Date).toISOString(),
      payload
    };
  },

  async listAfter(sessionId: string, afterSeq: number, limit = 500): Promise<ResearchEvent[]> {
    const { rows } = await getPool().query(
      `SELECT * FROM research_events WHERE session_id = $1 AND seq > $2 ORDER BY seq ASC LIMIT $3`,
      [sessionId, afterSeq, limit]
    );
    return rows.map((r: Record<string, unknown>) => ({
      id: r.id as string,
      sessionId: r.session_id as string,
      seq: Number(r.seq),
      createdAt: (r.created_at as Date).toISOString(),
      payload: r.payload as ResearchEventPayload
    }));
  }
};

/* --------------------------------- sources -------------------------------- */

export const sources = {
  async upsertFound(
    sessionId: string,
    url: string,
    title: string | null,
    metadata: Record<string, unknown> = {}
  ): Promise<Source> {
    const id = newId("src");
    const domain = safeDomain(url);
    const { rows } = await getPool().query(
      `INSERT INTO sources (id, session_id, url, title, domain, metadata)
       VALUES ($1, $2, $3, $4, $5, $6)
       ON CONFLICT (session_id, url)
       DO UPDATE SET title = COALESCE(sources.title, EXCLUDED.title)
       RETURNING *`,
      [id, sessionId, url, title, domain, JSON.stringify(metadata)]
    );
    return rowToSource(rows[0]);
  },

  async markFetched(id: string, title: string | null, content: string): Promise<Source> {
    const { rows } = await getPool().query(
      `UPDATE sources SET status = 'fetched', title = COALESCE($2, title), content = $3 WHERE id = $1 RETURNING *`,
      [id, title, content]
    );
    return rowToSource(rows[0]);
  },

  async markFailed(id: string): Promise<Source> {
    const { rows } = await getPool().query(
      `UPDATE sources SET status = 'failed' WHERE id = $1 RETURNING *`,
      [id]
    );
    return rowToSource(rows[0]);
  },

  async get(id: string): Promise<Source | null> {
    const { rows } = await getPool().query(`SELECT * FROM sources WHERE id = $1`, [id]);
    return rows[0] ? rowToSource(rows[0]) : null;
  },

  async getContent(id: string): Promise<string | null> {
    const { rows } = await getPool().query(`SELECT content FROM sources WHERE id = $1`, [id]);
    return rows[0]?.content ?? null;
  },

  async listBySession(sessionId: string): Promise<Source[]> {
    const { rows } = await getPool().query(
      `SELECT * FROM sources WHERE session_id = $1 ORDER BY created_at ASC`,
      [sessionId]
    );
    return rows.map(rowToSource);
  },

  /** Assign stable citation indices (1..n) to every source that has evidence. */
  async assignCitationIndices(sessionId: string): Promise<Source[]> {
    const { rows } = await getPool().query(
      `WITH cited AS (
         SELECT DISTINCT s.id, min(e.created_at) AS first_use
         FROM sources s JOIN evidence e ON e.source_id = s.id
         WHERE s.session_id = $1
         GROUP BY s.id
       ), numbered AS (
         SELECT id, row_number() OVER (ORDER BY first_use ASC) AS idx FROM cited
       )
       UPDATE sources SET citation_index = numbered.idx
       FROM numbered WHERE sources.id = numbered.id
       RETURNING sources.*`,
      [sessionId]
    );
    return rows.map(rowToSource).sort((a: Source, b: Source) => (a.citationIndex ?? 0) - (b.citationIndex ?? 0));
  }
};

/* --------------------------------- claims --------------------------------- */

export const claims = {
  async add(sessionId: string, text: string, confidence: number): Promise<Claim> {
    const id = newId("clm");
    const { rows } = await getPool().query(
      `INSERT INTO claims (id, session_id, text, confidence) VALUES ($1, $2, $3, $4) RETURNING *`,
      [id, sessionId, text, confidence]
    );
    return rowToClaim(rows[0]);
  },

  async update(
    id: string,
    patch: { status?: ClaimStatus; confidence?: number; publicReason?: string }
  ): Promise<Claim> {
    const { rows } = await getPool().query(
      `UPDATE claims SET
         status = COALESCE($2, status),
         confidence = COALESCE($3, confidence),
         public_reason = COALESCE($4, public_reason),
         updated_at = now()
       WHERE id = $1 RETURNING *`,
      [id, patch.status ?? null, patch.confidence ?? null, patch.publicReason ?? null]
    );
    return rowToClaim(rows[0]);
  },

  async get(id: string): Promise<Claim | null> {
    const { rows } = await getPool().query(`SELECT * FROM claims WHERE id = $1`, [id]);
    return rows[0] ? rowToClaim(rows[0]) : null;
  },

  async listBySession(sessionId: string): Promise<Claim[]> {
    const { rows } = await getPool().query(
      `SELECT * FROM claims WHERE session_id = $1 ORDER BY created_at ASC`,
      [sessionId]
    );
    return rows.map(rowToClaim);
  }
};

/* -------------------------------- evidence -------------------------------- */

export const evidence = {
  async add(
    sessionId: string,
    sourceId: string,
    quote: string,
    stance: Stance,
    claimId: string | null,
    note: string | null
  ): Promise<Evidence> {
    const id = newId("ev");
    const { rows } = await getPool().query(
      `INSERT INTO evidence (id, session_id, source_id, claim_id, quote, note, stance)
       VALUES ($1, $2, $3, $4, $5, $6, $7) RETURNING *`,
      [id, sessionId, sourceId, claimId, quote, note, stance]
    );
    return rowToEvidence(rows[0]);
  },

  async listBySession(sessionId: string): Promise<Evidence[]> {
    const { rows } = await getPool().query(
      `SELECT * FROM evidence WHERE session_id = $1 ORDER BY created_at ASC`,
      [sessionId]
    );
    return rows.map(rowToEvidence);
  }
};

/* ------------------------------ contradictions ----------------------------- */

export const contradictions = {
  async add(
    sessionId: string,
    claimId: string,
    description: string,
    evidenceIds: string[]
  ): Promise<Contradiction> {
    const id = newId("ctr");
    const { rows } = await getPool().query(
      `INSERT INTO contradictions (id, session_id, claim_id, description, evidence_ids)
       VALUES ($1, $2, $3, $4, $5) RETURNING *`,
      [id, sessionId, claimId, description, JSON.stringify(evidenceIds)]
    );
    return rowToContradiction(rows[0]);
  },

  async listBySession(sessionId: string): Promise<Contradiction[]> {
    const { rows } = await getPool().query(
      `SELECT * FROM contradictions WHERE session_id = $1 ORDER BY created_at ASC`,
      [sessionId]
    );
    return rows.map(rowToContradiction);
  }
};

/* --------------------------------- helpers -------------------------------- */

function safeDomain(url: string): string {
  try {
    return new URL(url).hostname;
  } catch {
    return "unknown";
  }
}