import { randomUUID } from "crypto";
import type { RowDataPacket } from "mysql2/promise";
import { isMariaDbEnabled } from "@/lib/db/config";
import { toMysqlDateTime } from "@/lib/db/datetime";
import { execute, queryRows } from "@/lib/db/pool";
import { ensureDbUser } from "@/lib/db/repositories/users-repo";

/**
 * Ensure a projects row exists for OAuth FK (connected_accounts.project_id).
 * Creates a minimal stub from session context when the browser project
 * has not been synced to MariaDB yet.
 */
export async function ensureProjectForOAuth(input: {
  projectId: string;
  userId: string;
  email?: string;
  name?: string;
  projectName?: string;
  website?: string;
  industry?: string;
  timezone?: string;
}): Promise<void> {
  if (!isMariaDbEnabled()) return;

  const existing = await queryRows<RowDataPacket[]>(
    "SELECT id FROM projects WHERE id = ? AND deleted_at IS NULL LIMIT 1",
    [input.projectId],
  );
  if (existing[0]) return;

  const { userId, organizationId } = await ensureDbUser({
    id: input.userId,
    email: input.email || `${input.userId}@postsync.local`,
    name: input.name || input.userId,
  });

  const now = toMysqlDateTime(new Date())!;
  await execute(
    `INSERT INTO projects (
       id, organization_id, owner_user_id, name, website_url, industry, status,
       countries_json, timezone, created_at, updated_at
     ) VALUES (?, ?, ?, ?, ?, ?, 'active', ?, ?, ?, ?)
     ON DUPLICATE KEY UPDATE
       deleted_at = NULL,
       owner_user_id = COALESCE(owner_user_id, VALUES(owner_user_id)),
       name = COALESCE(NULLIF(name, ''), VALUES(name)),
       updated_at = VALUES(updated_at)`,
    [
      input.projectId,
      organizationId,
      userId,
      input.projectName?.trim() || "Untitled Project",
      input.website?.trim() || null,
      input.industry?.trim() || null,
      JSON.stringify([]),
      input.timezone?.trim() || "UTC",
      now,
      now,
    ],
  );

  // Brand stub so later project sync has a row to update.
  await execute(
    `INSERT INTO brand_profiles (id, project_id, company_name)
     VALUES (?, ?, ?)
     ON DUPLICATE KEY UPDATE company_name = COALESCE(company_name, VALUES(company_name))`,
    [randomUUID(), input.projectId, input.projectName?.trim() || "Untitled Project"],
  );
}

/** @deprecated Use ensureProjectForOAuth — auto-creates stub instead of throwing. */
export async function assertProjectExistsForOAuth(projectId: string): Promise<void> {
  if (!isMariaDbEnabled()) return;
  const rows = await queryRows<RowDataPacket[]>(
    "SELECT id FROM projects WHERE id = ? AND deleted_at IS NULL LIMIT 1",
    [projectId],
  );
  if (!rows[0]) {
    throw new Error(
      `Project ${projectId} is not saved on the server yet. Open My Project, click Save, then reconnect the social account.`,
    );
  }
}