mock-data.sql

-- Create tables for housing management system
-- Drop NOT NULL constraint temporarily to allow cleanup
ALTER TABLE transfer_requests ALTER COLUMN current_room DROP NOT NULL;

-- Clear ALL existing data first to avoid conflicts
DELETE FROM transfer_requests;
DELETE FROM maintenance_requests;
DELETE FROM assignments;

-- Update existing rooms table and constraints
UPDATE rooms SET room_type = 'standard' WHERE room_type IS NULL;
ALTER TABLE rooms DROP CONSTRAINT IF EXISTS rooms_room_type_check;
ALTER TABLE rooms ADD CONSTRAINT rooms_room_type_check CHECK (room_type IN ('basic', 'standard', 'premium'));
ALTER TABLE rooms ADD COLUMN IF NOT EXISTS monthly_cost DECIMAL(10,2) DEFAULT 800.00;
ALTER TABLE rooms ADD COLUMN IF NOT EXISTS gender_restriction TEXT;
ALTER TABLE rooms ADD COLUMN IF NOT EXISTS created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW();

-- Add missing columns to existing employees table
ALTER TABLE employees ADD COLUMN IF NOT EXISTS full_name TEXT;
ALTER TABLE employees ADD COLUMN IF NOT EXISTS name TEXT;
ALTER TABLE employees ADD COLUMN IF NOT EXISTS email TEXT;
ALTER TABLE employees ADD COLUMN IF NOT EXISTS phone TEXT;
ALTER TABLE employees ADD COLUMN IF NOT EXISTS department TEXT;
ALTER TABLE employees ADD COLUMN IF NOT EXISTS position TEXT;
ALTER TABLE employees ADD COLUMN IF NOT EXISTS gender TEXT;
ALTER TABLE employees ADD COLUMN IF NOT EXISTS nationality TEXT;
ALTER TABLE employees ADD COLUMN IF NOT EXISTS status TEXT DEFAULT 'active';
ALTER TABLE employees ADD COLUMN IF NOT EXISTS job_grade TEXT DEFAULT '5';
ALTER TABLE employees ADD COLUMN IF NOT EXISTS hire_date DATE DEFAULT '2024-01-01';
ALTER TABLE employees ADD COLUMN IF NOT EXISTS created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW();

-- Update job_grade constraint to allow numeric grades
ALTER TABLE employees DROP CONSTRAINT IF EXISTS employees_job_grade_check;
ALTER TABLE employees ADD CONSTRAINT employees_job_grade_check CHECK (job_grade IN ('1', '2', '3', '4', '5', '6', '7', '8', '9', '10'));

-- Update any existing null values to avoid constraint violations
UPDATE employees SET full_name = 'Unknown' WHERE full_name IS NULL;
UPDATE employees SET name = 'Unknown' WHERE name IS NULL;
UPDATE employees SET email = 'unknown@company.com' WHERE email IS NULL;
UPDATE employees SET phone = '+966500000000' WHERE phone IS NULL;
UPDATE employees SET department = 'General' WHERE department IS NULL;
UPDATE employees SET position = 'Employee' WHERE position IS NULL;
UPDATE employees SET gender = 'male' WHERE gender IS NULL;
UPDATE employees SET nationality = 'Unknown' WHERE nationality IS NULL;
UPDATE employees SET status = 'active' WHERE status IS NULL;
UPDATE employees SET job_grade = '5' WHERE job_grade IS NULL;
UPDATE employees SET hire_date = '2024-01-01' WHERE hire_date IS NULL;

-- Update rooms null values
UPDATE rooms SET room_type = 'standard' WHERE room_type IS NULL;
UPDATE rooms SET gender_restriction = 'mixed' WHERE gender_restriction IS NULL;
UPDATE rooms SET status = 'available' WHERE status IS NULL;

-- Add missing columns to maintenance_requests table
ALTER TABLE maintenance_requests ADD COLUMN IF NOT EXISTS employee_id UUID REFERENCES employees(id) ON DELETE CASCADE;
ALTER TABLE maintenance_requests ADD COLUMN IF NOT EXISTS title TEXT;
ALTER TABLE maintenance_requests ADD COLUMN IF NOT EXISTS description TEXT;
ALTER TABLE maintenance_requests ADD COLUMN IF NOT EXISTS priority TEXT DEFAULT 'medium';
ALTER TABLE maintenance_requests ADD COLUMN IF NOT EXISTS requested_date TIMESTAMP WITH TIME ZONE DEFAULT NOW();
ALTER TABLE maintenance_requests ADD COLUMN IF NOT EXISTS completed_date TIMESTAMP WITH TIME ZONE;

