import type { Account } from '@wellformed/shared';
import type { ControlDb } from './db.js';
import { newId } from '../engine/ids.js';

interface AccountRow {
  id: string;
  email: string;
  name: string;
  disabled: number;
  created_at: string;
  last_seen_at: string | null;
  verified_at: string | null;
}

export function toAccount(row: AccountRow): Account {
  return {
    id: row.id,
    email: row.email,
    name: row.name,
    createdAt: row.created_at,
    lastSeenAt: row.last_seen_at,
    disabled: row.disabled === 1,
    verified: row.verified_at !== null,
  };
}

const COLUMNS = 'id, email, name, disabled, created_at, last_seen_at, verified_at';

/** Emails are compared and stored lowercase; the UNIQUE index is the enforcement. */
export function normaliseEmail(email: string): string {
  return email.trim().toLowerCase();
}

export function getAccountById(control: ControlDb, id: string): Account | null {
  const row = control.sqlite
    .prepare<[string], AccountRow>(`SELECT ${COLUMNS} FROM account WHERE id = ?`)
    .get(id);
  return row ? toAccount(row) : null;
}

export function getAccountByEmail(control: ControlDb, email: string): Account | null {
  const row = control.sqlite
    .prepare<[string], AccountRow>(`SELECT ${COLUMNS} FROM account WHERE email = ?`)
    .get(normaliseEmail(email));
  return row ? toAccount(row) : null;
}

export function getAccountWithHash(
  control: ControlDb,
  email: string,
): (Account & { passwordHash: string }) | null {
  const row = control.sqlite
    .prepare<[string], AccountRow & { password_hash: string }>(
      `SELECT ${COLUMNS}, password_hash FROM account WHERE email = ?`,
    )
    .get(normaliseEmail(email));
  return row ? { ...toAccount(row), passwordHash: row.password_hash } : null;
}

export interface NewAccount {
  email: string;
  name: string;
  passwordHash: string;
  /** Supplied only when adopting an existing database, which carries its own ids. */
  id?: string;
  createdAt?: string;
  lastSeenAt?: string | null;
  disabled?: boolean;
}

export function createAccount(control: ControlDb, input: NewAccount): Account {
  const id = input.id ?? newId('acc');
  control.sqlite
    .prepare(
      `INSERT INTO account (id, email, name, password_hash, disabled, created_at, last_seen_at)
       VALUES (?, ?, ?, ?, ?, COALESCE(?, datetime('now')), ?)`,
    )
    .run(
      id,
      normaliseEmail(input.email),
      input.name.trim(),
      input.passwordHash,
      input.disabled ? 1 : 0,
      input.createdAt ?? null,
      input.lastSeenAt ?? null,
    );
  return getAccountById(control, id)!;
}

export function setAccountPassword(control: ControlDb, id: string, passwordHash: string): void {
  control.sqlite.prepare('UPDATE account SET password_hash = ? WHERE id = ?').run(passwordHash, id);
}

export function setAccountVerified(control: ControlDb, id: string): void {
  control.sqlite
    .prepare(`UPDATE account SET verified_at = datetime('now') WHERE id = ? AND verified_at IS NULL`)
    .run(id);
}

export function setAccountName(control: ControlDb, id: string, name: string): void {
  control.sqlite.prepare('UPDATE account SET name = ? WHERE id = ?').run(name.trim(), id);
}

export function setAccountDisabled(control: ControlDb, id: string, disabled: boolean): void {
  control.sqlite.prepare('UPDATE account SET disabled = ? WHERE id = ?').run(disabled ? 1 : 0, id);
}

export function touchLastSeen(control: ControlDb, id: string): void {
  control.sqlite.prepare(`UPDATE account SET last_seen_at = datetime('now') WHERE id = ?`).run(id);
}

/**
 * Delete an account outright.
 *
 * Memberships and sessions cascade. Audit lines inside each workspace do not —
 * they keep the name of whoever made the change, which is the point of them.
 */
export function deleteAccount(control: ControlDb, id: string): void {
  control.sqlite.prepare('DELETE FROM account WHERE id = ?').run(id);
}