import { randomUUID } from "crypto";
import type { PoolConnection, RowDataPacket } from "mysql2/promise";
import {
  createDefaultScopedData,
  mergeProjectAccounts,
  type ProjectScopedData,
} from "@/lib/project/defaults";
import type { BrandProfile, ConnectedAccount, PostSequenceItem, Project } from "@/types/workflow";
import { fromMysqlDateTime, toMysqlDateTime } from "@/lib/db/datetime";
import { jsonString, parseJson, queryRows, withTransaction } from "@/lib/db/pool";
import { ensureDbUser, getActiveProjectId, setActiveProjectId } from "./users-repo";

export interface UserProjectsSnapshot {
  activeProjectId: string | null;
  projects: Project[];
  dataByProjectId: Record<string, ProjectScopedData>;
  updatedAt: string;
}

interface ProjectRow extends RowDataPacket {
  id: string;
  organization_id: string;
  owner_user_id: string | null;
  name: string;
  website_url: string | null;
  industry: string | null;
  status: "active" | "paused";
  countries_json: string | null;
  timezone: string | null;
  created_at: string;
  updated_at: string;
}

interface BrandRow extends RowDataPacket {
  project_id: string;
  company_name: string | null;
  tagline: string | null;
  industry: string | null;
  brand_voice: string | null;
  logo_data_url: string | null;
  logo_file_name: string | null;
  contact_phone: string | null;
  contact_email: string | null;
}

interface ContextRow extends RowDataPacket {
  id: string;
  project_id: string;
  name: string;
  saved: number;
  selected_post_id: string | null;
  posts_json: string | null;
  posts_per_day: number;
  automation_config_json: string | null;
}

interface AccountRow extends RowDataPacket {
  platform: string;
  label: string | null;
  account_name: string | null;
  external_id: string | null;
  status: string;
  token_expires_at: string | null;
  last_synced_at: string | null;
}

interface ScheduleRow extends RowDataPacket {
  config_json: string;
}

function mapProject(row: ProjectRow): Project {
  return {
    id: row.id,
    name: row.name,
    website: row.website_url ?? "",
    industry: row.industry ?? "",
    timezone: row.timezone ?? "UTC",
    countries: parseJson<string[]>(row.countries_json, []),
    status: row.status === "paused" ? "paused" : "active",
    createdAt: fromMysqlDateTime(row.created_at) ?? new Date().toISOString(),
    updatedAt: fromMysqlDateTime(row.updated_at) ?? new Date().toISOString(),
  };
}

function mapBrand(row: BrandRow | undefined, project: Project): BrandProfile {
  if (!row) {
    return {
      companyName: project.name,
      tagline: "",
      industry: project.industry,
      brandVoice: "Professional",
      logoDataUrl: null,
      logoFileName: null,
      contactPhone: "",
      contactEmail: "",
    };
  }
  return {
    companyName: row.company_name ?? project.name,
    tagline: row.tagline ?? "",
    industry: row.industry ?? project.industry,
    brandVoice: row.brand_voice ?? "Professional",
    logoDataUrl: row.logo_data_url,
    logoFileName: row.logo_file_name,
    contactPhone: row.contact_phone ?? "",
    contactEmail: row.contact_email ?? "",
  };
}

function mapAccounts(rows: AccountRow[]): ConnectedAccount[] {
  const mapped = rows.map((row) => {
    const status = row.status as ConnectedAccount["connectionStatus"];
    const connected = status === "connected";
    return {
      platform: row.platform,
      label: row.label ?? row.platform,
      profileName: row.account_name ?? "Not connected",
      connected,
      connectionStatus:
        status === "connected" ||
        status === "disconnected" ||
        status === "expired" ||
        status === "error"
          ? status
          : connected
            ? "connected"
            : "disconnected",
      accessToken: null,
      refreshToken: null,
      expiresAt: row.token_expires_at ? fromMysqlDateTime(row.token_expires_at) : null,
      lastSyncedAt: row.last_synced_at ? fromMysqlDateTime(row.last_synced_at) : null,
      externalId: row.external_id,
    } satisfies ConnectedAccount;
  });
  return mergeProjectAccounts(mapped);
}

