// FriendlyRP · Google Sheets ↔ Supabase Sync v5
const SUPABASE_URL = 'https://nfvzkdgewndmsulxnjhd.supabase.co';
const SUPABASE_KEY = 'eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJzdXBhYmFzZSIsInJlZiI6Im5mdnprZGdld25kbXN1bHhuamhkIiwicm9sZSI6ImFub24iLCJpYXQiOjE3ODAwNDIzOTYsImV4cCI6MjA5NTYxODM5Nn0.RQ8hCr8d991PsKFbSV7KSAYnjcarCUmFnXT8gKEY2Fs';

const TABLES = {
  'PayDay':        { table: 'payday',         cols: ['stufe','lohn_pro_stunde','tages_einnahmen'], key: 'stufe' },
  'Fahrzeuge':     { table: 'vehicle_stufen', cols: ['stufe','preis_von','preis_bis','haendler','typ'], key: 'stufe' },
  'Motorraeder':   { table: 'moto_stufen',    cols: ['stufe','preis_von','preis_bis'], key: 'stufe' },
  'LSPD':          { table: 'lspd_raenge',    cols: ['rang','titel','insignie','fahrzeuge'], key: 'rang' },
  'ShopLabels':    { table: 'shop_labels',    cols: ['label','beschreibung'], key: 'label' },
  'LootStufen':    { table: 'loot_stufen',    cols: ['stufe','miete_von','miete_bis','notiz'], key: 'stufe' },
  'Changelog':     { table: 'changelog',      cols: ['datum','version','titel','beschreibung','status'], key: 'id' },
};

function supaFetch(path, method, body) {
  return UrlFetchApp.fetch(SUPABASE_URL + '/rest/v1/' + path, {
    method: method || 'GET',
    headers: { 'apikey': SUPABASE_KEY, 'Authorization': 'Bearer ' + SUPABASE_KEY,
      'Content-Type': 'application/json', 'Prefer': 'return=minimal' },
    payload: body ? JSON.stringify(body) : undefined, muteHttpExceptions: true
  });
}
function pushToSupabase() {
  const ss = SpreadsheetApp.getActiveSpreadsheet(); let n = 0;
  for (const [name, cfg] of Object.entries(TABLES)) {
    const sheet = ss.getSheetByName(name); if (!sheet) continue;
    const rows = sheet.getDataRange().getValues(); if (rows.length < 2) continue;
    const data = rows.slice(1).filter(r => r[0] !== '').map(r => {
      const o = {}; cfg.cols.forEach((c,i) => { if(r[i]!=='') o[c]=r[i]; }); return o;
    });
    supaFetch(cfg.table, 'DELETE');
    if (data.length) supaFetch(cfg.table, 'POST', data); n++;
  }
  SpreadsheetApp.getUi().alert('✓ ' + n + ' Tabellen synchronisiert!');
}
function pullFromSupabase() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  for (const [name, cfg] of Object.entries(TABLES)) {
    let sheet = ss.getSheetByName(name);
    if (!sheet) sheet = ss.insertSheet(name);
    const data = JSON.parse(supaFetch(cfg.table + '?order=' + cfg.key).getContentText());
    sheet.clearContents();
    sheet.getRange(1,1,1,cfg.cols.length).setValues([cfg.cols]);
    if (data.length) sheet.getRange(2,1,data.length,cfg.cols.length).setValues(data.map(r => cfg.cols.map(c => r[c]??'')));
  }
  SpreadsheetApp.getUi().alert('✓ Alle Daten geladen!');
}
function onEdit(e) { if(TABLES[e.source.getActiveSheet().getName()]){Utilities.sleep(400);pushToSupabase();} }
function setupTrigger() {
  ScriptApp.getProjectTriggers().forEach(t => ScriptApp.deleteTrigger(t));
  ScriptApp.newTrigger('onEdit').forSpreadsheet(SpreadsheetApp.getActive()).onEdit().create();
  SpreadsheetApp.getUi().alert('✓ Auto-Sync eingerichtet!');
}
function onOpen() {
  SpreadsheetApp.getUi().createMenu('🔄 FriendlyRP Sync')
    .addItem('📥 Pull von Supabase','pullFromSupabase').addItem('📤 Push zu Supabase','pushToSupabase')
    .addSeparator().addItem('⚙️ Auto-Sync einrichten','setupTrigger').addToUi();
}