-- Add missing columns to transfer_requests table
ALTER TABLE transfer_requests ADD COLUMN IF NOT EXISTS current_room_id UUID REFERENCES rooms(id) ON DELETE CASCADE;
ALTER TABLE transfer_requests ADD COLUMN IF NOT EXISTS requested_room_id UUID REFERENCES rooms(id) ON DELETE CASCADE;
ALTER TABLE transfer_requests ADD COLUMN IF NOT EXISTS reason TEXT;
ALTER TABLE transfer_requests ADD COLUMN IF NOT EXISTS requested_date TIMESTAMP WITH TIME ZONE DEFAULT NOW();
ALTER TABLE transfer_requests ADD COLUMN IF NOT EXISTS processed_date TIMESTAMP WITH TIME ZONE;
ALTER TABLE transfer_requests ADD COLUMN IF NOT EXISTS processed_by UUID;
ALTER TABLE transfer_requests ADD COLUMN IF NOT EXISTS notes TEXT;

-- Add missing columns to meals table
ALTER TABLE meals ADD COLUMN IF NOT EXISTS meal_name TEXT DEFAULT 'Daily Meal';
ALTER TABLE meals ADD COLUMN IF NOT EXISTS category TEXT DEFAULT 'main';
ALTER TABLE meals ADD COLUMN IF NOT EXISTS available_date DATE DEFAULT CURRENT_DATE;
ALTER TABLE meals ADD COLUMN IF NOT EXISTS date DATE DEFAULT CURRENT_DATE;
ALTER TABLE meals ADD COLUMN IF NOT EXISTS menu_items TEXT;
ALTER TABLE meals ADD COLUMN IF NOT EXISTS price DECIMAL(8,2);
ALTER TABLE meals ADD COLUMN IF NOT EXISTS available_quantity INTEGER;
ALTER TABLE meals ADD COLUMN IF NOT EXISTS remaining_quantity INTEGER;
ALTER TABLE meals ADD COLUMN IF NOT EXISTS location TEXT;

-- Update meals constraint to allow common categories
ALTER TABLE meals DROP CONSTRAINT IF EXISTS meals_category_check;
ALTER TABLE meals ADD CONSTRAINT meals_category_check CHECK (category IN ('main', 'appetizer', 'dessert', 'beverage'));

-- Update existing meals null values
UPDATE meals SET meal_name = 'Daily Meal' WHERE meal_name IS NULL;
UPDATE meals SET category = 'main' WHERE category IS NULL;
UPDATE meals SET available_date = CURRENT_DATE WHERE available_date IS NULL;

-- Update existing transfer_requests null values
UPDATE transfer_requests SET current_room = 'A101' WHERE current_room IS NULL;
UPDATE transfer_requests SET reason = 'General request' WHERE reason IS NULL;

-- Clear existing data to avoid conflicts
DELETE FROM transfer_requests;
DELETE FROM maintenance_requests;
DELETE FROM assignments;
DELETE FROM rooms WHERE room_number IN ('A101', 'A102', 'A103', 'A201', 'A202', 'B101', 'B102', 'B201', 'C101', 'C102');
DELETE FROM employees WHERE employee_id LIKE 'EMP%';

