-- Enhanced Sample Data for Security Patrol System
-- Version 2.0 with Client Portal Data

USE security_patrol;

-- Sample Clients
INSERT INTO clients (
    company_name, contact_person, email, phone, address, city, state, zip_code,
    billing_address, tax_id, hourly_rate, monthly_rate, contract_start, contract_end,
    notification_email, notification_sms, login_username, login_password
) VALUES
('Downtown Mall Security', 'John Richardson', 'john@downtownmall.com', '555-0101', 
 '123 Main St', 'Downtown', 'NY', '10001', '123 Main St', 'TAX001', 28.50, 0.00,
 '2024-01-01', '2024-12-31', 'security@downtownmall.com', '555-0101', 
 'downtownmall', '$2y$10$example_hashed_password'),

('Industrial Park Corp', 'Sarah Martinez', 'sarah@industrialpark.com', '555-0102',
 '456 Industrial Blvd', 'Industrial City', 'TX', '75001', '456 Industrial Blvd', 'TAX002', 32.00, 5000.00,
 '2024-01-15', '2025-01-15', 'alerts@industrialpark.com', '555-0102',
 'industrialpark', '$2y$10$example_hashed_password'),

('Retail Chain Services', 'Mike Thompson', 'mike@retailchain.com', '555-0103',
 '789 Commerce Ave', 'Metro City', 'CA', '90210', '789 Commerce Ave', 'TAX003', 26.00, 0.00,
 '2024-02-01', '2025-02-01', 'security@retailchain.com', '555-0103',
 'retailchain', '$2y$10$example_hashed_password'),

('Office Complex Management', 'Lisa Wang', 'lisa@officecomplex.com', '555-0104',
 '321 Business Park Dr', 'Corporate Hills', 'FL', '33101', '321 Business Park Dr', 'TAX004', 24.00, 3500.00,
 '2024-03-01', '2025-03-01', 'management@officecomplex.com', '555-0104',
 'officecomplex', '$2y$10$example_hashed_password');

-- Sample Client Properties
INSERT INTO client_properties (
    client_id, property_name, property_type, address, city, state, zip_code,
    latitude, longitude, contact_person, contact_phone, patrol_frequency, start_time, end_time
) VALUES
(1, 'Downtown Mall - Main Building', 'retail', '123 Main St', 'Downtown', 'NY', '10001',
 40.7128, -74.0060, 'Security Desk', '555-0101', 'every_2_hours', '20:00:00', '08:00:00'),

(1, 'Downtown Mall - Parking Garage', 'retail', '125 Main St', 'Downtown', 'NY', '10001',
 40.7130, -74.0058, 'Parking Supervisor', '555-0111', 'hourly', '18:00:00', '06:00:00'),

(2, 'Industrial Park - Warehouse A', 'industrial', '456 Industrial Blvd', 'Industrial City', 'TX', '75001',
 32.7767, -96.7970, 'Warehouse Manager', '555-0102', 'every_4_hours', '22:00:00', '06:00:00'),

(2, 'Industrial Park - Office Complex', 'office', '458 Industrial Blvd', 'Industrial City', 'TX', '75001',
 32.7770, -96.7968, 'Office Security', '555-0112', 'twice_daily', '18:00:00', '08:00:00'),

(3, 'Retail Store #1 - Metro Plaza', 'retail', '789 Commerce Ave', 'Metro City', 'CA', '90210',
 34.0522, -118.2437, 'Store Manager', '555-0103', 'hourly', '21:00:00', '07:00:00'),

(3, 'Retail Store #2 - West Side', 'retail', '791 Commerce Ave', 'Metro City', 'CA', '90210',
 34.0525, -118.2440, 'Assistant Manager', '555-0113', 'every_2_hours', '20:00:00', '08:00:00'),

(4, 'Office Complex - Tower A', 'office', '321 Business Park Dr', 'Corporate Hills', 'FL', '33101',
 25.7617, -80.1918, 'Building Security', '555-0104', 'every_2_hours', '19:00:00', '07:00:00');

