import {
  boolean,
  date,
  index,
  integer,
  jsonb,
  pgEnum,
  pgTable,
  primaryKey,
  text,
  timestamp,
  uniqueIndex,
  uuid,
  varchar,
} from "drizzle-orm/pg-core";

export type LocaleCode = "en" | "el";

export type LocalizedText = Partial<Record<LocaleCode, string>>;

export type PricingSnapshot = {
  nights: number;
  currency: string;
  nightlyPrices: Array<{
    date: string;
    amountMinor: number;
    source: "base" | "season" | "override";
  }>;
  fees: Array<{ code: string; label: string; amountMinor: number }>;
  taxes: Array<{ code: string; label: string; amountMinor: number }>;
  discounts: Array<{ code: string; label: string; amountMinor: number }>;
  depositMinor: number;
  totalMinor: number;
  rules: Record<string, unknown>;
};

export const localeEnum = pgEnum("locale", ["en", "el"]);

export const bookingStatusEnum = pgEnum("booking_status", [
  "draft",
  "held",
  "pending",
  "pending_payment",
  "confirmed",
  "cancelled",
  "expired",
  "completed",
  "no_show",
  "refunded",
]);

export const bookingSourceEnum = pgEnum("booking_source", [
  "direct",
  "manual",
  "ical",
  "airbnb",
  "other",
]);

export const blockTypeEnum = pgEnum("availability_block_type", [
  "owner_stay",
  "maintenance",
  "manual_block",
  "external_calendar",
]);

export const holdStatusEnum = pgEnum("booking_hold_status", [
  "active",
  "converted",
  "expired",
  "released",
]);

export const inventoryLockTypeEnum = pgEnum("inventory_lock_type", [
  "booking",
  "hold",
  "block",
]);

export const paymentStatusEnum = pgEnum("payment_status", [
  "not_required",
  "pending",
  "authorized",
  "paid",
  "failed",
  "refunded",
]);

export const mediaSourceEnum = pgEnum("media_source", [
  "owner",
  "photographer",
  "ai_generated",
  "imported",
]);

export const mediaKindEnum = pgEnum("media_kind", [
  "property_photo",
  "generated_asset",
  "document",
  "open_graph",
]);

const timestamps = {
  createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
  updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull(),
};

export const users = pgTable(
  "users",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    email: varchar("email", { length: 320 }).notNull(),
    name: varchar("name", { length: 160 }).notNull(),
    passwordHash: text("password_hash").notNull(),
    active: boolean("active").default(true).notNull(),
    lastLoginAt: timestamp("last_login_at", { withTimezone: true }),
    ...timestamps,
  },
  (table) => [uniqueIndex("users_email_unique").on(table.email)]
);

export const roles = pgTable(
  "roles",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    code: varchar("code", { length: 80 }).notNull(),
    name: varchar("name", { length: 120 }).notNull(),
    description: text("description"),
    ...timestamps,
  },
  (table) => [uniqueIndex("roles_code_unique").on(table.code)]
);

export const userRoles = pgTable(
  "user_roles",
  {
    userId: uuid("user_id")
      .references(() => users.id, { onDelete: "cascade" })
      .notNull(),
    roleId: uuid("role_id")
      .references(() => roles.id, { onDelete: "cascade" })
      .notNull(),
    createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
  },
  (table) => [primaryKey({ columns: [table.userId, table.roleId] })]
);

export const sessions = pgTable(
  "sessions",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    userId: uuid("user_id")
      .references(() => users.id, { onDelete: "cascade" })
      .notNull(),
    tokenHash: text("token_hash").notNull(),
    expiresAt: timestamp("expires_at", { withTimezone: true }).notNull(),
    revokedAt: timestamp("revoked_at", { withTimezone: true }),
    createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
  },
  (table) => [
    uniqueIndex("sessions_token_hash_unique").on(table.tokenHash),
    index("sessions_user_id_idx").on(table.userId),
  ]
);

export const properties = pgTable(
  "properties",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    slug: varchar("slug", { length: 120 }).notNull(),
    name: varchar("name", { length: 180 }).notNull(),
    registrationNumber: varchar("registration_number", { length: 80 }),
    timezone: varchar("timezone", { length: 80 }).default("Europe/Athens").notNull(),
    currency: varchar("currency", { length: 3 }).default("EUR").notNull(),
    sourceUrl: text("source_url"),
    active: boolean("active").default(true).notNull(),
    ...timestamps,
  },
  (table) => [uniqueIndex("properties_slug_unique").on(table.slug)]
);

