/* global supabase */
// ============================================================
// سِنان كلينيك — Supabase Client & Data Service
// ============================================================

const { createClient } = window.supabase;

const SUPABASE_URL = 'https://hqvwnjvhxigovsiirytg.supabase.co';
const SUPABASE_KEY = 'sb_publishable_d54WUcQ-1pj_wN18rBodHQ_lwKLl1Bi';

const SB = createClient(SUPABASE_URL, SUPABASE_KEY);
const AUTH_DRAFT_KEY = 'senan.pending-signup';
const DEFAULT_CLINIC_NAME = 'عيادتي';
const DEFAULT_ADMIN_ROLE = 'مدير العيادة';
const PATIENT_ASSETS_BUCKET = 'patient-assets';

function normalizeEmail(email) {
  return String(email || '').trim().toLowerCase();
}

function savePendingSignupDraft(draft) {
  const email = normalizeEmail(draft?.email);
  if (!email) return;

  const payload = {
    clinicName: String(draft?.clinicName || '').trim(),
    adminName: String(draft?.adminName || '').trim(),
    email,
    savedAt: new Date().toISOString(),
  };

  try {
    window.localStorage.setItem(AUTH_DRAFT_KEY, JSON.stringify(payload));
  } catch (error) {
    console.warn('[Senan] failed to save signup draft:', error);
  }
}

function getPendingSignupDraft(email) {
  try {
    const raw = window.localStorage.getItem(AUTH_DRAFT_KEY);
    if (!raw) return null;
    const draft = JSON.parse(raw);
    if (!draft?.email) return null;
    if (!email) return draft;
    return normalizeEmail(email) === normalizeEmail(draft.email) ? draft : null;
  } catch (error) {
    console.warn('[Senan] failed to read signup draft:', error);
    return null;
  }
}

function clearPendingSignupDraft(email) {
  try {
    const draft = getPendingSignupDraft();
    if (!draft) return;
    if (!email || normalizeEmail(email) === normalizeEmail(draft.email)) {
      window.localStorage.removeItem(AUTH_DRAFT_KEY);
    }
  } catch (error) {
    console.warn('[Senan] failed to clear signup draft:', error);
  }
}

function decodeInvoiceNotes(rawNotes) {
  const fallback = {
    paymentMethod: '',
    doctorId: null,
    doctorName: '',
    memo: '',
    discountPercent: 0,
    discountAmount: 0,
    subtotal: 0,
    taxPercent: 0,
    taxAmount: 0,
    total: 0,
  };

  if (rawNotes == null) return fallback;

  if (typeof rawNotes === 'object') {
    return {
      paymentMethod: String(rawNotes.paymentMethod || rawNotes.payMethod || '').trim(),
      doctorId: rawNotes.doctorId || rawNotes.doctor_id || null,
      doctorName: String(rawNotes.doctorName || rawNotes.doctor_name || '').trim(),
      memo: String(rawNotes.memo || rawNotes.notes || '').trim(),
      discountPercent: Number(rawNotes.discountPercent || rawNotes.discount_percent || 0) || 0,
      discountAmount: Number(rawNotes.discountAmount || rawNotes.discount_amount || 0) || 0,
      subtotal: Number(rawNotes.subtotal || 0) || 0,
      taxPercent: Number(rawNotes.taxPercent || rawNotes.tax_percent || 0) || 0,
      taxAmount: Number(rawNotes.taxAmount || rawNotes.tax_amount || 0) || 0,
      total: Number(rawNotes.total || 0) || 0,
    };
  }

  const raw = String(rawNotes).trim();
  if (!raw) return fallback;

  try {
    const parsed = JSON.parse(raw);
    if (parsed && typeof parsed === 'object' && !Array.isArray(parsed)) {
      return {
        paymentMethod: String(parsed.paymentMethod || parsed.payMethod || '').trim(),
        doctorId: parsed.doctorId || parsed.doctor_id || null,
        doctorName: String(parsed.doctorName || parsed.doctor_name || '').trim(),
        memo: String(parsed.memo || parsed.notes || '').trim(),
        discountPercent: Number(parsed.discountPercent || parsed.discount_percent || 0) || 0,
        discountAmount: Number(parsed.discountAmount || parsed.discount_amount || 0) || 0,
        subtotal: Number(parsed.subtotal || 0) || 0,
        taxPercent: Number(parsed.taxPercent || parsed.tax_percent || 0) || 0,
        taxAmount: Number(parsed.taxAmount || parsed.tax_amount || 0) || 0,
        total: Number(parsed.total || 0) || 0,
      };
    }
  } catch (error) {
    // Old invoices stored a plain payment method string; preserve it.
  }

  return {
    paymentMethod: raw,
    doctorId: null,
    doctorName: '',
    memo: '',
    discountPercent: 0,
    discountAmount: 0,
    subtotal: 0,
    taxPercent: 0,
    taxAmount: 0,
    total: 0,
  };
}

function encodeInvoiceNotes(meta = {}) {
  return JSON.stringify({
    paymentMethod: String(meta.paymentMethod || meta.payMethod || '').trim(),
    doctorId: meta.doctorId || meta.doctor_id || null,
    doctorName: String(meta.doctorName || meta.doctor_name || '').trim(),
    memo: String(meta.memo || meta.notes || '').trim(),
    discountPercent: Number(meta.discountPercent || meta.discount_percent || 0) || 0,
    discountAmount: Number(meta.discountAmount || meta.discount_amount || 0) || 0,
    subtotal: Number(meta.subtotal || 0) || 0,
    taxPercent: Number(meta.taxPercent || meta.tax_percent || 0) || 0,
    taxAmount: Number(meta.taxAmount || meta.tax_amount || 0) || 0,
    total: Number(meta.total || 0) || 0,
  });
}

function sanitizeStorageSegment(value, fallback = 'file') {
  const cleaned = String(value || '')
    .trim()
    .replace(/[^\w.\-]+/g, '-')
    .replace(/-+/g, '-')
    .replace(/^-|-$/g, '');
  return cleaned || fallback;
}

async function uploadPatientAsset(clinicId, patientId, file, category = 'documents') {
  if (!file || !clinicId || !patientId) return null;
  const safeCategory = sanitizeStorageSegment(category, 'documents');
  const safeFileName = sanitizeStorageSegment(file.name || 'file');
  const path = [
    sanitizeStorageSegment(clinicId, 'clinic'),
    sanitizeStorageSegment(patientId, 'patient'),
    safeCategory,
    `${Date.now()}-${Math.random().toString(36).slice(2, 8)}-${safeFileName}`,
  ].join('/');

  const { error } = await SB.storage.from(PATIENT_ASSETS_BUCKET).upload(path, file, {
    upsert: false,
    contentType: file.type || 'application/octet-stream',
  });
  if (error) throw error;

  const { data } = SB.storage.from(PATIENT_ASSETS_BUCKET).getPublicUrl(path);
  return {
    bucket: PATIENT_ASSETS_BUCKET,
    path,
    url: data?.publicUrl || '',
  };
}

async function deletePatientAsset(asset) {
  const bucket = String(asset?.bucket || PATIENT_ASSETS_BUCKET || '').trim();
  const path = String(asset?.storagePath || asset?.path || '').trim();
  if (!bucket || !path) return;
  const { error } = await SB.storage.from(bucket).remove([path]);
  if (error) throw error;
}

const PATIENT_PROFILE_BLOB_PREFIX = '__senan_patient_profile__:';

function normalizePatientProfileData(profile = {}) {
  const src = profile && typeof profile === 'object' && !Array.isArray(profile) ? profile : {};
  return {
    medicalRecord: src.medicalRecord && typeof src.medicalRecord === 'object' && !Array.isArray(src.medicalRecord) ? src.medicalRecord : {},
    notes: Array.isArray(src.notes) ? src.notes : [],
    dentalChart: src.dentalChart && typeof src.dentalChart === 'object' && !Array.isArray(src.dentalChart) ? src.dentalChart : {},
    xrays: Array.isArray(src.xrays) ? src.xrays : [],
    documents: Array.isArray(src.documents) ? src.documents : [],
  };
}

function hasPatientProfileData(profile = {}) {
  const normalized = normalizePatientProfileData(profile);
  return (
    Object.keys(normalized.medicalRecord).length > 0 ||
    normalized.notes.length > 0 ||
    Object.keys(normalized.dentalChart).length > 0 ||
    normalized.xrays.length > 0 ||
    normalized.documents.length > 0
  );
}

