/**
 * Integration tests for booking constraints enforced by the create-reservation Edge Function
 * and the database (overlap exclusion constraint, cert check trigger).
 *
 * Requires local Supabase running with Edge Functions served:
 *   npx supabase start
 *   npx supabase functions serve
 *
 * Run with:
 *   yarn test:integration
 *
 * Each describe block creates isolated test users and cleans up after itself.
 */

import { describe, it, expect, beforeAll, afterAll } from 'vitest'
import { createClient, type SupabaseClient } from '@supabase/supabase-js'

const SUPABASE_URL             = process.env.SUPABASE_URL              ?? 'http://localhost:54321'
const SUPABASE_ANON_KEY        = process.env.SUPABASE_KEY              ?? ''
const SUPABASE_SERVICE_ROLE_KEY = process.env.SUPABASE_SERVICE_ROLE_KEY ?? ''
const FUNCTIONS_URL            = `${SUPABASE_URL}/functions/v1`

// ─── Helpers ──────────────────────────────────────────────────────────────────

let adminClient: SupabaseClient

function makeAnonClient() {
  return createClient(SUPABASE_URL, SUPABASE_ANON_KEY, {
    auth: { autoRefreshToken: false, persistSession: false },
  })
}

async function createTestUser(email: string, certifications: string[] = [], role = 'member') {
  const { data, error } = await adminClient.auth.admin.createUser({
    email,
    email_confirm: true,
  })
  if (error || !data.user) throw new Error(`Failed to create user: ${error?.message}`)
  const userId = data.user.id

  await adminClient.from('members').upsert({
    user_id:        userId,
    email,
    certifications,
    role,
    first_name:     'Test',
    last_name:      'User',
  }, { onConflict: 'user_id' })

  return userId
}

async function getSessionToken(email: string): Promise<string> {
  const anon = makeAnonClient()
  const { data: linkData } = await adminClient.auth.admin.generateLink({
    type: 'magiclink',
    email,
  })
  const { data: sessionData } = await anon.auth.verifyOtp({
    token_hash: linkData.properties!.hashed_token!,
    type: 'magiclink',
  })
  return sessionData.session!.access_token
}

async function callCreateReservation(
  token: string,
  body: {
    boat_id: string
    start_time: string
    end_time: string
    reason?: string
  },
) {
  const res = await fetch(`${FUNCTIONS_URL}/create-reservation`, {
    method:  'POST',
    headers: {
      'Content-Type':  'application/json',
      'Authorization': `Bearer ${token}`,
      'apikey':        SUPABASE_ANON_KEY,
    },
    body: JSON.stringify({ reason: 'Open Sail', ...body }),
  })
  const json = await res.json()
  return { status: res.status, body: json }
}

async function createTestBoat(requiredCerts: string[] = [], available = true): Promise<string> {
  const { data, error } = await adminClient.from('boats').insert({
    name:              `Test Boat ${Date.now()}`,
    required_certs:    requiredCerts,
    booking_available: available,
    max_passengers:    6,
  }).select('id').single()
  if (error) throw new Error(`Failed to create boat: ${error.message}`)
  return data.id
}

async function directInsertReservation(boatId: string, userId: string, start: string, end: string) {
  const { error } = await adminClient.from('reservations').insert({
    boat_id:    boatId,
    user_id:    userId,
    start_time: start,
    end_time:   end,
    reason:     'Test',
    status:     'confirmed',
  })
  return error
}

// Future Monday 09:00–12:30 (within current ISO week, far enough ahead)
function futureSlot(daysAhead: number, hour = 9, endHour = 12, endMin = 30): { start_time: string; end_time: string } {
  const d = new Date()
  d.setUTCDate(d.getUTCDate() + daysAhead)
  d.setUTCHours(hour, 0, 0, 0)
  const e = new Date(d)
  e.setUTCHours(endHour, endMin, 0, 0)
  return { start_time: d.toISOString(), end_time: e.toISOString() }
}

// ─── Test Suite ───────────────────────────────────────────────────────────────

beforeAll(() => {
  if (!SUPABASE_SERVICE_ROLE_KEY) {
    throw new Error('SUPABASE_SERVICE_ROLE_KEY is required. Run: npx supabase status')
  }
  adminClient = createClient(SUPABASE_URL, SUPABASE_SERVICE_ROLE_KEY, {
    auth: { autoRefreshToken: false, persistSession: false },
  })
})

