// MaxAMove CRM — Shared data, utilities, sample seed data, and Supabase helpers
// =============================================================================
//
// This file has two halves:
//   1. SAMPLE SEED DATA (top) — still used by modules not yet wired to Supabase
//   2. SUPABASE DATA LAYER (bottom) — real DB calls for Settings, Profile, Crew, Documents
//
// The seed section is unchanged from the original prototype so nothing else breaks.

// =============================================================================
// 1. SAMPLE SEED DATA  (unchanged from prototype)
// =============================================================================

const TODAY = new Date();
const d = (offsetDays) => {
  const dt = new Date(TODAY);
  dt.setDate(dt.getDate() + offsetDays);
  return dt.toISOString().split('T')[0];
};

const SEED_LEADS = [
  { id: 'L001', name: 'Rachel Torres', phone: '(555) 201-4432', email: 'rachel@email.com', source: 'Google', stage: 'new', moveDate: d(14), fromCity: 'Austin, TX', toCity: 'Dallas, TX', size: '2br', value: 1450, created: d(-2), notes: 'Called twice, very interested. Needs weekend availability.' },
  { id: 'L002', name: 'James Kowalski', phone: '(555) 388-0192', email: 'jkowalski@gmail.com', source: 'Referral', stage: 'new', moveDate: d(21), fromCity: 'Round Rock, TX', toCity: 'San Antonio, TX', size: '3br', value: 2100, created: d(-1), notes: 'Referred by Rachel Torres. Has piano.' },
  { id: 'L003', name: 'Priya Nair', phone: '(555) 774-2201', email: 'priya.n@work.com', source: 'Yelp', stage: 'quoted', moveDate: d(10), fromCity: 'Austin, TX', toCity: 'Houston, TX', size: '1br', value: 975, created: d(-5), notes: 'Estimate sent 2 days ago. Following up Friday.' },
  { id: 'L004', name: 'Marcus Webb', phone: '(555) 509-3341', email: 'mwebb@outlook.com', source: 'Google', stage: 'quoted', moveDate: d(8), fromCity: 'Plano, TX', toCity: 'Austin, TX', size: '4br', value: 3200, created: d(-7), notes: 'Big job. Waiting on decision between us and competitor.' },
  { id: 'L005', name: 'Sofia Reyes', phone: '(555) 112-9987', email: 'sofia.r@gmail.com', source: 'Nextdoor', stage: 'booked', moveDate: d(5), fromCity: 'Austin, TX', toCity: 'Cedar Park, TX', size: '2br', value: 1100, created: d(-10), notes: 'Deposit paid $220. Confirmed crew: Mike + Dave.' },
  { id: 'L006', name: 'Derek Huang', phone: '(555) 667-4421', email: 'derek@techco.io', source: 'Google', stage: 'booked', moveDate: d(3), fromCity: 'Houston, TX', toCity: 'Austin, TX', size: 'office', value: 4800, created: d(-14), notes: 'Office relocation, 3 trucks needed. Contact is Karen in HR.' },
  { id: 'L007', name: 'Amanda Price', phone: '(555) 230-8876', email: 'amandaprice@email.com', source: 'Facebook', stage: 'completed', moveDate: d(-7), fromCity: 'Austin, TX', toCity: 'Nashville, TN', size: '3br', value: 5200, created: d(-30), notes: 'Long distance. Very happy with service, left 5-star review.' },
  { id: 'L008', name: 'Tyler Booth', phone: '(555) 445-1230', email: 'tbooth@email.com', source: 'Referral', stage: 'completed', moveDate: d(-12), fromCity: 'Dallas, TX', toCity: 'Fort Worth, TX', size: '1br', value: 780, created: d(-25), notes: 'Quick local move. Tipped crew $100.' },
  { id: 'L009', name: 'Nina Castillo', phone: '(555) 998-3301', email: 'nina.c@gmail.com', source: 'Google', stage: 'new', moveDate: d(30), fromCity: 'Austin, TX', toCity: 'San Marcos, TX', size: 'studio', value: 650, created: d(0), notes: 'Just inquired online. Left voicemail.' },
  { id: 'L010', name: 'Ben Okafor', phone: '(555) 321-7754', email: 'ben.ok@email.com', source: 'Google', stage: 'quoted', moveDate: d(18), fromCity: 'Pflugerville, TX', toCity: 'Austin, TX', size: '2br', value: 1300, created: d(-3), notes: '' },
];

const SEED_JOBS = [
  { id: 'J001', customer: 'Sofia Reyes', phone: '(555) 112-9987', date: d(5), time: '8:00 AM', from: 'Austin, TX', to: 'Cedar Park, TX', size: '2br', crew: ['Mike R.', 'Dave S.'], truck: 'Truck #2 (20ft)', status: 'confirmed', value: 1100, estimateId: 'MM-2026-041' },
  { id: 'J002', customer: 'Derek Huang', phone: '(555) 667-4421', date: d(3), time: '7:00 AM', from: 'Houston, TX', to: 'Austin, TX', size: 'office', crew: ['Carlos M.', 'Mike R.', 'Jalen T.'], truck: 'Truck #1 (26ft) + Truck #3', status: 'confirmed', value: 4800, estimateId: 'MM-2026-038' },
  { id: 'J003', customer: 'Priya Nair', phone: '(555) 774-2201', date: d(10), time: '9:00 AM', from: 'Austin, TX', to: 'Houston, TX', size: '1br', crew: ['Dave S.'], truck: 'Truck #4 (16ft)', status: 'pending', value: 975, estimateId: 'MM-2026-044' },
  { id: 'J004', customer: 'Rachel Torres', phone: '(555) 201-4432', date: d(14), time: '8:00 AM', from: 'Austin, TX', to: 'Dallas, TX', size: '2br', crew: ['Carlos M.', 'Jalen T.'], truck: 'Truck #2 (20ft)', status: 'pending', value: 1450, estimateId: 'MM-2026-047' },
  { id: 'J005', customer: 'Amanda Price', phone: '(555) 230-8876', date: d(-7), time: '7:30 AM', from: 'Austin, TX', to: 'Nashville, TN', size: '3br', crew: ['Mike R.', 'Dave S.', 'Carlos M.'], truck: 'Truck #1 (26ft)', status: 'completed', value: 5200, estimateId: 'MM-2026-029' },
  { id: 'J006', customer: 'Tyler Booth', phone: '(555) 445-1230', date: d(-12), time: '10:00 AM', from: 'Dallas, TX', to: 'Fort Worth, TX', size: '1br', crew: ['Jalen T.'], truck: 'Truck #4 (16ft)', status: 'completed', value: 780, estimateId: 'MM-2026-022' },
];

const SEED_CUSTOMERS = [
  { id: 'C001', name: 'Amanda Price', email: 'amandaprice@email.com', phone: '(555) 230-8876', address: 'Nashville, TN', jobs: 1, totalSpent: 5200, lastMove: d(-7), source: 'Facebook', rating: 5, tags: ['long-distance', 'vip'] },
  { id: 'C002', name: 'Sofia Reyes', email: 'sofia.r@gmail.com', phone: '(555) 112-9987', address: 'Cedar Park, TX', jobs: 1, totalSpent: 1100, lastMove: d(5), source: 'Nextdoor', rating: null, tags: ['residential'] },
  { id: 'C003', name: 'Derek Huang', email: 'derek@techco.io', phone: '(555) 667-4421', address: 'Austin, TX', jobs: 1, totalSpent: 4800, lastMove: d(3), source: 'Google', rating: null, tags: ['commercial', 'repeat'] },
  { id: 'C004', name: 'Tyler Booth', email: 'tbooth@email.com', phone: '(555) 445-1230', address: 'Fort Worth, TX', jobs: 2, totalSpent: 1560, lastMove: d(-12), source: 'Referral', rating: 5, tags: ['residential'] },
  { id: 'C005', name: 'Linda Grant', email: 'linda.g@email.com', phone: '(555) 881-2234', address: 'Austin, TX', jobs: 3, totalSpent: 6750, lastMove: d(-45), source: 'Referral', rating: 5, tags: ['vip', 'repeat'] },
  { id: 'C006', name: 'Marcus Webb', email: 'mwebb@outlook.com', phone: '(555) 509-3341', address: 'Plano, TX', jobs: 0, totalSpent: 0, lastMove: null, source: 'Google', rating: null, tags: ['lead'] },
];

const SEED_CREW = [
  { id: 'CR001', name: 'Mike Rodriguez', role: 'Lead Mover', phone: '(555) 301-9912', email: 'mike@crew.com', status: 'available', jobs: 42, rating: 4.9, avatar: 'MR' },
  { id: 'CR002', name: 'Dave Simmons', role: 'Mover', phone: '(555) 204-7743', email: 'dave@crew.com', status: 'on-job', jobs: 38, rating: 4.8, avatar: 'DS' },
  { id: 'CR003', name: 'Carlos Mendoza', role: 'Lead Mover', phone: '(555) 519-3321', email: 'carlos@crew.com', status: 'available', jobs: 55, rating: 4.9, avatar: 'CM' },
  { id: 'CR004', name: 'Jalen Thompson', role: 'Mover', phone: '(555) 774-0091', email: 'jalen@crew.com', status: 'day-off', jobs: 21, rating: 4.7, avatar: 'JT' },
];

