DROP DATABASE IF EXISTS sentinelcloud_db;
CREATE DATABASE sentinelcloud_db;
USE sentinelcloud_db;

CREATE TABLE Users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('admin', 'user') NOT NULL DEFAULT 'user',
    mfa_enabled BOOLEAN NOT NULL DEFAULT FALSE,
    otp_code VARCHAR(10) NULL,
    otp_expires_at DATETIME NULL,
    is_verified TINYINT(1) NOT NULL DEFAULT 0,
    last_login DATETIME NULL,
    full_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_users_username (username),
    INDEX idx_users_role (role),
    INDEX idx_users_email (email)
);
ALTER TABLE Users MODIFY role VARCHAR(20) NOT NULL DEFAULT 'user';

CREATE TABLE EdgeNodes (
    id INT PRIMARY KEY AUTO_INCREMENT,
    assigned_user_id INT NULL,
    node_name VARCHAR(100) NOT NULL UNIQUE,
    location VARCHAR(200) NOT NULL,
    ip_address VARCHAR(45) NOT NULL,
    status ENUM('online', 'offline', 'warning') NOT NULL DEFAULT 'offline',
    stream_url VARCHAR(500) NOT NULL,
    last_heartbeat DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_node_owner FOREIGN KEY (assigned_user_id) REFERENCES Users(id) ON DELETE SET NULL ON UPDATE CASCADE,
    INDEX idx_nodes_status (status),
    INDEX idx_nodes_ip (ip_address),
    INDEX idx_nodes_owner (assigned_user_id)
);


CREATE TABLE VideoMetadata (
    id INT PRIMARY KEY AUTO_INCREMENT,
    edge_node_id INT NOT NULL,
    encryption_status ENUM('encrypted', 'encrypting', 'unencrypted') NOT NULL DEFAULT 'unencrypted',
    cloud_sync_status ENUM('synced', 'pending', 'failed') NOT NULL DEFAULT 'pending',
    file_path VARCHAR(500) NOT NULL,
    recorded_at DATETIME NOT NULL,
    duration_seconds INT UNSIGNED NULL,
    file_size_bytes BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_video_edge_node FOREIGN KEY (edge_node_id) REFERENCES EdgeNodes(id) ON DELETE CASCADE ON UPDATE CASCADE,
    INDEX idx_video_node (edge_node_id),
    INDEX idx_video_encryption (encryption_status),
    INDEX idx_video_sync (cloud_sync_status),
    INDEX idx_video_recorded (recorded_at)
);

CREATE TABLE AuditLogs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NULL,
    action_taken VARCHAR(50) NOT NULL,
    target_video_id INT NULL,
    ip_address VARCHAR(45) NULL,
    details VARCHAR(500) NULL,
    timestamp DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_audit_user FOREIGN KEY (user_id) REFERENCES Users(id) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_audit_video FOREIGN KEY (target_video_id) REFERENCES VideoMetadata(id) ON DELETE SET NULL ON UPDATE CASCADE,
    INDEX idx_audit_user (user_id),
    INDEX idx_audit_action (action_taken),
    INDEX idx_audit_timestamp (timestamp),
    INDEX idx_audit_video (target_video_id)
);

CREATE TABLE IntrusionAlerts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    node_id INT NOT NULL,
    threat_type VARCHAR(100) NOT NULL,
    severity ENUM('critical', 'medium', 'low') NOT NULL DEFAULT 'low',
    details TEXT NULL,
    detected_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    is_resolved BOOLEAN NOT NULL DEFAULT FALSE,
    resolved_by INT NULL,
    resolved_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_alert_node FOREIGN KEY (node_id) REFERENCES EdgeNodes(id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_alert_resolver FOREIGN KEY (resolved_by) REFERENCES Users(id) ON DELETE SET NULL ON UPDATE CASCADE,
    INDEX idx_alert_node (node_id),
    INDEX idx_alert_severity (severity),
    INDEX idx_alert_resolved (is_resolved),
    INDEX idx_alert_detected (detected_time)
);

