// 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, address:r.address || '', industry:r.industry, country:r.country, owner:r.owner, status:r.status, createdAt:r.created_at || null, updatedAt:r.updated_at || null });
  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 });
  // acceptDetail (Part/Serial nhập ở Biên bản nghiệm thu) · detail/showDetail (mô tả dưới tên hàng):
  // phải map ĐỦ ở CẢ 2 chiều (mapItem + upsertContract) — thiếu 1 chiều là mất dữ liệu lúc tải lại (0051).
  const mapItem     = (r) => ({ name:r.name, unit:r.unit, qty:Number(r.qty), price:Number(r.price), vat:Number(r.vat), detail:r.detail || '', showDetail:!!r.show_detail, acceptDetail:r.accept_detail || '' });
  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, contractNo:r.contract_no || '', direction:r.direction || 'sell', 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, billingCycle:r.billing_cycle || 'once', costContracts:r.cost_contracts || [], warranty:r.warranty || null, acceptedDate:r.accepted_date || null, revisions:r.revisions || [], docOverrides:r.doc_overrides || {}, createdAt:r.created_at || null, updatedAt:r.updated_at || null });
  const mapVendor   = (r) => ({ code:r.code, name:r.name, shortName:r.short_name, mst:r.mst, field:r.field, phone:r.phone || '', email:r.email || '', address:r.address || '', owner:r.owner, status:r.status });
  const mapDeal     = (r) => ({ code:r.code, name:r.name, endCustomer:r.end_customer || '', status:r.status, note:r.note || '', contracts:r.contracts || [] });
  const mapTask     = (r) => ({ id:r.code, title:r.title, status:r.status, priority:r.priority, assignee:r.assignee || '', dueDate:r.due_date || '', customer:r.customer || null, desc:r.description || '' });
  const mapTicket   = (r) => ({ code:r.code, customer:r.customer || '', contract:r.contract || null, contact:r.contact || null, channel:r.channel, category:r.category, priority:r.priority, status:r.status, title:r.title, desc:r.descr || '', assignee:r.assignee || '', createdDate:r.created_date, updatedDate:r.updated_date, interactions:r.interactions || [], result:r.result || null, report:r.report || null });
  const mapPost     = (r) => ({ id:r.id, slug:r.slug, title:r.title, excerpt:r.excerpt || '', content:r.content || '', coverUrl:r.cover_url || '', category:r.category || '', status:r.status, publishedAt:r.published_at || null, authorName:r.author_name || '', createdAt:r.created_at || null, updatedAt:r.updated_at || null });
  // cost tách sang bảng product_costs (RLS theo can_view_cost) — nhân viên không đủ quyền nhận
  // embed rỗng → cost 0. Embed to-many trả mảng (lấy phần tử đầu).
  const mapProduct  = (r) => { const pc = Array.isArray(r.product_costs) ? r.product_costs[0] : r.product_costs; return ({ code:r.code, sku:r.sku || '', name:r.name, category:r.category, unit:r.unit, vat:Number(r.vat), cost:Number(pc && pc.cost) || 0, vendor:r.vendor || '', status:r.status, desc:r.descr || '', match:r.match || [], priceHistory:r.price_history || [], itemType:r.item_type || 'product', stockTracked:!!r.stock_tracked, minStock:Number(r.min_stock) || 0, createdAt:r.created_at || null, updatedAt:r.updated_at || null }); };
  const mapMove     = (r) => ({ id:r.id, item:r.item, type:r.type, qty:Number(r.qty), date:r.date, refType:r.ref_type || '', refCode:r.ref_code || '', note:r.note || '', by:r.by || '' });
  const mapIssue    = (r) => ({ code:r.code, contract:r.contract || '', customer:r.customer || '', status:r.status, lines:r.lines || [], approvals:r.approvals || [], note:r.note || '', createdBy:r.created_by || '', createdDate:r.created_date, issuedDate:r.issued_date || null });
  const mapReceipt  = (r) => ({ code:r.code, source:r.source, buyContract:r.buy_contract || '', vendor:r.vendor || '', lines:r.lines || [], note:r.note || '', date:r.date, by:r.by || '' });
  const mapQuote    = (r) => ({ code:r.code, direction:r.direction || 'sell', customer:r.customer || '', rep:r.rep || null, owner:r.owner || '', issueDate:r.issue_date, validDays:r.valid_days, validUntil:r.valid_until, status:r.status, discountPct:Number(r.discount_pct) || 0, notes:r.notes || '', sentDate:r.sent_date || null, sentTo:r.sent_to || [], convertedTo:r.converted_to || null, items:r.items || [], history:r.history || [], confirmToken:r.confirm_token || null, confirmedAt:r.confirmed_at || null, confirmation:r.confirmation || null, schedule:r.schedule || [], acceptedDate:r.accepted_date || null });
  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, sentBy:r.sent_by || '' });
  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, address:c.address || null, 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 });
  // billing_cycle chỉ gửi khi HĐ có chọn chu kỳ (≠ once) — chưa chạy 0038 thì HĐ thường vẫn lưu được.
  const unContract = (c) => ({ code:c.code, contract_no:c.contractNo || null, direction:c.direction || 'sell', 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, cost_contracts:c.costContracts || [], warranty:c.warranty || null, accepted_date:c.acceptedDate || null, revisions:c.revisions || [], doc_overrides:c.docOverrides || {}, ...(c.billingCycle && c.billingCycle !== 'once' ? { billing_cycle:c.billingCycle } : {}) });
  const unVendor   = (v) => ({ code:v.code, name:v.name, short_name:v.shortName, mst:v.mst, field:v.field, phone:v.phone || null, email:v.email || null, address:v.address || null, owner:v.owner, status:v.status });
  const unDeal     = (d) => ({ code:d.code, name:d.name, end_customer:d.endCustomer || null, status:d.status, note:d.note || null, contracts:d.contracts || [] });
  const unTask     = (t) => ({ code:t.id, title:t.title, status:t.status, priority:t.priority, assignee:t.assignee || null, due_date:t.dueDate || null, customer:t.customer || null, description:t.desc || null });
  const unTicket   = (t) => ({ code:t.code, customer:t.customer || null, contract:t.contract || null, contact:t.contact || null, channel:t.channel, category:t.category, priority:t.priority, status:t.status, title:t.title, descr:t.desc || null, assignee:t.assignee || null, created_date:t.createdDate, updated_date:t.updatedDate, interactions:t.interactions || [], result:t.result || null, report:t.report || null });
  const unPost     = (w) => ({ id:w.id, slug:w.slug, title:w.title, excerpt:w.excerpt || null, content:w.content || null, cover_url:w.coverUrl || null, category:w.category || null, status:w.status || 'draft', published_at:w.publishedAt || null, author_name:w.authorName || null });
  // KHÔNG gửi cost vào product_products (cột đã dời sang product_costs từ 0042) → ghi cost riêng.
  const unProduct  = (pr) => ({ code:pr.code, sku:pr.sku || null, name:pr.name, category:pr.category || null, unit:pr.unit || null, vat:pr.vat ?? 8, vendor:pr.vendor || null, status:pr.status || 'active', descr:pr.desc || null, match:pr.match || [], price_history:pr.priceHistory || [], item_type:pr.itemType || 'product', stock_tracked:!!pr.stockTracked, min_stock:Number(pr.minStock) || 0 });
  const unMove     = (m) => ({ id:m.id, item:m.item, type:m.type, qty:Number(m.qty), date:m.date, ref_type:m.refType || null, ref_code:m.refCode || null, note:m.note || null, by:m.by || null });
  const unIssue    = (i) => ({ code:i.code, contract:i.contract || null, customer:i.customer || null, status:i.status || 'draft', lines:i.lines || [], approvals:i.approvals || [], note:i.note || null, created_by:i.createdBy || null, created_date:i.createdDate, issued_date:i.issuedDate || null });
  const unReceipt  = (r) => ({ code:r.code, source:r.source || 'manual', buy_contract:r.buyContract || null, vendor:r.vendor || null, lines:r.lines || [], note:r.note || null, date:r.date, by:r.by || null });
  // KHÔNG ghi confirmed_at/confirmation ở đây — chỉ RPC quote_confirm (khách) được ghi; upsert nội bộ bỏ qua → không đè xác nhận của khách.
  // direction cần cột từ 0036 — BẮT BUỘC chạy migration trước khi lưu/sửa báo giá (giống gotcha 0010 của HĐ).
  const unQuote    = (q) => ({ code:q.code, direction:q.direction === 'buy' ? 'buy' : 'sell', customer:q.customer || null, rep:q.rep || null, owner:q.owner || null, issue_date:q.issueDate || null, valid_days:q.validDays || null, valid_until:q.validUntil || null, status:q.status || 'draft', discount_pct:q.discountPct || 0, notes:q.notes || null, sent_date:q.sentDate || null, sent_to:q.sentTo || [], converted_to:q.convertedTo || null, items:q.items || [], history:q.history || [], confirm_token:q.confirmToken || null, schedule:q.schedule || [], accepted_date:q.acceptedDate || null });
  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', sent_by:e.sentBy || null });
  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 };
  }

  // map role DB (employee|manager|admin) → role app (module Người dùng dùng id 'staff')
  const mapRole = (r) => r === 'employee' ? 'staff' : (r || 'staff');
  async function listUsers() {
    if (!window.SB_READY) return null;
    const { data, error } = await sb.from('profiles').select('*').order('full_name');
    if (error || !data) return null;
    return data.map(r => ({
      id:r.id, name:r.full_name || r.email, email:r.email,
      role:mapRole(r.role), status:r.status || 'active', dept:r.dept || '',
      last:'', joined:r.created_at ? String(r.created_at).slice(0, 10) : '',
    }));
  }

  // Đổi vai trò user (ghi vào profiles.role) — cần RLS cho admin (migration 0007).
  // .select() để biết update có THỰC SỰ đổi dòng nào không: RLS chặn → 0 dòng, KHÔNG báo error
  // (postgREST coi update khớp 0 dòng là thành công) → ta tự ném lỗi để UI báo rõ.
  async function updateUserRole(id, role) {
    if (!window.SB_READY) return;
    const { data, error } = await sb.from('profiles').update({ role }).eq('id', id).select('id');
    if (error) throw error;
    if (!data || !data.length) throw new Error('RLS chặn cập nhật: tài khoản của bạn phải có role = "admin" trong profiles và đã chạy migration 0007.');
  }

  // Sửa hồ sơ user (full_name/dept/status) — admin sửa người khác cần RLS 0007 + cột từ 0013.
  async function updateProfile(id, patch) {
    if (!window.SB_READY) return;
    const row = {};
    if (patch.name !== undefined)   row.full_name = patch.name;
    if (patch.dept !== undefined)   row.dept = patch.dept || null;
    if (patch.status !== undefined) row.status = patch.status;
    if (patch.meta !== undefined)   row.meta = patch.meta;
    if (!Object.keys(row).length) return;
    const { data, error } = await sb.from('profiles').update(row).eq('id', id).select('id');
    if (error) throw error;
    if (!data || !data.length) throw new Error('RLS chặn cập nhật hồ sơ: cần role admin (migration 0007) và đã chạy 0013 (cột dept/status).');
  }

  // Quyền theo module CỦA CHÍNH MÌNH (core_module_access) → UI gate cho khớp RLS.
  // Đọc được nhờ policy select = is_staff() (0038). Trả { <module_key>: <grp> }; null nếu chưa migrate/lỗi
  // → caller giữ mặc định (không khoá nhầm ai).
  async function loadMyModules(uid) {
    if (!window.SB_READY || !uid) return null;
    const { data, error } = await sb.from('core_module_access').select('module_key,grp').eq('user_id', uid);
    if (error) return null;
    const m = {};
    (data || []).forEach(r => { if (r.module_key) m[r.module_key] = r.grp || 'member'; });
    return m;
  }

  async function myProfile(uid) {
    if (!window.SB_READY || !uid) return null;
    // thử kèm meta (0030) + dept (0013); fallback dần nếu cột chưa migrate (không vỡ đăng nhập).
    let { data, error } = await sb.from('profiles').select('id,email,full_name,role,dept,meta').eq('id', uid).maybeSingle();
    if (error) { const r = await sb.from('profiles').select('id,email,full_name,role,dept').eq('id', uid).maybeSingle(); data = r.data; error = r.error; }
    if (error) { const r = await sb.from('profiles').select('id,email,full_name,role').eq('id', uid).maybeSingle(); data = r.data; error = r.error; }
    if (error || !data) return null;
    return { id:data.id, name:data.full_name || data.email, email:data.email, role:data.role || 'staff', dept:data.dept || '', title:'', meta:data.meta || {} };
  }

  // Tải riêng (không nằm trong loadAll) — bảng có thể CHƯA migrate → trả null, app giữ mock.
  async function loadVendors() {
    if (!window.SB_READY) return null;
    const { data, error } = await sb.from('vendor_vendors').select('*').order('code');
    if (error || !data) return null;
    return data.map(mapVendor);
  }
  async function loadDeals() {
    if (!window.SB_READY) return null;
    const { data, error } = await sb.from('deal_deals').select('*').order('code');
    if (error || !data) return null;
    return data.map(mapDeal);
  }
  async function loadTasks() {
    if (!window.SB_READY) return null;
    const { data, error } = await sb.from('task_tasks').select('*').order('code', { ascending:false });
    if (error || !data) return null;
    return data.map(mapTask);
  }
  async function loadTickets() {
    if (!window.SB_READY) return null;
    const { data, error } = await sb.from('support_tickets').select('*').order('code', { ascending:false });
    if (error || !data) return null;
    return data.map(mapTicket);
  }
  async function loadPosts() {
    if (!window.SB_READY) return null;
    const { data, error } = await sb.from('cms_posts').select('*').order('created_at', { ascending:false });
    if (error || !data) return null;   // bảng chưa migrate → giữ local (không hỏng app)
    return data.map(mapPost);
  }
  // Nội dung website (cms_settings key 'site') — null = chưa migrate 0034 / lỗi; {} = chưa cấu hình.
  async function loadSiteContent() {
    if (!window.SB_READY) return null;
    const { data, error } = await sb.from('cms_settings').select('value').eq('key', 'site').maybeSingle();
    if (error) return null;
    return data ? (data.value || {}) : {};
  }
  async function saveSiteContent(value) { const { error } = await sb.from('cms_settings').upsert({ key:'site', value }); if (error) throw error; }
  // Liên hệ từ form website (cms_contacts, 0035) — null = chưa migrate/lỗi.
  async function loadContactMsgs() {
    if (!window.SB_READY) return null;
    const { data, error } = await sb.from('cms_contacts').select('*').order('created_at', { ascending:false }).limit(200);
    if (error || !data) return null;
    return data.map(r => ({ id:r.id, name:r.name, email:r.email, message:r.message, read:!!r.read, createdAt:r.created_at }));
  }
  async function markContactRead(id, read) { const { error } = await sb.from('cms_contacts').update({ read }).eq('id', id); if (error) throw error; }
  async function deleteContactMsg(id)     { const { error } = await sb.from('cms_contacts').delete().eq('id', id); if (error) throw error; }
  async function loadMoves() {
    if (!window.SB_READY) return null;
    const { data, error } = await sb.from('stock_moves').select('*').order('date', { ascending:false });
    if (error || !data) return null;
    return data.map(mapMove);
  }
  async function loadIssues() {
    if (!window.SB_READY) return null;
    const { data, error } = await sb.from('stock_issues').select('*').order('code', { ascending:false });
    if (error || !data) return null;
    return data.map(mapIssue);
  }
  async function loadReceipts() {
    if (!window.SB_READY) return null;
    const { data, error } = await sb.from('stock_receipts').select('*').order('code', { ascending:false });
    if (error || !data) return null;
    return data.map(mapReceipt);
  }
  async function insertMove(m)    { const { error } = await sb.from('stock_moves').insert(unMove(m)); if (error) throw error; }
  async function insertMoves(ms)  { if (!ms || !ms.length) return; const { error } = await sb.from('stock_moves').insert(ms.map(unMove)); if (error) throw error; }
  async function deleteMovesByRef(refCode) { const { error } = await sb.from('stock_moves').delete().eq('ref_code', refCode); if (error) throw error; }
  async function upsertIssue(i)   { const { error } = await sb.from('stock_issues').upsert(unIssue(i)); if (error) throw error; }
  async function deleteIssue(code){ const { error } = await sb.from('stock_issues').delete().eq('code', code); if (error) throw error; }
  async function upsertReceipt(r) { const { error } = await sb.from('stock_receipts').upsert(unReceipt(r)); if (error) throw error; }
  async function loadProducts() {
    if (!window.SB_READY) return null;
    const { data, error } = await sb.from('product_products').select('*, product_costs(cost)').order('code');
    if (error || !data) return null;
    return data.map(mapProduct);
  }
  async function loadQuotes() {
    if (!window.SB_READY) return null;
    const { data, error } = await sb.from('quote_quotes').select('*').order('code', { ascending:false });
    if (error || !data) return null;
    return data.map(mapQuote);
  }
  // Danh mục cấu hình (catalog). String-catalog → mảng nhãn; còn lại → {id,vi,en}.
  const STRING_CATS = ['industries', 'vendorFields', 'units', 'hrmRelations'];   // phải khớp CATALOG_STRING (main.jsx)
  async function loadCatalogs() {
    if (!window.SB_READY) return null;
    const { data, error } = await sb.from('app_catalogs').select('*').order('catalog').order('sort');
    if (error || !data) return null;
    const out = {};
    data.forEach(r => { (out[r.catalog] = out[r.catalog] || []).push(STRING_CATS.includes(r.catalog) ? r.label_vi : { id:r.item_id, vi:r.label_vi, en:r.label_en || r.label_vi }); });
    return out;
  }
  async function insertCatalogItem(row) { const { error } = await sb.from('app_catalogs').upsert(row, { onConflict:'catalog,item_id' }); if (error) throw error; }
  async function updateCatalogItem(catalog, item_id, patch) {
    const row = {}; if (patch.vi !== undefined) row.label_vi = patch.vi; if (patch.en !== undefined) row.label_en = patch.en;
    const { data, error } = await sb.from('app_catalogs').update(row).eq('catalog', catalog).eq('item_id', item_id).select('id');
    if (error) throw error; if (!data || !data.length) throw new Error('RLS chặn cập nhật danh mục: cần role admin + đã chạy 0016.');
  }
  async function deleteCatalogItem(catalog, item_id) {
    const { data, error } = await sb.from('app_catalogs').delete().eq('catalog', catalog).eq('item_id', item_id).select('id');
    if (error) throw error; if (!data || !data.length) throw new Error('RLS chặn xóa danh mục: cần role admin + đã chạy 0016.');
  }
  // Cấu hình key-value (app_settings) — tách riêng, null khi chưa migrate → app dùng mặc định.
  async function loadSettings() {
    if (!window.SB_READY) return null;
    const { data, error } = await sb.from('app_settings').select('*');
    if (error || !data) return null;
    const out = {}; data.forEach(r => { out[r.key] = r.value; }); return out;
  }
  async function saveSetting(key, value) {
    const { data, error } = await sb.from('app_settings').upsert({ key, value }).select('key');
    if (error) throw error; if (!data || !data.length) throw new Error('RLS chặn lưu cấu hình: cần role admin + đã chạy 0017.');
  }

  /* ---- File trên Supabase Storage. Mặc định bucket 'attachments' (HĐ/báo giá — MỌI staff đọc được);
         truyền `bucket` để dùng bucket khác, vd 'hrm-docs' (hồ sơ nhân sự — chỉ HR + chính chủ, xem 0050). ---- */
  const FILE_BUCKET = 'attachments';
  async function storageList(prefix, bucket) {
    if (!window.SB_READY) return [];
    const { data, error } = await sb.storage.from(bucket || FILE_BUCKET).list(prefix, { limit:200, sortBy:{ column:'created_at', order:'desc' } });
    if (error || !data) return [];
    return data.filter(o => o.id || o.metadata).map(o => ({
      name:o.name, path:prefix + '/' + o.name,
      size:(o.metadata && o.metadata.size) || 0, mimetype:(o.metadata && o.metadata.mimetype) || '',
      createdAt:o.created_at || null,
    }));
  }
  async function storageUpload(path, file, bucket) {
    if (!window.SB_READY) throw new Error('offline');
    const { error } = await sb.storage.from(bucket || FILE_BUCKET).upload(path, file, { upsert:false, cacheControl:'3600' });
    if (error) throw error;
  }
  async function storageSignedUrl(path, expiresIn, bucket) {
    if (!window.SB_READY) return null;
    const { data, error } = await sb.storage.from(bucket || FILE_BUCKET).createSignedUrl(path, expiresIn || 120);
    if (error || !data) return null;
    return data.signedUrl;
  }
  async function storageRemove(path, bucket) {
    if (!window.SB_READY) throw new Error('offline');
    const { error } = await sb.storage.from(bucket || FILE_BUCKET).remove([path]);
    if (error) throw error;
  }

  /* ---- 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,
      detail:(i.detail || '').trim() || null, show_detail:!!i.showDetail, accept_detail:(i.acceptDetail || '').trim() || null }));
    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 insertVendor(v) { const { error } = await sb.from('vendor_vendors').insert(unVendor(v)); if (error) throw error; }
  async function updateVendor(v)  { const { error } = await sb.from('vendor_vendors').update(unVendor(v)).eq('code', v.code); if (error) throw error; }
  async function deleteVendor(code) { const { error } = await sb.from('vendor_vendors').delete().eq('code', code); if (error) throw error; }
  async function upsertDeal(d)    { const { error } = await sb.from('deal_deals').upsert(unDeal(d)); if (error) throw error; }
  async function deleteDeal(code) { const { error } = await sb.from('deal_deals').delete().eq('code', code); if (error) throw error; }
  async function upsertTask(t)    { const { error } = await sb.from('task_tasks').upsert(unTask(t)); if (error) throw error; }
  async function deleteTask(code) { const { error } = await sb.from('task_tasks').delete().eq('code', code); if (error) throw error; }
  async function upsertTicket(t)  { const { error } = await sb.from('support_tickets').upsert(unTicket(t)); if (error) throw error; }
  async function deleteTicket(code) { const { error } = await sb.from('support_tickets').delete().eq('code', code); if (error) throw error; }
  async function upsertPost(w)    { const { error } = await sb.from('cms_posts').upsert(unPost(w)); if (error) throw error; }
  async function deletePost(id)   { const { error } = await sb.from('cms_posts').delete().eq('id', id); if (error) throw error; }
  // writeCost: chỉ ghi giá vốn sang product_costs khi user đủ quyền (canViewCost) — nhân viên
  // KHÔNG truyền cờ này → không đụng product_costs (tránh RLS lỗi + tránh ghi đè cost thật bằng 0).
  async function upsertProduct(pr, writeCost) {
    const { error } = await sb.from('product_products').upsert(unProduct(pr)); if (error) throw error;
    if (writeCost) { const { error:e2 } = await sb.from('product_costs').upsert({ code:pr.code, cost:pr.cost != null ? Number(pr.cost) : null }); if (e2) throw e2; }
  }
  async function deleteProduct(code){ const { error } = await sb.from('product_products').delete().eq('code', code); if (error) throw error; }
  async function upsertQuote(q)     { const { error } = await sb.from('quote_quotes').upsert(unQuote(q)); if (error) throw error; }
  async function deleteQuote(code)  { const { error } = await sb.from('quote_quotes').delete().eq('code', code); 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 deleteTemplate(id)  { const { error } = await sb.from('email_templates').delete().eq('id', id); 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; }

  // Thông báo (core_notifications — 0046): quét + đọc danh sách của tôi + đánh dấu đọc.
  async function notifScan(){ if (!window.SB_READY) return; try { await sb.rpc('notif_scan'); } catch (_){} }   // bảng chưa migrate → im lặng, app dùng mock
  async function loadNotifs(){ if (!window.SB_READY) return null; const { data, error } = await sb.from('core_notifications').select('*').order('created_at', { ascending:false }).limit(100); if (error || !data) return null; return data; }
  async function markNotifReadDb(id){ const { error } = await sb.from('core_notifications').update({ read_at:new Date().toISOString() }).eq('id', id); if (error) throw error; }
  async function markAllNotifsReadDb(){ const { error } = await sb.from('core_notifications').update({ read_at:new Date().toISOString() }).is('read_at', null); if (error) throw error; }

  // Nhật ký hệ thống (core_audit_log — 0044). Lọc server-side + phân trang; NÉM lỗi để UI phân biệt "chưa migrate".
  async function loadAudit(opts) {
    if (!window.SB_READY) return null;
    opts = opts || {};
    const from = opts.offset || 0, size = opts.limit || 50;
    let q = sb.from('core_audit_log').select('*').order('at', { ascending:false }).range(from, from + size - 1);
    if (opts.action) q = q.eq('action', opts.action);
    if (opts.tbl) q = q.eq('tbl', opts.tbl);
    if (opts.q) { const s = String(opts.q).replace(/[%,()]/g, ''); if (s) q = q.or('actor_email.ilike.%' + s + '%,record_code.ilike.%' + s + '%'); }
    const { data, error } = await q;
    if (error) throw error;
    return data || [];
  }

  /* ---- HRM (0047) — Nhân sự & Chấm công. Map per-bảng + loader/mutator GENERIC theo key. ---- */
  const mapHrmEmployee = (r) => ({ id:r.id, userId:r.user_id || null, fullName:r.full_name, gender:r.gender || '', birthDate:r.birth_date || '', nationality:r.nationality || '', address:r.address || '', idNumber:r.id_number || '', idIssueDate:r.id_issue_date || '', education:r.education || '', skillLevel:r.skill_level || '', position:r.position || '', dept:r.dept || '', startDate:r.start_date, endDate:r.end_date || null, endReason:r.end_reason || '', endType:r.end_type || null, siCode:r.si_code || '', siJoinDate:r.si_join_date || '', salary:r.salary || {}, events:r.events || [], leaveAdjust:Number(r.leave_adjust) || 0, otCapYear:r.ot_cap_year || 200, status:r.status, email:r.email || '', phone:r.phone || '', note:r.note || '', photo:r.photo || '' });
  const unHrmEmployee  = (e) => ({ id:e.id, user_id:e.userId || null, full_name:e.fullName, gender:e.gender || null, birth_date:e.birthDate || null, nationality:e.nationality || null, address:e.address || null, id_number:e.idNumber || null, id_issue_date:e.idIssueDate || null, education:e.education || null, skill_level:e.skillLevel || null, position:e.position || null, dept:e.dept || null, start_date:e.startDate, end_date:e.endDate || null, end_reason:e.endReason || null, end_type:e.endType || null, si_code:e.siCode || null, si_join_date:e.siJoinDate || null, salary:e.salary || {}, events:e.events || [], leave_adjust:+e.leaveAdjust || 0, ot_cap_year:+e.otCapYear || 200, status:e.status || 'active', email:e.email || null, phone:e.phone || null, note:e.note || null, photo:e.photo || null });
  const mapHrmContract = (r) => ({ id:r.id, employeeId:r.employee_id, parentId:r.parent_id || null, contractNo:r.contract_no || '', kind:r.kind, signDate:r.sign_date || '', startDate:r.start_date || '', endDate:r.end_date || null, position:r.position || '', dept:r.dept || '', salary:r.salary || {}, status:r.status, note:r.note || '' });
  const unHrmContract  = (c) => ({ id:c.id, employee_id:c.employeeId, parent_id:c.parentId || null, contract_no:c.contractNo || null, kind:c.kind || 'fixed', sign_date:c.signDate || null, start_date:c.startDate || null, end_date:c.endDate || null, position:c.position || null, dept:c.dept || null, salary:c.salary || {}, status:c.status || 'active', note:c.note || null });
  const mapHrmCareer   = (r) => ({ id:r.id, employeeId:r.employee_id, fromDate:r.from_date, toDate:r.to_date || null, position:r.position || '', dept:r.dept || '', contractId:r.contract_id || null, note:r.note || '' });
  const unHrmCareer    = (c) => ({ id:c.id, employee_id:c.employeeId, from_date:c.fromDate, to_date:c.toDate || null, position:c.position || null, dept:c.dept || null, contract_id:c.contractId || null, note:c.note || null });
  const mapHrmTraining = (r) => ({ id:r.id, employeeId:r.employee_id, name:r.name, org:r.org || '', fromDate:r.from_date || '', toDate:r.to_date || '', cost:r.cost != null ? Number(r.cost) : null, hours:r.hours != null ? Number(r.hours) : null, result:r.result || '', note:r.note || '' });
  const unHrmTraining  = (t) => ({ id:t.id, employee_id:t.employeeId, name:t.name, org:t.org || null, from_date:t.fromDate || null, to_date:t.toDate || null, cost:t.cost != null && t.cost !== '' ? +t.cost : null, hours:t.hours != null && t.hours !== '' ? +t.hours : null, result:t.result || null, note:t.note || null });
  const mapHrmReview   = (r) => ({ id:r.id, employeeId:r.employee_id, period:r.period, reviewDate:r.review_date, kind:r.kind || 'kpi', score:r.score != null ? Number(r.score) : null, rating:r.rating || 'meet', goals:r.goals || [], reviewerId:r.reviewer_id || null, note:r.note || '' });
  const unHrmReview    = (v) => ({ id:v.id, employee_id:v.employeeId, period:v.period, review_date:v.reviewDate, kind:v.kind || 'kpi', score:v.score != null && v.score !== '' ? +v.score : null, rating:v.rating || 'meet', goals:v.goals || [], reviewer_id:v.reviewerId || null, note:v.note || null });
  const mapHrmDecision = (r) => ({ id:r.id, employeeId:r.employee_id, kind:r.kind, decisionNo:r.decision_no || '', decisionDate:r.decision_date || '', content:r.content || '', amount:r.amount != null ? Number(r.amount) : null, note:r.note || '' });
  const unHrmDecision  = (d) => ({ id:d.id, employee_id:d.employeeId, kind:d.kind || 'reward', decision_no:d.decisionNo || null, decision_date:d.decisionDate || null, content:d.content || null, amount:d.amount != null && d.amount !== '' ? +d.amount : null, note:d.note || null });
  const mapHrmDependent = (r) => ({ id:r.id, employeeId:r.employee_id, fullName:r.full_name, relation:r.relation || '', birthDate:r.birth_date || '', phone:r.phone || '', note:r.note || '' });
  const unHrmDependent  = (d) => ({ id:d.id, employee_id:d.employeeId, full_name:d.fullName, relation:d.relation || null, birth_date:d.birthDate || null, phone:d.phone || null, note:d.note || null });
  const mapHrmBank = (r) => ({ id:r.id, employeeId:r.employee_id, bankName:r.bank_name, branch:r.branch || '', accountNo:r.account_no, accountHolder:r.account_holder || '', isPrimary:!!r.is_primary, note:r.note || '' });
  const unHrmBank  = (b) => ({ id:b.id, employee_id:b.employeeId, bank_name:b.bankName, branch:b.branch || null, account_no:b.accountNo, account_holder:b.accountHolder || null, is_primary:!!b.isPrimary, note:b.note || null });
  const mapHrmDoc  = (r) => ({ id:r.id, employeeId:r.employee_id, docType:r.doc_type || 'khac', name:r.name, path:r.path, size:r.size != null ? Number(r.size) : 0, mime:r.mime || '', issuedDate:r.issued_date || '', uploadedBy:r.uploaded_by || null, note:r.note || '', createdAt:r.created_at || null });
  const unHrmDoc   = (d) => ({ id:d.id, employee_id:d.employeeId, doc_type:d.docType || 'khac', name:d.name, path:d.path, size:d.size != null ? +d.size : null, mime:d.mime || null, issued_date:d.issuedDate || null, uploaded_by:d.uploadedBy || null, note:d.note || null });
  const mapHrmAttendance = (r) => ({ id:r.id, employeeId:r.employee_id, workDate:r.work_date, checkIn:r.check_in || null, checkOut:r.check_out || null, source:r.source || 'self', editedBy:r.edited_by || null, note:r.note || '' });
  const unHrmAttendance  = (a) => ({ id:a.id, employee_id:a.employeeId, work_date:a.workDate, check_in:a.checkIn || null, check_out:a.checkOut || null, source:a.source || 'self', note:a.note || null });
  const mapHrmRequest  = (r) => ({ id:r.id, employeeId:r.employee_id, kind:r.kind, leaveType:r.leave_type || null, startDate:r.start_date, endDate:r.end_date, days:r.days != null ? Number(r.days) : null, hours:r.hours != null ? Number(r.hours) : null, startTime:r.start_time ? String(r.start_time).slice(0, 5) : null, endTime:r.end_time ? String(r.end_time).slice(0, 5) : null, isNight:!!r.is_night, consent:r.consent !== false, reason:r.reason || '', status:r.status, decidedBy:r.decided_by || null, decidedAt:r.decided_at || null, decidedNote:r.decided_note || '' });
  const unHrmRequest   = (q) => ({ id:q.id, employee_id:q.employeeId, kind:q.kind || 'leave', leave_type:q.leaveType || null, start_date:q.startDate, end_date:q.endDate, days:q.days != null ? +q.days : null, hours:q.hours != null ? +q.hours : null, start_time:q.startTime || null, end_time:q.endTime || null, is_night:!!q.isNight, consent:q.consent !== false, reason:q.reason || null, status:q.status || 'pending', decided_by:q.decidedBy || null, decided_at:q.decidedAt || null, decided_note:q.decidedNote || null });
  const mapHrmHoliday  = (r) => ({ id:r.id, date:r.date, name:r.name || {}, paid:r.paid !== false });
  const unHrmHoliday   = (h) => ({ id:h.id, date:h.date, name:h.name || {}, paid:h.paid !== false });
  const mapHrmPayrun   = (r) => ({ id:r.id, period:r.period, region:r.region || 1, status:r.status || 'draft', config:r.config || {}, note:r.note || '', createdBy:r.created_by || null });
  const unHrmPayrun    = (p) => ({ id:p.id, period:p.period, region:+p.region || 1, status:p.status || 'draft', config:p.config || {}, note:p.note || null, created_by:p.createdBy || null });
  const mapHrmPayslip  = (r) => ({ id:r.id, payrunId:r.payrun_id, employeeId:r.employee_id, period:r.period, finalized:!!r.finalized, detail:r.detail || {} });
  const unHrmPayslip   = (s) => ({ id:s.id, payrun_id:s.payrunId, employee_id:s.employeeId, period:s.period, finalized:!!s.finalized, detail:s.detail || {} });

  const HRM_T = {
    employees: { table:'hrm_employees',  map:mapHrmEmployee,  un:unHrmEmployee,  q:(s) => s.order('id') },
    contracts: { table:'hrm_contracts',  map:mapHrmContract,  un:unHrmContract,  q:(s) => s.order('start_date') },
    careers:   { table:'hrm_careers',    map:mapHrmCareer,    un:unHrmCareer,    q:(s) => s.order('from_date') },
    trainings: { table:'hrm_trainings',  map:mapHrmTraining,  un:unHrmTraining,  q:(s) => s.order('from_date', { ascending:false }) },
    decisions: { table:'hrm_decisions',  map:mapHrmDecision,  un:unHrmDecision,  q:(s) => s.order('decision_date', { ascending:false }) },
    dependents:{ table:'hrm_dependents', map:mapHrmDependent, un:unHrmDependent, q:(s) => s.order('id') },
    bankAccounts:{ table:'hrm_bank_accounts', map:mapHrmBank, un:unHrmBank, q:(s) => s.order('is_primary', { ascending:false }).order('id') },
    documents: { table:'hrm_documents',  map:mapHrmDoc,       un:unHrmDoc,       q:(s) => s.order('created_at', { ascending:false }) },
    reviews:   { table:'hrm_reviews',    map:mapHrmReview,    un:unHrmReview,    q:(s) => s.order('review_date', { ascending:false }) },
    attendance:{ table:'hrm_attendance', map:mapHrmAttendance, un:unHrmAttendance, q:(s) => s.order('work_date', { ascending:false }).limit(1200) },
    requests:  { table:'hrm_requests',   map:mapHrmRequest,   un:unHrmRequest,   q:(s) => s.order('id', { ascending:false }) },
    holidays:  { table:'hrm_holidays',   map:mapHrmHoliday,   un:unHrmHoliday,   q:(s) => s.order('date') },
    payruns:   { table:'hrm_payruns',    map:mapHrmPayrun,    un:unHrmPayrun,    q:(s) => s.order('period', { ascending:false }) },
    payslips:  { table:'hrm_payslips',   map:mapHrmPayslip,   un:unHrmPayslip,   q:(s) => s.order('period', { ascending:false }).limit(2000) },
  };
  // Nạp CẢ bảng của module song song — từng bảng chịu-lỗi riêng (chưa migrate 0047/0049/0050 → key = null, app giữ mock).
  async function loadHrm() {
    if (!window.SB_READY) return null;
    const keys = Object.keys(HRM_T);
    const res = await Promise.all(keys.map(k => HRM_T[k].q(sb.from(HRM_T[k].table).select('*'))));
    const out = {};
    keys.forEach((k, i) => { const r = res[i]; out[k] = (r.error || !r.data) ? null : r.data.map(HRM_T[k].map); });
    return out;
  }
  // Danh bạ rút gọn (bảng hrm_directory — mọi staff đọc được, không lộ lương/CCCD; đồng bộ từ
  // hrm_employees bằng trigger, xem 0054). Trước 0054 là view security definer.
  async function loadHrmDir() {
    if (!window.SB_READY) return null;
    const { data, error } = await sb.from('hrm_directory').select('*').order('id');
    if (error || !data) return null;
    return data.map(r => ({ id:r.id, fullName:r.full_name, dept:r.dept || '', position:r.position || '', status:r.status,
      userId:r.user_id || null, email:r.email || '', phone:r.phone || '' }));
  }
  async function hrmUpsert(key, row) { const t = HRM_T[key]; const { error } = await sb.from(t.table).upsert(t.un(row)); if (error) throw error; }
  async function hrmDelete(key, id)  { const t = HRM_T[key]; const { error } = await sb.from(t.table).delete().eq('id', id); if (error) throw error; }

  window.DB = {
    loadAll, loadVendors, loadDeals, loadTasks, loadTickets, loadPosts, loadMoves, loadIssues, loadReceipts, loadProducts, loadQuotes, loadCatalogs, loadSettings, myProfile, listUsers, updateUserRole, updateProfile,
    loadHrm, loadHrmDir, hrmUpsert, hrmDelete, loadMyModules,
    insertMove, insertMoves, deleteMovesByRef, upsertIssue, deleteIssue, upsertReceipt,
    upsertProduct, deleteProduct, upsertQuote, deleteQuote,
    insertCatalogItem, updateCatalogItem, deleteCatalogItem, saveSetting,
    storageList, storageUpload, storageSignedUrl, storageRemove,
    insertCustomer, updateCustomer, deleteCustomer,
    insertContact, updateContact, deleteContact, addTimeline,
    insertExpense, updateExpense, deleteExpense,
    upsertContract, deleteContract,
    insertVendor, updateVendor, deleteVendor,
    upsertDeal, deleteDeal,
    upsertTask, deleteTask,
    upsertTicket, deleteTicket, upsertPost, deletePost, loadSiteContent, saveSiteContent, loadContactMsgs, markContactRead, deleteContactMsg,
    insertSentEmail, upsertTemplate, deleteTemplate, saveSettings,
    loadAudit,
    notifScan, loadNotifs, markNotifReadDb, markAllNotifsReadDb,
  };
})();