-- Enhanced Security Patrol System Database Schema
-- Version 2.0 with Client Portal Features

-- Set charset and create database
CREATE DATABASE IF NOT EXISTS security_patrol CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE security_patrol;

-- Drop existing tables in correct order (foreign key constraints)

-- Client Properties table (NEW)
CREATE TABLE client_properties (
    id INT AUTO_INCREMENT PRIMARY KEY,
    client_id INT NOT NULL,
    property_name VARCHAR(255) NOT NULL,
    property_type ENUM('office', 'warehouse', 'retail', 'residential', 'industrial', 'other') DEFAULT 'office',
    address TEXT NOT NULL,
    city VARCHAR(100),
    state VARCHAR(50),
    zip_code VARCHAR(20),
    latitude DECIMAL(10, 8),
    longitude DECIMAL(11, 8),
    contact_person VARCHAR(255),
    contact_phone VARCHAR(20),
    access_instructions TEXT,
    special_requirements TEXT,
    patrol_frequency ENUM('hourly', 'every_2_hours', 'every_4_hours', 'twice_daily', 'daily', 'custom') DEFAULT 'hourly',
    start_time TIME DEFAULT '18:00:00',
    end_time TIME DEFAULT '06:00:00',
    status ENUM('active', 'inactive', 'maintenance') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
    INDEX idx_client_id (client_id),
    INDEX idx_property_name (property_name),
    INDEX idx_status (status)
);

-- Location History table (enhanced)
CREATE TABLE location_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    latitude DECIMAL(10, 8) NOT NULL,
    longitude DECIMAL(11, 8) NOT NULL,
    accuracy FLOAT DEFAULT 0,
    altitude FLOAT DEFAULT 0,
    speed FLOAT DEFAULT 0,
    heading FLOAT DEFAULT 0,
    battery_level INT DEFAULT 100,
    is_active BOOLEAN DEFAULT TRUE,
    address VARCHAR(500),
    notes TEXT,
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_timestamp (user_id, timestamp),
    INDEX idx_timestamp (timestamp),
    INDEX idx_location (latitude, longitude)
);

-- Incidents table (enhanced)
CREATE TABLE incidents (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    incident_type ENUM('security_breach', 'maintenance_issue', 'safety_concern', 'emergency', 'observation', 'other') DEFAULT 'observation',
    priority ENUM('low', 'medium', 'high', 'critical') DEFAULT 'medium',
    status ENUM('open', 'in_progress', 'resolved', 'closed') DEFAULT 'open',
    latitude DECIMAL(10, 8),
    longitude DECIMAL(11, 8),
    address VARCHAR(500),
    photos TEXT, -- JSON array of image URLs
    evidence_files TEXT, -- JSON array of file URLs
    resolution TEXT,
    client_notified BOOLEAN DEFAULT FALSE,
    notification_sent_at TIMESTAMP NULL,
    resolved_by INT NULL,
    resolved_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (resolved_by) REFERENCES users(id) ON SET NULL,
    INDEX idx_user_id (user_id),
    INDEX idx_incident_type (incident_type),
    INDEX idx_priority (priority),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at)
);