INSERT INTO Users (username, password_hash, role, mfa_enabled, otp_code, otp_expires_at, is_verified, full_name, email, created_at) VALUES
('admin', '$2a$12$d.W1vMrH04wH1YzLEI7pYu4CRFdoU5QAuG3fPTbI24bxi/pg1R2oG', 'admin', TRUE, NULL, NULL, 1, 'System Administrator', 'admintest@gmail.com', '2026-01-15 09:00:00'),
('user', '$2a$12$o2Npzl0.Fs9i4r8IKWlq5OMS/K44HMxBNZnKbi/79UWToZY/wOkP2', 'user', FALSE, NULL, NULL, 1, 'John Customer', 'usertest@gmail.com', '2026-03-20 14:30:00');

INSERT INTO EdgeNodes (assigned_user_id, node_name, location, ip_address, status, stream_url, last_heartbeat) VALUES
(NULL, 'Edge-Node-Alpha', 'Main Entrance', '192.168.1.101', 'online', 'rtsp://192.168.1.101/stream1', NOW()),
(NULL, 'Edge-Node-Beta', 'Server Room', '192.168.1.102', 'online', 'rtsp://192.168.1.102/stream1', NOW()),
(2, 'Edge-Node-Gamma', 'Parking Lot B', '192.168.1.103', 'warning', 'rtsp://192.168.1.103/stream1', DATE_SUB(NOW(), INTERVAL 5 MINUTE)),
(NULL, 'Edge-Node-Delta', 'Warehouse', '192.168.1.104', 'online', 'rtsp://192.168.1.104/stream1', NOW()),
(2, 'Edge-Node-Epsilon', 'Lobby Area', '192.168.1.105', 'offline', 'rtsp://192.168.1.105/stream1', DATE_SUB(NOW(), INTERVAL 1 HOUR)),
(NULL, 'Edge-Node-Zeta', 'Back Gate', '192.168.1.106', 'online', 'rtsp://192.168.1.106/stream1', NOW());

INSERT INTO VideoMetadata (edge_node_id, encryption_status, cloud_sync_status, file_path, recorded_at, duration_seconds, file_size_bytes) VALUES
(1, 'encrypted', 'synced', '/recordings/cam01/2026-05-09_14-30.mp4', '2026-05-09 14:30:00', 3600, 2576980378),
(2, 'encrypted', 'synced', '/recordings/cam02/2026-05-09_14-30.mp4', '2026-05-09 14:30:00', 3600, 1932735283),
(3, 'encrypted', 'pending', '/recordings/cam03/2026-05-09_13-00.mp4', '2026-05-09 13:00:00', 1800, 966367642),
(4, 'encrypted', 'synced', '/recordings/cam04/2026-05-09_12-00.mp4', '2026-05-09 12:00:00', 7200, 4402341478),
(1, 'encrypting', 'pending', '/recordings/cam01/2026-05-09_15-30.mp4', '2026-05-09 15:30:00', 2700, 1717986918),
(6, 'encrypted', 'synced', '/recordings/cam06/2026-05-09_10-00.mp4', '2026-05-09 10:00:00', 5400, 3435973837),
(2, 'encrypted', 'failed', '/recordings/cam02/2026-05-09_08-00.mp4', '2026-05-09 08:00:00', 3600, 2147483648),
(5, 'encrypted', 'synced', '/recordings/cam05/2026-05-08_22-00.mp4', '2026-05-08 22:00:00', 7200, 4080218931);

