# Plan: #45 Loyalty Stamp Redemption + Discount/Sales System

## Context

Crussell already has:
- `loyalty_stamps` INT column on `users` table (default 0)
- Stamp award logic in `ProgressBookingHandler` (`bookings.go` lines 1566-1579): +1 stamp on booking completion, with 1-day dedup
- `payment_method` enum already includes `'discount'`
- Account page shows "X stamps until 10% off" and "Congratulations! You've earned 10% off!" when stamps >= 10
- No existing promo code, discount campaign, or redemption infrastructure
- Customer Relationship view shows `totalVisits`, `firstVisitDate`, `customerFor` — useful for milestone eligibility checks

## User Requirements

1. **Loyalty auto-redeem**: When stamps reach 10, next *completed* booking gets 10% off automatically
2. **Discount applied at completion**, not booking creation (booking may be cancelled)
3. **Customer UI**: No exact discount prices shown (too many variables). When card is full, show info that next appointment is discounted
4. **Email notification**: When card becomes full, email customer informing them next appointment is discounted (TODO — email system TBD)
5. **Discount campaigns**: Admin can create time-based sales (e.g., "Easter Sale 5% off weekend", "Opening week 10% off first appointment")
6. **Milestone discounts**: Admin can create milestone-based campaigns:
   - **Per-user milestones**: "Xth booking" for a specific customer (e.g., 100th booking → 15% off)
   - **Global milestones**: "Xth booking overall" across all customers (e.g., our 1000th booking → 10% off for that customer)
   - **Anniversary milestones**: First booking after hitting 6 months, 1 year, 2 years since customer's first booking
7. **Discount tracking**: Need a system for managing discounts and sales broadly

## Design Decisions

### Campaign Types

Two distinct campaign types with different eligibility logic:

**Time-Based Campaigns** (existing plan):
- Eligibility checked at booking *creation* (date range match)
- Applied at booking *completion*
- Examples: "Easter Sale 5% off", "Opening week 10% off first appointment"

**Milestone Campaigns** (new):
- Eligibility checked at booking *completion* (when the milestone is actually reached)
- Applied immediately upon completion
- Three sub-types:
  - **Per-user milestone**: "100th booking" — counts completed bookings per user. When user hits the target, discount applies.
  - **Global milestone**: "1000th booking overall" — counts all completed bookings across all users. When the global counter hits the target, that booking gets the discount.
  - **Anniversary milestone**: "6 months since first booking" — checks time elapsed since user's first completed booking. First booking after crossing the threshold gets the discount.

### Discount Application Timing

