CREATE TABLE companies ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(180) NOT NULL, legal_name VARCHAR(220) NULL, code VARCHAR(60) NOT NULL UNIQUE, country VARCHAR(100) DEFAULT 'Pakistan', timezone VARCHAR(80) DEFAULT 'Asia/Karachi', currency CHAR(3) DEFAULT 'PKR', status ENUM('active','suspended') DEFAULT 'active', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NULL ) ENGINE=InnoDB; CREATE TABLE branches ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, name VARCHAR(150) NOT NULL, code VARCHAR(60) NOT NULL, address TEXT NULL, phone VARCHAR(60) NULL, status ENUM('active','inactive') DEFAULT 'active', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_branch_code(company_id,code), FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE CASCADE ) ENGINE=InnoDB; CREATE TABLE roles ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NULL, name VARCHAR(100) NOT NULL, is_system TINYINT(1) DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_role(company_id,name), FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE CASCADE ) ENGINE=InnoDB; CREATE TABLE permissions ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, code VARCHAR(120) NOT NULL UNIQUE, description VARCHAR(255) NULL ) ENGINE=InnoDB; CREATE TABLE role_permissions ( role_id BIGINT UNSIGNED NOT NULL, permission_id BIGINT UNSIGNED NOT NULL, PRIMARY KEY(role_id,permission_id), FOREIGN KEY(role_id) REFERENCES roles(id) ON DELETE CASCADE, FOREIGN KEY(permission_id) REFERENCES permissions(id) ON DELETE CASCADE ) ENGINE=InnoDB; CREATE TABLE users ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, branch_id BIGINT UNSIGNED NULL, role_id BIGINT UNSIGNED NULL, name VARCHAR(180) NOT NULL, email VARCHAR(190) NOT NULL, password_hash VARCHAR(255) NOT NULL, phone VARCHAR(60) NULL, status ENUM('active','inactive','locked') DEFAULT 'active', is_super_admin TINYINT(1) DEFAULT 0, last_login_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_user_email(company_id,email), FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE CASCADE, FOREIGN KEY(branch_id) REFERENCES branches(id) ON DELETE SET NULL, FOREIGN KEY(role_id) REFERENCES roles(id) ON DELETE SET NULL ) ENGINE=InnoDB; CREATE TABLE clients ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, code VARCHAR(60) NOT NULL, name VARCHAR(180) NOT NULL, type VARCHAR(80) NULL, email VARCHAR(190) NULL, phone VARCHAR(60) NULL, address TEXT NULL, status ENUM('active','inactive') DEFAULT 'active', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_client_code(company_id,code), FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE CASCADE ) ENGINE=InnoDB; CREATE TABLE client_contacts ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, client_id BIGINT UNSIGNED NOT NULL, name VARCHAR(180) NOT NULL, email VARCHAR(190) NULL, phone VARCHAR(60) NULL, designation VARCHAR(120) NULL, is_primary TINYINT(1) DEFAULT 0, FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE CASCADE, FOREIGN KEY(client_id) REFERENCES clients(id) ON DELETE CASCADE ) ENGINE=InnoDB; CREATE TABLE contracts ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, client_id BIGINT UNSIGNED NOT NULL, contract_no VARCHAR(80) NOT NULL, title VARCHAR(180) NOT NULL, start_date DATE NOT NULL, end_date DATE NULL, billing_cycle ENUM('monthly','weekly','daily','custom') DEFAULT 'monthly', status ENUM('draft','active','expired','terminated') DEFAULT 'draft', notes TEXT NULL, FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE CASCADE, FOREIGN KEY(client_id) REFERENCES clients(id) ON DELETE RESTRICT, UNIQUE KEY uq_contract(company_id,contract_no) ) ENGINE=InnoDB; CREATE TABLE sites ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, client_id BIGINT UNSIGNED NULL, contract_id BIGINT UNSIGNED NULL, code VARCHAR(60) NOT NULL, name VARCHAR(180) NOT NULL, address TEXT NULL, latitude DECIMAL(10,7) NULL, longitude DECIMAL(10,7) NULL, status ENUM('active','inactive') DEFAULT 'active', FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE CASCADE, FOREIGN KEY(client_id) REFERENCES clients(id) ON DELETE SET NULL, FOREIGN KEY(contract_id) REFERENCES contracts(id) ON DELETE SET NULL, UNIQUE KEY uq_site(company_id,code) ) ENGINE=InnoDB; CREATE TABLE posts ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, site_id BIGINT UNSIGNED NOT NULL, name VARCHAR(150) NOT NULL, post_type VARCHAR(100) NULL, required_headcount INT DEFAULT 1, active TINYINT(1) DEFAULT 1, FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE CASCADE, FOREIGN KEY(site_id) REFERENCES sites(id) ON DELETE CASCADE ) ENGINE=InnoDB; CREATE TABLE employees ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, branch_id BIGINT UNSIGNED NULL, employee_no VARCHAR(80) NOT NULL, name VARCHAR(180) NOT NULL, phone VARCHAR(60) NULL, email VARCHAR(190) NULL, designation VARCHAR(120) NULL, join_date DATE NULL, employment_status ENUM('active','inactive','terminated') DEFAULT 'active', dob DATE NULL, address TEXT NULL, FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE CASCADE, UNIQUE KEY uq_employee(company_id,employee_no) ) ENGINE=InnoDB; CREATE TABLE guards ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, employee_id BIGINT UNSIGNED NOT NULL, guard_no VARCHAR(80) NOT NULL, grade VARCHAR(80) NULL, licence_no VARCHAR(120) NULL, licence_expiry DATE NULL, screening_status ENUM('pending','passed','failed','expired') DEFAULT 'pending', FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE CASCADE, FOREIGN KEY(employee_id) REFERENCES employees(id) ON DELETE CASCADE, UNIQUE KEY uq_guard(company_id,guard_no) ) ENGINE=InnoDB; CREATE TABLE deployments ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, guard_id BIGINT UNSIGNED NOT NULL, site_id BIGINT UNSIGNED NOT NULL, post_id BIGINT UNSIGNED NULL, start_date DATE NOT NULL, end_date DATE NULL, status ENUM('planned','active','ended') DEFAULT 'planned', FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE CASCADE, FOREIGN KEY(guard_id) REFERENCES guards(id) ON DELETE CASCADE, FOREIGN KEY(site_id) REFERENCES sites(id) ON DELETE CASCADE, FOREIGN KEY(post_id) REFERENCES posts(id) ON DELETE SET NULL ) ENGINE=InnoDB; CREATE TABLE shifts ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, site_id BIGINT UNSIGNED NOT NULL, post_id BIGINT UNSIGNED NULL, shift_date DATE NOT NULL, start_time TIME NOT NULL, end_time TIME NOT NULL, required_guards INT DEFAULT 1, status ENUM('draft','published','completed','cancelled') DEFAULT 'draft', FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE CASCADE, FOREIGN KEY(site_id) REFERENCES sites(id) ON DELETE CASCADE, FOREIGN KEY(post_id) REFERENCES posts(id) ON DELETE SET NULL ) ENGINE=InnoDB; CREATE TABLE shift_assignments ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, shift_id BIGINT UNSIGNED NOT NULL, guard_id BIGINT UNSIGNED NOT NULL, assignment_status ENUM('assigned','accepted','rejected','replacement','completed') DEFAULT 'assigned', assigned_at DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE CASCADE, FOREIGN KEY(shift_id) REFERENCES shifts(id) ON DELETE CASCADE, FOREIGN KEY(guard_id) REFERENCES guards(id) ON DELETE CASCADE, UNIQUE KEY uq_shift_guard(shift_id,guard_id) ) ENGINE=InnoDB; CREATE TABLE attendance ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, shift_id BIGINT UNSIGNED NULL, guard_id BIGINT UNSIGNED NOT NULL, clock_in DATETIME NULL, clock_out DATETIME NULL, clock_in_lat DECIMAL(10,7) NULL, clock_in_lng DECIMAL(10,7) NULL, clock_out_lat DECIMAL(10,7) NULL, clock_out_lng DECIMAL(10,7) NULL, status ENUM('present','late','absent','left_early','manual') DEFAULT 'present', verification_method ENUM('gps','qr','nfc','manual','device') DEFAULT 'gps', notes TEXT NULL, FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE CASCADE, FOREIGN KEY(shift_id) REFERENCES shifts(id) ON DELETE SET NULL, FOREIGN KEY(guard_id) REFERENCES guards(id) ON DELETE CASCADE ) ENGINE=InnoDB; CREATE TABLE guard_devices ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, guard_id BIGINT UNSIGNED NOT NULL, device_uuid VARCHAR(190) NOT NULL, token_hash CHAR(64) NOT NULL, platform ENUM('android','ios','web') NOT NULL, app_version VARCHAR(40) NULL, status ENUM('active','revoked') DEFAULT 'active', last_seen_at DATETIME NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE CASCADE, FOREIGN KEY(guard_id) REFERENCES guards(id) ON DELETE CASCADE, UNIQUE KEY uq_device_uuid(device_uuid), UNIQUE KEY uq_device_token(token_hash) ) ENGINE=InnoDB; CREATE TABLE tracking_sessions ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, guard_id BIGINT UNSIGNED NOT NULL, device_id BIGINT UNSIGNED NOT NULL, shift_id BIGINT UNSIGNED NULL, started_at DATETIME NOT NULL, ended_at DATETIME NULL, status ENUM('active','ended','forced_end') DEFAULT 'active', FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE CASCADE, FOREIGN KEY(guard_id) REFERENCES guards(id) ON DELETE CASCADE, FOREIGN KEY(device_id) REFERENCES guard_devices(id) ON DELETE CASCADE, FOREIGN KEY(shift_id) REFERENCES shifts(id) ON DELETE SET NULL ) ENGINE=InnoDB; CREATE TABLE guard_locations ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, guard_id BIGINT UNSIGNED NOT NULL, device_id BIGINT UNSIGNED NULL, tracking_session_id BIGINT UNSIGNED NULL, latitude DECIMAL(10,7) NOT NULL, longitude DECIMAL(10,7) NOT NULL, accuracy DECIMAL(8,2) NULL, recorded_at DATETIME NOT NULL, source ENUM('gps','network','manual') DEFAULT 'gps', FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE CASCADE, FOREIGN KEY(guard_id) REFERENCES guards(id) ON DELETE CASCADE, INDEX idx_guard_time(company_id,guard_id,recorded_at), INDEX idx_company_time(company_id,recorded_at) ) ENGINE=InnoDB; CREATE TABLE geofences ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, site_id BIGINT UNSIGNED NOT NULL, name VARCHAR(150) NOT NULL, latitude DECIMAL(10,7) NOT NULL, longitude DECIMAL(10,7) NOT NULL, radius_m INT NOT NULL DEFAULT 100, active TINYINT(1) DEFAULT 1, FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE CASCADE, FOREIGN KEY(site_id) REFERENCES sites(id) ON DELETE CASCADE ) ENGINE=InnoDB; CREATE TABLE patrol_routes ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, site_id BIGINT UNSIGNED NOT NULL, name VARCHAR(180) NOT NULL, frequency_minutes INT NULL, active TINYINT(1) DEFAULT 1, FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE CASCADE, FOREIGN KEY(site_id) REFERENCES sites(id) ON DELETE CASCADE ) ENGINE=InnoDB; CREATE TABLE patrol_checkpoints ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, route_id BIGINT UNSIGNED NOT NULL, code VARCHAR(120) NOT NULL, name VARCHAR(180) NOT NULL, checkpoint_type ENUM('qr','nfc','gps','manual') DEFAULT 'qr', latitude DECIMAL(10,7) NULL, longitude DECIMAL(10,7) NULL, sequence_no INT DEFAULT 1, FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE CASCADE, FOREIGN KEY(route_id) REFERENCES patrol_routes(id) ON DELETE CASCADE, UNIQUE KEY uq_checkpoint(company_id,code) ) ENGINE=InnoDB; CREATE TABLE patrol_runs ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, route_id BIGINT UNSIGNED NOT NULL, guard_id BIGINT UNSIGNED NOT NULL, shift_id BIGINT UNSIGNED NULL, started_at DATETIME NOT NULL, completed_at DATETIME NULL, status ENUM('planned','active','completed','missed','cancelled') DEFAULT 'planned', FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE CASCADE, FOREIGN KEY(route_id) REFERENCES patrol_routes(id) ON DELETE CASCADE, FOREIGN KEY(guard_id) REFERENCES guards(id) ON DELETE CASCADE, FOREIGN KEY(shift_id) REFERENCES shifts(id) ON DELETE SET NULL ) ENGINE=InnoDB; CREATE TABLE patrol_scans ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, run_id BIGINT UNSIGNED NOT NULL, checkpoint_id BIGINT UNSIGNED NOT NULL, guard_id BIGINT UNSIGNED NOT NULL, scanned_at DATETIME NOT NULL, latitude DECIMAL(10,7) NULL, longitude DECIMAL(10,7) NULL, method ENUM('qr','nfc','gps','manual') NOT NULL, offline_event_id VARCHAR(100) NULL, notes TEXT NULL, FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE CASCADE, FOREIGN KEY(run_id) REFERENCES patrol_runs(id) ON DELETE CASCADE, FOREIGN KEY(checkpoint_id) REFERENCES patrol_checkpoints(id) ON DELETE CASCADE, FOREIGN KEY(guard_id) REFERENCES guards(id) ON DELETE CASCADE, UNIQUE KEY uq_offline_event(company_id,offline_event_id) ) ENGINE=InnoDB; CREATE TABLE check_calls ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, guard_id BIGINT UNSIGNED NOT NULL, shift_id BIGINT UNSIGNED NULL, due_at DATETIME NOT NULL, completed_at DATETIME NULL, status ENUM('pending','completed','missed','escalated') DEFAULT 'pending', response_note TEXT NULL, FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE CASCADE, FOREIGN KEY(guard_id) REFERENCES guards(id) ON DELETE CASCADE ) ENGINE=InnoDB; CREATE TABLE sos_events ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, guard_id BIGINT UNSIGNED NOT NULL, device_id BIGINT UNSIGNED NULL, triggered_at DATETIME NOT NULL, latitude DECIMAL(10,7) NULL, longitude DECIMAL(10,7) NULL, status ENUM('open','acknowledged','dispatched','resolved','false_alarm') DEFAULT 'open', notes TEXT NULL, FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE CASCADE, FOREIGN KEY(guard_id) REFERENCES guards(id) ON DELETE CASCADE ) ENGINE=InnoDB; CREATE TABLE incidents ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, incident_no VARCHAR(80) NOT NULL, site_id BIGINT UNSIGNED NULL, guard_id BIGINT UNSIGNED NULL, occurred_at DATETIME NOT NULL, category VARCHAR(100) NULL, severity ENUM('low','medium','high','critical') DEFAULT 'medium', title VARCHAR(180) NOT NULL, description TEXT NOT NULL, action_taken TEXT NULL, latitude DECIMAL(10,7) NULL, longitude DECIMAL(10,7) NULL, status ENUM('open','investigating','resolved','closed') DEFAULT 'open', client_visible TINYINT(1) DEFAULT 0, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE CASCADE, FOREIGN KEY(site_id) REFERENCES sites(id) ON DELETE SET NULL, FOREIGN KEY(guard_id) REFERENCES guards(id) ON DELETE SET NULL, UNIQUE KEY uq_incident(company_id,incident_no) ) ENGINE=InnoDB; CREATE TABLE alerts ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, type VARCHAR(80) NOT NULL, severity ENUM('info','warning','critical') DEFAULT 'warning', title VARCHAR(180) NOT NULL, message TEXT NULL, entity_type VARCHAR(80) NULL, entity_id BIGINT UNSIGNED NULL, status ENUM('open','acknowledged','resolved') DEFAULT 'open', created_at DATETIME DEFAULT CURRENT_TIMESTAMP, resolved_at DATETIME NULL, FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE CASCADE ) ENGINE=InnoDB; CREATE TABLE compliance_items ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, employee_id BIGINT UNSIGNED NOT NULL, item_type VARCHAR(100) NOT NULL, reference_no VARCHAR(120) NULL, issue_date DATE NULL, expiry_date DATE NULL, status ENUM('pending','valid','expired','rejected') DEFAULT 'pending', notes TEXT NULL, FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE CASCADE, FOREIGN KEY(employee_id) REFERENCES employees(id) ON DELETE CASCADE, INDEX idx_compliance_expiry(company_id,expiry_date) ) ENGINE=InnoDB; CREATE TABLE training_records ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, employee_id BIGINT UNSIGNED NOT NULL, course VARCHAR(180) NOT NULL, completed_date DATE NULL, expiry_date DATE NULL, status ENUM('planned','completed','expired') DEFAULT 'planned', score DECIMAL(5,2) NULL, FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE CASCADE, FOREIGN KEY(employee_id) REFERENCES employees(id) ON DELETE CASCADE ) ENGINE=InnoDB; CREATE TABLE leave_requests ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, employee_id BIGINT UNSIGNED NOT NULL, leave_type VARCHAR(80) NOT NULL, start_date DATE NOT NULL, end_date DATE NOT NULL, reason TEXT NULL, status ENUM('pending','approved','rejected','cancelled') DEFAULT 'pending', approved_by BIGINT UNSIGNED NULL, FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE CASCADE, FOREIGN KEY(employee_id) REFERENCES employees(id) ON DELETE CASCADE ) ENGINE=InnoDB; CREATE TABLE timesheets ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, guard_id BIGINT UNSIGNED NOT NULL, period_start DATE NOT NULL, period_end DATE NOT NULL, regular_hours DECIMAL(10,2) DEFAULT 0, overtime_hours DECIMAL(10,2) DEFAULT 0, verified TINYINT(1) DEFAULT 0, verified_by BIGINT UNSIGNED NULL, FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE CASCADE, FOREIGN KEY(guard_id) REFERENCES guards(id) ON DELETE CASCADE ) ENGINE=InnoDB; CREATE TABLE payroll_periods ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, name VARCHAR(120) NOT NULL, period_start DATE NOT NULL, period_end DATE NOT NULL, status ENUM('draft','processing','approved','paid','closed') DEFAULT 'draft', FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE CASCADE ) ENGINE=InnoDB; CREATE TABLE payroll_items ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, payroll_period_id BIGINT UNSIGNED NOT NULL, employee_id BIGINT UNSIGNED NOT NULL, basic_salary DECIMAL(14,2) DEFAULT 0, overtime_amount DECIMAL(14,2) DEFAULT 0, allowances DECIMAL(14,2) DEFAULT 0, deductions DECIMAL(14,2) DEFAULT 0, net_pay DECIMAL(14,2) DEFAULT 0, FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE CASCADE, FOREIGN KEY(payroll_period_id) REFERENCES payroll_periods(id) ON DELETE CASCADE, FOREIGN KEY(employee_id) REFERENCES employees(id) ON DELETE CASCADE ) ENGINE=InnoDB; CREATE TABLE invoices ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, client_id BIGINT UNSIGNED NOT NULL, contract_id BIGINT UNSIGNED NULL, invoice_no VARCHAR(80) NOT NULL, invoice_date DATE NOT NULL, due_date DATE NULL, subtotal DECIMAL(14,2) DEFAULT 0, tax DECIMAL(14,2) DEFAULT 0, total DECIMAL(14,2) DEFAULT 0, status ENUM('draft','issued','partial','paid','void') DEFAULT 'draft', FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE CASCADE, FOREIGN KEY(client_id) REFERENCES clients(id) ON DELETE RESTRICT, FOREIGN KEY(contract_id) REFERENCES contracts(id) ON DELETE SET NULL, UNIQUE KEY uq_invoice(company_id,invoice_no) ) ENGINE=InnoDB; CREATE TABLE payments ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, client_id BIGINT UNSIGNED NULL, invoice_id BIGINT UNSIGNED NULL, amount DECIMAL(14,2) NOT NULL, payment_date DATE NOT NULL, method VARCHAR(60) NULL, reference_no VARCHAR(120) NULL, FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE CASCADE, FOREIGN KEY(client_id) REFERENCES clients(id) ON DELETE SET NULL, FOREIGN KEY(invoice_id) REFERENCES invoices(id) ON DELETE SET NULL ) ENGINE=InnoDB; CREATE TABLE assets ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, asset_code VARCHAR(80) NOT NULL, name VARCHAR(180) NOT NULL, category VARCHAR(100) NULL, status ENUM('available','assigned','maintenance','retired','lost') DEFAULT 'available', assigned_employee_id BIGINT UNSIGNED NULL, FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE CASCADE, FOREIGN KEY(assigned_employee_id) REFERENCES employees(id) ON DELETE SET NULL, UNIQUE KEY uq_asset(company_id,asset_code) ) ENGINE=InnoDB; CREATE TABLE vehicles ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, registration_no VARCHAR(60) NOT NULL, make VARCHAR(80) NULL, model VARCHAR(80) NULL, year SMALLINT NULL, status ENUM('active','maintenance','retired') DEFAULT 'active', FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE CASCADE, UNIQUE KEY uq_vehicle(company_id,registration_no) ) ENGINE=InnoDB; CREATE TABLE vehicle_maintenance ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, vehicle_id BIGINT UNSIGNED NOT NULL, service_date DATE NOT NULL, service_type VARCHAR(120) NULL, cost DECIMAL(14,2) DEFAULT 0, odometer INT NULL, notes TEXT NULL, FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE CASCADE, FOREIGN KEY(vehicle_id) REFERENCES vehicles(id) ON DELETE CASCADE ) ENGINE=InnoDB; CREATE TABLE client_requests ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, client_id BIGINT UNSIGNED NOT NULL, site_id BIGINT UNSIGNED NULL, request_type VARCHAR(100) NOT NULL, requested_for DATETIME NULL, headcount INT DEFAULT 1, details TEXT NULL, status ENUM('new','reviewing','approved','scheduled','rejected','completed') DEFAULT 'new', FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE CASCADE, FOREIGN KEY(client_id) REFERENCES clients(id) ON DELETE CASCADE, FOREIGN KEY(site_id) REFERENCES sites(id) ON DELETE SET NULL ) ENGINE=InnoDB; CREATE TABLE messages ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, sender_user_id BIGINT UNSIGNED NULL, recipient_user_id BIGINT UNSIGNED NULL, subject VARCHAR(180) NULL, body TEXT NOT NULL, sent_at DATETIME DEFAULT CURRENT_TIMESTAMP, read_at DATETIME NULL, FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE CASCADE ) ENGINE=InnoDB; CREATE TABLE notifications ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, user_id BIGINT UNSIGNED NOT NULL, type VARCHAR(80) NOT NULL, title VARCHAR(180) NOT NULL, message TEXT NULL, read_at DATETIME NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE CASCADE, FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE ) ENGINE=InnoDB; CREATE TABLE audit_logs ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, user_id BIGINT UNSIGNED NULL, action VARCHAR(100) NOT NULL, entity_type VARCHAR(100) NOT NULL, entity_id BIGINT UNSIGNED NULL, ip_address VARCHAR(45) NULL, user_agent VARCHAR(500) NULL, metadata JSON NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE CASCADE, INDEX idx_audit(company_id,created_at) ) ENGINE=InnoDB; CREATE TABLE workflow_approvals ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, entity_type VARCHAR(100) NOT NULL, entity_id BIGINT UNSIGNED NOT NULL, step_name VARCHAR(120) NOT NULL, requested_by BIGINT UNSIGNED NULL, approved_by BIGINT UNSIGNED NULL, status ENUM('pending','approved','rejected') DEFAULT 'pending', requested_at DATETIME DEFAULT CURRENT_TIMESTAMP, decided_at DATETIME NULL, FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE CASCADE ) ENGINE=InnoDB; INSERT INTO permissions(code,description) VALUES ('dashboard.view','View dashboard'),('clients.view','View clients'),('clients.manage','Manage clients'), ('sites.view','View sites'),('sites.manage','Manage sites'),('contracts.view','View contracts'),('contracts.manage','Manage contracts'),('guards.view','View guards'), ('guards.manage','Manage guards'),('operations.view','View operations'),('operations.manage','Manage operations'), ('tracking.view','View live tracking'),('tracking.write','Submit tracking'),('attendance.view','View attendance'),('attendance.manage','Manage attendance'),('incidents.view','View incidents'), ('incidents.manage','Manage incidents'),('payroll.view','View payroll'),('billing.view','View billing'), ('admin.users','Manage users'),('admin.roles','Manage roles'); INSERT INTO roles(name,is_system) VALUES ('Super Admin',1),('Operations Manager',1),('HR Manager',1),('Finance Manager',1), ('Control Room Operator',1),('Client User',1),('Security Guard',1); -- Super Admin role receives every current permission. INSERT INTO role_permissions(role_id,permission_id) SELECT 1,id FROM permissions; -- Phase 3 Operations Engine ALTER TABLE shifts ADD COLUMN notes TEXT NULL AFTER status; CREATE TABLE guard_availability ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, guard_id BIGINT UNSIGNED NOT NULL, available_date DATE NOT NULL, available_from TIME NULL, available_to TIME NULL, status ENUM('available','unavailable','leave','restricted') DEFAULT 'available', notes VARCHAR(500) NULL, FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE CASCADE, FOREIGN KEY(guard_id) REFERENCES guards(id) ON DELETE CASCADE, UNIQUE KEY uq_guard_availability(company_id,guard_id,available_date) ) ENGINE=InnoDB; CREATE TABLE shift_replacements ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, shift_id BIGINT UNSIGNED NOT NULL, old_guard_id BIGINT UNSIGNED NULL, new_guard_id BIGINT UNSIGNED NOT NULL, reason VARCHAR(255) NOT NULL, status ENUM('requested','approved','completed','cancelled') DEFAULT 'requested', requested_by BIGINT UNSIGNED NULL, approved_by BIGINT UNSIGNED NULL, requested_at DATETIME DEFAULT CURRENT_TIMESTAMP, approved_at DATETIME NULL, FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE CASCADE, FOREIGN KEY(shift_id) REFERENCES shifts(id) ON DELETE CASCADE, FOREIGN KEY(old_guard_id) REFERENCES guards(id) ON DELETE SET NULL, FOREIGN KEY(new_guard_id) REFERENCES guards(id) ON DELETE CASCADE, FOREIGN KEY(requested_by) REFERENCES users(id) ON DELETE SET NULL, FOREIGN KEY(approved_by) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB; CREATE TABLE roster_events ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, shift_id BIGINT UNSIGNED NULL, guard_id BIGINT UNSIGNED NULL, event_type VARCHAR(80) NOT NULL, details JSON NULL, created_by BIGINT UNSIGNED NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE CASCADE, FOREIGN KEY(shift_id) REFERENCES shifts(id) ON DELETE SET NULL, FOREIGN KEY(guard_id) REFERENCES guards(id) ON DELETE SET NULL, FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_roster_event(company_id,created_at) ) ENGINE=InnoDB; -- Phase 5 Guard App / Field Operations CREATE TABLE IF NOT EXISTS mobile_devices ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, branch_id BIGINT UNSIGNED NULL, user_id BIGINT UNSIGNED NULL, guard_id BIGINT UNSIGNED NULL, device_uuid VARCHAR(191) NOT NULL, platform ENUM('android','ios','web') NOT NULL, app_version VARCHAR(40) NULL, device_name VARCHAR(120) NULL, last_seen_at DATETIME NULL, status ENUM('pending','active','revoked','lost') NOT NULL DEFAULT 'pending', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_mobile_device(company_id,device_uuid), INDEX idx_mobile_guard(company_id,guard_id,status) ); CREATE TABLE IF NOT EXISTS mobile_sync_events ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, device_id BIGINT UNSIGNED NOT NULL, event_uuid CHAR(36) NOT NULL, event_type VARCHAR(60) NOT NULL, occurred_at DATETIME NOT NULL, received_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, payload_json LONGTEXT NULL, status ENUM('accepted','duplicate','rejected','failed') NOT NULL DEFAULT 'accepted', error_message VARCHAR(500) NULL, UNIQUE KEY uq_mobile_event(company_id,event_uuid) ); CREATE TABLE IF NOT EXISTS guard_tasks ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, branch_id BIGINT UNSIGNED NULL, guard_id BIGINT UNSIGNED NOT NULL, site_id BIGINT UNSIGNED NULL, shift_id BIGINT UNSIGNED NULL, title VARCHAR(180) NOT NULL, description TEXT NULL, due_at DATETIME NULL, priority ENUM('low','normal','high','urgent') NOT NULL DEFAULT 'normal', status ENUM('assigned','accepted','in_progress','completed','failed','cancelled') NOT NULL DEFAULT 'assigned', completed_at DATETIME NULL, completion_notes VARCHAR(1000) NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, INDEX idx_guard_tasks(company_id,guard_id,status,due_at) ); CREATE TABLE IF NOT EXISTS field_incident_media ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, incident_id BIGINT UNSIGNED NOT NULL, guard_id BIGINT UNSIGNED NULL, file_path VARCHAR(500) NOT NULL, media_type ENUM('image','video','audio','document') NOT NULL DEFAULT 'image', sha256 CHAR(64) NULL, captured_at DATETIME NULL, latitude DECIMAL(10,7) NULL, longitude DECIMAL(10,7) NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, INDEX idx_incident_media(incident_id) );