import { useCallback, useEffect, useMemo, useRef, useState } from "react";
import { supabase } from "@/lib/supabase";
import type { SheetSyncStatus } from "@/lib/store";

export type BriefReviewStatus = "new" | "rejected";

export interface BriefInboxItem {
  id: string;
  briefId: string;
  sourceCreatedAt: number;
  title: string;
  client: string;
  brand: string;
  country: string;
  requestedBy: string;
  requestedByEmail: string;
  deliveryDate: string;
  briefLink: string;
  documentLink: string;
  summary: string;
  requestType: string;
  deliverableType: string;
  reviewStatus: "new" | "approved" | "rejected";
  rejectionReason: string;
  approvedProjectId: string;
  reviewedByName: string;
  reviewedByEmail: string;
  reviewedAt: number | null;
  createdAt: number;
}

type DbBriefInboxItem = {
  id: string;
  brief_id: string | null;
  source_created_at: string | null;
  title: string | null;
  client: string | null;
  brand: string | null;
  country: string | null;
  requested_by: string | null;
  requested_by_email: string | null;
  delivery_date: string | null;
  brief_link: string | null;
  document_link: string | null;
  summary: string | null;
  request_type: string | null;
  deliverable_type: string | null;
  review_status: string | null;
  rejection_reason: string | null;
  approved_project_id: string | null;
  reviewed_by_name: string | null;
  reviewed_by_email: string | null;
  reviewed_at: string | null;
  created_at: string | null;
};

type RpcApprovalResponse = {
  ok?: boolean;
  already_approved?: boolean;
  project_id?: string | null;
  brief_id?: string | null;
};

type RpcRejectResponse = {
  ok?: boolean;
  already_rejected?: boolean;
  brief_id?: string | null;
};

export type BriefApprovalResult = {
  projectId: string;
  sheetSyncStatus: SheetSyncStatus;
  sheetSyncMessage: string;
};

export type BriefInboxQuery = {
  status: BriefReviewStatus;
  search: string;
  page: number;
  pageSize: number;
  enabled?: boolean;
};

function asTimestamp(value: string | null | undefined, fallback = Date.now()) {
  if (!value) return fallback;
  const parsed = new Date(value).getTime();
  return Number.isFinite(parsed) ? parsed : fallback;
}

function mapDbBrief(row: DbBriefInboxItem): BriefInboxItem {
  const createdAt = asTimestamp(row.created_at);

  return {
    id: row.id,
    briefId: row.brief_id || "",
    sourceCreatedAt: asTimestamp(row.source_created_at, createdAt),
    title: row.title || "",
    client: row.client || "",
    brand: row.brand || "",
    country: row.country || "",
    requestedBy: row.requested_by || "",
    requestedByEmail: row.requested_by_email || "",
    deliveryDate: row.delivery_date || "",
    briefLink: row.brief_link || "",
    documentLink: row.document_link || "",
    summary: row.summary || "",
    requestType: row.request_type || "",
    deliverableType: row.deliverable_type || "",
    reviewStatus:
      row.review_status === "approved"
        ? "approved"
        : row.review_status === "rejected"
          ? "rejected"
          : "new",
    rejectionReason: row.rejection_reason || "",
    approvedProjectId: row.approved_project_id || "",
    reviewedByName: row.reviewed_by_name || "",
    reviewedByEmail: row.reviewed_by_email || "",
    reviewedAt: row.reviewed_at ? asTimestamp(row.reviewed_at) : null,
    createdAt,
  };
}

function cleanSearch(value: string) {
  return value.trim().replace(/[%_,]/g, " ").replace(/\s+/g, " ");
}

function getSyncWebhookUrl() {
  return import.meta.env.VITE_WEBHOOK_URL?.trim() || "";
}

async function syncApprovedProject(projectId: string): Promise<{
  sheetSyncStatus: SheetSyncStatus;
  sheetSyncMessage: string;
}> {
  const webhookUrl = getSyncWebhookUrl();

  if (!webhookUrl) {
    return {
      sheetSyncStatus: "skipped",
      sheetSyncMessage:
        "Proyecto aprobado y creado en Supabase. No hay webhook configurado para sincronizar Google Sheet.",
    };
  }

  const controller = new AbortController();
  const timeout = window.setTimeout(() => controller.abort(), 15000);

  try {
    const response = await fetch(webhookUrl, {
      method: "POST",
      headers: { "Content-Type": "application/json" },
      body: JSON.stringify({
        project_id: projectId,
        action: "create",
        source: "tablero-cdc-brief-review",
        sent_at: new Date().toISOString(),
      }),
      signal: controller.signal,
    });

    if (!response.ok) {
      const errorText = await response.text().catch(() => "");
      const message = errorText || `${response.status} ${response.statusText}`.trim();

      return {
        sheetSyncStatus: "failed",
        sheetSyncMessage: `El proyecto quedó Activo en el Tablero, pero el Sheet no se pudo sincronizar: ${message}`,
      };
    }

    return {
      sheetSyncStatus: "synced",
      sheetSyncMessage: "Brief aprobado, proyecto Activo creado y Google Sheet sincronizado.",
    };
  } catch (error) {
    const isTimeout = error instanceof DOMException && error.name === "AbortError";

    return {
      sheetSyncStatus: isTimeout ? "timeout" : "failed",
      sheetSyncMessage: isTimeout
        ? "El proyecto quedó Activo en el Tablero, pero n8n tardó demasiado en responder. Revisa el Sheet si fuera necesario."
        : "El proyecto quedó Activo en el Tablero, pero no se pudo sincronizar con Google Sheet.",
    };
  } finally {
    window.clearTimeout(timeout);
  }
}

