import { createClient as createSupabaseClient, type SupabaseClient } from "@supabase/supabase-js";
import { createAdminClient } from "@/lib/supabase-server";

type NullableString = string | null;

export type BankAccountSnapshot = {
  id: string;
  company_id: string;
  name: NullableString;
  bank_name: NullableString;
  bank_code: NullableString;
  agency_number: NullableString;
  agency_digit: NullableString;
  account_number: NullableString;
  account_digit: NullableString;
  pix_key: NullableString;
  logo_key: NullableString;
  type: NullableString;
  initial_balance: number;
  active: boolean;
  flow_balance: number;
  current_balance: number;
};

export type BankAccountDependencySummary = {
  cash_flow: number;
  payments: number;
  accounts_payable: number;
  accounts_receivable: number;
  receivable_receipts: number;
  pdv_cash_sessions: number;
  open_pdv_cash_sessions: number;
  payment_integrations: number;
  total: number;
  blockers: string[];
};

export type BankAccountAuditEventType =
  | "ACCOUNT_CREATED"
  | "ACCOUNT_UPDATED"
  | "ACCOUNT_DEACTIVATED"
  | "ACCOUNT_REACTIVATED"
  | "BALANCE_ADJUSTMENT"
  | "ACCOUNT_DELETED";

type IntegrationRow = {
  id?: string | null;
  settings?: Record<string, unknown> | null;
  active?: boolean | null;
};

function normalizeText(value: unknown) {
  return String(value || "").trim();
}

function toNumber(value: unknown) {
  const parsed = Number(value);
  return Number.isFinite(parsed) ? parsed : 0;
}

async function countRows(
  query:
    | PromiseLike<{
        count: number | null;
        error: { message?: string | null } | null;
      }>
    | {
        count: number | null;
        error: { message?: string | null } | null;
      }
) {
  const result = await query;
  if (result.error) {
    throw new Error(result.error.message || "Falha ao contar dependências da conta bancária.");
  }
  return result.count ?? 0;
}

export async function getBankAccountSnapshot(
  supabase: SupabaseClient,
  companyId: string,
  bankAccountId: string
): Promise<BankAccountSnapshot | null> {
  const { data: baseRow, error: baseError } = await supabase
    .from("bank_accounts")
    .select(
      "id,company_id,name,bank_name,bank_code,agency_number,agency_digit,account_number,account_digit,pix_key,logo_key,type,initial_balance,active"
    )
    .eq("company_id", companyId)
    .eq("id", bankAccountId)
    .maybeSingle();

  if (baseError) {
    throw new Error(baseError.message || "Falha ao carregar a conta bancária.");
  }
  if (!baseRow?.id) return null;

  let flowBalance = 0;
  let currentBalance = toNumber(baseRow.initial_balance);

  const withCompany = await supabase
    .from("v_bank_account_balance")
    .select("bank_account_id,flow_balance,current_balance")
    .eq("company_id", companyId)
    .eq("bank_account_id", bankAccountId)
    .maybeSingle();

  if (!withCompany.error && withCompany.data?.bank_account_id) {
    flowBalance = toNumber(withCompany.data.flow_balance);
    currentBalance = toNumber(withCompany.data.current_balance);
  } else {
    const flowResult = await supabase
      .from("cash_flow")
      .select("type,amount")
      .eq("company_id", companyId)
      .eq("bank_account_id", bankAccountId);

    if (flowResult.error) {
      throw new Error(flowResult.error.message || "Falha ao calcular o saldo da conta bancária.");
    }

    flowBalance = (flowResult.data ?? []).reduce((sum, row) => {
      const amount = Math.abs(toNumber((row as { amount?: unknown }).amount));
      const type = (row as { type?: unknown }).type;
      return sum + (type === "OUT" ? -amount : amount);
    }, 0);
    currentBalance = toNumber(baseRow.initial_balance) + flowBalance;
  }

  return {
    id: String(baseRow.id),
    company_id: String(baseRow.company_id),
    name: baseRow.name ?? null,
    bank_name: baseRow.bank_name ?? null,
    bank_code: baseRow.bank_code ?? null,
    agency_number: baseRow.agency_number ?? null,
    agency_digit: baseRow.agency_digit ?? null,
    account_number: baseRow.account_number ?? null,
    account_digit: baseRow.account_digit ?? null,
    pix_key: baseRow.pix_key ?? null,
    logo_key: baseRow.logo_key ?? null,
    type: baseRow.type ?? null,
    initial_balance: toNumber(baseRow.initial_balance),
    active: baseRow.active !== false,
    flow_balance: flowBalance,
    current_balance: currentBalance,
  };
}

