-- SepuraHome Advanced Device Management Database Schema
-- This extends the basic device tracking with enterprise-level features

-- Enhanced devices table (extends existing table)
ALTER TABLE devices ADD COLUMN IF NOT EXISTS customer_id VARCHAR(50) DEFAULT NULL;
ALTER TABLE devices ADD COLUMN IF NOT EXISTS site_location VARCHAR(100) DEFAULT 'Unknown';
ALTER TABLE devices ADD COLUMN IF NOT EXISTS health_score INT DEFAULT 100;
ALTER TABLE devices ADD COLUMN IF NOT EXISTS performance_score INT DEFAULT 100;
ALTER TABLE devices ADD COLUMN IF NOT EXISTS uptime_percentage DECIMAL(5,2) DEFAULT 100.00;
ALTER TABLE devices ADD COLUMN IF NOT EXISTS error_count INT DEFAULT 0;
ALTER TABLE devices ADD COLUMN IF NOT EXISTS signal_strength INT DEFAULT 100;
ALTER TABLE devices ADD COLUMN IF NOT EXISTS data_usage_mb INT DEFAULT 0;
ALTER TABLE devices ADD COLUMN IF NOT EXISTS last_maintenance DATETIME DEFAULT NULL;
ALTER TABLE devices ADD COLUMN IF NOT EXISTS device_type VARCHAR(50) DEFAULT 'SepuraHome';
ALTER TABLE devices ADD COLUMN IF NOT EXISTS installation_date DATE DEFAULT NULL;
ALTER TABLE devices ADD COLUMN IF NOT EXISTS warranty_expiry DATE DEFAULT NULL;

-- Add indexes for performance
CREATE INDEX IF NOT EXISTS idx_devices_customer ON devices(customer_id);
CREATE INDEX IF NOT EXISTS idx_devices_location ON devices(site_location);
CREATE INDEX IF NOT EXISTS idx_devices_health ON devices(health_score);
CREATE INDEX IF NOT EXISTS idx_devices_online ON devices(last_seen);

-- Device Performance History Table
CREATE TABLE IF NOT EXISTS device_performance_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    device_id VARCHAR(100) NOT NULL,
    health_score INT NOT NULL,
    performance_score INT NOT NULL,
    uptime_percentage DECIMAL(5,2) NOT NULL,
    error_count INT DEFAULT 0,
    signal_strength INT DEFAULT 0,
    cpu_usage DECIMAL(5,2) DEFAULT 0,
    memory_usage DECIMAL(5,2) DEFAULT 0,
    temperature DECIMAL(5,2) DEFAULT 0,
    recorded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (device_id) REFERENCES devices(device_id) ON DELETE CASCADE,
    INDEX idx_performance_device_time (device_id, recorded_at),
    INDEX idx_performance_time (recorded_at)
);

-- Alerts System Table
CREATE TABLE IF NOT EXISTS alerts (
    alert_id INT AUTO_INCREMENT PRIMARY KEY,
    device_id VARCHAR(100) NOT NULL,
    alert_type ENUM('offline', 'low_battery', 'high_temperature', 'low_performance', 'maintenance_due', 'firmware_outdated', 'connectivity_issue', 'hardware_failure', 'custom') NOT NULL,
    severity ENUM('low', 'medium', 'high', 'critical') NOT NULL DEFAULT 'medium',
    message TEXT NOT NULL,
    acknowledged BOOLEAN DEFAULT FALSE,
    acknowledged_at DATETIME NULL,
    acknowledged_by VARCHAR(100) NULL,
    resolved BOOLEAN DEFAULT FALSE,
    resolved_at DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (device_id) REFERENCES devices(device_id) ON DELETE CASCADE,
    INDEX idx_alerts_device (device_id),
    INDEX idx_alerts_type (alert_type),
    INDEX idx_alerts_severity (severity),
    INDEX idx_alerts_acknowledged (acknowledged),
    INDEX idx_alerts_created (created_at)
);