-- Sample Enhanced Users (Guards with property assignments)
INSERT INTO users (
    name, email, phone, password, role, status, assigned_property_id, employee_id, 
    hire_date, hourly_wage, emergency_contact_name, emergency_contact_phone
) VALUES
-- Admin Users
('System Administrator', 'admin@patrol.com', '555-9999', '$2y$10$example_hashed_password', 'admin', 'active', 
 NULL, 'EMP001', '2024-01-01', 50.00, 'Emergency Contact', '555-9998'),

('Security Manager', 'manager@patrol.com', '555-9998', '$2y$10$example_hashed_password', 'manager', 'active',
 NULL, 'EMP002', '2024-01-01', 45.00, 'Emergency Contact', '555-9997'),

-- Supervisors
('Downtown Supervisor', 'supervisor1@patrol.com', '555-1001', '$2y$10$example_hashed_password', 'supervisor', 'active',
 1, 'EMP003', '2024-01-15', 35.00, 'Jane Doe', '555-2001'),

('Industrial Supervisor', 'supervisor2@patrol.com', '555-1002', '$2y$10$example_hashed_password', 'supervisor', 'active',
 3, 'EMP004', '2024-01-15', 35.00, 'Bob Smith', '555-2002'),

-- Guards assigned to properties
('Guard Alpha', 'guard1@patrol.com', '555-2001', '$2y$10$example_hashed_password', 'guard', 'active',
 1, 'EMP101', '2024-02-01', 28.50, 'Contact Alpha', '555-3001'),

('Guard Beta', 'guard2@patrol.com', '555-2002', '$2y$10$example_hashed_password', 'guard', 'active',
 2, 'EMP102', '2024-02-01', 28.50, 'Contact Beta', '555-3002'),

('Guard Gamma', 'guard3@patrol.com', '555-2003', '$2y$10$example_hashed_password', 'guard', 'active',
 3, 'EMP103', '2024-02-05', 32.00, 'Contact Gamma', '555-3003'),

('Guard Delta', 'guard4@patrol.com', '555-2004', '$2y$10$example_hashed_password', 'guard', 'active',
 4, 'EMP104', '2024-02-05', 32.00, 'Contact Delta', '555-3004'),

('Guard Echo', 'guard5@patrol.com', '555-2005', '$2y$10$example_hashed_password', 'guard', 'active',
 5, 'EMP105', '2024-02-10', 26.00, 'Contact Echo', '555-3005'),

('Guard Foxtrot', 'guard6@patrol.com', '555-2006', '$2y$10$example_hashed_password', 'guard', 'active',
 6, 'EMP106', '2024-02-10', 26.00, 'Contact Foxtrot', '555-3006'),

('Guard Golf', 'guard7@patrol.com', '555-2007', '$2y$10$example_hashed_password', 'guard', 'active',
 7, 'EMP107', '2024-02-15', 24.00, 'Contact Golf', '555-3007');

-- Sample Location History (Recent GPS tracking data)
INSERT INTO location_history (
    user_id, latitude, longitude, accuracy, speed, battery_level, address, timestamp
) VALUES
-- Guard Alpha at Downtown Mall
(5, 40.7128, -74.0060, 5.2, 0.8, 85, '123 Main St, Downtown, NY', DATE_SUB(NOW(), INTERVAL 5 MINUTE)),
(5, 40.7129, -74.0061, 4.8, 1.2, 85, '123 Main St, Downtown, NY', DATE_SUB(NOW(), INTERVAL 10 MINUTE)),
(5, 40.7127, -74.0059, 6.1, 0.5, 84, '123 Main St, Downtown, NY', DATE_SUB(NOW(), INTERVAL 15 MINUTE)),

-- Guard Beta at Parking Garage
(6, 40.7130, -74.0058, 3.2, 2.1, 92, '125 Main St, Downtown, NY', DATE_SUB(NOW(), INTERVAL 3 MINUTE)),
(6, 40.7131, -74.0057, 4.5, 1.8, 92, '125 Main St, Downtown, NY', DATE_SUB(NOW(), INTERVAL 8 MINUTE)),

-- Guard Gamma at Industrial Park
(7, 32.7767, -96.7970, 7.3, 0.0, 76, '456 Industrial Blvd, Industrial City, TX', DATE_SUB(NOW(), INTERVAL 2 MINUTE)),
(7, 32.7768, -96.7971, 6.8, 1.5, 76, '456 Industrial Blvd, Industrial City, TX', DATE_SUB(NOW(), INTERVAL 12 MINUTE)),

