import {
  assertWorkspaceSlug,
  isLocale,
  planOf,
  DEFAULT_LOCALE,
  type Locale,
  type Member,
  type Role,
  type SubscriptionStatus,
  type Workspace,
  type WorkspaceStatus,
} from '@wellformed/shared';
import { newId } from '../engine/ids.js';
import { toAccount } from './accounts.js';
import type { ControlDb } from './db.js';

/**
 * A workspace as the server knows it, including where its data lives.
 *
 * `dbFile` is relative to the tenant directory for workspaces Wellformed created,
 * and absolute for a database adopted from a single-tenant install. The
 * registry is the only thing that resolves it to a path.
 */
export interface WorkspaceRecord {
  id: string;
  slug: string;
  name: string;
  dbFile: string;
  status: WorkspaceStatus;
  currency: string;
  locale: Locale;
  createdAt: string;
  createdBy: string | null;
}

interface WorkspaceRow {
  id: string;
  slug: string;
  name: string;
  db_file: string;
  status: string;
  currency: string;
  locale: string;
  created_at: string;
  created_by: string | null;
}

/** A stored locale this build no longer supports falls back rather than crashes. */
function toLocale(value: string): Locale {
  return isLocale(value) ? value : DEFAULT_LOCALE;
}

function toRecord(row: WorkspaceRow): WorkspaceRecord {
  return {
    id: row.id,
    slug: row.slug,
    name: row.name,
    dbFile: row.db_file,
    status: row.status as WorkspaceStatus,
    currency: row.currency,
    locale: toLocale(row.locale),
    createdAt: row.created_at,
    createdBy: row.created_by,
  };
}

const COLUMNS = 'id, slug, name, db_file, status, currency, locale, created_at, created_by';

export function getWorkspaceBySlug(control: ControlDb, slug: string): WorkspaceRecord | null {
  const row = control.sqlite
    .prepare<[string], WorkspaceRow>(`SELECT ${COLUMNS} FROM workspace WHERE slug = ?`)
    .get(slug.trim().toLowerCase());
  return row ? toRecord(row) : null;
}

export function getWorkspaceById(control: ControlDb, id: string): WorkspaceRecord | null {
  const row = control.sqlite
    .prepare<[string], WorkspaceRow>(`SELECT ${COLUMNS} FROM workspace WHERE id = ?`)
    .get(id);
  return row ? toRecord(row) : null;
}

export function listAllWorkspaces(control: ControlDb): WorkspaceRecord[] {
  return control.sqlite
    .prepare<[], WorkspaceRow>(`SELECT ${COLUMNS} FROM workspace ORDER BY created_at`)
    .all()
    .map(toRecord);
}

export function workspaceExistsForFile(control: ControlDb, dbFile: string): boolean {
  const row = control.sqlite
    .prepare<[string], { n: number }>('SELECT COUNT(*) AS n FROM workspace WHERE db_file = ?')
    .get(dbFile);
  return (row?.n ?? 0) > 0;
}

export interface NewWorkspace {
  slug: string;
  name: string;
  createdBy?: string | null;
  /** The language the workspace opens in. Defaults to English. */
  locale?: Locale;
  /** Only supplied when adopting an existing file; otherwise derived from the id. */
  dbFile?: string;
  id?: string;
}

export function createWorkspace(control: ControlDb, input: NewWorkspace): WorkspaceRecord {
  // Validated here rather than at each caller, because a workspace whose slug
  // cannot appear in a URL is a workspace nobody can open — and the callers
  // include adoption and the CLI, not only the signup route.
  assertWorkspaceSlug(input.slug.trim().toLowerCase());

  const id = input.id ?? newId('ws');
  // The file is named after the immutable id, never the slug, so renaming a
  // workspace is a metadata change and never touches the filesystem.
  const dbFile = input.dbFile ?? `${id}.db`;

  control.sqlite
    .prepare(
      'INSERT INTO workspace (id, slug, name, db_file, locale, created_by) VALUES (?, ?, ?, ?, ?, ?)',
    )
    .run(
      id,
      input.slug.trim().toLowerCase(),
      input.name.trim(),
      dbFile,
      input.locale ?? DEFAULT_LOCALE,
      input.createdBy ?? null,
    );

  return getWorkspaceById(control, id)!;
}

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

export function setWorkspaceSlug(control: ControlDb, id: string, slug: string): void {
  control.sqlite.prepare('UPDATE workspace SET slug = ? WHERE id = ?').run(slug.trim().toLowerCase(), id);
}

export function setWorkspaceCurrency(control: ControlDb, id: string, currency: string): void {
  control.sqlite
    .prepare('UPDATE workspace SET currency = ? WHERE id = ?')
    .run(currency.trim().toUpperCase(), id);
}

export function setWorkspaceLocale(control: ControlDb, id: string, locale: Locale): void {
  control.sqlite.prepare('UPDATE workspace SET locale = ? WHERE id = ?').run(locale, id);
}

export function setWorkspaceStatus(control: ControlDb, id: string, status: WorkspaceStatus): void {
  control.sqlite.prepare('UPDATE workspace SET status = ? WHERE id = ?').run(status, id);
}

export function deleteWorkspace(control: ControlDb, id: string): void {
  control.sqlite.prepare('DELETE FROM workspace WHERE id = ?').run(id);
}

// ---- memberships ----------------------------------------------------------