export const accommodationTypes = pgTable(
  "accommodation_types",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    propertyId: uuid("property_id")
      .references(() => properties.id, { onDelete: "cascade" })
      .notNull(),
    slug: varchar("slug", { length: 120 }).notNull(),
    name: varchar("name", { length: 180 }).notNull(),
    maxGuests: integer("max_guests").notNull(),
    bedrooms: integer("bedrooms").notNull(),
    beds: integer("beds").notNull(),
    baths: integer("baths").notNull(),
    active: boolean("active").default(true).notNull(),
    ...timestamps,
  },
  (table) => [
    uniqueIndex("accommodation_types_property_slug_unique").on(
      table.propertyId,
      table.slug
    ),
  ]
);

export const units = pgTable(
  "units",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    propertyId: uuid("property_id")
      .references(() => properties.id, { onDelete: "cascade" })
      .notNull(),
    accommodationTypeId: uuid("accommodation_type_id")
      .references(() => accommodationTypes.id, { onDelete: "restrict" })
      .notNull(),
    slug: varchar("slug", { length: 120 }).notNull(),
    name: varchar("name", { length: 180 }).notNull(),
    sortOrder: integer("sort_order").default(0).notNull(),
    active: boolean("active").default(true).notNull(),
    ...timestamps,
  },
  (table) => [
    uniqueIndex("units_property_slug_unique").on(table.propertyId, table.slug),
    index("units_accommodation_type_idx").on(table.accommodationTypeId),
  ]
);

export const amenities = pgTable(
  "amenities",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    code: varchar("code", { length: 120 }).notNull(),
    label: jsonb("label").$type<LocalizedText>().notNull(),
    icon: varchar("icon", { length: 80 }),
    active: boolean("active").default(true).notNull(),
    ...timestamps,
  },
  (table) => [uniqueIndex("amenities_code_unique").on(table.code)]
);

export const propertyAmenities = pgTable(
  "property_amenities",
  {
    propertyId: uuid("property_id")
      .references(() => properties.id, { onDelete: "cascade" })
      .notNull(),
    amenityId: uuid("amenity_id")
      .references(() => amenities.id, { onDelete: "cascade" })
      .notNull(),
    sortOrder: integer("sort_order").default(0).notNull(),
    createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
  },
  (table) => [primaryKey({ columns: [table.propertyId, table.amenityId] })]
);

export const localizedContent = pgTable(
  "localized_content",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    entityType: varchar("entity_type", { length: 80 }).notNull(),
    entityId: uuid("entity_id").notNull(),
    locale: localeEnum("locale").notNull(),
    field: varchar("field", { length: 120 }).notNull(),
    value: text("value").notNull(),
    reviewStatus: varchar("review_status", { length: 40 })
      .default("needs_review")
      .notNull(),
    ...timestamps,
  },
  (table) => [
    uniqueIndex("localized_content_unique").on(
      table.entityType,
      table.entityId,
      table.locale,
      table.field
    ),
  ]
);

export const mediaAssets = pgTable(
  "media_assets",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    propertyId: uuid("property_id").references(() => properties.id, {
      onDelete: "cascade",
    }),
    kind: mediaKindEnum("kind").notNull(),
    source: mediaSourceEnum("source").notNull(),
    path: text("path").notNull(),
    originalFileName: text("original_file_name"),
    mimeType: varchar("mime_type", { length: 120 }).notNull(),
    width: integer("width"),
    height: integer("height"),
    sizeBytes: integer("size_bytes"),
    alt: jsonb("alt").$type<LocalizedText>().default({}).notNull(),
    caption: jsonb("caption").$type<LocalizedText>().default({}).notNull(),
    aiPrompt: text("ai_prompt"),
    aiModel: varchar("ai_model", { length: 80 }),
    approvedAt: timestamp("approved_at", { withTimezone: true }),
    approvedById: uuid("approved_by_id").references(() => users.id, {
      onDelete: "set null",
    }),
    ...timestamps,
  },
  (table) => [
    index("media_assets_property_idx").on(table.propertyId),
    index("media_assets_kind_idx").on(table.kind),
  ]
);

