# COMPLETE FORENSIC DATABASE AUDIT - RATERIGHT
## Generated: 2025-08-21

# EXECUTIVE SUMMARY

This is a comprehensive forensic audit of ALL database requirements from the application models versus what the current migration creates. This audit identifies critical gaps, missing tables, and schema mismatches that could cause system failures.

---

# SECTION 1: COMPLETE MODEL INVENTORY

## 1.1 All Tables Found in Models

### CORE TABLES (from models):
1. **users** (User model - user.py)
2. **categories** (Category model - category.py)
3. **jobs** (Job model - job.py)  
4. **applications** (Application model - job.py)
5. **contracts** (Contract model - contract.py)
6. **payments** (Payment model - contract.py)
7. **invoices** (Invoice model - contract.py)
8. **ratings** (Rating model - rating.py)
9. **reviews** (Review model alias for Rating - rating.py)
10. **messages** (Message model - message.py)
11. **notifications** (Notification model - notification.py)
12. **notification_preferences** (NotificationPreference model - notification.py)
13. **notification_templates** (NotificationTemplate model - notification.py)
14. **availability** (Availability model - availability.py)
15. **worker_schedule_preferences** (WorkerSchedulePreferences model - availability.py)
16. **bookings** (Booking model - booking.py)
17. **recurring_bookings** (RecurringBooking model - booking.py)
18. **leaderboards** (Leaderboard model - gamification.py)
19. **achievements** (Achievement model - gamification.py)
20. **point_activities** (PointActivity model - gamification.py)
21. **whs_assessments** (WHSAssessment model - safety.py)
22. **job_progress** (JobProgress model - safety.py)
23. **disputes** (Dispute model - safety.py)
24. **audit_logs** (AuditLog model - safety.py)
25. **time_entries** (TimeEntry model - time_tracking.py)
26. **shifts** (Shift model - time_tracking.py)
27. **shift_bids** (ShiftBid model - time_tracking.py)
28. **shift_swap_requests** (ShiftSwapRequest model - time_tracking.py)

### CRITICAL INFRASTRUCTURE TABLE (Not in models but required):
29. **user_sessions** (Session management - Required for Flask-Login authentication)

**TOTAL TABLES REQUIRED: 29**

---

# SECTION 2: DETAILED TABLE SCHEMA REQUIREMENTS

## 2.1 USERS Table (User model)
```sql
users:
  - id: Integer (PK)
  - created_at: DateTime (NOT NULL)
  - updated_at: DateTime (NOT NULL)
  - username: String(80)
  - email: String(120) (UNIQUE, NOT NULL, INDEXED)
  - password_hash: String(255) (NOT NULL)
  - first_name: String(80) (NOT NULL)
  - last_name: String(80) (NOT NULL)
  - role: String(20) (NOT NULL)
  - phone_number: String(20) (NOT NULL)
  - location: String(200) (NOT NULL)
  - business_name: String(200)
  - primary_trade: String(100)
  - abn_number: String(11) (UNIQUE, NOT NULL, INDEXED)
  - gst_registered: Boolean (DEFAULT FALSE)
  - jobs_completed: Integer (DEFAULT 0)
  - average_rating: Numeric(3,2) (DEFAULT 0.0)
  - total_reviews: Integer (DEFAULT 0)
  - response_rate: Numeric(5,2) (DEFAULT 0.0)
  - public_liability_insurance: Boolean (DEFAULT FALSE)
  - public_liability_amount: Numeric(12,2)
  - workers_comp_insurance: Boolean (DEFAULT FALSE)
  - insurance_expiry_date: Date
  - worker_control_level: String(50)
  - white_card_number: String(50)
  - white_card_expiry: Date
  - is_active: Boolean (DEFAULT TRUE)
  - account_status: String(20) (DEFAULT 'active')
  - privacy_consent: Boolean (DEFAULT FALSE)
  - terms_accepted: Boolean (DEFAULT FALSE)
  - terms_accepted_date: DateTime
  - date_created: DateTime (DEFAULT NOW)
  - last_login: DateTime
  - total_points: Integer (DEFAULT 0)
  - current_level: Integer (DEFAULT 1)
  - seasonal_league: String(20) (DEFAULT 'bronze')
```