-- Maintenance Schedule Table
CREATE TABLE IF NOT EXISTS maintenance_schedule (
    maintenance_id INT AUTO_INCREMENT PRIMARY KEY,
    device_id VARCHAR(100) NOT NULL,
    maintenance_type ENUM('routine', 'preventive', 'corrective', 'firmware_update', 'calibration', 'replacement', 'inspection') NOT NULL,
    scheduled_date DATETIME NOT NULL,
    completed_date DATETIME NULL,
    priority ENUM('low', 'medium', 'high', 'urgent') DEFAULT 'medium',
    description TEXT,
    technician_assigned VARCHAR(100) NULL,
    estimated_duration INT DEFAULT 60, -- minutes
    cost_estimate DECIMAL(10,2) DEFAULT 0,
    status ENUM('scheduled', 'in_progress', 'completed', 'cancelled', 'postponed') DEFAULT 'scheduled',
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (device_id) REFERENCES devices(device_id) ON DELETE CASCADE,
    INDEX idx_maintenance_device (device_id),
    INDEX idx_maintenance_date (scheduled_date),
    INDEX idx_maintenance_status (status),
    INDEX idx_maintenance_priority (priority)
);

-- Firmware Management Table
CREATE TABLE IF NOT EXISTS firmware_updates (
    update_id INT AUTO_INCREMENT PRIMARY KEY,
    device_id VARCHAR(100) NOT NULL,
    current_firmware_version VARCHAR(50),
    target_firmware_version VARCHAR(50) NOT NULL,
    update_type ENUM('manual', 'automatic', 'scheduled') DEFAULT 'manual',
    status ENUM('pending', 'downloading', 'installing', 'completed', 'failed', 'cancelled') DEFAULT 'pending',
    progress_percentage INT DEFAULT 0,
    error_message TEXT NULL,
    initiated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    started_at DATETIME NULL,
    completed_at DATETIME NULL,
    initiated_by VARCHAR(100) DEFAULT 'system',
    FOREIGN KEY (device_id) REFERENCES devices(device_id) ON DELETE CASCADE,
    INDEX idx_firmware_device (device_id),
    INDEX idx_firmware_status (status),
    INDEX idx_firmware_version (target_firmware_version)
);

-- Customer Management Table
CREATE TABLE IF NOT EXISTS customers (
    customer_id VARCHAR(50) PRIMARY KEY,
    company_name VARCHAR(100) NOT NULL,
    contact_person VARCHAR(100),
    email VARCHAR(100),
    phone VARCHAR(20),
    address TEXT,
    city VARCHAR(50),
    state VARCHAR(50),
    country VARCHAR(50),
    postal_code VARCHAR(20),
    account_type ENUM('basic', 'premium', 'enterprise') DEFAULT 'basic',
    contract_start_date DATE,
    contract_end_date DATE,
    billing_contact VARCHAR(100),
    technical_contact VARCHAR(100),
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_customers_company (company_name),
    INDEX idx_customers_type (account_type)
);

-- Site Locations Table
CREATE TABLE IF NOT EXISTS site_locations (
    site_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id VARCHAR(50) NOT NULL,
    site_name VARCHAR(100) NOT NULL,
    site_type ENUM('office', 'warehouse', 'factory', 'retail', 'residential', 'other') DEFAULT 'office',
    address TEXT,
    city VARCHAR(50),
    state VARCHAR(50),
    country VARCHAR(50),
    postal_code VARCHAR(20),
    latitude DECIMAL(10, 8) NULL,
    longitude DECIMAL(11, 8) NULL,
    timezone VARCHAR(50) DEFAULT 'UTC',
    contact_person VARCHAR(100),
    contact_phone VARCHAR(20),
    installation_date DATE,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE,
    INDEX idx_sites_customer (customer_id),
    INDEX idx_sites_type (site_type),
    INDEX idx_sites_location (latitude, longitude)
);

-- Device Groups/Categories Table
CREATE TABLE IF NOT EXISTS device_groups (
    group_id INT AUTO_INCREMENT PRIMARY KEY,
    group_name VARCHAR(100) NOT NULL,
    group_type ENUM('location', 'function', 'model', 'customer', 'custom') DEFAULT 'custom',
    description TEXT,
    parent_group_id INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (parent_group_id) REFERENCES device_groups(group_id) ON DELETE SET NULL,
    INDEX idx_groups_type (group_type),
    INDEX idx_groups_parent (parent_group_id)
);

-- Device Group Assignments
CREATE TABLE IF NOT EXISTS device_group_assignments (
    assignment_id INT AUTO_INCREMENT PRIMARY KEY,
    device_id VARCHAR(100) NOT NULL,
    group_id INT NOT NULL,
    assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (device_id) REFERENCES devices(device_id) ON DELETE CASCADE,
    FOREIGN KEY (group_id) REFERENCES device_groups(group_id) ON DELETE CASCADE,
    UNIQUE KEY unique_device_group (device_id, group_id),
    INDEX idx_assignments_device (device_id),
    INDEX idx_assignments_group (group_id)
);

