import { sql } from 'drizzle-orm'
import {
  bigint,
  boolean,
  index,
  integer,
  pgTable,
  primaryKey,
  text,
  timestamp,
  uniqueIndex,
  uuid,
} from 'drizzle-orm/pg-core'
import { auditLog } from '@platform-modules/audit'
import {
  authUsers,
  authUsersCreationSql,
  serviceTokens,
  serviceTokensCreationSql,
  userSessions,
  userSessionsCreationSql,
} from '@platform-modules/auth/engine-custom'
import { entitlementTierGrants, entitlements } from '@platform-modules/entitlements'
import { ledgerEntries, walletBalances } from '@platform-modules/ledger'
import {
  permissionsTable as tenancyPermissions,
  rolePermissionsTable as tenancyRolePermissions,
  rolesTable as tenancyRoles,
} from '@platform-modules/tenancy/rbac-triad'

export {
  auditLog,
  authUsers,
  entitlements,
  entitlementTierGrants,
  ledgerEntries,
  serviceTokens,
  tenancyPermissions,
  tenancyRolePermissions,
  tenancyRoles,
  userSessions,
  walletBalances,
}

export function periodKey(account: string, plugin: string, period: string): string {
  return `${account}:${plugin}:${period}`
}

export const apiKeys = pgTable('api_keys', {
  id: text('id').primaryKey(),
  prefix: text('prefix').notNull().unique(),
  secretHash: text('secret_hash').notNull(),
  owner: text('owner').notNull(),
  scopes: text('scopes').notNull(),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull(),
  lastUsedAt: timestamp('last_used_at', { withTimezone: true }),
  revokedAt: timestamp('revoked_at', { withTimezone: true }),
  expiresAt: timestamp('expires_at', { withTimezone: true }),
})

export const oauthClients = pgTable('oauth_clients', {
  id: text('id').primaryKey(),
  name: text('name').notNull(),
  redirectUris: text('redirect_uris').notNull(),
  scopes: text('scopes').notNull(),
  confidential: boolean('confidential').notNull().default(false),
  clientSecretHash: text('client_secret_hash'),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull(),
})

export const oauthCodes = pgTable('oauth_codes', {
  id: text('id').primaryKey(),
  clientId: text('client_id')
    .notNull()
    .references(() => oauthClients.id),
  redirectUri: text('redirect_uri').notNull(),
  scope: text('scope').notNull(),
  codeChallenge: text('code_challenge').notNull(),
  codeChallengeMethod: text('code_challenge_method').notNull(),
  codeHash: text('code_hash').notNull().unique(),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull(),
  expiresAt: timestamp('expires_at', { withTimezone: true }).notNull(),
  usedAt: timestamp('used_at', { withTimezone: true }),
})

export const oauthTokens = pgTable('oauth_tokens', {
  id: text('id').primaryKey(),
  clientId: text('client_id')
    .notNull()
    .references(() => oauthClients.id),
  codeId: text('code_id')
    .notNull()
    .references(() => oauthCodes.id),
  accessTokenPrefix: text('access_token_prefix').notNull().unique(),
  accessTokenHash: text('access_token_hash').notNull().unique(),
  scope: text('scope').notNull(),
  tokenType: text('token_type').notNull(),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull(),
  expiresAt: timestamp('expires_at', { withTimezone: true }).notNull(),
})

export const accounts = pgTable(
  'accounts',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    slug: text('slug').notNull().unique(),
    type: text('type').notNull().default('individual'),
    status: text('status').notNull().default('active'),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (table) => [index('accounts_status_idx').on(table.status)],
)

export const plugins = pgTable('plugins', {
  key: text('key').primaryKey(),
  name: text('name').notNull(),
  permissionCatalog: text('permission_catalog')
    .array()
    .notNull()
    .default(sql`'{}'::text[]`),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
})