export async function getBankAccountDependencies(
  companyId: string,
  bankAccountId: string,
  relatedUserIds: string[]
): Promise<BankAccountDependencySummary> {
  const admin = createAdminClient();

  const [cashFlow, payments, payables, receivables, receipts, pdvSessions, openPdvSessions, integrations] =
    await Promise.all([
      countRows(
        admin
          .from("cash_flow")
          .select("id", { count: "exact", head: true })
          .eq("company_id", companyId)
          .eq("bank_account_id", bankAccountId)
      ),
      countRows(
        admin
          .from("payments")
          .select("id", { count: "exact", head: true })
          .in("user_id", relatedUserIds)
          .eq("bank_account_id", bankAccountId)
      ),
      countRows(
        admin
          .from("accounts_payable")
          .select("id", { count: "exact", head: true })
          .eq("company_id", companyId)
          .eq("bank_account_id", bankAccountId)
      ),
      countRows(
        admin
          .from("accounts_receivable")
          .select("id", { count: "exact", head: true })
          .eq("company_id", companyId)
          .eq("bank_account_id", bankAccountId)
      ),
      countRows(
        admin
          .from("receivable_receipts")
          .select("id", { count: "exact", head: true })
          .eq("company_id", companyId)
          .eq("bank_account_id", bankAccountId)
      ),
      countRows(
        admin
          .from("pdv_cash_sessions")
          .select("id", { count: "exact", head: true })
          .eq("company_id", companyId)
          .eq("bank_account_id", bankAccountId)
      ),
      countRows(
        admin
          .from("pdv_cash_sessions")
          .select("id", { count: "exact", head: true })
          .eq("company_id", companyId)
          .eq("bank_account_id", bankAccountId)
          .is("closed_at", null)
      ),
      admin
        .from("integrations")
        .select("id,settings,active")
        /* Os dois provedores guardam a conta de destino no mesmo campo.
           Olhar so um deixaria a conta de uma loja do Asaas ser apagada com o
           vinculo vivo, e o dinheiro sem lugar para cair. */
        .in("provider", ["mercado_pago", "asaas"])
        .in("user_id", relatedUserIds),
    ]);

  if (integrations.error) {
    throw new Error(integrations.error.message || "Falha ao validar integrações vinculadas.");
  }

  const integracoesDePagamento = (integrations.data as IntegrationRow[] | null | undefined)?.filter((row) => {
    if (row.active === false) return false;
    return normalizeText(row.settings?.bank_account_id) === bankAccountId;
  }).length ?? 0;

  const blockers: string[] = [];
  if (cashFlow > 0) blockers.push(`Fluxo de caixa (${cashFlow})`);
  if (payments > 0) blockers.push(`Pagamentos (${payments})`);
  if (payables > 0) blockers.push(`Contas a pagar (${payables})`);
  if (receivables > 0) blockers.push(`Contas a receber (${receivables})`);
  if (receipts > 0) blockers.push(`Recebimentos (${receipts})`);
  if (pdvSessions > 0) blockers.push(`Sessões de PDV (${pdvSessions})`);
  if (integracoesDePagamento > 0) blockers.push(`Integração de pagamento (${integracoesDePagamento})`);

  return {
    cash_flow: cashFlow,
    payments,
    accounts_payable: payables,
    accounts_receivable: receivables,
    receivable_receipts: receipts,
    pdv_cash_sessions: pdvSessions,
    open_pdv_cash_sessions: openPdvSessions,
    payment_integrations: integracoesDePagamento,
    total: cashFlow + payments + payables + receivables + receipts + pdvSessions + integracoesDePagamento,
    blockers,
  };
}