// ── Overlap constraint (DB-level) ─────────────────────────────────────────────
describe('overlap constraint', () => {
  const email = `test-overlap-${Date.now()}@oysqn.test`
  let userId: string
  let boatId: string
  let token: string

  beforeAll(async () => {
    userId = await createTestUser(email)
    boatId = await createTestBoat()
    token  = await getSessionToken(email)
  })

  afterAll(async () => {
    await adminClient.from('reservations').delete().eq('boat_id', boatId)
    await adminClient.from('boats').delete().eq('id', boatId)
    await adminClient.auth.admin.deleteUser(userId)
  })

  it('allows first booking for a slot', async () => {
    const slot = futureSlot(14)
    const { status } = await callCreateReservation(token, { boat_id: boatId, ...slot })
    expect(status).toBe(201)
  })

  it('rejects an overlapping booking for the same boat', async () => {
    // Use a different week from the first test so the weekly pre-booking limit isn't reached
    const slot = futureSlot(21)
    // Book once directly via admin
    await directInsertReservation(boatId, userId, slot.start_time, slot.end_time)
    // Second booking for same slot via Edge Function — DB exclusion constraint fires
    const { status, body } = await callCreateReservation(token, { boat_id: boatId, ...slot })
    expect(status).toBe(409)
    expect(body.error.code).toBe('slot_taken')
  })

  it('allows a booking on the same boat at a different time', async () => {
    // Third distinct week — no pre-booking limit conflict, different time slot
    const slot = futureSlot(28, 13, 16, 30)
    const { status } = await callCreateReservation(token, { boat_id: boatId, ...slot })
    expect(status).toBe(201)
  })
})

// ── Certification check (DB trigger + Edge Function) ─────────────────────────
describe('certification check', () => {
  const emailNoCert = `test-nocert-${Date.now()}@oysqn.test`
  const emailCert   = `test-cert-${Date.now()}@oysqn.test`
  let userNoCert: string
  let userCert: string
  let boatId: string
  let tokenNoCert: string
  let tokenCert: string

  beforeAll(async () => {
    boatId      = await createTestBoat(['j27'])
    userNoCert  = await createTestUser(emailNoCert, [])
    userCert    = await createTestUser(emailCert, ['j27'])
    tokenNoCert = await getSessionToken(emailNoCert)
    tokenCert   = await getSessionToken(emailCert)
  })

  afterAll(async () => {
    await adminClient.from('reservations').delete().eq('boat_id', boatId)
    await adminClient.from('boats').delete().eq('id', boatId)
    await adminClient.auth.admin.deleteUser(userNoCert)
    await adminClient.auth.admin.deleteUser(userCert)
  })

  it('rejects booking when member lacks required cert', async () => {
    const slot = futureSlot(35)
    const { status, body } = await callCreateReservation(tokenNoCert, { boat_id: boatId, ...slot })
    expect(status).toBe(422)
    expect(body.error.code).toBe('cert_required')
  })

  it('allows booking when member has required cert', async () => {
    const slot = futureSlot(36)
    const { status } = await callCreateReservation(tokenCert, { boat_id: boatId, ...slot })
    expect(status).toBe(201)
  })

  it('allows booking on a boat with no cert requirements', async () => {
    const openBoat = await createTestBoat([])
    const slot = futureSlot(37)
    const { status } = await callCreateReservation(tokenNoCert, { boat_id: openBoat, ...slot })
    expect(status).toBe(201)
    await adminClient.from('boats').delete().eq('id', openBoat)
  })
})

// ── Out-of-service check ──────────────────────────────────────────────────────
describe('boat out-of-service check', () => {
  const email = `test-oos-${Date.now()}@oysqn.test`
  let userId: string
  let boatId: string
  let token: string

  beforeAll(async () => {
    boatId = await createTestBoat([], false)  // booking_available = false
    userId = await createTestUser(email)
    token  = await getSessionToken(email)
  })

  afterAll(async () => {
    await adminClient.from('boats').delete().eq('id', boatId)
    await adminClient.auth.admin.deleteUser(userId)
  })

  it('rejects booking when boat is out of service', async () => {
    const slot = futureSlot(40)
    const { status, body } = await callCreateReservation(token, { boat_id: boatId, ...slot })
    expect(status).toBe(422)
    expect(body.error.code).toBe('boat_unavailable')
  })
})

