import { sql, relations } from "drizzle-orm";
import { mysqlTable, text, varchar, timestamp, boolean, int, json, mysqlEnum } from "drizzle-orm/mysql-core";
import { createInsertSchema } from "drizzle-zod";
import { z } from "zod";

// Re-export auth models
export * from "./models/auth";

// Enums as MySQL enums
export const userRoleValues = ["investigator", "supervisor", "admin", "auditor"] as const;
export const caseTypeValues = ["criminal", "internal_affairs", "fraud", "cybercrime"] as const;
export const caseStatusValues = ["open", "active", "under_review", "closed", "urgent"] as const;
export const casePriorityValues = ["low", "medium", "high", "critical"] as const;
export const reportStatusValues = ["draft", "submitted", "approved", "rejected"] as const;
export const templateTypeValues = ["file_upload", "question_form"] as const;
export const taskStatusValues = ["assigned", "in_progress", "submitted", "under_review", "completed", "archived"] as const;

// User Profiles (extends auth users with roles)
export const userProfiles = mysqlTable("user_profiles", {
  id: varchar("id", { length: 36 }).primaryKey().default(sql`(UUID())`),
  userId: varchar("user_id", { length: 36 }).notNull().unique(),
  role: mysqlEnum("role", userRoleValues).notNull().default("investigator"),
  badgeNumber: varchar("badge_number", { length: 50 }).unique(),
  department: varchar("department", { length: 255 }),
  isActive: boolean("is_active").notNull().default(true),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow().onUpdateNow(),
});

// Cases
export const cases = mysqlTable("cases", {
  id: varchar("id", { length: 36 }).primaryKey().default(sql`(UUID())`),
  caseNumber: varchar("case_number", { length: 50 }).notNull().unique(),
  title: varchar("title", { length: 500 }).notNull(),
  description: text("description"),
  type: mysqlEnum("type", caseTypeValues).notNull(),
  status: mysqlEnum("status", caseStatusValues).notNull().default("open"),
  priority: mysqlEnum("priority", casePriorityValues).notNull().default("medium"),
  leadInvestigatorId: varchar("lead_investigator_id", { length: 36 }),
  assignedOfficers: json("assigned_officers").$type<string[]>().default([]),
  isRestricted: boolean("is_restricted").notNull().default(false),
  createdBy: varchar("created_by", { length: 36 }).notNull(),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow().onUpdateNow(),
  closedAt: timestamp("closed_at"),
});

// Case Notes
export const caseNotes = mysqlTable("case_notes", {
  id: varchar("id", { length: 36 }).primaryKey().default(sql`(UUID())`),
  caseId: varchar("case_id", { length: 36 }).notNull(),
  authorId: varchar("author_id", { length: 36 }).notNull(),
  content: text("content").notNull(),
  isConfidential: boolean("is_confidential").notNull().default(false),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow().onUpdateNow(),
});

// Audit Logs
export const auditLogs = mysqlTable("audit_logs", {
  id: varchar("id", { length: 36 }).primaryKey().default(sql`(UUID())`),
  userId: varchar("user_id", { length: 36 }).notNull(),
  userName: varchar("user_name", { length: 255 }),
  action: varchar("action", { length: 100 }).notNull(),
  entityType: varchar("entity_type", { length: 100 }).notNull(),
  entityId: varchar("entity_id", { length: 36 }),
  details: text("details"),
  ipAddress: varchar("ip_address", { length: 45 }),
  createdAt: timestamp("created_at").defaultNow(),
});

// Report Templates
export const reportTemplates = mysqlTable("report_templates", {
  id: varchar("id", { length: 36 }).primaryKey().default(sql`(UUID())`),
  name: varchar("name", { length: 255 }).notNull(),
  description: text("description"),
  templateType: mysqlEnum("template_type", templateTypeValues).notNull().default("file_upload"),
  caseTypes: json("case_types").$type<string[]>().notNull().default([]),
  schemaJson: text("schema_json").notNull().default("{}"),
  maxFileSizeMb: int("max_file_size_mb"),
  allowedFileTypes: json("allowed_file_types").$type<string[]>(),
  isActive: boolean("is_active").notNull().default(true),
  createdBy: varchar("created_by", { length: 36 }).notNull(),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow().onUpdateNow(),
});