-- Geofences table
CREATE TABLE geofences (
    id INT AUTO_INCREMENT PRIMARY KEY,
    property_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    fence_type ENUM('circle', 'polygon') DEFAULT 'circle',
    center_lat DECIMAL(10, 8),
    center_lng DECIMAL(11, 8),
    radius_meters INT,
    polygon_points JSON, -- For polygon geofences
    alert_on_enter BOOLEAN DEFAULT TRUE,
    alert_on_exit BOOLEAN DEFAULT TRUE,
    active_hours JSON, -- Time ranges when geofence is active
    status ENUM('active', 'inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    FOREIGN KEY (property_id) REFERENCES client_properties(id) ON DELETE CASCADE,
    INDEX idx_property_id (property_id),
    INDEX idx_status (status)
);

-- Vehicles table
CREATE TABLE vehicles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    client_id INT NULL,
    vehicle_name VARCHAR(255) NOT NULL,
    license_plate VARCHAR(20),
    make VARCHAR(100),
    model VARCHAR(100),
    year INT,
    color VARCHAR(50),
    vin VARCHAR(17),
    gps_device_id VARCHAR(100),
    status ENUM('active', 'maintenance', 'inactive') DEFAULT 'active',
    assigned_guard_id INT NULL,
    fuel_type ENUM('gasoline', 'diesel', 'electric', 'hybrid') DEFAULT 'gasoline',
    insurance_policy VARCHAR(100),
    registration_expires DATE,
    last_maintenance DATE,
    next_maintenance DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    FOREIGN KEY (client_id) REFERENCES clients(id) ON SET NULL,
    FOREIGN KEY (assigned_guard_id) REFERENCES users(id) ON SET NULL,
    INDEX idx_client_id (client_id),
    INDEX idx_license_plate (license_plate),
    INDEX idx_status (status)
);

-- Client Notifications table (NEW)
CREATE TABLE client_notifications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    client_id INT NOT NULL,
    notification_type ENUM('incident_alert', 'guard_status', 'system_alert', 'billing', 'report') DEFAULT 'incident_alert',
    title VARCHAR(255) NOT NULL,
    message TEXT NOT NULL,
    priority ENUM('low', 'medium', 'high', 'urgent') DEFAULT 'medium',
    status ENUM('pending', 'sent', 'delivered', 'failed') DEFAULT 'pending',
    delivery_method ENUM('email', 'sms', 'both') DEFAULT 'email',
    recipient_email VARCHAR(255),
    recipient_sms VARCHAR(20),
    related_incident_id INT NULL,
    related_user_id INT NULL,
    scheduled_at TIMESTAMP NULL,
    sent_at TIMESTAMP NULL,
    delivered_at TIMESTAMP NULL,
    error_message TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
    FOREIGN KEY (related_incident_id) REFERENCES incidents(id) ON SET NULL,
    FOREIGN KEY (related_user_id) REFERENCES users(id) ON SET NULL,
    INDEX idx_client_id (client_id),
    INDEX idx_status (status),
    INDEX idx_priority (priority),
    INDEX idx_created_at (created_at)
);

-- Billing Records table (NEW)
CREATE TABLE billing_records (
    id INT AUTO_INCREMENT PRIMARY KEY,
    client_id INT NOT NULL,
    invoice_number VARCHAR(50) UNIQUE NOT NULL,
    billing_period_start DATE NOT NULL,
    billing_period_end DATE NOT NULL,
    billing_date DATE NOT NULL,
    due_date DATE NOT NULL,
    subtotal DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    tax_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    discount_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    total_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    currency VARCHAR(3) DEFAULT 'USD',
    status ENUM('draft', 'sent', 'paid', 'overdue', 'cancelled') DEFAULT 'draft',
    payment_method VARCHAR(50),
    payment_reference VARCHAR(100),
    paid_date DATE NULL,
    guard_hours DECIMAL(8,2) DEFAULT 0.00,
    overtime_hours DECIMAL(8,2) DEFAULT 0.00,
    vehicle_usage_hours DECIMAL(8,2) DEFAULT 0.00,
    additional_charges JSON, -- Array of additional charges
    notes TEXT,
    pdf_file_path VARCHAR(500),
    sent_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
    INDEX idx_client_id (client_id),
    INDEX idx_invoice_number (invoice_number),
    INDEX idx_billing_date (billing_date),
    INDEX idx_status (status)
);