-- System Events/Activity Log
CREATE TABLE IF NOT EXISTS system_events (
    event_id INT AUTO_INCREMENT PRIMARY KEY,
    event_type ENUM('device_registered', 'device_online', 'device_offline', 'firmware_updated', 'maintenance_completed', 'alert_created', 'alert_resolved', 'user_action', 'system_action') NOT NULL,
    device_id VARCHAR(100) NULL,
    user_id VARCHAR(100) NULL,
    description TEXT NOT NULL,
    metadata JSON NULL,
    severity ENUM('info', 'warning', 'error', 'critical') DEFAULT 'info',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (device_id) REFERENCES devices(device_id) ON DELETE SET NULL,
    INDEX idx_events_type (event_type),
    INDEX idx_events_device (device_id),
    INDEX idx_events_time (created_at),
    INDEX idx_events_severity (severity)
);

-- Device Data/Sensor Readings Table
CREATE TABLE IF NOT EXISTS device_data (
    data_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    device_id VARCHAR(100) NOT NULL,
    data_type VARCHAR(50) NOT NULL, -- temperature, humidity, pressure, etc.
    value DECIMAL(10,4) NOT NULL,
    unit VARCHAR(20) NOT NULL,
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (device_id) REFERENCES devices(device_id) ON DELETE CASCADE,
    INDEX idx_data_device_type (device_id, data_type),
    INDEX idx_data_timestamp (timestamp),
    PARTITION BY RANGE (UNIX_TIMESTAMP(timestamp)) (
        PARTITION p_old VALUES LESS THAN (UNIX_TIMESTAMP('2024-01-01')),
        PARTITION p_2024 VALUES LESS THAN (UNIX_TIMESTAMP('2025-01-01')),
        PARTITION p_2025 VALUES LESS THAN (UNIX_TIMESTAMP('2026-01-01')),
        PARTITION p_future VALUES LESS THAN MAXVALUE
    )
);

-- Device Commands Table (for remote control)
CREATE TABLE IF NOT EXISTS device_commands (
    command_id INT AUTO_INCREMENT PRIMARY KEY,
    device_id VARCHAR(100) NOT NULL,
    command_type ENUM('restart', 'update_firmware', 'change_config', 'run_diagnostic', 'calibrate', 'factory_reset', 'custom') NOT NULL,
    command_data JSON NULL,
    status ENUM('pending', 'sent', 'acknowledged', 'completed', 'failed', 'timeout') DEFAULT 'pending',
    sent_at DATETIME NULL,
    acknowledged_at DATETIME NULL,
    completed_at DATETIME NULL,
    response_data JSON NULL,
    error_message TEXT NULL,
    timeout_at DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_by VARCHAR(100) DEFAULT 'system',
    FOREIGN KEY (device_id) REFERENCES devices(device_id) ON DELETE CASCADE,
    INDEX idx_commands_device (device_id),
    INDEX idx_commands_status (status),
    INDEX idx_commands_created (created_at)
);

-- Business Reports Cache Table
CREATE TABLE IF NOT EXISTS report_cache (
    cache_id INT AUTO_INCREMENT PRIMARY KEY,
    report_type VARCHAR(100) NOT NULL,
    parameters JSON NOT NULL,
    report_data JSON NOT NULL,
    generated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP NOT NULL,
    INDEX idx_cache_type (report_type),
    INDEX idx_cache_expires (expires_at)
);

-- System Configuration Table
CREATE TABLE IF NOT EXISTS system_config (
    config_key VARCHAR(100) PRIMARY KEY,
    config_value TEXT NOT NULL,
    description TEXT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    updated_by VARCHAR(100) DEFAULT 'system'
);

-- Insert default system configurations
INSERT IGNORE INTO system_config (config_key, config_value, description) VALUES
('maintenance_alert_days', '7', 'Days before maintenance due to send alert'),
('firmware_check_interval', '24', 'Hours between firmware update checks'),
('offline_alert_minutes', '5', 'Minutes before device considered offline'),
('performance_threshold_warning', '70', 'Performance score threshold for warnings'),
('performance_threshold_critical', '50', 'Performance score threshold for critical alerts'),
('health_check_interval', '60', 'Minutes between health score calculations'),
('data_retention_days', '365', 'Days to retain historical data'),
('alert_escalation_hours', '4', 'Hours before unacknowledged critical alerts escalate'),
('backup_frequency_hours', '24', 'Hours between automated backups'),
('dashboard_refresh_seconds', '30', 'Seconds between dashboard auto-refresh');

-- Create Views for Common Queries

