import { existsSync } from 'node:fs';
import Database from 'better-sqlite3';
import { slugifyWorkspaceName, type Role } from '@wellformed/shared';
import { availableSlug } from '../tenancy/provision.js';
import { createAccount, getAccountByEmail } from '../control/accounts.js';
import { transactControl, type ControlDb } from '../control/db.js';
import {
  createWorkspace,
  setMembership,
  workspaceExistsForFile,
  type WorkspaceRecord,
} from '../control/workspaces.js';

export interface AdoptionResult {
  adopted: boolean;
  workspace?: WorkspaceRecord;
  accountsMoved: number;
}

interface LegacyUser {
  id: string;
  email: string;
  name: string;
  role: string;
  password_hash: string;
  disabled: number;
  created_at: string;
  last_seen_at: string | null;
}

/**
 * Turn a pre-tenancy `wellformed.db` into the installation's first workspace.
 *
 * Single-tenant Wellformed kept its users inside the same file as the data. Those
 * accounts have to be lifted into the control plane *before* the workspace
 * migrations run, because migration 2 drops the tables they live in. So this
 * opens the file directly, with no migration pass, reads what it needs and
 * closes again; the registry opens it properly afterwards and the drop happens
 * then.
 *
 * Idempotent, and a no-op when there is no such file: on a fresh install, or
 * once the file is already registered, it does nothing.
 */
export function adoptLegacyDatabase(
  control: ControlDb,
  options: { file: string; slug: string; name: string },
): AdoptionResult {
  if (!existsSync(options.file)) return { adopted: false, accountsMoved: 0 };
  if (workspaceExistsForFile(control, options.file)) return { adopted: false, accountsMoved: 0 };

  const users = readLegacyUsers(options.file);

  // The configured slug is a preference, not a promise: it may be reserved, or
  // already taken by a workspace created before this ran. Boot must not fail
  // over the *name* of the thing it is rescuing.
  const slug = availableSlug(control, slugifyWorkspaceName(options.slug));

  return transactControl(control, () => {
    const workspace = createWorkspace(control, {
      slug,
      name: options.name,
      // Adopted in place: the file keeps its path, so an existing backup script
      // or bind mount pointing at it goes on working.
      dbFile: options.file,
    });

    let accountsMoved = 0;
    for (const user of users) {
      const email = user.email.trim().toLowerCase();
      // An account may already exist if adoption ran, was interrupted after the
      // account insert, and is running again.
      const existing = getAccountByEmail(control, email);
      const account =
        existing ??
        createAccount(control, {
          id: user.id,
          email,
          name: user.name,
          passwordHash: user.password_hash,
          disabled: user.disabled === 1,
          createdAt: user.created_at,
          lastSeenAt: user.last_seen_at,
        });
      setMembership(control, workspace.id, account.id, normaliseRole(user.role));
      accountsMoved++;
    }

    return { adopted: true, workspace, accountsMoved };
  });
}

function normaliseRole(role: string): Role {
  return role === 'admin' || role === 'editor' ? role : 'viewer';
}

function readLegacyUsers(file: string): LegacyUser[] {
  const sqlite = new Database(file, { readonly: true });
  try {
    const hasUsers = sqlite
      .prepare<[], { n: number }>(
        `SELECT COUNT(*) AS n FROM sqlite_master WHERE type = 'table' AND name = '_wellformed_user'`,
      )
      .get();
    if ((hasUsers?.n ?? 0) === 0) return [];

    return sqlite
      .prepare<[], LegacyUser>(
        `SELECT id, email, name, role, password_hash, disabled, created_at, last_seen_at
         FROM _wellformed_user`,
      )
      .all();
  } finally {
    sqlite.close();
  }
}