CREATE TABLE IF NOT EXISTS rooms (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  room_number TEXT NOT NULL,
  block TEXT NOT NULL,
  floor INTEGER NOT NULL,
  capacity INTEGER NOT NULL,
  monthly_cost DECIMAL(10,2) NOT NULL,
  gender_restriction TEXT CHECK (gender_restriction IN ('male', 'female', 'mixed')),
  status TEXT DEFAULT 'available' CHECK (status IN ('available', 'occupied', 'maintenance', 'reserved')),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS employees (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  employee_id TEXT UNIQUE NOT NULL,
  name TEXT NOT NULL,
  email TEXT,
  phone TEXT,
  department TEXT,
  position TEXT,
  gender TEXT CHECK (gender IN ('male', 'female')),
  nationality TEXT,
  status TEXT DEFAULT 'active' CHECK (status IN ('active', 'inactive', 'terminated')),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS assignments (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  employee_id UUID REFERENCES employees(id) ON DELETE CASCADE,
  room_id UUID REFERENCES rooms(id) ON DELETE CASCADE,
  assigned_date DATE DEFAULT CURRENT_DATE,
  end_date DATE,
  status TEXT DEFAULT 'active' CHECK (status IN ('active', 'ended', 'pending')),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS maintenance_requests (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  room_id UUID REFERENCES rooms(id) ON DELETE CASCADE,
  employee_id UUID REFERENCES employees(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  description TEXT,
  priority TEXT DEFAULT 'medium' CHECK (priority IN ('low', 'medium', 'high', 'urgent')),
  status TEXT DEFAULT 'pending' CHECK (status IN ('pending', 'in_progress', 'completed', 'cancelled')),
  requested_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  completed_date TIMESTAMP WITH TIME ZONE
);

-- Insert mock rooms with standardized pricing (all available since no assignments)
INSERT INTO rooms (room_number, room_type, block, floor, capacity, monthly_cost, gender_restriction, status) VALUES
-- Basic rooms (800 SAR)
('A101', 'basic', 'A', 1, 2, 800.00, 'male', 'available'),
('A102', 'basic', 'A', 1, 2, 800.00, 'male', 'available'),
('A103', 'basic', 'A', 1, 2, 800.00, 'female', 'available'),
('A201', 'basic', 'A', 2, 2, 800.00, 'female', 'available'),
-- Standard rooms (1200 SAR)
('B101', 'standard', 'B', 1, 1, 1200.00, 'male', 'available'),
('B102', 'standard', 'B', 1, 1, 1200.00, 'male', 'maintenance'),
('B201', 'standard', 'B', 2, 1, 1200.00, 'female', 'available'),
('B202', 'standard', 'B', 2, 1, 1200.00, 'female', 'available'),
-- Premium rooms (1800 SAR)
('C101', 'premium', 'C', 1, 1, 1800.00, 'mixed', 'available'),
('C102', 'premium', 'C', 1, 1, 1800.00, 'mixed', 'available');

-- Insert mock employees
INSERT INTO employees (employee_id, full_name, name, email, phone, department, position, gender, nationality, status, job_grade, hire_date) VALUES
('EMP001', 'Ahmed Al-Rashid', 'Ahmed Al-Rashid', 'ahmed.rashid@company.com', '+966501234567', 'Engineering', 'Senior Engineer', 'male', 'Saudi Arabia', 'active', '7', '2023-06-15'),
('EMP002', 'Sarah Johnson', 'Sarah Johnson', 'sarah.johnson@company.com', '+966501234568', 'Marketing', 'Marketing Manager', 'female', 'USA', 'active', '8', '2023-03-20'),
('EMP003', 'Mohammed Hassan', 'Mohammed Hassan', 'mohammed.hassan@company.com', '+966501234569', 'IT', 'Software Developer', 'male', 'Egypt', 'active', '6', '2023-09-10'),
('EMP004', 'Lisa Chen', 'Lisa Chen', 'lisa.chen@company.com', '+966501234570', 'Finance', 'Financial Analyst', 'female', 'China', 'active', '6', '2023-07-25'),
('EMP005', 'Omar Abdullah', 'Omar Abdullah', 'omar.abdullah@company.com', '+966501234571', 'Operations', 'Operations Supervisor', 'male', 'Jordan', 'active', '7', '2023-04-12'),
('EMP006', 'Maria Garcia', 'Maria Garcia', 'maria.garcia@company.com', '+966501234572', 'HR', 'HR Specialist', 'female', 'Spain', 'active', '5', '2023-11-08'),
('EMP007', 'Khalid Al-Mansouri', 'Khalid Al-Mansouri', 'khalid.mansouri@company.com', '+966501234573', 'Engineering', 'Project Manager', 'male', 'UAE', 'active', '8', '2023-02-14'),
('EMP008', 'Jennifer Smith', 'Jennifer Smith', 'jennifer.smith@company.com', '+966501234574', 'Quality', 'Quality Inspector', 'female', 'UK', 'active', '5', '2023-08-30');

CREATE TABLE IF NOT EXISTS transfer_requests (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  employee_id UUID REFERENCES employees(id) ON DELETE CASCADE,
  current_room_id UUID REFERENCES rooms(id) ON DELETE CASCADE,
  requested_room_id UUID REFERENCES rooms(id) ON DELETE CASCADE,
  reason TEXT NOT NULL,
  status TEXT DEFAULT 'pending' CHECK (status IN ('pending', 'approved', 'rejected', 'completed')),
  requested_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  processed_date TIMESTAMP WITH TIME ZONE,
  processed_by UUID REFERENCES profiles(id),
  notes TEXT
);

CREATE TABLE IF NOT EXISTS meals (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  meal_type TEXT NOT NULL CHECK (meal_type IN ('breakfast', 'lunch', 'dinner')),
  date DATE NOT NULL,
  menu_items TEXT NOT NULL,
  price DECIMAL(8,2) NOT NULL,
  available_quantity INTEGER NOT NULL,
  remaining_quantity INTEGER NOT NULL,
  location TEXT NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS meal_bookings (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  employee_id UUID REFERENCES employees(id) ON DELETE CASCADE,
  meal_id UUID REFERENCES meals(id) ON DELETE CASCADE,
  quantity INTEGER DEFAULT 1,
  booking_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  status TEXT DEFAULT 'confirmed' CHECK (status IN ('confirmed', 'cancelled', 'consumed'))
);

CREATE TABLE IF NOT EXISTS transportation (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  route_name TEXT NOT NULL,
  departure_location TEXT NOT NULL,
  destination TEXT NOT NULL,
  departure_time TIME NOT NULL,
  arrival_time TIME NOT NULL,
  capacity INTEGER NOT NULL,
  available_seats INTEGER NOT NULL,
  price DECIMAL(8,2) NOT NULL,
  days_of_week TEXT[] NOT NULL,
  status TEXT DEFAULT 'active' CHECK (status IN ('active', 'inactive', 'cancelled'))
);

CREATE TABLE IF NOT EXISTS transportation_bookings (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  employee_id UUID REFERENCES employees(id) ON DELETE CASCADE,
  transportation_id UUID REFERENCES transportation(id) ON DELETE CASCADE,
  booking_date DATE NOT NULL,
  pickup_location TEXT,
  status TEXT DEFAULT 'confirmed' CHECK (status IN ('confirmed', 'cancelled', 'completed')),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS deductions (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  employee_id UUID REFERENCES employees(id) ON DELETE CASCADE,
  deduction_type TEXT NOT NULL CHECK (deduction_type IN ('housing', 'meal', 'transportation', 'other')),
  amount DECIMAL(10,2) NOT NULL,
  description TEXT,
  deduction_date DATE DEFAULT CURRENT_DATE,
  month_year TEXT NOT NULL,
  status TEXT DEFAULT 'pending' CHECK (status IN ('pending', 'processed', 'cancelled')),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Insert mock transfer requests
INSERT INTO transfer_requests (employee_id, current_room_id, requested_room_id, reason, status, requested_date) VALUES
((SELECT id FROM employees WHERE employee_id = 'EMP003'), 
(SELECT id FROM rooms WHERE room_number = 'A101'), 
(SELECT id FROM rooms WHERE room_number = 'A202'), 
'Prefer single occupancy room', 'pending', NOW() - INTERVAL '3 days'),
((SELECT id FROM employees WHERE employee_id = 'EMP004'), 
(SELECT id FROM rooms WHERE room_number = 'A201'), 
(SELECT id FROM rooms WHERE room_number = 'C102'), 
'Closer to workplace', 'approved', NOW() - INTERVAL '5 days'),
((SELECT id FROM employees WHERE employee_id = 'EMP006'), 
(SELECT id FROM rooms WHERE room_number = 'C101'), 
(SELECT id FROM rooms WHERE room_number = 'B201'), 
'Roommate compatibility issues', 'rejected', NOW() - INTERVAL '7 days');

-- Insert mock meals
INSERT INTO meals (meal_type, meal_name, category, available_date, date, menu_items, price, available_quantity, remaining_quantity, location) VALUES
('breakfast', 'Morning Breakfast', 'main', CURRENT_DATE, CURRENT_DATE, 'Scrambled eggs, toast, orange juice, coffee', 15.00, 100, 45, 'Main Cafeteria'),
('lunch', 'Lunch Special', 'main', CURRENT_DATE, CURRENT_DATE, 'Grilled chicken, rice, vegetables, salad', 25.00, 150, 78, 'Main Cafeteria'),
('dinner', 'Evening Dinner', 'main', CURRENT_DATE, CURRENT_DATE, 'Fish fillet, pasta, bread, dessert', 30.00, 120, 92, 'Main Cafeteria'),
('breakfast', 'Morning Breakfast', 'main', CURRENT_DATE + 1, CURRENT_DATE + 1, 'Pancakes, fruit, milk, tea', 18.00, 100, 100, 'Main Cafeteria'),
('lunch', 'Lunch Special', 'main', CURRENT_DATE + 1, CURRENT_DATE + 1, 'Beef stew, mashed potatoes, green beans', 28.00, 150, 150, 'Main Cafeteria'),
('dinner', 'Evening Dinner', 'main', CURRENT_DATE + 1, CURRENT_DATE + 1, 'Grilled salmon, quinoa, steamed broccoli', 35.00, 120, 120, 'Main Cafeteria');

-- Insert mock meal bookings
INSERT INTO meal_bookings (employee_id, meal_id, quantity, status) VALUES
((SELECT id FROM employees WHERE employee_id = 'EMP001'), 
(SELECT id FROM meals WHERE meal_type = 'breakfast' AND date = CURRENT_DATE), 1, 'consumed'),
((SELECT id FROM employees WHERE employee_id = 'EMP002'), 
(SELECT id FROM meals WHERE meal_type = 'lunch' AND date = CURRENT_DATE), 1, 'confirmed'),
((SELECT id FROM employees WHERE employee_id = 'EMP003'), 
(SELECT id FROM meals WHERE meal_type = 'dinner' AND date = CURRENT_DATE), 1, 'confirmed'),
((SELECT id FROM employees WHERE employee_id = 'EMP004'), 
(SELECT id FROM meals WHERE meal_type = 'breakfast' AND date = CURRENT_DATE + 1), 1, 'confirmed');

-- Insert mock transportation routes
INSERT INTO transportation (route_name, departure_location, destination, departure_time, arrival_time, capacity, available_seats, price, days_of_week, status) VALUES
('City Center Route', 'Company Campus', 'King Fahd Road', '07:30', '08:15', 50, 23, 10.00, ARRAY['monday', 'tuesday', 'wednesday', 'thursday', 'friday'], 'active'),
('Airport Route', 'Company Campus', 'King Khalid Airport', '05:00', '06:30', 30, 15, 25.00, ARRAY['saturday', 'sunday'], 'active'),
('Mall Route', 'Company Campus', 'Riyadh Gallery Mall', '18:00', '18:45', 40, 31, 8.00, ARRAY['thursday', 'friday', 'saturday'], 'active'),
('Hospital Route', 'Company Campus', 'King Faisal Hospital', '16:00', '16:30', 25, 20, 12.00, ARRAY['monday', 'wednesday', 'friday'], 'active');

-- Insert mock transportation bookings
INSERT INTO transportation_bookings (employee_id, transportation_id, booking_date, pickup_location, status) VALUES
((SELECT id FROM employees WHERE employee_id = 'EMP001'), 
(SELECT id FROM transportation WHERE route_name = 'City Center Route'), CURRENT_DATE, 'Block A', 'confirmed'),
((SELECT id FROM employees WHERE employee_id = 'EMP002'), 
(SELECT id FROM transportation WHERE route_name = 'Mall Route'), CURRENT_DATE + 1, 'Block C', 'confirmed'),
((SELECT id FROM employees WHERE employee_id = 'EMP005'), 
(SELECT id FROM transportation WHERE route_name = 'Airport Route'), CURRENT_DATE + 2, 'Block B', 'confirmed'),
((SELECT id FROM employees WHERE employee_id = 'EMP007'), 
(SELECT id FROM transportation WHERE route_name = 'Hospital Route'), CURRENT_DATE, 'Block A', 'completed');

-- Deductions table created but no mock data inserted
-- Deductions will be calculated automatically based on assignments