- **Loyalty discount**: Applied at booking *completion* (status → "completed"). At that point, calculate 10% of booking total, create a `discount` payment_method record for the discount amount, deduct stamps.
- **Time-based campaign discount**: Applied at booking *completion*. Eligibility checked at booking creation (date range), but actual application happens at completion.
- **Milestone campaign discount**: Applied at booking *completion*. Eligibility is only determinable at completion (that's when the milestone is reached).

### Why Completion-Time Application?

If applied at booking creation:
- Customer books → discount reserved → customer cancels → discount lost or needs rollback
- Multiple pre-booked appointments create ambiguity about which one gets the discount

At completion:
- Only actual completed appointments get discounts
- No cancellation edge cases
- Clean accounting

### Global Booking Counter

For "Xth booking overall" milestones, we query `SELECT COUNT(*) FROM bookings WHERE status = 'completed'` at completion time. If the count is 99, the current booking is the 100th. No dedicated counter table — the query is fast enough with an index on `status`.

### Anniversary Milestone Logic

For "6 months since first booking" campaigns:
- At booking completion: check if user's first completed booking was ≥ 6 months ago AND they have no prior anniversary discount of this type applied
- Use `booking_discounts` table to check if this specific anniversary milestone has already been claimed by this user
- Only applies once per milestone per user

### Campaign "First Appointment" Logic

For campaigns like "10% off first appointment":
- At booking creation: check if user has any existing completed bookings. If not, mark booking as eligible for campaign discount.
- At completion: apply the discount.

### Customer UI Principle

The account page should NOT show "You'll save £12.50" because:
- Services may change between booking and completion
- Admin may override prices
- Multiple bookings may be pending

Instead: "Your next completed appointment will receive a 10% loyalty discount."

For milestone campaigns, the customer doesn't need to know in advance — the discount is a surprise applied at completion. Admin can optionally notify via email (TODO).

---

## Database Schema Changes

### 1. `loyalty_redemptions` Table

Tracks when stamps are converted into a pending discount.

```sql
CREATE TABLE loyalty_redemptions (
    id CHAR(12) PRIMARY KEY DEFAULT generate_short_id('loyalty_redemptions'),
    user_id CHAR(12) NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    stamps_redeemed INT NOT NULL DEFAULT 10,
    status VARCHAR(20) NOT NULL DEFAULT 'pending', -- 'pending', 'applied', 'expired'
    applied_to_booking_id CHAR(12) REFERENCES bookings(id) ON DELETE SET NULL,
    redeemed_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    applied_at TIMESTAMPTZ,
    expires_at TIMESTAMPTZ NOT NULL DEFAULT (NOW() + INTERVAL '6 months')
);

CREATE INDEX idx_loyalty_redemptions_user ON loyalty_redemptions(user_id);
CREATE INDEX idx_loyalty_redemptions_status ON loyalty_redemptions(status);
```

**Lifecycle**:
1. When stamps reach 10 → auto-create row with `status = 'pending'`
2. When booking completes with redemption → `status = 'applied'`, `applied_to_booking_id` set
3. After 6 months unused → `status = 'expired'` (via cron or on-demand check)

### 2. `discount_campaigns` Table

Admin-managed promotional campaigns (time-based and milestone-based).

```sql
CREATE TYPE campaign_type AS ENUM ('time_based', 'milestone');
CREATE TYPE milestone_type AS ENUM ('per_user_booking_count', 'global_booking_count', 'anniversary');
CREATE TYPE milestone_unit AS ENUM ('bookings', 'months', 'years');
CREATE TYPE discount_campaign_scope AS ENUM ('all_bookings', 'first_booking_only', 'new_customers_only');
CREATE TYPE discount_campaign_status AS ENUM ('draft', 'active', 'completed', 'cancelled');

CREATE TABLE discount_campaigns (
    id CHAR(12) PRIMARY KEY DEFAULT generate_short_id('discount_campaigns'),
    name VARCHAR(100) NOT NULL,                    -- "Easter Sale 2026" or "100th Booking Milestone"
    description TEXT,                               -- "5% off all services this weekend"
    campaign_type campaign_type NOT NULL DEFAULT 'time_based',
    discount_percent NUMERIC(5,2) NOT NULL,         -- 5.00, 10.00, etc.
    
    -- Time-based fields (used when campaign_type = 'time_based')
    scope discount_campaign_scope,                  -- 'all_bookings', 'first_booking_only', etc.
    start_date TIMESTAMPTZ,                         -- NULL for milestone campaigns
    end_date TIMESTAMPTZ,                           -- NULL for milestone campaigns
    
    -- Milestone fields (used when campaign_type = 'milestone')
    milestone_type milestone_type,                  -- 'per_user_booking_count', 'global_booking_count', 'anniversary'
    milestone_value INT,                            -- Target number (e.g., 100 for 100th booking, 6 for 6 months)
    milestone_unit milestone_unit,                  -- 'bookings', 'months', 'years'
    
    -- Common fields
    status discount_campaign_status NOT NULL DEFAULT 'draft',
    max_redemptions INT,                            -- NULL = unlimited. For global milestones, this is the total count cap.
    times_redeemed INT NOT NULL DEFAULT 0,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by CHAR(12) REFERENCES users(id) ON DELETE SET NULL,
    
    CONSTRAINT chk_dates CHECK (campaign_type = 'milestone' OR (start_date IS NOT NULL AND end_date IS NOT NULL AND end_date > start_date)),
    CONSTRAINT chk_discount CHECK (discount_percent > 0 AND discount_percent <= 100),
    CONSTRAINT chk_milestone CHECK (campaign_type = 'time_based' OR (milestone_type IS NOT NULL AND milestone_value IS NOT NULL AND milestone_unit IS NOT NULL))
);

CREATE INDEX idx_discount_campaigns_dates ON discount_campaigns(start_date, end_date) WHERE start_date IS NOT NULL;
CREATE INDEX idx_discount_campaigns_status ON discount_campaigns(status);
CREATE INDEX idx_discount_campaigns_type ON discount_campaigns(campaign_type);
```

**Milestone Examples**:

| Campaign | campaign_type | milestone_type | milestone_value | milestone_unit |
|----------|--------------|----------------|-----------------|----------------|
| "100th booking for each customer" | `milestone` | `per_user_booking_count` | 100 | `bookings` |
| "Our 1000th booking overall" | `milestone` | `global_booking_count` | 1000 | `bookings` |
| "6 months since first visit" | `milestone` | `anniversary` | 6 | `months` |
| "1 year anniversary" | `milestone` | `anniversary` | 1 | `years` |
| "Easter Sale 5% off" | `time_based` | NULL | NULL | NULL |

### 2b. Global Booking Count (no dedicated table)

For "Xth booking overall" milestones, we query at completion time:

```sql
SELECT COUNT(*) FROM bookings WHERE status = 'completed';
```

If the count is 99, the booking being completed is the 100th. No dedicated counter table needed — the query is fast enough (indexed on `status`) and avoids a separate table. At completion, we check all active `global_booking_count` milestone campaigns to see if the current count matches any milestone value.

### 3. `booking_discounts` Table

Tracks which discounts were applied to which bookings.

```sql
CREATE TABLE booking_discounts (
    id CHAR(12) PRIMARY KEY DEFAULT generate_short_id('booking_discounts'),
    booking_id CHAR(12) NOT NULL REFERENCES bookings(id) ON DELETE CASCADE,
    user_id CHAR(12) NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    discount_source VARCHAR(30) NOT NULL,  -- 'loyalty' or 'campaign'
    source_id CHAR(12),                    -- loyalty_redemption_id or campaign_id (nullable for loyalty)
    campaign_type campaign_type,           -- 'time_based' or 'milestone' (NULL for loyalty)
    milestone_type milestone_type,         -- NULL unless campaign_type = 'milestone'
    discount_percent NUMERIC(5,2) NOT NULL,
    original_total NUMERIC(10,2) NOT NULL, -- booking total before discount
    discount_amount NUMERIC(10,2) NOT NULL,
    applied_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_booking_discounts_booking ON booking_discounts(booking_id);
CREATE INDEX idx_booking_discounts_source ON booking_discounts(discount_source, source_id);
CREATE INDEX idx_booking_discounts_user ON booking_discounts(user_id);
CREATE INDEX idx_booking_discounts_milestone ON booking_discounts(user_id, milestone_type, source_id);
```

The `user_id` and `milestone_type` columns enable anniversary dedup: check if a user already has a `booking_discounts` row with `milestone_type = 'anniversary'` and `source_id = <campaign_id>` before applying.

### 4. Alter `bookings` Table

```sql
ALTER TABLE bookings ADD COLUMN discount_eligible BOOLEAN NOT NULL DEFAULT FALSE;
```

This flag is set at booking creation time if:
- User has a pending loyalty redemption, OR
- An active campaign applies to this booking

---

## Backend Implementation

### Phase 1: Loyalty Auto-Redemption

#### 1.1 Stamp Threshold Detection (modify `ProgressBookingHandler`)

**File**: `backend/handlers/bookings/bookings.go` (around line 1566)

Current code increments stamps. After increment, check if stamps == 10:

```go
// After: SET loyalty_stamps = loyalty_stamps + 1
// Add:
var newStampCount int
err = db.DB.QueryRow(r.Context(), `
    SELECT loyalty_stamps FROM users WHERE id = $1
`, booking.User.ID).Scan(&newStampCount)

if newStampCount == 10 {
    // Auto-create pending redemption
    _, err = db.DB.Exec(r.Context(), `
        INSERT INTO loyalty_redemptions (user_id, stamps_redeemed, status, redeemed_at)
        VALUES ($1, 10, 'pending', NOW())
    `, booking.User.ID)
    
    // TODO: Send email notification when email system is wired
    // Email: "Congratulations! Your loyalty card is full. Your next completed appointment will receive 10% off."
    // log.Printf("TODO: Send loyalty card full email to user %s", booking.User.ID)
}
```

#### 1.2 Booking Creation — Mark Discount Eligibility

**File**: `backend/handlers/bookings/bookings.go` (`CreateBookingHandler`, ~line 1267)
**File**: `backend/handlers/bookings/manage.go` (`AdminCreateBookingForUserHandler`, ~line 621)

Before INSERT into bookings, check for pending loyalty redemption or active campaigns:

```go
// Check if user has pending loyalty redemption
var hasPendingRedemption bool
err := db.DB.QueryRow(r.Context(), `
    SELECT EXISTS(
        SELECT 1 FROM loyalty_redemptions 
        WHERE user_id = $1 AND status = 'pending'
        AND expires_at > NOW()
    )
`, userID).Scan(&hasPendingRedemption)

// Check for active campaigns
var activeCampaigns int
err = db.DB.QueryRow(r.Context(), `
    SELECT COUNT(*) FROM discount_campaigns
    WHERE status = 'active'
    AND start_date <= NOW()
    AND end_date >= NOW()
    AND (scope = 'all_bookings' 
         OR (scope = 'first_booking_only' AND NOT EXISTS(
             SELECT 1 FROM bookings b WHERE b.user_id = $1 AND b.status = 'completed'
         ))
         OR (scope = 'new_customers_only' AND NOT EXISTS(
             SELECT 1 FROM bookings b WHERE b.user_id = $1
         ))
    )
    AND (max_redemptions IS NULL OR times_redeemed < max_redemptions)
`, userID).Scan(&activeCampaigns)

discountEligible := hasPendingRedemption || activeCampaigns > 0
```

Then include `discount_eligible` in the bookings INSERT.

#### 1.3 Booking Completion — Apply Discount

**File**: `backend/handlers/bookings/bookings.go` (`ProgressBookingHandler`, ~line 1537)

After stamp award, before deposit reduction:

```go
if req.Status == "completed" {
    // ... existing stamp award logic ...
    
    // Calculate booking total
    var bookingTotal float64
    err := db.DB.QueryRow(r.Context(), `
        SELECT COALESCE(SUM(COALESCE(bs.override_price, s.price)), 0)
        FROM booking_services bs
        JOIN services s ON bs.service_id = s.id
        WHERE bs.booking_id = $1
    `, bookingID).Scan(&bookingTotal)
    
    // Check for pending loyalty redemption
    var redemptionID string
    err = db.DB.QueryRow(r.Context(), `
        SELECT id FROM loyalty_redemptions
        WHERE user_id = $1 AND status = 'pending'
        AND expires_at > NOW()
        ORDER BY redeemed_at ASC
        LIMIT 1
    `, booking.User.ID).Scan(&redemptionID)
    
    if redemptionID != "" {
        discountAmount := roundTo2(bookingTotal * 0.10)
        
        // Record discount
        _, err = db.DB.Exec(r.Context(), `
            INSERT INTO booking_discounts (booking_id, user_id, discount_source, source_id, campaign_type, milestone_type, discount_percent, original_total, discount_amount)
            VALUES ($1, $2, 'loyalty', $3, NULL, NULL, 10.00, $4, $5)
        `, bookingID, booking.User.ID, redemptionID, bookingTotal, discountAmount)
        
        // Create discount payment record
        _, err = db.DB.Exec(r.Context(), `
            INSERT INTO payments (booking_id, payment_type, payment_method, amount, status, created_by)
            VALUES ($1, 'partial', 'discount', $2, 'completed', $3)
        `, bookingID, discountAmount, booking.User.ID)
        
        // Update redemption status
        _, err = db.DB.Exec(r.Context(), `
            UPDATE loyalty_redemptions SET status = 'applied', applied_to_booking_id = $1, applied_at = NOW()
            WHERE id = $2
        `, bookingID, redemptionID)
        
        // Deduct stamps
        _, err = db.DB.Exec(r.Context(), `
            UPDATE users SET loyalty_stamps = GREATEST(0, loyalty_stamps - 10) WHERE id = $1
        `, booking.User.ID)
    }
    
    // Check for active time-based campaign discount
    var campaignID string
    var campaignPercent float64
    err = db.DB.QueryRow(r.Context(), `
        SELECT id, discount_percent FROM discount_campaigns
        WHERE status = 'active' AND campaign_type = 'time_based'
        AND start_date <= NOW() AND end_date >= NOW()
        AND (scope = 'all_bookings' OR ...)
        AND (max_redemptions IS NULL OR times_redeemed < max_redemptions)
        LIMIT 1
    `).Scan(&campaignID, &campaignPercent)
    
    if campaignID != "" && redemptionID == "" {
        // Only apply campaign if no loyalty discount was applied
        discountAmount := roundTo2(bookingTotal * campaignPercent / 100)
        
        _, err = db.DB.Exec(r.Context(), `
            INSERT INTO booking_discounts (booking_id, user_id, discount_source, source_id, campaign_type, milestone_type, discount_percent, original_total, discount_amount)
            VALUES ($1, $2, 'campaign', $3, 'time_based', NULL, $4, $5, $6)
        `, bookingID, booking.User.ID, campaignID, campaignPercent, bookingTotal, discountAmount)
        
        _, err = db.DB.Exec(r.Context(), `
            INSERT INTO payments (booking_id, payment_type, payment_method, amount, status, created_by)
            VALUES ($1, 'partial', 'discount', $2, 'completed', $3)
        `, bookingID, discountAmount, booking.User.ID)
        
        _, err = db.DB.Exec(r.Context(), `
            UPDATE discount_campaigns SET times_redeemed = times_redeemed + 1 WHERE id = $1
        `, campaignID)
    }
    
    // Check for milestone campaigns (applied at completion time)
    if redemptionID == "" && campaignID == "" {
        // Per-user booking count milestone
        var userBookingCount int
        db.DB.QueryRow(r.Context(), `SELECT COUNT(*) FROM bookings WHERE user_id = $1 AND status = 'completed'`, booking.User.ID).Scan(&userBookingCount)
        
        var milestoneCampaignID string
        var milestonePercent float64
        db.DB.QueryRow(r.Context(), `
            SELECT id, discount_percent FROM discount_campaigns
            WHERE status = 'active' AND campaign_type = 'milestone' AND milestone_type = 'per_user_booking_count'
            AND milestone_value = $1
            AND NOT EXISTS (
                SELECT 1 FROM booking_discounts WHERE user_id = $2 AND source_id = discount_campaigns.id
            )
        `, userBookingCount, booking.User.ID).Scan(&milestoneCampaignID, &milestonePercent)
        
        if milestoneCampaignID != "" {
            // Apply per-user milestone discount
            discountAmount := roundTo2(bookingTotal * milestonePercent / 100)
            _, err = db.DB.Exec(r.Context(), `
                INSERT INTO booking_discounts (booking_id, user_id, discount_source, source_id, campaign_type, milestone_type, discount_percent, original_total, discount_amount)
                VALUES ($1, $2, 'campaign', $3, 'milestone', 'per_user_booking_count', $4, $5, $6)
            `, bookingID, booking.User.ID, milestoneCampaignID, milestonePercent, bookingTotal, discountAmount)
            _, err = db.DB.Exec(r.Context(), `
                INSERT INTO payments (booking_id, payment_type, payment_method, amount, status, created_by)
                VALUES ($1, 'partial', 'discount', $2, 'completed', $3)
            `, bookingID, discountAmount, booking.User.ID)
            _, err = db.DB.Exec(r.Context(), `
                UPDATE discount_campaigns SET times_redeemed = times_redeemed + 1 WHERE id = $1
            `, milestoneCampaignID)
        }
        
        // Global booking count milestone
        if milestoneCampaignID == "" {
            var globalCount int
            db.DB.QueryRow(r.Context(), `SELECT COUNT(*) FROM bookings WHERE status = 'completed'`).Scan(&globalCount)
            // globalCount is N-1 (current booking not yet marked completed), so this booking is the (globalCount+1)th
            var globalCampaignID string
            var globalPercent float64
            db.DB.QueryRow(r.Context(), `
                SELECT id, discount_percent FROM discount_campaigns
                WHERE status = 'active' AND campaign_type = 'milestone' AND milestone_type = 'global_booking_count'
                AND milestone_value = $1
                AND (max_redemptions IS NULL OR times_redeemed < max_redemptions)
            `, globalCount+1).Scan(&globalCampaignID, &globalPercent)
            
            if globalCampaignID != "" {
                discountAmount := roundTo2(bookingTotal * globalPercent / 100)
                _, err = db.DB.Exec(r.Context(), `
                    INSERT INTO booking_discounts (booking_id, user_id, discount_source, source_id, campaign_type, milestone_type, discount_percent, original_total, discount_amount)
                    VALUES ($1, $2, 'campaign', $3, 'milestone', 'global_booking_count', $4, $5, $6)
                `, bookingID, booking.User.ID, globalCampaignID, globalPercent, bookingTotal, discountAmount)
                _, err = db.DB.Exec(r.Context(), `
                    INSERT INTO payments (booking_id, payment_type, payment_method, amount, status, created_by)
                    VALUES ($1, 'partial', 'discount', $2, 'completed', $3)
                `, bookingID, discountAmount, booking.User.ID)
                _, err = db.DB.Exec(r.Context(), `
                    UPDATE discount_campaigns SET times_redeemed = times_redeemed + 1 WHERE id = $1
                `, globalCampaignID)
            }
        }
        
        // Anniversary milestone
        if milestoneCampaignID == "" && globalCampaignID == "" {
            var firstVisitDate time.Time
            db.DB.QueryRow(r.Context(), `SELECT MIN(start_time) FROM bookings WHERE user_id = $1 AND status = 'completed'`, booking.User.ID).Scan(&firstVisitDate)
            if !firstVisitDate.IsZero() {
                // Check all active anniversary milestones
                rows, _ := db.DB.Query(r.Context(), `
                    SELECT id, discount_percent, milestone_value, milestone_unit FROM discount_campaigns
                    WHERE status = 'active' AND campaign_type = 'milestone' AND milestone_type = 'anniversary'
                    AND NOT EXISTS (
                        SELECT 1 FROM booking_discounts WHERE user_id = $1 AND source_id = discount_campaigns.id AND milestone_type = 'anniversary'
                    )
                `, booking.User.ID)
                defer rows.Close()
                for rows.Next() {
                    var annID string
                    var annPercent float64
                    var annValue int
                    var annUnit string
                    rows.Scan(&annID, &annPercent, &annValue, &annUnit)
                    
                    elapsed := time.Since(firstVisitDate)
                    var matches bool
                    switch annUnit {
                    case "months":
                        matches = int(elapsed.Hours()/(30*24)) >= annValue
                    case "years":
                        matches = int(elapsed.Hours()/(365.25*24)) >= annValue
                    }
                    
                    if matches {
                        discountAmount := roundTo2(bookingTotal * annPercent / 100)
                        _, err = db.DB.Exec(r.Context(), `
                            INSERT INTO booking_discounts (booking_id, user_id, discount_source, source_id, campaign_type, milestone_type, discount_percent, original_total, discount_amount)
                            VALUES ($1, $2, 'campaign', $3, 'milestone', 'anniversary', $4, $5, $6)
                        `, bookingID, booking.User.ID, annID, annPercent, bookingTotal, discountAmount)
                        _, err = db.DB.Exec(r.Context(), `
                            INSERT INTO payments (booking_id, payment_type, payment_method, amount, status, created_by)
                            VALUES ($1, 'partial', 'discount', $2, 'completed', $3)
                        `, bookingID, discountAmount, booking.User.ID)
                        _, err = db.DB.Exec(r.Context(), `
                            UPDATE discount_campaigns SET times_redeemed = times_redeemed + 1 WHERE id = $1
                        `, annID)
                        break // Only one anniversary milestone per completion
                    }
                }
            }
        }
    }
    
    // ... existing deposit reduction logic ...
}
```

### Phase 2: Discount Campaign CRUD

#### 2.1 New Handler File

**File**: `backend/handlers/admin/discount_campaigns.go`

Endpoints:
- `GET /api/admin/discount-campaigns` — list all campaigns
- `POST /api/admin/discount-campaigns` — create campaign
- `PUT /api/admin/discount-campaigns/{id}` — update campaign (status, dates, etc.)
- `DELETE /api/admin/discount-campaigns/{id}` — cancel campaign
- `GET /api/admin/discount-campaigns/{id}/stats` — redemption stats

Handler structure follows existing admin pattern (like `handlers/admin/services.go`).

#### 2.2 Router Wiring

**File**: `backend/main.go` (~line 232, in admin routes)

```go
r.Group(func(r chi.Router) {
    r.Use(middleware.RequireAuth)
    r.Use(middleware.RequireAdmin)
    // ... existing routes ...
    r.Get("/discount-campaigns", admin.GetDiscountCampaigns)
    r.Post("/discount-campaigns", admin.CreateDiscountCampaign)
    r.Put("/discount-campaigns/{id}", admin.UpdateDiscountCampaign)
    r.Delete("/discount-campaigns/{id}", admin.DeleteDiscountCampaign)
    r.Get("/discount-campaigns/{id}/stats", admin.GetCampaignStats)
})
```

### Phase 3: Frontend — Customer Account Page

**File**: `frontend/src/routes/account/+page.svelte` (lines 826-843)

Current: Shows stamp count and "Congratulations! You've earned 10% off!"

Changes:
- When stamps >= 10: "Your loyalty card is full! Your next completed appointment will receive 10% off."
- When stamps < 10: Keep existing countdown
- Add "Pending discount" indicator if user has a pending redemption: "You have a 10% discount waiting for your next completed appointment."
- NO exact price amounts shown

### Phase 4: Frontend — Admin Campaign Management

**File**: New `frontend/src/routes/admin/discounts/+page.svelte`

Admin UI for:
- List campaigns with status badges (draft/active/completed/cancelled)
- Create campaign form: name, description, discount %, scope, date range, max redemptions
- Campaign stats: times redeemed, revenue impact
- Quick actions: activate, cancel

Add link to admin dashboard/sidebar.

### Phase 5: Email Notification (TODO Placeholder)

**File**: `backend/handlers/bookings/bookings.go` (in stamp award section)

```go
// TODO: Email notification — requires SMTP provider (E5)
// When: loyalty_stamps reaches 10
// To: user's email
// Subject: "Your loyalty reward is ready! 🎉"
// Body: "Congratulations! You've collected 10 stamps. Your next completed appointment will receive 10% off automatically."
// 
// When email system is wired (E5), call:
// emailService.SendLoyaltyRewardEmail(user.Email, user.FirstName)
```

---

## Migration SQL

```sql
-- Run as part of database migration

-- 1. Create loyalty_redemptions table
CREATE TABLE loyalty_redemptions (
    id CHAR(12) PRIMARY KEY DEFAULT generate_short_id('loyalty_redemptions'),
    user_id CHAR(12) NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    stamps_redeemed INT NOT NULL DEFAULT 10,
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    applied_to_booking_id CHAR(12) REFERENCES bookings(id) ON DELETE SET NULL,
    redeemed_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    applied_at TIMESTAMPTZ,
    expires_at TIMESTAMPTZ NOT NULL DEFAULT (NOW() + INTERVAL '6 months')
);

CREATE INDEX idx_loyalty_redemptions_user ON loyalty_redemptions(user_id);
CREATE INDEX idx_loyalty_redemptions_status ON loyalty_redemptions(status);

-- 2. Create discount_campaigns table with milestone support
CREATE TYPE campaign_type AS ENUM ('time_based', 'milestone');
CREATE TYPE milestone_type AS ENUM ('per_user_booking_count', 'global_booking_count', 'anniversary');
CREATE TYPE milestone_unit AS ENUM ('bookings', 'months', 'years');
CREATE TYPE discount_campaign_scope AS ENUM ('all_bookings', 'first_booking_only', 'new_customers_only');
CREATE TYPE discount_campaign_status AS ENUM ('draft', 'active', 'completed', 'cancelled');

CREATE TABLE discount_campaigns (
    id CHAR(12) PRIMARY KEY DEFAULT generate_short_id('discount_campaigns'),
    name VARCHAR(100) NOT NULL,
    description TEXT,
    campaign_type campaign_type NOT NULL DEFAULT 'time_based',
    discount_percent NUMERIC(5,2) NOT NULL,
    scope discount_campaign_scope,
    start_date TIMESTAMPTZ,
    end_date TIMESTAMPTZ,
    milestone_type milestone_type,
    milestone_value INT,
    milestone_unit milestone_unit,
    status discount_campaign_status NOT NULL DEFAULT 'draft',
    max_redemptions INT,
    times_redeemed INT NOT NULL DEFAULT 0,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by CHAR(12) REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT chk_dates CHECK (campaign_type = 'milestone' OR (start_date IS NOT NULL AND end_date IS NOT NULL AND end_date > start_date)),
    CONSTRAINT chk_discount CHECK (discount_percent > 0 AND discount_percent <= 100),
    CONSTRAINT chk_milestone CHECK (campaign_type = 'time_based' OR (milestone_type IS NOT NULL AND milestone_value IS NOT NULL AND milestone_unit IS NOT NULL))
);

CREATE INDEX idx_discount_campaigns_dates ON discount_campaigns(start_date, end_date) WHERE start_date IS NOT NULL;
CREATE INDEX idx_discount_campaigns_status ON discount_campaigns(status);
CREATE INDEX idx_discount_campaigns_type ON discount_campaigns(campaign_type);

-- 3. Create booking_discounts table with milestone tracking
CREATE TABLE booking_discounts (
    id CHAR(12) PRIMARY KEY DEFAULT generate_short_id('booking_discounts'),
    booking_id CHAR(12) NOT NULL REFERENCES bookings(id) ON DELETE CASCADE,
    user_id CHAR(12) NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    discount_source VARCHAR(30) NOT NULL,
    source_id CHAR(12),
    campaign_type campaign_type,
    milestone_type milestone_type,
    discount_percent NUMERIC(5,2) NOT NULL,
    original_total NUMERIC(10,2) NOT NULL,
    discount_amount NUMERIC(10,2) NOT NULL,
    applied_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_booking_discounts_booking ON booking_discounts(booking_id);
CREATE INDEX idx_booking_discounts_source ON booking_discounts(discount_source, source_id);
CREATE INDEX idx_booking_discounts_user ON booking_discounts(user_id);
CREATE INDEX idx_booking_discounts_milestone ON booking_discounts(user_id, milestone_type, source_id);

-- 4. Add discount_eligible to bookings
ALTER TABLE bookings ADD COLUMN discount_eligible BOOLEAN NOT NULL DEFAULT FALSE;

-- 5. Add trigger for discount_campaigns updated_at
CREATE TRIGGER trigger_update_discount_campaigns_timestamp
    BEFORE UPDATE ON discount_campaigns
    FOR EACH ROW
    EXECUTE FUNCTION update_business_settings_timestamp();
```

---

## File Change Summary

| File | Change |
|------|--------|
| `init-scripts/init-script.sql` | Add 3 new tables + 5 new enums + 1 column alter |
| `backend/handlers/bookings/bookings.go` | Modify ProgressBookingHandler (stamp threshold + discount apply for loyalty/time-based/milestone), modify CreateBookingHandler (discount_eligible check for time-based campaigns) |
| `backend/handlers/bookings/manage.go` | Modify AdminCreateBookingForUserHandler (discount_eligible check) |
| `backend/handlers/admin/discount_campaigns.go` | NEW: CRUD handlers for discount campaigns (supports both time-based and milestone types) |
| `backend/main.go` | Wire discount campaign routes |
| `frontend/src/routes/account/+page.svelte` | Update loyalty display text, add pending discount indicator |
| `frontend/src/routes/admin/discounts/+page.svelte` | NEW: Admin campaign management UI (campaign type selector, milestone fields) |
| `frontend/src/lib/types/` | Add TypeScript types for loyalty_redemptions, discount_campaigns, booking_discounts, milestone enums |

---

## Testing Plan

1. **Unit**: Test discount calculation (rounding edge cases)
2. **Integration**: 
   - Complete booking with pending redemption → verify discount applied, stamps deducted
   - Complete booking with active time-based campaign → verify discount applied, times_redeemed incremented
   - Cancel booking with discount_eligible=true → verify no discount applied, redemption stays pending
   - Multiple bookings pending → only first completed gets loyalty discount
   - **Per-user milestone**: Create 99 completed bookings for a user, set up "100th booking" milestone campaign, complete 100th → verify discount applied
   - **Global milestone**: Seed 99 completed bookings globally, set up "100th booking overall" milestone, complete next booking → verify discount applied
   - **Anniversary milestone**: Create user with first booking 6+ months ago, set up "6 months" anniversary campaign, complete next booking → verify discount applied. Complete another → verify NO second discount (dedup works)
3. **Edge cases**:
   - User has 10 stamps but redemption expired → no discount
   - Campaign max_redemptions reached → no discount
   - Both loyalty + campaign eligible → loyalty takes priority (only one discount per booking)
   - Booking total is £0 (free service) → discount is £0, no payment created
   - Two milestone campaigns with same value → both apply? No, single discount per booking, pick highest percent

---

## Risks & Mitigations

| Risk | Mitigation |
|------|-----------|
| Discount + VAT interaction | Discount payment is separate from service payment. VAT calculated on net service amount. Discount payment has no VAT. |
| Admin overrides price after booking | Discount calculated at completion time using actual booking_services override prices — always accurate |
| Campaign date timezone issues | All dates stored as TIMESTAMPTZ, compared with NOW() — consistent |
| Double-discount (loyalty + campaign) | Priority: loyalty first. If loyalty applied, campaign skipped. Single discount per booking. |
| Stamps deducted but booking fails | All operations in single transaction — rollback on failure |
| Global booking count race condition | `SELECT COUNT(*)` at completion time is accurate — the booking being completed hasn't been marked completed yet, so count is N-1. The current booking is the Nth. |
| Anniversary milestone double-apply | `booking_discounts` table tracks per-user per-milestone applications. Query before applying to ensure not already claimed. |
| Per-user milestone double-apply | Same dedup via `booking_discounts` — check if user already has a discount for this specific milestone campaign. |
