// db.jsx — lớp dữ liệu Supabase cho 4 module (no-build). Nạp SAU supabase.jsx, TRƯỚC main.jsx.
// Map snake↔camel + reshape lồng nhau (timeline/items/schedule) → shape KHỚP mock để UI không phải đổi.
// Chỉ dùng khi window.SB_READY && có session (main.jsx tự gate). Mock vẫn chạy khi chưa cấu hình.
(function () {
  const sb = window.sb;

  /* ---- DB row → app shape (camelCase, giống data.jsx/cdata.jsx) ---- */
  const mapCustomer = (r) => ({ code:r.code, name:r.name, shortName:r.short_name, lang:r.lang, mst:r.mst, industry:r.industry, country:r.country, owner:r.owner, status:r.status });
  const mapContact  = (r) => ({ code:r.code, name:r.name, native:r.native || '', nationality:r.nationality, type:r.type, customer:r.customer || '', title:r.title, email:r.email, phone:r.phone, owner:r.owner, status:r.status, tx:[] });
  const mapTx       = (r) => ({ date:r.date, type:r.type, title:r.title, body:r.body });
  const mapItem     = (r) => ({ name:r.name, unit:r.unit, qty:Number(r.qty), price:Number(r.price), vat:Number(r.vat) });
  const mapSched    = (r) => ({ no:r.no, amount:Number(r.amount), dueDate:r.due_date, condition:r.condition, paid:r.paid, paidDate:r.paid_date });
  const mapContract = (r) => ({ code:r.code, category:r.category, signDate:r.sign_date, customer:r.customer || '', rep:r.rep || null, termType:r.term_type, startDate:r.start_date, endDate:r.end_date, status:r.status, remindBefore:r.remind_before });
  const mapExpense  = (r) => ({ id:r.id, date:r.date, cat:r.cat, desc:r.descr, amount:Number(r.amount), status:r.status });
  const mapSent     = (r) => ({ id:r.id, to:r.to || [], cc:r.cc || [], subject:r.subject, html:r.html, date:r.date, status:r.status });
  const mapTemplate = (r) => ({ id:r.id, name:r.name, subject:r.subject, html:r.html });
  const mapSettings = (r) => ({ signature:r.signature_html, account:r.gmail_account, connected:r.connected });

  /* ---- app shape → DB row ---- */
  const unCustomer = (c) => ({ code:c.code, name:c.name, short_name:c.shortName, lang:c.lang, mst:c.mst, industry:c.industry, country:c.country, owner:c.owner, status:c.status });
  const unContact  = (c) => ({ code:c.code, name:c.name, native:c.native || null, nationality:c.nationality, type:c.type, customer:c.customer || null, title:c.title, email:c.email, phone:c.phone, owner:c.owner, status:c.status });
  const unContract = (c) => ({ code:c.code, category:c.category, sign_date:c.signDate, customer:c.customer || null, rep:c.rep || null, term_type:c.termType, start_date:c.startDate, end_date:c.endDate || null, status:c.status, remind_before:c.remindBefore });
  const unExpense  = (e) => ({ id:e.id, date:e.date, cat:e.cat, descr:e.desc, amount:e.amount, status:e.status });
  const unSent     = (e) => ({ id:e.id, to:e.to || [], cc:e.cc || [], subject:e.subject, html:e.html, date:e.date, status:e.status || 'sent' });
  const unTemplate = (t) => ({ id:t.id, name:t.name, subject:t.subject, html:t.html });

  async function loadAll() {
    if (!window.SB_READY) return null;
    const [cs, ct, tl, con, items, sch, exp, mt, ms, mg] = await Promise.all([
      sb.from('customer_customers').select('*').order('code'),
      sb.from('contact_contacts').select('*').order('code'),
      sb.from('contact_timeline').select('*').order('date', { ascending:false }),
      sb.from('contract_contracts').select('*').order('code'),
      sb.from('contract_items').select('*').order('idx'),
      sb.from('contract_schedule').select('*').order('no'),
      sb.from('finance_expenses').select('*').order('date'),
      sb.from('email_templates').select('*').order('id'),
      sb.from('email_sent').select('*').order('date', { ascending:false }),
      sb.from('email_settings').select('*').eq('id', 'default').maybeSingle(),
    ]);
    const bad = [cs, ct, tl, con, items, sch, exp, mt, ms].find(r => r.error);
    if (bad && bad.error) { console.warn('[db] loadAll:', bad.error.message); return null; }

    const customers = (cs.data || []).map(mapCustomer);

    const txByC = {};
    (tl.data || []).forEach(r => { (txByC[r.contact_code] = txByC[r.contact_code] || []).push(mapTx(r)); });
    const contacts = (ct.data || []).map(r => { const c = mapContact(r); c.tx = txByC[r.code] || []; return c; });

    const itByC = {}, scByC = {};
    (items.data || []).forEach(r => { (itByC[r.contract_code] = itByC[r.contract_code] || []).push(mapItem(r)); });
    (sch.data || []).forEach(r => { (scByC[r.contract_code] = scByC[r.contract_code] || []).push(mapSched(r)); });
    const contracts = (con.data || []).map(r => { const c = mapContract(r); c.items = itByC[r.code] || []; c.schedule = scByC[r.code] || []; return c; });

    const expenses = (exp.data || []).map(mapExpense);

    const templates = (mt.data || []).map(mapTemplate);
    const sentEmails = (ms.data || []).map(mapSent);
    const mailSettings = mg.data ? mapSettings(mg.data) : null;

    return { customers, contacts, contracts, expenses, templates, sentEmails, mailSettings };
  }

  async function listUsers() {
    if (!window.SB_READY) return null;
    const { data, error } = await sb.from('profiles').select('id,full_name,email').order('full_name');
    if (error || !data) return null;
    return data.map(r => ({ id:r.id, name:r.full_name || r.email, email:r.email }));
  }

  async function myProfile(uid) {
    if (!window.SB_READY || !uid) return null;
    const { data, error } = await sb.from('profiles').select('id,email,full_name,role').eq('id', uid).maybeSingle();
    if (error || !data) return null;
    return { id:data.id, name:data.full_name || data.email, email:data.email, role:data.role || 'staff', title:'' };
  }

  /* ---- writes (throw để caller rollback) ---- */
  async function insertCustomer(c) { const { error } = await sb.from('customer_customers').insert(unCustomer(c)); if (error) throw error; }
  async function insertContact(c)  { const { error } = await sb.from('contact_contacts').insert(unContact(c));   if (error) throw error; }
  async function addTimeline(code, t) { const { error } = await sb.from('contact_timeline').insert({ contact_code:code, date:t.date, type:t.type, title:t.title, body:t.body }); if (error) throw error; }
  async function insertExpense(e)  { const { error } = await sb.from('finance_expenses').insert(unExpense(e));    if (error) throw error; }

  // upsert HĐ + thay toàn bộ items/schedule (dùng cho tạo mới & sửa: mark-paid, renew)
  async function upsertContract(c) {
    let r = await sb.from('contract_contracts').upsert(unContract(c)); if (r.error) throw r.error;
    await sb.from('contract_items').delete().eq('contract_code', c.code);
    await sb.from('contract_schedule').delete().eq('contract_code', c.code);
    const items = (c.items || []).map((i, idx) => ({ contract_code:c.code, idx:idx + 1, name:i.name, unit:i.unit, qty:i.qty, price:i.price, vat:i.vat }));
    const sched = (c.schedule || []).map(p => ({ contract_code:c.code, no:p.no, amount:p.amount, due_date:p.dueDate, condition:p.condition, paid:!!p.paid, paid_date:p.paidDate || null }));
    if (items.length) { r = await sb.from('contract_items').insert(items);    if (r.error) throw r.error; }
    if (sched.length) { r = await sb.from('contract_schedule').insert(sched); if (r.error) throw r.error; }
  }

  async function updateCustomer(c) { const { error } = await sb.from('customer_customers').update(unCustomer(c)).eq('code', c.code); if (error) throw error; }
  async function deleteCustomer(code) { const { error } = await sb.from('customer_customers').delete().eq('code', code); if (error) throw error; }
  async function updateContact(c)  { const { error } = await sb.from('contact_contacts').update(unContact(c)).eq('code', c.code); if (error) throw error; }
  async function deleteContact(code) { const { error } = await sb.from('contact_contacts').delete().eq('code', code); if (error) throw error; }
  async function deleteContract(code) { const { error } = await sb.from('contract_contracts').delete().eq('code', code); if (error) throw error; } // items/schedule cascade
  async function updateExpense(e) { const { error } = await sb.from('finance_expenses').update(unExpense(e)).eq('id', e.id); if (error) throw error; }
  async function deleteExpense(id) { const { error } = await sb.from('finance_expenses').delete().eq('id', id); if (error) throw error; }

  async function insertSentEmail(e) { const { error } = await sb.from('email_sent').insert(unSent(e)); if (error) throw error; }
  async function upsertTemplate(t)   { const { error } = await sb.from('email_templates').upsert(unTemplate(t)); if (error) throw error; }
  async function saveSettings(s)     { const { error } = await sb.from('email_settings').upsert({ id:'default', signature_html:s.signature, gmail_account:s.account, connected:s.connected !== false }); if (error) throw error; }

  window.DB = {
    loadAll, myProfile, listUsers,
    insertCustomer, updateCustomer, deleteCustomer,
    insertContact, updateContact, deleteContact, addTimeline,
    insertExpense, updateExpense, deleteExpense,
    upsertContract, deleteContract,
    insertSentEmail, upsertTemplate, saveSettings,
  };
})();