export function useBriefInbox({ status, search, page, pageSize, enabled = true }: BriefInboxQuery) {
  const [items, setItems] = useState<BriefInboxItem[]>([]);
  const [totalItems, setTotalItems] = useState(0);
  const [loading, setLoading] = useState(false);
  const [error, setError] = useState<string | null>(null);
  const requestSeq = useRef(0);

  const normalizedSearch = useMemo(() => cleanSearch(search), [search]);

  const load = useCallback(async () => {
    if (!enabled) return;

    const requestId = (requestSeq.current += 1);
    setLoading(true);
    setError(null);

    try {
      const from = Math.max(0, (page - 1) * pageSize);
      const to = from + pageSize - 1;

      let query = supabase
        .from("tablero_cdc_brief_inbox")
        .select(
          "id, brief_id, source_created_at, title, client, brand, country, requested_by, requested_by_email, delivery_date, brief_link, document_link, summary, request_type, deliverable_type, review_status, rejection_reason, approved_project_id, reviewed_by_name, reviewed_by_email, reviewed_at, created_at",
          { count: "exact" },
        )
        .eq("review_status", status)
        .order("created_at", { ascending: false });

      if (normalizedSearch) {
        const pattern = `%${normalizedSearch}%`;
        query = query.or(
          `title.ilike.${pattern},client.ilike.${pattern},brand.ilike.${pattern},country.ilike.${pattern},requested_by.ilike.${pattern},requested_by_email.ilike.${pattern},request_type.ilike.${pattern},deliverable_type.ilike.${pattern},summary.ilike.${pattern}`,
        );
      }

      const { data, error: queryError, count } = await query.range(from, to);

      if (queryError) throw queryError;
      if (requestId !== requestSeq.current) return;

      setItems(((data || []) as DbBriefInboxItem[]).map(mapDbBrief));
      setTotalItems(count ?? 0);
    } catch (loadError) {
      if (requestId !== requestSeq.current) return;

      console.error("Error cargando bandeja de CDC Brief:", loadError);
      setError(
        loadError instanceof Error
          ? loadError.message
          : "No se pudieron cargar los briefs pendientes de revisión.",
      );
      setItems([]);
      setTotalItems(0);
    } finally {
      if (requestId === requestSeq.current) setLoading(false);
    }
  }, [enabled, normalizedSearch, page, pageSize, status]);

  useEffect(() => {
    if (!enabled) return;
    void load();
  }, [enabled, load]);

  useEffect(() => {
    if (!enabled) return;

    let refreshTimeout: ReturnType<typeof setTimeout> | null = null;
    const channel = supabase
      .channel(`tablero-cdc-brief-inbox-${status}`)
      .on(
        "postgres_changes",
        { event: "*", schema: "public", table: "tablero_cdc_brief_inbox" },
        () => {
          if (refreshTimeout) clearTimeout(refreshTimeout);
          refreshTimeout = setTimeout(() => void load(), 300);
        },
      )
      .subscribe((subscriptionStatus) => {
        if (subscriptionStatus === "CHANNEL_ERROR" || subscriptionStatus === "TIMED_OUT") {
          console.warn(
            "Supabase Realtime no pudo suscribirse a tablero_cdc_brief_inbox:",
            subscriptionStatus,
          );
        }
      });

    return () => {
      if (refreshTimeout) clearTimeout(refreshTimeout);
      void supabase.removeChannel(channel);
    };
  }, [enabled, load, status]);

  const approve = useCallback(
    async (id: string): Promise<BriefApprovalResult> => {
      const { data, error: rpcError } = await supabase.rpc("tablero_cdc_approve_brief", {
        p_inbox_id: id,
      });

      if (rpcError) throw rpcError;

      const result = (data || {}) as RpcApprovalResponse;
      const projectId = String(result.project_id || "").trim();

      if (!projectId) {
        throw new Error("Supabase aprobó el brief, pero no devolvió el Project ID.");
      }

      const syncResult = await syncApprovedProject(projectId);
      await load();

      return {
        projectId,
        ...syncResult,
      };
    },
    [load],
  );

  const reject = useCallback(
    async (id: string, reason = ""): Promise<void> => {
      const { data, error: rpcError } = await supabase.rpc("tablero_cdc_reject_brief", {
        p_inbox_id: id,
        p_reason: reason.trim() || null,
      });

      if (rpcError) throw rpcError;

      const result = (data || {}) as RpcRejectResponse;
      if (result.ok === false) {
        throw new Error("Supabase no pudo mover el brief a Rechazados.");
      }

      await load();
    },
    [load],
  );

  return {
    items,
    totalItems,
    loading,
    error,
    refresh: load,
    approve,
    reject,
  };
}

