import { randomUUID } from "crypto";
import type { RowDataPacket } from "mysql2/promise";
import type { ConnectedAccount, ConnectionStatus } from "@/types/workflow";
import { fromMysqlDateTime, toMysqlDateTime } from "@/lib/db/datetime";
import { execute, queryRows } from "@/lib/db/pool";

export interface StoredSocialAccount extends ConnectedAccount {
  updatedAt: string;
  publishToken?: string | null;
}

interface AccountRow extends RowDataPacket {
  id: string;
  platform: string;
  label: string | null;
  account_name: string | null;
  external_id: string | null;
  token_encrypted: string | null;
  refresh_token_encrypted: string | null;
  publish_token_encrypted: string | null;
  token_expires_at: string | null;
  last_synced_at: string | null;
  status: string;
  updated_at: string;
}

function mapStatus(status: string, hasToken: boolean): ConnectionStatus {
  if (status === "expired") return "expired";
  if (status === "error") return "error";
  if (status === "connected" || hasToken) return "connected";
  return "disconnected";
}

function mapRow(row: AccountRow): StoredSocialAccount {
  const accessToken = row.token_encrypted;
  const connectionStatus = mapStatus(row.status, Boolean(accessToken));
  return {
    platform: row.platform,
    label: row.label ?? row.platform,
    profileName: row.account_name ?? "Not connected",
    connected: connectionStatus === "connected",
    connectionStatus,
    accessToken,
    refreshToken: row.refresh_token_encrypted,
    publishToken: row.publish_token_encrypted,
    expiresAt: fromMysqlDateTime(row.token_expires_at),
    lastSyncedAt: fromMysqlDateTime(row.last_synced_at),
    externalId: row.external_id,
    updatedAt: fromMysqlDateTime(row.updated_at) ?? new Date().toISOString(),
  };
}

export async function loadProjectSocialAccountsFromDb(
  _userId: string,
  projectId: string,
): Promise<StoredSocialAccount[]> {
  const rows = await queryRows<AccountRow[]>(
    `SELECT id, platform, label, account_name, external_id, token_encrypted,
            refresh_token_encrypted, publish_token_encrypted, token_expires_at,
            last_synced_at, status, updated_at
     FROM connected_accounts WHERE project_id = ?`,
    [projectId],
  );
  return rows.map(mapRow);
}

export async function saveProjectSocialAccountsToDb(
  _userId: string,
  projectId: string,
  accounts: StoredSocialAccount[],
): Promise<void> {
  for (const account of accounts) {
    await upsertProjectSocialAccountToDb(_userId, projectId, account);
  }

  const platforms = accounts.map((a) => a.platform);
  if (!platforms.length) return;

  // Soft-disconnect platforms removed from the list
  const placeholders = platforms.map(() => "?").join(",");
  await execute(
    `UPDATE connected_accounts
     SET status = 'disconnected', token_encrypted = NULL, refresh_token_encrypted = NULL,
         publish_token_encrypted = NULL
     WHERE project_id = ? AND platform NOT IN (${placeholders})`,
    [projectId, ...platforms],
  );
}

export async function upsertProjectSocialAccountToDb(
  _userId: string,
  projectId: string,
  account: Omit<StoredSocialAccount, "updatedAt"> & { updatedAt?: string },
): Promise<StoredSocialAccount> {
  const id = randomUUID();
  const status =
    account.connectionStatus === "expired"
      ? "expired"
      : account.connectionStatus === "error"
        ? "error"
        : account.connected
          ? "connected"
          : "disconnected";

  await execute(
    `INSERT INTO connected_accounts (
       id, project_id, platform, label, account_name, external_id,
       token_encrypted, refresh_token_encrypted, publish_token_encrypted,
       token_expires_at, last_synced_at, status
     ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, UTC_TIMESTAMP(), ?)
     ON DUPLICATE KEY UPDATE
       label = VALUES(label),
       account_name = VALUES(account_name),
       external_id = VALUES(external_id),
       token_encrypted = VALUES(token_encrypted),
       refresh_token_encrypted = VALUES(refresh_token_encrypted),
       publish_token_encrypted = VALUES(publish_token_encrypted),
       token_expires_at = VALUES(token_expires_at),
       last_synced_at = UTC_TIMESTAMP(),
       status = VALUES(status)`,
    [
      id,
      projectId,
      account.platform,
      account.label,
      account.profileName,
      account.externalId ?? null,
      account.accessToken ?? null,
      account.refreshToken ?? null,
      account.publishToken ?? null,
      toMysqlDateTime(account.expiresAt),
      status,
    ],
  );

  const rows = await loadProjectSocialAccountsFromDb(_userId, projectId);
  return (
    rows.find((a) => a.platform === account.platform) ?? {
      ...account,
      updatedAt: new Date().toISOString(),
    }
  );
}

export async function deleteProjectSocialAccountFromDb(
  _userId: string,
  projectId: string,
  platform: string,
): Promise<void> {
  await execute(
    `UPDATE connected_accounts
     SET status = 'disconnected',
         token_encrypted = NULL,
         refresh_token_encrypted = NULL,
         publish_token_encrypted = NULL,
         external_id = NULL
     WHERE project_id = ? AND platform = ?`,
    [projectId, platform],
  );
}