async function loadScopedData(
  project: Project,
  organizationId: string,
): Promise<ProjectScopedData> {
  const defaults = createDefaultScopedData();

  const [brands, contexts, accounts, schedules] = await Promise.all([
    queryRows<BrandRow[]>("SELECT * FROM brand_profiles WHERE project_id = ? LIMIT 1", [
      project.id,
    ]),
    queryRows<ContextRow[]>(
      `SELECT id, project_id, name, saved, selected_post_id, posts_json, posts_per_day, automation_config_json
       FROM posting_contexts WHERE project_id = ? ORDER BY updated_at DESC LIMIT 1`,
      [project.id],
    ),
    queryRows<AccountRow[]>(
      `SELECT platform, label, account_name, external_id, status, token_expires_at, last_synced_at
       FROM connected_accounts WHERE project_id = ?`,
      [project.id],
    ),
    queryRows<ScheduleRow[]>(
      "SELECT config_json FROM schedule_configs WHERE project_id = ? LIMIT 1",
      [project.id],
    ),
  ]);

  const ctx = contexts[0];
  const posts = parseJson<PostSequenceItem[]>(ctx?.posts_json, defaults.context.posts);
  const scheduleConfig = parseJson<{
    schedule?: ProjectScopedData["schedule"];
    automation?: Record<string, boolean>;
  }>(schedules[0]?.config_json, {});

  const scheduleFromDb = scheduleConfig.schedule;
  const hasScheduleRow = Boolean(schedules[0]?.config_json);
  const schedule: ProjectScopedData["schedule"] = scheduleFromDb
    ? {
        postsPerDay: scheduleFromDb.postsPerDay === 1 ? 1 : 2,
        // A schedule_configs row means Save Schedule / project sync already persisted it.
        saved: scheduleFromDb.saved !== false || hasScheduleRow,
        scheduleSeed: scheduleFromDb.scheduleSeed,
      }
    : hasScheduleRow
      ? {
          postsPerDay: (ctx?.posts_per_day === 1 ? 1 : 2) as 1 | 2,
          saved: true,
        }
      : {
          postsPerDay: (ctx?.posts_per_day === 1 ? 1 : 2) as 1 | 2,
          saved: false,
        };

  const postsHaveContent =
    Array.isArray(posts) && posts.some((p) => Boolean(p.content?.trim()));
  const contextSaved = Boolean(ctx?.saved) || (Boolean(ctx) && postsHaveContent);

  void organizationId;

  return {
    brand: mapBrand(brands[0], project),
    context: {
      name: ctx?.name || defaults.context.name,
      posts: Array.isArray(posts) && posts.length ? posts : defaults.context.posts,
      saved: contextSaved,
    },
    selectedPostId: ctx?.selected_post_id ?? defaults.selectedPostId,
    accounts: accounts.length ? mapAccounts(accounts) : defaults.accounts,
    schedule,
    automation: scheduleConfig.automation ?? defaults.automation,
  };
}

