💰 מאור אישי

// ============================================================

// דשבורד כספים — Google Sheets Backend (טבלאות מסודרות)

// ============================================================


const SHEETS_MAP = {

  income:            'הכנסות',

  expenses:          'הוצאות',

  fixed:             'הוצאות קבועות',

  investments:       'השקעות',

  collection:        'גבייה',

  invoices:          'חשבוניות',

  contracts:         'חוזים',

  supplierPayments:  'ספקים עתידיים',

  subscriptions:     'מנויים',

  vendorPresets:     'ספקים שמורים'

};

const META_SHEET = '_מטא';

const SETTINGS_SHEET = 'הגדרות';


function doGet(e) {

  try {

    const ss = SpreadsheetApp.getActiveSpreadsheet();

    const result = {};

    Object.keys(SHEETS_MAP).forEach(k => {

      result[k] = readSheet(ss, SHEETS_MAP[k]);

    });

    result.settings = readSettings(ss);

    const meta = readMeta(ss);

    return respond({ ok: true, data: result, lastUpdated: meta.lastUpdated || null });

  } catch (err) {

    return respond({ ok: false, error: err.toString() });

  }

}


function doPost(e) {

  try {

    const ss = SpreadsheetApp.getActiveSpreadsheet();

    const body = e && e.postData ? e.postData.contents : '{}';

    const data = JSON.parse(body);

    Object.keys(SHEETS_MAP).forEach(k => {

      writeSheet(ss, SHEETS_MAP[k], data[k] || []);

    });

    writeSettings(ss, data.settings || {});

    const now = new Date().toISOString();

    writeMeta(ss, now, data);

    return respond({ ok: true, lastUpdated: now });

  } catch (err) {

    return respond({ ok: false, error: err.toString() });

  }

}


function respond(obj) {

  return ContentService.createTextOutput(JSON.stringify(obj))

    .setMimeType(ContentService.MimeType.JSON);

}


function getOrCreateSheet(ss, name) {

  let s = ss.getSheetByName(name);

  if (!s) s = ss.insertSheet(name);

  return s;

}


function readSheet(ss, name) {

  const sheet = ss.getSheetByName(name);

  if (!sheet) return [];

  const values = sheet.getDataRange().getValues();

  if (values.length < 2) return [];

  const headers = values[0].map(h => String(h || '').trim());

  return values.slice(1).map(row => {

    const obj = {};

    headers.forEach((h, i) => {

      if (!h) return;

      let v = row[i];

      if (v === '' || v === null || v === undefined) return;

      if (typeof v === 'string') {

        const s = v.trim();

        if ((s.startsWith('{') && s.endsWith('}')) || (s.startsWith('[') && s.endsWith(']'))) {

          try { v = JSON.parse(s); } catch (e) {}

        }

      }

      obj[h] = v;

    });

    return obj;

  }).filter(o => Object.keys(o).length > 0);

}


function readSettings(ss) {

  const sheet = ss.getSheetByName(SETTINGS_SHEET);

  if (!sheet) return {};

  const values = sheet.getDataRange().getValues();

  const out = {};

  values.slice(1).forEach(row => {

    if (!row[0]) return;

    let v = row[1];

    if (typeof v === 'string') {

      const s = v.trim();

      if ((s.startsWith('{') && s.endsWith('}')) || (s.startsWith('[') && s.endsWith(']'))) {

        try { v = JSON.parse(s); } catch (e) {}

      }

    }

    if (v !== '' && v !== null && v !== undefined) out[String(row[0])] = v;

  });

  return out;

}


function readMeta(ss) {

  const sheet = ss.getSheetByName(META_SHEET);

  if (!sheet) return {};

  const values = sheet.getDataRange().getValues();

  const out = {};

  values.slice(1).forEach(row => {

    if (row[0]) out[String(row[0])] = row[1];

  });

  return out;

}


function writeSheet(ss, name, records) {

  const sheet = getOrCreateSheet(ss, name);

  sheet.clear();

  if (!records || records.length === 0) {

    sheet.getRange(1, 1).setValue('(אין נתונים)')

      .setFontColor('#9CA3AF').setFontStyle('italic');

    return;

  }

  const fieldSet = {};

  records.forEach(r => Object.keys(r).forEach(k => fieldSet[k] = true));

  const fields = Object.keys(fieldSet);

  const maxCols = sheet.getMaxColumns();

  if (fields.length > maxCols) {

    sheet.insertColumnsAfter(maxCols, fields.length - maxCols);

  }

  const headerRange = sheet.getRange(1, 1, 1, fields.length);

  headerRange.setValues([fields]);

  headerRange.setFontWeight('bold').setBackground('#1F2937').setFontColor('#FFFFFF');

  const rows = records.map(r => fields.map(f => valueToCell(r[f])));

  sheet.getRange(2, 1, rows.length, fields.length).setValues(rows);

  sheet.setFrozenRows(1);

  try { sheet.autoResizeColumns(1, fields.length); } catch (e) {}

}


function valueToCell(v) {

  if (v === undefined || v === null) return '';

  if (typeof v === 'object') return JSON.stringify(v);

  return v;

}


function writeSettings(ss, settings) {

  const sheet = getOrCreateSheet(ss, SETTINGS_SHEET);

  sheet.clear();

  const header = sheet.getRange(1, 1, 1, 2);

  header.setValues([['שדה', 'ערך']]);

  header.setFontWeight('bold').setBackground('#1F2937').setFontColor('#FFFFFF');

  const keys = Object.keys(settings || {});

  if (keys.length > 0) {

    const rows = keys.map(k => [k, valueToCell(settings[k])]);

    sheet.getRange(2, 1, rows.length, 2).setValues(rows);

  }

  sheet.setFrozenRows(1);

  try { sheet.autoResizeColumns(1, 2); } catch (e) {}

}


function writeMeta(ss, lastUpdated, data) {

  const sheet = getOrCreateSheet(ss, META_SHEET);

  sheet.clear();

  const rows = [

    ['שדה', 'ערך'],

    ['עדכון אחרון', new Date(lastUpdated).toLocaleString('he-IL')],

    ['ISO Timestamp', lastUpdated]

  ];

  Object.keys(SHEETS_MAP).forEach(k => {

    const arr = data[k];

    if (Array.isArray(arr)) rows.push([SHEETS_MAP[k], arr.length + ' רשומות']);

  });

  sheet.getRange(1, 1, rows.length, 2).setValues(rows);

  sheet.getRange(1, 1, 1, 2).setFontWeight('bold').setBackground('#1F2937').setFontColor('#FFFFFF');

  sheet.setFrozenRows(1);

  try { sheet.autoResizeColumns(1, 2); } catch (e) {}

}