// ── Weekly session limit ──────────────────────────────────────────────────────
describe('weekly session limit', () => {
  const email = `test-weekly-${Date.now()}@oysqn.test`
  let userId: string
  let boatId: string
  let token: string

  // Use a fixed far-future week so existing data doesn't interfere
  // Monday 2026-06-08 (week with no holidays)
  const WEEK_BASE = new Date('2026-06-08T09:00:00Z')

  function weekSlot(dayOffset: number, hour = 9): { start_time: string; end_time: string } {
    const s = new Date(WEEK_BASE)
    s.setUTCDate(WEEK_BASE.getUTCDate() + dayOffset)
    s.setUTCHours(hour, 0, 0, 0)
    const e = new Date(s)
    e.setUTCHours(hour + 3, 30, 0, 0)
    return { start_time: s.toISOString(), end_time: e.toISOString() }
  }

  beforeAll(async () => {
    userId = await createTestUser(email)
    boatId = await createTestBoat()
    token  = await getSessionToken(email)

    // Set max_sessions_per_week to 2 (should already be the default)
    await adminClient.from('booking_config')
      .upsert({ key: 'max_sessions_per_week', value: 2 }, { onConflict: 'key' })
    // Set advance hours to 1 so our far-future bookings count as pre-bookings
    await adminClient.from('booking_config')
      .upsert({ key: 'open_session_advance_hours', value: 1 }, { onConflict: 'key' })
  })

  afterAll(async () => {
    await adminClient.from('reservations').delete().eq('boat_id', boatId)
    await adminClient.from('boats').delete().eq('id', boatId)
    await adminClient.auth.admin.deleteUser(userId)
    // Restore default
    await adminClient.from('booking_config')
      .upsert({ key: 'open_session_advance_hours', value: 24 }, { onConflict: 'key' })
  })

  it('allows first pre-booking of the week', async () => {
    const { status } = await callCreateReservation(token, { boat_id: boatId, ...weekSlot(0) })
    expect(status).toBe(201)
  })

  it('allows second pre-booking of the week', async () => {
    const { status } = await callCreateReservation(token, { boat_id: boatId, ...weekSlot(1) })
    expect(status).toBe(201)
  })

  it('rejects third pre-booking of the week', async () => {
    const { status, body } = await callCreateReservation(token, { boat_id: boatId, ...weekSlot(2) })
    expect(status).toBe(422)
    expect(body.error.code).toBe('booking_limit_weekly')
  })
})

// ── Weekend session limit ─────────────────────────────────────────────────────
describe('weekend session limit', () => {
  const email = `test-weekend-${Date.now()}@oysqn.test`
  let userId: string
  let boatId: string
  let token: string

  // 2026-06-13 (Saturday) and 2026-06-14 (Sunday) — same 2-week period
  // 2026-06-20 (Saturday) — same 2-week period as June 13 (period weeks = 2)
  const SAT1 = '2026-06-13T09:00:00Z'
  const SAT1_END = '2026-06-13T12:30:00Z'
  const SUN1 = '2026-06-14T09:00:00Z'
  const SUN1_END = '2026-06-14T12:30:00Z'

  beforeAll(async () => {
    userId = await createTestUser(email)
    boatId = await createTestBoat()
    token  = await getSessionToken(email)

    await adminClient.from('booking_config')
      .upsert({ key: 'max_weekend_sessions_per_period', value: 1 }, { onConflict: 'key' })
    await adminClient.from('booking_config')
      .upsert({ key: 'weekend_period_weeks', value: 2 }, { onConflict: 'key' })
    await adminClient.from('booking_config')
      .upsert({ key: 'open_session_advance_hours', value: 1 }, { onConflict: 'key' })
  })

  afterAll(async () => {
    await adminClient.from('reservations').delete().eq('boat_id', boatId)
    await adminClient.from('boats').delete().eq('id', boatId)
    await adminClient.auth.admin.deleteUser(userId)
    await adminClient.from('booking_config')
      .upsert({ key: 'open_session_advance_hours', value: 24 }, { onConflict: 'key' })
  })

  it('allows first weekend booking in the period', async () => {
    const { status } = await callCreateReservation(token, {
      boat_id: boatId, start_time: SAT1, end_time: SAT1_END,
    })
    expect(status).toBe(201)
  })

  it('rejects second weekend booking in the same period', async () => {
    const { status, body } = await callCreateReservation(token, {
      boat_id: boatId, start_time: SUN1, end_time: SUN1_END,
    })
    expect(status).toBe(422)
    expect(body.error.code).toBe('booking_limit_weekend')
  })
})

