const SUPABASE_BASE = 'https://hkybarlwyujbzrvcwpvz.supabase.co/rest/v1';
const SCRAPED_TABLE = `${SUPABASE_BASE}/scraped_rounds`;
const LIVE_TABLE = `${SUPABASE_BASE}/ROUNDS_Finished`;
const LIVE_CACHE_TABLE = `${SUPABASE_BASE}/live_round_cache`;
const LICENSE_TABLE = `${SUPABASE_BASE}/license_keys`;
const CLAN_MATCH_TABLE = `${SUPABASE_BASE}/clan_matchID`;
const KEY = 'sb_publishable_enH8nzmHUOt3kAfAepI2yg_pLZtRT8t';
const AUTH_SESSION_KEY = 'mw_sidebar_auth_session_v1';

const HEADERS = {
  apikey: KEY,
  Authorization: `Bearer ${KEY}`,
  'Content-Type': 'application/json'
};

function getAuthHeaders() {
  const headers = { ...HEADERS };
  try {
    const raw = localStorage.getItem(AUTH_SESSION_KEY);
    if (!raw) return headers;
    const session = JSON.parse(raw);
    const token = session?.access_token;
    if (token) {
      headers.Authorization = `Bearer ${token}`;
    }
  } catch (_) {
    // ignore parse errors
  }
  return headers;
}

const KORREL_LOG_TABLE = `${SUPABASE_BASE}/korrel_tools_log`;
const KORREL_LOG_USER_KEY = 'mw_live_korrel_user_id';

function getKorrelLogUserId() {
  try {
    const stored = localStorage.getItem(KORREL_LOG_USER_KEY);
    if (stored) return stored;
  } catch (err) {
    /* ignore */
  }
  const generated = typeof crypto !== 'undefined' && typeof crypto.randomUUID === 'function'
    ? crypto.randomUUID()
    : `anon:${Date.now()}-${Math.random().toString(36).slice(2, 8)}`;
  try {
    localStorage.setItem(KORREL_LOG_USER_KEY, generated);
  } catch (err) {
    /* ignore */
  }
  return generated;
}

export async function logKorrelMessage(message, { level = 'info', source = 'mw-live', userLabel = 'MW Live' } = {}) {
  if (!message) return null;
  const payload = [{
    user_id: getKorrelLogUserId(),
    user_label: userLabel,
    level,
    source,
    message,
    created_at: new Date().toISOString()
  }];
  const res = await fetch(KORREL_LOG_TABLE, {
    method: 'POST',
    headers: getAuthHeaders(),
    body: JSON.stringify(payload)
  });
  if (!res.ok) {
    const text = await res.text().catch(() => '');
    console.warn('[MW] Failed to log Korrel message:', res.status, text);
    throw new Error(`Failed to log Korrel message: ${res.status} ${text}`);
  }
  return res.json().catch(() => null);
}

function buildSelect(fields = []) {
  return fields.join(',');
}

export async function fetchLiveRounds() {
  const select = buildSelect([
    'id',
    'block_id',
    'time_round_finished',
    'league',
    'cycle',
    'round_time',
    'multiplier',
    'clan_name',
    'clan_id',
    'clan_th_per_s',
    'clan_percentage',
    'clan_boosts',
    'winner_player_name',
    'player_th_per_s',
    'player_minebox',
    'player_percentage',
    'player_boosts',
    'winner_reward_gmt',
    'send_reward_fund',
    'leading_clans_snapshot',
    'leading_players_snapshot',
    'top_10_players_with_boosts',
    'top_10_clans_with_boosts',
    'data_complete'
  ]);
  const url = `${LIVE_TABLE}?select=${select}&order=time_round_finished.desc.nullslast&order=block_id.desc&limit=5`;
  const res = await fetch(url, { headers: getAuthHeaders() });
  if (!res.ok) {
    const errorText = await res.text();
    console.error('[MW] Failed to fetch rounds:', res.status, errorText);
    throw new Error(`Failed to fetch rounds: ${res.status}`);
  }
  return res.json();
}

export async function fetchLicenses() {
  const url = `${LICENSE_TABLE}?select=license_key,active,expires_at&limit=10`;
  const res = await fetch(url, { headers: getAuthHeaders() });
  if (!res.ok) throw new Error('Failed license fetch');
  return res.json();
}