function decodePatientProfileNotes(rawNotes) {
  const raw = String(rawNotes || '').trim();
  if (!raw) return { summary: '', profileData: normalizePatientProfileData() };
  if (!raw.startsWith(PATIENT_PROFILE_BLOB_PREFIX)) {
    return { summary: raw, profileData: normalizePatientProfileData() };
  }
  try {
    const parsed = JSON.parse(raw.slice(PATIENT_PROFILE_BLOB_PREFIX.length));
    return {
      summary: String(parsed?.summary || parsed?.notes || '').trim(),
      profileData: normalizePatientProfileData(parsed?.profileData || parsed?.profile || {}),
    };
  } catch (error) {
    console.warn('[Senan] failed to decode patient profile blob:', error);
    return { summary: raw, profileData: normalizePatientProfileData() };
  }
}

function encodePatientProfileNotes(summary, profileData = {}) {
  const cleanSummary = String(summary || '').trim();
  const normalized = normalizePatientProfileData(profileData);
  if (!hasPatientProfileData(normalized)) return cleanSummary;
  return PATIENT_PROFILE_BLOB_PREFIX + JSON.stringify({
    summary: cleanSummary,
    profileData: normalized,
  });
}

function mergePatientProfileData(currentProfile = {}, incomingProfile = {}) {
  const current = normalizePatientProfileData(currentProfile);
  const incoming = incomingProfile && typeof incomingProfile === 'object' && !Array.isArray(incomingProfile) ? incomingProfile : {};
  return {
    medicalRecord: Object.prototype.hasOwnProperty.call(incoming, 'medicalRecord') ? normalizePatientProfileData({ medicalRecord: incoming.medicalRecord }).medicalRecord : current.medicalRecord,
    notes: Object.prototype.hasOwnProperty.call(incoming, 'notes') ? normalizePatientProfileData({ notes: incoming.notes }).notes : current.notes,
    dentalChart: Object.prototype.hasOwnProperty.call(incoming, 'dentalChart') ? normalizePatientProfileData({ dentalChart: incoming.dentalChart }).dentalChart : current.dentalChart,
    xrays: Object.prototype.hasOwnProperty.call(incoming, 'xrays') ? normalizePatientProfileData({ xrays: incoming.xrays }).xrays : current.xrays,
    documents: Object.prototype.hasOwnProperty.call(incoming, 'documents') ? normalizePatientProfileData({ documents: incoming.documents }).documents : current.documents,
  };
}

function isMissingRelationError(error) {
  const message = String(error?.message || '').toLowerCase();
  const details = String(error?.details || '').toLowerCase();
  const hint = String(error?.hint || '').toLowerCase();
  const combined = `${message} ${details} ${hint}`;
  return (
    error?.code === '42P01' ||
    error?.code === 'PGRST205' ||
    (combined.includes('relation') && combined.includes('does not exist')) ||
    (combined.includes('could not find') && combined.includes('schema cache')) ||
    (combined.includes('table') && combined.includes('not found'))
  );
}

function isMissingColumnError(error, columnName = '') {
  const message = String(error?.message || '').toLowerCase();
  const details = String(error?.details || '').toLowerCase();
  const hint = String(error?.hint || '').toLowerCase();
  const combined = `${message} ${details} ${hint}`;
  const target = String(columnName || '').trim().toLowerCase();
  return (
    error?.code === '42703' ||
    (
      combined.includes('column') &&
      combined.includes('does not exist') &&
      (!target || combined.includes(target))
    )
  );
}

let appointmentWorkflowColumnAvailable = true;

async function fetchAppointmentRow(id) {
  const related = await SB.from('appointments')
    .select('*, patients(name, avatar), doctors(name, color)')
    .eq('id', id)
    .single();
  if (!related.error) return related;

  const fallback = await SB.from('appointments')
    .select('*')
    .eq('id', id)
    .single();
  return fallback;
}

async function getCurrentActor() {
  try {
    const { data } = await SB.auth.getUser();
    const user = data?.user || null;
    if (!user) return { userId: null, actorName: '' };
    const actorName = String(
      user.user_metadata?.admin_name ||
      user.user_metadata?.full_name ||
      user.email ||
      ''
    ).trim();
    return { userId: user.id, actorName };
  } catch (error) {
    console.warn('[Senan] failed to resolve current actor:', error);
    return { userId: null, actorName: '' };
  }
}

async function safeLogAuditEvent(clinicId, entityType, entityId, action, details = {}) {
  if (!clinicId || !entityType || !action) return;
  try {
    const actor = await getCurrentActor();
    const { error } = await SB.from('audit_log').insert({
      clinic_id: clinicId,
      entity_type: entityType,
      entity_id: entityId ? String(entityId) : null,
      action,
      actor_user_id: actor.userId,
      actor_name: actor.actorName,
      details,
    });
    if (error && !isMissingRelationError(error)) {
      console.warn('[Senan] audit log insert failed:', error);
    }
  } catch (error) {
    if (!isMissingRelationError(error)) {
      console.warn('[Senan] audit log insert failed:', error);
    }
  }
}

async function getMappedInvoiceById(id) {
  if (!id) return null;
  const { data: row, error } = await SB.from('invoices')
    .select('*, patients(name)')
    .eq('id', id)
    .single();
  if (error) throw error;
  const { data: items, error: itemsError } = await SB.from('invoice_items')
    .select('*')
    .eq('invoice_id', id);
  if (itemsError) throw itemsError;
  return mapInvoice(row, items || []);
}

let patientProfileTablesReadyPromise = null;

async function arePatientProfileTablesReady(forceRefresh = false) {
  if (!forceRefresh && patientProfileTablesReadyPromise) return patientProfileTablesReadyPromise;

  patientProfileTablesReadyPromise = Promise.all([
    SB.from('patient_medical_records').select('patient_id').limit(1),
    SB.from('patient_dental_charts').select('patient_id').limit(1),
    SB.from('patient_notes').select('id').limit(1),
    SB.from('patient_xrays').select('id').limit(1),
    SB.from('patient_documents').select('id').limit(1),
  ]).then(results => {
    const firstError = results.find(result => result?.error)?.error;
    if (firstError) throw firstError;
    return true;
  }).catch(error => {
    if (isMissingRelationError(error)) return false;
    throw error;
  });

  return patientProfileTablesReadyPromise;
}

function mapStoredPatientProfileEntry(row) {
  const data = row?.data && typeof row.data === 'object' && !Array.isArray(row.data) ? row.data : {};
  return {
    ...data,
    id: data.id || row?.entry_key || row?.id || '',
  };
}

function mergeLoadedPatientProfiles(patients, profileMap) {
  return (patients || []).map(patient => ({
    ...patient,
    profileData: mergePatientProfileData(patient.profileData, profileMap?.[patient.id] || {}),
  }));
}

async function loadPatientProfiles(clinicId, patientIds) {
  const ids = Array.isArray(patientIds) ? patientIds.filter(Boolean) : [];
  if (!clinicId || ids.length === 0) return {};
  if (!(await arePatientProfileTablesReady())) return {};

  const [
    { data: medicalRows, error: medicalError },
    { data: dentalRows, error: dentalError },
    { data: noteRows, error: noteError },
    { data: xrayRows, error: xrayError },
    { data: documentRows, error: documentError },
  ] = await Promise.all([
    SB.from('patient_medical_records').select('patient_id, data').eq('clinic_id', clinicId).in('patient_id', ids),
    SB.from('patient_dental_charts').select('patient_id, data').eq('clinic_id', clinicId).in('patient_id', ids),
    SB.from('patient_notes').select('id, patient_id, entry_key, sort_order, data').eq('clinic_id', clinicId).in('patient_id', ids).order('sort_order', { ascending: true }).order('created_at', { ascending: true }),
    SB.from('patient_xrays').select('id, patient_id, entry_key, sort_order, data').eq('clinic_id', clinicId).in('patient_id', ids).order('sort_order', { ascending: true }).order('created_at', { ascending: true }),
    SB.from('patient_documents').select('id, patient_id, entry_key, sort_order, data').eq('clinic_id', clinicId).in('patient_id', ids).order('sort_order', { ascending: true }).order('created_at', { ascending: true }),
  ]);

  const firstError = medicalError || dentalError || noteError || xrayError || documentError;
  if (firstError) {
    if (isMissingRelationError(firstError)) {
      patientProfileTablesReadyPromise = Promise.resolve(false);
      return {};
    }
    throw firstError;
  }

  const profileMap = Object.fromEntries(ids.map(id => [id, {}]));

  for (const row of medicalRows || []) {
    profileMap[row.patient_id] = {
      ...(profileMap[row.patient_id] || {}),
      medicalRecord: row?.data && typeof row.data === 'object' && !Array.isArray(row.data) ? row.data : {},
    };
  }

  for (const row of dentalRows || []) {
    profileMap[row.patient_id] = {
      ...(profileMap[row.patient_id] || {}),
      dentalChart: row?.data && typeof row.data === 'object' && !Array.isArray(row.data) ? row.data : {},
    };
  }

  for (const row of noteRows || []) {
    const current = profileMap[row.patient_id] || normalizePatientProfileData();
    current.notes = [...(current.notes || []), mapStoredPatientProfileEntry(row)];
    profileMap[row.patient_id] = current;
  }

  for (const row of xrayRows || []) {
    const current = profileMap[row.patient_id] || normalizePatientProfileData();
    current.xrays = [...(current.xrays || []), mapStoredPatientProfileEntry(row)];
    profileMap[row.patient_id] = current;
  }

  for (const row of documentRows || []) {
    const current = profileMap[row.patient_id] || normalizePatientProfileData();
    current.documents = [...(current.documents || []), mapStoredPatientProfileEntry(row)];
    profileMap[row.patient_id] = current;
  }

  return profileMap;
}