async function upsertProjectGraph(
  conn: PoolConnection,
  userId: string,
  organizationId: string,
  project: Project,
  scoped: ProjectScopedData,
): Promise<void> {
  await conn.execute(
    `INSERT INTO projects (
       id, organization_id, owner_user_id, name, website_url, industry, status, countries_json, timezone, created_at, updated_at
     ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
     ON DUPLICATE KEY UPDATE
       organization_id = VALUES(organization_id),
       owner_user_id = VALUES(owner_user_id),
       name = VALUES(name),
       website_url = VALUES(website_url),
       industry = VALUES(industry),
       status = VALUES(status),
       countries_json = VALUES(countries_json),
       timezone = VALUES(timezone),
       updated_at = VALUES(updated_at),
       deleted_at = NULL`,
    [
      project.id,
      organizationId,
      userId,
      project.name,
      project.website || null,
      project.industry || null,
      project.status === "paused" ? "paused" : "active",
      jsonString(project.countries ?? []),
      project.timezone || "UTC",
      toMysqlDateTime(project.createdAt) ?? toMysqlDateTime(new Date()),
      toMysqlDateTime(project.updatedAt) ?? toMysqlDateTime(new Date()),
    ],
  );

  const brand = scoped.brand;
  const brandId = randomUUID();
  await conn.execute(
    `INSERT INTO brand_profiles (
       id, project_id, company_name, tagline, industry, brand_voice,
       logo_data_url, logo_file_name, contact_phone, contact_email
     ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
     ON DUPLICATE KEY UPDATE
       company_name = VALUES(company_name),
       tagline = VALUES(tagline),
       industry = VALUES(industry),
       brand_voice = VALUES(brand_voice),
       logo_data_url = VALUES(logo_data_url),
       logo_file_name = VALUES(logo_file_name),
       contact_phone = VALUES(contact_phone),
       contact_email = VALUES(contact_email)`,
    [
      brandId,
      project.id,
      brand.companyName || project.name,
      brand.tagline || null,
      brand.industry || null,
      brand.brandVoice || null,
      brand.logoDataUrl || null,
      brand.logoFileName || null,
      brand.contactPhone || null,
      brand.contactEmail || null,
    ],
  );

  const [ctxRows] = await conn.query<RowDataPacket[]>(
    "SELECT id FROM posting_contexts WHERE project_id = ? ORDER BY updated_at DESC LIMIT 1",
    [project.id],
  );
  const contextId = (ctxRows[0] as { id?: string } | undefined)?.id ?? randomUUID();
  const postsJson = jsonString(scoped.context.posts ?? []);
  const contextSavedFlag =
    scoped.context.saved ||
    (Array.isArray(scoped.context.posts) &&
      scoped.context.posts.some((p) => Boolean(p.content?.trim())))
      ? 1
      : 0;

  await conn.execute(
    `INSERT INTO posting_contexts (
       id, project_id, name, start_date, end_date, posts_per_day, saved, selected_post_id, posts_json, is_active
     ) VALUES (?, ?, ?, NULL, NULL, ?, ?, ?, ?, 1)
     ON DUPLICATE KEY UPDATE
       name = VALUES(name),
       posts_per_day = VALUES(posts_per_day),
       saved = VALUES(saved),
       selected_post_id = VALUES(selected_post_id),
       posts_json = VALUES(posts_json),
       is_active = 1`,
    [
      contextId,
      project.id,
      scoped.context.name || "Campaign Sequence",
      scoped.schedule.postsPerDay === 1 ? 1 : 2,
      contextSavedFlag,
      scoped.selectedPostId,
      postsJson,
    ],
  );

  // Persist schedule BEFORE account rows so a datetime/account failure
  // (within a savepoint) does not wipe Smart Schedule + Posting Context.
  const scheduleToStore = {
    ...scoped.schedule,
    saved:
      Boolean(scoped.schedule.saved) || typeof scoped.schedule.scheduleSeed === "number",
  };
  const scheduleId = randomUUID();
  await conn.execute(
    `INSERT INTO schedule_configs (id, project_id, timezone, config_json)
     VALUES (?, ?, ?, ?)
     ON DUPLICATE KEY UPDATE
       timezone = VALUES(timezone),
       config_json = VALUES(config_json)`,
    [
      scheduleId,
      project.id,
      project.timezone || "UTC",
      jsonString({
        schedule: scheduleToStore,
        automation: scoped.automation,
      }),
    ],
  );

  // Client-facing account flags only. Never write client ISO datetimes here —
  // token_expires_at / last_synced_at belong to the OAuth upsert path.
  for (const account of mergeProjectAccounts(scoped.accounts)) {
    const accountId = randomUUID();
    const status =
      account.connectionStatus === "expired"
        ? "expired"
        : account.connectionStatus === "error"
          ? "error"
          : account.connected
            ? "connected"
            : "disconnected";

    await conn.execute("SAVEPOINT account_status_sync");
    try {
      await conn.execute(
        `INSERT INTO connected_accounts (
           id, project_id, platform, label, account_name, external_id, status
         ) VALUES (?, ?, ?, ?, ?, ?, ?)
         ON DUPLICATE KEY UPDATE
           label = VALUES(label),
           account_name = COALESCE(VALUES(account_name), account_name),
           external_id = COALESCE(VALUES(external_id), external_id),
           status = CASE
             WHEN token_encrypted IS NOT NULL OR publish_token_encrypted IS NOT NULL
               THEN VALUES(status)
             ELSE status
           END`,
        [
          accountId,
          project.id,
          account.platform,
          account.label,
          account.profileName,
          account.externalId ?? null,
          status,
        ],
      );
      await conn.execute("RELEASE SAVEPOINT account_status_sync");
    } catch (err) {
      console.error("[PostSync] connected_accounts status sync skipped:", err);
      await conn.execute("ROLLBACK TO SAVEPOINT account_status_sync");
    }
  }
}