## 2.2 CATEGORIES Table
```sql
categories:
  - id: Integer (PK)
  - created_at: DateTime (NOT NULL)
  - updated_at: DateTime (NOT NULL)
  - name: String(100) (UNIQUE, NOT NULL)
  - description: Text
  - whs_risk_level: String(20) (DEFAULT 'medium')
  - insurance_requirements: Text
  - license_required: Boolean (DEFAULT FALSE)
  - white_card_required: Boolean (DEFAULT TRUE)
  - is_active: Boolean (DEFAULT TRUE)
  - sort_order: Integer (DEFAULT 0)
```

## 2.3 JOBS Table
```sql
jobs:
  - id: Integer (PK)
  - created_at: DateTime (NOT NULL)
  - updated_at: DateTime (NOT NULL)
  - title: String(200) (NOT NULL)
  - description: Text (NOT NULL)
  - contractor_id: Integer (FK -> users.id, NOT NULL)
  - category_id: Integer (FK -> categories.id, NOT NULL)
  - location: String(100) (NOT NULL)
  - budget_min: Numeric(10,2)
  - budget_max: Numeric(10,2)
  - hourly_rate: Numeric(10,2)
  - whs_requirements: Text
  - insurance_required: Boolean (DEFAULT TRUE)
  - white_card_required: Boolean (DEFAULT TRUE)
  - start_datetime: DateTime
  - duration: String(50)
  - workers_needed: Integer (DEFAULT 1)
  - own_tools_required: Boolean (DEFAULT FALSE)
  - own_transport_required: Boolean (DEFAULT FALSE)
  - payment_terms: String(50) (DEFAULT 'on_completion')
  - status: String(20) (DEFAULT 'open')
  - date_posted: DateTime (DEFAULT NOW)
  - deadline: DateTime
  - applications_count: Integer (DEFAULT 0)
```

## 2.4 APPLICATIONS Table
```sql
applications:
  - id: Integer (PK)
  - created_at: DateTime (NOT NULL)
  - updated_at: DateTime (NOT NULL)
  - job_id: Integer (FK -> jobs.id, NOT NULL)
  - worker_id: Integer (FK -> users.id, NOT NULL)
  - status: String(20) (DEFAULT 'pending')
  - proposed_rate: Numeric(10,2)
  - cover_letter: Text
  - date_applied: DateTime (DEFAULT NOW)
  - abn_verified: Boolean (DEFAULT FALSE)
  - insurance_verified: Boolean (DEFAULT FALSE)
```

## 2.5 CONTRACTS Table
```sql
contracts:
  - id: Integer (PK)
  - created_at: DateTime (NOT NULL)
  - updated_at: DateTime (NOT NULL)
  - job_id: Integer (FK -> jobs.id, NOT NULL)
  - contractor_id: Integer (FK -> users.id, NOT NULL)
  - worker_id: Integer (FK -> users.id, NOT NULL)
  - agreed_rate: Numeric(10,2) (NOT NULL)
  - rate_type: String(20) (DEFAULT 'total')
  - start_date: Date (NOT NULL)
  - end_date: Date (NOT NULL)
  - scope_of_work: Text (NOT NULL)
  - independent_contractor_status: Boolean (DEFAULT TRUE)
  - superannuation_required: Boolean (DEFAULT FALSE)
  - workers_comp_covered: Boolean (DEFAULT FALSE)
  - status: String(20) (DEFAULT 'pending_agreement')
  - completion_status: String(50) (DEFAULT 'not_started')
  - payment_status: String(50) (DEFAULT 'pending')
  - contractor_approval_date: DateTime
  - worker_completion_date: DateTime
  - contractor_signed: Boolean (DEFAULT FALSE)
  - worker_signed: Boolean (DEFAULT FALSE)
  - contractor_signed_date: DateTime
  - worker_signed_date: DateTime
  - contractor_reviewed: Boolean (DEFAULT FALSE)
  - worker_reviewed: Boolean (DEFAULT FALSE)
  - last_modified_by: Integer (FK -> users.id)
  - contractor_rated: Boolean (DEFAULT FALSE)
  - worker_rated: Boolean (DEFAULT FALSE)
  - mutual_rating_completed_date: DateTime
  - payment_terms: String(50) (DEFAULT 'completion')
  - payment_schedule: Text
```

