import { randomUUID } from 'node:crypto';
import bcrypt from 'bcryptjs';
import { Pool } from 'pg';
import { config } from './config';
import { runMigrations } from './migrationRunner';

export type UserRole = 'user' | 'admin';

export interface DatabaseUser {
  id: string;
  email: string;
  username: string | null;
  account_type: 'candidate' | 'guardian' | 'representative';
  password_hash: string;
  display_name: string;
  role: UserRole;
  is_active: boolean;
  token_version: number;
  deleted_at: Date | null;
  created_at: Date;
}

export const pool = new Pool({
  connectionString: config.databaseUrl,
  max: 10,
});

export const initializeDatabase = async (): Promise<void> => {
  await pool.query(`
    CREATE TABLE IF NOT EXISTS users (
      id UUID PRIMARY KEY,
      email TEXT NOT NULL UNIQUE,
      password_hash TEXT NOT NULL,
      display_name TEXT NOT NULL,
      role TEXT NOT NULL CHECK (role IN ('user', 'admin')),
      is_active BOOLEAN NOT NULL DEFAULT TRUE,
      created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
    );

    CREATE TABLE IF NOT EXISTS app_states (
      user_id UUID PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
      state JSONB NOT NULL,
      updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
    );

    CREATE TABLE IF NOT EXISTS audit_events (
      id BIGSERIAL PRIMARY KEY,
      user_id UUID REFERENCES users(id) ON DELETE SET NULL,
      event_type TEXT NOT NULL,
      details JSONB NOT NULL DEFAULT '{}'::jsonb,
      created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
    );

    CREATE INDEX IF NOT EXISTS audit_events_user_id_idx ON audit_events(user_id);
    CREATE INDEX IF NOT EXISTS audit_events_created_at_idx ON audit_events(created_at DESC);
  `);

  await runMigrations(pool);

  if (!config.seedDemoAccounts) return;

  await seedUser(
    config.seedUserEmail,
    config.seedUserPassword,
    'أحمد بن منصور',
    'user',
  );
  await seedUser(
    config.seedPartnerEmail,
    config.seedPartnerPassword,
    'مريم',
    'user',
  );
  await pool.query(
    `INSERT INTO profiles (user_id, display_name, city, bio, birth_year, is_published)
     SELECT id, display_name, 'فرانكفورت، ألمانيا',
            'I am looking for a thoughtful and respectful family introduction.', 1993, TRUE
     FROM users WHERE email = $1
     ON CONFLICT (user_id) DO NOTHING`,
    [config.seedUserEmail.trim().toLowerCase()],
  );
  await pool.query(
    `INSERT INTO profiles (user_id, display_name, city, bio, birth_year, is_published)
     SELECT id, display_name, 'كولن، ألمانيا',
            'I value clarity, family understanding, and a calm conversation.', 1997, TRUE
     FROM users WHERE email = $1
     ON CONFLICT (user_id) DO NOTHING`,
    [config.seedPartnerEmail.trim().toLowerCase()],
  );
  await seedUser(
    config.seedAdminEmail,
    config.seedAdminPassword,
    'مدير النظام',
    'admin',
  );
};

const seedUser = async (
  email: string,
  password: string,
  displayName: string,
  role: UserRole,
): Promise<void> => {
  const normalizedEmail = email.trim().toLowerCase();
  const suppressed = await pool.query(
    'SELECT 1 FROM seed_account_suppressions WHERE email=$1',
    [normalizedEmail],
  );
  if (suppressed.rowCount) return;
  const existing = await pool.query<{ id: string }>('SELECT id FROM users WHERE email = $1', [
    normalizedEmail,
  ]);
  if (existing.rowCount) return;

  const id = randomUUID();
  const passwordHash = await bcrypt.hash(password, 12);
  await pool.query(
    `INSERT INTO users (id, email, password_hash, display_name, role)
     VALUES ($1, $2, $3, $4, $5)`,
    [id, normalizedEmail, passwordHash, displayName, role],
  );

};

export const findUserByEmail = async (email: string): Promise<DatabaseUser | null> => {
  const result = await pool.query<DatabaseUser>(
    'SELECT * FROM users WHERE email = $1 LIMIT 1',
    [email.trim().toLowerCase()],
  );
  return result.rows[0] ?? null;
};

export const findUserByIdentifier = async (identifier: string): Promise<DatabaseUser | null> => {
  const normalized = identifier.trim().toLowerCase();
  const result = await pool.query<DatabaseUser>(
    `SELECT * FROM users
     WHERE LOWER(email)=$1 OR LOWER(username)=$1
     LIMIT 1`,
    [normalized],
  );
  return result.rows[0] ?? null;
};