const SEED_TRUCKS = [
  { id: 'T001', name: 'Truck #1', size: '26 ft', plate: 'TXK-4421', status: 'available', mileage: 48200, nextService: d(30) },
  { id: 'T002', name: 'Truck #2', size: '20 ft', plate: 'TXK-8893', status: 'deployed', mileage: 61450, nextService: d(15) },
  { id: 'T003', name: 'Truck #3', size: '20 ft', plate: 'TXK-2201', status: 'available', mileage: 33100, nextService: d(60) },
  { id: 'T004', name: 'Truck #4', size: '16 ft', plate: 'TXK-5510', status: 'maintenance', mileage: 72800, nextService: d(-3) },
];

const REVENUE_HISTORY = [
  { month: 'Nov', revenue: 28400, jobs: 18 },
  { month: 'Dec', revenue: 21200, jobs: 14 },
  { month: 'Jan', revenue: 19800, jobs: 13 },
  { month: 'Feb', revenue: 31600, jobs: 22 },
  { month: 'Mar', revenue: 38900, jobs: 26 },
  { month: 'Apr', revenue: 24330, jobs: 17 },
];

const SOURCE_BREAKDOWN = [
  { source: 'Google', leads: 41, pct: 38 },
  { source: 'Referral', leads: 29, pct: 27 },
  { source: 'Yelp', leads: 18, pct: 17 },
  { source: 'Nextdoor', leads: 11, pct: 10 },
  { source: 'Facebook', leads: 9, pct: 8 },
];

const PIPELINE_STAGES = [
  { id: 'new', label: 'New Lead', color: '#64748b', bg: '#f1f5f9' },
  { id: 'quoted', label: 'Quoted', color: '#0ea5e9', bg: '#e0f2fe' },
  { id: 'tentative', label: 'Tentative Hold', color: '#a855f7', bg: '#faf5ff' },
  { id: 'booked', label: 'Booked', color: '#f97316', bg: '#fff7ed' },
  { id: 'completed', label: 'Completed', color: '#22c55e', bg: '#f0fdf4' },
];

function fmtDate(str) {
  if (!str) return '—';
  const dt = new Date(str + 'T12:00');
  return dt.toLocaleDateString('en-US', { month: 'short', day: 'numeric', year: 'numeric' });
}
function fmtShortDate(str) {
  if (!str) return '—';
  const dt = new Date(str + 'T12:00');
  return dt.toLocaleDateString('en-US', { month: 'short', day: 'numeric' });
}
function daysUntil(str) {
  if (!str) return null;
  const dt = new Date(str + 'T12:00');
  const diff = Math.round((dt - TODAY) / 86400000);
  return diff;
}

// =============================================================================
// 2. SUPABASE DATA LAYER
// =============================================================================
//
// All functions are async and throw on error — wrap calls in try/catch in UI.
// Functions return camelCase objects (DB uses snake_case); translation is done here.

// ─── Defaults used when a setting hasn't been configured yet ───

const DEFAULT_PRICING_TIERS = [
  { crewSize: 2, hourlyRate: 150 },
  { crewSize: 3, hourlyRate: 195 },
  { crewSize: 4, hourlyRate: 240 },
];

// Labor-only rates per crew size. Lower than full-service since there's no truck/owner overhead.
// 2-man baseline = $139 (per Gavin); other sizes scaled in $50 steps. All editable in Settings.
const DEFAULT_LABOR_ONLY_TIERS = [
  { crewSize: 1, hourlyRate: 89 },
  { crewSize: 2, hourlyRate: 139 },
  { crewSize: 3, hourlyRate: 189 },
  { crewSize: 4, hourlyRate: 239 },
];

// Lead sources Gavin can pick from when adding a lead. Editable in Settings.
const DEFAULT_LEAD_SOURCES = [
  'Google', 'Thumbtack', 'Referral', 'Yelp', 'Facebook', 'Nextdoor', 'Website', 'Other',
];

const DEFAULT_SURCHARGES = [
  { id: 'stairs',     name: 'Stairs (per flight)',         defaultAmount: 25,  type: 'per_unit', unit: 'flight' },
  { id: 'piano',      name: 'Piano / safe',                defaultAmount: 175, type: 'flat',     unit: 'item' },
  { id: 'heavy',      name: 'Heavy item (300+ lbs)',       defaultAmount: 75,  type: 'flat',     unit: 'item' },
  { id: 'long_carry', name: 'Long carry (over 75 ft)',     defaultAmount: 50,  type: 'flat',     unit: 'flat' },
  { id: 'packing',    name: 'Packing labor',               defaultAmount: 65,  type: 'per_hour', unit: 'hour' },
  { id: 'fuel',       name: 'Fuel surcharge (%)',          defaultAmount: 5,   type: 'percent',  unit: '%' },
];

const SETTINGS_EXTRA_DEFAULTS = {
  address: '',
  phone: '',
  businessEmail: '',
  usdotNumber: '',
  mcNumber: '',
  serviceArea: '',
  avgWeightPerMoverLbs: 1500,
  depositPct: 20,
  cancellationWindowHours: 48,
  paymentTermsDays: 0,
  pricingTiers: DEFAULT_PRICING_TIERS,
  laborOnlyTiers: DEFAULT_LABOR_ONLY_TIERS,
  defaultTripFee: 50,
  surcharges: DEFAULT_SURCHARGES,
  minimumHours: 2,
  travelBillingRule: 'double_drive',  // 'double_drive' | 'included' | 'per_mile'
  perMileRate: 0,
  leadSources: DEFAULT_LEAD_SOURCES,
};

// ─── Helper: require a live Supabase client ───
function _sb() {
  if (!window.sb) throw new Error('Supabase client not initialized. Refresh the page.');
  return window.sb;
}

function _currentUserId() {
  return window.currentUser?.id || null;
}

// ─── SETTINGS (single-row table, id=1) ───

async function loadSettings() {
  const { data, error } = await _sb()
    .from('settings')
    .select('*')
    .eq('id', 1)
    .single();
  if (error) throw error;

  const extra = { ...SETTINGS_EXTRA_DEFAULTS, ...(data.extra || {}) };
  return {
    companyName: data.company_name || 'MaxAMove',
    hourlyRate: Number(data.hourly_rate) || 150,     // legacy default rate (still used as fallback)
    taxRate: Number(data.tax_rate) || 0,
    emailFrom: data.email_from || '',
    smsFromNumber: data.sms_from_number || '',
    // extra jsonb fields:
    address: extra.address,
    phone: extra.phone,
    businessEmail: extra.businessEmail,
    usdotNumber: extra.usdotNumber,
    mcNumber: extra.mcNumber,
    serviceArea: extra.serviceArea,
    avgWeightPerMoverLbs: Number(extra.avgWeightPerMoverLbs) || 1500,
    depositPct: Number(extra.depositPct) || 0,
    cancellationWindowHours: Number(extra.cancellationWindowHours) || 48,
    paymentTermsDays: Number(extra.paymentTermsDays) || 0,
    pricingTiers: Array.isArray(extra.pricingTiers) && extra.pricingTiers.length
      ? extra.pricingTiers
      : DEFAULT_PRICING_TIERS,
    laborOnlyTiers: Array.isArray(extra.laborOnlyTiers) && extra.laborOnlyTiers.length
      ? extra.laborOnlyTiers
      : DEFAULT_LABOR_ONLY_TIERS,
    defaultTripFee: extra.defaultTripFee != null ? Number(extra.defaultTripFee) : 50,
    surcharges: Array.isArray(extra.surcharges) && extra.surcharges.length
      ? extra.surcharges
      : DEFAULT_SURCHARGES,
    minimumHours: Number(extra.minimumHours) || 2,
    travelBillingRule: extra.travelBillingRule || 'double_drive',
    perMileRate: Number(extra.perMileRate) || 0,
    leadSources: Array.isArray(extra.leadSources) && extra.leadSources.length
      ? extra.leadSources
      : DEFAULT_LEAD_SOURCES,
    updatedAt: data.updated_at,
  };
}

async function saveSettings(patch) {
  // Partition patch into first-class columns vs extra-jsonb fields
  const firstClass = {};
  const extraPatch = {};
  const FIRST_CLASS = ['companyName', 'hourlyRate', 'taxRate', 'emailFrom', 'smsFromNumber'];
  const F2C = {
    companyName: 'company_name',
    hourlyRate: 'hourly_rate',
    taxRate: 'tax_rate',
    emailFrom: 'email_from',
    smsFromNumber: 'sms_from_number',
  };
  for (const [k, v] of Object.entries(patch)) {
    if (FIRST_CLASS.includes(k)) firstClass[F2C[k]] = v;
    else extraPatch[k] = v;
  }

  // Load current extra to merge
  const { data: current, error: readErr } = await _sb()
    .from('settings')
    .select('extra')
    .eq('id', 1)
    .single();
  if (readErr) throw readErr;
  const mergedExtra = { ...(current.extra || {}), ...extraPatch };

  const updateRow = { ...firstClass, extra: mergedExtra, updated_at: new Date().toISOString() };

  const { error } = await _sb()
    .from('settings')
    .update(updateRow)
    .eq('id', 1);
  if (error) throw error;

  // Fire-and-forget audit log (don't block on failure)
  logAudit('settings_update', 'settings', null, null, patch).catch(() => {});

  return loadSettings();
}

