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 yeUsersTable = pgTable("ye_users", {
  id: id(),
  name: text("name").notNull(),
  email: text("email").notNull().unique(),
  role: text("role").notNull().default("admin"),
  initials: text("initials").notNull().default("YE"),
  passwordHash: text("password_hash"),
  active: boolean("active").notNull().default(true),
  createdAt: createdAt(),
  updatedAt: updatedAt(),
});

/**
 * Second-factor challenges contain only a keyed digest of the expected answer.
 * The answer itself is never persisted.
 */
export const yeAdminSecondFactorChallengesTable = pgTable("ye_admin_second_factor_challenges", {
  id: id(),
  userId: uuid("user_id").notNull(),
  method: text("method").notNull(),
  responseHash: text("response_hash").notNull(),
  challengeText: text("challenge_text"),
  expiresAt: timestamp("expires_at", { withTimezone: true }).notNull(),
  attempts: integer("attempts").notNull().default(0),
  consumedAt: timestamp("consumed_at", { withTimezone: true }),
  issuedBy: uuid("issued_by"),
  createdAt: createdAt(),
});

/** Opaque bearer/session credentials are stored only as SHA-256 digests. */
export const yeSessionsTable = pgTable("ye_sessions", {
  id: id(),
  userId: uuid("user_id").notNull(),
  tokenHash: text("token_hash").notNull().unique(),
  sessionType: text("session_type").notNull().default("web"),
  impersonatorUserId: uuid("impersonator_user_id"),
  impersonatedUserId: uuid("impersonated_user_id"),
  csrfTokenHash: text("csrf_token_hash"),
  expiresAt: timestamp("expires_at", { withTimezone: true }).notNull(),
  revokedAt: timestamp("revoked_at", { withTimezone: true }),
  lastUsedAt: timestamp("last_used_at", { withTimezone: true }),
  userAgent: text("user_agent"),
  ipAddress: text("ip_address"),
  createdAt: createdAt(),
});

export const yeQuotesTable = pgTable("ye_quotes", {
  id: id(),
  quoteNumber: text("quote_number").notNull().unique(),
  clientName: text("client_name").notNull(),
  company: text("company"),
  email: text("email").notNull(),
  phone: text("phone"),
  service: text("service").notNull(),
  message: text("message").notNull(),
  status: text("status").notNull().default("new"),
  amount: doublePrecision("amount"),
  priority: text("priority").notNull().default("normal"),
  createdAt: createdAt(),
  updatedAt: updatedAt(),
});

export const yeClientsTable = pgTable("ye_clients", {
  id: id(),
  name: text("name").notNull(),
  company: text("company").notNull(),
  email: text("email").notNull(),
  phone: text("phone").notNull(),
  status: text("status").notNull().default("prospect"),
  projectsCount: integer("projects_count").notNull().default(0),
  createdAt: createdAt(),
});

export const yeProjectsTable = pgTable("ye_projects", {
  id: id(),
  projectNumber: text("project_number").notNull().unique(),
  name: text("name").notNull(),
  status: text("status").notNull().default("planning"),
  address: text("address").notNull(),
  clientId: uuid("client_id"),
  clientName: text("client_name").notNull().default("Cliente por asignar"),
  worksitesCount: integer("worksites_count").notNull().default(0),
  progress: integer("progress").notNull().default(0),
  updatedAt: updatedAt(),
});

export const yeWorksitesTable = pgTable("ye_worksites", {
  id: id(),
  projectId: uuid("project_id").notNull(),
  projectNumber: text("project_number").notNull(),
  name: text("name").notNull(),
  address: text("address").notNull(),
  status: text("status").notNull().default("active"),
  areasCount: integer("areas_count").notNull().default(1),
  supervisorName: text("supervisor_name"),
  latitude: doublePrecision("latitude"),
  longitude: doublePrecision("longitude"),
  geofenceRadiusMeters: integer("geofence_radius_meters").notNull().default(150),
  scheduleStart: text("schedule_start"),
  scheduleEnd: text("schedule_end"),
  scheduleGraceMinutes: integer("schedule_grace_minutes").notNull().default(15),
  qrDisplayAuthorized: boolean("qr_display_authorized").notNull().default(false),
});

export const yeEquipmentTable = pgTable("ye_equipment", {
  id: id(),
  assetNumber: text("asset_number").notNull().unique(),
  name: text("name").notNull(),
  category: text("category").notNull(),
  status: text("status").notNull().default("available"),
  location: text("location").notNull(),
  nextMaintenance: date("next_maintenance", { mode: "string" }).notNull(),
  hourlyRate: doublePrecision("hourly_rate"),
});

export const yeMaterialsTable = pgTable("ye_materials", {
  id: id(),
  sku: text("sku").notNull().unique(),
  name: text("name").notNull(),
  category: text("category").notNull(),
  stock: doublePrecision("stock").notNull().default(0),
  unit: text("unit").notNull(),
  reorderLevel: doublePrecision("reorder_level").notNull().default(0),
});

