import { integer, pgTable, text, timestamp } from 'drizzle-orm/pg-core'

/** Engine-owned identity row — `sessionVersion` drives `sv` revocation. */
export const authUsers = pgTable('auth_users', {
  id: text('id').primaryKey(),
  email: text('email').notNull().unique(),
  passwordHash: text('password_hash'),
  sessionVersion: integer('session_version').notNull().default(0),
  roles: text('roles').notNull().default('["user"]'),
  status: text('status').notNull().default('active'),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull(),
})

/** Idempotent DDL to create the auth_users table (neon-http: one statement). FK root — run first. */
export const authUsersCreationSql = () =>
  `CREATE TABLE IF NOT EXISTS auth_users (
    id text PRIMARY KEY,
    email text NOT NULL UNIQUE,
    password_hash text,
    session_version integer NOT NULL DEFAULT 0,
    roles text NOT NULL DEFAULT '["user"]',
    status text NOT NULL DEFAULT 'active',
    created_at timestamptz NOT NULL
  )`

/** Idempotent DDL to create the user_sessions table (neon-http: one statement). FKs auth_users. */
export const userSessionsCreationSql = () =>
  `CREATE TABLE IF NOT EXISTS user_sessions (
    id text PRIMARY KEY,
    user_id text NOT NULL REFERENCES auth_users(id),
    refresh_token_hash text NOT NULL,
    previous_refresh_token_hash text,
    last_refreshed_at timestamptz,
    expires_at timestamptz NOT NULL,
    status text NOT NULL DEFAULT 'active'
  )`

/** Idempotent DDL for adopters migrating existing auth_users tables (neon-http: one statement). */
export const authUsersStatusMigrationSql = () =>
  `ALTER TABLE auth_users ADD COLUMN IF NOT EXISTS status text NOT NULL DEFAULT 'active'`

/** Idempotent DDL to create the service_tokens table (neon-http: one statement). FKs auth_users. */
export const serviceTokensCreationSql = () =>
  `CREATE TABLE IF NOT EXISTS service_tokens (
    id text PRIMARY KEY,
    user_id text NOT NULL REFERENCES auth_users(id),
    token_hash text NOT NULL UNIQUE,
    label text NOT NULL,
    expires_at timestamptz,
    status text NOT NULL DEFAULT 'active',
    created_at timestamptz NOT NULL,
    last_used_at timestamptz
  )`

/** One refresh-token row per device/session — no access-token hash column. */
export const userSessions = pgTable('user_sessions', {
  id: text('id').primaryKey(),
  userId: text('user_id')
    .notNull()
    .references(() => authUsers.id),
  refreshTokenHash: text('refresh_token_hash').notNull(),
  previousRefreshTokenHash: text('previous_refresh_token_hash'),
  lastRefreshedAt: timestamp('last_refreshed_at', { withTimezone: true }),
  expiresAt: timestamp('expires_at', { withTimezone: true }).notNull(),
  status: text('status').notNull().default('active'),
})

/** Opaque machine/agent credential (PAT shape) — independent of userSessions; not rotating. */
export const serviceTokens = pgTable('service_tokens', {
  id: text('id').primaryKey(),
  userId: text('user_id')
    .notNull()
    .references(() => authUsers.id),
  tokenHash: text('token_hash').notNull().unique(),
  label: text('label').notNull(),
  expiresAt: timestamp('expires_at', { withTimezone: true }), // null = non-expiring
  status: text('status').notNull().default('active'),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull(),
  lastUsedAt: timestamp('last_used_at', { withTimezone: true }),
})

export type AuthUsersTable = typeof authUsers
export type UserSessionsTable = typeof userSessions
export type ServiceTokensTable = typeof serviceTokens

export type CustomAuthSchema = {
  authUsers: AuthUsersTable
  userSessions: UserSessionsTable
  serviceTokens: ServiceTokensTable
}

export const customAuthSchema = {
  authUsers,
  userSessions,
  serviceTokens,
}