export async function getUserProjectsFromDb(
  sessionUserId: string,
  sessionEmail?: string,
  sessionName?: string,
): Promise<UserProjectsSnapshot | null> {
  const { userId, organizationId } = await ensureDbUser({
    id: sessionUserId,
    email: sessionEmail || `${sessionUserId}@postsync.local`,
    name: sessionName || sessionUserId,
  });

  const projectRows = await queryRows<ProjectRow[]>(
    `SELECT * FROM projects
     WHERE deleted_at IS NULL AND (owner_user_id = ? OR organization_id = ?)
     ORDER BY updated_at DESC`,
    [userId, organizationId],
  );

  if (!projectRows.length) {
    const activeProjectId = await getActiveProjectId(userId);
    return {
      activeProjectId,
      projects: [],
      dataByProjectId: {},
      updatedAt: new Date().toISOString(),
    };
  }

  const dataByProjectId: Record<string, ProjectScopedData> = {};
  const projects: Project[] = [];

  for (const row of projectRows) {
    const project = mapProject(row);
    projects.push(project);
    dataByProjectId[project.id] = await loadScopedData(project, organizationId);
  }

  let activeProjectId = await getActiveProjectId(userId);
  if (activeProjectId && !projects.some((p) => p.id === activeProjectId)) {
    activeProjectId = projects[0]?.id ?? null;
  }
  if (!activeProjectId) activeProjectId = projects[0]?.id ?? null;

  return {
    activeProjectId,
    projects,
    dataByProjectId,
    updatedAt: new Date().toISOString(),
  };
}

export async function saveUserProjectsToDb(
  sessionUserId: string,
  snapshot: Omit<UserProjectsSnapshot, "updatedAt">,
  sessionEmail?: string,
  sessionName?: string,
): Promise<UserProjectsSnapshot> {
  const { userId, organizationId } = await ensureDbUser({
    id: sessionUserId,
    email: sessionEmail || `${sessionUserId}@postsync.local`,
    name: sessionName || sessionUserId,
  });

  await withTransaction(async (conn) => {
    const keepIds = new Set(snapshot.projects.map((p) => p.id));

    const existing = await conn.query<RowDataPacket[]>(
      `SELECT id FROM projects WHERE deleted_at IS NULL AND (owner_user_id = ? OR organization_id = ?)`,
      [userId, organizationId],
    );
    const existingIds = (existing[0] as RowDataPacket[]).map((r) => String(r.id));

    for (const id of existingIds) {
      if (!keepIds.has(id)) {
        await conn.execute("UPDATE projects SET deleted_at = CURRENT_TIMESTAMP WHERE id = ?", [id]);
      }
    }

    for (const project of snapshot.projects) {
      const scoped = snapshot.dataByProjectId[project.id] ?? createDefaultScopedData();
      await upsertProjectGraph(conn, userId, organizationId, project, scoped);
    }

    await setActiveProjectId(userId, snapshot.activeProjectId, conn);
  });

  return {
    ...snapshot,
    updatedAt: new Date().toISOString(),
  };
}