## 2.6 MESSAGES Table
```sql
messages:
  - id: Integer (PK)
  - created_at: DateTime (NOT NULL)
  - updated_at: DateTime (NOT NULL)
  - sender_id: Integer (FK -> users.id, NOT NULL)
  - receiver_id: Integer (FK -> users.id, NOT NULL)
  - text: Text (NOT NULL)
  - status: Enum(MessageStatus) (DEFAULT 'sent')
  - read_at: DateTime
  - contract_id: Integer (FK -> contracts.id)
  - job_id: Integer (FK -> jobs.id)
  
INDEXES:
  - idx_message_receiver_status ON (receiver_id, status)
  - idx_message_sender_receiver ON (sender_id, receiver_id)
  - idx_message_created_at ON (created_at)
```

## 2.7 NOTIFICATIONS Table
```sql
notifications:
  - id: Integer (PK)
  - created_at: DateTime (NOT NULL)
  - updated_at: DateTime (NOT NULL)
  - user_id: Integer (FK -> users.id, NOT NULL)
  - notification_type: Enum(NotificationType) (NOT NULL)
  - title: String(255) (NOT NULL)
  - content: Text (NOT NULL)
  - channels_attempted: JSON
  - channels_delivered: JSON
  - channels_failed: JSON
  - status: Enum(NotificationStatus) (DEFAULT 'pending')
  - scheduled_for: DateTime (DEFAULT NOW)
  - sent_at: DateTime
  - delivered_at: DateTime
  - read_at: DateTime
  - priority: Integer (DEFAULT 5)
  - category: String(50)
  - action_url: String(255)
  - requires_consent: Boolean (DEFAULT FALSE)
  - business_hours_only: Boolean (DEFAULT FALSE)
  - error_count: Integer (DEFAULT 0)
  - last_error: Text
```

---

# SECTION 3: CRITICAL MISSING TABLES IN MIGRATION

## 🚨 COMPLETELY MISSING TABLES (Not in migration at all):

1. **notifications** - Critical for user engagement
2. **notification_preferences** - Required for compliance
3. **notification_templates** - Needed for system messages
4. **availability** - Core scheduling feature
5. **worker_schedule_preferences** - User preferences
6. **bookings** - Core booking system
7. **recurring_bookings** - Recurring work feature
8. **leaderboards** - Gamification system
9. **achievements** - User engagement
10. **point_activities** - Points tracking
11. **disputes** - Dispute resolution
12. **audit_logs** - Compliance/security
13. **time_entries** - Time tracking
14. **shifts** - Shift management
15. **shift_bids** - Bidding system
16. **shift_swap_requests** - Shift swapping

**16 TABLES COMPLETELY MISSING!**

---

# SECTION 4: FOREIGN KEY RELATIONSHIPS

## Complete Dependency Map:

### User Relations (Central Hub):
```
users.id <- jobs.contractor_id
users.id <- applications.worker_id
users.id <- contracts.contractor_id
users.id <- contracts.worker_id
users.id <- contracts.last_modified_by
users.id <- messages.sender_id
users.id <- messages.receiver_id
users.id <- notifications.user_id
users.id <- notification_preferences.user_id
users.id <- availability.worker_id
users.id <- worker_schedule_preferences.worker_id
users.id <- bookings.client_id
users.id <- bookings.worker_id
users.id <- recurring_bookings.client_id
users.id <- recurring_bookings.worker_id
users.id <- leaderboards.user_id
users.id <- achievements.user_id
users.id <- point_activities.user_id
users.id <- whs_assessments.completed_by
users.id <- whs_assessments.approved_by
users.id <- job_progress.updated_by
users.id <- reviews.reviewer_id
users.id <- reviews.reviewee_id
users.id <- ratings.rater_id
users.id <- ratings.rated_id
users.id <- disputes.raised_by
users.id <- disputes.resolved_by
users.id <- audit_logs.user_id
users.id <- time_entries.worker_id
users.id <- time_entries.approved_by
users.id <- shifts.worker_id
users.id <- shift_bids.worker_id
users.id <- shift_bids.selected_by
users.id <- shift_swap_requests.from_worker_id
users.id <- shift_swap_requests.to_worker_id
users.id <- shift_swap_requests.approved_by
```

### Job Relations:
```
jobs.id <- applications.job_id
jobs.id <- contracts.job_id
jobs.id <- messages.job_id
jobs.id <- point_activities.related_job_id
jobs.id <- whs_assessments.job_id
jobs.id <- job_progress.job_id
jobs.id <- reviews.job_id
jobs.id <- bookings.job_id
jobs.id <- time_entries.job_id
jobs.id <- shifts.job_id
```