// Case Tasks (investigation tasks)
export const caseTasks = mysqlTable("case_tasks", {
  id: varchar("id", { length: 36 }).primaryKey().default(sql`(UUID())`),
  caseId: varchar("case_id", { length: 36 }).notNull(),
  investigatorId: varchar("investigator_id", { length: 36 }).notNull(),
  supervisorId: varchar("supervisor_id", { length: 36 }),
  title: varchar("title", { length: 500 }).notNull(),
  description: text("description"),
  status: mysqlEnum("status", taskStatusValues).notNull().default("assigned"),
  startedAt: timestamp("started_at"),
  submittedAt: timestamp("submitted_at"),
  reviewedAt: timestamp("reviewed_at"),
  reviewNotes: text("review_notes"),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow().onUpdateNow(),
});

// Case Reports
export const caseReports = mysqlTable("case_reports", {
  id: varchar("id", { length: 36 }).primaryKey().default(sql`(UUID())`),
  caseId: varchar("case_id", { length: 36 }).notNull(),
  templateId: varchar("template_id", { length: 36 }),
  reportType: varchar("report_type", { length: 50 }).default("summary"),
  authorId: varchar("author_id", { length: 36 }).notNull(),
  title: varchar("title", { length: 500 }).notNull(),
  status: mysqlEnum("status", reportStatusValues).notNull().default("draft"),
  formData: text("form_data"),
  pdfPath: varchar("pdf_path", { length: 500 }),
  submittedAt: timestamp("submitted_at"),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow().onUpdateNow(),
});

// Report Attachments
export const reportAttachments = mysqlTable("report_attachments", {
  id: varchar("id", { length: 36 }).primaryKey().default(sql`(UUID())`),
  reportId: varchar("report_id", { length: 36 }).notNull(),
  fileName: varchar("file_name", { length: 255 }).notNull(),
  fileType: varchar("file_type", { length: 100 }).notNull(),
  filePath: varchar("file_path", { length: 500 }).notNull(),
  fileSize: int("file_size").notNull(),
  uploadedBy: varchar("uploaded_by", { length: 36 }).notNull(),
  createdAt: timestamp("created_at").defaultNow(),
});

// Relations
export const userProfilesRelations = relations(userProfiles, ({ many }) => ({
  createdCases: many(cases),
  caseNotes: many(caseNotes),
  auditLogs: many(auditLogs),
}));

export const casesRelations = relations(cases, ({ one, many }) => ({
  leadInvestigator: one(userProfiles, {
    fields: [cases.leadInvestigatorId],
    references: [userProfiles.userId],
  }),
  creator: one(userProfiles, {
    fields: [cases.createdBy],
    references: [userProfiles.userId],
  }),
  notes: many(caseNotes),
  reports: many(caseReports),
  tasks: many(caseTasks),
}));

export const caseTasksRelations = relations(caseTasks, ({ one }) => ({
  case: one(cases, {
    fields: [caseTasks.caseId],
    references: [cases.id],
  }),
  investigator: one(userProfiles, {
    fields: [caseTasks.investigatorId],
    references: [userProfiles.userId],
  }),
  supervisor: one(userProfiles, {
    fields: [caseTasks.supervisorId],
    references: [userProfiles.userId],
  }),
}));

export const caseReportsRelations = relations(caseReports, ({ one, many }) => ({
  case: one(cases, {
    fields: [caseReports.caseId],
    references: [cases.id],
  }),
  template: one(reportTemplates, {
    fields: [caseReports.templateId],
    references: [reportTemplates.id],
  }),
  author: one(userProfiles, {
    fields: [caseReports.authorId],
    references: [userProfiles.userId],
  }),
  attachments: many(reportAttachments),
}));

export const reportAttachmentsRelations = relations(reportAttachments, ({ one }) => ({
  report: one(caseReports, {
    fields: [reportAttachments.reportId],
    references: [caseReports.id],
  }),
}));

export const caseNotesRelations = relations(caseNotes, ({ one }) => ({
  case: one(cases, {
    fields: [caseNotes.caseId],
    references: [cases.id],
  }),
  author: one(userProfiles, {
    fields: [caseNotes.authorId],
    references: [userProfiles.userId],
  }),
}));

export const auditLogsRelations = relations(auditLogs, ({ one }) => ({
  user: one(userProfiles, {
    fields: [auditLogs.userId],
    references: [userProfiles.userId],
  }),
}));

// Insert schemas
export const insertUserProfileSchema = createInsertSchema(userProfiles).omit({
  id: true,
  createdAt: true,
  updatedAt: true,
});