export type BriefUnreadState = {
  unreadCount: number;
  loading: boolean;
  error: string | null;
  refresh: () => Promise<void>;
  markAllSeen: () => Promise<number>;
};

type RpcMarkSeenResponse = {
  ok?: boolean;
  marked_count?: number;
  unseen_count?: number;
};

/**
 * Per-user notification counter for the "Nuevos" brief inbox.
 *
 * A brief counts as unread only while it is still review_status='new' and the
 * authenticated user has not marked it as seen. The seen state lives in
 * Supabase, so every teammate has an independent counter. Changes made by
 * another teammate (approve/reject/new brief) refresh this counter through
 * Realtime, with focus/interval refreshes as a safe fallback.
 */
export function useBriefUnreadCount(enabled = true): BriefUnreadState {
  const [unreadCount, setUnreadCount] = useState(0);
  const [loading, setLoading] = useState(false);
  const [error, setError] = useState<string | null>(null);
  const requestSeq = useRef(0);

  const refresh = useCallback(async () => {
    if (!enabled) {
      setUnreadCount(0);
      return;
    }

    const requestId = (requestSeq.current += 1);
    setLoading(true);

    try {
      const { data, error: rpcError } = await supabase.rpc(
        "tablero_cdc_get_unseen_brief_count",
      );

      if (rpcError) throw rpcError;
      if (requestId !== requestSeq.current) return;

      const nextCount = Number(data ?? 0);
      setUnreadCount(Number.isFinite(nextCount) && nextCount > 0 ? nextCount : 0);
      setError(null);
    } catch (refreshError) {
      if (requestId !== requestSeq.current) return;

      console.error("Error cargando notificaciones de briefs nuevos:", refreshError);
      setError(
        refreshError instanceof Error
          ? refreshError.message
          : "No se pudo actualizar el contador de briefs nuevos.",
      );
    } finally {
      if (requestId === requestSeq.current) setLoading(false);
    }
  }, [enabled]);

  const markAllSeen = useCallback(async (): Promise<number> => {
    if (!enabled) return 0;

    // Clear immediately in the UI; the RPC persists this only for the current
    // authenticated user. If it fails, refresh restores the server truth.
    setUnreadCount(0);

    try {
      const { data, error: rpcError } = await supabase.rpc("tablero_cdc_mark_briefs_seen");
      if (rpcError) throw rpcError;

      const result = (data || {}) as RpcMarkSeenResponse;
      const remaining = Number(result.unseen_count ?? 0);
      const normalizedRemaining =
        Number.isFinite(remaining) && remaining > 0 ? remaining : 0;

      setUnreadCount(normalizedRemaining);
      setError(null);
      return normalizedRemaining;
    } catch (markError) {
      await refresh();
      throw markError;
    }
  }, [enabled, refresh]);

  useEffect(() => {
    if (!enabled) {
      setUnreadCount(0);
      return;
    }

    void refresh();
  }, [enabled, refresh]);

  useEffect(() => {
    if (!enabled) return;

    let refreshTimeout: ReturnType<typeof setTimeout> | null = null;
    const scheduleRefresh = () => {
      if (refreshTimeout) clearTimeout(refreshTimeout);
      refreshTimeout = setTimeout(() => void refresh(), 250);
    };

    const channel = supabase
      .channel("tablero-cdc-brief-unread-counter")
      .on(
        "postgres_changes",
        { event: "*", schema: "public", table: "tablero_cdc_brief_inbox" },
        scheduleRefresh,
      )
      .subscribe((subscriptionStatus) => {
        if (subscriptionStatus === "CHANNEL_ERROR" || subscriptionStatus === "TIMED_OUT") {
          console.warn(
            "Supabase Realtime no pudo actualizar el contador de briefs:",
            subscriptionStatus,
          );
        }
      });

    // Fallback: mantiene el contador correcto incluso si Realtime pierde conexión.
    const interval = window.setInterval(() => {
      if (document.visibilityState === "visible") void refresh();
    }, 30000);

    const handleFocus = () => void refresh();
    const handleVisibility = () => {
      if (document.visibilityState === "visible") void refresh();
    };

    window.addEventListener("focus", handleFocus);
    document.addEventListener("visibilitychange", handleVisibility);

    return () => {
      if (refreshTimeout) clearTimeout(refreshTimeout);
      window.clearInterval(interval);
      window.removeEventListener("focus", handleFocus);
      document.removeEventListener("visibilitychange", handleVisibility);
      void supabase.removeChannel(channel);
    };
  }, [enabled, refresh]);

  return {
    unreadCount,
    loading,
    error,
    refresh,
    markAllSeen,
  };
}

