import { drizzle } from "drizzle-orm/node-postgres";
import { sql } from "drizzle-orm";
import { Pool, PoolConfig } from "pg";

const databaseUrl = process.env.DATABASE_URL;

if (!databaseUrl) {
  throw new Error("DATABASE_URL environment variable is required");
}

const poolConfig: PoolConfig = {
  connectionString: databaseUrl,
  max: Number(process.env.DB_POOL_MAX || "20"),
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 10000,
  ssl:
    process.env.DB_SSL === "true" || databaseUrl.includes("sslmode=require")
      ? { rejectUnauthorized: false }
      : undefined,
};

const globalForDb = globalThis as typeof globalThis & {
  __claimsPostgresqlPool?: Pool;
  __claimsDbInitialized?: boolean;
};

export const pool = globalForDb.__claimsPostgresqlPool ?? new Pool(poolConfig);

pool.on("error", (err) => {
  console.error("Unexpected error on idle PostgreSQL client:", err);
});

if (process.env.NODE_ENV !== "production") {
  globalForDb.__claimsPostgresqlPool = pool;
}

export const db = drizzle(pool);

/**
 * Ensures all required PostgreSQL tables and indexes exist on fresh VPS deployments.
 * Runs idempotently on first start.
 */
export async function ensureDatabaseTables() {
  if (globalForDb.__claimsDbInitialized) return;

  try {
    // 1. admin_settings table
    await db.execute(sql`
      CREATE TABLE IF NOT EXISTS admin_settings (
        key VARCHAR(64) PRIMARY KEY,
        value TEXT NOT NULL,
        updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL
      );
    `);

    // 2. winning_entries table
    await db.execute(sql`
      CREATE TABLE IF NOT EXISTS winning_entries (
        id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
        serial_number VARCHAR(32) NOT NULL,
        winning_numbers VARCHAR(40) NOT NULL,
        prize_amount VARCHAR(80) DEFAULT '$1,300,000,000' NOT NULL,
        issue_date VARCHAR(40) DEFAULT '2026-01-15' NOT NULL,
        recipient_email VARCHAR(254),
        status VARCHAR(60) DEFAULT 'active' NOT NULL,
        active BOOLEAN DEFAULT TRUE NOT NULL,
        claimed_at TIMESTAMP WITH TIME ZONE,
        created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL
      );
    `);

    await db.execute(sql`
      CREATE UNIQUE INDEX IF NOT EXISTS winning_entries_serial_unique 
      ON winning_entries (serial_number);
    `);

    // 3. winner_claims table
    await db.execute(sql`
      CREATE TABLE IF NOT EXISTS winner_claims (
        id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
        entry_id UUID NOT NULL REFERENCES winning_entries(id) ON DELETE CASCADE,
        first_name VARCHAR(80) NOT NULL,
        last_name VARCHAR(80) NOT NULL,
        middle_name VARCHAR(80),
        date_of_birth VARCHAR(30),
        nationality VARCHAR(100),
        id_number VARCHAR(80),
        email VARCHAR(254) NOT NULL,
        phone VARCHAR(50) NOT NULL,
        preferred_contact VARCHAR(20) NOT NULL,
        social_media VARCHAR(180),
        address VARCHAR(180) NOT NULL,
        city VARCHAR(100) NOT NULL,
        region VARCHAR(100) NOT NULL,
        postal_code VARCHAR(20) NOT NULL,
        country VARCHAR(100) NOT NULL,
        ticket_number VARCHAR(32) NOT NULL,
        winning_numbers VARCHAR(40) NOT NULL,
        prize_amount VARCHAR(80),
        payment_preference VARCHAR(20) NOT NULL,
        notes TEXT,
        signature VARCHAR(120),
        certified BOOLEAN NOT NULL,
        status VARCHAR(60) DEFAULT 'under_review' NOT NULL,
        created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL
      );
    `);

    globalForDb.__claimsDbInitialized = true;
  } catch (err) {
    console.error("Auto table creation notice:", err);
  }
}