async function savePatientProfileToBlob(patientId, summary, profileData) {
  const normalized = normalizePatientProfileData(profileData);
  const { data: row, error } = await SB.from('patients')
    .update({ notes: encodePatientProfileNotes(summary, normalized) })
    .eq('id', patientId)
    .select()
    .single();
  if (error) throw error;
  const mapped = mapPatient(row);
  mapped.profileData = normalized;
  return mapped;
}

async function savePatientProfileToTables(patientId, clinicId, summary, profileData) {
  const normalized = normalizePatientProfileData(profileData);
  const cleanSummary = String(summary || '').trim();

  const { data: row, error } = await SB.from('patients')
    .update({ notes: cleanSummary })
    .eq('id', patientId)
    .select()
    .single();
  if (error) throw error;

  const upsertSingletonProfile = async (table, data) => {
    const hasData = data && typeof data === 'object' && !Array.isArray(data) && Object.keys(data).length > 0;
    if (!hasData) {
      const { error: deleteError } = await SB.from(table)
        .delete()
        .eq('clinic_id', clinicId)
        .eq('patient_id', patientId);
      if (deleteError) throw deleteError;
      return;
    }
    const { error: upsertError } = await SB.from(table).upsert({
      clinic_id: clinicId,
      patient_id: patientId,
      data,
      updated_at: new Date().toISOString(),
    }, { onConflict: 'patient_id' });
    if (upsertError) throw upsertError;
  };

  const replaceProfileCollection = async (table, items) => {
    const { error: deleteError } = await SB.from(table)
      .delete()
      .eq('clinic_id', clinicId)
      .eq('patient_id', patientId);
    if (deleteError) throw deleteError;

    const rows = (Array.isArray(items) ? items : []).map((item, index) => {
      const data = item && typeof item === 'object' && !Array.isArray(item) ? item : { value: item };
      return {
        clinic_id: clinicId,
        patient_id: patientId,
        entry_key: String(data.id || `${table}-${index}`),
        sort_order: index,
        data,
      };
    });

    if (!rows.length) return;
    const { error: insertError } = await SB.from(table).insert(rows);
    if (insertError) throw insertError;
  };

  await upsertSingletonProfile('patient_medical_records', normalized.medicalRecord);
  await upsertSingletonProfile('patient_dental_charts', normalized.dentalChart);
  await replaceProfileCollection('patient_notes', normalized.notes);
  await replaceProfileCollection('patient_xrays', normalized.xrays);
  await replaceProfileCollection('patient_documents', normalized.documents);

  const mapped = mapPatient(row);
  mapped.profileData = normalized;
  return mapped;
}

async function persistPatientProfileData(patientId, clinicId, summary, profileData) {
  if (!patientId) throw new Error('Missing patient id');
  if (!clinicId) return savePatientProfileToBlob(patientId, summary, profileData);

  try {
    if (!(await arePatientProfileTablesReady())) {
      return savePatientProfileToBlob(patientId, summary, profileData);
    }
    return await savePatientProfileToTables(patientId, clinicId, summary, profileData);
  } catch (error) {
    if (isMissingRelationError(error)) {
      patientProfileTablesReadyPromise = Promise.resolve(false);
      return savePatientProfileToBlob(patientId, summary, profileData);
    }
    throw error;
  }
}

function pickClinicName(user, draft) {
  return String(
    draft?.clinicName ||
    user?.user_metadata?.clinic_name ||
    DEFAULT_CLINIC_NAME
  ).trim();
}

function pickAdminName(user, draft) {
  return String(
    draft?.adminName ||
    user?.user_metadata?.admin_name ||
    user?.user_metadata?.full_name ||
    ''
  ).trim();
}

async function getClinicForOwner(userId) {
  const { data, error } = await SB.from('clinics')
    .select('id, name, owner_id, phone, address, plan, trial_ends_at, created_at')
    .eq('owner_id', userId)
    .limit(1);

  if (error) throw error;
  return Array.isArray(data) && data[0] ? data[0] : null;
}

async function ensureClinicForOwner(user, draft) {
  if (!user?.id) throw new Error('Missing authenticated user');

  const preferredName = pickClinicName(user, draft) || DEFAULT_CLINIC_NAME;
  let clinic = await getClinicForOwner(user.id);

  if (!clinic) {
    const { data, error } = await SB.from('clinics')
      .insert({ owner_id: user.id, name: preferredName })
      .select('id, name, owner_id, phone, address, plan, trial_ends_at, created_at')
      .single();
    if (error) throw error;
    clinic = data;
  } else if (draft?.clinicName && clinic.name !== preferredName) {
    const { data, error } = await SB.from('clinics')
      .update({ name: preferredName })
      .eq('id', clinic.id)
      .select('id, name, owner_id, phone, address, plan, trial_ends_at, created_at')
      .single();
    if (error) throw error;
    clinic = data;
  }

  return clinic;
}

async function ensureAdminStaff(clinicId, adminName) {
  const cleanName = String(adminName || '').trim();
  if (!clinicId || !cleanName) return null;

  const { data, error } = await SB.from('staff')
    .select('id, clinic_id, name, role, phone, shift, avatar, created_at')
    .eq('clinic_id', clinicId)
    .eq('name', cleanName)
    .limit(1);
  if (error) throw error;

  if (Array.isArray(data) && data[0]) return data[0];

  const { data: row, error: insertError } = await SB.from('staff')
    .insert({
      clinic_id: clinicId,
      name: cleanName,
      role: DEFAULT_ADMIN_ROLE,
      shift: 'صباحي',
      avatar: initials(cleanName),
    })
    .select()
    .single();
  if (insertError) throw insertError;

  return row;
}

async function ensureClinicAccess(user, draftOverride) {
  const draft = draftOverride || getPendingSignupDraft(user?.email);
  const clinic = await ensureClinicForOwner(user, draft);
  const adminName = pickAdminName(user, draft);
  const adminStaff = await ensureAdminStaff(clinic?.id, adminName);

  if (draft?.email) clearPendingSignupDraft(draft.email);

  return { clinic, adminStaff, draft };
}

async function registerClinicAccount({ clinicName, adminName, email, password }) {
  const normalizedEmail = normalizeEmail(email);
  const draft = {
    clinicName: String(clinicName || '').trim(),
    adminName: String(adminName || '').trim(),
    email: normalizedEmail,
  };

  savePendingSignupDraft(draft);

  const { data, error } = await SB.auth.signUp({
    email: normalizedEmail,
    password,
    options: {
      data: {
        clinic_name: draft.clinicName,
        admin_name: draft.adminName,
        full_name: draft.adminName,
      },
      emailRedirectTo: window.location.href.split('#')[0],
    },
  });

  if (error) throw error;

  let setup = null;
  if (data?.session && data?.user) {
    setup = await ensureClinicAccess(data.user, draft);
  }

  return {
    ...data,
    setup,
    requiresEmailConfirmation: !data?.session,
  };
}

async function loginClinicAccount({ email, password }) {
  const { data, error } = await SB.auth.signInWithPassword({ email: normalizeEmail(email), password });
  if (error) throw error;
  return data;
}

// ============================================================
// Data Mappers — Supabase format → App format
// ============================================================

function initials(name) {
  if (!name) return '؟';
  const parts = name.trim().split(' ');
  return parts.length >= 2 ? parts[0][0] + parts[1][0] : name.substring(0, 2);
}

