// Toveli — schéma PostgreSQL (identité, publications directes, échanges). Drizzle ORM.
// Les annonces agrégées restent dans la base canonique SQLite (lecture seule) ; ici ne vivent que les données Toveli.
import { pgTable, uuid, text, timestamp, integer, boolean, jsonb, numeric, doublePrecision, index, uniqueIndex, primaryKey, check, bigserial } from "drizzle-orm/pg-core";
import { sql } from "drizzle-orm";

export const users = pgTable("users", {
  id: uuid("id").primaryKey().defaultRandom(),
  email: text("email").notNull(),                 // adresse normalisée (minuscules, espaces retirés — points et + conservés)
  emailVerifiedAt: timestamp("email_verified_at", { withTimezone: true }),
  displayName: text("display_name"),
  phoneEncrypted: text("phone_encrypted"),
  locale: text("locale").notNull().default("fr-CA"),
  status: text("status").notNull().default("ACTIVE"), // ACTIVE | SUSPENDED | DELETED
  prefs: jsonb("prefs").notNull().default(sql`'{}'::jsonb`), // notifications, confidentialité
  totpSecretEncrypted: text("totp_secret_encrypted"), // MFA forte requise pour les rôles élevés
  createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
  updatedAt: timestamp("updated_at", { withTimezone: true }).notNull().defaultNow(),
  deletedAt: timestamp("deleted_at", { withTimezone: true }),
}, (t) => [uniqueIndex("users_email_uq").on(t.email), check("users_status_ck", sql`${t.status} in ('ACTIVE','SUSPENDED','DELETED')`)]);

export const userProfiles = pgTable("user_profiles", {
  userId: uuid("user_id").primaryKey().references(() => users.id, { onDelete: "cascade" }),
  publicName: text("public_name"),
  bio: text("bio"),
  avatarKey: text("avatar_key"),
  sellerType: text("seller_type").notNull().default("OWNER"), // OWNER | AGENT (déclaratif) — AGENT_VERIFIED est un rôle attribué côté serveur
  verificationStatus: text("verification_status").notNull().default("NONE"),
  contactPreferences: jsonb("contact_preferences").notNull().default(sql`'{"email":true,"phone":false}'::jsonb`),
  updatedAt: timestamp("updated_at", { withTimezone: true }).notNull().defaultNow(),
});

export const otpChallenges = pgTable("otp_challenges", {
  id: uuid("id").primaryKey().defaultRandom(),
  emailNormalized: text("email_normalized").notNull(),
  purpose: text("purpose").notNull().default("LOGIN"), // LOGIN | EMAIL_CHANGE
  secretDigest: text("secret_digest").notNull(),      // HMAC-SHA256(code, OTP_HMAC_SECRET, id) — jamais le code
  expiresAt: timestamp("expires_at", { withTimezone: true }).notNull(),
  attempts: integer("attempts").notNull().default(0),
  consumedAt: timestamp("consumed_at", { withTimezone: true }),
  requestedIpHash: text("requested_ip_hash"),
  meta: jsonb("meta").notNull().default(sql`'{}'::jsonb`),
  createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
}, (t) => [index("otp_email_idx").on(t.emailNormalized, t.createdAt), index("otp_expires_idx").on(t.expiresAt)]);

export const sessions = pgTable("sessions", {
  id: uuid("id").primaryKey().defaultRandom(),
  userId: uuid("user_id").notNull().references(() => users.id, { onDelete: "cascade" }),
  tokenDigest: text("token_digest").notNull(),        // sha256(token) — le jeton n'est jamais stocké en clair
  issuedAt: timestamp("issued_at", { withTimezone: true }).notNull().defaultNow(),
  lastSeenAt: timestamp("last_seen_at", { withTimezone: true }).notNull().defaultNow(),
  expiresAt: timestamp("expires_at", { withTimezone: true }).notNull(),
  revokedAt: timestamp("revoked_at", { withTimezone: true }),
  deviceLabel: text("device_label"),
  ipHash: text("ip_hash"),
  mfaAt: timestamp("mfa_at", { withTimezone: true }), // dernière vérification TOTP (actions admin)
}, (t) => [uniqueIndex("sessions_token_uq").on(t.tokenDigest), index("sessions_user_idx").on(t.userId)]);

export const userRoles = pgTable("user_roles", {
  userId: uuid("user_id").notNull().references(() => users.id, { onDelete: "cascade" }),
  role: text("role").notNull(), // ADMIN | MODERATOR | AGENT_VERIFIED
  grantedBy: uuid("granted_by"),
  grantedAt: timestamp("granted_at", { withTimezone: true }).notNull().defaultNow(),
  note: text("note"),
}, (t) => [primaryKey({ columns: [t.userId, t.role] }), check("user_roles_role_ck", sql`${t.role} in ('ADMIN','MODERATOR','AGENT_VERIFIED')`)]);