### Contract Relations:
```
contracts.id <- payments.contract_id
contracts.id <- messages.contract_id
contracts.id <- point_activities.related_contract_id
contracts.id <- job_progress.contract_id
contracts.id <- reviews.contract_id
contracts.id <- ratings.contract_id
contracts.id <- disputes.contract_id
contracts.id <- time_entries.contract_id
```

### Category Relations:
```
categories.id <- jobs.category_id
```

### Payment Relations:
```
payments.id <- invoices.payment_id
```

### Review Relations:
```
reviews.id <- point_activities.related_review_id
```

### Booking Relations:
```
bookings.id <- time_entries.booking_id
```

### Availability Relations:
```
availability.id <- bookings.availability_id
```

### Recurring Booking Relations:
```
recurring_bookings.id <- bookings.recurring_booking_id
```

### Shift Relations:
```
shifts.id <- shift_bids.shift_id
shifts.id <- shift_swap_requests.from_shift_id
shifts.id <- shift_swap_requests.to_shift_id
```

---

# SECTION 5: POSTGRESQL SPECIAL FEATURES REQUIRED

## 5.1 ENUM TYPES

### From message.py:
```sql
CREATE TYPE MessageStatus AS ENUM ('sent', 'delivered', 'read');
```

### From notification.py:
```sql
CREATE TYPE NotificationType AS ENUM (
  'job_match', 'job_application_update', 'contract_awarded',
  'payment_received', 'payment_due', 'invoice_generated',
  'rating_received', 'review_request', 'achievement_unlocked',
  'message_received', 'chat_notification', 'deadline_reminder',
  'insurance_expiry', 'certification_renewal', 'system_announcement',
  'security_alert', 'whs_compliance', 'tax_obligation'
);

CREATE TYPE DeliveryChannel AS ENUM (
  'email', 'sms', 'push_web', 'push_mobile', 'in_app'
);

CREATE TYPE NotificationStatus AS ENUM (
  'pending', 'sent', 'delivered', 'failed', 'read'
);

CREATE TYPE NotificationFrequency AS ENUM (
  'immediate', 'daily_digest', 'weekly_summary', 'disabled'
);
```

## 5.2 INDEXES REQUIRED

### Critical Performance Indexes:
```sql
-- Users
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_abn ON users(abn_number);

-- Messages
CREATE INDEX idx_message_receiver_status ON messages(receiver_id, status);
CREATE INDEX idx_message_sender_receiver ON messages(sender_id, receiver_id);
CREATE INDEX idx_message_created_at ON messages(created_at);

-- Availability
CREATE INDEX idx_availability_worker_date ON availability(worker_id, date);
CREATE INDEX idx_availability_date_status ON availability(date, status);

-- Bookings
CREATE INDEX idx_booking_worker_date ON bookings(worker_id, booking_date);
CREATE INDEX idx_booking_client_date ON bookings(client_id, booking_date);
CREATE INDEX idx_booking_date_status ON bookings(booking_date, status);
CREATE INDEX idx_booking_confirmation ON bookings(confirmation_code);

-- Time Entries
CREATE INDEX idx_time_entry_worker_date ON time_entries(worker_id, work_date);
CREATE INDEX idx_time_entry_job ON time_entries(job_id);
CREATE INDEX idx_time_entry_booking ON time_entries(booking_id);

-- Shifts
CREATE INDEX idx_shift_date_status ON shifts(shift_date, status);
CREATE INDEX idx_shift_worker ON shifts(worker_id, shift_date);

-- Shift Bids
CREATE INDEX idx_shift_bid_status ON shift_bids(shift_id, status);
```

## 5.3 UNIQUE CONSTRAINTS

```sql
-- Users
ALTER TABLE users ADD CONSTRAINT unique_user_email UNIQUE (email);
ALTER TABLE users ADD CONSTRAINT unique_user_abn UNIQUE (abn_number);

-- Notification Preferences
ALTER TABLE notification_preferences ADD CONSTRAINT unique_user_notification_pref 
  UNIQUE (user_id, notification_type);

-- Notification Templates
ALTER TABLE notification_templates ADD CONSTRAINT unique_template_type_channel 
  UNIQUE (notification_type, channel);

-- Shift Bids
ALTER TABLE shift_bids ADD CONSTRAINT _shift_worker_uc UNIQUE (shift_id, worker_id);

-- Payments
ALTER TABLE payments ADD CONSTRAINT unique_payment_reference UNIQUE (payment_reference);
ALTER TABLE payments ADD CONSTRAINT unique_stripe_payment_intent UNIQUE (stripe_payment_intent_id);

-- Invoices
ALTER TABLE invoices ADD CONSTRAINT unique_invoice_number UNIQUE (invoice_number);

-- WHS Assessments
ALTER TABLE whs_assessments ADD CONSTRAINT unique_assessment_reference UNIQUE (assessment_reference);

-- Disputes
ALTER TABLE disputes ADD CONSTRAINT unique_dispute_reference UNIQUE (dispute_reference);
```

