import { index, integer, real, sqliteTable, text, uniqueIndex } from "drizzle-orm/sqlite-core";

export const workspaceUsers = sqliteTable("workspace_users", {
  id: text("id").primaryKey(),
  email: text("email").notNull(),
  displayName: text("display_name"),
  role: text("role").notNull().default("viewer"),
  createdAt: text("created_at").notNull(),
  updatedAt: text("updated_at").notNull(),
}, (table) => [uniqueIndex("workspace_users_email_unique").on(table.email)]);

export const geoProjects = sqliteTable("geo_projects", {
  id: text("id").primaryKey(),
  slug: text("slug").notNull(),
  projectCode: text("project_code").notNull().default(""),
  projectType: text("project_type").notNull().default("client"),
  projectName: text("project_name").notNull(),
  brandName: text("brand_name").notNull(),
  clientName: text("client_name").notNull(),
  coreKeyword: text("core_keyword").notNull().default(""),
  derivativeKeywordsJson: text("derivative_keywords_json").notNull().default("[]"),
  platformsJson: text("platforms_json").notNull().default("[]"),
  optimizationRegion: text("optimization_region").notNull().default(""),
  serviceScope: text("service_scope").notNull().default("domestic"),
  acceptanceRulesJson: text("acceptance_rules_json").notNull().default("{}"),
  measurementProtocolJson: text("measurement_protocol_json").notNull().default("{}"),
  configVersion: integer("config_version").notNull().default(1),
  configFingerprint: text("config_fingerprint").notNull().default(""),
  frozenAt: text("frozen_at"),
  frozenBy: text("frozen_by"),
  startDate: text("start_date"),
  endDate: text("end_date"),
  baselineMonth: text("baseline_month"),
  currentMonth: text("current_month"),
  status: text("status").notNull().default("active"),
  dataMode: text("data_mode").notNull().default("empty"),
  logoKey: text("logo_key"),
  deletedAt: text("deleted_at"),
  deletedBy: text("deleted_by"),
  deleteAfter: text("delete_after"),
  createdBy: text("created_by").notNull(),
  createdAt: text("created_at").notNull(),
  updatedAt: text("updated_at").notNull(),
}, (table) => [
  uniqueIndex("geo_projects_slug_unique").on(table.slug),
  index("geo_projects_type_idx").on(table.projectType),
  index("geo_projects_status_idx").on(table.status),
]);

export const geoProjectMembers = sqliteTable("geo_project_members", {
  id: text("id").primaryKey(),
  projectId: text("project_id").notNull().references(() => geoProjects.id, { onDelete: "cascade" }),
  userEmail: text("user_email").notNull(),
  displayName: text("display_name"),
  role: text("role").notNull().default("viewer"),
  status: text("status").notNull().default("active"),
  createdAt: text("created_at").notNull(),
  updatedAt: text("updated_at"),
}, (table) => [
  uniqueIndex("geo_project_members_unique").on(table.projectId, table.userEmail),
  index("geo_project_members_email_idx").on(table.userEmail),
]);

export const geoSnapshots = sqliteTable("geo_snapshots", {
  id: text("id").primaryKey(),
  projectId: text("project_id").notNull().references(() => geoProjects.id, { onDelete: "cascade" }),
  reportMonth: text("report_month").notNull(),
  version: integer("version").notNull().default(1),
  status: text("status").notNull().default("draft"),
  qualityScore: real("quality_score").notNull().default(0),
  answerCount: integer("answer_count").notNull().default(0),
  deliveryCount: integer("delivery_count").notNull().default(0),
  evidenceCount: integer("evidence_count").notNull().default(0),
  sourceFilename: text("source_filename"),
  summaryJson: text("summary_json").notNull().default("{}"),
  scopeFingerprint: text("scope_fingerprint").notNull().default(""),
  scopeJson: text("scope_json").notNull().default("{}"),
  gateJson: text("gate_json").notNull().default("{}"),
  reviewedBy: text("reviewed_by"),
  reviewNote: text("review_note"),
  publishedBy: text("published_by"),
  acceptanceStatus: text("acceptance_status").notNull().default("pending"),
  acceptedAt: text("accepted_at"),
  acceptedBy: text("accepted_by"),
  createdBy: text("created_by").notNull(),
  createdAt: text("created_at").notNull(),
  updatedAt: text("updated_at").notNull(),
  publishedAt: text("published_at"),
}, (table) => [
  index("geo_snapshots_project_idx").on(table.projectId),
  index("geo_snapshots_month_idx").on(table.projectId, table.reportMonth),
  uniqueIndex("geo_snapshots_project_month_version_unique").on(table.projectId, table.reportMonth, table.version),
]);