// ── Open-session window bypass ────────────────────────────────────────────────
describe('open-session window bypasses pre-booking limits', () => {
  const email = `test-opensession-${Date.now()}@oysqn.test`
  let userId: string
  let boatId: string
  let token: string

  beforeAll(async () => {
    userId = await createTestUser(email)
    boatId = await createTestBoat()
    token  = await getSessionToken(email)

    // Set limit to 0 pre-bookings/week so only open sessions can go through
    await adminClient.from('booking_config')
      .upsert({ key: 'max_sessions_per_week', value: 0 }, { onConflict: 'key' })
    // Open session window: 9999 hours (everything is an open session)
    await adminClient.from('booking_config')
      .upsert({ key: 'open_session_advance_hours', value: 9999 }, { onConflict: 'key' })
  })

  afterAll(async () => {
    await adminClient.from('reservations').delete().eq('boat_id', boatId)
    await adminClient.from('boats').delete().eq('id', boatId)
    await adminClient.auth.admin.deleteUser(userId)
    await adminClient.from('booking_config')
      .upsert({ key: 'max_sessions_per_week', value: 2 }, { onConflict: 'key' })
    await adminClient.from('booking_config')
      .upsert({ key: 'open_session_advance_hours', value: 24 }, { onConflict: 'key' })
  })

  it('allows booking even when pre-booking limit is 0, because slot is within open-session window', async () => {
    const slot = futureSlot(1)
    const { status } = await callCreateReservation(token, { boat_id: boatId, ...slot })
    expect(status).toBe(201)
  })
})

// ── Historical booking constraint ────────────────────────────────────────────
describe('historical booking constraint', () => {
  const emailMember = `test-hist-member-${Date.now()}@oysqn.test`
  const emailAdmin  = `test-hist-admin-${Date.now()}@oysqn.test`
  let memberUserId: string
  let adminUserId: string
  let boatId: string
  let memberToken: string
  let adminToken: string

  function pastSlot(): { start_time: string; end_time: string } {
    const d = new Date()
    d.setUTCDate(d.getUTCDate() - 7)
    d.setUTCHours(9, 0, 0, 0)
    const e = new Date(d)
    e.setUTCHours(12, 30, 0, 0)
    return { start_time: d.toISOString(), end_time: e.toISOString() }
  }

  beforeAll(async () => {
    boatId       = await createTestBoat()
    memberUserId = await createTestUser(emailMember, [], 'member')
    adminUserId  = await createTestUser(emailAdmin,  [], 'admin')
    memberToken  = await getSessionToken(emailMember)
    adminToken   = await getSessionToken(emailAdmin)
  })

  afterAll(async () => {
    await adminClient.from('reservations').delete().eq('boat_id', boatId)
    await adminClient.from('boats').delete().eq('id', boatId)
    await adminClient.auth.admin.deleteUser(memberUserId)
    await adminClient.auth.admin.deleteUser(adminUserId)
  })

  it('rejects a historical booking by a regular member', async () => {
    const slot = pastSlot()
    const { status, body } = await callCreateReservation(memberToken, { boat_id: boatId, ...slot })
    expect(status).toBe(422)
    expect(body.error.code).toBe('historical_booking_not_allowed')
  })

  it('allows a historical booking by an admin', async () => {
    const slot = pastSlot()
    const { status } = await callCreateReservation(adminToken, { boat_id: boatId, ...slot })
    expect(status).toBe(201)
  })

  it('rejects a historical booking by a skipper (non-admin role)', async () => {
    const emailSkipper = `test-hist-skipper-${Date.now()}@oysqn.test`
    const skipperId = await createTestUser(emailSkipper, [], 'skipper')
    const skipperToken = await getSessionToken(emailSkipper)
    try {
      const slot = pastSlot()
      const { status, body } = await callCreateReservation(skipperToken, { boat_id: boatId, ...slot })
      expect(status).toBe(422)
      expect(body.error.code).toBe('historical_booking_not_allowed')
    } finally {
      await adminClient.auth.admin.deleteUser(skipperId)
    }
  })
})

