/**
 * WorthDoing.ai
 * Author: Simon-Pierre Boucher
 * Contact: contact@spboucher.ai
 * File: src/lib/db/schema.ts
 * Description: Drizzle PostgreSQL schema — investigations, hypotheses, evidence, opportunities, and the full agent data model.
 */
import {
  pgTable,
  uuid,
  text,
  timestamp,
  integer,
  real,
  boolean,
  jsonb,
  bigserial,
  uniqueIndex,
  index,
  primaryKey,
} from "drizzle-orm/pg-core";

/* ----------------------------- users ----------------------------- */

export const users = pgTable("users", {
  id: uuid("id").primaryKey().defaultRandom(),
  email: text("email").unique(),
  name: text("name"),
  createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
});

/* ------------------------- investigations ------------------------ */

export type BudgetLimits = {
  maxAgentSteps: number;
  maxSearches: number;
  maxScrapes: number;
  maxCrawls: number;
  maxWallTimeMs: number;
};

export type BudgetUsed = {
  agentSteps: number;
  searches: number;
  scrapes: number;
  crawls: number;
  inputTokens: number;
  outputTokens: number;
  costUsd: number;
};

export const investigations = pgTable(
  "investigations",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    userId: uuid("user_id").references(() => users.id),
    objective: text("objective").notNull(),
    status: text("status", {
      enum: ["pending", "running", "completed", "failed", "cancelled"],
    })
      .notNull()
      .default("pending"),
    phase: text("phase", {
      enum: ["scouting", "investigating", "skeptic", "synthesizing", "done"],
    })
      .notNull()
      .default("scouting"),
    stopReason: text("stop_reason"), // structured: objective_met | budget_steps | budget_wall_time | agent_finished | error | cancelled
    conclusion: text("conclusion"),
    outcome: text("outcome", {
      enum: ["opportunities_found", "insufficient_evidence", "no_strong_opportunity"],
    }),
    budget: jsonb("budget").$type<BudgetLimits>().notNull(),
    budgetUsed: jsonb("budget_used").$type<BudgetUsed>().notNull(),
    promptVersion: text("prompt_version").notNull(),
    toolSchemaVersion: text("tool_schema_version").notNull(),
    model: text("model").notNull(),
    error: text("error"),
    createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
    startedAt: timestamp("started_at", { withTimezone: true }),
    completedAt: timestamp("completed_at", { withTimezone: true }),
  },
  (t) => [index("investigations_status_idx").on(t.status), index("investigations_created_idx").on(t.createdAt)],
);

export const investigationSteps = pgTable(
  "investigation_steps",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    investigationId: uuid("investigation_id")
      .notNull()
      .references(() => investigations.id, { onDelete: "cascade" }),
    stepNumber: integer("step_number").notNull(),
    model: text("model").notNull(),
    inputTokens: integer("input_tokens").notNull().default(0),
    outputTokens: integer("output_tokens").notNull().default(0),
    costUsd: real("cost_usd").notNull().default(0),
    latencyMs: integer("latency_ms").notNull().default(0),
    toolName: text("tool_name"),
    toolArgs: jsonb("tool_args"),
    toolResultSummary: text("tool_result_summary"),
    decisionSummary: text("decision_summary"),
    error: text("error"),
    createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [index("steps_investigation_idx").on(t.investigationId, t.stepNumber)],
);

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