export const seasons = pgTable(
  "seasons",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    propertyId: uuid("property_id")
      .references(() => properties.id, { onDelete: "cascade" })
      .notNull(),
    name: varchar("name", { length: 160 }).notNull(),
    startDate: date("start_date").notNull(),
    endDate: date("end_date").notNull(),
    priority: integer("priority").default(0).notNull(),
    ...timestamps,
  },
  (table) => [index("seasons_property_dates_idx").on(table.propertyId, table.startDate)]
);

export const ratePlans = pgTable(
  "rate_plans",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    unitId: uuid("unit_id")
      .references(() => units.id, { onDelete: "cascade" })
      .notNull(),
    seasonId: uuid("season_id").references(() => seasons.id, {
      onDelete: "set null",
    }),
    name: varchar("name", { length: 160 }).notNull(),
    currency: varchar("currency", { length: 3 }).default("EUR").notNull(),
    nightlyMinor: integer("nightly_minor").notNull(),
    weekendNightlyMinor: integer("weekend_nightly_minor"),
    active: boolean("active").default(true).notNull(),
    ...timestamps,
  },
  (table) => [index("rate_plans_unit_idx").on(table.unitId)]
);

export const rateOverrides = pgTable(
  "rate_overrides",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    unitId: uuid("unit_id")
      .references(() => units.id, { onDelete: "cascade" })
      .notNull(),
    date: date("date").notNull(),
    nightlyMinor: integer("nightly_minor").notNull(),
    minStayNights: integer("min_stay_nights"),
    note: text("note"),
    ...timestamps,
  },
  (table) => [uniqueIndex("rate_overrides_unit_date_unique").on(table.unitId, table.date)]
);

export const stayRules = pgTable(
  "stay_rules",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    unitId: uuid("unit_id")
      .references(() => units.id, { onDelete: "cascade" })
      .notNull(),
    startDate: date("start_date").notNull(),
    endDate: date("end_date").notNull(),
    minStayNights: integer("min_stay_nights"),
    maxStayNights: integer("max_stay_nights"),
    minNoticeDays: integer("min_notice_days"),
    maxAdvanceDays: integer("max_advance_days"),
    ...timestamps,
  },
  (table) => [index("stay_rules_unit_dates_idx").on(table.unitId, table.startDate)]
);

export const fees = pgTable("fees", {
  id: uuid("id").defaultRandom().primaryKey(),
  propertyId: uuid("property_id")
    .references(() => properties.id, { onDelete: "cascade" })
    .notNull(),
  code: varchar("code", { length: 120 }).notNull(),
  label: jsonb("label").$type<LocalizedText>().notNull(),
  amountMinor: integer("amount_minor").notNull(),
  taxable: boolean("taxable").default(false).notNull(),
  active: boolean("active").default(true).notNull(),
  ...timestamps,
});

export const taxes = pgTable("taxes", {
  id: uuid("id").defaultRandom().primaryKey(),
  propertyId: uuid("property_id")
    .references(() => properties.id, { onDelete: "cascade" })
    .notNull(),
  code: varchar("code", { length: 120 }).notNull(),
  label: jsonb("label").$type<LocalizedText>().notNull(),
  percentageBps: integer("percentage_bps").notNull(),
  active: boolean("active").default(true).notNull(),
  ...timestamps,
});

export const promotions = pgTable(
  "promotions",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    propertyId: uuid("property_id")
      .references(() => properties.id, { onDelete: "cascade" })
      .notNull(),
    code: varchar("code", { length: 80 }).notNull(),
    label: jsonb("label").$type<LocalizedText>().notNull(),
    discountMinor: integer("discount_minor"),
    discountBps: integer("discount_bps"),
    startsAt: timestamp("starts_at", { withTimezone: true }),
    endsAt: timestamp("ends_at", { withTimezone: true }),
    maxRedemptions: integer("max_redemptions"),
    active: boolean("active").default(true).notNull(),
    ...timestamps,
  },
  (table) => [uniqueIndex("promotions_property_code_unique").on(table.propertyId, table.code)]
);

