-- Monitoring System Database Schema

-- Devices table
CREATE TABLE IF NOT EXISTS devices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    device_id VARCHAR(255) UNIQUE NOT NULL,
    device_name VARCHAR(255),
    device_model VARCHAR(255),
    android_version VARCHAR(50),
    app_version VARCHAR(50),
    last_seen TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_device_id (device_id),
    INDEX idx_last_seen (last_seen)
);

-- Call logs table
CREATE TABLE IF NOT EXISTS call_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    device_id VARCHAR(255) NOT NULL,
    timestamp BIGINT NOT NULL,
    duration BIGINT NOT NULL,
    type VARCHAR(20) NOT NULL, -- incoming, outgoing, missed
    number VARCHAR(50) NOT NULL,
    contact_name VARCHAR(255),
    latitude DECIMAL(10, 8),
    longitude DECIMAL(11, 8),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_device_timestamp (device_id, timestamp),
    INDEX idx_type (type),
    INDEX idx_timestamp (timestamp),
    FOREIGN KEY (device_id) REFERENCES devices(device_id) ON DELETE CASCADE
);

-- Locations table
CREATE TABLE IF NOT EXISTS locations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    device_id VARCHAR(255) NOT NULL,
    latitude DECIMAL(10, 8) NOT NULL,
    longitude DECIMAL(11, 8) NOT NULL,
    accuracy FLOAT,
    altitude FLOAT,
    speed FLOAT,
    bearing FLOAT,
    timestamp BIGINT NOT NULL,
    address TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_device_timestamp (device_id, timestamp),
    INDEX idx_coordinates (latitude, longitude),
    INDEX idx_timestamp (timestamp),
    FOREIGN KEY (device_id) REFERENCES devices(device_id) ON DELETE CASCADE
);

-- Media files table
CREATE TABLE IF NOT EXISTS media_files (
    id INT AUTO_INCREMENT PRIMARY KEY,
    device_id VARCHAR(255) NOT NULL,
    file_name VARCHAR(255) NOT NULL,
    file_path VARCHAR(500) NOT NULL,
    file_size BIGINT NOT NULL,
    mime_type VARCHAR(100) NOT NULL,
    file_type VARCHAR(20) NOT NULL, -- image, video, audio, screenshot
    timestamp BIGINT NOT NULL,
    checksum VARCHAR(64),
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_device_type (device_id, file_type),
    INDEX idx_timestamp (timestamp),
    INDEX idx_file_type (file_type),
    FOREIGN KEY (device_id) REFERENCES devices(device_id) ON DELETE CASCADE
);

-- Remote commands table
CREATE TABLE IF NOT EXISTS remote_commands (
    id INT AUTO_INCREMENT PRIMARY KEY,
    device_id VARCHAR(255) NOT NULL,
    command_type VARCHAR(50) NOT NULL,
    command_param TEXT,
    status VARCHAR(20) DEFAULT 'pending', -- pending, executed, failed
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    executed_at TIMESTAMP NULL,
    response TEXT,
    INDEX idx_device_status (device_id, status),
    INDEX idx_command_type (command_type),
    INDEX idx_created_at (created_at),
    FOREIGN KEY (device_id) REFERENCES devices(device_id) ON DELETE CASCADE
);

-- Contacts table (for tracking contact changes)
CREATE TABLE IF NOT EXISTS contacts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    device_id VARCHAR(255) NOT NULL,
    contact_name VARCHAR(255) NOT NULL,
    phone_number VARCHAR(50),
    email VARCHAR(255),
    timestamp BIGINT NOT NULL,
    action VARCHAR(20) NOT NULL, -- added, updated, deleted
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_device_timestamp (device_id, timestamp),
    INDEX idx_phone (phone_number),
    FOREIGN KEY (device_id) REFERENCES devices(device_id) ON DELETE CASCADE
);

-- System logs table (for debugging and monitoring)
CREATE TABLE IF NOT EXISTS system_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    device_id VARCHAR(255),
    log_level VARCHAR(20) NOT NULL, -- info, warning, error, debug
    message TEXT NOT NULL,
    details TEXT,
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_device_level (device_id, log_level),
    INDEX idx_timestamp (timestamp)
);

-- Create views for common queries
CREATE VIEW device_summary AS
SELECT 
    d.device_id,
    d.device_name,
    d.last_seen,
    COUNT(DISTINCT cl.id) as total_calls,
    COUNT(DISTINCT l.id) as total_locations,
    COUNT(DISTINCT mf.id) as total_media,
    COUNT(DISTINCT rc.id) as total_commands,
    MAX(cl.timestamp) as last_call_time,
    MAX(l.timestamp) as last_location_time,
    MAX(mf.timestamp) as last_media_time
FROM devices d
LEFT JOIN call_logs cl ON d.device_id = cl.device_id
LEFT JOIN locations l ON d.device_id = l.device_id
LEFT JOIN media_files mf ON d.device_id = mf.device_id
LEFT JOIN remote_commands rc ON d.device_id = rc.device_id
GROUP BY d.device_id, d.device_name, d.last_seen;