export async function fetchBoostUsage() {
  const url = `${CLAN_MATCH_TABLE}?select=clan_id,clan_name&limit=8`;
  const res = await fetch(url, { headers: getAuthHeaders() });
  if (!res.ok) throw new Error('Failed boost usage');
  return res.json();
}

// Fetch all clan mappings from clan_matchID table
export async function fetchClanMappings() {
  const url = `${CLAN_MATCH_TABLE}?select=clan_id,clan_name&order=time_scrape.desc&limit=1000`;
  const res = await fetch(url, { headers: getAuthHeaders() });
  if (!res.ok) {
    const errorText = await res.text();
    console.error('[MW] Failed to fetch clan mappings:', res.status, errorText);
    throw new Error(`Failed to fetch clan mappings: ${res.status}`);
  }
  return res.json();
}

// Upsert a finished round into the ROUNDS_Finished table
// Will update if the combination of block_id, cycle, and league already exists
export async function insertFinishedRound(roundData) {
  console.log('[MW] Upserting finished round:', roundData);
  window.debugLogger?.info('insertFinishedRound Called', {
    block_id: roundData.block_id,
    clan: roundData.clan_name,
    data_complete: roundData.data_complete
  });

  // Validate required fields
  if (!roundData.block_id) {
    const error = new Error('block_id is required');
    window.debugLogger?.error('Validation Failed', { error: 'block_id is required' });
    throw error;
  }
  if (!roundData.time_round_finished) {
    const error = new Error('time_round_finished is required');
    window.debugLogger?.error('Validation Failed', { error: 'time_round_finished is required' });
    throw error;
  }

  // Use on_conflict parameter to handle duplicates based on block_id, cycle, league
  // Assuming the table has a unique constraint on (block_id, cycle, league)
  const url = `${LIVE_TABLE}?on_conflict=block_id,cycle,league`;
  const payload = [{
    time_round_finished: roundData.time_round_finished,
    league: roundData.league || null,
    cycle: roundData.cycle || null,
    block_id: parseInt(roundData.block_id),
    round_time: roundData.round_time || null,
    multiplier: roundData.multiplier ? parseFloat(roundData.multiplier) : null,
    clan_name: roundData.clan_name || null,
    clan_th_per_s: roundData.clan_th_per_s ? parseFloat(roundData.clan_th_per_s) : null,
    clan_percentage: roundData.clan_percentage ? parseFloat(roundData.clan_percentage) : null,
    clan_boosts: roundData.clan_boosts || null,
    player_th_per_s: roundData.player_th_per_s ? parseFloat(roundData.player_th_per_s) : null,
    player_minebox: roundData.player_minebox || null,
    player_percentage: roundData.player_percentage ? parseFloat(roundData.player_percentage) : null,
    player_boosts: roundData.player_boosts || null,
    send_reward_fund: roundData.send_reward_fund ? parseFloat(roundData.send_reward_fund) : null,
    clan_id: roundData.clan_id ? parseInt(roundData.clan_id) : null,
    leading_clans_snapshot: roundData.leading_clans_snapshot || null,
    leading_players_snapshot: roundData.leading_players_snapshot || null,
    winner_player_name: roundData.winner_player_name || null,
    winner_reward_gmt: roundData.winner_reward_gmt ? parseFloat(roundData.winner_reward_gmt) : null,
    top_10_players_with_boosts: roundData.top_10_players_with_boosts || null,
    top_10_clans_with_boosts: roundData.top_10_clans_with_boosts || null,
    data_complete: roundData.data_complete || false
  }];

  const res = await fetch(url, {
    method: 'POST',
    headers: {
      ...getAuthHeaders(),
      'Prefer': 'resolution=merge-duplicates'
    },
    body: JSON.stringify(payload)
  });

  if (!res.ok) {
    const text = await res.text();
    console.error('[MW] Failed to upsert finished round:', res.status, text);
    window.debugLogger?.error('Database Upsert Failed', {
      status: res.status,
      error: text,
      block_id: roundData.block_id
    });
    if (text.includes('42P10')) {
      console.warn('[MW] Upsert failed due to missing unique constraint, retrying as insert');
      window.debugLogger?.warning('Database Upsert Fallback', {
        message: 'Retrying insert without on_conflict',
        block_id: roundData.block_id
      });
      const insertRes = await fetch(LIVE_TABLE, {
        method: 'POST',
        headers: {
          ...getAuthHeaders(),
          'Prefer': 'return=representation'
        },
        body: JSON.stringify(payload)
      });
      if (!insertRes.ok) {
        const insertText = await insertRes.text();
        console.error('[MW] Failed to insert finished round:', insertRes.status, insertText);
        window.debugLogger?.error('Database Insert Failed', {
          status: insertRes.status,
          error: insertText,
          block_id: roundData.block_id
        });
        throw new Error(`Failed to insert finished round: ${insertRes.status} - ${insertText}`);
      }
      const insertResult = await insertRes.json();
      console.log('[MW] Successfully inserted finished round:', insertResult);
      window.debugLogger?.success('Database Insert Success', {
        block_id: roundData.block_id,
        clan: roundData.clan_name
      });
      return insertResult;
    }
    throw new Error(`Failed to upsert finished round: ${res.status} - ${text}`);
  }

  const result = await res.json();
  console.log('[MW] Successfully upserted finished round:', result);
  window.debugLogger?.success('Database Upsert Success', {
    block_id: roundData.block_id,
    clan: roundData.clan_name
  });
  return result;
}