function mapPatient(p) {
  const noteData = decodePatientProfileNotes(p.notes);
  return {
    id: p.id,
    name: p.name || '',
    age: p.age || 0,
    gender: p.gender || '',
    phone: p.phone || '',
    email: p.email || '',
    location: p.location || '',
    notes: noteData.summary,
    status: p.status || 'active',
    insurance: p.insurance || 'بدون تأمين',
    avatar: p.avatar || initials(p.name),
    balance: parseFloat(p.balance) || 0,
    lastVisit: p.last_visit || null,
    nextVisit: p.next_visit || null,
    doctor: p.doctors?.name || p.doctor_name || '',
    clinic_id: p.clinic_id,
    created_at: p.created_at,
    profileData: noteData.profileData,
    rawNotes: p.notes || '',
  };
}

function mapDoctor(d) {
  return {
    id: d.id,
    name: d.name || '',
    specialty: d.specialty || '',
    color: d.color || '#0891b2',
    avatar: d.avatar || initials(d.name),
    status: d.status || 'متاح',
    commissionRate: parseFloat(d.commission_rate) || 0.30,
    patients: 0,
    rating: 4.8,
    clinic_id: d.clinic_id,
  };
}

function deriveAppointmentWorkflowStatus(row = {}) {
  const explicit = String(row.workflow_status || '').trim();
  if (explicit) return explicit;
  switch (row.status) {
    case 'done': return 'completed';
    case 'cancelled': return 'cancelled';
    case 'in-progress': return 'in-chair';
    case 'confirmed': return 'waiting';
    default: return 'booked';
  }
}

function mapSession(s) {
  return {
    id: s.id,
    invoiceId: s.invoice_id,
    patientId: s.patient_id,
    patient: s.patients?.name || '',
    doctorId: s.doctor_id,
    doctor: s.doctors?.name || '',
    date: s.date || '',
    paidAmount: parseFloat(s.paid_amount) || 0,
    workDone: s.work_done || '',
    nextAppt: s.next_appt || null,
    doctorCommission: parseFloat(s.doctor_commission) || 0,
    notes: s.notes || '',
    clinic_id: s.clinic_id,
    created_at: s.created_at,
  };
}

function mapPayment(row) {
  return {
    id: row.id,
    clinic_id: row.clinic_id,
    invoiceId: row.invoice_id,
    patientId: row.patient_id,
    patient: row.patients?.name || '',
    sessionId: row.session_id || null,
    amount: parseFloat(row.amount) || 0,
    method: row.method || 'cash',
    date: row.payment_date || row.created_at || '',
    receivedBy: row.received_by || '',
    sourceType: row.source_type || 'manual',
    status: row.status || 'posted',
    notes: row.notes || '',
    created_at: row.created_at,
  };
}

function mapCashShift(row) {
  return {
    id: row.id,
    clinic_id: row.clinic_id,
    openedAt: row.opened_at || row.created_at || '',
    closedAt: row.closed_at || null,
    openingBalance: parseFloat(row.opening_balance) || 0,
    cashIn: parseFloat(row.cash_in) || 0,
    cashOut: parseFloat(row.cash_out) || 0,
    expectedClose: parseFloat(row.expected_close) || 0,
    actualClose: row.actual_close == null ? null : parseFloat(row.actual_close) || 0,
    variance: parseFloat(row.variance) || 0,
    status: row.status || 'open',
    notes: row.notes || '',
    openedBy: row.opened_by || '',
    closedBy: row.closed_by || '',
    created_at: row.created_at,
    updated_at: row.updated_at || row.created_at,
  };
}

function mapAuditEntry(row) {
  return {
    id: row.id,
    clinic_id: row.clinic_id,
    entityType: row.entity_type || '',
    entityId: row.entity_id || '',
    action: row.action || '',
    actorUserId: row.actor_user_id || null,
    actorName: row.actor_name || '',
    details: row.details && typeof row.details === 'object' && !Array.isArray(row.details) ? row.details : {},
    created_at: row.created_at,
  };
}

function mapPrescription(row, items) {
  const myItems = (items || []).filter(it => it.prescription_id === row.id);
  return {
    id: row.id,
    patientId: row.patient_id || null,
    patient: row.patients?.name || '',
    doctorId: row.doctor_id || null,
    doctor: row.doctors?.name || '',
    date: row.date || '',
    notes: row.notes || '',
    meds: myItems.map(it => ({
      id: it.id,
      name: it.med_name || '',
      dose: it.dose || '',
      duration: it.duration || '',
    })),
    clinic_id: row.clinic_id,
    created_at: row.created_at,
  };
}

function mapPurchaseInvoice(row, items) {
  const myItems = (items || []).filter(it => it.purchase_invoice_id === row.id);
  return {
    id: row.id,
    supplier: row.supplier || '',
    date: row.date || '',
    total: parseFloat(row.total) || 0,
    notes: row.notes || '',
    clinic_id: row.clinic_id,
    created_at: row.created_at,
    items: myItems.map(it => ({
      id: it.id,
      inventoryId: it.inventory_id || null,
      name: it.name || '',
      qty: parseFloat(it.qty) || 0,
      unitPrice: parseFloat(it.unit_price) || 0,
      total: (parseFloat(it.qty) || 0) * (parseFloat(it.unit_price) || 0),
    })),
  };
}

async function adjustInventoryStock(inventoryId, deltaQty) {
  const qtyDelta = Number(deltaQty) || 0;
  if (!inventoryId || !qtyDelta) return;
  const { data: current, error: fetchError } = await SB.from('inventory')
    .select('id, stock')
    .eq('id', inventoryId)
    .single();
  if (fetchError) throw fetchError;
  const nextStock = Math.max(0, (Number(current?.stock) || 0) + qtyDelta);
  const { error: updateError } = await SB.from('inventory')
    .update({ stock: nextStock })
    .eq('id', inventoryId);
  if (updateError) throw updateError;
}

async function refreshInventoryPurchasePrice(inventoryId) {
  if (!inventoryId) return;
  const { data, error } = await SB.from('purchase_invoice_items')
    .select('unit_price, purchase_invoices(date, created_at)')
    .eq('inventory_id', inventoryId);
  if (error) throw error;
  const latest = (data || [])
    .slice()
    .sort((a, b) => {
      const dateA = new Date(a.purchase_invoices?.created_at || a.purchase_invoices?.date || 0).getTime();
      const dateB = new Date(b.purchase_invoices?.created_at || b.purchase_invoices?.date || 0).getTime();
      return dateB - dateA;
    })[0];
  if (!latest) {
    const { error: resetError } = await SB.from('inventory')
      .update({ price: 0 })
      .eq('id', inventoryId);
    if (resetError) throw resetError;
    return;
  }
  const { error: updateError } = await SB.from('inventory')
    .update({ price: Number(latest.unit_price) || 0 })
    .eq('id', inventoryId);
  if (updateError) throw updateError;
}

function mapBomItem(b) {
  return {
    id: b.id,
    serviceId: b.service_id,
    serviceName: b.services?.name || '',
    inventoryId: b.inventory_id,
    itemName: b.inventory?.name || '',
    unit: b.inventory?.unit || 'قطعة',
    standardQty: parseFloat(b.standard_qty) || 1,
    clinic_id: b.clinic_id,
  };
}

function mapAppointment(a) {
  return {
    id: a.id,
    patient: a.patients?.name || '',
    patientId: a.patient_id,
    doctor: a.doctors?.name || '',
    doctorId: a.doctor_id,
    doctorColor: a.doctors?.color || '#0891b2',
    time: (a.time || '').substring(0, 5),
    duration: a.duration || 30,
    date: a.date || '',
    type: a.type || '',
    status: a.status || 'pending',
    workflowStatus: deriveAppointmentWorkflowStatus(a),
    room: a.room || '',
    notes: a.notes || '',
    clinic_id: a.clinic_id,
  };
}

function mapInvoice(inv, items) {
  const myItems = (items || []).filter(it => it.invoice_id === inv.id);
  const noteMeta = decodeInvoiceNotes(inv.notes);
  return {
    id: inv.id,
    patient: inv.patients?.name || '',
    patientId: inv.patient_id,
    date: inv.date || '',
    total: parseFloat(inv.total) || 0,
    paid: parseFloat(inv.paid) || 0,
    status: inv.status || 'unpaid',
    notes: noteMeta.memo || '',
    paymentMethod: inv.payment_method || noteMeta.paymentMethod || '',
    doctorId: inv.doctor_id || noteMeta.doctorId || null,
    doctorName: inv.doctor_name || noteMeta.doctorName || '',
    discountPercent: noteMeta.discountPercent || 0,
    discountAmount: noteMeta.discountAmount || 0,
    subtotal: noteMeta.subtotal || 0,
    taxPercent: noteMeta.taxPercent || 0,
    taxAmount: noteMeta.taxAmount || 0,
    noteMeta,
    rawNotes: inv.notes || '',
    created_at: inv.created_at,
    items: myItems.map(it => ({
      id: it.id,
      name: it.name || '',
      qty: it.qty || 1,
      price: parseFloat(it.price) || 0,
      discount: parseFloat(it.discount) || 0,
    })),
    clinic_id: inv.clinic_id,
  };
}