export const yeEmployeesTable = pgTable("ye_employees", {
  id: id(),
  employeeNumber: text("employee_number").notNull().unique(),
  name: text("name").notNull(),
  role: text("role").notNull(),
  phone: text("phone").notNull(),
  status: text("status").notNull().default("active"),
  assignedWorksite: text("assigned_worksite"),
  hireDate: date("hire_date", { mode: "string" }).notNull(),
  profileImage: text("profile_image").notNull().default("/employee-profiles/default.svg"),
  department: text("department").notNull().default("Operaciones"),
  position: text("position").notNull().default("Empleado"),
  email: text("email").notNull().default("employee@example.com"),
  address: text("address").notNull().default("Puerto Rico"),
  emergencyContacts: jsonb("emergency_contacts")
    .$type<Array<{ name: string; relationship: string; phone: string }>>()
    .notNull()
    .default([]),
  employmentType: text("employment_type").notNull().default("full_time"),
  appAccess: boolean("app_access").notNull().default(false),
  readOnly: boolean("read_only").notNull().default(true),
  userId: uuid("user_id"),
});

/**
 * Mobile credentials are issued by corporate staff only.  The employee code
 * and is never persisted in clear text. The digest is an HMAC-SHA-256
 * keyed by a server-side pepper so codes remain suitable for exact lookup.
 */
export const yeEmployeeCredentialsTable = pgTable("ye_employee_credentials", {
  id: id(),
  employeeId: uuid("employee_id").notNull().unique(),
  employeeCodeHash: text("employee_code_hash").notNull().unique(),
  status: text("status").notNull().default("active"),
  issuedBy: uuid("issued_by").notNull(),
  issuedAt: createdAt(),
  shownAt: timestamp("shown_at", { withTimezone: true }),
  rotatedAt: timestamp("rotated_at", { withTimezone: true }),
  revokedAt: timestamp("revoked_at", { withTimezone: true }),
  lastUsedAt: timestamp("last_used_at", { withTimezone: true }),
});

export const yeAttendanceTable = pgTable("ye_attendance", {
  id: id(),
  employeeId: uuid("employee_id"),
  employeeName: text("employee_name").notNull(),
  employeeNumber: text("employee_number").notNull(),
  worksiteId: uuid("worksite_id"),
  worksiteName: text("worksite_name").notNull(),
  deviceId: text("device_id"),
  action: text("action").notNull(),
  latitude: doublePrecision("latitude"),
  longitude: doublePrecision("longitude"),
  capturedAt: timestamp("captured_at", { withTimezone: true }).notNull(),
  platform: text("platform"),
  challengeId: uuid("challenge_id"),
  deviceCredentialHash: text("device_credential_hash"),
  riskScore: integer("risk_score").notNull().default(0),
  validation: jsonb("validation")
    .$type<Record<string, boolean>>()
    .notNull()
    .default({
      identity: true,
      device: true,
      location: true,
      schedule: true,
    }),
});

export const yeSecurityEventsTable = pgTable("ye_security_events", {
  id: id(),
  type: text("type").notNull(),
  message: text("message").notNull(),
  severity: text("severity").notNull().default("info"),
  worksiteName: text("worksite_name"),
  createdAt: createdAt(),
  resolved: boolean("resolved").notNull().default(false),
});

/** Append-only security evidence for every QR attendance decision. */
export const yeSecurityAttemptsTable = pgTable("ye_security_attempts", {
  id: id(),
  employeeId: uuid("employee_id"),
  employeeNumber: text("employee_number"),
  projectId: uuid("project_id"),
  worksiteId: uuid("worksite_id"),
  deviceId: uuid("device_id"),
  platform: text("platform"),
  result: text("result").notNull(),
  reason: text("reason").notNull(),
  latitude: doublePrecision("latitude"),
  longitude: doublePrecision("longitude"),
  riskScore: integer("risk_score").notNull().default(0),
  challengeId: uuid("challenge_id"),
  createdAt: createdAt(),
});

export const yePoliciesTable = pgTable("ye_policies", {
  id: id(),
  title: text("title").notNull(),
  version: text("version").notNull(),
  summary: text("summary").notNull(),
  category: text("category").notNull(),
  status: text("status").notNull().default("draft"),
  updatedAt: updatedAt(),
  requiredAcceptance: boolean("required_acceptance").notNull().default(true),
});

export const yeDocumentsTable = pgTable("ye_documents", {
  id: id(),
  name: text("name").notNull(),
  category: text("category").notNull(),
  status: text("status").notNull().default("current"),
  owner: text("owner").notNull(),
  expiresAt: date("expires_at", { mode: "string" }),
  fileUrl: text("file_url"),
  updatedAt: updatedAt(),
});

export const yeSettingsTable = pgTable("ye_settings", {
  id: id(),
  companyName: text("company_name").notNull(),
  legalName: text("legal_name").notNull(),
  phone: text("phone").notNull(),
  email: text("email").notNull(),
  address: text("address").notNull(),
  defaultLanguage: text("default_language").notNull().default("es"),
  quoteNotificationEmail: text("quote_notification_email").notNull(),
  siteContent: jsonb("site_content").$type<Record<string, string>>().notNull().default({}),
  integrationSettings: jsonb("integration_settings").$type<Record<string, unknown>>().notNull().default({}),
  adminSecondFactorEnabled: boolean("admin_second_factor_enabled").notNull().default(false),
  adminSecondFactorMethod: text("admin_second_factor_method").notNull().default("daily_code"),
});

export const yeActivityTable = pgTable("ye_activity", {
  id: id(),
  action: text("action").notNull(),
  actor: text("actor").notNull(),
  timestamp: timestamp("timestamp", { withTimezone: true }).notNull().defaultNow(),
  severity: text("severity").notNull().default("info"),
});