export const insertCaseSchema = createInsertSchema(cases).omit({
  id: true,
  caseNumber: true,
  createdAt: true,
  updatedAt: true,
  closedAt: true,
});

export const insertCaseNoteSchema = createInsertSchema(caseNotes).omit({
  id: true,
  createdAt: true,
  updatedAt: true,
});

export const insertAuditLogSchema = createInsertSchema(auditLogs).omit({
  id: true,
  createdAt: true,
});

export const insertReportTemplateSchema = createInsertSchema(reportTemplates).omit({
  id: true,
  createdAt: true,
  updatedAt: true,
});

export const insertCaseReportSchema = createInsertSchema(caseReports).omit({
  id: true,
  pdfPath: true,
  submittedAt: true,
  createdAt: true,
  updatedAt: true,
}).extend({
  templateId: z.string().nullable().optional(),
  reportType: z.string().default("summary"),
});

export const insertReportAttachmentSchema = createInsertSchema(reportAttachments).omit({
  id: true,
  createdAt: true,
});

export const insertCaseTaskSchema = createInsertSchema(caseTasks).omit({
  id: true,
  startedAt: true,
  submittedAt: true,
  reviewedAt: true,
  createdAt: true,
  updatedAt: true,
});

// Types
export type InsertUserProfile = z.infer<typeof insertUserProfileSchema>;
export type UserProfile = typeof userProfiles.$inferSelect;

export type InsertCase = z.infer<typeof insertCaseSchema>;
export type Case = typeof cases.$inferSelect;

export type InsertCaseNote = z.infer<typeof insertCaseNoteSchema>;
export type CaseNote = typeof caseNotes.$inferSelect;

export type InsertAuditLog = z.infer<typeof insertAuditLogSchema>;
export type AuditLog = typeof auditLogs.$inferSelect;

export type InsertReportTemplate = z.infer<typeof insertReportTemplateSchema>;
export type ReportTemplate = typeof reportTemplates.$inferSelect;

export type InsertCaseReport = z.infer<typeof insertCaseReportSchema>;
export type CaseReport = typeof caseReports.$inferSelect;

export type InsertReportAttachment = z.infer<typeof insertReportAttachmentSchema>;
export type ReportAttachment = typeof reportAttachments.$inferSelect;

export type InsertCaseTask = z.infer<typeof insertCaseTaskSchema>;
export type CaseTask = typeof caseTasks.$inferSelect;

export type ReportStatus = "draft" | "submitted" | "approved" | "rejected";
export type TemplateType = "file_upload" | "question_form";
export type TaskStatus = "assigned" | "in_progress" | "submitted" | "under_review" | "completed" | "archived";

// Question types for questionnaire builder
export interface TemplateQuestion {
  id: string;
  prompt: string;
  type: "short_text" | "long_text" | "dropdown" | "checkbox";
  isRequired: boolean;
  options?: string[];
  displayOrder: number;
}

// Role types
export type UserRole = "investigator" | "supervisor" | "admin" | "auditor";
export type CaseType = "criminal" | "internal_affairs" | "fraud" | "cybercrime";
export type CaseStatus = "open" | "active" | "under_review" | "closed" | "urgent";
export type CasePriority = "low" | "medium" | "high" | "critical";

// Permission helpers
export const ROLE_PERMISSIONS: Record<UserRole, {
  canViewInternalAffairs: boolean;
  canCreateCases: boolean;
  canAssignCases: boolean;
  canCloseCases: boolean;
  canViewAuditLogs: boolean;
  canManageUsers: boolean;
  canManageTemplates: boolean;
}> = {
  investigator: {
    canViewInternalAffairs: false,
    canCreateCases: true,
    canAssignCases: false,
    canCloseCases: false,
    canViewAuditLogs: false,
    canManageUsers: false,
    canManageTemplates: false,
  },
  supervisor: {
    canViewInternalAffairs: true,
    canCreateCases: true,
    canAssignCases: true,
    canCloseCases: true,
    canViewAuditLogs: true,
    canManageUsers: true,
    canManageTemplates: true,
  },
  admin: {
    canViewInternalAffairs: true,
    canCreateCases: true,
    canAssignCases: true,
    canCloseCases: true,
    canViewAuditLogs: true,
    canManageUsers: true,
    canManageTemplates: true,
  },
  auditor: {
    canViewInternalAffairs: true,
    canCreateCases: false,
    canAssignCases: false,
    canCloseCases: false,
    canViewAuditLogs: true,
    canManageUsers: false,
    canManageTemplates: false,
  },
};