export const geoImportJobs = sqliteTable("geo_import_jobs", {
  id: text("id").primaryKey(),
  projectId: text("project_id").notNull().references(() => geoProjects.id, { onDelete: "cascade" }),
  snapshotId: text("snapshot_id").references(() => geoSnapshots.id, { onDelete: "set null" }),
  importType: text("import_type").notNull(),
  filename: text("filename").notNull(),
  objectKey: text("object_key"),
  status: text("status").notNull(),
  totalRows: integer("total_rows").notNull().default(0),
  validRows: integer("valid_rows").notNull().default(0),
  errorRows: integer("error_rows").notNull().default(0),
  errorsJson: text("errors_json").notNull().default("[]"),
  createdBy: text("created_by").notNull(),
  createdAt: text("created_at").notNull(),
}, (table) => [index("geo_import_jobs_project_idx").on(table.projectId)]);

export const geoEvidenceRecords = sqliteTable("geo_evidence_records", {
  id: text("id").primaryKey(),
  projectId: text("project_id").notNull().references(() => geoProjects.id, { onDelete: "cascade" }),
  snapshotId: text("snapshot_id").references(() => geoSnapshots.id, { onDelete: "set null" }),
  recordId: text("record_id").notNull(),
  promptId: text("prompt_id").notNull().default(""),
  platform: text("platform").notNull().default(""),
  modelVersion: text("model_version").notNull().default(""),
  testedAt: text("tested_at").notNull().default(""),
  recordStatus: text("record_status").notNull().default("valid"),
  question: text("question").notNull().default(""),
  answer: text("answer").notNull().default(""),
  keyword: text("keyword").notNull().default(""),
  questionType: text("question_type").notNull().default(""),
  scene: text("scene").notNull().default(""),
  region: text("region").notNull().default(""),
  conversationUrl: text("conversation_url"),
  evidenceObjectKey: text("evidence_object_key"),
  mention: integer("mention").notNull().default(0),
  recommendationPrompt: integer("recommendation_prompt").notNull().default(0),
  recommend: integer("recommend").notNull().default(0),
  top3: integer("top3").notNull().default(0),
  rank: real("rank"),
  sentiment: text("sentiment"),
  accuracy: integer("accuracy"),
  factPointsTotal: integer("fact_points_total").notNull().default(0),
  factPointsCorrect: integer("fact_points_correct").notNull().default(0),
  accuracyNote: text("accuracy_note"),
  judgedBy: text("judged_by"),
  citationDetectable: integer("citation_detectable").notNull().default(0),
  brandCited: integer("brand_cited").notNull().default(0),
  citationUrls: text("citation_urls").notNull().default("[]"),
  importJobId: text("import_job_id"),
  sha256: text("sha256").notNull(),
  createdAt: text("created_at").notNull(),
}, (table) => [
  uniqueIndex("geo_evidence_project_snapshot_record_unique").on(table.projectId, table.snapshotId, table.recordId),
  index("geo_evidence_snapshot_idx").on(table.snapshotId),
]);

export const geoDeliveryItems = sqliteTable("geo_delivery_items", {
  id: text("id").primaryKey(),
  projectId: text("project_id").notNull().references(() => geoProjects.id, { onDelete: "cascade" }),
  snapshotId: text("snapshot_id").references(() => geoSnapshots.id, { onDelete: "set null" }),
  itemId: text("item_id").notNull(),
  title: text("title").notNull(),
  media: text("media").notNull(),
  mediaLevel: text("media_level").notNull().default(""),
  originalUrl: text("original_url").notNull(),
  publishDate: text("publish_date"),
  indexStatus: text("index_status").notNull().default("待检测"),
  citationStatus: text("citation_status").notNull().default("待检测"),
  aiPlatforms: text("ai_platforms").notNull().default("[]"),
  importJobId: text("import_job_id"),
  sha256: text("sha256").notNull(),
  createdAt: text("created_at").notNull(),
}, (table) => [
  uniqueIndex("geo_delivery_project_snapshot_item_unique").on(table.projectId, table.snapshotId, table.itemId),
  index("geo_delivery_snapshot_idx").on(table.snapshotId),
]);