-- Guard Delta at Office Complex
(8, 32.7770, -96.7968, 4.2, 0.3, 88, '458 Industrial Blvd, Industrial City, TX', DATE_SUB(NOW(), INTERVAL 7 MINUTE)),

-- Guard Echo at Retail Store
(9, 34.0522, -118.2437, 5.8, 1.0, 68, '789 Commerce Ave, Metro City, CA', DATE_SUB(NOW(), INTERVAL 1 MINUTE)),
(9, 34.0523, -118.2438, 4.9, 1.8, 68, '789 Commerce Ave, Metro City, CA', DATE_SUB(NOW(), INTERVAL 6 MINUTE)),

-- Guard Foxtrot at West Side Store
(10, 34.0525, -118.2440, 6.2, 0.7, 91, '791 Commerce Ave, Metro City, CA', DATE_SUB(NOW(), INTERVAL 4 MINUTE)),

-- Guard Golf at Office Tower
(11, 25.7617, -80.1918, 3.8, 0.9, 79, '321 Business Park Dr, Corporate Hills, FL', DATE_SUB(NOW(), INTERVAL 9 MINUTE));

-- Sample Incidents with client relevance
INSERT INTO incidents (
    user_id, title, description, incident_type, priority, status, latitude, longitude, 
    address, client_notified, created_at
) VALUES
(5, 'Suspicious Activity - Main Entrance', 'Individual loitering near main entrance after hours, approached and identified', 
 'security_breach', 'medium', 'resolved', 40.7128, -74.0060, '123 Main St, Downtown, NY', TRUE, DATE_SUB(NOW(), INTERVAL 2 HOUR)),

(6, 'Parking Garage Light Out', 'Light fixture on Level 2, Section B is not working, safety concern for evening patrol', 
 'maintenance_issue', 'low', 'open', 40.7130, -74.0058, '125 Main St, Downtown, NY', TRUE, DATE_SUB(NOW(), INTERVAL 4 HOUR)),

(7, 'Warehouse Door Alarm', 'Motion sensor triggered in Warehouse A loading dock area, investigated and secured', 
 'security_breach', 'high', 'resolved', 32.7767, -96.7970, '456 Industrial Blvd, Industrial City, TX', TRUE, DATE_SUB(NOW(), INTERVAL 1 DAY)),

(9, 'Slip and Fall Hazard', 'Wet floor near entrance not properly marked, cleaned and signage placed', 
 'safety_concern', 'medium', 'resolved', 34.0522, -118.2437, '789 Commerce Ave, Metro City, CA', TRUE, DATE_SUB(NOW(), INTERVAL 6 HOUR)),

(11, 'Elevator Malfunction', 'Elevator #2 stuck between floors 3-4, maintenance called and resolved', 
 'maintenance_issue', 'high', 'resolved', 25.7617, -80.1918, '321 Business Park Dr, Corporate Hills, FL', TRUE, DATE_SUB(NOW(), INTERVAL 3 HOUR));

-- Sample Geofences
INSERT INTO geofences (
    property_id, name, description, fence_type, center_lat, center_lng, radius_meters, 
    alert_on_enter, alert_on_exit, status
) VALUES
(1, 'Mall Main Building Perimeter', 'Main building security perimeter', 'circle', 
 40.7128, -74.0060, 50, TRUE, TRUE, 'active'),

(2, 'Parking Garage Entry Points', 'Monitor vehicle access points', 'circle', 
 40.7130, -74.0058, 30, TRUE, FALSE, 'active'),

(3, 'Warehouse Secure Zone', 'High-security warehouse area', 'circle', 
 32.7767, -96.7970, 75, TRUE, TRUE, 'active'),

(5, 'Retail Store Perimeter', 'Store security boundary', 'circle', 
 34.0522, -118.2437, 40, TRUE, TRUE, 'active'),

(7, 'Office Complex Lobby', 'Main lobby monitoring zone', 'circle', 
 25.7617, -80.1918, 25, TRUE, FALSE, 'active');

