import { boolean, date, doublePrecision, integer, jsonb, pgTable, text, timestamp, uuid } from "drizzle-orm/pg-core";

const id = () => uuid("id").primaryKey().defaultRandom();
const createdAt = () => timestamp("created_at", { withTimezone: true }).notNull().defaultNow();
const updatedAt = () => timestamp("updated_at", { withTimezone: true }).notNull().defaultNow();

export const yeAppointmentsTable = pgTable("ye_appointments", {
  id: id(),
  appointmentNumber: text("appointment_number").notNull().unique(),
  division: text("division").notNull(),
  service: text("service").notNull(),
  appointmentType: text("appointment_type").notNull(),
  customerName: text("customer_name").notNull(),
  email: text("email").notNull(),
  phone: text("phone"),
  scheduledAt: timestamp("scheduled_at", { withTimezone: true }).notNull(),
  location: text("location"),
  mode: text("mode").notNull().default("in_person"),
  status: text("status").notNull().default("requested"),
  notes: text("notes"),
  assignedTo: text("assigned_to"),
  createdAt: createdAt(),
  updatedAt: updatedAt(),
});

export const yeAlertsTable = pgTable("ye_alerts", {
  id: id(),
  title: text("title").notNull(),
  message: text("message").notNull(),
  level: text("level").notNull().default("information"),
  recipients: text("recipients").notNull().default("all_employees"),
  worksiteName: text("worksite_name"),
  status: text("status").notNull().default("draft"),
  requiresAcknowledgement: boolean("requires_acknowledgement").notNull().default(false),
  sentAt: timestamp("sent_at", { withTimezone: true }),
  createdAt: createdAt(),
});

export const yeAuditLogsTable = pgTable("ye_audit_logs", {
  id: id(),
  eventType: text("event_type").notNull(),
  actorName: text("actor_name").notNull(),
  actorRole: text("actor_role").notNull(),
  resourceType: text("resource_type").notNull(),
  resourceId: text("resource_id"),
  action: text("action").notNull(),
  beforeValue: jsonb("before_value"),
  afterValue: jsonb("after_value"),
  reason: text("reason"),
  ipAddress: text("ip_address"),
  device: text("device"),
  createdAt: createdAt(),
});

export const yeRolesTable = pgTable("ye_roles", {
  id: id(),
  name: text("name").notNull().unique(),
  displayName: text("display_name").notNull(),
  description: text("description").notNull(),
  permissions: jsonb("permissions").$type<string[]>().notNull().default([]),
  systemRole: boolean("system_role").notNull().default(false),
  createdAt: createdAt(),
});

export const yeDevicesTable = pgTable("ye_devices", {
  id: id(),
  employeeId: uuid("employee_id"),
  employeeName: text("employee_name").notNull(),
  platform: text("platform").notNull(),
  model: text("model"),
  osVersion: text("os_version"),
  appVersion: text("app_version"),
  /** Client installation identifiers are never retained, only this digest. */
  installationIdHash: text("installation_id_hash").notNull().unique(),
  deviceCredentialHash: text("device_credential_hash").unique(),
  status: text("status").notNull().default("pending"),
  approvedBy: uuid("approved_by"),
  revokedAt: timestamp("revoked_at", { withTimezone: true }),
  replacedAt: timestamp("replaced_at", { withTimezone: true }),
  lastLogin: timestamp("last_login", { withTimezone: true }),
  lastActivity: timestamp("last_activity", { withTimezone: true }),
  authorizedAt: timestamp("authorized_at", { withTimezone: true }).notNull().defaultNow(),
});

/** Corporate-issued, rotating QR display resource for a project/worksite. */
export const yeProjectQrResourcesTable = pgTable("ye_project_qr_resources", {
  id: id(),
  projectId: uuid("project_id").notNull().unique(),
  worksiteId: uuid("worksite_id"),
  keyVersion: text("key_version").notNull(),
  status: text("status").notNull().default("active"),
  createdBy: uuid("created_by").notNull(),
  rotatedAt: timestamp("rotated_at", { withTimezone: true }),
  createdAt: createdAt(),
});

/** Short-lived, single-use challenges signed by the server. */
export const yeQrChallengesTable = pgTable("ye_qr_challenges", {
  id: id(),
  resourceId: uuid("resource_id").notNull(),
  projectId: uuid("project_id").notNull(),
  worksiteId: uuid("worksite_id").notNull(),
  nonceHash: text("nonce_hash").notNull().unique(),
  displaySessionId: text("display_session_id").notNull(),
  keyVersion: text("key_version").notNull(),
  issuedAt: timestamp("issued_at", { withTimezone: true }).notNull(),
  expiresAt: timestamp("expires_at", { withTimezone: true }).notNull(),
  consumedAt: timestamp("consumed_at", { withTimezone: true }),
  createdAt: createdAt(),
});

export const yeJobPostingsTable = pgTable("ye_job_postings", {
  id: id(),
  title: text("title").notNull(),
  department: text("department").notNull(),
  location: text("location").notNull(),
  employmentType: text("employment_type").notNull(),
  description: text("description").notNull(),
  requirements: text("requirements").notNull(),
  status: text("status").notNull().default("open"),
  createdAt: createdAt(),
  updatedAt: updatedAt(),
});

export const yeJobApplicationsTable = pgTable("ye_job_applications", {
  id: id(),
  jobPostingId: uuid("job_posting_id"),
  applicantName: text("applicant_name").notNull(),
  email: text("email").notNull(),
  phone: text("phone"),
  resumeUrl: text("resume_url"),
  certifications: text("certifications"),
  status: text("status").notNull().default("received"),
  notes: text("notes"),
  createdAt: createdAt(),
  updatedAt: updatedAt(),
});

export const yeResourcesTable = pgTable("ye_resources", {
  id: id(),
  title: text("title").notNull(),
  category: text("category").notNull(),
  summary: text("summary").notNull(),
  body: text("body").notNull(),
  language: text("language").notNull().default("es"),
  status: text("status").notNull().default("published"),
  updatedAt: updatedAt(),
});

export const yeMediaTable = pgTable("ye_media", {
  id: id(),
  name: text("name").notNull(),
  mediaType: text("media_type").notNull(),
  url: text("url").notNull(),
  altText: text("alt_text").notNull(),
  projectId: uuid("project_id"),
  uploadedBy: text("uploaded_by").notNull(),
  createdAt: createdAt(),
});

export const yePayrollExportsTable = pgTable("ye_payroll_exports", {
  id: id(),
  exportNumber: text("export_number").notNull().unique(),
  periodStart: date("period_start", { mode: "string" }).notNull(),
  periodEnd: date("period_end", { mode: "string" }).notNull(),
  format: text("format").notNull(),
  employeeCount: integer("employee_count").notNull().default(0),
  status: text("status").notNull().default("queued"),
  fileUrl: text("file_url"),
  createdBy: text("created_by").notNull(),
  createdAt: createdAt(),
});

export const yeLeaveRequestsTable = pgTable("ye_leave_requests", {
  id: id(),
  employeeId: uuid("employee_id"),
  employeeName: text("employee_name").notNull(),
  leaveType: text("leave_type").notNull(),
  startDate: date("start_date", { mode: "string" }).notNull(),
  endDate: date("end_date", { mode: "string" }).notNull(),
  reason: text("reason").notNull(),
  status: text("status").notNull().default("pending"),
  reviewedBy: text("reviewed_by"),
  createdAt: createdAt(),
  updatedAt: updatedAt(),
});