export const geoDeliveryAcceptanceItems = sqliteTable("geo_delivery_acceptance_items", {
  id: text("id").primaryKey(),
  projectId: text("project_id").notNull().references(() => geoProjects.id, { onDelete: "cascade" }),
  snapshotId: text("snapshot_id").notNull().references(() => geoSnapshots.id, { onDelete: "cascade" }),
  itemId: text("item_id").notNull(),
  category: text("category").notNull(),
  itemName: text("item_name").notNull(),
  unit: text("unit").notNull(),
  contractQuantity: real("contract_quantity").notNull(),
  qualifiedQuantity: real("qualified_quantity").notNull(),
  weight: real("weight").notNull(),
  evidenceUrl: text("evidence_url").notNull(),
  note: text("note").notNull().default(""),
  importJobId: text("import_job_id"),
  createdAt: text("created_at").notNull(),
}, (table) => [
  uniqueIndex("geo_delivery_acceptance_snapshot_item_unique").on(table.snapshotId, table.itemId),
  index("geo_delivery_acceptance_snapshot_idx").on(table.snapshotId),
]);

export const geoProjectConfigVersions = sqliteTable("geo_project_config_versions", {
  id: text("id").primaryKey(),
  projectId: text("project_id").notNull().references(() => geoProjects.id, { onDelete: "cascade" }),
  version: integer("version").notNull(),
  configJson: text("config_json").notNull(),
  fingerprint: text("fingerprint").notNull(),
  status: text("status").notNull().default("draft"),
  changeReason: text("change_reason"),
  createdBy: text("created_by").notNull(),
  createdAt: text("created_at").notNull(),
  frozenAt: text("frozen_at"),
}, (table) => [
  uniqueIndex("geo_project_config_version_unique").on(table.projectId, table.version),
  index("geo_project_config_project_idx").on(table.projectId),
]);

export const geoCompetitorRecords = sqliteTable("geo_competitor_records", {
  id: text("id").primaryKey(),
  projectId: text("project_id").notNull().references(() => geoProjects.id, { onDelete: "cascade" }),
  snapshotId: text("snapshot_id").notNull().references(() => geoSnapshots.id, { onDelete: "cascade" }),
  recordId: text("record_id").notNull(),
  promptId: text("prompt_id").notNull(),
  platform: text("platform").notNull(),
  competitorName: text("competitor_name").notNull(),
  mentioned: integer("mentioned").notNull().default(0),
  recommended: integer("recommended").notNull().default(0),
  rank: real("rank"),
  importJobId: text("import_job_id"),
  createdAt: text("created_at").notNull(),
}, (table) => [
  uniqueIndex("geo_competitor_snapshot_record_unique").on(table.snapshotId, table.recordId, table.competitorName),
  index("geo_competitor_snapshot_idx").on(table.snapshotId),
]);

export const geoAcceptanceRecords = sqliteTable("geo_acceptance_records", {
  id: text("id").primaryKey(),
  projectId: text("project_id").notNull().references(() => geoProjects.id, { onDelete: "cascade" }),
  snapshotId: text("snapshot_id").notNull().references(() => geoSnapshots.id, { onDelete: "cascade" }),
  status: text("status").notNull(),
  comment: text("comment").notNull().default(""),
  submittedBy: text("submitted_by").notNull(),
  submittedAt: text("submitted_at").notNull(),
}, (table) => [
  index("geo_acceptance_snapshot_idx").on(table.snapshotId),
]);

export const geoAuditLogs = sqliteTable("geo_audit_logs", {
  id: text("id").primaryKey(),
  projectId: text("project_id").references(() => geoProjects.id, { onDelete: "set null" }),
  actorEmail: text("actor_email").notNull(),
  action: text("action").notNull(),
  targetType: text("target_type").notNull(),
  targetId: text("target_id"),
  detailJson: text("detail_json").notNull().default("{}"),
  createdAt: text("created_at").notNull(),
}, (table) => [
  index("geo_audit_project_idx").on(table.projectId, table.createdAt),
]);
