import type {
  LighthouseDiagnostic,
  LighthouseOpportunity,
  ResourceBreakdown,
} from "@/lib/sitespeed";

import {
  boolean,
  index,
  integer,
  jsonb,
  pgTable,
  real,
  text,
  timestamp,
} from "drizzle-orm/pg-core";

/* ----------------------------- Better Auth ----------------------------- */
/* Schemas required by better-auth's Drizzle adapter. Keep table + column
   names aligned with better-auth defaults (singular, snake_case). */

export const user = pgTable("user", {
  id: text("id").primaryKey(),
  name: text("name").notNull(),
  email: text("email").notNull().unique(),
  emailVerified: boolean("email_verified").notNull().default(false),
  image: text("image"),
  createdAt: timestamp("created_at", { withTimezone: true, mode: "date" }).notNull().defaultNow(),
  updatedAt: timestamp("updated_at", { withTimezone: true, mode: "date" }).notNull().defaultNow(),
  // Better Auth admin plugin
  role: text("role"),
  banned: boolean("banned").default(false),
  banReason: text("ban_reason"),
  banExpires: timestamp("ban_expires", { withTimezone: true, mode: "date" }),
});

export const session = pgTable("session", {
  id: text("id").primaryKey(),
  expiresAt: timestamp("expires_at", { withTimezone: true, mode: "date" }).notNull(),
  token: text("token").notNull().unique(),
  ipAddress: text("ip_address"),
  userAgent: text("user_agent"),
  userId: text("user_id")
    .notNull()
    .references(() => user.id, { onDelete: "cascade" }),
  createdAt: timestamp("created_at", { withTimezone: true, mode: "date" }).notNull().defaultNow(),
  updatedAt: timestamp("updated_at", { withTimezone: true, mode: "date" }).notNull().defaultNow(),
  // Better Auth admin plugin (impersonation)
  impersonatedBy: text("impersonated_by"),
  // Better Auth organization plugin
  activeOrganizationId: text("active_organization_id"),
});

export const account = pgTable("account", {
  id: text("id").primaryKey(),
  accountId: text("account_id").notNull(),
  providerId: text("provider_id").notNull(),
  userId: text("user_id")
    .notNull()
    .references(() => user.id, { onDelete: "cascade" }),
  accessToken: text("access_token"),
  refreshToken: text("refresh_token"),
  idToken: text("id_token"),
  accessTokenExpiresAt: timestamp("access_token_expires_at", {
    withTimezone: true,
    mode: "date",
  }),
  refreshTokenExpiresAt: timestamp("refresh_token_expires_at", {
    withTimezone: true,
    mode: "date",
  }),
  scope: text("scope"),
  password: text("password"),
  createdAt: timestamp("created_at", { withTimezone: true, mode: "date" }).notNull().defaultNow(),
  updatedAt: timestamp("updated_at", { withTimezone: true, mode: "date" }).notNull().defaultNow(),
});

export const verification = pgTable("verification", {
  id: text("id").primaryKey(),
  identifier: text("identifier").notNull(),
  value: text("value").notNull(),
  expiresAt: timestamp("expires_at", { withTimezone: true, mode: "date" }).notNull(),
  createdAt: timestamp("created_at", { withTimezone: true, mode: "date" }).defaultNow(),
  updatedAt: timestamp("updated_at", { withTimezone: true, mode: "date" }).defaultNow(),
});

/* ------------------------ Better Auth — Organization ------------------------ */
/* Tables required by better-auth's `organization` plugin. Singular, snake_case. */

export const organization = pgTable("organization", {
  id: text("id").primaryKey(),
  name: text("name").notNull(),
  slug: text("slug").notNull().unique(),
  logo: text("logo"),
  metadata: text("metadata"),
  createdAt: timestamp("created_at", { withTimezone: true, mode: "date" }).notNull().defaultNow(),
});

export const member = pgTable(
  "member",
  {
    id: text("id").primaryKey(),
    organizationId: text("organization_id")
      .notNull()
      .references(() => organization.id, { onDelete: "cascade" }),
    userId: text("user_id")
      .notNull()
      .references(() => user.id, { onDelete: "cascade" }),
    role: text("role").notNull(), // "owner" | "admin" | "member"
    createdAt: timestamp("created_at", { withTimezone: true, mode: "date" }).notNull().defaultNow(),
  },
  (t) => [index("idx_member_org").on(t.organizationId), index("idx_member_user").on(t.userId)],
);