-- Active Devices View
CREATE OR REPLACE VIEW active_devices AS
SELECT 
    d.*,
    CASE WHEN d.last_seen > DATE_SUB(NOW(), INTERVAL 5 MINUTE) THEN 1 ELSE 0 END as online,
    c.company_name as customer_name,
    s.site_name,
    COUNT(DISTINCT a.alert_id) as active_alerts
FROM devices d
LEFT JOIN customers c ON d.customer_id = c.customer_id
LEFT JOIN site_locations s ON d.site_location = s.site_name
LEFT JOIN alerts a ON d.device_id = a.device_id AND a.acknowledged = 0
WHERE d.is_active = 1
GROUP BY d.device_id;

-- Device Health Summary View
CREATE OR REPLACE VIEW device_health_summary AS
SELECT 
    d.device_id,
    d.device_name,
    d.health_score,
    d.performance_score,
    d.uptime_percentage,
    d.error_count,
    CASE 
        WHEN d.health_score >= 90 THEN 'Excellent'
        WHEN d.health_score >= 75 THEN 'Good'
        WHEN d.health_score >= 60 THEN 'Fair'
        ELSE 'Poor'
    END as health_status,
    COUNT(DISTINCT m.maintenance_id) as pending_maintenance,
    COUNT(DISTINCT a.alert_id) as active_alerts
FROM devices d
LEFT JOIN maintenance_schedule m ON d.device_id = m.device_id AND m.status = 'scheduled'
LEFT JOIN alerts a ON d.device_id = a.device_id AND a.acknowledged = 0
GROUP BY d.device_id;

-- Fleet Statistics View
CREATE OR REPLACE VIEW fleet_statistics AS
SELECT 
    COUNT(*) as total_devices,
    SUM(CASE WHEN last_seen > DATE_SUB(NOW(), INTERVAL 5 MINUTE) THEN 1 ELSE 0 END) as online_devices,
    SUM(CASE WHEN is_active = 1 THEN 1 ELSE 0 END) as active_devices,
    AVG(health_score) as avg_health_score,
    AVG(performance_score) as avg_performance_score,
    AVG(uptime_percentage) as avg_uptime,
    COUNT(DISTINCT customer_id) as total_customers,
    SUM(error_count) as total_errors
FROM devices;

-- Recent Activities View
CREATE OR REPLACE VIEW recent_activities AS
SELECT 
    'device' as activity_type,
    CONCAT('Device ', device_name, ' came online') as description,
    last_seen as activity_time,
    device_id as related_device,
    NULL as related_alert
FROM devices 
WHERE last_seen > DATE_SUB(NOW(), INTERVAL 1 HOUR)
UNION ALL
SELECT 
    'alert' as activity_type,
    CONCAT(alert_type, ': ', message) as description,
    created_at as activity_time,
    device_id as related_device,
    alert_id as related_alert
FROM alerts 
WHERE created_at > DATE_SUB(NOW(), INTERVAL 1 HOUR)
ORDER BY activity_time DESC
LIMIT 50;

-- Triggers for automated tasks

-- Trigger to create alerts for offline devices
DELIMITER //
CREATE TRIGGER IF NOT EXISTS check_device_offline 
AFTER UPDATE ON devices
FOR EACH ROW
BEGIN
    IF OLD.last_seen != NEW.last_seen AND 
       NEW.last_seen < DATE_SUB(NOW(), INTERVAL 10 MINUTE) AND 
       NEW.is_active = 1 THEN
        INSERT IGNORE INTO alerts (device_id, alert_type, severity, message)
        VALUES (NEW.device_id, 'offline', 'medium', 'Device has been offline for more than 10 minutes');
    END IF;
END//

-- Trigger to create alerts for low performance
CREATE TRIGGER IF NOT EXISTS check_device_performance 
AFTER UPDATE ON devices
FOR EACH ROW
BEGIN
    IF NEW.performance_score < 60 AND (OLD.performance_score IS NULL OR OLD.performance_score >= 60) THEN
        INSERT IGNORE INTO alerts (device_id, alert_type, severity, message)
        VALUES (NEW.device_id, 'low_performance', 'high', CONCAT('Device performance dropped to ', NEW.performance_score, '%'));
    END IF;
END//

-- Trigger to log system events
CREATE TRIGGER IF NOT EXISTS log_device_events 
AFTER INSERT ON devices
FOR EACH ROW
BEGIN
    INSERT INTO system_events (event_type, device_id, description)
    VALUES ('device_registered', NEW.device_id, CONCAT('New device registered: ', NEW.device_name));
END//

