clean-mock-data.sql

-- Clean mock data setup - rooms and employees only
-- Drop NOT NULL constraint temporarily to allow cleanup
ALTER TABLE transfer_requests ALTER COLUMN current_room DROP NOT NULL;

-- Clear ALL existing data first
DELETE FROM transfer_requests;
DELETE FROM maintenance_requests;
DELETE FROM assignments;
DELETE FROM rooms WHERE room_number LIKE 'A%' OR room_number LIKE 'B%' OR room_number LIKE 'C%';
DELETE FROM employees WHERE employee_id LIKE 'EMP%';

-- Update existing tables
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
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();

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';

-- Update constraints
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 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 null values
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;

-- Insert clean mock data
INSERT INTO rooms (room_number, room_type, block, floor, capacity, monthly_cost, gender_restriction, status) VALUES
('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'),
('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'),
('C101', 'premium', 'C', 1, 1, 1800.00, 'mixed', 'available'),
('C102', 'premium', 'C', 1, 1, 1800.00, 'mixed', 'available');

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');