-- Sample Vehicles
INSERT INTO vehicles (
    client_id, vehicle_name, license_plate, make, model, year, color, 
    assigned_guard_id, status, insurance_policy
) VALUES
(1, 'Security Patrol Unit 1', 'SEC-001', 'Ford', 'Explorer', 2023, 'White', 5, 'active', 'INS-001-2024'),
(2, 'Industrial Security Van', 'IND-001', 'Chevrolet', 'Express', 2022, 'Blue', 7, 'active', 'INS-002-2024'),
(3, 'Retail Mobile Unit', 'RET-001', 'Honda', 'Pilot', 2023, 'Black', 9, 'active', 'INS-003-2024'),
(4, 'Office Security Car', 'OFF-001', 'Toyota', 'Camry', 2022, 'Silver', 11, 'active', 'INS-004-2024');

-- Sample Client Notifications
INSERT INTO client_notifications (
    client_id, notification_type, title, message, priority, status, delivery_method,
    recipient_email, related_incident_id, created_at
) VALUES
(1, 'incident_alert', 'Security Alert - Suspicious Activity', 
 'Guard Alpha reported suspicious activity at main entrance. Individual was identified and situation resolved.', 
 'medium', 'sent', 'email', 'security@downtownmall.com', 1, DATE_SUB(NOW(), INTERVAL 2 HOUR)),

(2, 'incident_alert', 'High Priority - Warehouse Alarm', 
 'Motion sensor alarm triggered in Warehouse A. Guard Gamma responded immediately and secured the area.', 
 'high', 'sent', 'both', 'alerts@industrialpark.com', 3, DATE_SUB(NOW(), INTERVAL 1 DAY)),

(3, 'guard_status', 'Guard Status Update', 
 'All retail locations are currently secured. Guards Echo and Foxtrot completed evening rounds successfully.', 
 'low', 'sent', 'email', 'security@retailchain.com', NULL, DATE_SUB(NOW(), INTERVAL 30 MINUTE)),

(4, 'incident_alert', 'Maintenance Issue - Elevator', 
 'Elevator malfunction reported and resolved. Maintenance team contacted and issue fixed promptly.', 
 'medium', 'sent', 'email', 'management@officecomplex.com', 5, DATE_SUB(NOW(), INTERVAL 3 HOUR));

-- Sample Billing Records
INSERT INTO billing_records (
    client_id, invoice_number, billing_period_start, billing_period_end, billing_date, due_date,
    subtotal, guard_hours, status
) VALUES
(1, 'INV-2024-001', '2024-01-01', '2024-01-31', '2024-02-01', '2024-03-02', 
 6840.00, 240.00, 'paid'),

(2, 'INV-2024-002', '2024-01-01', '2024-01-31', '2024-02-01', '2024-03-02', 
 9600.00, 300.00, 'paid'),

(3, 'INV-2024-003', '2024-02-01', '2024-02-29', '2024-03-01', '2024-03-31', 
 5200.00, 200.00, 'sent'),

(4, 'INV-2024-004', '2024-03-01', '2024-03-31', '2024-04-01', '2024-05-01', 
 4800.00, 200.00, 'sent');

-- Sample Client Reports Configuration
INSERT INTO client_reports (
    client_id, report_name, report_type, report_config, schedule_type, 
    file_format, created_by, status
) VALUES
(1, 'Daily Security Summary', 'activity', 
 '{"include_incidents": true, "include_locations": true, "time_range": "24_hours"}', 
 'daily', 'pdf', 1, 'active'),

(2, 'Weekly Incident Report', 'incidents', 
 '{"priority_levels": ["high", "critical"], "include_photos": true}', 
 'weekly', 'pdf', 1, 'active'),

(3, 'Monthly Performance Review', 'performance', 
 '{"metrics": ["response_time", "patrol_coverage", "incident_resolution"]}', 
 'monthly', 'excel', 1, 'active'),

(4, 'Billing Statement', 'billing', 
 '{"include_hours": true, "include_overtime": true, "breakdown_by_guard": true}', 
 'monthly', 'pdf', 1, 'active');

-- Update client passwords to be properly hashed (in production, use proper password_hash())
UPDATE clients SET login_password = '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi';