export const invitation = pgTable(
  "invitation",
  {
    id: text("id").primaryKey(),
    organizationId: text("organization_id")
      .notNull()
      .references(() => organization.id, { onDelete: "cascade" }),
    email: text("email").notNull(),
    role: text("role").notNull(), // "admin" | "member"
    status: text("status").notNull(), // "pending" | "accepted" | "rejected" | "expired"
    inviterId: text("inviter_id")
      .notNull()
      .references(() => user.id, { onDelete: "cascade" }),
    expiresAt: timestamp("expires_at", { withTimezone: true, mode: "date" }).notNull(),
  },
  (t) => [index("idx_invitation_org").on(t.organizationId)],
);

/* ------------------------ Billing — Subscription ------------------------ */
/* Source of truth app-side for an org's current plan + Stripe state.
   Synchronized by Phase 6 webhooks. For now (Phase 5), every row has
   plan="free" + stripe_customer_id=null. */

export type Plan = "free" | "starter" | "pro" | "business" | "enterprise";
export type BillingInterval = "month" | "year";
export type SubStatus =
  | "active"
  | "trialing"
  | "past_due"
  | "canceled"
  | "incomplete"
  | "incomplete_expired"
  | "unpaid";

export const orgSubscription = pgTable(
  "es_org_subscription",
  {
    id: text("id").primaryKey(),
    organizationId: text("organization_id")
      .notNull()
      .unique()
      .references(() => organization.id, { onDelete: "cascade" }),

    plan: text("plan").$type<Plan>().notNull().default("free"),
    interval: text("interval").$type<BillingInterval>(),
    /* Billing currency for the active subscription. Typed as Currency in
       lib/billing/currency.ts; stored as plain text here to keep the schema
       module dependency-free. */
    currency: text("currency").notNull().default("eur"),
    /* Extra seats purchased beyond the tier's included quota. Tracked as a
       separate Stripe Subscription Item (quantity-based) — this column
       mirrors that quantity for app-side quota checks. */
    extraSeats: integer("extra_seats").notNull().default(0),

    stripeCustomerId: text("stripe_customer_id").unique(),
    stripeSubscriptionId: text("stripe_subscription_id").unique(),
    stripePriceId: text("stripe_price_id"),

    status: text("status").$type<SubStatus>().notNull().default("active"),
    currentPeriodStart: timestamp("current_period_start", {
      withTimezone: true,
      mode: "date",
    }),
    currentPeriodEnd: timestamp("current_period_end", {
      withTimezone: true,
      mode: "date",
    }),
    cancelAtPeriodEnd: boolean("cancel_at_period_end").notNull().default(false),
    canceledAt: timestamp("canceled_at", { withTimezone: true, mode: "date" }),

    createdAt: timestamp("created_at", { withTimezone: true, mode: "date" }).notNull().defaultNow(),
    updatedAt: timestamp("updated_at", { withTimezone: true, mode: "date" }).notNull().defaultNow(),
  },
  (t) => [
    index("idx_sub_stripe_customer").on(t.stripeCustomerId),
    index("idx_sub_stripe_sub").on(t.stripeSubscriptionId),
  ],
);

/* Webhook idempotence: every Stripe event id we've processed.
   Inserted at the top of the webhook handler with ON CONFLICT DO NOTHING.
   If conflict → we've already seen this event → return 200 early. */
export const stripeEvent = pgTable("es_stripe_event", {
  id: text("id").primaryKey(), // Stripe event.id
  type: text("type").notNull(),
  receivedAt: timestamp("received_at", { withTimezone: true, mode: "date" }).notNull().defaultNow(),
});

/* -------------------------------- Sites -------------------------------- */

export type AuditCadence = "none" | "daily" | "weekly";