// ─── MY PROFILE (the logged-in user's own row) ───

async function loadMyProfile() {
  const userId = _currentUserId();
  if (!userId) throw new Error('Not signed in');

  const { data, error } = await _sb()
    .from('profiles')
    .select('*')
    .eq('id', userId)
    .single();
  if (error) throw error;

  return {
    id: data.id,
    fullName: data.full_name || '',
    email: data.email || window.currentUser?.email || '',
    role: data.role,
    phone: data.phone || '',
    avatarUrl: data.avatar_url || '',
    active: data.active !== false,
    extra: data.extra || {},
    createdAt: data.created_at,
  };
}

async function saveMyProfile(patch) {
  const userId = _currentUserId();
  if (!userId) throw new Error('Not signed in');

  const update = { updated_at: new Date().toISOString() };
  if (patch.fullName !== undefined) update.full_name = patch.fullName;
  if (patch.phone !== undefined) update.phone = patch.phone;
  if (patch.avatarUrl !== undefined) update.avatar_url = patch.avatarUrl;

  const { error } = await _sb().from('profiles').update(update).eq('id', userId);
  if (error) throw error;

  logAudit('profile_update', 'profile', userId, null, patch).catch(() => {});
  return loadMyProfile();
}

async function changeMyPassword(newPassword) {
  if (!newPassword || newPassword.length < 6) {
    throw new Error('Password must be at least 6 characters.');
  }
  const { error } = await _sb().auth.updateUser({ password: newPassword });
  if (error) throw error;
  logAudit('password_changed', 'user', _currentUserId()).catch(() => {});
  return true;
}

// ─── TEAM (owner-only management) ───

async function listTeamMembers() {
  // Uses the RPC we created — only callable by owner
  const { data, error } = await _sb().rpc('list_team_members');
  if (error) throw error;
  return (data || []).map(row => ({
    id: row.id,
    fullName: row.full_name || '',
    email: row.email || '',
    role: row.role,
    phone: row.phone || '',
    avatarUrl: row.avatar_url || '',
    active: row.active !== false,
    createdAt: row.created_at,
  }));
}

async function updateTeamMemberRole(userId, newRole) {
  const VALID = ['owner', 'staff', 'crew', 'customer', 'viewer'];
  if (!VALID.includes(newRole)) throw new Error('Invalid role: ' + newRole);

  const { error } = await _sb()
    .from('profiles')
    .update({ role: newRole, updated_at: new Date().toISOString() })
    .eq('id', userId);
  if (error) throw error;

  logAudit('role_change', 'profile', userId, null, { role: newRole }).catch(() => {});
  return true;
}

async function setTeamMemberActive(userId, active) {
  const { error } = await _sb()
    .from('profiles')
    .update({ active: !!active, updated_at: new Date().toISOString() })
    .eq('id', userId);
  if (error) throw error;

  logAudit(active ? 'profile_activate' : 'profile_deactivate', 'profile', userId).catch(() => {});
  return true;
}

// ─── CREW MEMBERS ───

async function listCrew({ includeInactive = false } = {}) {
  let q = _sb().from('crew_members').select('*').order('name');
  if (!includeInactive) q = q.eq('active', true);
  const { data, error } = await q;
  if (error) throw error;
  return (data || []).map(_crewRowToUI);
}

async function createCrewMember(input) {
  const baselineRate = input.lbsPerHourBaseline != null
    ? Number(input.lbsPerHourBaseline)
    : (window.DEFAULT_LBS_PER_HOUR_PER_MOVER || 382);
  const row = {
    name: (input.name || '').trim(),
    role: input.role || 'mover',
    phone: input.phone || null,
    email: input.email || null,
    hourly_rate: input.hourlyRate != null ? Number(input.hourlyRate) : null,
    active: input.active !== false,
    extra: {
      ...(input.extra || {}),
      ...(input.capacityWeightLbs != null ? { capacityWeightLbs: Number(input.capacityWeightLbs) } : {}),
      ...(input.notes ? { notes: input.notes } : {}),
      // Per-mover learning fields
      lbsPerHourBaseline: baselineRate,
      lbsPerHourLearned: input.lbsPerHourLearned != null ? Number(input.lbsPerHourLearned) : null,
      jobsAnalyzedCount: 0,
    },
  };
  if (!row.name) throw new Error('Crew member name is required.');
  const { data, error } = await _sb().from('crew_members').insert(row).select('*').single();
  if (error) throw error;
  logAudit('crew_create', 'crew_member', data.id, null, row).catch(() => {});
  return _crewRowToUI(data);
}

async function updateCrewMember(id, patch) {
  const update = {};
  if (patch.name !== undefined) update.name = patch.name;
  if (patch.role !== undefined) update.role = patch.role;
  if (patch.phone !== undefined) update.phone = patch.phone;
  if (patch.email !== undefined) update.email = patch.email;
  if (patch.hourlyRate !== undefined) update.hourly_rate = Number(patch.hourlyRate);
  if (patch.active !== undefined) update.active = !!patch.active;

  // Extra jsonb merge
  const extraPatch = {};
  if (patch.capacityWeightLbs !== undefined) extraPatch.capacityWeightLbs = Number(patch.capacityWeightLbs);
  if (patch.notes !== undefined) extraPatch.notes = patch.notes;
  if (patch.lbsPerHourBaseline !== undefined) extraPatch.lbsPerHourBaseline = Number(patch.lbsPerHourBaseline);
  if (patch.lbsPerHourLearned !== undefined) extraPatch.lbsPerHourLearned = patch.lbsPerHourLearned == null ? null : Number(patch.lbsPerHourLearned);
  if (patch.jobsAnalyzedCount !== undefined) extraPatch.jobsAnalyzedCount = Number(patch.jobsAnalyzedCount);
  if (patch.lbsPerHourLastUpdated !== undefined) extraPatch.lbsPerHourLastUpdated = patch.lbsPerHourLastUpdated;
  // Crew HQ portal password — stored alongside other crew_member metadata so the
  // owner can rebuild the one-tap deep link any time without resetting the
  // worker's Supabase Auth account.
  if (patch.portalPassword !== undefined) extraPatch.portalPassword = patch.portalPassword;
  // Auth email used for Crew HQ. Falls back to the crew member's real email
  // when one exists, but for crew without an email we generate a synthetic
  // identifier so they can still get a one-tap link.
  if (patch.portalEmail !== undefined) extraPatch.portalEmail = patch.portalEmail;
  if (Object.keys(extraPatch).length) {
    const { data: cur, error: rErr } = await _sb().from('crew_members').select('extra').eq('id', id).single();
    if (rErr) throw rErr;
    update.extra = { ...(cur.extra || {}), ...extraPatch };
  }

  const { data, error } = await _sb().from('crew_members').update(update).eq('id', id).select('*').single();
  if (error) throw error;
  logAudit('crew_update', 'crew_member', id, null, patch).catch(() => {});
  return _crewRowToUI(data);
}

async function deactivateCrewMember(id) {
  return updateCrewMember(id, { active: false });
}

function _crewRowToUI(row) {
  const extra = row.extra || {};
  const initials = (row.name || '')
    .split(/\s+/).filter(Boolean).slice(0, 2)
    .map(s => s[0]).join('').toUpperCase();
  return {
    id: row.id,
    name: row.name,
    role: row.role,
    phone: row.phone || '',
    email: row.email || '',
    hourlyRate: row.hourly_rate != null ? Number(row.hourly_rate) : null,
    rating: row.rating != null ? Number(row.rating) : null,
    jobCount: row.job_count || 0,
    active: row.active !== false,
    avatar: initials || '?',
    capacityWeightLbs: extra.capacityWeightLbs != null ? Number(extra.capacityWeightLbs) : null,
    notes: extra.notes || '',
    // Per-mover learning fields
    lbsPerHourBaseline: extra.lbsPerHourBaseline != null
      ? Number(extra.lbsPerHourBaseline)
      : (window.DEFAULT_LBS_PER_HOUR_PER_MOVER || 382),
    lbsPerHourLearned: extra.lbsPerHourLearned != null ? Number(extra.lbsPerHourLearned) : null,
    jobsAnalyzedCount: extra.jobsAnalyzedCount != null ? Number(extra.jobsAnalyzedCount) : 0,
    lbsPerHourLastUpdated: extra.lbsPerHourLastUpdated || null,
    // Effective rate to use for estimating (learned if we have data, otherwise baseline)
    effectiveLbsPerHour: extra.lbsPerHourLearned != null ? Number(extra.lbsPerHourLearned)
      : extra.lbsPerHourBaseline != null ? Number(extra.lbsPerHourBaseline)
      : (window.DEFAULT_LBS_PER_HOUR_PER_MOVER || 382),
    portalPassword: extra.portalPassword || null,
    portalEmail: extra.portalEmail || null,
    createdAt: row.created_at,
  };
}