/**
 * The role `accountId` holds in `workspaceId`, or null when they hold none.
 *
 * This single lookup is the whole authorisation boundary between tenants: no
 * membership row, no workspace database handle, no data. Every workspace-scoped
 * route reaches its data through it.
 */
export function getMembershipRole(
  control: ControlDb,
  workspaceId: string,
  accountId: string,
): Role | null {
  const row = control.sqlite
    .prepare<[string, string], { role: string }>(
      'SELECT role FROM membership WHERE workspace_id = ? AND account_id = ?',
    )
    .get(workspaceId, accountId);
  return (row?.role as Role) ?? null;
}

export function setMembership(
  control: ControlDb,
  workspaceId: string,
  accountId: string,
  role: Role,
): void {
  control.sqlite
    .prepare(
      `INSERT INTO membership (workspace_id, account_id, role) VALUES (?, ?, ?)
       ON CONFLICT (workspace_id, account_id) DO UPDATE SET role = excluded.role`,
    )
    .run(workspaceId, accountId, role);
}

export function removeMembership(control: ControlDb, workspaceId: string, accountId: string): void {
  control.sqlite
    .prepare('DELETE FROM membership WHERE workspace_id = ? AND account_id = ?')
    .run(workspaceId, accountId);
}

/** Every workspace `accountId` can reach, with the role they hold in each. */
export function listWorkspacesForAccount(control: ControlDb, accountId: string): Workspace[] {
  return control.sqlite
    .prepare<
      [string],
      WorkspaceRow & { role: string; plan: string; billing_status: string; trial_ends_at: string | null }
    >(
      `SELECT w.id, w.slug, w.name, w.db_file, w.status, w.currency, w.locale, w.created_at,
              w.created_by, w.plan, w.billing_status, w.trial_ends_at, m.role
       FROM membership m JOIN workspace w ON w.id = m.workspace_id
       WHERE m.account_id = ?
       ORDER BY w.name COLLATE NOCASE`,
    )
    .all(accountId)
    .map(toWorkspace);
}

/**
 * A workspace as one account sees it: the record, their role, and enough of
 * the plan for the shell to warn about a trial running out without a second
 * request on every page load.
 */
function toWorkspace(
  row: WorkspaceRow & { role: string; plan: string; billing_status: string; trial_ends_at: string | null },
): Workspace {
  return {
    id: row.id,
    slug: row.slug,
    name: row.name,
    role: row.role as Role,
    status: row.status as WorkspaceStatus,
    currency: row.currency,
    locale: toLocale(row.locale),
    createdAt: row.created_at,
    plan: planOf(row.plan).id,
    billingStatus: row.billing_status as SubscriptionStatus,
    trialEndsAt: row.trial_ends_at,
  };
}

/** One workspace, as `accountId` sees it. Null when they hold no membership. */
export function workspaceForAccount(
  control: ControlDb,
  workspaceId: string,
  accountId: string,
): Workspace | null {
  const row = control.sqlite
    .prepare<
      [string, string],
      WorkspaceRow & { role: string; plan: string; billing_status: string; trial_ends_at: string | null }
    >(
      `SELECT w.id, w.slug, w.name, w.db_file, w.status, w.currency, w.locale, w.created_at,
              w.created_by, w.plan, w.billing_status, w.trial_ends_at, m.role
       FROM membership m JOIN workspace w ON w.id = m.workspace_id
       WHERE w.id = ? AND m.account_id = ?`,
    )
    .get(workspaceId, accountId);
  return row ? toWorkspace(row) : null;
}

export function listMembers(control: ControlDb, workspaceId: string): Member[] {
  return control.sqlite
    .prepare<
      [string],
      {
        id: string;
        email: string;
        name: string;
        disabled: number;
        created_at: string;
        last_seen_at: string | null;
        verified_at: string | null;
        role: string;
        joined_at: string;
      }
    >(
      `SELECT a.id, a.email, a.name, a.disabled, a.created_at, a.last_seen_at, a.verified_at,
              m.role, m.created_at AS joined_at
       FROM membership m JOIN account a ON a.id = m.account_id
       WHERE m.workspace_id = ?
       ORDER BY m.created_at`,
    )
    .all(workspaceId)
    .map((row) => ({ ...toAccount(row), role: row.role as Role, joinedAt: row.joined_at }));
}

/** Guards the "do not lock every admin out of a workspace" rule. */
export function countActiveAdmins(control: ControlDb, workspaceId: string): number {
  const row = control.sqlite
    .prepare<[string], { n: number }>(
      `SELECT COUNT(*) AS n FROM membership m JOIN account a ON a.id = m.account_id
       WHERE m.workspace_id = ? AND m.role = 'admin' AND a.disabled = 0`,
    )
    .get(workspaceId);
  return row?.n ?? 0;
}

/** How many workspaces this account is the last remaining admin of. */
export function countWorkspacesOwnedAlone(control: ControlDb, accountId: string): number {
  const row = control.sqlite
    .prepare<[string, string], { n: number }>(
      `SELECT COUNT(*) AS n FROM membership mine
       WHERE mine.account_id = ? AND mine.role = 'admin'
         AND (SELECT COUNT(*) FROM membership other JOIN account a ON a.id = other.account_id
              WHERE other.workspace_id = mine.workspace_id AND other.role = 'admin'
                AND other.account_id <> ? AND a.disabled = 0) = 0`,
    )
    .get(accountId, accountId);
  return row?.n ?? 0;
}