export const agentEvents = pgTable(
  "agent_events",
  {
    id: bigserial("id", { mode: "number" }).primaryKey(),
    investigationId: uuid("investigation_id")
      .notNull()
      .references(() => investigations.id, { onDelete: "cascade" }),
    seq: integer("seq").notNull(),
    type: text("type").notNull(),
    payload: jsonb("payload").notNull(),
    createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [uniqueIndex("agent_events_seq_idx").on(t.investigationId, t.seq)],
);

/* ----------------------------- searches -------------------------- */

export const searches = pgTable(
  "searches",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    investigationId: uuid("investigation_id")
      .notNull()
      .references(() => investigations.id, { onDelete: "cascade" }),
    query: text("query").notNull(),
    intent: text("intent"), // explore | verify | falsify | market | technical
    provider: text("provider").notNull().default("firecrawl"),
    resultCount: integer("result_count").notNull().default(0),
    results: jsonb("results").notNull(), // [{url,title,description}]
    createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [index("searches_investigation_idx").on(t.investigationId)],
);

/* ------------------------ sources & contents --------------------- */

export const sources = pgTable(
  "sources",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    url: text("url").notNull(),
    canonicalUrl: text("canonical_url").notNull(),
    domain: text("domain").notNull(),
    title: text("title"),
    description: text("description"),
    firstSeenInvestigationId: uuid("first_seen_investigation_id").references(() => investigations.id),
    createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [uniqueIndex("sources_canonical_idx").on(t.canonicalUrl), index("sources_domain_idx").on(t.domain)],
);

export const sourceContents = pgTable(
  "source_contents",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    sourceId: uuid("source_id")
      .notNull()
      .references(() => sources.id, { onDelete: "cascade" }),
    contentHash: text("content_hash").notNull(),
    markdown: text("markdown").notNull(),
    httpStatus: integer("http_status"),
    wordCount: integer("word_count").notNull().default(0),
    retrievedAt: timestamp("retrieved_at", { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [index("source_contents_source_idx").on(t.sourceId, t.retrievedAt), index("source_contents_hash_idx").on(t.contentHash)],
);

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

export const claims = pgTable(
  "claims",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    investigationId: uuid("investigation_id")
      .notNull()
      .references(() => investigations.id, { onDelete: "cascade" }),
    text: text("text").notNull(),
    status: text("status", { enum: ["open", "supported", "contradicted", "mixed"] })
      .notNull()
      .default("open"),
    confidence: real("confidence").notNull().default(0.5),
    createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [index("claims_investigation_idx").on(t.investigationId)],
);

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

export const evidence = pgTable(
  "evidence",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    investigationId: uuid("investigation_id")
      .notNull()
      .references(() => investigations.id, { onDelete: "cascade" }),
    sourceId: uuid("source_id")
      .notNull()
      .references(() => sources.id),
    claimId: uuid("claim_id").references(() => claims.id),
    kind: text("kind", { enum: ["support", "contradict", "context"] }).notNull(),
    quote: text("quote").notNull(), // exact or near-exact excerpt from the source
    summary: text("summary").notNull(), // agent's one-line interpretation
    strength: real("strength").notNull().default(0.5), // 0..1 evidential weight
    fingerprint: text("fingerprint").notNull(), // hash(sourceId + normalized quote) for dedup
    createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    index("evidence_investigation_idx").on(t.investigationId),
    uniqueIndex("evidence_fingerprint_idx").on(t.investigationId, t.fingerprint),
  ],
);

/* ---------------------------- hypotheses ------------------------- */

export const hypotheses = pgTable(
  "hypotheses",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    investigationId: uuid("investigation_id")
      .notNull()
      .references(() => investigations.id, { onDelete: "cascade" }),
    parentHypothesisId: uuid("parent_hypothesis_id"),
    title: text("title").notNull(),
    statement: text("statement").notNull(), // falsifiable statement
    status: text("status", {
      enum: ["proposed", "investigating", "supported", "weakened", "rejected", "validated"],
    })
      .notNull()
      .default("proposed"),
    confidence: real("confidence").notNull().default(0.5),
    rationale: text("rationale"),
    adversarialChecked: boolean("adversarial_checked").notNull().default(false),
    createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp("updated_at", { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [index("hypotheses_investigation_idx").on(t.investigationId)],
);

export const hypothesisEvidence = pgTable(
  "hypothesis_evidence",
  {
    hypothesisId: uuid("hypothesis_id")
      .notNull()
      .references(() => hypotheses.id, { onDelete: "cascade" }),
    evidenceId: uuid("evidence_id")
      .notNull()
      .references(() => evidence.id, { onDelete: "cascade" }),
    relation: text("relation", { enum: ["supports", "contradicts"] }).notNull(),
    weight: real("weight").notNull().default(0.5),
  },
  (t) => [primaryKey({ columns: [t.hypothesisId, t.evidenceId] })],
);

/* --------------------------- opportunities ----------------------- */

export const opportunities = pgTable(
  "opportunities",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    investigationId: uuid("investigation_id")
      .notNull()
      .references(() => investigations.id, { onDelete: "cascade" }),
    hypothesisId: uuid("hypothesis_id").references(() => hypotheses.id),
    title: text("title").notNull(),
    summary: text("summary").notNull(),
    problem: text("problem").notNull(),
    whyNow: text("why_now").notNull(),
    risks: text("risks").notNull(),
    skepticCase: text("skeptic_case").notNull(), // strongest argument against — mandatory
    reportMd: text("report_md"), // streamed synthesis report
    status: text("status", { enum: ["candidate", "validated", "rejected"] })
      .notNull()
      .default("candidate"),
    worthScore: real("worth_score"),
    evidenceConfidence: real("evidence_confidence"),
    createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp("updated_at", { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [index("opportunities_investigation_idx").on(t.investigationId), index("opportunities_score_idx").on(t.worthScore)],
);

export const opportunityEvidence = pgTable(
  "opportunity_evidence",
  {
    opportunityId: uuid("opportunity_id")
      .notNull()
      .references(() => opportunities.id, { onDelete: "cascade" }),
    evidenceId: uuid("evidence_id")
      .notNull()
      .references(() => evidence.id, { onDelete: "cascade" }),
    role: text("role", { enum: ["demand", "neglect", "feasibility", "why_now", "risk", "competition", "context"] })
      .notNull()
      .default("context"),
  },
  (t) => [primaryKey({ columns: [t.opportunityId, t.evidenceId] })],
);

export const opportunityScores = pgTable(
  "opportunity_scores",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    opportunityId: uuid("opportunity_id")
      .notNull()
      .references(() => opportunities.id, { onDelete: "cascade" }),
    dimension: text("dimension", {
      enum: ["demand", "neglectedness", "feasibility", "why_now", "impact", "competition", "risk"],
    }).notNull(),
    score: real("score").notNull(), // 0..100
    confidence: real("confidence").notNull(), // 0..1, evidence-backed
    reasoning: text("reasoning").notNull(),
    evidenceIds: jsonb("evidence_ids").$type<string[]>().notNull(),
    createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [uniqueIndex("opportunity_scores_dim_idx").on(t.opportunityId, t.dimension)],
);

/* ---------------------------- competitors ------------------------ */

export const competitors = pgTable(
  "competitors",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    name: text("name").notNull(),
    url: text("url"),
    description: text("description"),
    createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [uniqueIndex("competitors_name_idx").on(t.name)],
);

export const opportunityCompetitors = pgTable(
  "opportunity_competitors",
  {
    opportunityId: uuid("opportunity_id")
      .notNull()
      .references(() => opportunities.id, { onDelete: "cascade" }),
    competitorId: uuid("competitor_id")
      .notNull()
      .references(() => competitors.id, { onDelete: "cascade" }),
    note: text("note"),
  },
  (t) => [primaryKey({ columns: [t.opportunityId, t.competitorId] })],
);

/* ------------------------- saved opportunities ------------------- */

export const savedOpportunities = pgTable(
  "saved_opportunities",
  {
    userId: uuid("user_id")
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),
    opportunityId: uuid("opportunity_id")
      .notNull()
      .references(() => opportunities.id, { onDelete: "cascade" }),
    createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [primaryKey({ columns: [t.userId, t.opportunityId] })],
);