import { pgTable, varchar, integer, text, jsonb, timestamp, index } from "drizzle-orm/pg-core";
import { sql } from "drizzle-orm";
import { z } from "zod";
import { createInsertSchema } from "drizzle-zod";

const idCol = () => varchar("id").primaryKey().default(sql`gen_random_uuid()`);
const ts = (name: string) => timestamp(name, { withTimezone: true }).notNull().defaultNow();

export const pilotSmokeRuns = pgTable("pilot_smoke_runs", {
  id: idCol(),
  runRef: varchar("run_ref", { length: 64 }).notNull().unique("pilot_smoke_runs_run_ref_key"),
  triggeredBy: varchar("triggered_by", { length: 128 }).notNull(),
  durationMs: integer("duration_ms").notNull().default(0),
  pillarCount: integer("pillar_count").notNull().default(0),
  passedCount: integer("passed_count").notNull().default(0),
  failedCount: integer("failed_count").notNull().default(0),
  compositeScore: integer("composite_score").notNull().default(0),
  grade: varchar("grade", { length: 4 }).notNull().default("F"),
  goNoGo: varchar("go_no_go", { length: 8 }).notNull().default("NO-GO"),
  pillarsJson: jsonb("pillars_json").notNull().default("[]"),
  errorsJson: jsonb("errors_json").notNull().default("[]"),
  ranAt: ts("ran_at"),
}, (t) => ({
  ranAtIdx: index("pilot_smoke_runs_ran_at_idx").on(t.ranAt.desc()),
}));

export const pilotMasterScores = pgTable("pilot_master_scores", {
  id: idCol(),
  scoreRef: varchar("score_ref", { length: 64 }).notNull().unique("pilot_master_scores_score_ref_key"),
  generatedBy: varchar("generated_by", { length: 128 }).notNull(),
  compositeScore: integer("composite_score").notNull().default(0),
  grade: varchar("grade", { length: 4 }).notNull().default("F"),
  waveScoresJson: jsonb("wave_scores_json").notNull().default("{}"),
  pillarBreakdownJson: jsonb("pillar_breakdown_json").notNull().default("[]"),
  payloadHash: varchar("payload_hash", { length: 128 }).notNull(),
  signatureHex: varchar("signature_hex", { length: 256 }).notNull(),
  generatedAt: ts("generated_at"),
}, (t) => ({
  generatedAtIdx: index("pilot_master_scores_generated_at_idx").on(t.generatedAt.desc()),
}));

export const insertPilotSmokeRunSchema = createInsertSchema(pilotSmokeRuns).omit({ id: true, ranAt: true });
export const insertPilotMasterScoreSchema = createInsertSchema(pilotMasterScores).omit({ id: true, generatedAt: true });
export type InsertPilotSmokeRun = z.infer<typeof insertPilotSmokeRunSchema>;
export type InsertPilotMasterScore = z.infer<typeof insertPilotMasterScoreSchema>;
export type PilotSmokeRun = typeof pilotSmokeRuns.$inferSelect;
export type PilotMasterScore = typeof pilotMasterScores.$inferSelect;