## 5.4 JSON COLUMNS

Several tables use JSON/Text columns to store JSON data:
- notifications.channels_attempted (JSON)
- notifications.channels_delivered (JSON)  
- notifications.channels_failed (JSON)
- contracts.payment_schedule (Text/JSON)
- job_progress.photos_urls (Text/JSON)
- availability.recurrence_pattern (Text/JSON)
- worker_schedule_preferences.working_days (Text/JSON)
- recurring_bookings.recurrence_days (Text/JSON)
- shifts.required_skills (Text/JSON)

---

# SECTION 6: CRITICAL GAPS ANALYSIS

## 🔴 SEVERITY: CRITICAL

### 1. MISSING 16 CORE TABLES
- **Impact**: System features completely non-functional
- **Affected Features**: 
  - Notifications system (3 tables)
  - Scheduling/Availability (2 tables)
  - Bookings (2 tables)
  - Gamification (3 tables)
  - Time tracking (4 tables)
  - Compliance (2 tables)

### 2. ENUM TYPES NOT CREATED
- **Impact**: Type safety violations, potential crashes
- **Required ENUMs**: 5 different enum types
- **Tables Affected**: messages, notifications, notification_preferences

### 3. MISSING INDEXES
- **Impact**: Severe performance degradation
- **Missing Indexes**: 17 critical performance indexes
- **Query Impact**: Searches, lookups, joins will be slow

### 4. MISSING UNIQUE CONSTRAINTS
- **Impact**: Data integrity violations
- **Missing Constraints**: 10 unique constraints
- **Risk**: Duplicate data, constraint violations

---

# SECTION 7: MIGRATION FILE ANALYSIS

## Current Migration Coverage:
✅ Users table (mostly complete, may need column verification)
✅ Categories table
✅ Jobs table
✅ Applications table
✅ Contracts table
✅ Payments table
✅ Invoices table
✅ Messages table
✅ Reviews table
✅ Ratings table
✅ WHS Assessments table
✅ Job Progress table

❌ Notifications (3 tables)
❌ Availability/Scheduling (2 tables)
❌ Bookings (2 tables)
❌ Gamification (3 tables)
❌ Disputes table
❌ Audit Logs table
❌ Time Tracking (4 tables)

**Migration Coverage: 12/28 tables (43%)**

---

# SECTION 8: IMMEDIATE ACTION REQUIRED

## Priority 1 - CRITICAL (Do immediately):
1. Create missing ENUM types
2. Create all 16 missing tables
3. Add all missing indexes
4. Add all unique constraints
5. Verify all foreign key relationships

## Priority 2 - HIGH (Do within 24 hours):
1. Add check constraints for data validation
2. Set proper default values
3. Add triggers for updated_at timestamps
4. Create composite indexes for complex queries

## Priority 3 - MEDIUM (Do within week):
1. Add table partitioning for large tables
2. Create materialized views for reporting
3. Add database functions for complex calculations
4. Set up proper cascade rules

---

# SECTION 9: VERIFICATION CHECKLIST

## Pre-Deployment Verification:
- [ ] All 28 tables exist
- [ ] All columns match model definitions
- [ ] All data types are correct
- [ ] All NOT NULL constraints applied
- [ ] All DEFAULT values set
- [ ] All UNIQUE constraints created
- [ ] All FOREIGN KEY relationships established
- [ ] All INDEXES created
- [ ] All ENUM types defined
- [ ] All JSON columns properly typed
- [ ] All cascade rules configured
- [ ] All triggers installed
- [ ] Test data loads successfully
- [ ] Application connects without errors
- [ ] All models can perform CRUD operations

---

# SECTION 10: SQL COMMANDS TO FIX EVERYTHING