export const properties = pgTable("properties", {
  id: uuid("id").primaryKey().defaultRandom(),
  canonicalAddress: text("canonical_address"),
  unit: text("unit"),
  city: text("city"),
  region: text("region"),
  postalCode: text("postal_code"),
  lat: doublePrecision("lat"),
  lng: doublePrecision("lng"),
  geographicPrecision: text("geographic_precision").notNull().default("EXACT"), // EXACT | STREET | AREA
  geoSource: text("geo_source"),             // role_unit:<id> | manual | geocoder
  roleUnitId: text("role_unit_id"),
  propertyType: text("property_type").notNull(), // appartement | condo | maison | jumele | plex | terrain | commercial | chalet | autre
  bedrooms: numeric("bedrooms", { precision: 4, scale: 1 }),
  bathrooms: numeric("bathrooms", { precision: 4, scale: 1 }),
  areaSqft: integer("area_sqft"),
  lotSqft: integer("lot_sqft"),
  yearBuilt: integer("year_built"),
  privacyLevel: text("privacy_level").notNull().default("EXACT"), // EXACT | STREET_ONLY | AREA_ONLY
  createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
}, (t) => [index("properties_geo_idx").on(t.lat, t.lng), index("properties_city_idx").on(t.city)]);

export const listings = pgTable("listings", {
  id: uuid("id").primaryKey().defaultRandom(),
  slug: text("slug").notNull(),
  propertyId: uuid("property_id").notNull().references(() => properties.id, { onDelete: "restrict" }),
  ownerUserId: uuid("owner_user_id").references(() => users.id, { onDelete: "set null" }),
  listingType: text("listing_type").notNull(),  // SALE | RENT
  sourceType: text("source_type").notNull().default("DIRECT_OWNER"), // DIRECT_OWNER | DIRECT_AGENT | AGGREGATED_PARTNER
  status: text("status").notNull().default("DRAFT"), // DRAFT | PENDING_REVIEW | PUBLISHED | PAUSED | REJECTED | ARCHIVED | SOLD | RENTED
  title: text("title"),
  description: text("description"),
  priceAmount: numeric("price_amount", { precision: 12, scale: 2 }),
  currency: text("currency").notNull().default("CAD"),
  rentalPeriod: text("rental_period"),          // MONTH (location)
  availabilityDate: text("availability_date"),  // YYYY-MM-DD ou 'now'
  furnished: boolean("furnished"),
  petsPolicy: text("pets_policy"),              // allowed | not_allowed | ask
  smokingPolicy: text("smoking_policy"),
  parking: text("parking"),
  leaseTerms: text("lease_terms"),
  inclusions: jsonb("inclusions").notNull().default(sql`'[]'::jsonb`),
  features: jsonb("features").notNull().default(sql`'{}'::jsonb`), // champs extensibles par type
  publicationAt: timestamp("publication_at", { withTimezone: true }),
  expiresAt: timestamp("expires_at", { withTimezone: true }),
  moderationState: text("moderation_state").notNull().default("NONE"), // NONE | AUTO_OK | FLAGGED | HUMAN_OK | REJECTED
  moderationNote: text("moderation_note"),
  externalUrl: text("external_url"),
  upstreamSource: text("upstream_source"),
  upstreamId: text("upstream_id"),
  upstreamSyncAt: timestamp("upstream_sync_at", { withTimezone: true }),
  wizardStep: integer("wizard_step").notNull().default(0),
  attestedAt: timestamp("attested_at", { withTimezone: true }), // attestation du droit de publier
  version: integer("version").notNull().default(1),              // contrôle de concurrence optimiste
  viewsCount: integer("views_count").notNull().default(0),
  createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
  updatedAt: timestamp("updated_at", { withTimezone: true }).notNull().defaultNow(),
}, (t) => [
  uniqueIndex("listings_slug_uq").on(t.slug),
  index("listings_status_idx").on(t.status, t.listingType, t.publicationAt),
  index("listings_owner_idx").on(t.ownerUserId),
  uniqueIndex("listings_upstream_uq").on(t.upstreamSource, t.upstreamId),
  check("listings_type_ck", sql`${t.listingType} in ('SALE','RENT')`),
  check("listings_source_ck", sql`${t.sourceType} in ('DIRECT_OWNER','DIRECT_AGENT','AGGREGATED_PARTNER')`),
  check("listings_status_ck", sql`${t.status} in ('DRAFT','PENDING_REVIEW','PUBLISHED','PAUSED','REJECTED','ARCHIVED','SOLD','RENTED')`),
  check("listings_price_ck", sql`${t.priceAmount} is null or ${t.priceAmount} >= 0`),
  check("listings_owner_direct_ck", sql`${t.sourceType} = 'AGGREGATED_PARTNER' or ${t.ownerUserId} is not null`),
  check("listings_rent_period_ck", sql`${t.listingType} <> 'RENT' or ${t.rentalPeriod} is null or ${t.rentalPeriod} in ('MONTH','WEEK')`),
]);