// ── New reservation status ────────────────────────────────────────────────────
describe('new reservation status', () => {
  const email = `test-status-${Date.now()}@oysqn.test`
  let userId: string
  let boatId: string
  let token: string

  beforeAll(async () => {
    userId = await createTestUser(email)
    boatId = await createTestBoat()
    token  = await getSessionToken(email)
  })

  afterAll(async () => {
    await adminClient.from('reservations').delete().eq('boat_id', boatId)
    await adminClient.from('boats').delete().eq('id', boatId)
    await adminClient.auth.admin.deleteUser(userId)
  })

  it('creates reservation with status confirmed', async () => {
    const slot = futureSlot(70)
    const { status, body } = await callCreateReservation(token, { boat_id: boatId, ...slot })
    expect(status).toBe(201)
    expect(body.reservation.status).toBe('confirmed')
  })
})

// ── Cancel reservation ────────────────────────────────────────────────────────
describe('cancel reservation', () => {
  const emailA = `test-cancel-a-${Date.now()}@oysqn.test`
  const emailB = `test-cancel-b-${Date.now()}@oysqn.test`
  let userA: string
  let userB: string
  let boatId: string
  let tokenA: string
  let tokenB: string

  const slot = futureSlot(72)
  let reservationId: string

  beforeAll(async () => {
    boatId = await createTestBoat()
    userA  = await createTestUser(emailA)
    userB  = await createTestUser(emailB)
    tokenA = await getSessionToken(emailA)
    tokenB = await getSessionToken(emailB)

    // User A books the slot
    const { body } = await callCreateReservation(tokenA, { boat_id: boatId, ...slot })
    reservationId = body.reservation.id
  })

  afterAll(async () => {
    await adminClient.from('reservations').delete().eq('boat_id', boatId)
    await adminClient.from('boats').delete().eq('id', boatId)
    await adminClient.auth.admin.deleteUser(userA)
    await adminClient.auth.admin.deleteUser(userB)
  })

  it('user can cancel their own reservation', async () => {
    const client = createClient(SUPABASE_URL, SUPABASE_ANON_KEY, {
      auth: { autoRefreshToken: false, persistSession: false },
      global: { headers: { Authorization: `Bearer ${tokenA}` } },
    })
    const { error } = await client
      .from('reservations')
      .update({ status: 'cancelled' })
      .eq('id', reservationId)
    expect(error).toBeNull()

    const { data } = await adminClient
      .from('reservations')
      .select('status')
      .eq('id', reservationId)
      .single()
    expect(data!.status).toBe('cancelled')
  })

  it('cancelled reservation is excluded from reservation_slots view', async () => {
    const { data } = await adminClient
      .from('reservation_slots' as never)
      .select('id')
      .eq('id', reservationId)
    expect(data).toHaveLength(0)
  })

  it('cancelled slot can be re-booked by another user', async () => {
    const { status } = await callCreateReservation(tokenB, { boat_id: boatId, ...slot })
    expect(status).toBe(201)
  })
})

// ── Edit reservation ──────────────────────────────────────────────────────────
describe('edit reservation', () => {
  const email = `test-edit-${Date.now()}@oysqn.test`
  let userId: string
  let boatId: string
  let token: string
  let reservationId: string

  beforeAll(async () => {
    userId = await createTestUser(email)
    boatId = await createTestBoat()
    token  = await getSessionToken(email)

    const { body } = await callCreateReservation(token, { boat_id: boatId, ...futureSlot(74), reason: 'Open Sail' })
    reservationId = body.reservation.id
  })

  afterAll(async () => {
    await adminClient.from('reservations').delete().eq('boat_id', boatId)
    await adminClient.from('boats').delete().eq('id', boatId)
    await adminClient.auth.admin.deleteUser(userId)
  })

  it('user can update reason and comment on own reservation', async () => {
    const client = createClient(SUPABASE_URL, SUPABASE_ANON_KEY, {
      auth: { autoRefreshToken: false, persistSession: false },
      global: { headers: { Authorization: `Bearer ${token}` } },
    })
    const { error } = await client
      .from('reservations')
      .update({ reason: 'Racing', comment: 'Crew of 4' })
      .eq('id', reservationId)
    expect(error).toBeNull()

    const { data } = await adminClient
      .from('reservations')
      .select('reason, comment')
      .eq('id', reservationId)
      .single()
    expect(data!.reason).toBe('Racing')
    expect(data!.comment).toBe('Crew of 4')
  })

  it('editing does not change the reservation status', async () => {
    const { data } = await adminClient
      .from('reservations')
      .select('status')
      .eq('id', reservationId)
      .single()
    expect(data!.status).toBe('confirmed')
  })
})