export const subscriptionPackages = pgTable(
  'subscription_packages',
  {
    pluginKey: text('plugin_key')
      .notNull()
      .references(() => plugins.key, { onDelete: 'cascade' }),
    tierKey: text('tier_key').notNull(),
    currency: text('currency').notNull(),
    priceMinor: bigint('price_minor', { mode: 'bigint' }).notNull(),
    creditAllocation: bigint('credit_allocation', { mode: 'bigint' }).notNull().default(0n),
    seatsConfig: integer('seats_config').notNull(),
    paypalPlanId: text('paypal_plan_id').notNull().unique(),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (table) => [
    primaryKey({
      columns: [table.pluginKey, table.tierKey],
      name: 'subscription_packages_plugin_tier_pk',
    }),
    index('subscription_packages_plugin_idx').on(table.pluginKey),
  ],
)

export const subscriptions = pgTable(
  'subscriptions',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    accountId: uuid('account_id')
      .notNull()
      .references(() => accounts.id, { onDelete: 'cascade' }),
    pluginKey: text('plugin_key')
      .notNull()
      .references(() => plugins.key, { onDelete: 'cascade' }),
    tierKey: text('tier_key').notNull(),
    status: text('status').notNull().default('pending'),
    currentPeriodStart: timestamp('current_period_start', { withTimezone: true }),
    currentPeriodEnd: timestamp('current_period_end', { withTimezone: true }),
    paypalSubscriptionId: text('paypal_subscription_id').unique(),
    gracePeriodEndAt: timestamp('grace_period_end_at', { withTimezone: true }),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (table) => [
    uniqueIndex('subscriptions_account_plugin_uq').on(table.accountId, table.pluginKey),
    index('subscriptions_status_idx').on(table.status),
  ],
)

export const sites = pgTable(
  'sites',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    accountId: uuid('account_id')
      .notNull()
      .references(() => accounts.id, { onDelete: 'cascade' }),
    pluginKey: text('plugin_key')
      .notNull()
      .references(() => plugins.key, { onDelete: 'cascade' }),
    displayUrl: text('display_url').notNull(),
    status: text('status').notNull().default('active'),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    disconnectedAt: timestamp('disconnected_at', { withTimezone: true }),
  },
  (table) => [index('sites_account_plugin_idx').on(table.accountId, table.pluginKey)],
)

export const credentials = pgTable(
  'credentials',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    accountId: uuid('account_id')
      .notNull()
      .references(() => accounts.id, { onDelete: 'cascade' }),
    siteId: uuid('site_id')
      .notNull()
      .references(() => sites.id, { onDelete: 'cascade' }),
    provider: text('provider').notNull(),
    apiKeyId: text('api_key_id').references(() => apiKeys.id, { onDelete: 'set null' }),
    oauthTokenId: text('oauth_token_id').references(() => oauthTokens.id, {
      onDelete: 'set null',
    }),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    revokedAt: timestamp('revoked_at', { withTimezone: true }),
    lastUsedAt: timestamp('last_used_at', { withTimezone: true }),
  },
  (table) => [
    uniqueIndex('credentials_account_site_provider_uq').on(
      table.accountId,
      table.siteId,
      table.provider,
    ),
  ],
)

