additional-mock-data.sql

-- Create additional tables
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, -- ['monday', 'tuesday', etc.]
  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, -- Format: 'YYYY-MM'
  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, date, menu_items, price, available_quantity, remaining_quantity, location) VALUES
('breakfast', CURRENT_DATE, 'Scrambled eggs, toast, orange juice, coffee', 15.00, 100, 45, 'Main Cafeteria'),
('lunch', CURRENT_DATE, 'Grilled chicken, rice, vegetables, salad', 25.00, 150, 78, 'Main Cafeteria'),
('dinner', CURRENT_DATE, 'Fish fillet, pasta, bread, dessert', 30.00, 120, 92, 'Main Cafeteria'),
('breakfast', CURRENT_DATE + 1, 'Pancakes, fruit, milk, tea', 18.00, 100, 100, 'Main Cafeteria'),
('lunch', CURRENT_DATE + 1, 'Beef stew, mashed potatoes, green beans', 28.00, 150, 150, 'Main Cafeteria'),
('dinner', 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