-- Client Reports table (NEW)
CREATE TABLE client_reports (
    id INT AUTO_INCREMENT PRIMARY KEY,
    client_id INT NOT NULL,
    report_name VARCHAR(255) NOT NULL,
    report_type ENUM('activity', 'incidents', 'billing', 'performance', 'custom') DEFAULT 'activity',
    report_config JSON NOT NULL, -- Report parameters and filters
    schedule_type ENUM('once', 'daily', 'weekly', 'monthly', 'quarterly') DEFAULT 'once',
    schedule_config JSON, -- Schedule parameters
    file_format ENUM('pdf', 'excel', 'csv') DEFAULT 'pdf',
    last_generated TIMESTAMP NULL,
    next_scheduled TIMESTAMP NULL,
    status ENUM('active', 'paused', 'completed') DEFAULT 'active',
    file_path VARCHAR(500),
    created_by INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_client_id (client_id),
    INDEX idx_report_type (report_type),
    INDEX idx_next_scheduled (next_scheduled)
);

-- Notification Logs table (NEW)
CREATE TABLE notification_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    notification_id INT NOT NULL,
    event_type ENUM('created', 'sent', 'delivered', 'failed', 'opened', 'clicked') NOT NULL,
    event_data JSON,
    ip_address VARCHAR(45),
    user_agent TEXT,
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (notification_id) REFERENCES client_notifications(id) ON DELETE CASCADE,
    INDEX idx_notification_id (notification_id),
    INDEX idx_event_type (event_type),
    INDEX idx_timestamp (timestamp)
);

-- Login Attempts table
CREATE TABLE login_attempts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255),
    ip_address VARCHAR(45),
    user_agent TEXT,
    success BOOLEAN DEFAULT FALSE,
    failure_reason VARCHAR(255),
    attempted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_email (email),
    INDEX idx_ip_address (ip_address),
    INDEX idx_attempted_at (attempted_at)
);

-- Audit Logs table
CREATE TABLE audit_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NULL,
    client_id INT NULL,
    action VARCHAR(100) NOT NULL,
    table_name VARCHAR(100),
    record_id INT,
    old_values JSON,
    new_values JSON,
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (user_id) REFERENCES users(id) ON SET NULL,
    FOREIGN KEY (client_id) REFERENCES clients(id) ON SET NULL,
    INDEX idx_user_id (user_id),
    INDEX idx_client_id (client_id),
    INDEX idx_action (action),
    INDEX idx_created_at (created_at)
);
    SET NEW.total_amount = NEW.subtotal + NEW.tax_amount - NEW.discount_amount;
END//

CREATE TRIGGER log_incident_changes
AFTER UPDATE ON incidents
FOR EACH ROW
BEGIN
    IF OLD.status != NEW.status OR OLD.priority != NEW.priority THEN
        INSERT INTO audit_logs (user_id, action, table_name, record_id, old_values, new_values, created_at)
        VALUES (NEW.user_id, 'UPDATE', 'incidents', NEW.id, 
                JSON_OBJECT('status', OLD.status, 'priority', OLD.priority),
                JSON_OBJECT('status', NEW.status, 'priority', NEW.priority),
                NOW());
    END IF;
END//

DELIMITER ;

-- Create indexes for performance
CREATE INDEX idx_location_recent ON location_history (user_id, timestamp DESC);
CREATE INDEX idx_incidents_recent ON incidents (user_id, created_at DESC);
CREATE INDEX idx_billing_period ON billing_records (client_id, billing_period_start, billing_period_end);

-- Insert system configuration
INSERT INTO audit_logs (action, table_name, new_values, created_at) 
VALUES ('SYSTEM_INIT', 'schema', JSON_OBJECT('version', '2.0', 'features', 'client_portal'), NOW());

-- Create placeholder files directory references
CREATE TABLE system_config (
    config_key VARCHAR(100) PRIMARY KEY,
    config_value TEXT,
    description TEXT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

INSERT INTO system_config VALUES 
('schema_version', '2.0', 'Database schema version', NOW()),
('client_portal_enabled', 'true', 'Enable client portal features', NOW()),
('email_notifications_enabled', 'true', 'Enable email notifications', NOW()),
('sms_notifications_enabled', 'false', 'Enable SMS notifications', NOW()),
('billing_auto_generate', 'true', 'Auto-generate monthly invoices', NOW());