// Insert/update live round cache in the scraped_rounds table
export async function insertLiveScrapedRound(payload) {
  const url = `${SCRAPED_TABLE}?on_conflict=round_id,league,cycle`;
  const sanitizedPayload = { ...payload };
  const res = await fetch(url, {
    method: 'POST',
    headers: {
      ...getAuthHeaders(),
      Prefer: 'resolution=merge-duplicates'
    },
    body: JSON.stringify([sanitizedPayload])
  });

  if (!res.ok) {
    const text = await res.text();
    console.error('[MW] Failed to upsert live scraped round:', res.status, text);
    throw new Error(`Failed to upsert live scraped round: ${res.status} - ${text}`);
  }

  const result = await res.json();
  console.debug('[MW] Successfully upserted live scraped round:', result);
  return result;
}

export async function insertLiveRoundCache(row) {
  if (!row || !row.owner_id || !row.cycle_id || !row.league_id) {
    throw new Error('Missing owner_id, cycle_id, or league_id for live cache insert');
  }
  const url = `${LIVE_CACHE_TABLE}?on_conflict=owner_id,cycle_id,league_id`;
  const payload = [row];
  const res = await fetch(url, {
    method: 'POST',
    headers: {
      ...getAuthHeaders(),
      Prefer: 'resolution=merge-duplicates'
    },
    body: JSON.stringify(payload)
  });
  if (!res.ok) {
    const text = await res.text().catch(() => '');
    console.error('[MW] Failed to upsert live cache row:', res.status, text);
    throw new Error(`Failed to upsert live cache row: ${res.status} - ${text}`);
  }
  const result = await res.json().catch(() => null);
  console.debug('[MW] Successfully upserted live cache row:', result);
  return result;
}

// Upsert clan match data (clan_id and clan_name mapping)
export async function upsertClanMatch(clanId, clanName) {
  console.log('[MW] Upserting clan match:', { clanId, clanName });

  if (!clanId || !clanName) {
    console.warn('[MW] Skipping clan match upsert - missing clanId or clanName');
    return null;
  }

  const url = `${CLAN_MATCH_TABLE}?on_conflict=clan_id`;
  const payload = [{
    clan_id: parseInt(clanId),
    clan_name: clanName,
    time_scrape: new Date().toISOString()
  }];

  const res = await fetch(url, {
    method: 'POST',
    headers: {
      ...getAuthHeaders(),
      'Prefer': 'resolution=merge-duplicates,return=representation'
    },
    body: JSON.stringify(payload)
  });

  if (!res.ok) {
    const text = await res.text();
    console.error('[MW] Failed to upsert clan match:', res.status, text);
    throw new Error(`Failed to upsert clan match: ${res.status} - ${text}`);
  }

  const text = await res.text().catch(() => '');
  if (!text) {
    console.log('[MW] Successfully upserted clan match (empty response).');
    return [];
  }
  try {
    const result = JSON.parse(text);
    console.log('[MW] Successfully upserted clan match:', result);
    return result;
  } catch (err) {
    console.warn('[MW] Upserted clan match but response was not JSON:', text);
    return [];
  }
}
