Low-Level Design

pending
  ├── confirmed   (receptionist approves + assigns staff)
  ├── rejected    (receptionist rejects)
  └── cancelled   (customer cancels before confirmation)

confirmed
  ├── in_progress  (service started)
  ├── cancelled    (customer cancels, within window)
  ├── rescheduled  (customer reschedules)
  └── no_show      (customer didn't arrive, +15min after end_time)

in_progress
  └── completed    (service done, invoice generated, gems awarded)

const GST_RATE = 0.18

function splitGST(inclusivePaise: number) {
  const basePaise = Math.round(inclusivePaise / (1 + GST_RATE))
  const gstPaise = inclusivePaise - basePaise
  const cgstPaise = Math.floor(gstPaise / 2)   // CGST = half of GST
  const sgstPaise = gstPaise - cgstPaise         // SGST = remainder
  return { basePaise, gstPaise, cgstPaise, sgstPaise, totalPaise: inclusivePaise }
}

// Earn rate: 1 gem per ₹100 invoiced (floor)
function calculateGemsEarned(invoiceTotalPaise: number): number {
  return Math.floor(invoiceTotalPaise / 10000)  // 10000 paise = ₹100
}

// Only on invoice_type = 'service'
// NOT on membership purchases or membership sessions
// Gems expire 365 days from earn date

-- Booking queries
CREATE INDEX idx_booking_customer ON booking(customer_id);
CREATE INDEX idx_booking_branch_date ON booking(branch_id, booking_date);
CREATE INDEX idx_booking_status ON booking(status);

-- Availability queries
CREATE INDEX idx_booking_staff_date ON booking(assigned_staff_id, booking_date)
  WHERE status IN ('confirmed', 'in_progress');

-- Membership queries
CREATE INDEX idx_membership_customer_active ON spa_membership(customer_id)
  WHERE status = 'active';

-- Loyalty queries
CREATE INDEX idx_loyalty_tx_account ON loyalty_transaction(loyalty_account_id);
CREATE INDEX idx_loyalty_tx_expiry ON loyalty_transaction(expires_at)
  WHERE transaction_type = 'earn' AND is_expired = false;

-- Lead queries
CREATE INDEX idx_lead_status ON lead(status);
CREATE INDEX idx_lead_source ON lead(source);