// ─── CREW HQ PORTAL ACCESS HELPERS ───
// Generate a random password that's friendly to read off a phone screen.
// (Skips lookalike chars 0/O, 1/l/I.)
function generateWorkerPassword(len = 12) {
  const chars = 'ABCDEFGHJKLMNPQRSTUVWXYZabcdefghjkmnpqrstuvwxyz23456789';
  let s = '';
  for (let i = 0; i < len; i++) s += chars.charAt(Math.floor(Math.random() * chars.length));
  return s;
}

// Build the one-tap deep link that signs the crew member into Crew HQ. The
// hash format mirrors the customer-portal pattern (#t=...) so existing share
// flows feel familiar: worker.html#wp=<base64(email:password)>
function buildWorkerPortalLink(email, password) {
  if (!email || !password) return null;
  const token = btoa(email + ':' + password).replace(/\+/g, '-').replace(/\//g, '_').replace(/=+$/, '');
  const url = new URL('worker.html', window.location.href);
  url.hash = 'wp=' + token;
  return url.href;
}

// Convenience: get the live deep link for a crew_member UI object. Prefers the
// stored auth email (which may be synthetic) over the human-facing email so the
// link always matches the auth account that was actually created.
function getWorkerPortalLink(crewMember) {
  if (!crewMember || !crewMember.portalPassword) return null;
  const email = crewMember.portalEmail || crewMember.email;
  if (!email) return null;
  return buildWorkerPortalLink(email, crewMember.portalPassword);
}

// Build a deterministic-ish synthetic email for crew members who don't have a
// real one. The address is purely an internal Supabase Auth identifier —
// crew never types or sees it; they just tap their link. Format keeps the
// crew member's name slug + a short id slice for human readability if Gavin
// ever needs to look it up in the Supabase dashboard.
function _syntheticPortalEmail(crewMember) {
  const slug = (crewMember.name || 'crew')
    .toLowerCase()
    .replace(/[^a-z0-9]+/g, '-')
    .replace(/^-+|-+$/g, '')
    .slice(0, 20) || 'crew';
  const idTail = String(crewMember.id || '').replace(/-/g, '').slice(0, 8) || Math.random().toString(36).slice(2, 10);
  return `crew-${slug}-${idTail}@hq.maxamove.local`;
}

// First-time portal access: generate a password, create the Supabase Auth user
// using a TEMP client (so Gavin's main session isn't disturbed), and persist
// the password + auth email back onto the crew_member row. Email is OPTIONAL —
// for crew members without one we generate a synthetic identifier so they can
// still get a one-tap link.
//
// We pass `crew_member_id` in user_metadata at signUp so the worker portal can
// resolve the auth user back to a crew_members row WITHOUT relying on email
// matching. That keeps the email field purely cosmetic.
//
// Returns { link, existed, password }.
//   - existed === true → auth user was already created (e.g. via Supabase
//     dashboard or a previous attempt). Caller should warn that the password
//     we just generated won't actually sign them in unless Gavin sets it
//     manually in Supabase → Authentication.
async function ensureWorkerPortalAccess(crewMember) {
  if (!crewMember || !crewMember.id) {
    throw new Error('Crew member must be saved before generating portal access.');
  }
  // If we already have a stored password, rebuild and return — the auth user
  // was created earlier, no need to sign up again.
  if (crewMember.portalPassword) {
    const link = getWorkerPortalLink(crewMember);
    return { link, existed: false, regenerated: false };
  }
  const password = generateWorkerPassword();
  // Use the real email if there is one, otherwise generate a synthetic
  // identifier. Either way, persist it as `portalEmail` so Settings can rebuild
  // the link reliably even if Gavin later edits the human email field.
  const portalEmail = (crewMember.email && crewMember.email.includes('@'))
    ? crewMember.email
    : _syntheticPortalEmail(crewMember);
  const tempClient = window.supabase.createClient(
    window.SUPABASE_URL, window.SUPABASE_ANON_KEY,
    { auth: { persistSession: false, autoRefreshToken: false } }
  );
  let existed = false;
  try {
    const { error } = await tempClient.auth.signUp({
      email: portalEmail,
      password,
      options: {
        // Stuffing crew_member_id into user_metadata lets the worker portal
        // resolve the signed-in user back to a crew_members row without
        // relying on the email field (which may be missing or rotated).
        data: { crew_member_id: crewMember.id, crew_name: crewMember.name || null },
      },
    });
    if (error) {
      if (/already (registered|exists)|registered already/i.test(error.message)) {
        existed = true;
      } else {
        throw error;
      }
    }
  } catch (e) {
    if (!/already (registered|exists)/i.test(e.message || '')) throw e;
    existed = true;
  }
  await updateCrewMember(crewMember.id, { portalPassword: password, portalEmail });
  return {
    link: buildWorkerPortalLink(portalEmail, password),
    existed,
    password,
  };
}

window.generateWorkerPassword = generateWorkerPassword;
window.buildWorkerPortalLink = buildWorkerPortalLink;
window.getWorkerPortalLink = getWorkerPortalLink;
window.ensureWorkerPortalAccess = ensureWorkerPortalAccess;

// ─── CREW PERFORMANCE LEARNING ───
// Called when a job completes — distributes the actual lbs/hour evenly across
// each assigned crew member and updates their rolling weighted average.
// Lower confidence (fewer jobs) means new data has more impact.
async function recordCrewJobPerformance(crewMemberId, lbsContributed, hoursWorked) {
  if (!crewMemberId || !lbsContributed || !hoursWorked || hoursWorked <= 0) return null;
  try {
    const { data: cur, error: rErr } = await _sb().from('crew_members').select('extra').eq('id', crewMemberId).single();
    if (rErr || !cur) return null;
    const extra = cur.extra || {};
    const oldRate = extra.lbsPerHourLearned != null
      ? Number(extra.lbsPerHourLearned)
      : (extra.lbsPerHourBaseline != null ? Number(extra.lbsPerHourBaseline) : (window.DEFAULT_LBS_PER_HOUR_PER_MOVER || 382));
    const oldCount = Number(extra.jobsAnalyzedCount || 0);
    const thisJobRate = lbsContributed / hoursWorked;
    const newCount = oldCount + 1;
    // Weighted moving average — every job counts equally
    const newRate = (oldRate * oldCount + thisJobRate) / newCount;
    return updateCrewMember(crewMemberId, {
      lbsPerHourLearned: Math.round(newRate),
      jobsAnalyzedCount: newCount,
      lbsPerHourLastUpdated: new Date().toISOString(),
    });
  } catch (e) {
    console.warn('[crew-learning] failed to update', crewMemberId, e);
    return null;
  }
}

// Combined throughput for an assembled crew (sum of each member's effective rate).
// Falls back to the generic DEFAULT_LBS_PER_HOUR_PER_MOVER × N when no specific
// crew is assigned or the assignment is partial.
function calcCrewThroughput(crewMembers, fallbackCount) {
  const def = (window.DEFAULT_LBS_PER_HOUR_PER_MOVER || 382);
  if (!Array.isArray(crewMembers) || crewMembers.length === 0) {
    return (fallbackCount || 0) * def;
  }
  return crewMembers.reduce((sum, m) => {
    if (!m) return sum + def;
    const rate = m.lbsPerHourLearned != null ? Number(m.lbsPerHourLearned)
      : m.lbsPerHourBaseline != null ? Number(m.lbsPerHourBaseline)
      : def;
    return sum + rate;
  }, 0);
}

// ─── DOCUMENT TEMPLATES ───

async function listDocumentTemplates({ includeInactive = false } = {}) {
  let q = _sb().from('document_templates').select('*').order('sort_order').order('title');
  if (!includeInactive) q = q.eq('active', true);
  const { data, error } = await q;
  if (error) throw error;
  return (data || []).map(_docRowToUI);
}

async function uploadDocumentTemplate(file, metadata) {
  if (!file) throw new Error('No file selected.');
  if (!metadata.title) throw new Error('Document title is required.');
  if (!metadata.category) throw new Error('Category is required.');

  // Storage path: documents/<timestamp>-<safeName>
  const safeName = file.name.replace(/[^a-zA-Z0-9_.-]/g, '_');
  const storagePath = `${Date.now()}-${safeName}`;

  // 1. Upload the file
  const { error: upErr } = await _sb().storage
    .from('documents')
    .upload(storagePath, file, { upsert: false, contentType: file.type });
  if (upErr) throw upErr;

  // 2. Create metadata row
  const row = {
    title: metadata.title.trim(),
    category: metadata.category,
    description: metadata.description || null,
    file_path: storagePath,
    file_name: file.name,
    file_size: file.size,
    mime_type: file.type,
    attach_to_estimates: !!metadata.attachToEstimates,
    attach_to_bols: !!metadata.attachToBols,
    attach_to_invoices: !!metadata.attachToInvoices,
    show_in_portal: !!metadata.showInPortal,
    requires_signature: !!metadata.requiresSignature,
    sort_order: metadata.sortOrder || 0,
    active: metadata.active !== false,
    uploaded_by: _currentUserId(),
  };

  const { data, error } = await _sb().from('document_templates').insert(row).select('*').single();
  if (error) {
    // Rollback the storage upload so we don't leak orphan files
    await _sb().storage.from('documents').remove([storagePath]).catch(() => {});
    throw error;
  }

  logAudit('document_upload', 'document_template', data.id, null, row).catch(() => {});
  return _docRowToUI(data);
}

async function updateDocumentTemplate(id, patch) {
  const update = {};
  const MAP = {
    title: 'title',
    category: 'category',
    description: 'description',
    attachToEstimates: 'attach_to_estimates',
    attachToBols: 'attach_to_bols',
    attachToInvoices: 'attach_to_invoices',
    showInPortal: 'show_in_portal',
    requiresSignature: 'requires_signature',
    sortOrder: 'sort_order',
    active: 'active',
  };
  for (const [k, v] of Object.entries(patch)) {
    if (MAP[k]) update[MAP[k]] = v;
  }
  const { data, error } = await _sb()
    .from('document_templates')
    .update(update)
    .eq('id', id)
    .select('*')
    .single();
  if (error) throw error;

  logAudit('document_update', 'document_template', id, null, patch).catch(() => {});
  return _docRowToUI(data);
}

async function deleteDocumentTemplate(id, { hard = false } = {}) {
  if (!hard) {
    // Soft delete — mark inactive but keep file in storage for audit
    return updateDocumentTemplate(id, { active: false });
  }
  // Hard delete — remove file from storage AND delete row
  const { data: row, error: rErr } = await _sb()
    .from('document_templates')
    .select('file_path')
    .eq('id', id)
    .single();
  if (rErr) throw rErr;

  if (row?.file_path) {
    await _sb().storage.from('documents').remove([row.file_path]).catch(() => {});
  }
  const { error } = await _sb().from('document_templates').delete().eq('id', id);
  if (error) throw error;

  logAudit('document_delete', 'document_template', id).catch(() => {});
  return true;
}

async function getDocumentDownloadUrl(filePath, expiresInSec = 3600) {
  const { data, error } = await _sb().storage
    .from('documents')
    .createSignedUrl(filePath, expiresInSec);
  if (error) throw error;
  return data.signedUrl;
}

function _docRowToUI(row) {
  return {
    id: row.id,
    title: row.title,
    category: row.category,
    description: row.description || '',
    filePath: row.file_path,
    fileName: row.file_name || '',
    fileSize: row.file_size || 0,
    mimeType: row.mime_type || '',
    attachToEstimates: !!row.attach_to_estimates,
    attachToBols: !!row.attach_to_bols,
    attachToInvoices: !!row.attach_to_invoices,
    showInPortal: !!row.show_in_portal,
    requiresSignature: !!row.requires_signature,
    sortOrder: row.sort_order || 0,
    active: row.active !== false,
    version: row.version || 1,
    uploadedBy: row.uploaded_by,
    createdAt: row.created_at,
    updatedAt: row.updated_at,
  };
}

// ─── AUDIT LOG (fire-and-forget) ───

async function logAudit(action, entityType = null, entityId = null, before = null, after = null) {
  try {
    const row = {
      user_id: _currentUserId(),
      action,
      entity_type: entityType,
      entity_id: entityId,
      before,
      after,
      metadata: {},
    };
    const { error } = await _sb().from('audit_log').insert(row);
    if (error && console?.warn) console.warn('[audit]', action, error.message);
  } catch (e) {
    if (console?.warn) console.warn('[audit]', action, e.message);
  }
}

// ─── DOCUMENT CATEGORY LABELS (for UI) ───

const DOCUMENT_CATEGORIES = [
  { id: 'estimate_terms',   label: 'Estimate Terms',        helper: 'Terms attached to estimates/quotes' },
  { id: 'job_contract',     label: 'Job Contract',          helper: 'Service agreement for booked jobs' },
  { id: 'bol_terms',        label: 'BOL Terms',             helper: 'Terms printed on Bills of Lading' },
  { id: 'insurance_waiver', label: 'Insurance Waiver',      helper: 'Valuation & insurance options' },
  { id: 'liability_release',label: 'Liability Release',     helper: 'Release-of-liability forms' },
  { id: 'inventory_sheet',  label: 'Inventory Sheet',       helper: 'Pre-printed inventory checklists' },
  { id: 'valuation_form',   label: 'Valuation Form',        helper: 'Declared valuation coverage forms' },
  { id: 'claims_form',      label: 'Claims Form',           helper: 'Damage/loss claim forms' },
  { id: 'other',            label: 'Other',                 helper: 'Anything else' },
];

// ─────────────────────────────────────────────────────────────────────
// CUSTOMERS
// ─────────────────────────────────────────────────────────────────────

// Strip all non-digits from a phone string so storage is consistent
// (e.g. "(615) 743-5288" → "6157435288"). Used on every customer/lead
// write so the de-dupe lookup actually matches.
function _normalizePhone(p) {
  if (p == null) return null;
  const digits = String(p).replace(/\D/g, '');
  return digits || null;
}

async function listCustomers() {
  const { data, error } = await _sb()
    .from('customers')
    .select('*')
    .order('name');
  if (error) throw error;
  return (data || []).map(_customerRowToUI);
}

async function getCustomer(id) {
  const { data, error } = await _sb().from('customers').select('*').eq('id', id).single();
  if (error) throw error;
  return _customerRowToUI(data);
}

async function findCustomerByContact({ email, phone, name }) {
  // Find an existing customer by email OR phone (most reliable identifiers).
  // Returns null if none found.
  // Phone match: tries digits-only (new-style storage) AND XXX-XXXX legacy
  // formatting so customers created before phone-normalization landed still
  // de-dupe correctly.
  const filters = [];
  if (email && email.trim()) filters.push(`email.eq.${email.trim().toLowerCase()}`);
  if (phone && String(phone).trim()) {
    const digits = String(phone).replace(/\D/g, '');
    if (digits.length >= 7) {
      const last7  = digits.slice(-7);
      const last4  = digits.slice(-4);
      const last3a = digits.slice(-7, -4);
      filters.push(`phone.like.%${last7}%`);                 // digits-only stored
      filters.push(`phone.like.%${last3a}-${last4}%`);       // legacy "XXX-XXXX"
      filters.push(`phone.like.%${last3a} ${last4}%`);       // legacy "XXX XXXX"
    }
  }
  if (filters.length === 0) return null;

  const { data, error } = await _sb()
    .from('customers')
    .select('*')
    .or(filters.join(','))
    .limit(1);
  if (error) throw error;
  return data?.[0] ? _customerRowToUI(data[0]) : null;
}

async function getOrCreateCustomer(input) {
  // Looks up an existing customer by email/phone. Creates a new one if no match.
  if (!input.name && !input.firstName) throw new Error('Customer name is required.');

  const fullName = input.name || `${input.firstName || ''} ${input.lastName || ''}`.trim();
  const existing = await findCustomerByContact({ email: input.email, phone: input.phone });
  if (existing) {
    // Optionally enrich the existing record with any new info
    const patch = {};
    if (input.address && !existing.address) patch.address = input.address;
    if (input.source && !existing.source) patch.source = input.source;
    if (Object.keys(patch).length) await updateCustomer(existing.id, patch);
    return existing;
  }
  return createCustomer({
    name: fullName,
    firstName: input.firstName,
    lastName: input.lastName,
    email: input.email,
    phone: input.phone,
    address: input.address || input.currentAddress,
    source: input.source,
    notes: input.notes,
    tags: input.tags,
  });
}

async function createCustomer(input) {
  const fullName = input.name || `${input.firstName || ''} ${input.lastName || ''}`.trim();
  if (!fullName) throw new Error('Customer name is required.');

  const extra = { ...(input.extra || {}) };
  if (input.firstName) extra.firstName = input.firstName;
  if (input.lastName) extra.lastName = input.lastName;
  if (input.contactPref) extra.contactPref = input.contactPref;

  const row = {
    name: fullName,
    email: input.email ? input.email.toLowerCase() : null,
    phone: _normalizePhone(input.phone),
    address: input.address || null,
    source: input.source || null,
    rating: input.rating || null,
    tags: input.tags || [],
    notes: input.notes || null,
    extra,
  };
  const { data, error } = await _sb().from('customers').insert(row).select('*').single();
  if (error) throw error;
  logAudit('customer_create', 'customer', data.id, null, row).catch(() => {});
  return _customerRowToUI(data);
}

async function updateCustomer(id, patch) {
  const update = { updated_at: new Date().toISOString() };
  const MAP = {
    name: 'name', email: 'email', phone: 'phone', address: 'address',
    source: 'source', rating: 'rating', tags: 'tags', notes: 'notes',
  };
  for (const [k, v] of Object.entries(patch)) {
    if (MAP[k]) update[MAP[k]] = (k === 'phone') ? _normalizePhone(v) : v;
  }
  // Handle extra-jsonb merge
  const extraPatch = {};
  if (patch.firstName !== undefined) extraPatch.firstName = patch.firstName;
  if (patch.lastName !== undefined) extraPatch.lastName = patch.lastName;
  if (patch.contactPref !== undefined) extraPatch.contactPref = patch.contactPref;
  if (Object.keys(extraPatch).length) {
    const { data: cur } = await _sb().from('customers').select('extra').eq('id', id).single();
    update.extra = { ...(cur?.extra || {}), ...extraPatch };
  }

  const { data, error } = await _sb().from('customers').update(update).eq('id', id).select('*').single();
  if (error) throw error;
  logAudit('customer_update', 'customer', id, null, patch).catch(() => {});
  return _customerRowToUI(data);
}

async function deleteCustomer(id) {
  // Soft-delete via tag — we don't truly delete because of FK constraints
  return updateCustomer(id, { tags: ['deleted'] });
}

function _customerRowToUI(row) {
  const extra = row.extra || {};
  return {
    id: row.id,
    name: row.name,
    firstName: extra.firstName || (row.name?.split(/\s+/)[0] || ''),
    lastName: extra.lastName || (row.name?.split(/\s+/).slice(1).join(' ') || ''),
    email: row.email || '',
    phone: row.phone || '',
    address: row.address || '',
    source: row.source || '',
    rating: row.rating || null,
    tags: row.tags || [],
    notes: row.notes || '',
    totalSpent: row.total_spent != null ? Number(row.total_spent) : 0,
    jobs: row.job_count || 0,           // legacy UI key
    jobCount: row.job_count || 0,
    lastMove: row.last_move_at || null,
    contactPref: extra.contactPref || 'email',
    extra,
    createdAt: row.created_at,
  };
}

// ─────────────────────────────────────────────────────────────────────
// LEADS
// ─────────────────────────────────────────────────────────────────────

async function listLeads() {
  const { data, error } = await _sb()
    .from('leads')
    .select('*')
    .order('created_at', { ascending: false });
  if (error) throw error;
  return (data || []).map(_leadRowToUI);
}

async function createLead(input) {
  if (!input.name) throw new Error('Lead name is required.');

  // Auto-create a customer in the background if email or phone is provided
  let customerId = input.customerId || null;
  if (!customerId && (input.email || input.phone)) {
    try {
      const cust = await getOrCreateCustomer({
        name: input.name,
        email: input.email,
        phone: input.phone,
        source: input.source,
      });
      customerId = cust.id;
    } catch (e) {
      console.warn('[createLead] customer auto-create failed', e.message);
    }
  }

  const row = {
    customer_id: customerId,
    name: input.name.trim(),
    phone: _normalizePhone(input.phone),
    email: input.email ? input.email.toLowerCase() : null,
    source: input.source || null,
    stage: input.stage || 'new',
    move_date: input.moveDate || null,
    from_address: input.fromCity || input.fromAddress || null,
    to_address: input.toCity || input.toAddress || null,
    size: input.size || null,
    estimated_value: input.value != null ? Number(input.value) : null,
    cost_of_lead: input.costOfLead != null && input.costOfLead !== '' ? Number(input.costOfLead) : null,
    notes: input.notes || null,
    extra: input.extra || {},
  };

  const { data, error } = await _sb().from('leads').insert(row).select('*').single();
  if (error) throw error;
  logAudit('lead_create', 'lead', data.id, null, row).catch(() => {});
  return _leadRowToUI(data);
}

async function updateLead(id, patch) {
  const update = { updated_at: new Date().toISOString() };
  const MAP = {
    name: 'name', phone: 'phone', email: 'email', source: 'source',
    stage: 'stage', notes: 'notes',
    moveDate: 'move_date',
    fromCity: 'from_address', fromAddress: 'from_address',
    toCity: 'to_address',     toAddress: 'to_address',
    size: 'size',
    value: 'estimated_value',
    costOfLead: 'cost_of_lead',
    customerId: 'customer_id',
  };
  for (const [k, v] of Object.entries(patch)) {
    if (MAP[k]) {
      if (k === 'phone') update[MAP[k]] = _normalizePhone(v);
      else update[MAP[k]] = v === '' ? null : v;
    }
  }
  const { data, error } = await _sb().from('leads').update(update).eq('id', id).select('*').single();
  if (error) throw error;
  logAudit('lead_update', 'lead', id, null, patch).catch(() => {});
  return _leadRowToUI(data);
}

async function moveLeadStage(id, newStage) {
  return updateLead(id, { stage: newStage });
}

async function deleteLead(id) {
  const { error } = await _sb().from('leads').delete().eq('id', id);
  if (error) throw error;
  logAudit('lead_delete', 'lead', id).catch(() => {});
  return true;
}

function _leadRowToUI(row) {
  return {
    id: row.id,
    customerId: row.customer_id || null,
    name: row.name,
    phone: row.phone || '',
    email: row.email || '',
    source: row.source || '',
    stage: row.stage || 'new',
    moveDate: row.move_date || '',
    fromCity: row.from_address || '',
    toCity: row.to_address || '',
    fromAddress: row.from_address || '',
    toAddress: row.to_address || '',
    size: row.size || '',
    value: row.estimated_value != null ? Number(row.estimated_value) : 0,
    costOfLead: row.cost_of_lead != null ? Number(row.cost_of_lead) : null,
    notes: row.notes || '',
    created: row.created_at ? row.created_at.split('T')[0] : '',
    createdAt: row.created_at,
    extra: row.extra || {},
  };
}

// ─────────────────────────────────────────────────────────────────────
// TRUCKS
// ─────────────────────────────────────────────────────────────────────

async function listTrucks() {
  const { data, error } = await _sb().from('trucks').select('*').order('name');
  if (error) throw error;
  return (data || []).map(_truckRowToUI);
}

async function createTruck(input) {
  if (!input.name) throw new Error('Truck name is required.');
  const row = {
    name: input.name.trim(),
    size: input.size || null,
    plate: input.plate || null,
    status: input.status || 'available',
    mileage: input.mileage != null ? Number(input.mileage) : 0,
    capacity_cuft: input.capacityCuft != null ? Number(input.capacityCuft) : null,
    capacity_lbs: input.capacityLbs != null ? Number(input.capacityLbs) : null,
    next_service_date: input.nextService || null,
    notes: input.notes || null,
    extra: input.extra || {},
  };
  const { data, error } = await _sb().from('trucks').insert(row).select('*').single();
  if (error) throw error;
  logAudit('truck_create', 'truck', data.id, null, row).catch(() => {});
  return _truckRowToUI(data);
}

async function updateTruck(id, patch) {
  const update = { updated_at: new Date().toISOString() };
  const MAP = {
    name: 'name', size: 'size', plate: 'plate', status: 'status',
    mileage: 'mileage',
    nextService: 'next_service_date',
    capacityCuft: 'capacity_cuft',
    capacityLbs: 'capacity_lbs',
    notes: 'notes',
  };
  for (const [k, v] of Object.entries(patch)) {
    if (MAP[k]) update[MAP[k]] = v === '' ? null : v;
  }
  const { data, error } = await _sb().from('trucks').update(update).eq('id', id).select('*').single();
  if (error) throw error;
  logAudit('truck_update', 'truck', id, null, patch).catch(() => {});
  return _truckRowToUI(data);
}

async function listTruckSizePresets() {
  const { data, error } = await _sb()
    .from('truck_size_presets')
    .select('*')
    .order('sort_order');
  if (error) throw error;
  return (data || []).map(r => ({
    id: r.id,
    label: r.label,
    capacityCuft: Number(r.capacity_cuft),
    capacityLbs: Number(r.capacity_lbs),
  }));
}

async function deleteTruck(id) {
  // Soft-delete by setting status to 'out-of-service'
  return updateTruck(id, { status: 'out-of-service' });
}

function _truckRowToUI(row) {
  return {
    id: row.id,
    name: row.name,
    size: row.size || '',
    plate: row.plate || '',
    status: row.status || 'available',
    mileage: row.mileage || 0,
    capacityCuft: row.capacity_cuft != null ? Number(row.capacity_cuft) : null,
    capacityLbs: row.capacity_lbs != null ? Number(row.capacity_lbs) : null,
    notes: row.notes || '',
    nextService: row.next_service_date || null,
    label: row.size ? `${row.name} (${row.size})` : row.name,  // for UI dropdowns
    extra: row.extra || {},
    createdAt: row.created_at,
  };
}

// ─────────────────────────────────────────────────────────────────────
// JOBS
// ─────────────────────────────────────────────────────────────────────

async function listJobs() {
  // Fetch jobs with embedded customer/truck/estimate refs.
  // crew_ids is an array of UUIDs — resolve to names client-side from a single crew query.
  const [jobsResp, crewResp] = await Promise.all([
    _sb().from('jobs').select(`
      *,
      customers (id, name, phone, email, address),
      trucks (id, name, size, plate),
      estimates (id, number)
    `).order('scheduled_date', { ascending: true, nullsFirst: false }),
    _sb().from('crew_members').select('id, name')
  ]);
  if (jobsResp.error) throw jobsResp.error;
  if (crewResp.error) throw crewResp.error;

  // Self-heal: find jobs whose crew_ids is empty but whose linked estimate has
  // crew assigned in pricing.crewIds (the source-of-truth — that's where the
  // wizard's crew picker writes). Repairs older jobs created before the
  // crew-on-create wiring landed so each crew member's Crew HQ portal sees
  // their upcoming moves. Single batched fetch, single update per stale job.
  // Only fills when EMPTY, never overwrites a job's existing crew assignment.
  const stale = (jobsResp.data || []).filter(
    j => (!j.crew_ids || j.crew_ids.length === 0) && j.estimate_id
  );
  if (stale.length) {
    try {
      const estIds = stale.map(j => j.estimate_id);
      const { data: ests } = await _sb().from('estimates')
        .select('id, pricing')
        .in('id', estIds);
      const estCrewById = new Map(
        (ests || []).map(e => [e.id, Array.isArray(e.pricing?.crewIds) ? e.pricing.crewIds : []])
      );
      await Promise.all(stale.map(async j => {
        const ids = estCrewById.get(j.estimate_id) || [];
        if (ids.length === 0) return;
        const { error } = await _sb().from('jobs')
          .update({ crew_ids: ids, updated_at: new Date().toISOString() })
          .eq('id', j.id);
        if (error) {
          console.warn('[listJobs] crew backfill failed for job', j.id, error.message);
          return;
        }
        j.crew_ids = ids;  // reflect in the in-memory row so the next render is correct
      }));
    } catch (e) {
      console.warn('[listJobs] crew backfill skipped:', e.message);
    }
  }

  const crewMap = new Map((crewResp.data || []).map(c => [c.id, c.name]));
  return (jobsResp.data || []).map(row => _jobRowToUI(row, crewMap));
}

async function createJob(input) {
  // Resolve customer: either an existing customerId, or auto-create from name/email/phone
  let customerId = input.customerId || null;
  if (!customerId && (input.customer || input.customerName || input.email)) {
    const cust = await getOrCreateCustomer({
      name: input.customer || input.customerName,
      email: input.email,
      phone: input.phone,
    });
    customerId = cust.id;
  }
  if (!customerId) throw new Error('Customer is required for a job.');
  if (!input.scheduledDate && !input.date) throw new Error('Scheduled date is required.');

  const number = input.number || await generateJobNumber();
  const row = {
    number,
    estimate_id: input.estimateUuid || input.estimateId || null,
    customer_id: customerId,
    scheduled_date: input.scheduledDate || input.date,
    scheduled_time: input.scheduledTime || input.time || null,
    from_address: input.fromAddress || input.from || null,
    to_address: input.toAddress || input.to || null,
    size: input.size || null,
    truck_id: input.truckId || null,
    crew_ids: input.crewIds || [],
    status: input.status || 'pending',
    contracted_value: input.value != null ? Number(input.value) : null,
    notes: input.notes || null,
    extra: input.extra || {},
  };
  const { data, error } = await _sb().from('jobs').insert(row).select('*, customers (id, name, phone), trucks (id, name, size)').single();
  if (error) throw error;
  logAudit('job_create', 'job', data.id, null, row).catch(() => {});
  // Refetch to get full denormalized row
  const list = await listJobs();
  return list.find(j => j.id === data.id) || _jobRowToUI(data, new Map());
}

async function updateJob(id, patch) {
  const update = { updated_at: new Date().toISOString() };
  const MAP = {
    customerId: 'customer_id',
    scheduledDate: 'scheduled_date', date: 'scheduled_date',
    scheduledTime: 'scheduled_time', time: 'scheduled_time',
    fromAddress: 'from_address', from: 'from_address',
    toAddress: 'to_address', to: 'to_address',
    size: 'size',
    truckId: 'truck_id',
    crewIds: 'crew_ids',
    status: 'status',
    notes: 'notes',
    value: 'contracted_value',
    finalValue: 'final_value',
    estimateUuid: 'estimate_id',
  };
  // Anything NOT in MAP gets folded into the JSONB `extra` column so we can
  // extend per-job data (accuracy tracking, etc.) without DB migrations.
  const extraPatch = {};
  for (const [k, v] of Object.entries(patch)) {
    if (MAP[k]) update[MAP[k]] = v === '' ? null : v;
    else extraPatch[k] = v;
  }
  if (Object.keys(extraPatch).length) {
    const { data: cur } = await _sb().from('jobs').select('extra').eq('id', id).single();
    update.extra = { ...(cur?.extra || {}), ...extraPatch };
  }
  const { error } = await _sb().from('jobs').update(update).eq('id', id);
  if (error) throw error;
  logAudit('job_update', 'job', id, null, patch).catch(() => {});
  // Refetch the full job row
  const list = await listJobs();
  return list.find(j => j.id === id);
}

async function deleteJob(id) {
  const { error } = await _sb().from('jobs').delete().eq('id', id);
  if (error) throw error;
  logAudit('job_delete', 'job', id).catch(() => {});
  return true;
}

async function generateJobNumber() {
  const year = new Date().getFullYear();
  const prefix = `J-${year}-`;
  const { data, error } = await _sb()
    .from('jobs')
    .select('number')
    .like('number', prefix + '%')
    .order('number', { ascending: false })
    .limit(1);
  if (error) throw error;
  const last = data?.[0]?.number || '';
  const lastN = parseInt(last.split('-').pop()) || 0;
  return `${prefix}${String(lastN + 1).padStart(3, '0')}`;
}

function _jobRowToUI(row, crewMap) {
  const cust = row.customers || {};
  const truck = row.trucks || {};
  const est = row.estimates || {};
  const crewIds = row.crew_ids || [];
  const crewNames = crewIds.map(id => (crewMap && crewMap.get(id)) || '?').filter(n => n !== '?');
  const truckLabel = truck.name ? (truck.size ? `${truck.name} (${truck.size})` : truck.name) : '';

  return {
    id: row.id,
    number: row.number || '',
    customer: cust.name || '—',
    customerId: cust.id || row.customer_id || null,
    phone: cust.phone || '',
    email: cust.email || '',
    address: cust.address || '',
    date: row.scheduled_date || '',
    time: row.scheduled_time ? row.scheduled_time.slice(0, 5) : '',
    scheduledDate: row.scheduled_date || '',
    scheduledTime: row.scheduled_time || '',
    from: row.from_address || '',
    to: row.to_address || '',
    fromAddress: row.from_address || '',
    toAddress: row.to_address || '',
    size: row.size || '',
    crew: crewNames,
    crewIds,
    truck: truckLabel,
    truckId: truck.id || row.truck_id || null,
    status: row.status || 'pending',
    value: row.contracted_value != null ? Number(row.contracted_value) : 0,
    contractedValue: row.contracted_value != null ? Number(row.contracted_value) : 0,
    finalValue: row.final_value != null ? Number(row.final_value) : null,
    estimateId: est.number || '',
    estimateUuid: row.estimate_id || est.id || null,
    notes: row.notes || '',
    extra: row.extra || {},
    // Accuracy tracking — surfaced from extra blob for convenience
    actualBillableHours: row.extra && row.extra.actualBillableHours != null ? Number(row.extra.actualBillableHours) : null,
    actualTotalHours: row.extra && row.extra.actualTotalHours != null ? Number(row.extra.actualTotalHours) : null,
    completedAt: row.extra && row.extra.completedAt ? row.extra.completedAt : null,
    createdAt: row.created_at,
  };
}

// ─────────────────────────────────────────────────────────────────────
// ESTIMATES
// ─────────────────────────────────────────────────────────────────────

async function listEstimates() {
  const { data, error } = await _sb()
    .from('estimates')
    .select(`
      *,
      customers (id, name, email, phone, address)
    `)
    .order('created_at', { ascending: false });
  if (error) throw error;
  return (data || []).map(_estimateRowToUI);
}

async function loadEstimate(id) {
  const { data, error } = await _sb()
    .from('estimates')
    .select(`*, customers (id, name, email, phone, address, extra), leads (id, name)`)
    .eq('id', id)
    .single();
  if (error) throw error;
  return _estimateRowToUI(data);
}

async function generateEstimateNumber() {
  const year = new Date().getFullYear();
  const prefix = `MM-${year}-`;
  // Start counter at 438 so the first real estimate looks established (not #001).
  // Future estimates auto-increment from whatever's actually in the database.
  const STARTING_NUMBER = 437;
  // Pull ALL estimates with this year's prefix. We need to find the max NUMERICALLY,
  // not alphabetically — Postgres text sort treats "716" > "0717" because '7' > '0',
  // which causes duplicate-number collisions when padding is inconsistent across rows.
  const { data, error } = await _sb()
    .from('estimates')
    .select('number')
    .like('number', prefix + '%');
  if (error) throw error;
  const maxN = (data || []).reduce((m, row) => {
    const n = parseInt((row.number || '').split('-').pop()) || 0;
    return n > m ? n : m;
  }, 0);
  const next = Math.max(maxN, STARTING_NUMBER) + 1;
  return `${prefix}${String(next).padStart(4, '0')}`;
}

async function saveEstimate(state, { id = null, leadId = null } = {}) {
  // state is the prototype's full estimate state shape:
  //   { step, status, estimateNum, customer, move, inventory, pricing }
  // Persists to estimates table; auto-creates/links a customers row.

  const cust = state.customer || {};
  const hasName = `${cust.firstName || ''} ${cust.lastName || ''}`.trim() || (cust.email || '').trim();
  // For NEW estimates require name AND contact info so getOrCreateCustomer can dedupe.
  // Without contact info, every save creates a fresh customer row (the bug that produced
  // 27 orphan "Gavin smith" rows). Updates to existing estimates skip this check.
  if (!id) {
    const hasContact = (cust.email || '').trim() || (cust.phone || '').trim();
    if (!hasName) throw new Error('Customer name is required to save the estimate.');
    if (!hasContact) throw new Error('Customer email or phone is required to save the estimate.');
  }

  const customer = await getOrCreateCustomer({
    firstName: cust.firstName,
    lastName: cust.lastName,
    name: `${cust.firstName || ''} ${cust.lastName || ''}`.trim() || cust.email || 'Unnamed',
    email: cust.email,
    phone: cust.phone,
    address: cust.currentAddress,
    source: cust.source,
    contactPref: cust.contactPref,
    notes: cust.notes,
  });

  // Auto-create a Lead row when an estimate is started fresh from the Estimates page
  // (no leadId passed in, and we're inserting — not updating). Reuses an existing lead
  // for this customer if there is one, so we don't dupe.
  let effectiveLeadId = leadId;
  if (!id && !effectiveLeadId && customer && customer.id) {
    try {
      const { data: existingLead } = await _sb()
        .from('leads').select('id').eq('customer_id', customer.id)
        .order('created_at', { ascending: false }).limit(1).maybeSingle();
      if (existingLead) {
        effectiveLeadId = existingLead.id;
      } else {
        const newLead = await createLead({
          customerId: customer.id,
          name: `${cust.firstName || ''} ${cust.lastName || ''}`.trim() || cust.email || customer.name || 'Unnamed',
          email: cust.email,
          phone: cust.phone,
          source: cust.source || 'estimate-direct',
          stage: 'quoted',
          moveDate: state.move?.moveDate,
          fromAddress: state.move?.fromAddress,
          toAddress: state.move?.toAddress,
          size: state.move?.moveSize,
        });
        effectiveLeadId = newLead.id;
      }
    } catch (e) {
      console.warn('[saveEstimate] auto-create lead failed', e.message);
    }
  }

  const number = state.estimateNum || await generateEstimateNumber();
  const row = {
    number,
    customer_id: customer.id,
    lead_id: effectiveLeadId || null,
    status: state.status || 'draft',
    move_data: state.move || {},
    inventory: state.inventory || [],
    pricing: state.pricing || {},
    extra: state.extra || {},
  };

  // Compute the live total so we can mirror it onto the lead and (if booked) the job.
  // Falls back to the stored pricing.total if calcTotals isn't loaded (e.g. background job).
  let estTotal = 0;
  try {
    if (typeof window !== 'undefined' && typeof window.calcTotals === 'function') {
      estTotal = Number(window.calcTotals(state.pricing || {}, state.move || {}).total) || 0;
    } else {
      estTotal = Number((state.pricing || {}).total) || 0;
    }
  } catch { estTotal = Number((state.pricing || {}).total) || 0; }

  let saved;
  if (id) {
    const { data, error } = await _sb().from('estimates').update({ ...row, updated_at: new Date().toISOString() }).eq('id', id).select(`*, customers (id, name, email, phone, address, extra)`).single();
    if (error) throw error;
    logAudit('estimate_update', 'estimate', id, null, { status: row.status }).catch(() => {});
    saved = data;
  } else {
    const { data, error } = await _sb().from('estimates').insert(row).select(`*, customers (id, name, email, phone, address, extra)`).single();
    if (error) throw error;
    logAudit('estimate_create', 'estimate', data.id, null, { number }).catch(() => {});
    saved = data;
  }

  // Mirror the estimate total onto the linked lead's estimated_value so the Leads
  // pipeline and detail drawer reflect the latest quote without manual editing.
  if (saved && saved.lead_id && estTotal > 0) {
    _sb().from('leads')
      .update({ estimated_value: estTotal, updated_at: new Date().toISOString() })
      .eq('id', saved.lead_id)
      .then(({ error: e }) => { if (e) console.warn('[saveEstimate] lead value sync failed', e.message); });
  }
  // Mirror onto any Job already created from this estimate so the Jobs board "Value"
  // column tracks edits to the underlying estimate.
  if (saved && saved.id && estTotal > 0) {
    _sb().from('jobs')
      .update({ contracted_value: estTotal, updated_at: new Date().toISOString() })
      .eq('estimate_id', saved.id)
      .then(({ error: e }) => { if (e) console.warn('[saveEstimate] job value sync failed', e.message); });
  }

  // Mirror the assigned crew onto any linked Job so each crew member's Crew HQ
  // portal sees the upcoming move. Without this the job's crew_ids stays empty
  // when crew is added/changed on the estimate after booking.
  // Crew is stored in pricing.crewIds (the wizard's crew picker writes there).
  if (saved && saved.id) {
    const estCrewIds = Array.isArray(saved.pricing?.crewIds) ? saved.pricing.crewIds : [];
    _sb().from('jobs')
      .update({ crew_ids: estCrewIds, updated_at: new Date().toISOString() })
      .eq('estimate_id', saved.id)
      .then(({ error: e }) => { if (e) console.warn('[saveEstimate] job crew sync failed', e.message); });
  }

  return _estimateRowToUI(saved);
}

async function updateEstimateStatus(id, status, extras = {}) {
  const patch = { status, updated_at: new Date().toISOString(), ...extras };
  if (status === 'sent' && !extras.sent_at) patch.sent_at = new Date().toISOString();
  if (status === 'signed' && !extras.signed_at) patch.signed_at = new Date().toISOString();
  const { data, error } = await _sb().from('estimates').update(patch).eq('id', id).select(`*, customers (id, name, email, phone, address, extra)`).single();
  if (error) throw error;
  logAudit('estimate_status', 'estimate', id, null, { status }).catch(() => {});
  return _estimateRowToUI(data);
}

async function deleteEstimate(id) {
  const { error } = await _sb().from('estimates').delete().eq('id', id);
  if (error) throw error;
  logAudit('estimate_delete', 'estimate', id).catch(() => {});
  return true;
}

function _estimateRowToUI(row) {
  const cust = row.customers || {};
  const custExtra = cust.extra || {};
  return {
    id: row.id,                                     // Supabase UUID — different from estimateNum
    estimateUuid: row.id,                           // alias for clarity
    estimateNum: row.number || '',                  // human-readable e.g. MM-2026-001
    status: row.status || 'draft',
    leadId: row.lead_id || null,
    customerId: row.customer_id || null,
    customer: {
      firstName: custExtra.firstName || (cust.name?.split(/\s+/)[0] || ''),
      lastName: custExtra.lastName || (cust.name?.split(/\s+/).slice(1).join(' ') || ''),
      email: cust.email || '',
      phone: cust.phone || '',
      currentAddress: cust.address || '',
      contactPref: custExtra.contactPref || 'email',
      source: '',  // could pull from cust if desired
      notes: '',
    },
    move: row.move_data || {
      moveDate: '', moveSize: '',
      fromAddress: '', fromFloor: 'ground', fromElevator: 'yes',
      toAddress: '', toFloor: 'ground', toElevator: 'yes',
      distance: '', packing: 'none', specialInstructions: '',
    },
    inventory: row.inventory || [],
    pricing: row.pricing || {
      movers: 2, hours: 4, hourlyRate: 150,
      truckSize: 'medium', truckFee: 200, fuelRate: 0.85,
      insuranceType: 'none', insuranceFee: '', insuranceValue: '', insurancePct: 0.6,
      packingFee: '', discount: '', discountType: 'flat', internalNotes: '',
    },
    step: 1,    // always start at step 1 when reloaded
    sentAt: row.sent_at,
    signedAt: row.signed_at,
    expiresAt: row.expires_at,
    pdfUrl: row.pdf_url,
    createdAt: row.created_at,
    updatedAt: row.updated_at,
  };
}

// ─── Expose to the rest of the app ───

Object.assign(window, {
  // Seed data (kept for any module not yet wired to Supabase)
  SEED_LEADS, SEED_JOBS, SEED_CUSTOMERS, SEED_CREW, SEED_TRUCKS,
  REVENUE_HISTORY, SOURCE_BREAKDOWN, PIPELINE_STAGES,
  fmtDate, fmtShortDate, daysUntil, TODAY,

  // Settings / Profile / Team / Crew / Documents (Phase 1)
  DEFAULT_PRICING_TIERS,
  DEFAULT_SURCHARGES,
  DEFAULT_LEAD_SOURCES,
  DOCUMENT_CATEGORIES,
  loadSettings, saveSettings,
  loadMyProfile, saveMyProfile, changeMyPassword,
  listTeamMembers, updateTeamMemberRole, setTeamMemberActive,
  listCrew, createCrewMember, updateCrewMember, deactivateCrewMember,
  recordCrewJobPerformance, calcCrewThroughput,
  listDocumentTemplates, uploadDocumentTemplate, updateDocumentTemplate,
  deleteDocumentTemplate, getDocumentDownloadUrl,
  logAudit,

  // Phase 2 — Customers / Leads / Jobs / Trucks / Estimates
  listCustomers, getCustomer, findCustomerByContact, getOrCreateCustomer,
  createCustomer, updateCustomer, deleteCustomer,
  listLeads, createLead, updateLead, moveLeadStage, deleteLead,
  listTrucks, createTruck, updateTruck, deleteTruck, listTruckSizePresets,
  listJobs, createJob, updateJob, deleteJob, generateJobNumber,
  listEstimates, loadEstimate, saveEstimate, updateEstimateStatus,
  deleteEstimate, generateEstimateNumber,
});