export const listingMedia = pgTable("listing_media", {
  id: uuid("id").primaryKey().defaultRandom(),
  listingId: uuid("listing_id").notNull().references(() => listings.id, { onDelete: "cascade" }),
  storageKey: text("storage_key").notNull(),
  mediaType: text("media_type").notNull().default("IMAGE"),
  mimeType: text("mime_type"),
  position: integer("position").notNull().default(0),
  altText: text("alt_text"),
  licenseStatus: text("license_status").notNull().default("OWNER_DECLARED"),
  scanStatus: text("scan_status").notNull().default("PENDING"), // PENDING | CLEAN | REJECTED
  width: integer("width"), height: integer("height"), bytes: integer("bytes"),
  status: text("status").notNull().default("UPLOADING"), // UPLOADING | READY | DELETED
  uploadToken: text("upload_token"),
  uploadedAt: timestamp("uploaded_at", { withTimezone: true }).notNull().defaultNow(),
}, (t) => [index("listing_media_listing_idx").on(t.listingId, t.position)]);

export const listingVersions = pgTable("listing_versions", {
  id: bigserial("id", { mode: "number" }).primaryKey(),
  listingId: uuid("listing_id").notNull().references(() => listings.id, { onDelete: "cascade" }),
  version: integer("version").notNull(),
  actorId: uuid("actor_id"),
  snapshot: jsonb("snapshot").notNull(),
  createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
}, (t) => [index("listing_versions_listing_idx").on(t.listingId, t.version)]);

export const listingClaims = pgTable("listing_claims", {
  id: uuid("id").primaryKey().defaultRandom(),
  aggregatedUid: text("aggregated_uid").notNull(), // uid de l'annonce agrégée (base canonique)
  requesterId: uuid("requester_id").notNull().references(() => users.id, { onDelete: "cascade" }),
  status: text("status").notNull().default("PENDING"), // PENDING | APPROVED | REJECTED
  message: text("message"),
  proofKeys: jsonb("proof_keys").notNull().default(sql`'[]'::jsonb`),
  reviewedBy: uuid("reviewed_by"),
  reviewedAt: timestamp("reviewed_at", { withTimezone: true }),
  createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
}, (t) => [index("listing_claims_uid_idx").on(t.aggregatedUid)]);

export const favorites = pgTable("favorites", {
  userId: uuid("user_id").notNull().references(() => users.id, { onDelete: "cascade" }),
  listingUid: text("listing_uid").notNull(), // 'toveli:<id>' ou uid agrégé
  createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
}, (t) => [primaryKey({ columns: [t.userId, t.listingUid] })]);

export const savedSearches = pgTable("saved_searches", {
  id: uuid("id").primaryKey().defaultRandom(),
  userId: uuid("user_id").notNull().references(() => users.id, { onDelete: "cascade" }),
  label: text("label").notNull(),
  query: jsonb("query").notNull(),
  alertEnabled: boolean("alert_enabled").notNull().default(false),
  alertFrequency: text("alert_frequency").notNull().default("DAILY"), // INSTANT | DAILY | WEEKLY | OFF
  lastAlertAt: timestamp("last_alert_at", { withTimezone: true }),
  createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
}, (t) => [index("saved_searches_user_idx").on(t.userId)]);

export const emailDeliveries = pgTable("email_deliveries", {
  id: uuid("id").primaryKey().defaultRandom(),
  toHash: text("to_hash").notNull(),
  template: text("template").notNull(),
  providerId: text("provider_id"),
  status: text("status").notNull(), // QUEUED | SENT | MOCKED | FAILED
  error: text("error"),
  createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
}, (t) => [index("email_deliveries_created_idx").on(t.createdAt)]);