INSERT INTO IntrusionAlerts (node_id, threat_type, severity, details, detected_time, is_resolved) VALUES
(2, 'Brute Force Attack', 'critical', 'Multiple failed login attempts detected from IP 10.0.5.23. 47 attempts in 2 minutes.', DATE_SUB(NOW(), INTERVAL 3 MINUTE), FALSE),
(1, 'Port Scanning', 'medium', 'Sequential port scan detected from external IP 203.0.113.45 targeting ports 1-1024.', DATE_SUB(NOW(), INTERVAL 15 MINUTE), FALSE),
(3, 'Anomalous Traffic', 'low', 'Unusual bandwidth spike detected on Edge-Node-Gamma. Traffic normalized after investigation.', DATE_SUB(NOW(), INTERVAL 1 HOUR), TRUE),
(5, 'MitM Attempt', 'critical', 'ARP spoofing detected on network segment. Potential Man-in-the-Middle attack targeting surveillance data.', DATE_SUB(NOW(), INTERVAL 2 HOUR), FALSE),
(4, 'Unauthorized Access', 'medium', 'Unauthorized API access attempt blocked. Invalid authentication token from internal network.', DATE_SUB(NOW(), INTERVAL 4 HOUR), TRUE),
(6, 'Data Exfiltration', 'critical', 'Large data transfer detected to unknown external IP. Possible video data exfiltration attempt.', DATE_SUB(NOW(), INTERVAL 10 MINUTE), FALSE);

INSERT INTO AuditLogs (user_id, action_taken, target_video_id, ip_address, details, timestamp) VALUES
(1, 'LOGIN', NULL, '192.168.1.50', 'User logged in successfully', DATE_SUB(NOW(), INTERVAL 2 MINUTE)),
(1, 'VIEW_FEED', NULL, '192.168.1.50', 'Accessed live feed monitoring - all cameras', DATE_SUB(NOW(), INTERVAL 5 MINUTE)),
(2, 'LOGIN', NULL, '10.0.1.15', 'User logged in successfully', DATE_SUB(NOW(), INTERVAL 1 HOUR)),
(2, 'VIEW_FEED', NULL, '10.0.1.15', 'Accessed live feed monitoring - assigned cameras', DATE_SUB(NOW(), INTERVAL 1 HOUR)),
(1, 'RESOLVE_ALERT', NULL, '192.168.1.50', 'Resolved intrusion alert #3: Anomalous Traffic', DATE_SUB(NOW(), INTERVAL 2 HOUR)),
(1, 'CREATE_USER', NULL, '192.168.1.50', 'Created user: sarah (user)', DATE_SUB(NOW(), INTERVAL 30 DAY)),
(1, 'SETTINGS_CHANGE', NULL, '192.168.1.50', 'MFA enabled for user sarah', DATE_SUB(NOW(), INTERVAL 29 DAY)),
(2, 'LOGOUT', NULL, '10.0.1.15', 'User logged out', DATE_SUB(NOW(), INTERVAL 1 HOUR));

CREATE OR REPLACE VIEW vw_active_alerts AS
SELECT ia.id AS alert_id, ia.threat_type, ia.severity, ia.details, ia.detected_time, en.node_name, en.location AS node_location, en.ip_address AS node_ip
FROM IntrusionAlerts ia
JOIN EdgeNodes en ON ia.node_id = en.id
WHERE ia.is_resolved = FALSE
ORDER BY FIELD(ia.severity, 'critical', 'medium', 'low'), ia.detected_time DESC;

CREATE OR REPLACE VIEW vw_video_archive AS
SELECT vm.id AS video_id, CONCAT('REC-', LPAD(vm.id, 4, '0')) AS recording_id, en.node_name, en.location AS camera_location, vm.encryption_status, vm.cloud_sync_status, vm.file_path, vm.recorded_at, SEC_TO_TIME(vm.duration_seconds) AS duration, 
CASE WHEN vm.file_size_bytes >= 1073741824 THEN CONCAT(ROUND(vm.file_size_bytes / 1073741824, 1), ' GB') WHEN vm.file_size_bytes >= 1048576 THEN CONCAT(ROUND(vm.file_size_bytes / 1048576, 1), ' MB') WHEN vm.file_size_bytes >= 1024 THEN CONCAT(ROUND(vm.file_size_bytes / 1024, 1), ' KB') ELSE CONCAT(vm.file_size_bytes, ' B') END AS file_size
FROM VideoMetadata vm
JOIN EdgeNodes en ON vm.edge_node_id = en.id
ORDER BY vm.recorded_at DESC;

