import { supabase } from "@/lib/supabase";

export type TableroCdcUserAccess = {
  email: string;
  fullName: string | null;
  role: string;
  isActive: boolean;
  canDeleteProjects: boolean;
  canManageInternalPricing: boolean;
  canControlPricingSummary: boolean;
  canManageTariffCatalog: boolean;
  canViewTeamHours: boolean;
  canCreateProjects: boolean;
};

type AccessRow = {
  email?: string | null;
  full_name?: string | null;
  role?: string | null;
  is_active?: boolean | null;
  can_delete_projects?: boolean | null;
  can_manage_internal_pricing?: boolean | null;
  can_control_pricing_summary?: boolean | null;
  can_manage_tariff_catalog?: boolean | null;
  can_view_team_hours?: boolean | null;
  can_create_projects?: boolean | null;
};

function normalizeEmail(email: string | null | undefined): string {
  return String(email ?? "")
    .trim()
    .toLowerCase();
}

function normalizeAccessRow(row: AccessRow | null | undefined): TableroCdcUserAccess | null {
  if (!row?.email || row.is_active !== true) return null;

  return {
    email: normalizeEmail(row.email),
    fullName: row.full_name ?? null,
    role: String(row.role || "user").trim() || "user",
    isActive: true,
    canDeleteProjects: row.can_delete_projects === true,
    canManageInternalPricing: row.can_manage_internal_pricing === true,
    canControlPricingSummary: row.can_control_pricing_summary === true,
    canManageTariffCatalog: row.can_manage_tariff_catalog === true,
    canViewTeamHours: row.can_view_team_hours === true,
    canCreateProjects: row.can_create_projects === true,
  };
}

export async function getActiveTableroCdcAccess(
  email: string | null | undefined,
): Promise<TableroCdcUserAccess | null> {
  const normalizedEmail = normalizeEmail(email);

  if (!normalizedEmail) return null;

  const queryWithCreatePermission = await supabase
    .from("tablero_cdc_allowed_users")
    .select(
      "email, full_name, role, is_active, can_delete_projects, can_manage_internal_pricing, can_control_pricing_summary, can_manage_tariff_catalog, can_view_team_hours, can_create_projects",
    )
    .eq("email", normalizedEmail)
    .eq("is_active", true)
    .maybeSingle();

  if (!queryWithCreatePermission.error) {
    return normalizeAccessRow(queryWithCreatePermission.data as AccessRow | null);
  }

  const createPermissionMessage = queryWithCreatePermission.error.message || "";
  const isMissingCreatePermission =
    createPermissionMessage.includes("can_create_projects") ||
    createPermissionMessage.includes("column") ||
    createPermissionMessage.includes("schema cache");

  if (!isMissingCreatePermission) {
    throw queryWithCreatePermission.error;
  }

  // Compatibilidad durante el despliegue: si la columna nueva aún no existe,
  // el resto de la app continúa funcionando, pero crear proyectos permanece bloqueado.
  const queryWithTeamHoursPermission = await supabase
    .from("tablero_cdc_allowed_users")
    .select(
      "email, full_name, role, is_active, can_delete_projects, can_manage_internal_pricing, can_control_pricing_summary, can_manage_tariff_catalog, can_view_team_hours",
    )
    .eq("email", normalizedEmail)
    .eq("is_active", true)
    .maybeSingle();

  if (!queryWithTeamHoursPermission.error) {
    return normalizeAccessRow(queryWithTeamHoursPermission.data as AccessRow | null);
  }

  const teamHoursMessage = queryWithTeamHoursPermission.error.message || "";
  const isMissingTeamHoursPermission =
    teamHoursMessage.includes("can_view_team_hours") ||
    teamHoursMessage.includes("column") ||
    teamHoursMessage.includes("schema cache");

  if (!isMissingTeamHoursPermission) {
    throw queryWithTeamHoursPermission.error;
  }

  // Compatibilidad durante el despliegue: si el permiso de Horas del equipo todavía
  // no existe, el resto del Tablero sigue funcionando y ese acceso permanece oculto.
  const queryWithCatalogPermission = await supabase
    .from("tablero_cdc_allowed_users")
    .select(
      "email, full_name, role, is_active, can_delete_projects, can_manage_internal_pricing, can_control_pricing_summary, can_manage_tariff_catalog",
    )
    .eq("email", normalizedEmail)
    .eq("is_active", true)
    .maybeSingle();

  if (!queryWithCatalogPermission.error) {
    return normalizeAccessRow(queryWithCatalogPermission.data as AccessRow | null);
  }

  const catalogMessage = queryWithCatalogPermission.error.message || "";
  const isMissingCatalogPermission =
    catalogMessage.includes("can_manage_tariff_catalog") ||
    catalogMessage.includes("column") ||
    catalogMessage.includes("schema cache");

  if (!isMissingCatalogPermission) {
    throw queryWithCatalogPermission.error;
  }

  const fallbackQuery = await supabase
    .from("tablero_cdc_allowed_users")
    .select(
      "email, full_name, role, is_active, can_delete_projects, can_manage_internal_pricing, can_control_pricing_summary",
    )
    .eq("email", normalizedEmail)
    .eq("is_active", true)
    .maybeSingle();

  if (fallbackQuery.error) throw fallbackQuery.error;

  return normalizeAccessRow(fallbackQuery.data as AccessRow | null);
}

export async function getCurrentTableroCdcAccess(): Promise<TableroCdcUserAccess> {
  const { data, error } = await supabase.auth.getUser();

  if (error || !data.user) {
    throw new Error("No hay una sesión activa de Supabase.");
  }

  const access = await getActiveTableroCdcAccess(data.user.email);

  if (!access) {
    throw new Error("Tu correo no está autorizado para acceder al Tablero CDC.");
  }

  return access;
}