export const members = pgTable(
  'members',
  {
    accountId: uuid('account_id')
      .notNull()
      .references(() => accounts.id, { onDelete: 'cascade' }),
    userId: text('user_id')
      .notNull()
      .references(() => authUsers.id, { onDelete: 'cascade' }),
    roleKey: text('role_key').notNull(),
    status: text('status').notNull().default('active'),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (table) => [
    primaryKey({ columns: [table.accountId, table.userId], name: 'members_account_user_pk' }),
    index('members_account_role_idx').on(table.accountId, table.roleKey),
  ],
)

export const invoiceDocument = pgTable('invoice_document', {
  idempotencyKey: text('idempotency_key').primaryKey(),
  documentId: text('document_id').notNull(),
  documentNumber: text('document_number').notNull(),
  documentUrl: text('document_url').notNull(),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
})

export const invoiceReferences = pgTable(
  'invoice_references',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    accountId: uuid('account_id')
      .notNull()
      .references(() => accounts.id, { onDelete: 'cascade' }),
    subscriptionId: uuid('subscription_id')
      .notNull()
      .references(() => subscriptions.id, { onDelete: 'cascade' }),
    morningDocumentId: text('morning_document_id').notNull(),
    documentNumber: text('document_number').notNull(),
    documentUrl: text('document_url').notNull(),
    docType: text('doc_type').notNull(),
    amount: bigint('amount', { mode: 'bigint' }).notNull(),
    vatAmount: bigint('vat_amount', { mode: 'bigint' }).notNull(),
    currency: text('currency').notNull(),
    idempotencyKey: text('idempotency_key').notNull().unique(),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (table) => [index('invoice_references_account_idx').on(table.accountId)],
)

export const webhookEvents = pgTable('webhook_events', {
  eventId: text('event_id').primaryKey(),
  claimedAt: timestamp('claimed_at', { withTimezone: true }),
  processedAt: timestamp('processed_at', { withTimezone: true }),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
})

export const chargeIntents = pgTable(
  'charge_intents',
  {
    chargeKey: text('charge_key').primaryKey(),
    amount: bigint('amount', { mode: 'bigint' }).notNull(),
    currency: text('currency').notNull(),
    refundReservedMinor: bigint('refund_reserved_minor', { mode: 'bigint' }).notNull().default(0n),
    status: text('status').notNull().default('pending'),
    providerRef: text('provider_ref'),
    claimedAt: timestamp('claimed_at', { withTimezone: true }).notNull().defaultNow(),
    settledAt: timestamp('settled_at', { withTimezone: true }),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (table) => [index('charge_intents_status_idx').on(table.status)],
)

export const refundReservations = pgTable('refund_reservations', {
  refundKey: text('refund_key').primaryKey(),
  chargeKey: text('charge_key')
    .notNull()
    .references(() => chargeIntents.chargeKey, { onDelete: 'cascade' }),
  walletKey: text('wallet_key').notNull(),
  amountMinor: bigint('amount_minor', { mode: 'bigint' }).notNull(),
  currency: text('currency').notNull(),
  status: text('status').notNull().default('pending'),
  providerRefundKey: text('provider_refund_key'),
  completedAt: timestamp('completed_at', { withTimezone: true }),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
})

export const pressZoneSchema = {
  accounts,
  plugins,
  subscriptionPackages,
  subscriptions,
  sites,
  credentials,
  members,
  invoiceReferences,
  invoiceDocument,
  webhookEvents,
  chargeIntents,
  refundReservations,
  authUsers,
  userSessions,
  serviceTokens,
  apiKeys,
  oauthClients,
  oauthCodes,
  oauthTokens,
  entitlements,
  entitlementTierGrants,
  tenancyRoles,
  tenancyPermissions,
  tenancyRolePermissions,
  auditLog,
  ledgerEntries,
  walletBalances,
}

export type PressZoneSchema = typeof pressZoneSchema

const pressZoneInitStatements = [
  `
  CREATE TABLE IF NOT EXISTS accounts (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    slug text NOT NULL UNIQUE,
    type text NOT NULL DEFAULT 'individual',
    status text NOT NULL DEFAULT 'active',
    created_at timestamptz NOT NULL DEFAULT NOW()
  )`,
  `CREATE INDEX IF NOT EXISTS accounts_status_idx ON accounts (status)`,
  `
  CREATE TABLE IF NOT EXISTS plugins (
    key text PRIMARY KEY,
    name text NOT NULL,
    permission_catalog text[] NOT NULL DEFAULT '{}'::text[],
    created_at timestamptz NOT NULL DEFAULT NOW()
  )`,
  `
  CREATE TABLE IF NOT EXISTS subscription_packages (
    plugin_key text NOT NULL REFERENCES plugins(key) ON DELETE CASCADE,
    tier_key text NOT NULL,
    currency text NOT NULL,
    price_minor bigint NOT NULL,
    credit_allocation bigint NOT NULL DEFAULT 0,
    seats_config integer NOT NULL,
    paypal_plan_id text NOT NULL UNIQUE,
    created_at timestamptz NOT NULL DEFAULT NOW(),
    CONSTRAINT subscription_packages_plugin_tier_pk PRIMARY KEY (plugin_key, tier_key)
  )`,
  `CREATE INDEX IF NOT EXISTS subscription_packages_plugin_idx ON subscription_packages (plugin_key)`,
  `
  CREATE TABLE IF NOT EXISTS subscriptions (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    account_id uuid NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
    plugin_key text NOT NULL REFERENCES plugins(key) ON DELETE CASCADE,
    tier_key text NOT NULL,
    status text NOT NULL DEFAULT 'pending',
    current_period_start timestamptz,
    current_period_end timestamptz,
    paypal_subscription_id text UNIQUE,
    grace_period_end_at timestamptz,
    created_at timestamptz NOT NULL DEFAULT NOW(),
    updated_at timestamptz NOT NULL DEFAULT NOW()
  )`,
  `CREATE UNIQUE INDEX IF NOT EXISTS subscriptions_account_plugin_uq ON subscriptions (account_id, plugin_key)`,
  `CREATE INDEX IF NOT EXISTS subscriptions_status_idx ON subscriptions (status)`,
  `
  CREATE TABLE IF NOT EXISTS sites (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    account_id uuid NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
    plugin_key text NOT NULL REFERENCES plugins(key) ON DELETE CASCADE,
    display_url text NOT NULL,
    status text NOT NULL DEFAULT 'active',
    created_at timestamptz NOT NULL DEFAULT NOW(),
    disconnected_at timestamptz
  )`,
  `CREATE INDEX IF NOT EXISTS sites_account_plugin_idx ON sites (account_id, plugin_key)`,
  authUsersCreationSql(),
  userSessionsCreationSql(),
  serviceTokensCreationSql(),
  `
  CREATE TABLE IF NOT EXISTS api_keys (
    id text PRIMARY KEY,
    prefix text NOT NULL UNIQUE,
    secret_hash text NOT NULL,
    owner text NOT NULL,
    scopes text NOT NULL,
    created_at timestamptz NOT NULL,
    last_used_at timestamptz,
    revoked_at timestamptz,
    expires_at timestamptz
  )`,
  `
  CREATE TABLE IF NOT EXISTS oauth_clients (
    id text PRIMARY KEY,
    name text NOT NULL,
    redirect_uris text NOT NULL,
    scopes text NOT NULL,
    confidential boolean NOT NULL DEFAULT false,
    client_secret_hash text,
    created_at timestamptz NOT NULL
  )`,
  `
  CREATE TABLE IF NOT EXISTS oauth_codes (
    id text PRIMARY KEY,
    client_id text NOT NULL REFERENCES oauth_clients(id),
    redirect_uri text NOT NULL,
    scope text NOT NULL,
    code_challenge text NOT NULL,
    code_challenge_method text NOT NULL,
    code_hash text NOT NULL UNIQUE,
    created_at timestamptz NOT NULL,
    expires_at timestamptz NOT NULL,
    used_at timestamptz
  )`,
  `
  CREATE TABLE IF NOT EXISTS oauth_tokens (
    id text PRIMARY KEY,
    client_id text NOT NULL REFERENCES oauth_clients(id),
    code_id text NOT NULL REFERENCES oauth_codes(id),
    access_token_prefix text NOT NULL UNIQUE,
    access_token_hash text NOT NULL UNIQUE,
    scope text NOT NULL,
    token_type text NOT NULL,
    created_at timestamptz NOT NULL,
    expires_at timestamptz NOT NULL
  )`,
  `
  CREATE TABLE IF NOT EXISTS credentials (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    account_id uuid NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
    site_id uuid NOT NULL REFERENCES sites(id) ON DELETE CASCADE,
    provider text NOT NULL,
    api_key_id text REFERENCES api_keys(id) ON DELETE SET NULL,
    oauth_token_id text REFERENCES oauth_tokens(id) ON DELETE SET NULL,
    created_at timestamptz NOT NULL DEFAULT NOW(),
    revoked_at timestamptz,
    last_used_at timestamptz
  )`,
  `CREATE UNIQUE INDEX IF NOT EXISTS credentials_account_site_provider_uq ON credentials (account_id, site_id, provider)`,
  `
  CREATE TABLE IF NOT EXISTS members (
    account_id uuid NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
    user_id text NOT NULL REFERENCES auth_users(id) ON DELETE CASCADE,
    role_key text NOT NULL,
    status text NOT NULL DEFAULT 'active',
    created_at timestamptz NOT NULL DEFAULT NOW(),
    CONSTRAINT members_account_user_pk PRIMARY KEY (account_id, user_id)
  )`,
  `CREATE INDEX IF NOT EXISTS members_account_role_idx ON members (account_id, role_key)`,
  `
  CREATE TABLE IF NOT EXISTS entitlements (
    account text NOT NULL,
    capability text NOT NULL,
    tier text NOT NULL,
    granted_at timestamptz NOT NULL DEFAULT NOW(),
    expires_at timestamptz,
    CONSTRAINT entitlements_account_capability_pk PRIMARY KEY (account, capability)
  )`,
  `
  CREATE TABLE IF NOT EXISTS entitlement_tier_grants (
    capability text NOT NULL,
    tier text NOT NULL,
    quota_key text NOT NULL DEFAULT '',
    quota_value integer,
    CONSTRAINT entitlement_tier_grants_capability_tier_quota_pk
      PRIMARY KEY (capability, tier, quota_key)
  )`,
  `
  CREATE TABLE IF NOT EXISTS tenancy_roles (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id uuid NOT NULL,
    name text NOT NULL,
    is_system_role boolean NOT NULL DEFAULT false
  )`,
  `
  CREATE TABLE IF NOT EXISTS tenancy_permissions (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    key text NOT NULL UNIQUE
  )`,
  `
  CREATE TABLE IF NOT EXISTS tenancy_role_permissions (
    role_id uuid NOT NULL REFERENCES tenancy_roles(id) ON DELETE CASCADE,
    permission_id uuid NOT NULL REFERENCES tenancy_permissions(id) ON DELETE CASCADE,
    PRIMARY KEY (role_id, permission_id)
  )`,
  `
  CREATE TABLE IF NOT EXISTS audit_log (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    actor_id text,
    actor_type text,
    actor_label text,
    action text NOT NULL,
    entity_type text NOT NULL,
    entity_id text NOT NULL,
    metadata jsonb,
    ip text,
    tenant_id text,
    created_at timestamptz(3) NOT NULL DEFAULT NOW()
  )`,
  `CREATE INDEX IF NOT EXISTS audit_log_tenant_created_idx ON audit_log (tenant_id, created_at DESC)`,
  `CREATE INDEX IF NOT EXISTS audit_log_tenant_entity_idx ON audit_log (tenant_id, entity_type, entity_id)`,
  `CREATE INDEX IF NOT EXISTS audit_log_tenant_actor_idx ON audit_log (tenant_id, actor_id)`,
  `
  CREATE TABLE IF NOT EXISTS ledger_entries (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    delta bigint NOT NULL,
    currency text,
    reason text NOT NULL,
    ref jsonb,
    idempotency_key text NOT NULL,
    created_at timestamptz NOT NULL DEFAULT NOW()
  )`,
  `CREATE UNIQUE INDEX IF NOT EXISTS ledger_entries_idempotency_key_uq ON ledger_entries (idempotency_key)`,
  `
  CREATE TABLE IF NOT EXISTS wallet_balances (
    owner_id text PRIMARY KEY,
    balance bigint NOT NULL DEFAULT 0,
    updated_at timestamptz NOT NULL DEFAULT NOW()
  )`,
  `
  CREATE TABLE IF NOT EXISTS invoice_document (
    idempotency_key text PRIMARY KEY,
    document_id text NOT NULL,
    document_number text NOT NULL,
    document_url text NOT NULL,
    created_at timestamptz NOT NULL DEFAULT NOW()
  )`,
  `
  CREATE TABLE IF NOT EXISTS invoice_references (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    account_id uuid NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
    subscription_id uuid NOT NULL REFERENCES subscriptions(id) ON DELETE CASCADE,
    morning_document_id text NOT NULL,
    document_number text NOT NULL,
    document_url text NOT NULL,
    doc_type text NOT NULL,
    amount bigint NOT NULL,
    vat_amount bigint NOT NULL,
    currency text NOT NULL,
    idempotency_key text NOT NULL UNIQUE,
    created_at timestamptz NOT NULL DEFAULT NOW()
  )`,
  `CREATE INDEX IF NOT EXISTS invoice_references_account_idx ON invoice_references (account_id)`,
  `
  CREATE TABLE IF NOT EXISTS webhook_events (
    event_id text PRIMARY KEY,
    claimed_at timestamptz,
    processed_at timestamptz,
    created_at timestamptz NOT NULL DEFAULT NOW()
  )`,
  `
  CREATE TABLE IF NOT EXISTS charge_intents (
    charge_key text PRIMARY KEY,
    amount bigint NOT NULL,
    currency text NOT NULL,
    refund_reserved_minor bigint NOT NULL DEFAULT 0,
    status text NOT NULL DEFAULT 'pending',
    provider_ref text,
    claimed_at timestamptz NOT NULL DEFAULT NOW(),
    settled_at timestamptz,
    created_at timestamptz NOT NULL DEFAULT NOW()
  )`,
  `CREATE INDEX IF NOT EXISTS charge_intents_status_idx ON charge_intents (status)`,
  `
  CREATE TABLE IF NOT EXISTS refund_reservations (
    refund_key text PRIMARY KEY,
    charge_key text NOT NULL REFERENCES charge_intents(charge_key) ON DELETE CASCADE,
    wallet_key text NOT NULL,
    amount_minor bigint NOT NULL,
    currency text NOT NULL,
    status text NOT NULL DEFAULT 'pending',
    provider_refund_key text,
    completed_at timestamptz,
    created_at timestamptz NOT NULL DEFAULT NOW()
  )`,
] as const

export const pressZoneInitSql = `${pressZoneInitStatements.join(';\n\n')};`