-- Create view for recent activity
CREATE VIEW recent_activity AS
SELECT 
    device_id,
    'call' as activity_type,
    timestamp,
    CONCAT(type, ' call to ', number) as description,
    created_at
FROM call_logs
UNION ALL
SELECT 
    device_id,
    'location' as activity_type,
    timestamp,
    CONCAT('Location: ', latitude, ', ', longitude) as description,
    created_at
FROM locations
UNION ALL
SELECT 
    device_id,
    'media' as activity_type,
    timestamp,
    CONCAT('Media: ', file_name) as description,
    uploaded_at as created_at
FROM media_files
ORDER BY timestamp DESC;

-- Insert default configuration
INSERT IGNORE INTO system_logs (device_id, log_level, message) 
VALUES (NULL, 'info', 'Database initialized successfully');

-- Create stored procedures for common operations
DELIMITER //

-- Procedure to clean up old data
CREATE PROCEDURE CleanupOldData(IN days_to_keep INT)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;
    
    START TRANSACTION;
    
    -- Delete old call logs (keep only specified days)
    DELETE FROM call_logs 
    WHERE created_at < DATE_SUB(NOW(), INTERVAL days_to_keep DAY);
    
    -- Delete old locations (keep only specified days)
    DELETE FROM locations 
    WHERE created_at < DATE_SUB(NOW(), INTERVAL days_to_keep DAY);
    
    -- Delete old media files (keep only specified days)
    DELETE FROM media_files 
    WHERE created_at < DATE_SUB(NOW(), INTERVAL days_to_keep DAY);
    
    -- Delete old system logs (keep only 30 days)
    DELETE FROM system_logs 
    WHERE timestamp < DATE_SUB(NOW(), INTERVAL 30 DAY);
    
    COMMIT;
    
    INSERT INTO system_logs (device_id, log_level, message) 
    VALUES (NULL, 'info', CONCAT('Cleanup completed - kept data for ', days_to_keep, ' days'));
END //

-- Procedure to get device statistics
CREATE PROCEDURE GetDeviceStats(IN device_id_param VARCHAR(255))
BEGIN
    SELECT 
        d.device_name,
        d.last_seen,
        COUNT(DISTINCT cl.id) as total_calls,
        COUNT(DISTINCT CASE WHEN cl.type = 'incoming' THEN cl.id END) as incoming_calls,
        COUNT(DISTINCT CASE WHEN cl.type = 'outgoing' THEN cl.id END) as outgoing_calls,
        COUNT(DISTINCT CASE WHEN cl.type = 'missed' THEN cl.id END) as missed_calls,
        AVG(cl.duration) as avg_call_duration,
        COUNT(DISTINCT l.id) as total_locations,
        COUNT(DISTINCT mf.id) as total_media,
        COUNT(DISTINCT CASE WHEN mf.file_type = 'image' THEN mf.id END) as total_images,
        COUNT(DISTINCT CASE WHEN mf.file_type = 'video' THEN mf.id END) as total_videos,
        COUNT(DISTINCT CASE WHEN mf.file_type = 'audio' THEN mf.id END) as total_audio,
        COUNT(DISTINCT rc.id) as total_commands,
        COUNT(DISTINCT CASE WHEN rc.status = 'pending' THEN rc.id END) as pending_commands
    FROM devices d
    LEFT JOIN call_logs cl ON d.device_id = cl.device_id
    LEFT JOIN locations l ON d.device_id = l.device_id
    LEFT JOIN media_files mf ON d.device_id = mf.device_id
    LEFT JOIN remote_commands rc ON d.device_id = rc.device_id
    WHERE d.device_id = device_id_param
    GROUP BY d.device_id, d.device_name, d.last_seen;
END //

DELIMITER ;

-- Create indexes for better performance
CREATE INDEX idx_call_logs_composite ON call_logs(device_id, type, timestamp);
CREATE INDEX idx_locations_composite ON locations(device_id, timestamp, latitude, longitude);
CREATE INDEX idx_media_composite ON media_files(device_id, file_type, timestamp);
CREATE INDEX idx_commands_composite ON remote_commands(device_id, status, created_at);

-- Set up automatic cleanup (requires MySQL event scheduler)
-- Note: This requires SUPER privilege and event scheduler to be enabled
-- SET GLOBAL event_scheduler = ON;

-- CREATE EVENT IF NOT EXISTS cleanup_old_data
-- ON SCHEDULE EVERY 1 DAY
-- STARTS CURRENT_TIMESTAMP
-- DO
--   CALL CleanupOldData(30); -- Keep data for 30 days

-- Grant permissions (adjust as needed for your setup)
-- GRANT SELECT, INSERT, UPDATE, DELETE ON monitoring_db.* TO 'monitoring_user'@'localhost';
-- FLUSH PRIVILEGES;