CREATE OR REPLACE VIEW vw_user_activity AS
SELECT u.id AS user_id, u.username, u.full_name, u.role, al.action_taken, al.ip_address, al.details, al.timestamp, CASE WHEN al.target_video_id IS NOT NULL THEN CONCAT('VID-', al.target_video_id) ELSE NULL END AS related_video
FROM AuditLogs al
LEFT JOIN Users u ON al.user_id = u.id
ORDER BY al.timestamp DESC;

CREATE OR REPLACE VIEW vw_node_health AS
SELECT en.id AS node_id, en.node_name, en.location, en.ip_address, en.status, en.last_heartbeat, TIMESTAMPDIFF(SECOND, en.last_heartbeat, NOW()) AS seconds_since_heartbeat, COUNT(vm.id) AS total_recordings, SUM(CASE WHEN vm.cloud_sync_status = 'synced' THEN 1 ELSE 0 END) AS synced_recordings, SUM(CASE WHEN vm.cloud_sync_status = 'pending' THEN 1 ELSE 0 END) AS pending_recordings, SUM(CASE WHEN vm.cloud_sync_status = 'failed' THEN 1 ELSE 0 END) AS failed_recordings
FROM EdgeNodes en
LEFT JOIN VideoMetadata vm ON en.id = vm.edge_node_id
GROUP BY en.id, en.node_name, en.location, en.ip_address, en.status, en.last_heartbeat
ORDER BY en.node_name;

DELIMITER //
CREATE PROCEDURE sp_log_action(IN p_user_id INT, IN p_action VARCHAR(50), IN p_details VARCHAR(500), IN p_video_id INT, IN p_ip_address VARCHAR(45))
BEGIN
    INSERT INTO AuditLogs (user_id, action_taken, details, target_video_id, ip_address) VALUES (p_user_id, p_action, p_details, p_video_id, p_ip_address);
END //
DELIMITER ;

DELIMITER //
CREATE PROCEDURE sp_resolve_alert(IN p_alert_id INT, IN p_admin_id INT, IN p_ip_address VARCHAR(45))
BEGIN
    UPDATE IntrusionAlerts SET is_resolved = TRUE, resolved_by = p_admin_id, resolved_at = NOW() WHERE id = p_alert_id;
    CALL sp_log_action(p_admin_id, 'RESOLVE_ALERT', CONCAT('Resolved intrusion alert #', p_alert_id), NULL, p_ip_address);
END //
DELIMITER ;

DELIMITER //
CREATE PROCEDURE sp_node_heartbeat(IN p_node_id INT)
BEGIN
    UPDATE EdgeNodes SET last_heartbeat = NOW(), status = IF(status = 'offline', 'online', status) WHERE id = p_node_id;
END //
DELIMITER ;

DELIMITER //
CREATE PROCEDURE sp_verify_otp(IN p_user_id INT, IN p_otp_code VARCHAR(10), OUT p_success TINYINT)
BEGIN
    DECLARE v_count INT DEFAULT 0;
    SELECT COUNT(*) INTO v_count FROM Users WHERE id = p_user_id AND otp_code = p_otp_code AND otp_expires_at > NOW() AND is_verified = 0;
    IF v_count > 0 THEN
        UPDATE Users SET is_verified = 1, otp_code = NULL, otp_expires_at = NULL WHERE id = p_user_id;
        SET p_success = 1;
    ELSE
        SET p_success = 0;
    END IF;
END //
DELIMITER ;

DELIMITER //
CREATE PROCEDURE sp_update_last_login(IN p_user_id INT, IN p_ip_address VARCHAR(45))
BEGIN
    UPDATE Users SET last_login = NOW() WHERE id = p_user_id;
    CALL sp_log_action(p_user_id, 'LOGIN', 'User logged in successfully', NULL, p_ip_address);
END //
DELIMITER ;

-- Later this was run in user table
--UPDATE Users 
--SET 
  --  password_hash = '$2a$12$MuzsXYP1NJfBTzmzJuDIG.sPGqWueGNiXa8Vi9hxl22uiSxwCuM9i', 
    --mfa_enabled = 1 
--WHERE username IN ('admin', 'user');