// ── RLS: cancel/edit permissions ──────────────────────────────────────────────
describe('RLS: cancel/edit permissions', () => {
  const emailA = `test-perm-a-${Date.now()}@oysqn.test`
  const emailB = `test-perm-b-${Date.now()}@oysqn.test`
  let userA: string
  let userB: string
  let boatId: string
  let tokenA: string
  let tokenB: string
  let reservationId: string

  beforeAll(async () => {
    boatId = await createTestBoat()
    userA  = await createTestUser(emailA)
    userB  = await createTestUser(emailB)
    tokenA = await getSessionToken(emailA)
    tokenB = await getSessionToken(emailB)

    const { body } = await callCreateReservation(tokenA, { boat_id: boatId, ...futureSlot(76) })
    reservationId = body.reservation.id
  })

  afterAll(async () => {
    await adminClient.from('reservations').delete().eq('boat_id', boatId)
    await adminClient.from('boats').delete().eq('id', boatId)
    await adminClient.auth.admin.deleteUser(userA)
    await adminClient.auth.admin.deleteUser(userB)
  })

  it('user B cannot cancel user A reservation', async () => {
    const clientB = createClient(SUPABASE_URL, SUPABASE_ANON_KEY, {
      auth: { autoRefreshToken: false, persistSession: false },
      global: { headers: { Authorization: `Bearer ${tokenB}` } },
    })
    // RLS silently ignores the update (matches 0 rows) — no error, but status unchanged
    await clientB
      .from('reservations')
      .update({ status: 'cancelled' })
      .eq('id', reservationId)

    const { data } = await adminClient
      .from('reservations')
      .select('status')
      .eq('id', reservationId)
      .single()
    expect(data!.status).not.toBe('cancelled')
  })

  it('user B cannot edit user A reservation', async () => {
    const clientB = createClient(SUPABASE_URL, SUPABASE_ANON_KEY, {
      auth: { autoRefreshToken: false, persistSession: false },
      global: { headers: { Authorization: `Bearer ${tokenB}` } },
    })
    await clientB
      .from('reservations')
      .update({ comment: 'injected by B' })
      .eq('id', reservationId)

    const { data } = await adminClient
      .from('reservations')
      .select('comment')
      .eq('id', reservationId)
      .single()
    expect(data!.comment).not.toBe('injected by B')
  })
})

// ── RBAC visibility: reservation_slots view ───────────────────────────────────
describe('RBAC: reservation_slots view', () => {
  const emailOwner = `test-rbac-owner-${Date.now()}@oysqn.test`
  const emailOther = `test-rbac-other-${Date.now()}@oysqn.test`
  let ownerUserId: string
  let otherUserId: string
  let boatId: string
  let ownerToken: string
  let otherToken: string

  beforeAll(async () => {
    boatId      = await createTestBoat()
    ownerUserId = await createTestUser(emailOwner)
    otherUserId = await createTestUser(emailOther)
    ownerToken  = await getSessionToken(emailOwner)
    otherToken  = await getSessionToken(emailOther)

    // Create a reservation for ownerUser directly via admin
    const slot = futureSlot(50)
    await directInsertReservation(boatId, ownerUserId, slot.start_time, slot.end_time)
  })

  afterAll(async () => {
    await adminClient.from('reservations').delete().eq('boat_id', boatId)
    await adminClient.from('boats').delete().eq('id', boatId)
    await adminClient.auth.admin.deleteUser(ownerUserId)
    await adminClient.auth.admin.deleteUser(otherUserId)
  })

  it('owner can see full reservation details', async () => {
    const client = createClient(SUPABASE_URL, SUPABASE_ANON_KEY, {
      auth: { autoRefreshToken: false, persistSession: false },
      global: { headers: { Authorization: `Bearer ${ownerToken}` } },
    })
    const { data, error } = await client.from('reservations').select('*').eq('boat_id', boatId)
    expect(error).toBeNull()
    expect(data).toHaveLength(1)
    expect(data![0].user_id).toBeDefined()
    expect(data![0].reason).toBeDefined()
  })

  it('other member sees the slot via reservation_slots (boat/time/status only)', async () => {
    const client = createClient(SUPABASE_URL, SUPABASE_ANON_KEY, {
      auth: { autoRefreshToken: false, persistSession: false },
      global: { headers: { Authorization: `Bearer ${otherToken}` } },
    })
    // reservation_slots view — should return the row
    const { data: slots, error: slotsErr } = await client
      .from('reservation_slots' as never)
      .select('id, boat_id, start_time, end_time, status')
      .eq('boat_id', boatId)
    expect(slotsErr).toBeNull()
    expect(slots).toHaveLength(1)
  })

  it('other member cannot see personal details of owner reservation via direct table query', async () => {
    const client = createClient(SUPABASE_URL, SUPABASE_ANON_KEY, {
      auth: { autoRefreshToken: false, persistSession: false },
      global: { headers: { Authorization: `Bearer ${otherToken}` } },
    })
    // Should return 0 rows — RLS "Users can read own reservations" blocks other members
    const { data } = await client.from('reservations').select('*').eq('boat_id', boatId)
    const ownerRows = (data ?? []).filter((r: { user_id: string }) => r.user_id === ownerUserId)
    expect(ownerRows).toHaveLength(0)
  })
})