export const inquiries = pgTable("inquiries", {
  id: uuid("id").primaryKey().defaultRandom(),
  listingId: uuid("listing_id").notNull().references(() => listings.id, { onDelete: "cascade" }),
  senderUserId: uuid("sender_user_id").notNull().references(() => users.id, { onDelete: "cascade" }),
  recipientUserId: uuid("recipient_user_id").notNull().references(() => users.id, { onDelete: "cascade" }),
  category: text("category").notNull().default("INFO"), // INFO | VISIT | APPLICATION | OFFER
  status: text("status").notNull().default("OPEN"),     // OPEN | ARCHIVED | BLOCKED
  lastMessageAt: timestamp("last_message_at", { withTimezone: true }).notNull().defaultNow(),
  createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
}, (t) => [index("inquiries_listing_idx").on(t.listingId), index("inquiries_sender_idx").on(t.senderUserId), index("inquiries_recipient_idx").on(t.recipientUserId), uniqueIndex("inquiries_unique_thread").on(t.listingId, t.senderUserId)]);

export const messages = pgTable("messages", {
  id: uuid("id").primaryKey().defaultRandom(),
  inquiryId: uuid("inquiry_id").notNull().references(() => inquiries.id, { onDelete: "cascade" }),
  senderId: uuid("sender_id").notNull().references(() => users.id, { onDelete: "cascade" }),
  body: text("body").notNull(),
  idempotencyKey: text("idempotency_key"),
  createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
  readAt: timestamp("read_at", { withTimezone: true }),
}, (t) => [index("messages_inquiry_idx").on(t.inquiryId, t.createdAt), uniqueIndex("messages_idem_uq").on(t.inquiryId, t.idempotencyKey)]);

export const viewingRequests = pgTable("viewing_requests", {
  id: uuid("id").primaryKey().defaultRandom(),
  listingId: uuid("listing_id").notNull().references(() => listings.id, { onDelete: "cascade" }),
  inquiryId: uuid("inquiry_id").references(() => inquiries.id, { onDelete: "set null" }),
  requesterId: uuid("requester_id").notNull().references(() => users.id, { onDelete: "cascade" }),
  ownerId: uuid("owner_id").notNull().references(() => users.id, { onDelete: "cascade" }),
  proposedSlots: jsonb("proposed_slots").notNull(), // [ISO America/Toronto]
  confirmedSlot: timestamp("confirmed_slot", { withTimezone: true }),
  status: text("status").notNull().default("REQUESTED"), // REQUESTED | CONFIRMED | CANCELLED | DONE
  note: text("note"),
  createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
  updatedAt: timestamp("updated_at", { withTimezone: true }).notNull().defaultNow(),
}, (t) => [index("viewings_listing_idx").on(t.listingId), index("viewings_requester_idx").on(t.requesterId), index("viewings_owner_idx").on(t.ownerId), check("viewings_status_ck", sql`${t.status} in ('REQUESTED','CONFIRMED','CANCELLED','DONE')`)]);

export const reports = pgTable("reports", {
  id: uuid("id").primaryKey().defaultRandom(),
  targetType: text("target_type").notNull(), // LISTING | USER | MESSAGE | AGGREGATED_LISTING
  targetId: text("target_id").notNull(),
  reporterId: uuid("reporter_id").references(() => users.id, { onDelete: "set null" }),
  reason: text("reason").notNull(),
  details: text("details"),
  status: text("status").notNull().default("OPEN"), // OPEN | RESOLVED | DISMISSED
  createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
}, (t) => [index("reports_status_idx").on(t.status, t.createdAt)]);

export const moderationActions = pgTable("moderation_actions", {
  id: bigserial("id", { mode: "number" }).primaryKey(),
  actorId: uuid("actor_id").notNull(),
  targetType: text("target_type").notNull(),
  targetId: text("target_id").notNull(),
  action: text("action").notNull(),
  note: text("note"),
  createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
});

export const auditEvents = pgTable("audit_events", {
  id: bigserial("id", { mode: "number" }).primaryKey(),
  actorId: uuid("actor_id"),
  action: text("action").notNull(),
  objectType: text("object_type"),
  objectId: text("object_id"),
  ipHash: text("ip_hash"),
  metadataRedacted: jsonb("metadata_redacted").notNull().default(sql`'{}'::jsonb`),
  createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
}, (t) => [index("audit_created_idx").on(t.createdAt), index("audit_actor_idx").on(t.actorId)]);

export const rateLimits = pgTable("rate_limits", {
  key: text("key").notNull(),            // ex. otp:ip:<hash> | otp:email:<hash>
  windowStart: timestamp("window_start", { withTimezone: true }).notNull(),
  count: integer("count").notNull().default(0),
}, (t) => [primaryKey({ columns: [t.key, t.windowStart] })]);