-- =====================================================
-- Zen Zone Spa - Migration 001: Role-Based Access & Payment Tracking
-- Created: 2025-11-02
-- Purpose: Add payment tracking and remove card payment option
-- =====================================================

-- Step 1: Add payment tracking fields to massages table
ALTER TABLE massages 
ADD COLUMN payment_sent_to_momo BOOLEAN DEFAULT FALSE COMMENT 'Indicates if payment was sent to therapist via MoMo',
ADD COLUMN payment_verified BOOLEAN DEFAULT FALSE COMMENT 'Indicates if payment was verified by admin',
ADD COLUMN payment_verified_at DATETIME NULL COMMENT 'When payment was verified',
ADD COLUMN payment_verified_by INT NULL COMMENT 'Admin user who verified the payment',
ADD COLUMN payment_notes TEXT NULL COMMENT 'Notes about payment/follow-up';

-- Step 2: Add foreign key for payment_verified_by
ALTER TABLE massages 
ADD CONSTRAINT fk_payment_verified_by 
FOREIGN KEY (payment_verified_by) REFERENCES users(id) ON DELETE SET NULL;

-- Step 3: Add indexes for performance
ALTER TABLE massages 
ADD INDEX idx_payment_tracking (payment_sent_to_momo, payment_verified, session_date),
ADD INDEX idx_payment_verified (payment_verified, payment_verified_at);

-- Step 4: Update payment_method enum to remove 'card'
-- First, update any existing 'card' payments to 'cash'
UPDATE massages 
SET payment_method = 'cash' 
WHERE payment_method = 'card';

UPDATE expenses 
SET payment_method = 'cash' 
WHERE payment_method = 'card';

-- Now modify the enum
ALTER TABLE massages 
MODIFY COLUMN payment_method ENUM('cash', 'mobile_money', 'bank_transfer') DEFAULT 'cash';

ALTER TABLE expenses 
MODIFY COLUMN payment_method ENUM('cash', 'bank_transfer', 'mobile_money') DEFAULT 'cash';

-- Step 5: Ensure therapists have user_id linked (for role-based access)
-- Add index for quick lookup
ALTER TABLE therapists 
ADD INDEX idx_user_id (user_id);

-- Step 6: Verify role enum includes 'therapist'
-- (Already exists in schema, but let's ensure)
-- ALTER TABLE users 
-- MODIFY COLUMN role ENUM('admin', 'manager', 'therapist', 'staff') DEFAULT 'staff';

-- =====================================================
-- Migration Complete
-- =====================================================
-- Next steps:
-- 1. Run this migration on cPanel database
-- 2. Create test therapist user
-- 3. Link therapist to user account
-- =====================================================