// ── Cancelled reservations do not count toward weekly limit ───────────────────
describe('cancelled reservations do not count toward weekly limit', () => {
  const email = `test-cancel-weekly-${Date.now()}@oysqn.test`
  let userId: string
  let boatId: string
  let token: string

  // Fixed far-future week: 2026-07-06 (Mon) — no holidays, isolated from other tests
  const WEEK_BASE = new Date('2026-07-06T09:00:00Z')
  function weekSlot(dayOffset: number): { start_time: string; end_time: string } {
    const s = new Date(WEEK_BASE)
    s.setUTCDate(WEEK_BASE.getUTCDate() + dayOffset)
    const e = new Date(s)
    e.setUTCHours(s.getUTCHours() + 3, 30, 0, 0)
    return { start_time: s.toISOString(), end_time: e.toISOString() }
  }

  let firstReservationId: string

  beforeAll(async () => {
    userId = await createTestUser(email)
    boatId = await createTestBoat()
    token  = await getSessionToken(email)
    await adminClient.from('booking_config')
      .upsert({ key: 'max_sessions_per_week', value: 2 }, { onConflict: 'key' })
    await adminClient.from('booking_config')
      .upsert({ key: 'open_session_advance_hours', value: 1 }, { onConflict: 'key' })
  })

  afterAll(async () => {
    await adminClient.from('reservations').delete().eq('boat_id', boatId)
    await adminClient.from('boats').delete().eq('id', boatId)
    await adminClient.auth.admin.deleteUser(userId)
    await adminClient.from('booking_config')
      .upsert({ key: 'open_session_advance_hours', value: 24 }, { onConflict: 'key' })
  })

  it('first booking succeeds', async () => {
    const { status, body } = await callCreateReservation(token, { boat_id: boatId, ...weekSlot(0) })
    expect(status).toBe(201)
    firstReservationId = body.reservation.id
  })

  it('second booking succeeds', async () => {
    const { status } = await callCreateReservation(token, { boat_id: boatId, ...weekSlot(1) })
    expect(status).toBe(201)
  })

  it('third booking is rejected (limit reached)', async () => {
    const { status, body } = await callCreateReservation(token, { boat_id: boatId, ...weekSlot(2) })
    expect(status).toBe(422)
    expect(body.error.code).toBe('booking_limit_weekly')
  })

  it('cancelling the first booking frees up the slot count', async () => {
    await adminClient.from('reservations').update({ status: 'cancelled' }).eq('id', firstReservationId)
  })

  it('third booking now succeeds after cancellation', async () => {
    const { status } = await callCreateReservation(token, { boat_id: boatId, ...weekSlot(2) })
    expect(status).toBe(201)
  })
})