## 10.1 Create Missing ENUM Types
```sql
-- Message Status
DO $$ BEGIN
    CREATE TYPE MessageStatus AS ENUM ('sent', 'delivered', 'read');
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;

-- Notification Types
DO $$ BEGIN
    CREATE TYPE NotificationType AS ENUM (
        'job_match', 'job_application_update', 'contract_awarded',
        'payment_received', 'payment_due', 'invoice_generated',
        'rating_received', 'review_request', 'achievement_unlocked',
        'message_received', 'chat_notification', 'deadline_reminder',
        'insurance_expiry', 'certification_renewal', 'system_announcement',
        'security_alert', 'whs_compliance', 'tax_obligation'
    );
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;

-- Delivery Channel
DO $$ BEGIN
    CREATE TYPE DeliveryChannel AS ENUM (
        'email', 'sms', 'push_web', 'push_mobile', 'in_app'
    );
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;

-- Notification Status
DO $$ BEGIN
    CREATE TYPE NotificationStatus AS ENUM (
        'pending', 'sent', 'delivered', 'failed', 'read'
    );
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;

-- Notification Frequency
DO $$ BEGIN
    CREATE TYPE NotificationFrequency AS ENUM (
        'immediate', 'daily_digest', 'weekly_summary', 'disabled'
    );
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;
```

