import { randomUUID } from "crypto";
import type { RowDataPacket } from "mysql2/promise";
import { execute, queryRows, withTransaction } from "@/lib/db/pool";
import type { PoolConnection } from "mysql2/promise";

interface UserRow extends RowDataPacket {
  id: string;
  organization_id: string;
  email: string;
  full_name: string;
}

/** Ensure a session user exists in MariaDB (creates org + user on first touch). */
export async function ensureDbUser(input: {
  id: string;
  email: string;
  name: string;
}): Promise<{ userId: string; organizationId: string }> {
  const existing = await queryRows<UserRow[]>(
    "SELECT id, organization_id, email, full_name FROM users WHERE id = ? OR email = ? LIMIT 1",
    [input.id, input.email],
  );

  if (existing[0]) {
    return { userId: existing[0].id, organizationId: existing[0].organization_id };
  }

  const organizationId = randomUUID();
  const userId = input.id || randomUUID();
  const passwordHash = "legacy-session-user-no-password";

  await withTransaction(async (conn) => {
    await conn.execute(
      `INSERT INTO organizations (id, name, timezone) VALUES (?, ?, 'UTC')`,
      [organizationId, `${input.name || input.email} Org`],
    );
    await conn.execute(
      `INSERT INTO users (id, organization_id, email, password_hash, full_name, role, email_verified)
       VALUES (?, ?, ?, ?, ?, 'org_admin', 1)`,
      [userId, organizationId, input.email, passwordHash, input.name || input.email],
    );
    await conn.execute(
      `INSERT INTO user_workspaces (user_id, active_project_id) VALUES (?, NULL)
       ON DUPLICATE KEY UPDATE user_id = user_id`,
      [userId],
    );
  });

  return { userId, organizationId };
}

export async function getActiveProjectId(userId: string): Promise<string | null> {
  const rows = await queryRows<RowDataPacket[]>(
    "SELECT active_project_id FROM user_workspaces WHERE user_id = ? LIMIT 1",
    [userId],
  );
  return (rows[0]?.active_project_id as string | null) ?? null;
}

export async function setActiveProjectId(
  userId: string,
  activeProjectId: string | null,
  conn?: PoolConnection,
): Promise<void> {
  const sql = `INSERT INTO user_workspaces (user_id, active_project_id) VALUES (?, ?)
               ON DUPLICATE KEY UPDATE active_project_id = VALUES(active_project_id)`;
  const params = [userId, activeProjectId];
  if (conn) await conn.execute(sql, params);
  else await execute(sql, params);
}

export async function listDbUserIds(): Promise<string[]> {
  const rows = await queryRows<RowDataPacket[]>(
    `SELECT DISTINCT u.id AS id
     FROM users u
     LEFT JOIN projects p ON p.owner_user_id = u.id AND p.deleted_at IS NULL
     WHERE p.id IS NOT NULL
     ORDER BY u.id`,
  );
  return rows.map((r) => String(r.id));
}