// ── Cancelled reservations do not count toward weekend limit ──────────────────
describe('cancelled reservations do not count toward weekend limit', () => {
  const email = `test-cancel-weekend-${Date.now()}@oysqn.test`
  let userId: string
  let boatId: string
  let token: string

  // 2026-08-01 (Saturday) and 2026-08-02 (Sunday) — same 2-week period
  const SAT = '2026-08-01T09:00:00Z'
  const SAT_END = '2026-08-01T12:30:00Z'
  const SUN = '2026-08-02T09:00:00Z'
  const SUN_END = '2026-08-02T12:30:00Z'

  let satReservationId: string

  beforeAll(async () => {
    userId = await createTestUser(email)
    boatId = await createTestBoat()
    token  = await getSessionToken(email)
    await adminClient.from('booking_config')
      .upsert({ key: 'max_weekend_sessions_per_period', value: 1 }, { onConflict: 'key' })
    await adminClient.from('booking_config')
      .upsert({ key: 'weekend_period_weeks', value: 2 }, { onConflict: 'key' })
    await adminClient.from('booking_config')
      .upsert({ key: 'open_session_advance_hours', value: 1 }, { onConflict: 'key' })
  })

  afterAll(async () => {
    await adminClient.from('reservations').delete().eq('boat_id', boatId)
    await adminClient.from('boats').delete().eq('id', boatId)
    await adminClient.auth.admin.deleteUser(userId)
    await adminClient.from('booking_config')
      .upsert({ key: 'open_session_advance_hours', value: 24 }, { onConflict: 'key' })
  })

  it('first weekend booking succeeds', async () => {
    const { status, body } = await callCreateReservation(token, {
      boat_id: boatId, start_time: SAT, end_time: SAT_END,
    })
    expect(status).toBe(201)
    satReservationId = body.reservation.id
  })

  it('second weekend booking in same period is rejected', async () => {
    const { status, body } = await callCreateReservation(token, {
      boat_id: boatId, start_time: SUN, end_time: SUN_END,
    })
    expect(status).toBe(422)
    expect(body.error.code).toBe('booking_limit_weekend')
  })

  it('cancelling the first weekend booking frees up the period count', async () => {
    await adminClient.from('reservations').update({ status: 'cancelled' }).eq('id', satReservationId)
  })

  it('second weekend booking now succeeds after cancellation', async () => {
    const { status } = await callCreateReservation(token, {
      boat_id: boatId, start_time: SUN, end_time: SUN_END,
    })
    expect(status).toBe(201)
  })
})

// ── Cannot modify past reservations ───────────────────────────────────────────
describe('cannot modify past reservations', () => {
  const email = `test-past-mod-${Date.now()}@oysqn.test`
  let userId: string
  let boatId: string
  let token: string
  let pastReservationId: string
  let futureReservationId: string

  function pastSlot() {
    const s = new Date()
    s.setUTCDate(s.getUTCDate() - 3)
    s.setUTCHours(9, 0, 0, 0)
    const e = new Date(s)
    e.setUTCHours(12, 30, 0, 0)
    return { start_time: s.toISOString(), end_time: e.toISOString() }
  }

  beforeAll(async () => {
    userId = await createTestUser(email)
    boatId = await createTestBoat()
    token  = await getSessionToken(email)

    // Admin inserts a past reservation for the test user
    const past = pastSlot()
    const { data: pastRes } = await adminClient.from('reservations').insert({
      boat_id: boatId, user_id: userId, start_time: past.start_time, end_time: past.end_time,
      reason: 'Open Sail', status: 'confirmed',
    }).select('id').single()
    pastReservationId = pastRes!.id

    // And a future one for comparison
    const { body } = await callCreateReservation(token, { boat_id: boatId, ...futureSlot(90) })
    futureReservationId = body.reservation.id
  })

  afterAll(async () => {
    await adminClient.from('reservations').delete().eq('boat_id', boatId)
    await adminClient.from('boats').delete().eq('id', boatId)
    await adminClient.auth.admin.deleteUser(userId)
  })

  it('cannot cancel a past reservation', async () => {
    const client = createClient(SUPABASE_URL, SUPABASE_ANON_KEY, {
      auth: { autoRefreshToken: false, persistSession: false },
      global: { headers: { Authorization: `Bearer ${token}` } },
    })
    const { error } = await client
      .from('reservations')
      .update({ status: 'cancelled' })
      .eq('id', pastReservationId)
    expect(error).not.toBeNull()
    expect(error!.message).toMatch(/past_reservation/)
  })

  it('cannot edit reason/comment on a past reservation', async () => {
    const client = createClient(SUPABASE_URL, SUPABASE_ANON_KEY, {
      auth: { autoRefreshToken: false, persistSession: false },
      global: { headers: { Authorization: `Bearer ${token}` } },
    })
    const { error } = await client
      .from('reservations')
      .update({ comment: 'should not work' })
      .eq('id', pastReservationId)
    expect(error).not.toBeNull()
    expect(error!.message).toMatch(/past_reservation/)
  })

  it('can still cancel a future reservation', async () => {
    const client = createClient(SUPABASE_URL, SUPABASE_ANON_KEY, {
      auth: { autoRefreshToken: false, persistSession: false },
      global: { headers: { Authorization: `Bearer ${token}` } },
    })
    const { error } = await client
      .from('reservations')
      .update({ status: 'cancelled' })
      .eq('id', futureReservationId)
    expect(error).toBeNull()
  })
})