export const availabilityBlocks = pgTable(
  "availability_blocks",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    unitId: uuid("unit_id")
      .references(() => units.id, { onDelete: "cascade" })
      .notNull(),
    type: blockTypeEnum("type").notNull(),
    startDate: date("start_date").notNull(),
    endDate: date("end_date").notNull(),
    reason: text("reason"),
    sourceId: text("source_id"),
    ...timestamps,
  },
  (table) => [index("availability_blocks_unit_dates_idx").on(table.unitId, table.startDate)]
);

export const bookingHolds = pgTable(
  "booking_holds",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    unitId: uuid("unit_id")
      .references(() => units.id, { onDelete: "cascade" })
      .notNull(),
    status: holdStatusEnum("status").default("active").notNull(),
    checkIn: date("check_in").notNull(),
    checkOut: date("check_out").notNull(),
    expiresAt: timestamp("expires_at", { withTimezone: true }).notNull(),
    idempotencyKey: text("idempotency_key").notNull(),
    ...timestamps,
  },
  (table) => [
    uniqueIndex("booking_holds_idempotency_unique").on(table.idempotencyKey),
    index("booking_holds_unit_dates_idx").on(table.unitId, table.checkIn),
  ]
);

export const inventoryLocks = pgTable(
  "inventory_locks",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    unitId: uuid("unit_id")
      .references(() => units.id, { onDelete: "cascade" })
      .notNull(),
    type: inventoryLockTypeEnum("type").notNull(),
    sourceId: uuid("source_id").notNull(),
    checkIn: date("check_in").notNull(),
    checkOut: date("check_out").notNull(),
    active: boolean("active").default(true).notNull(),
    expiresAt: timestamp("expires_at", { withTimezone: true }),
    createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
  },
  (table) => [
    uniqueIndex("inventory_locks_source_unique").on(table.type, table.sourceId),
    index("inventory_locks_unit_dates_idx").on(table.unitId, table.checkIn),
  ]
);

export const bookings = pgTable(
  "bookings",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    unitId: uuid("unit_id")
      .references(() => units.id, { onDelete: "restrict" })
      .notNull(),
    holdId: uuid("hold_id").references(() => bookingHolds.id, {
      onDelete: "set null",
    }),
    status: bookingStatusEnum("status").default("draft").notNull(),
    source: bookingSourceEnum("source").default("direct").notNull(),
    checkIn: date("check_in").notNull(),
    checkOut: date("check_out").notNull(),
    adults: integer("adults").default(1).notNull(),
    children: integer("children").default(0).notNull(),
    infants: integer("infants").default(0).notNull(),
    guestName: varchar("guest_name", { length: 180 }).notNull(),
    guestEmail: varchar("guest_email", { length: 320 }).notNull(),
    guestPhone: varchar("guest_phone", { length: 80 }),
    currency: varchar("currency", { length: 3 }).default("EUR").notNull(),
    totalMinor: integer("total_minor").default(0).notNull(),
    depositMinor: integer("deposit_minor").default(0).notNull(),
    pricingSnapshot: jsonb("pricing_snapshot").$type<PricingSnapshot>().notNull(),
    guestManagementTokenHash: text("guest_management_token_hash"),
    cancellationReason: text("cancellation_reason"),
    internalNotes: text("internal_notes"),
    confirmedAt: timestamp("confirmed_at", { withTimezone: true }),
    cancelledAt: timestamp("cancelled_at", { withTimezone: true }),
    completedAt: timestamp("completed_at", { withTimezone: true }),
    ...timestamps,
  },
  (table) => [
    index("bookings_unit_dates_idx").on(table.unitId, table.checkIn),
    index("bookings_status_idx").on(table.status),
    index("bookings_guest_email_idx").on(table.guestEmail),
  ]
);

export const bookingGuests = pgTable("booking_guests", {
  id: uuid("id").defaultRandom().primaryKey(),
  bookingId: uuid("booking_id")
    .references(() => bookings.id, { onDelete: "cascade" })
    .notNull(),
  name: varchar("name", { length: 180 }),
  ageGroup: varchar("age_group", { length: 40 }).notNull(),
  createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
});