export const sites = pgTable(
  "es_site",
  {
    id: text("id").primaryKey(),
    organizationId: text("organization_id")
      .notNull()
      .references(() => organization.id, { onDelete: "cascade" }),
    createdByUserId: text("created_by_user_id").references(() => user.id, {
      onDelete: "set null",
    }),
    hostname: text("hostname").notNull(),
    url: text("url").notNull(),
    label: text("label"),
    createdAt: timestamp("created_at", { withTimezone: true, mode: "string" })
      .notNull()
      .defaultNow(),
    // Scheduled audits (cron driven from pg_cron or host cron).
    auditCadence: text("audit_cadence").$type<AuditCadence>().notNull().default("none"),
    lastAuditAt: timestamp("last_audit_at", { withTimezone: true, mode: "string" }),
    nextAuditAt: timestamp("next_audit_at", { withTimezone: true, mode: "string" }),
  },
  (table) => [
    index("idx_site_org").on(table.organizationId),
    index("idx_site_next_audit").on(table.nextAuditAt),
  ],
);

export const siteTests = pgTable(
  "es_site_test",
  {
    id: text("id").primaryKey(),
    siteId: text("site_id").references(() => sites.id, { onDelete: "cascade" }),
    organizationId: text("organization_id").references(() => organization.id, {
      onDelete: "cascade",
    }),
    triggeredByUserId: text("triggered_by_user_id").references(() => user.id, {
      onDelete: "set null",
    }),
    hostname: text("hostname"),
    url: text("url").notNull(),
    device: text("device"), // "desktop" | "mobile"
    status: text("status").notNull(),
    browser: text("browser"),
    iterations: integer("iterations"),
    error: text("error"),
    createdAt: timestamp("created_at", { withTimezone: true, mode: "string" })
      .notNull()
      .defaultNow(),
    completedAt: timestamp("completed_at", { withTimezone: true, mode: "string" }),

    // Core Web Vitals + metrics
    fcp: real("fcp"),
    lcp: real("lcp"),
    tbt: real("tbt"),
    cls: real("cls"),
    ttfb: real("ttfb"),
    fid: real("fid"),
    si: real("si"),
    fullyLoaded: real("fully_loaded"),
    domContentLoaded: real("dom_content_loaded"),
    domInteractive: real("dom_interactive"),
    requests: integer("requests"),
    transferSize: integer("transfer_size"),

    // Lighthouse scores
    lighthousePerf: integer("lighthouse_perf"),
    lighthouseA11y: integer("lighthouse_a11y"),
    lighthouseBp: integer("lighthouse_bp"),
    lighthouseSeo: integer("lighthouse_seo"),

    // Lighthouse data (native jsonb — typed via @/lib/sitespeed)
    lighthouseOpportunities: jsonb("lighthouse_opportunities").$type<LighthouseOpportunity[]>(),
    lighthouseDiagnostics: jsonb("lighthouse_diagnostics").$type<LighthouseDiagnostic[]>(),
    resourceBreakdown: jsonb("resource_breakdown").$type<ResourceBreakdown>(),

    // Computed (from our scoring, kept for backward compat)
    performanceScore: integer("performance_score"),

    // Paths from API
    reportUrl: text("report_url"),
    lighthouseReportUrl: text("lighthouse_report_url"),
    harUrl: text("har_url"),
    screenshotAfter: text("screenshot_after"),
    screenshotLcp: text("screenshot_lcp"),
    screenshotCls: text("screenshot_cls"),
  },
  (table) => [
    index("idx_site_test_site").on(table.siteId),
    index("idx_site_test_org").on(table.organizationId),
    index("idx_site_test_created").on(table.createdAt),
    index("idx_site_test_hostname").on(table.hostname),
  ],
);

/* -------------------------------- Leads -------------------------------- */
/* Email capture for marketing — populated by the public audit / embed flow. */

export const leads = pgTable(
  "es_lead",
  {
    id: text("id").primaryKey(),
    email: text("email").notNull(),
    source: text("source").notNull(), // "audit" | "embed" | "homepage" | ...
    siteUrl: text("site_url"),
    consent: boolean("consent").notNull().default(false),
    ipAddress: text("ip_address"),
    userAgent: text("user_agent"),
    userId: text("user_id").references(() => user.id, { onDelete: "set null" }),
    createdAt: timestamp("created_at", { withTimezone: true, mode: "string" })
      .notNull()
      .defaultNow(),
  },
  (table) => [index("idx_lead_email").on(table.email), index("idx_lead_source").on(table.source)],
);