function mapInventoryItem(item) {
  return {
    id: item.id,
    name: item.name || '',
    category: item.category || '',
    stock: parseFloat(item.stock) || 0,
    minStock: parseFloat(item.min_stock) || 0,
    unit: item.unit || 'قطعة',
    price: parseFloat(item.price) || 0,
    supplier: item.supplier || '',
    expiry: item.expiry || '—',
    clinic_id: item.clinic_id,
  };
}

function mapService(s) {
  return {
    id: s.id,
    name: s.name || '',
    price: parseFloat(s.price) || 0,
    duration: s.duration || 30,
    category: s.category || '',
    clinic_id: s.clinic_id,
  };
}

// ============================================================
// CRUD Helpers
// ============================================================

const DB = {
  // --- Patients ---
  async addPatient(clinicId, data) {
    const payload = { ...data };
    payload.notes = encodePatientProfileNotes(data?.notes || '', data?.profileData || {});
    delete payload.profileData;
    const { data: row, error } = await SB.from('patients')
      .insert({ ...payload, clinic_id: clinicId, avatar: initials(data.name) })
      .select().single();
    if (error) throw error;
    const mapped = mapPatient(row);
    safeLogAuditEvent(clinicId, 'patient', mapped.id, 'create', {
      name: mapped.name,
      phone: mapped.phone,
    });
    return mapped;
  },
  async updatePatient(id, data) {
    const { data: existingRow, error: existingError } = await SB.from('patients')
      .select('id, clinic_id, name, phone')
      .eq('id', id)
      .single();
    if (existingError) throw existingError;
    const payload = { ...data };
    const hasSummaryNote = Object.prototype.hasOwnProperty.call(payload, 'notes');
    const hasProfileData = Object.prototype.hasOwnProperty.call(payload, 'profileData');
    delete payload.profileData;

    if (hasSummaryNote || hasProfileData) {
      const { data: currentRow, error: currentError } = await SB.from('patients')
        .select('notes')
        .eq('id', id)
        .single();
      if (currentError) throw currentError;
      const decoded = decodePatientProfileNotes(currentRow?.notes);
      const nextSummary = hasSummaryNote ? payload.notes : decoded.summary;
      const nextProfile = hasProfileData
        ? mergePatientProfileData(decoded.profileData, data.profileData)
        : decoded.profileData;
      payload.notes = encodePatientProfileNotes(nextSummary, nextProfile);
    }

    const { data: row, error } = await SB.from('patients')
      .update(payload).eq('id', id).select().single();
    if (error) throw error;
    const mapped = mapPatient(row);
    safeLogAuditEvent(existingRow.clinic_id, 'patient', id, 'update', {
      before: { name: existingRow.name, phone: existingRow.phone },
      updatedFields: Object.keys(data || {}),
    });
    return mapped;
  },
  async savePatientProfile(patientId, clinicId, summary, profileData) {
    const saved = await persistPatientProfileData(patientId, clinicId, summary, profileData);
    safeLogAuditEvent(clinicId, 'patient_profile', patientId, 'update', {
      summaryLength: String(summary || '').length,
      sections: Object.keys(profileData || {}),
    });
    return saved;
  },
  async uploadPatientAsset(clinicId, patientId, file, category) {
    return uploadPatientAsset(clinicId, patientId, file, category);
  },
  async deletePatientAsset(asset) {
    return deletePatientAsset(asset);
  },
  async deletePatient(id) {
    const { data: current, error: currentError } = await SB.from('patients')
      .select('id, clinic_id, name')
      .eq('id', id)
      .single();
    if (currentError) throw currentError;
    const { error } = await SB.from('patients').delete().eq('id', id);
    if (error) throw error;
    safeLogAuditEvent(current.clinic_id, 'patient', id, 'delete', { name: current.name });
  },

  // --- Doctors ---
  async addDoctor(clinicId, data) {
    const { data: row, error } = await SB.from('doctors')
      .insert({ ...data, clinic_id: clinicId, avatar: initials(data.name) })
      .select().single();
    if (error) throw error;
    return mapDoctor(row);
  },
  async updateDoctor(id, data) {
    const { data: row, error } = await SB.from('doctors')
      .update(data).eq('id', id).select().single();
    if (error) throw error;
    return mapDoctor(row);
  },

  // --- Appointments ---
  async addAppointment(clinicId, data) {
    const payload = { ...data, clinic_id: clinicId };
    if (appointmentWorkflowColumnAvailable) {
      payload.workflow_status = data?.workflow_status || 'booked';
    }

    let insertResult = await SB.from('appointments')
      .insert(payload)
      .select('id')
      .single();

    if (insertResult.error && appointmentWorkflowColumnAvailable && isMissingColumnError(insertResult.error, 'workflow_status')) {
      appointmentWorkflowColumnAvailable = false;
      const fallbackPayload = { ...data, clinic_id: clinicId };
      insertResult = await SB.from('appointments')
        .insert(fallbackPayload)
        .select('id')
        .single();
    }

    if (insertResult.error) throw insertResult.error;

    const { data: row, error } = await fetchAppointmentRow(insertResult.data?.id);
    if (error) throw error;
    const mapped = mapAppointment(row || { ...payload, id: insertResult.data?.id });
    safeLogAuditEvent(clinicId, 'appointment', mapped.id, 'create', {
      patientId: mapped.patientId,
      date: mapped.date,
      time: mapped.time,
      status: mapped.status,
      workflowStatus: mapped.workflowStatus,
    });
    return mapped;
  },
  async updateAppointment(id, data) {
    let currentResult = await SB.from('appointments')
      .select('id, clinic_id, patient_id, status, workflow_status, date, time')
      .eq('id', id)
      .single();

    if (currentResult.error && isMissingColumnError(currentResult.error, 'workflow_status')) {
      appointmentWorkflowColumnAvailable = false;
      currentResult = await SB.from('appointments')
        .select('id, clinic_id, patient_id, status, date, time')
        .eq('id', id)
        .single();
    }

    if (currentResult.error) throw currentResult.error;
    const current = currentResult.data;

    const updatePayload = { ...data };
    if (!appointmentWorkflowColumnAvailable) delete updatePayload.workflow_status;

    let updateResult = await SB.from('appointments')
      .update(updatePayload)
      .eq('id', id)
      .select('id')
      .single();

    if (updateResult.error && appointmentWorkflowColumnAvailable && isMissingColumnError(updateResult.error, 'workflow_status')) {
      appointmentWorkflowColumnAvailable = false;
      const fallbackPayload = { ...updatePayload };
      delete fallbackPayload.workflow_status;
      updateResult = await SB.from('appointments')
        .update(fallbackPayload)
        .eq('id', id)
        .select('id')
        .single();
    }

    if (updateResult.error) throw updateResult.error;

    const { data: row, error } = await fetchAppointmentRow(id);
    if (error) throw error;
    const mapped = mapAppointment(row);
    safeLogAuditEvent(current.clinic_id, 'appointment', id, 'update', {
      patientId: current.patient_id,
      before: {
        status: current.status,
        workflowStatus: current.workflow_status || deriveAppointmentWorkflowStatus(current),
        date: current.date,
        time: current.time,
      },
      changes: data,
    });
    return mapped;
  },
  async deleteAppointment(id) {
    const { data: current, error: currentError } = await SB.from('appointments')
      .select('id, clinic_id, patient_id, date, time')
      .eq('id', id)
      .single();
    if (currentError) throw currentError;
    const { error } = await SB.from('appointments').delete().eq('id', id);
    if (error) throw error;
    safeLogAuditEvent(current.clinic_id, 'appointment', id, 'delete', {
      patientId: current.patient_id,
      date: current.date,
      time: current.time,
    });
  },

  // --- Invoices (with multiple items) ---
  async addInvoice(clinicId, invoiceData, items) {
    // 1. Insert invoice
    const computedTotal = items.reduce((s, it) => s + (it.qty * it.price - (it.discount || 0)), 0);
    const total = Number(invoiceData.total);
    const invoicePayload = { ...invoiceData, clinic_id: clinicId, total: Number.isFinite(total) ? total : computedTotal };
    const invoiceInsertPayload = { ...invoicePayload };
    const hasDoctorMeta = Boolean(invoiceInsertPayload.doctor_id || invoiceInsertPayload.doctor_name);
    let inv = null;

    try {
      const { data: row, error: invErr } = await SB.from('invoices')
        .insert(invoiceInsertPayload)
        .select('*, patients(name)').single();
      if (invErr) throw invErr;
      inv = row;
    } catch (firstError) {
      if (!hasDoctorMeta) throw firstError;
      const fallbackPayload = { ...invoicePayload };
      delete fallbackPayload.doctor_id;
      delete fallbackPayload.doctor_name;
      const { data: row, error: fallbackErr } = await SB.from('invoices')
        .insert(fallbackPayload)
        .select('*, patients(name)').single();
      if (fallbackErr) throw fallbackErr;
      inv = row;
    }

    // 2. Insert items
    if (items.length > 0) {
      const itemRows = items.map(it => ({
        invoice_id: inv.id,
        service_id: it.service_id || null,
        name: it.name,
        qty: it.qty || 1,
        price: it.price || 0,
        discount: it.discount || 0,
      }));
      const { error: itemErr } = await SB.from('invoice_items').insert(itemRows);
      if (itemErr) throw itemErr;
    }

    // 3. Fetch invoice items and return
    const { data: invItems } = await SB.from('invoice_items').select('*').eq('invoice_id', inv.id);
    const mapped = mapInvoice({ ...inv, ...invoicePayload }, invItems || []);
    safeLogAuditEvent(clinicId, 'invoice', mapped.id, 'create', {
      patientId: mapped.patientId,
      total: mapped.total,
      items: (mapped.items || []).map(item => ({ name: item.name, qty: item.qty, price: item.price })),
    });
    return mapped;
  },

  async updateInvoicePaid(id, paid) {
    const totalRes = await SB.from('invoices').select('total').eq('id', id).single();
    const total = totalRes.data?.total || 0;
    const status = paid >= total ? 'paid' : paid > 0 ? 'partial' : 'unpaid';
    const { data: row, error } = await SB.from('invoices')
      .update({ paid, status }).eq('id', id)
      .select('*, patients(name)').single();
    if (error) throw error;
    const { data: items } = await SB.from('invoice_items').select('*').eq('invoice_id', id);
    return mapInvoice(row, items || []);
  },

  async deleteInvoice(id) {
    const { data: current, error: currentError } = await SB.from('invoices')
      .select('id, clinic_id, patient_id, total')
      .eq('id', id)
      .single();
    if (currentError) throw currentError;
    await SB.from('payments').delete().eq('invoice_id', id);
    await SB.from('sessions').delete().eq('invoice_id', id);
    await SB.from('invoice_items').delete().eq('invoice_id', id);
    const { error } = await SB.from('invoices').delete().eq('id', id);
    if (error) throw error;
    safeLogAuditEvent(current.clinic_id, 'invoice', id, 'delete', {
      patientId: current.patient_id,
      total: current.total,
    });
  },

  async getInvoicePayments(invoiceId) {
    const { data, error } = await SB.from('payments')
      .select('*, patients(name)')
      .eq('invoice_id', invoiceId)
      .order('payment_date', { ascending: false })
      .order('created_at', { ascending: false });
    if (error) throw error;
    return (data || []).map(mapPayment);
  },

  async getClinicPayments(clinicId) {
    const { data, error } = await SB.from('payments')
      .select('*, patients(name)')
      .eq('clinic_id', clinicId)
      .order('payment_date', { ascending: false })
      .order('created_at', { ascending: false });
    if (error) {
      if (isMissingRelationError(error)) return [];
      throw error;
    }
    return (data || []).map(mapPayment);
  },

  async addPayment(clinicId, paymentData) {
    const actor = await getCurrentActor();
    const payload = {
      clinic_id: clinicId,
      invoice_id: paymentData.invoice_id,
      patient_id: paymentData.patient_id || null,
      session_id: paymentData.session_id || null,
      amount: Number(paymentData.amount) || 0,
      method: paymentData.method || 'cash',
      payment_date: paymentData.payment_date || paymentData.date || new Date().toISOString().slice(0, 10),
      received_by: String(paymentData.received_by || paymentData.receivedBy || actor.actorName || '').trim(),
      source_type: paymentData.source_type || paymentData.sourceType || 'manual',
      status: paymentData.status || 'posted',
      notes: paymentData.notes || '',
      created_by: actor.userId,
    };
    const { data: row, error } = await SB.from('payments')
      .insert(payload)
      .select('*, patients(name)')
      .single();
    if (error) throw error;
    const mappedPayment = mapPayment(row);
    const mappedInvoice = await getMappedInvoiceById(payload.invoice_id);
    safeLogAuditEvent(clinicId, 'payment', mappedPayment.id, 'create', {
      invoiceId: payload.invoice_id,
      patientId: payload.patient_id,
      sessionId: payload.session_id,
      amount: mappedPayment.amount,
      method: mappedPayment.method,
      sourceType: mappedPayment.sourceType,
    });
    return { payment: mappedPayment, invoice: mappedInvoice };
  },

  async deletePayment(id) {
    const { data: current, error: currentError } = await SB.from('payments')
      .select('id, clinic_id, invoice_id, patient_id, session_id, amount, method, source_type')
      .eq('id', id)
      .single();
    if (currentError) throw currentError;
    const { error } = await SB.from('payments').delete().eq('id', id);
    if (error) throw error;
    const mappedInvoice = await getMappedInvoiceById(current.invoice_id);
    safeLogAuditEvent(current.clinic_id, 'payment', id, 'delete', {
      invoiceId: current.invoice_id,
      patientId: current.patient_id,
      sessionId: current.session_id,
      amount: current.amount,
      method: current.method,
      sourceType: current.source_type,
    });
    return { invoice: mappedInvoice, sessionId: current.session_id };
  },

  // --- Services ---
  async addService(clinicId, data) {
    const { data: row, error } = await SB.from('services')
      .insert({ ...data, clinic_id: clinicId }).select().single();
    if (error) throw error;
    return mapService(row);
  },

  // --- Inventory ---
  async addInventoryItem(clinicId, data) {
    const { data: row, error } = await SB.from('inventory')
      .insert({ ...data, clinic_id: clinicId }).select().single();
    if (error) throw error;
    return mapInventoryItem(row);
  },
  async updateInventoryItem(id, data) {
    const { data: row, error } = await SB.from('inventory')
      .update(data).eq('id', id).select().single();
    if (error) throw error;
    return mapInventoryItem(row);
  },

  // --- Purchase Invoice (auto-updates inventory via trigger) ---
  async addPurchaseInvoice(clinicId, purchaseData, items) {
    const total = items.reduce((s, it) => s + it.qty * it.unit_price, 0);
    const { data: purchase, error } = await SB.from('purchase_invoices')
      .insert({ ...purchaseData, clinic_id: clinicId, total }).select().single();
    if (error) throw error;

    if (items.length > 0) {
      const rows = items.map(it => ({
        purchase_invoice_id: purchase.id,
        inventory_id: it.inventory_id || null,
        name: it.name,
        qty: it.qty,
        unit_price: it.unit_price,
      }));
      const { error: itemErr } = await SB.from('purchase_invoice_items').insert(rows);
      if (itemErr) throw itemErr;
    }
    const refreshedInventoryIds = [...new Set((items || []).map(item => item.inventory_id).filter(Boolean))];
    for (const inventoryId of refreshedInventoryIds) {
      await refreshInventoryPurchasePrice(inventoryId);
    }
    const { data: purchaseItems } = await SB.from('purchase_invoice_items')
      .select('*')
      .eq('purchase_invoice_id', purchase.id);
    return mapPurchaseInvoice(purchase, purchaseItems || []);
  },
  async updatePurchaseInvoice(id, purchaseData, items) {
    const { data: currentPurchase, error: currentError } = await SB.from('purchase_invoices')
      .select('*')
      .eq('id', id)
      .single();
    if (currentError) throw currentError;
    const { data: currentItems, error: itemsError } = await SB.from('purchase_invoice_items')
      .select('*')
      .eq('purchase_invoice_id', id);
    if (itemsError) throw itemsError;

    for (const item of currentItems || []) {
      await adjustInventoryStock(item.inventory_id, -(Number(item.qty) || 0));
      await refreshInventoryPurchasePrice(item.inventory_id);
    }

    const total = items.reduce((s, it) => s + (Number(it.qty) || 0) * (Number(it.unit_price) || 0), 0);
    const { data: updatedPurchase, error: updateError } = await SB.from('purchase_invoices')
      .update({ ...purchaseData, total })
      .eq('id', id)
      .select()
      .single();
    if (updateError) throw updateError;

    const { error: deleteItemsError } = await SB.from('purchase_invoice_items').delete().eq('purchase_invoice_id', id);
    if (deleteItemsError) throw deleteItemsError;

    if (items.length > 0) {
      const rows = items.map(it => ({
        purchase_invoice_id: id,
        inventory_id: it.inventory_id || null,
        name: it.name,
        qty: Number(it.qty) || 0,
        unit_price: Number(it.unit_price) || 0,
      }));
      const { error: insertError } = await SB.from('purchase_invoice_items').insert(rows);
      if (insertError) throw insertError;
    }

    const { data: updatedItems, error: reloadItemsError } = await SB.from('purchase_invoice_items')
      .select('*')
      .eq('purchase_invoice_id', id);
    if (reloadItemsError) throw reloadItemsError;
    const refreshedInventoryIds = [...new Set([
      ...(currentItems || []).map(item => item.inventory_id).filter(Boolean),
      ...(items || []).map(item => item.inventory_id).filter(Boolean),
    ])];
    for (const inventoryId of refreshedInventoryIds) {
      await refreshInventoryPurchasePrice(inventoryId);
    }
    return mapPurchaseInvoice(updatedPurchase, updatedItems || []);
  },
  async deletePurchaseInvoice(id) {
    const { data: currentItems, error: itemsError } = await SB.from('purchase_invoice_items')
      .select('*')
      .eq('purchase_invoice_id', id);
    if (itemsError) throw itemsError;
    for (const item of currentItems || []) {
      await adjustInventoryStock(item.inventory_id, -(Number(item.qty) || 0));
    }
    const { error: deleteItemsError } = await SB.from('purchase_invoice_items').delete().eq('purchase_invoice_id', id);
    if (deleteItemsError) throw deleteItemsError;
    const { error } = await SB.from('purchase_invoices').delete().eq('id', id);
    if (error) throw error;
    const refreshedInventoryIds = [...new Set((currentItems || []).map(item => item.inventory_id).filter(Boolean))];
    for (const inventoryId of refreshedInventoryIds) {
      await refreshInventoryPurchasePrice(inventoryId);
    }
  },

  // --- Physical Count (auto-updates inventory via trigger) ---
  async addPhysicalCount(clinicId, inventoryId, actualCount, notes) {
    const { data: row, error } = await SB.from('physical_counts')
      .insert({ clinic_id: clinicId, inventory_id: inventoryId, actual_count: actualCount, notes })
      .select().single();
    if (error) throw error;
    return row;
  },

  // --- Expenses ---
  async addExpense(clinicId, data) {
    const { data: row, error } = await SB.from('expenses')
      .insert({ ...data, clinic_id: clinicId }).select().single();
    if (error) throw error;
    return row;
  },
  async updateExpense(id, data) {
    const { data: row, error } = await SB.from('expenses')
      .update(data)
      .eq('id', id)
      .select()
      .single();
    if (error) throw error;
    return row;
  },
  async deleteExpense(id) {
    const { error } = await SB.from('expenses').delete().eq('id', id);
    if (error) throw error;
  },

  // --- Prescriptions ---
  async addPrescription(clinicId, prescriptionData, meds) {
    const cleanedMeds = (meds || [])
      .map(med => ({
        name: String(med?.name || '').trim(),
        dose: String(med?.dose || '').trim(),
        duration: String(med?.duration || '').trim(),
      }))
      .filter(med => med.name);

    if (!cleanedMeds.length) {
      throw new Error('Prescription must include at least one medication');
    }

    const { data: row, error } = await SB.from('prescriptions')
      .insert({
        clinic_id: clinicId,
        patient_id: prescriptionData?.patientId || null,
        doctor_id: prescriptionData?.doctorId || null,
        date: prescriptionData?.date || new Date().toISOString().split('T')[0],
        notes: prescriptionData?.notes || '',
      })
      .select('*, patients(name), doctors(name)')
      .single();
    if (error) throw error;

    const itemRows = cleanedMeds.map(med => ({
      prescription_id: row.id,
      med_name: med.name,
      dose: med.dose,
      duration: med.duration,
    }));
    const { error: itemsError } = await SB.from('prescription_items').insert(itemRows);
    if (itemsError) throw itemsError;

    const { data: savedItems, error: reloadError } = await SB.from('prescription_items')
      .select('*')
      .eq('prescription_id', row.id);
    if (reloadError) throw reloadError;

    return mapPrescription(row, savedItems || []);
  },

  // --- Sessions ---
  async addSession(clinicId, data) {
    const { data: row, error } = await SB.from('sessions')
      .insert({ ...data, clinic_id: clinicId })
      .select('*, doctors(name)').single();
    if (error) throw error;
    const mapped = mapSession(row);
    safeLogAuditEvent(clinicId, 'session', mapped.id, 'create', {
      invoiceId: mapped.invoiceId,
      patientId: mapped.patientId,
      paidAmount: mapped.paidAmount,
      date: mapped.date,
    });
    return mapped;
  },
  async addSessionWithPayment(clinicId, data) {
    const session = await this.addSession(clinicId, data);
    let payment = null;
    let invoice = null;
    const paidAmount = Number(data?.paid_amount) || 0;
    if (paidAmount > 0) {
      const result = await this.addPayment(clinicId, {
        invoice_id: data.invoice_id,
        patient_id: data.patient_id,
        session_id: session.id,
        amount: paidAmount,
        method: data.payment_method || 'cash',
        payment_date: data.date,
        received_by: data.received_by || '',
        source_type: 'session',
        notes: data.notes || '',
      });
      payment = result.payment;
      invoice = result.invoice;
    } else {
      invoice = await getMappedInvoiceById(data.invoice_id);
    }
    return { session, payment, invoice };
  },
  async getInvoiceSessions(invoiceId) {
    const { data, error } = await SB.from('sessions')
      .select('*, doctors(name)')
      .eq('invoice_id', invoiceId)
      .order('date').order('created_at');
    if (error) throw error;
    return (data || []).map(mapSession);
  },
  async getClinicSessions(clinicId) {
    const { data, error } = await SB.from('sessions')
      .select('*, doctors(name), patients(name)')
      .eq('clinic_id', clinicId)
      .order('date', { ascending: false })
      .order('created_at', { ascending: false });
    if (error) throw error;
    return (data || []).map(mapSession);
  },
  async deleteSession(id) {
    const { data: current, error: currentError } = await SB.from('sessions')
      .select('id, clinic_id, invoice_id, patient_id, paid_amount, date')
      .eq('id', id)
      .single();
    if (currentError) throw currentError;
    await SB.from('payments').delete().eq('session_id', id);
    const { error } = await SB.from('sessions').delete().eq('id', id);
    if (error) throw error;
    const invoice = await getMappedInvoiceById(current.invoice_id);
    safeLogAuditEvent(current.clinic_id, 'session', id, 'delete', {
      invoiceId: current.invoice_id,
      patientId: current.patient_id,
      paidAmount: current.paid_amount,
      date: current.date,
    });
    return { invoice };
  },
  async getClinicCashShifts(clinicId) {
    const { data, error } = await SB.from('cash_shifts')
      .select('*')
      .eq('clinic_id', clinicId)
      .order('opened_at', { ascending: false });
    if (error) {
      if (isMissingRelationError(error)) return [];
      throw error;
    }
    return (data || []).map(mapCashShift);
  },
  async getOpenCashShift(clinicId) {
    const rows = await this.getClinicCashShifts(clinicId);
    return rows.find(shift => shift.status === 'open') || null;
  },
  async openCashShift(clinicId, data = {}) {
    const actor = await getCurrentActor();
    const payload = {
      clinic_id: clinicId,
      opening_balance: Number(data.opening_balance || data.openingBalance) || 0,
      opened_by: String(data.opened_by || data.openedBy || actor.actorName || '').trim(),
      notes: data.notes || '',
      status: 'open',
    };
    const { data: row, error } = await SB.from('cash_shifts')
      .insert(payload)
      .select()
      .single();
    if (error) throw error;
    const mapped = mapCashShift(row);
    safeLogAuditEvent(clinicId, 'cash_shift', mapped.id, 'open', {
      openingBalance: mapped.openingBalance,
      openedBy: mapped.openedBy,
    });
    return mapped;
  },
  async closeCashShift(id, data = {}) {
    const { data: current, error: currentError } = await SB.from('cash_shifts')
      .select('*')
      .eq('id', id)
      .single();
    if (currentError) throw currentError;
    const payload = {
      closed_at: new Date().toISOString(),
      cash_in: Number(data.cash_in || data.cashIn) || 0,
      cash_out: Number(data.cash_out || data.cashOut) || 0,
      expected_close: Number(data.expected_close || data.expectedClose) || 0,
      actual_close: Number(data.actual_close || data.actualClose) || 0,
      variance: Number(data.variance) || 0,
      closed_by: String(data.closed_by || data.closedBy || '').trim(),
      notes: data.notes != null ? data.notes : current.notes,
      status: 'closed',
      updated_at: new Date().toISOString(),
    };
    const { data: row, error } = await SB.from('cash_shifts')
      .update(payload)
      .eq('id', id)
      .select()
      .single();
    if (error) throw error;
    const mapped = mapCashShift(row);
    safeLogAuditEvent(current.clinic_id, 'cash_shift', id, 'close', {
      cashIn: mapped.cashIn,
      cashOut: mapped.cashOut,
      expectedClose: mapped.expectedClose,
      actualClose: mapped.actualClose,
      variance: mapped.variance,
    });
    return mapped;
  },
  async getClinicAuditLog(clinicId, limit = 150) {
    const { data, error } = await SB.from('audit_log')
      .select('*')
      .eq('clinic_id', clinicId)
      .order('created_at', { ascending: false })
      .limit(limit);
    if (error) {
      if (isMissingRelationError(error)) return [];
      throw error;
    }
    return (data || []).map(mapAuditEntry);
  },
  async logAuditEvent(clinicId, entityType, entityId, action, details = {}) {
    return safeLogAuditEvent(clinicId, entityType, entityId, action, details);
  },
  async getPurchaseInvoices(clinicId) {
    const { data: purchases, error: purchaseErr } = await SB.from('purchase_invoices')
      .select('*')
      .eq('clinic_id', clinicId)
      .order('date', { ascending: false })
      .order('created_at', { ascending: false });
    if (purchaseErr) throw purchaseErr;
    const purchaseIds = (purchases || []).map(row => row.id).filter(Boolean);
    if (purchaseIds.length === 0) return [];
    const { data: items, error: itemsErr } = await SB.from('purchase_invoice_items')
      .select('*')
      .in('purchase_invoice_id', purchaseIds);
    if (itemsErr) throw itemsErr;
    return (purchases || []).map(row => mapPurchaseInvoice(row, items || []));
  },

  // --- BOM ---
  async getBomForService(serviceId) {
    const { data, error } = await SB.from('bom')
      .select('*, inventory(name, unit)')
      .eq('service_id', serviceId);
    if (error) throw error;
    return (data || []).map(mapBomItem);
  },
  async addBomItem(clinicId, serviceId, inventoryId, standardQty) {
    const { data: row, error } = await SB.from('bom')
      .upsert({ clinic_id: clinicId, service_id: serviceId, inventory_id: inventoryId, standard_qty: standardQty },
               { onConflict: 'service_id,inventory_id' })
      .select('*, inventory(name, unit)').single();
    if (error) throw error;
    return mapBomItem(row);
  },
  async deleteBomItem(id) {
    const { error } = await SB.from('bom').delete().eq('id', id);
    if (error) throw error;
  },
  async deleteService(id) {
    const { error: bomError } = await SB.from('bom').delete().eq('service_id', id);
    if (bomError) throw bomError;
    const { error } = await SB.from('services').delete().eq('id', id);
    if (error) throw error;
  },
  async deleteInventoryItem(id) {
    const { error: bomError } = await SB.from('bom').delete().eq('inventory_id', id);
    if (bomError) throw bomError;
    const { error } = await SB.from('inventory').delete().eq('id', id);
    if (error) throw error;
  },

  // --- Staff ---
  async addStaff(clinicId, data) {
    const { data: row, error } = await SB.from('staff')
      .insert({ ...data, clinic_id: clinicId, avatar: initials(data.name) }).select().single();
    if (error) throw error;
    return row;
  },
};

