// db.jsx — Supabase data-layer + adapter zwischen DB-Schema und UI-Mock-Format.
// Die UI-Komponenten (inbox.jsx) erwarten chat-Objekte im Format aus data.jsx
// (id, name, handle, unread, aiEnabled, aiPaused, messages[]). Dieser Layer
// uebersetzt DB-Rows in genau dieses Format, damit die View unveraendert bleibt.
//
// Phase 2 = Read-Only: nur SELECT + Realtime-Subscribes. Keine Outbound-Send,
// keine Updates auf chats/accounts. Das kommt in Phase 3.

(function () {
  const env = window.TF_ENV;
  if (!env || !env.SUPABASE_URL || !env.SUPABASE_ANON_KEY) {
    console.error('[db] window.TF_ENV fehlt — siehe shared/env.example.js');
    return;
  }
  if (!window.supabase) {
    console.error('[db] window.supabase fehlt — Supabase JS-SDK nicht geladen');
    return;
  }

  const sb = window.supabase.createClient(env.SUPABASE_URL, env.SUPABASE_ANON_KEY, {
    auth: {
      persistSession: true,
      autoRefreshToken: true,
      detectSessionInUrl: false,
    },
    realtime: {
      params: { eventsPerSecond: 5 },
    },
  });

  // ----- Adapter: DB-Row -> UI-Format -----------------------------------------

  const hashSeed = (s) => {
    let h = 0;
    for (let i = 0; i < s.length; i++) h = ((h << 5) - h + s.charCodeAt(i)) | 0;
    return Math.abs(h);
  };

  const mapDbMessage = (row) => {
    // direction: in/out; source: customer/ai/dashboard/ig_app
    const fromUs = row.direction === 'out';
    let author;
    if (fromUs) {
      if (row.source === 'ai') author = 'ai';
      else author = 'staff'; // dashboard | ig_app
    }
    const out = {
      id: row.id,
      from: fromUs ? 'us' : 'them',
      text: row.text || '',
      t: new Date(row.created_at).getTime(),
    };
    if (author) out.author = author;
    if (row.source) out.source = row.source; // propagate raw source for UI badge + class
    if (row.kind === 'image' && row.attachment_url) {
      out.image = row.attachment_url;
      delete out.text;
    }
    if (fromUs && row.source === 'dashboard') {
      out.authorName = 'Studio';
    } else if (fromUs && row.source === 'ig_app') {
      out.authorName = 'Direkt aus Instagram';
    }
    return out;
  };

  const mapDbChat = (row, messages = []) => {
    const handle = row.ig_username || `user_${String(row.ig_user_id).slice(-6)}`;
    const name = row.ig_full_name || handle;
    return {
      id: row.id,
      name,
      handle,
      avatarSeed: hashSeed(row.id),
      online: false, // V1: nicht verfuegbar
      unread: row.unread_count || 0,
      aiEnabled: row.ai_enabled !== false,
      aiPaused: row.ai_paused === true,
      pauseReason: row.pause_reason || null,
      inMessageRequests: row.in_message_requests === true,
      pinned: !!row.pinned_at,
      pinnedAt: row.pinned_at || null,
      archived: !!row.archived_at,
      archivedAt: row.archived_at || null,
      lastMessageAt: row.last_message_at ? new Date(row.last_message_at).getTime() : 0,
      tags: Array.isArray(row.tags) ? row.tags : [],
      profilePic: row.ig_profile_pic_url || null,
      staffNote: row.staff_note || null,
      customer: {
        firstSeen: row.created_at
          ? new Date(row.created_at).toLocaleDateString('de-DE')
          : '',
        city: '',
        followers: '',
      },
      messages,
    };
  };

  // ----- Auth -----------------------------------------------------------------

  async function getSession() {
    const { data, error } = await sb.auth.getSession();
    if (error) {
      console.error('[db] getSession error:', error);
      return null;
    }
    return data.session;
  }

  async function getCurrentUser() {
    const session = await getSession();
    if (!session) return null;
    // ergaenzendes SELECT in public.users → account_id, display_name, role
    const { data, error } = await sb
      .from('users')
      .select('id, account_id, display_name, role')
      .eq('id', session.user.id)
      .maybeSingle();
    if (error) {
      console.error('[db] users select error:', error);
      return {
        id: session.user.id,
        email: session.user.email,
        name: session.user.email?.split('@')[0] || 'User',
        account_id: null,
        role: null,
        _orphan: true, // kein public.users-Eintrag → Setup unvollstaendig
      };
    }
    if (!data) {
      return {
        id: session.user.id,
        email: session.user.email,
        name: session.user.email?.split('@')[0] || 'User',
        account_id: null,
        role: null,
        _orphan: true,
      };
    }
    return {
      id: data.id,
      email: session.user.email,
      name: data.display_name,
      account_id: data.account_id,
      role: data.role,
      _orphan: false,
    };
  }

  async function signIn(email, password) {
    const { data, error } = await sb.auth.signInWithPassword({ email, password });
    if (error) throw error;
    return data;
  }

  async function signOut() {
    const { error } = await sb.auth.signOut();
    if (error) console.error('[db] signOut error:', error);
  }

  // ----- Account / Chats / Messages -------------------------------------------

  async function loadAccount(accountId) {
    const { data, error } = await sb
      .from('accounts')
      .select('id, slug, display_name, ki_global_on, timezone')
      .eq('id', accountId)
      .maybeSingle();
    if (error) {
      console.error('[db] loadAccount error:', error);
      return null;
    }
    return data;
  }

  // Strategie: erst alle chats laden, dann pro chat die letzten N messages.
  // RLS sorgt dafuer, dass nur eigene Account-Chats sichtbar sind.
  // Defensive: archived_at-Filter wird beim ersten Postgres-42703 (column does
  // not exist) silently fallback gemacht, damit Pre-Migration-Deploys nicht
  // alle Chats unsichtbar machen.
  let _archivedColumnMissing = false;
  async function loadChats(opts = {}) {
    // Default 500 statt 50 — sonst zeigen Filter-Counts veraltete Zahlen
    // weil nicht alle Chats geladen sind. Tattoo Fashion hat ~70 chats heute,
    // wird wahrscheinlich nie >500 erreichen ohne Pagination.
    const limit = opts.limit || 500;
    const archived = opts.archived === true;
    const buildQuery = (skipArchivedFilter) => {
      let q = sb
        .from('chats')
        .select('*')
        .order('last_message_at', { ascending: false, nullsFirst: false })
        .limit(limit);
      if (skipArchivedFilter) return q;
      if (archived) q = q.not('archived_at', 'is', null);
      else q = q.is('archived_at', null);
      return q;
    };
    let { data: chats, error } = await buildQuery(_archivedColumnMissing);
    // Fallback: archived_at-Spalte fehlt → DB-Migration nicht durch
    if (error && error.code === '42703') {
      console.warn('[db] archived_at column missing — retrying without filter. Run scripts/migrations/0010_archived_at.sql to fix.');
      _archivedColumnMissing = true;
      // Wenn explizit Archive-View, kann nichts ausgeben werden
      if (archived) return [];
      const retry = await buildQuery(true);
      chats = retry.data;
      error = retry.error;
    }
    if (error) {
      console.error('[db] loadChats error:', error);
      return [];
    }
    if (!chats || chats.length === 0) return [];

    // Letzte N Messages pro Chat in EINEM Roundtrip (chat_id IN (...))
    const ids = chats.map((c) => c.id);
    const { data: msgs, error: mErr } = await sb
      .from('messages')
      .select('*')
      .in('chat_id', ids)
      .order('created_at', { ascending: true });
    if (mErr) {
      console.error('[db] loadChats messages error:', mErr);
      return chats.map((c) => mapDbChat(c, []));
    }

    const byChat = {};
    (msgs || []).forEach((m) => {
      (byChat[m.chat_id] = byChat[m.chat_id] || []).push(m);
    });

    return chats.map((c) => mapDbChat(c, (byChat[c.id] || []).map(mapDbMessage)));
  }

  async function loadMessages(chatId) {
    const { data, error } = await sb
      .from('messages')
      .select('*')
      .eq('chat_id', chatId)
      .order('created_at', { ascending: true });
    if (error) {
      console.error('[db] loadMessages error:', error);
      return [];
    }
    return (data || []).map(mapDbMessage);
  }

  // ----- Phase 3: Schreib-Operations ------------------------------------------

  // UUID v4 fuer corr_id (matched mit messages.id im n8n-Outbound-Workflow).
  // Reihenfolge: crypto.randomUUID -> crypto.getRandomValues -> Math.random.
  function newCorrId() {
    if (typeof crypto !== 'undefined' && typeof crypto.randomUUID === 'function') {
      return crypto.randomUUID();
    }
    if (typeof crypto !== 'undefined' && typeof crypto.getRandomValues === 'function') {
      const buf = new Uint8Array(16);
      crypto.getRandomValues(buf);
      buf[6] = (buf[6] & 0x0f) | 0x40; // version 4
      buf[8] = (buf[8] & 0x3f) | 0x80; // variant
      const hex = Array.from(buf, (b) => b.toString(16).padStart(2, '0')).join('');
      return [
        hex.slice(0, 8),
        hex.slice(8, 12),
        hex.slice(12, 16),
        hex.slice(16, 20),
        hex.slice(20, 32),
      ].join('-');
    }
    // Last resort, nicht crypto-secure — vermeidet aber im Pre-Insert
    // einen 409-Collision-Loop bei legacy-Browsern.
    return 'xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx'.replace(/[xy]/g, (c) => {
      const r = (Math.random() * 16) | 0;
      const v = c === 'x' ? r : (r & 0x3) | 0x8;
      return v.toString(16);
    });
  }

  // Outbound-Send: POST an n8n-Workflow `outbound`. n8n schreibt Pre-Insert
  // mit id=corr_id, ruft Graph-API, patcht status. Realtime-INSERT kommt
  // beim Frontend mit derselben id an → dedup via id-match.
  async function sendOutbound(chatId, text, corrId, userId) {
    const url   = env.N8N_OUTBOUND_URL;
    const token = env.DASHBOARD_OUTBOUND_TOKEN;
    if (!url || !token) {
      throw new Error('N8N_OUTBOUND_URL oder DASHBOARD_OUTBOUND_TOKEN fehlt in shared/env.js');
    }
    // user_id ist seit n8n-Workflow v0.3 Pflicht (Validate-DB pruft
    // chat.account_id === user.account_id). Frueh fail-fast statt 400 vom Server.
    if (!userId || typeof userId !== 'string') {
      throw new Error('sendOutbound: userId fehlt — Aufrufer muss authentifizierten User durchreichen');
    }
    const res = await fetch(url, {
      method: 'POST',
      headers: {
        'Content-Type':  'application/json',
        'Authorization': 'Bearer ' + token,
      },
      body: JSON.stringify({
        chat_id: chatId,
        text,
        corr_id: corrId,
        user_id: userId,
      }),
    });
    let body = null;
    try { body = await res.json(); } catch (_) {}
    if (!res.ok) {
      const msg = (body && body.error) || ('http-' + res.status);
      throw new Error(msg);
    }
    // 207 = Graph erfolgreich, aber DB-PATCH failed → Message bleibt in DB
    // auf status='sending'. KI-Auto-Pause-Trigger feuert nicht. Fuer User
    // dennoch "gesendet", aber Audit-Trail inkonsistent → loggen.
    if (body && body.warning === 'db-patch-failed') {
      console.warn('[db] sendOutbound 207: Graph ok, DB inkonsistent', body);
    }
    return body;
  }

  async function setChatAIEnabled(chatId, enabled, userId, accountId) {
    const { error } = await sb
      .from('chats')
      .update({ ai_enabled: !!enabled })
      .eq('id', chatId);
    if (error) throw error;
    if (accountId) {
      const { error: eErr } = await sb.from('events').insert({
        account_id: accountId,
        chat_id:    chatId,
        user_id:    userId || null,
        type:       enabled ? 'ai_enabled_chat' : 'ai_disabled_chat',
        payload:    { source: 'dashboard' },
      });
      if (eErr) console.error('[db] setChatAIEnabled events insert error:', eErr);
    }
  }

  async function resumeChatAIManual(chatId, userId, accountId) {
    const { error } = await sb
      .from('chats')
      .update({ ai_paused: false, pause_reason: null, paused_by: null, paused_at: null })
      .eq('id', chatId);
    if (error) throw error;
    if (accountId) {
      const { error: eErr } = await sb.from('events').insert({
        account_id: accountId,
        chat_id:    chatId,
        user_id:    userId || null,
        type:       'ai_resumed_manually',
        payload:    { source: 'dashboard' },
      });
      if (eErr) console.error('[db] resumeChatAIManual events insert error:', eErr);
    }
  }

  async function setGlobalAI(accountId, on, userId) {
    // .select() erzwingt, dass Supabase die betroffene Row zurueckgibt — bei
    // RLS-Fehlschlag kommt ein leeres Array statt silent-success durch.
    const { data, error } = await sb
      .from('accounts')
      .update({ ki_global_on: !!on })
      .eq('id', accountId)
      .select('id');
    if (error) throw error;
    if (!data || data.length === 0) {
      throw new Error('setGlobalAI: keine Row aktualisiert (RLS oder accountId falsch?)');
    }
    const { error: eErr } = await sb.from('events').insert({
      account_id: accountId,
      chat_id:    null,
      user_id:    userId || null,
      type:       on ? 'ai_global_on' : 'ai_global_off',
      payload:    { source: 'dashboard' },
    });
    if (eErr) console.error('[db] setGlobalAI events insert error:', eErr);
  }

  async function markChatRead(chatId) {
    const { error } = await sb
      .from('chats')
      .update({ unread_count: 0 })
      .eq('id', chatId);
    if (error) console.error('[db] markChatRead error:', error);
  }

  async function pinChat(chatId, pinned = true) {
    const value = pinned ? new Date().toISOString() : null;
    const { error } = await sb
      .from('chats')
      .update({ pinned_at: value })
      .eq('id', chatId);
    if (error) console.error('[db] pinChat error:', error);
    return !error;
  }

  async function archiveChat(chatId, archived = true) {
    const value = archived ? new Date().toISOString() : null;
    const { error } = await sb
      .from('chats')
      .update({ archived_at: value })
      .eq('id', chatId);
    if (error) console.error('[db] archiveChat error:', error);
    return !error;
  }

  async function deleteChat(chatId) {
    // Hard-Delete — Cascade entfernt messages-Rows automatisch (FK-Constraint).
    const { error } = await sb.from('chats').delete().eq('id', chatId);
    if (error) console.error('[db] deleteChat error:', error);
    return !error;
  }

  async function updateChatTags(chatId, tags) {
    const { error } = await sb.from('chats').update({ tags: Array.isArray(tags) ? tags : [] }).eq('id', chatId);
    if (error) console.error('[db] updateChatTags error:', error);
    return !error;
  }

  async function updateChatNote(chatId, note) {
    const value = (note || '').trim() || null;
    const { error } = await sb.from('chats').update({ staff_note: value }).eq('id', chatId);
    if (error) {
      if (error.code === '42703') {
        console.warn('[db] staff_note column missing — run scripts/migrations/0011_chat_features.sql');
        return false;
      }
      console.error('[db] updateChatNote error:', error);
    }
    return !error;
  }

  // ----- Realtime -------------------------------------------------------------

  function subscribeChats(onChange) {
    const channel = sb
      .channel('inbox:chats')
      .on(
        'postgres_changes',
        { event: '*', schema: 'public', table: 'chats' },
        (payload) => onChange(payload)
      )
      .subscribe();
    return () => sb.removeChannel(channel);
  }

  // Listen auf INSERT + UPDATE, damit der status-Lifecycle (sending -> sent /
  // failed) im Frontend ankommt. DELETE bewusst ausgelassen (Phase 3 hat
  // keine Message-Loeschung im UI).
  function subscribeMessages(chatId, onChange) {
    const channel = sb
      .channel(`inbox:messages:${chatId}`)
      .on(
        'postgres_changes',
        {
          event: 'INSERT',
          schema: 'public',
          table: 'messages',
          filter: `chat_id=eq.${chatId}`,
        },
        (payload) => onChange(payload.new, 'INSERT')
      )
      .on(
        'postgres_changes',
        {
          event: 'UPDATE',
          schema: 'public',
          table: 'messages',
          filter: `chat_id=eq.${chatId}`,
        },
        (payload) => onChange(payload.new, 'UPDATE')
      )
      .subscribe();
    return () => sb.removeChannel(channel);
  }

  // ----- Export ---------------------------------------------------------------

  window.TF_DB = {
    sb,
    mapDbChat,
    mapDbMessage,
    auth: { getSession, getCurrentUser, signIn, signOut },
    loadAccount,
    loadChats,
    loadMessages,
    subscribeChats,
    subscribeMessages,
    // Phase 3
    newCorrId,
    sendOutbound,
    setChatAIEnabled,
    resumeChatAIManual,
    setGlobalAI,
    markChatRead,
    pinChat,
    archiveChat,
    deleteChat,
    updateChatTags,
    updateChatNote,
  };
})();