DELIMITER ;

-- Create stored procedures for common operations

-- Procedure to calculate device health score
DELIMITER //
CREATE PROCEDURE IF NOT EXISTS CalculateDeviceHealth(IN device_id_param VARCHAR(100))
BEGIN
    DECLARE health_score INT DEFAULT 100;
    DECLARE error_count_val INT;
    DECLARE uptime_val DECIMAL(5,2);
    DECLARE last_seen_minutes INT;
    
    -- Get current values
    SELECT error_count, uptime_percentage, 
           TIMESTAMPDIFF(MINUTE, last_seen, NOW()) as minutes_offline
    INTO error_count_val, uptime_val, last_seen_minutes
    FROM devices 
    WHERE device_id = device_id_param;
    
    -- Calculate health score based on various factors
    SET health_score = health_score - (error_count_val * 5); -- 5 points per error
    SET health_score = health_score - ((100 - uptime_val) * 0.5); -- uptime factor
    
    -- Penalty for being offline
    IF last_seen_minutes > 60 THEN
        SET health_score = health_score - 20;
    ELSEIF last_seen_minutes > 10 THEN
        SET health_score = health_score - 10;
    END IF;
    
    -- Ensure score stays within bounds
    IF health_score < 0 THEN SET health_score = 0; END IF;
    IF health_score > 100 THEN SET health_score = 100; END IF;
    
    -- Update the device
    UPDATE devices 
    SET health_score = health_score 
    WHERE device_id = device_id_param;
END//

-- Procedure to clean old data
CREATE PROCEDURE IF NOT EXISTS CleanOldData()
BEGIN
    DECLARE retention_days INT;
    
    -- Get retention period from config
    SELECT CAST(config_value AS UNSIGNED) INTO retention_days
    FROM system_config 
    WHERE config_key = 'data_retention_days';
    
    -- Clean old performance history
    DELETE FROM device_performance_history 
    WHERE recorded_at < DATE_SUB(NOW(), INTERVAL retention_days DAY);
    
    -- Clean resolved alerts older than 90 days
    DELETE FROM alerts 
    WHERE resolved = 1 AND resolved_at < DATE_SUB(NOW(), INTERVAL 90 DAY);
    
    -- Clean old system events
    DELETE FROM system_events 
    WHERE created_at < DATE_SUB(NOW(), INTERVAL retention_days DAY);
    
    -- Clean expired report cache
    DELETE FROM report_cache 
    WHERE expires_at < NOW();
END//

DELIMITER ;

-- Schedule regular maintenance (requires MySQL Event Scheduler)
-- SET GLOBAL event_scheduler = ON;

-- Create event to calculate health scores every hour
-- CREATE EVENT IF NOT EXISTS calculate_health_scores
-- ON SCHEDULE EVERY 1 HOUR
-- DO
-- BEGIN
--     DECLARE done INT DEFAULT FALSE;
--     DECLARE device_id_val VARCHAR(100);
--     DECLARE device_cursor CURSOR FOR SELECT device_id FROM devices WHERE is_active = 1;
--     DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
--     
--     OPEN device_cursor;
--     read_loop: LOOP
--         FETCH device_cursor INTO device_id_val;
--         IF done THEN
--             LEAVE read_loop;
--         END IF;
--         CALL CalculateDeviceHealth(device_id_val);
--     END LOOP;
--     CLOSE device_cursor;
-- END;

-- Create event to clean old data daily
-- CREATE EVENT IF NOT EXISTS daily_cleanup
-- ON SCHEDULE EVERY 1 DAY
-- STARTS TIMESTAMP(CURDATE() + INTERVAL 1 DAY, '02:00:00')
-- DO
-- CALL CleanOldData();

-- Insert sample data for testing (optional)
-- INSERT IGNORE INTO customers VALUES 
-- ('CUST001', 'Tech Solutions Inc', 'John Smith', 'john@techsolutions.com', '+1-555-0123', '123 Main St', 'San Francisco', 'CA', 'USA', '94105', 'premium', '2024-01-01', '2025-01-01', 'billing@techsolutions.com', 'tech@techsolutions.com', 'Premium customer with 24/7 support', NOW(), NOW());

-- INSERT IGNORE INTO device_groups VALUES 
-- (1, 'Production Devices', 'function', 'Devices used in production environment', NULL, NOW()),
-- (2, 'Development Devices', 'function', 'Devices used for development and testing', NULL, NOW()),
-- (3, 'San Francisco Office', 'location', 'Devices at SF office location', NULL, NOW());

COMMIT;