export async function executeHardDeleteOperations(
  companyId: string,
  bankAccountId: string,
  relatedUserIds: string[],
  mode: "migrate" | "exterminate",
  targetBankAccountId?: string,
  accessToken?: string
) {
  const admin = createAdminClient();
  const url = process.env.NEXT_PUBLIC_SUPABASE_URL;
  const anonKey = process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY;

  if (!url || !anonKey) {
    throw new Error("Supabase env vars ausentes para RPC autenticada.");
  }
  if (!accessToken?.trim()) {
    throw new Error("Sessão autenticada inválida para executar a ação administrativa.");
  }

  const actor = createSupabaseClient(url, anonKey.trim(), {
    auth: { autoRefreshToken: false, persistSession: false, detectSessionInUrl: false },
    global: {
      headers: {
        Authorization: `Bearer ${accessToken.trim()}`,
      },
    },
  });

  if (mode === "migrate") {
    if (!targetBankAccountId) throw new Error("Conta de destino não informada para migração.");
    const { error } = await actor.rpc("admin_migrate_bank_account_links", {
      p_company_id: companyId,
      p_old_bank_account_id: bankAccountId,
      p_new_bank_account_id: targetBankAccountId,
      p_related_user_ids: relatedUserIds,
    });
    if (error) {
      if (error.code === "PGRST202") {
        throw new Error("A RPC administrativa de migração não foi encontrada no banco. Verifique a migration 107.");
      }
      throw new Error(`Falha na migração de vínculos: ${error.message}`);
    }

    // Update integrations if any
    const { data: integrations, error: integrationsError } = await admin.from("integrations").select("id,settings").in("provider", ["mercado_pago", "asaas"]).in("user_id", relatedUserIds);
    if (integrationsError) throw new Error(`Integrations error: ${integrationsError.message}`);
    
    if (integrations) {
      for (const row of integrations) {
        if (normalizeText((row.settings as Record<string, unknown>)?.bank_account_id) === bankAccountId) {
          const newSettings = { ...(row.settings as Record<string, unknown>), bank_account_id: targetBankAccountId };
          const { error: updateError } = await admin.from("integrations").update({ settings: newSettings }).eq("id", row.id);
          if (updateError) throw new Error(`Integrations update error: ${updateError.message}`);
        }
      }
    }
  } else if (mode === "exterminate") {
    const { error } = await actor.rpc("admin_hard_delete_bank_account", {
      p_company_id: companyId,
      p_bank_account_id: bankAccountId,
      p_related_user_ids: relatedUserIds,
    });
    if (error) {
      if (error.code === "PGRST202") {
        throw new Error("A RPC administrativa de exclusão não foi encontrada no banco. Verifique a migration 107.");
      }
      throw new Error(`Falha na exclusão física: ${error.message}`);
    }

    const { data: integrations, error: integrationsError } = await admin.from("integrations").select("id,settings").in("provider", ["mercado_pago", "asaas"]).in("user_id", relatedUserIds);
    if (integrationsError) throw new Error(`Integrations error: ${integrationsError.message}`);

    if (integrations) {
      for (const row of integrations) {
        if (normalizeText((row.settings as Record<string, unknown>)?.bank_account_id) === bankAccountId) {
          const newSettings = { ...(row.settings as Record<string, unknown>) };
          delete newSettings.bank_account_id;
          const { error: updateError } = await admin.from("integrations").update({ settings: newSettings }).eq("id", row.id);
          if (updateError) throw new Error(`Integrations update error: ${updateError.message}`);
        }
      }
    }
  }
}

export async function logBankAccountAuditEvent(params: {
  companyId: string;
  bankAccountId?: string | null;
  userId: string;
  eventType: BankAccountAuditEventType;
  reason?: string | null;
  beforeState?: unknown;
  afterState?: unknown;
  metadata?: unknown;
}) {
  const admin = createAdminClient();
  const { error } = await admin.from("bank_account_admin_events").insert({
    company_id: params.companyId,
    bank_account_id: params.bankAccountId || null,
    created_by: params.userId,
    event_type: params.eventType,
    reason: params.reason || null,
    before_state: params.beforeState ?? null,
    after_state: params.afterState ?? null,
    metadata: params.metadata ?? null,
  });

  if (error) {
    const message = error.message || "Falha ao registrar auditoria da conta bancária.";
    const lower = message.toLowerCase();
    const isMissingStructure =
      lower.includes("does not exist") ||
      lower.includes("could not find the table") ||
      lower.includes("schema cache");
    if (isMissingStructure) {
      console.warn("bank_account_admin_events indisponível; auditoria crítica pendente de migration:", message);
      return;
    }
    throw new Error(message);
  }
}