// ============================================================
// Load all data for a clinic
// ============================================================
async function loadClinicData(clinicId) {
  const safe = (p) => Promise.resolve(p).catch(e => { console.warn('[Senan] query failed:', e); return { data: null, error: e }; });

  const [
    { data: pts },
    { data: docs },
    { data: apts },
    { data: invs },
    { data: invItems },
    { data: inv },
    { data: svcs },
    { data: stf },
    { data: exp },
    { data: rxs },
    { data: rxItems },
    { data: sess },
    { data: pays },
    { data: shifts },
    { data: audit },
  ] = await Promise.all([
    safe(SB.from('patients').select('*').eq('clinic_id', clinicId).order('created_at', { ascending: false })),
    safe(SB.from('doctors').select('*').eq('clinic_id', clinicId).order('name')),
    safe(SB.from('appointments').select('*, patients(name, avatar), doctors(name, color)').eq('clinic_id', clinicId).order('date').order('time')),
    safe(SB.from('invoices').select('*, patients(name)').eq('clinic_id', clinicId).order('created_at', { ascending: false })),
    safe(SB.from('invoice_items').select('*')),
    safe(SB.from('inventory').select('*').eq('clinic_id', clinicId).order('name')),
    safe(SB.from('services').select('*').eq('clinic_id', clinicId).order('name')),
    safe(SB.from('staff').select('*').eq('clinic_id', clinicId)),
    safe(SB.from('expenses').select('*').eq('clinic_id', clinicId).order('date', { ascending: false })),
    safe(SB.from('prescriptions').select('*, patients(name), doctors(name)').eq('clinic_id', clinicId).order('date', { ascending: false }).order('created_at', { ascending: false })),
    safe(SB.from('prescription_items').select('*')),
    safe(SB.from('sessions').select('*, doctors(name), patients(name)').eq('clinic_id', clinicId).order('date', { ascending: false }).order('created_at', { ascending: false })),
    safe(SB.from('payments').select('*, patients(name)').eq('clinic_id', clinicId).order('payment_date', { ascending: false }).order('created_at', { ascending: false })),
    safe(SB.from('cash_shifts').select('*').eq('clinic_id', clinicId).order('opened_at', { ascending: false })),
    safe(SB.from('audit_log').select('*').eq('clinic_id', clinicId).order('created_at', { ascending: false }).limit(150)),
  ]);

  const mappedInvs = (invs || []).map(inv => mapInvoice(inv, invItems || []));
  const mappedPrescriptions = (rxs || []).map(rx => mapPrescription(rx, rxItems || []));
  const mappedPatients = (pts || []).map(mapPatient);
  let patientProfiles = {};

  try {
    patientProfiles = await loadPatientProfiles(clinicId, mappedPatients.map(patient => patient.id));
  } catch (error) {
    console.warn('[Senan] failed to load patient profile tables:', error);
  }

  return {
    patients:     mergeLoadedPatientProfiles(mappedPatients, patientProfiles),
    doctors:      (docs    || []).map(mapDoctor),
    appointments: (apts    || []).map(mapAppointment),
    invoices:     mappedInvs,
    inventory:    (inv     || []).map(mapInventoryItem),
    services:     (svcs    || []).map(mapService),
    staff:        stf  || [],
    expenses:     exp  || [],
    prescriptions: mappedPrescriptions,
    sessions:     (sess || []).map(mapSession),
    payments:     (pays || []).map(mapPayment),
    cashShifts:   (shifts || []).map(mapCashShift),
    auditLog:     (audit || []).map(mapAuditEntry),
  };
}

// Expose globally
const AuthAPI = {
  loginClinicAccount,
  registerClinicAccount,
  getClinicForOwner,
  ensureClinicForOwner,
  ensureClinicAccess,
  savePendingSignupDraft,
  getPendingSignupDraft,
  clearPendingSignupDraft,
};

Object.assign(window, {
  SB, DB, AuthAPI, loadClinicData,
  mapPatient, mapDoctor, mapAppointment, mapInvoice, mapInventoryItem, mapService, mapSession, mapPayment, mapCashShift, mapAuditEntry, mapPurchaseInvoice, mapBomItem, mapPrescription, initials,
  decodeInvoiceNotes, encodeInvoiceNotes,
  normalizePatientProfileData, savePatientProfile: persistPatientProfileData, uploadPatientAsset, deletePatientAsset,
});