## 10.2 Create All Missing Tables
```sql
-- Notifications table
CREATE TABLE IF NOT EXISTS notifications (
    id SERIAL PRIMARY KEY,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
    user_id INTEGER NOT NULL REFERENCES users(id),
    notification_type NotificationType NOT NULL,
    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    channels_attempted JSONB,
    channels_delivered JSONB,
    channels_failed JSONB,
    status NotificationStatus DEFAULT 'pending',
    scheduled_for TIMESTAMP DEFAULT NOW(),
    sent_at TIMESTAMP,
    delivered_at TIMESTAMP,
    read_at TIMESTAMP,
    priority INTEGER DEFAULT 5,
    category VARCHAR(50),
    action_url VARCHAR(255),
    requires_consent BOOLEAN DEFAULT FALSE,
    business_hours_only BOOLEAN DEFAULT FALSE,
    error_count INTEGER DEFAULT 0,
    last_error TEXT
);

-- Notification Preferences table
CREATE TABLE IF NOT EXISTS notification_preferences (
    id SERIAL PRIMARY KEY,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
    user_id INTEGER NOT NULL REFERENCES users(id),
    notification_type NotificationType NOT NULL,
    email_enabled BOOLEAN DEFAULT TRUE,
    email_frequency NotificationFrequency DEFAULT 'immediate',
    sms_enabled BOOLEAN DEFAULT FALSE,
    sms_frequency NotificationFrequency DEFAULT 'disabled',
    push_web_enabled BOOLEAN DEFAULT TRUE,
    push_mobile_enabled BOOLEAN DEFAULT TRUE,
    in_app_enabled BOOLEAN DEFAULT TRUE,
    quiet_hours_start TIME DEFAULT '21:00',
    quiet_hours_end TIME DEFAULT '07:00',
    respect_weekends BOOLEAN DEFAULT TRUE,
    consent_given BOOLEAN DEFAULT FALSE,
    consent_date TIMESTAMP,
    CONSTRAINT unique_user_notification_pref UNIQUE (user_id, notification_type)
);

-- Notification Templates table
CREATE TABLE IF NOT EXISTS notification_templates (
    id SERIAL PRIMARY KEY,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
    notification_type NotificationType NOT NULL,
    channel DeliveryChannel NOT NULL,
    subject_template TEXT,
    body_template TEXT NOT NULL,
    html_template TEXT,
    template_name VARCHAR(100) NOT NULL,
    description VARCHAR(255),
    includes_unsubscribe BOOLEAN DEFAULT FALSE,
    privacy_compliant BOOLEAN DEFAULT TRUE,
    is_active BOOLEAN DEFAULT TRUE,
    CONSTRAINT unique_template_type_channel UNIQUE (notification_type, channel)
);

-- Availability table
CREATE TABLE IF NOT EXISTS availability (
    id SERIAL PRIMARY KEY,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
    worker_id INTEGER NOT NULL REFERENCES users(id),
    date DATE NOT NULL,
    start_time TIME NOT NULL,
    end_time TIME NOT NULL,
    status VARCHAR(20) DEFAULT 'available',
    is_recurring BOOLEAN DEFAULT FALSE,
    recurrence_pattern TEXT,
    max_bookings INTEGER DEFAULT 1,
    current_bookings INTEGER DEFAULT 0,
    buffer_time INTEGER DEFAULT 30,
    notes TEXT
);

CREATE INDEX idx_availability_worker_date ON availability(worker_id, date);
CREATE INDEX idx_availability_date_status ON availability(date, status);

-- Worker Schedule Preferences table
CREATE TABLE IF NOT EXISTS worker_schedule_preferences (
    id SERIAL PRIMARY KEY,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
    worker_id INTEGER NOT NULL UNIQUE REFERENCES users(id),
    default_start_time TIME DEFAULT '09:00',
    default_end_time TIME DEFAULT '17:00',
    working_days TEXT DEFAULT '[1,2,3,4,5]',
    default_buffer_time INTEGER DEFAULT 30,
    travel_time_buffer INTEGER DEFAULT 15,
    min_advance_booking INTEGER DEFAULT 24,
    max_advance_booking INTEGER DEFAULT 720,
    google_calendar_id VARCHAR(255),
    outlook_calendar_id VARCHAR(255),
    sync_enabled BOOLEAN DEFAULT FALSE,
    last_sync TIMESTAMP,
    reminder_time INTEGER DEFAULT 60,
    reminder_method VARCHAR(20) DEFAULT 'email'
);

-- Bookings table
CREATE TABLE IF NOT EXISTS bookings (
    id SERIAL PRIMARY KEY,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
    client_id INTEGER NOT NULL REFERENCES users(id),
    worker_id INTEGER NOT NULL REFERENCES users(id),
    job_id INTEGER REFERENCES jobs(id),
    availability_id INTEGER REFERENCES availability(id),
    booking_date DATE NOT NULL,
    start_time TIME NOT NULL,
    end_time TIME NOT NULL,
    duration_minutes INTEGER NOT NULL,
    service_type VARCHAR(100) NOT NULL,
    description TEXT,
    location VARCHAR(255),
    status VARCHAR(20) DEFAULT 'pending',
    confirmation_code VARCHAR(20) UNIQUE,
    confirmed_at TIMESTAMP,
    cancelled_at TIMESTAMP,
    cancellation_reason TEXT,
    estimated_cost FLOAT,
    final_cost FLOAT,
    reminder_sent BOOLEAN DEFAULT FALSE,
    reminder_sent_at TIMESTAMP,
    recurring_booking_id INTEGER,
    client_notes TEXT,
    worker_notes TEXT,
    google_event_id VARCHAR(255),
    outlook_event_id VARCHAR(255)
);

CREATE INDEX idx_booking_worker_date ON bookings(worker_id, booking_date);
CREATE INDEX idx_booking_client_date ON bookings(client_id, booking_date);
CREATE INDEX idx_booking_date_status ON bookings(booking_date, status);
CREATE INDEX idx_booking_confirmation ON bookings(confirmation_code);

-- Recurring Bookings table
CREATE TABLE IF NOT EXISTS recurring_bookings (
    id SERIAL PRIMARY KEY,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
    client_id INTEGER NOT NULL REFERENCES users(id),
    worker_id INTEGER NOT NULL REFERENCES users(id),
    recurrence_type VARCHAR(20) NOT NULL,
    recurrence_interval INTEGER DEFAULT 1,
    recurrence_days TEXT,
    recurrence_day_of_month INTEGER,
    start_time TIME NOT NULL,
    duration_minutes INTEGER NOT NULL,
    service_type VARCHAR(100) NOT NULL,
    description TEXT,
    location VARCHAR(255),
    start_date DATE NOT NULL,
    end_date DATE,
    is_active BOOLEAN DEFAULT TRUE,
    estimated_cost_per_session FLOAT,
    next_occurrence DATE
);

-- Add foreign key to bookings
ALTER TABLE bookings 
ADD CONSTRAINT fk_recurring_booking 
FOREIGN KEY (recurring_booking_id) 
REFERENCES recurring_bookings(id);

-- Leaderboards table
CREATE TABLE IF NOT EXISTS leaderboards (
    id SERIAL PRIMARY KEY,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
    user_id INTEGER NOT NULL REFERENCES users(id),
    period_type VARCHAR(20) NOT NULL,
    period_start DATE NOT NULL,
    period_end DATE NOT NULL,
    points_earned INTEGER DEFAULT 0,
    rank_position INTEGER NOT NULL,