export const bookingPriceLines = pgTable("booking_price_lines", {
  id: uuid("id").defaultRandom().primaryKey(),
  bookingId: uuid("booking_id")
    .references(() => bookings.id, { onDelete: "cascade" })
    .notNull(),
  type: varchar("type", { length: 40 }).notNull(),
  code: varchar("code", { length: 120 }).notNull(),
  label: jsonb("label").$type<LocalizedText>().notNull(),
  amountMinor: integer("amount_minor").notNull(),
  sortOrder: integer("sort_order").default(0).notNull(),
});

export const payments = pgTable("payments", {
  id: uuid("id").defaultRandom().primaryKey(),
  bookingId: uuid("booking_id")
    .references(() => bookings.id, { onDelete: "cascade" })
    .notNull(),
  provider: varchar("provider", { length: 80 }).notNull(),
  status: paymentStatusEnum("status").default("pending").notNull(),
  amountMinor: integer("amount_minor").notNull(),
  currency: varchar("currency", { length: 3 }).default("EUR").notNull(),
  providerReference: text("provider_reference"),
  ...timestamps,
});

export const paymentEvents = pgTable("payment_events", {
  id: uuid("id").defaultRandom().primaryKey(),
  paymentId: uuid("payment_id")
    .references(() => payments.id, { onDelete: "cascade" })
    .notNull(),
  eventType: varchar("event_type", { length: 120 }).notNull(),
  payload: jsonb("payload").$type<Record<string, unknown>>().default({}).notNull(),
  createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
});

export const emailEvents = pgTable("email_events", {
  id: uuid("id").defaultRandom().primaryKey(),
  bookingId: uuid("booking_id").references(() => bookings.id, {
    onDelete: "set null",
  }),
  template: varchar("template", { length: 120 }).notNull(),
  locale: localeEnum("locale").notNull(),
  recipient: varchar("recipient", { length: 320 }).notNull(),
  status: varchar("status", { length: 40 }).notNull(),
  providerMessageId: text("provider_message_id"),
  error: text("error"),
  createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
});

export const contactEnquiries = pgTable("contact_enquiries", {
  id: uuid("id").defaultRandom().primaryKey(),
  locale: localeEnum("locale").notNull(),
  name: varchar("name", { length: 180 }).notNull(),
  email: varchar("email", { length: 320 }).notNull(),
  phone: varchar("phone", { length: 80 }),
  message: text("message").notNull(),
  consentAt: timestamp("consent_at", { withTimezone: true }).notNull(),
  status: varchar("status", { length: 40 }).default("new").notNull(),
  createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
});

export const icalFeeds = pgTable("ical_feeds", {
  id: uuid("id").defaultRandom().primaryKey(),
  unitId: uuid("unit_id")
    .references(() => units.id, { onDelete: "cascade" })
    .notNull(),
  name: varchar("name", { length: 160 }).notNull(),
  url: text("url").notNull(),
  active: boolean("active").default(true).notNull(),
  lastSyncedAt: timestamp("last_synced_at", { withTimezone: true }),
  ...timestamps,
});

export const icalSyncEvents = pgTable("ical_sync_events", {
  id: uuid("id").defaultRandom().primaryKey(),
  feedId: uuid("feed_id")
    .references(() => icalFeeds.id, { onDelete: "cascade" })
    .notNull(),
  status: varchar("status", { length: 40 }).notNull(),
  importedCount: integer("imported_count").default(0).notNull(),
  conflictCount: integer("conflict_count").default(0).notNull(),
  message: text("message"),
  payload: jsonb("payload").$type<Record<string, unknown>>().default({}).notNull(),
  createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
});

export const auditEvents = pgTable(
  "audit_events",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    actorUserId: uuid("actor_user_id").references(() => users.id, {
      onDelete: "set null",
    }),
    action: varchar("action", { length: 160 }).notNull(),
    entityType: varchar("entity_type", { length: 80 }).notNull(),
    entityId: uuid("entity_id"),
    metadata: jsonb("metadata").$type<Record<string, unknown>>().default({}).notNull(),
    ipHash: text("ip_hash"),
    createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
  },
  (table) => [index("audit_events_entity_idx").on(table.entityType, table.entityId)]
);

export const applicationSettings = pgTable(
  "application_settings",
  {
    key: varchar("key", { length: 160 }).primaryKey(),
    value: jsonb("value").$type<Record<string, unknown>>().notNull(),
    updatedById: uuid("updated_by_id").references(() => users.id, {
      onDelete: "set null",
    }),
    updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull(),
  }
);
