DBMS_CODE.sql

CREATE 	DATABASE MALWARE_DETECTION_SYSTEM;
USE MALWARE_DETECTION_SYSTEM;
CREATE TABLE users (
    userId BIGINT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(255) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE
);
CREATE TABLE files (
    fileId BIGINT AUTO_INCREMENT PRIMARY KEY,
    fileName VARCHAR(255) NOT NULL,
    filePath VARCHAR(500) NOT NULL,
    fileHash VARCHAR(128) NOT NULL,
    fileSize BIGINT NOT NULL,
    uploadTimestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    isMalicious BOOLEAN DEFAULT FALSE,
    userId BIGINT,
    FOREIGN KEY (userId) REFERENCES users(userId)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE scans (
    scan_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    file_id BIGINT NOT NULL,
    scan_start_time DATETIME NOT NULL,
    scan_end_time DATETIME NOT NULL,
    FOREIGN KEY (file_id) REFERENCES files(fileId) ON DELETE CASCADE On update cascade
);

-- Before Normalization:
-- CREATE TABLE static_scans (
--     staticid BIGINT AUTO_INCREMENT PRIMARY KEY,
--     fileId BIGINT,
--     scanId BIGINT,
--     fileType VARCHAR(255),
--     hashMD5 VARCHAR(255),
--     hashSHA256 VARCHAR(255),
--     digitalSignature TEXT,
--     entropyScore DECIMAL(10, 2),
--     packerUsed VARCHAR(255),
--     compressionMethod VARCHAR(255),
--     sectionEntropy JSON,  -- Storing the section entropy as JSON (e.g., {"".text": 6.5, ".data": 3.2})
--     suspiciousSections JSON,  -- Storing suspicious sections as a JSON array
--     importedLibraries JSON,  -- Storing imported libraries as a JSON array
--     exportedFunctions JSON,  -- Storing exported functions as a JSON array
--     syscallsUsed JSON,  -- Storing system calls as a JSON array
--     assemblyInstructions JSON,  -- Storing assembly instructions as a JSON array
--     asciiStrings JSON,  -- Storing extracted readable strings as a JSON array
--     suspiciousStrings JSON,  -- Storing suspicious strings (IPs, URLs, etc.) as a JSON array
--     FOREIGN KEY (fileId) REFERENCES files(fileId) ON DELETE CASCADE,
--     FOREIGN KEY (scanId) REFERENCES scans(scan_id) ON DELETE CASCADE
-- );

CREATE TABLE static_scans (
    staticid BIGINT AUTO_INCREMENT PRIMARY KEY,
    fileId BIGINT,
    scanId BIGINT,
    fileType VARCHAR(255),
    hashMD5 VARCHAR(255),
    hashSHA256 VARCHAR(255),
    digitalSignature TEXT,
    entropyScore DECIMAL(10, 2),
    packerUsed VARCHAR(255),
    compressionMethod VARCHAR(255),
    FOREIGN KEY (fileId) REFERENCES files(fileId) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (scanId) REFERENCES scans(scan_id) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE section_entropy (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    staticid BIGINT,
    section_name VARCHAR(255),
    entropy_score DECIMAL(10, 2),
    FOREIGN KEY (staticid) REFERENCES static_scans(staticid) ON DELETE CASCADE
);
CREATE TABLE suspicious_sections (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    staticid BIGINT,
    section_name VARCHAR(255),
    FOREIGN KEY (staticid) REFERENCES static_scans(staticid) ON DELETE CASCADE
);
CREATE TABLE imported_libraries (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    staticid BIGINT,
    library_name VARCHAR(255),
    FOREIGN KEY (staticid) REFERENCES static_scans(staticid) ON DELETE CASCADE
);
CREATE TABLE exported_functions (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    staticid BIGINT,
    function_name VARCHAR(255),
    FOREIGN KEY (staticid) REFERENCES static_scans(staticid) ON DELETE CASCADE
);
CREATE TABLE syscalls_used (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    staticid BIGINT,
    syscall_name VARCHAR(255),
    FOREIGN KEY (staticid) REFERENCES static_scans(staticid) ON DELETE CASCADE
);
CREATE TABLE assembly_instructions (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    staticid BIGINT,
    instruction TEXT,
    FOREIGN KEY (staticid) REFERENCES static_scans(staticid) ON DELETE CASCADE
);
CREATE TABLE ascii_strings (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    staticid BIGINT,
    string_value TEXT,
    FOREIGN KEY (staticid) REFERENCES static_scans(staticid) ON DELETE CASCADE
);
CREATE TABLE suspicious_strings (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    staticid BIGINT,
    string_value TEXT,
    FOREIGN KEY (staticid) REFERENCES static_scans(staticid) ON DELETE CASCADE
);



CREATE TABLE dynamic_scan (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    scan_id BIGINT,
    sandbox_id BIGINT,
    anti_debugging_detected BOOLEAN,
    anti_vm_detected BOOLEAN,
    FOREIGN KEY (scan_id) REFERENCES scans(scan_id),
    FOREIGN KEY (sandbox_id) REFERENCES sandbox(sandbox_id)
);
CREATE TABLE processes_spawned (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    dynamic_scan_id BIGINT,
    process_name TEXT,
    FOREIGN KEY (dynamic_scan_id) REFERENCES dynamic_scan(id)
);
CREATE TABLE network_activity (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    dynamic_scan_id BIGINT,
    activity TEXT,
    FOREIGN KEY (dynamic_scan_id) REFERENCES dynamic_scan(id)
);
CREATE TABLE files_modified (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    dynamic_scan_id BIGINT,
    file_path TEXT,
    FOREIGN KEY (dynamic_scan_id) REFERENCES dynamic_scan(id)
);
CREATE TABLE dll_loaded (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    dynamic_scan_id BIGINT,
    dll_name TEXT,
    FOREIGN KEY (dynamic_scan_id) REFERENCES dynamic_scan(id)
);
CREATE TABLE command_executed (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    dynamic_scan_id BIGINT,
    command TEXT,
    FOREIGN KEY (dynamic_scan_id) REFERENCES dynamic_scan(id)
);
CREATE TABLE mutexes_created (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    dynamic_scan_id BIGINT,
    mutex_name TEXT,
    FOREIGN KEY (dynamic_scan_id) REFERENCES dynamic_scan(id)
);
CREATE TABLE persistence_mechanisms (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    dynamic_scan_id BIGINT,
    mechanism TEXT,
    FOREIGN KEY (dynamic_scan_id) REFERENCES dynamic_scan(id)
);
CREATE TABLE system_calls (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    dynamic_scan_id BIGINT,
    call_name TEXT,
    FOREIGN KEY (dynamic_scan_id) REFERENCES dynamic_scan(id)
);
CREATE TABLE registry_changes (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    dynamic_scan_id BIGINT,
    registry_key TEXT,
    registry_value TEXT,
    FOREIGN KEY (dynamic_scan_id) REFERENCES dynamic_scan(id)
);


CREATE TABLE sandbox (
    sandbox_id BIGINT PRIMARY KEY,
    os_version VARCHAR(255),
    network_config VARCHAR(255),
    hardware_emulation BOOLEAN,
    execution_time_limit BIGINT,
    cpu_architecture VARCHAR(100),
    memory_allocated INT
);
CREATE TABLE sandbox_tools (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    sandbox_id BIGINT,
    tool_name VARCHAR(255),
    FOREIGN KEY (sandbox_id) REFERENCES sandbox(sandbox_id) ON DELETE CASCADE
);



CREATE TABLE malware (
    signature_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    signature_hash VARCHAR(255) UNIQUE,
    malware_name VARCHAR(255),
    severity_level VARCHAR(50),
    malware_type VARCHAR(100),
    first_seen DATE,
    last_seen DATE
);
ALTER TABLE malware_variants DROP FOREIGN KEY malware_variants_ibfk_1; -- Replace 'fk_malware_variants_signature_id' with the actual name of the foreign key constraint.
ALTER TABLE malware MODIFY signature_id BIGINT AUTO_INCREMENT;
ALTER TABLE malware_variants ADD CONSTRAINT fk_malware_variants_signature_id FOREIGN KEY (signature_id) REFERENCES malware(signature_id) ON DELETE CASCADE;

CREATE TABLE malware_variants (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    signature_id BIGINT,
    variant_name VARCHAR(255),
    FOREIGN KEY (signature_id) REFERENCES malware(signature_id) ON DELETE CASCADE
);
SHOW CREATE TABLE malware_variants;
Show create table scans;

CREATE TABLE ai_analysis (
    ai_id BIGINT PRIMARY KEY,
    scan_id BIGINT,
    model_version VARCHAR(255),
    severity_score DOUBLE,
    anomaly_type VARCHAR(255),
    confidence_level DOUBLE,
    explainability_report TEXT,
    FOREIGN KEY (scan_id) REFERENCES scans(scan_id)
);
CREATE TABLE ai_features (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    ai_id BIGINT,
    feature_name VARCHAR(255),
    FOREIGN KEY (ai_id) REFERENCES ai_analysis(ai_id) ON DELETE CASCADE
);


CREATE TABLE report (
    report_id BIGINT PRIMARY KEY,
    scan_id BIGINT,
    ai_id BIGINT,
    file_id BIGINT,
    user_id BIGINT,
    generated_at TIMESTAMP,
    summary TEXT,
    detailed_findings TEXT,
    FOREIGN KEY (scan_id) REFERENCES scans(scan_id),
    FOREIGN KEY (ai_id) REFERENCES ai_analysis(ai_id),
    FOREIGN KEY (file_id) REFERENCES files(fileId),
    FOREIGN KEY (user_id) REFERENCES users(userId)
);
CREATE TABLE report_actions (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    report_id BIGINT,
    action TEXT,
    FOREIGN KEY (report_id) REFERENCES report(report_id) ON DELETE CASCADE
);

INSERT INTO users (userId, username, password, email) VALUES
('1', 'pankhuri18', 'Password', 'pankhu.var@gmail.com'),
('2', 'pritika11', 'Password11', 'pritika@gmail.com'),
('3', 'maria', 'MariaPass', 'reeti05@gmail.com'),
('4', 'p0orv', 'Patrick17', 'poorv.17@gmail.com'),
('5', 'reuel', 'REUELpass12', 'reuel@gmail.com'),
('6', 'sonik17', 'Password17', 'sonik34@gmail.com'),
('7', 'ananya13', 'Password', 'ananya.p@gmail.com'),
('8', 'Omayr15', 'omayr15$', 'omayr.yunus.btech2023@sitpune.edu.in');

INSERT INTO files (fileId, fileName, filePath, fileHash, fileSize, uploadTimestamp, isMalicious, userId) 
VALUES 
('3', 'TestFile.zip', 'C:\\Users\\pankh\\Documents\\TestFiles\\TestFile.zip', 'cb29a7a456b84f99a7e3d8c40dfada9e1c8b7c6e83f01d98c7e9e0a5474bcf77', '102400', '2025-04-15 11:55:12', '0', '2'),
('4', 'MalwareSample.exe', 'C:\\Users\\pankh\\Documents\\MalwareSamples\\MalwareSample.exe', 'a248f8d578e2d89b4b054aa786fb7c45e7f343a31cf6367a79b5a86a88cfef55', '734980', '2025-04-15 12:00:25', '1', '3'),
('5', 'ExampleFile.pdf', 'C:\\Users\\pankh\\Documents\\PDFs\\ExampleFile.pdf', 'b3b4a67caa946cc8d28a2a05682367f02c9bbbf35ed847f47d62fc67b43d8e34', '204800', '2025-04-15 12:05:30', '0', '4'),
('6', 'Setup.exe', 'C:\\Users\\pankh\\Downloads\\SetupFiles\\Setup.exe', 'e9fa2b0c8c37b98d9b81b7411e429bb85d2d4b8b60693bc97159e65e9c0bde9a', '512000', '2025-04-15 12:08:45', '0', '5'),
('7', 'VirusSample.bat', 'C:\\Users\\pankh\\Documents\\BatchFiles\\VirusSample.bat', 'fa102e2bb255f33be0c92e5da37251b16a9bcf679e10f4ee2e3d8f3136a1bcf8', '11075', '2025-04-15 12:15:09', '1', '6');

INSERT INTO scans (file_id, scan_start_time, scan_end_time) 
VALUES 
(3, '2025-04-03 09:30:00', '2025-04-03 10:00:00'),
(4, '2025-04-04 14:00:00', '2025-04-04 14:40:00'),
(5, '2025-04-05 11:20:00', '2025-04-05 11:50:00'),
(6, '2025-04-06 08:10:00', '2025-04-06 08:40:00'),
(7, '2025-04-07 15:25:00', '2025-04-07 15:55:00');

INSERT INTO static_scans (fileId, scanId, fileType, hashMD5, hashSHA256, digitalSignature, entropyScore, packerUsed, compressionMethod) 
VALUES 
(1, 1, 'exe', 'd41d8cd98f00b204e9800998ecf8427e', 'e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855', 'Unknown', 7.89, 'UPX', 'ZIP'),
(2, 2, 'dll', '098f6bcd4621d373cade4e832627b4f6', 'b41b5173bffb15b97735d47c8ec5a0b17c032e36e09a4828b10b6d535c3de11d', 'Signed', 5.67, 'None', 'RAR'),
(3, 3, 'exe', '3c92e8baf9d7a1f1e9aeb0d6187451c9', 'aad02b72de6259d249b27f165981a7efb2c672ee207b973a71c5a2305ca9c648', 'Signed', 9.15, 'UPX', 'None'),
(4, 4, 'sys', 'f6e7a5c8d3b91f2362a089b45b9a1234', 'cb6f56b71cb3ea123f2e2d3e0173649087b1a1c8ed6b9d1bb8e88a9b1a39d22f', 'None', 6.43, 'None', 'None'),
(5, 5, 'exe', '1234567890abcdef1234567890abcdef', '74c0d97f23a1b0a129bb20f65b64c4d041f7d8d18c0748704c4e97371ee46c2f', 'Unknown', 8.32, 'UPX', 'LZMA'),
(6, 6, 'dll', 'a1d0d183bfabe9bda0f8e3d015db91b3', 'e209f1225d961e02f82320f4d06a4cc0191b77f74395cc9f57a256d53f39f6ff', 'None', 7.56, 'PECompact', 'ZIP'),
(7, 7, 'exe', '0cc175b9c0f1b6a831c399e269772661', 'e5a72f5de8eabf16e2e92987069e6cfc649e8b516a4b3480003d007ffdf04b49', 'Signed', 4.12, 'UPX', 'LZMA');

select * from static_scans;

INSERT INTO section_entropy (staticid, section_name, entropy_score) 
VALUES 
(1, 'text', 7.92),
(2, 'data', 5.45),
(20, 'text', 8.12),
(21, 'data', 6.34),
(22, 'text', 7.91),
(23, 'text', 8.42),
(24, 'data', 6.98),
(25, 'text', 7.88),
(26, 'data', 8.42);

INSERT INTO suspicious_sections (staticid, section_name) 
VALUES 
(1, 'text'),
(2, 'data'),
(20, 'text'),
(21, 'data'),
(22, 'text'),
(23, 'data'),
(24, 'text'),
(25, 'data'),
(26, 'text');

INSERT INTO imported_libraries (staticid, library_name) 
VALUES 
(1, 'kernel32.dll'),
(2, 'user32.dll'),
(20, 'advapi32.dll'),
(21, 'msvcrt.dll'),
(22, 'shell32.dll'),
(23, 'gdi32.dll'),
(24, 'ole32.dll'),
(25, 'ws2_32.dll'),
(26, 'iphlpapi.dll');

INSERT INTO exported_functions (staticid, function_name) 
VALUES 
(1, 'CreateFile'),
(2, 'GetMessage'),
(20, 'RegOpenKeyEx'),
(21, 'printf'),
(22, 'MessageBox'),
(23, 'CreateWindow'),
(24, 'ConnectSocket'),
(25, 'SendData'),
(26, 'GetAdaptersInfo');

INSERT INTO syscalls_used (staticid, syscall_name) 
VALUES 
(1, 'NtCreateFile'),
(2, 'NtReadFile'),
(20, 'NtOpenKey'),
(21, 'NtWriteFile'),
(22, 'NtDisplayString'),
(23, 'NtCreateWindow'),
(24, 'NtConnectSocket'),
(25, 'NtSendData'),
(26, 'NtGetAdaptersInfo');

INSERT INTO assembly_instructions (staticid, instruction) 
VALUES 
(1, 'mov eax, 0x123456'),
(2, 'cmp eax, ebx'),
(20, 'jmp 0xdeadbeef'),
(21, 'push eax'),
(22, 'pop ebx'),
(23, 'call 0x0badc0de'),
(24, 'mov [eax], ebx'),
(25, 'sub esp, 0x10'),
(26, 'add eax, 0x5');

INSERT INTO ascii_strings (staticid, string_value) 
VALUES 
(1, 'HelloWorld'),
(2, 'TestString'),
(20, 'MalwareDetected'),
(21, 'Keylogger'),
(22, 'FileSystemError'),
(23, 'NetworkConnected'),
(24, 'PhishingAttempt'),
(25, 'RootkitFound'),
(26, 'FilelessMalware');

INSERT INTO suspicious_strings (staticid, string_value) 
VALUES 
(1, 'cmd.exe'),
(2, 'powershell'),
(20, 'explorer.exe'),
(21, 'keylog'),
(22, 'bypass'),
(23, 'rootkit'),
(24, 'dataexfil'),
(25, 'backdoor'),
(26, 'ransomware');


INSERT INTO dynamic_scan (scan_id, sandbox_id, anti_debugging_detected, anti_vm_detected) 
VALUES 
(1, 1, TRUE, FALSE),
(2, 2, FALSE, TRUE),
(3, 3, TRUE, TRUE),
(4, 4, FALSE, FALSE),
(5, 5, TRUE, FALSE),
(6, 6, FALSE, TRUE),
(7, 7, TRUE, TRUE);

INSERT INTO processes_spawned (dynamic_scan_id, process_name) 
VALUES 
(8, 'process1.exe'),
(9, 'process2.exe'),
(10, 'process3.exe'),
(11, 'process4.exe'),
(12, 'process5.exe'),
(13, 'process6.exe'),
(14, 'process7.exe');

INSERT INTO network_activity (dynamic_scan_id, activity) 
VALUES 
(8, 'Incoming TCP connection'),
(9, 'Outgoing UDP connection'),
(10, 'ICMP request'),
(11, 'HTTP request to malicious site'),
(12, 'Outgoing HTTP request'),
(13, 'SSH connection attempt'),
(14, 'Malicious HTTP response');

INSERT INTO files_modified (dynamic_scan_id, file_path) 
VALUES 
(8, 'C:\\Temp\\file1.txt'),
(9, 'C:\\Temp\\file2.txt'),
(10, 'C:\\Temp\\file3.txt'),
(11, 'C:\\Windows\\System32\\file4.sys'),
(12, 'C:\\Users\\Admin\\Documents\\file5.doc'),
(13, 'C:\\Users\\Admin\\Downloads\\file6.pdf'),
(14, 'C:\\Program Files\\file7.exe');

INSERT INTO dll_loaded (dynamic_scan_id, dll_name) 
VALUES 
(8, 'example.dll'),
(9, 'debugger.dll'),
(10, 'netstat.dll'),
(11, 'crypto.dll'),
(12, 'system.dll'),
(13, 'network.dll'),
(14, 'kernel32.dll');

INSERT INTO command_executed (dynamic_scan_id, command) 
VALUES 
(8, 'del C:\\Temp\\file1.txt'),
(9, 'netstat -an'),
(10, 'tasklist'),
(11, 'ipconfig'),
(12, 'dir C:\\Users\\Admin'),
(13, 'mkdir C:\\NewFolder'),
(14, 'rm -rf /home/user/test');

INSERT INTO mutexes_created (dynamic_scan_id, mutex_name) 
VALUES 
(8, 'mutex1'),
(9, 'mutex2'),
(10, 'mutex3'),
(11, 'mutex4'),
(12, 'mutex5'),
(13, 'mutex6'),
(14, 'mutex7');

INSERT INTO persistence_mechanisms (dynamic_scan_id, mechanism) 
VALUES 
(8, 'Startup registry key'),
(9, 'Scheduled task'),
(10, 'Service registration'),
(11, 'DLL hijacking'),
(12, 'Windows Task Scheduler'),
(13, 'WMI persistence'),
(14, 'Scheduled task creation');

INSERT INTO system_calls (dynamic_scan_id, call_name) 
VALUES 
(8, 'CreateFile'),
(9, 'WriteFile'),
(10, 'ReadFile'),
(11, 'VirtualAlloc'),
(12, 'CreateThread'),
(13, 'OpenProcess'),
(14, 'CloseHandle');

INSERT INTO registry_changes (dynamic_scan_id, registry_key, registry_value) 
VALUES 
(8, 'HKEY_CURRENT_USER\\Software\\Microsoft\\Windows\\CurrentVersion\\Run', 'MaliciousApp'),
(9, 'HKEY_LOCAL_MACHINE\\SOFTWARE\\Microsoft\\Windows\\CurrentVersion\\Uninstall', 'MalwareProgram'),
(10, 'HKEY_CLASSES_ROOT\\exefile\\shell\\open\\command', 'C:\\Malware.exe'),
(11, 'HKEY_CURRENT_USER\\Software\\Malware', 'Persistent'),
(12, 'HKEY_LOCAL_MACHINE\\Software\\Microsoft\\Windows\\CurrentVersion\\RunOnce', 'TrojanApp'),
(13, 'HKEY_CLASSES_ROOT\\exefile\\shell\\open\\command', 'C:\\Virus.exe'),
(14, 'HKEY_CURRENT_USER\\Software\\MyApp', 'AppData');

INSERT INTO sandbox (sandbox_id, os_version, network_config, hardware_emulation, execution_time_limit, cpu_architecture, memory_allocated) 
VALUES 
(1, 'Windows 10', '192.168.1.0/24', TRUE, 3600, 'x64', 8192),
(2, 'Ubuntu 20.04', '192.168.0.0/24', FALSE, 1800, 'x86', 4096),
(3, 'Windows 7', '10.0.0.0/24', TRUE, 7200, 'x64', 16384),
(4, 'MacOS 12', '10.10.0.0/24', FALSE, 1200, 'x64', 8192),
(5, 'Linux 5.4', '192.168.100.0/24', TRUE, 5400, 'x64', 8192),
(6, 'Windows 8', '10.1.1.0/24', TRUE, 1500, 'x86', 1024),
(7, 'Ubuntu 18.04', '192.168.2.0/24', FALSE, 3600, 'x64', 8192),
(8, 'Debian 10', '10.10.10.0/24', TRUE, 3000, 'x64', 4096),
(9, 'CentOS 7', '172.16.0.0/24', TRUE, 4800, 'x64', 4096),
(10, 'Windows Server 2016', '192.168.50.0/24', FALSE, 5400, 'x64', 16384);

INSERT INTO sandbox_tools (sandbox_id, tool_name) 
VALUES 
(1, 'Wireshark'),
(2, 'Metasploit'),
(3, 'Volatility'),
(4, 'IDA Pro'),
(5, 'Ghidra'),
(6, 'Fiddler'),
(7, 'Burp Suite'),
(8, 'Cuckoo Sandbox'),
(9, 'OllyDbg'),
(10, 'Radare2');


INSERT INTO malware (signature_id, signature_hash, malware_name, severity_level, malware_type, first_seen, last_seen) 
VALUES 
(1, 'a1b2c3d4e5f67890123456789abcdef', 'TrojanDownloader', 'High', 'Downloader', '2025-03-01', '2025-04-10'),
(2, 'b1c2d3e4f567890123456789abcdef0', 'RansomwareX', 'Critical', 'Ransomware', '2025-01-15', '2025-04-01'),
(3, 'c1d2e3f4g567890234567890abcdef12', 'Spybot', 'Medium', 'Spyware', '2024-11-01', '2025-03-25'),
(4, 'd1e2f3g4h67890134567890abcdef34', 'Keylogger123', 'High', 'Keylogger', '2025-02-20', '2025-03-15'),
(5, 'e1f2g3h4i7890124567890abcdef56', 'BackdoorBot', 'Critical', 'Backdoor', '2025-01-05', '2025-04-10'),
(6, 'f1g2h3i4j890123567890abcdef78', 'AdwareX', 'Low', 'Adware', '2024-12-10', '2025-02-05'),
(7, 'g1h2i3j4k901234678901abcdef90', 'WormBot', 'High', 'Worm', '2024-10-15', '2025-03-01'),
(8, 'h1i2j3k4l012345789012abcdefab', 'TrojanSpy', 'Medium', 'Trojan', '2024-11-05', '2025-03-30'),
(9, 'i1j2k3l4m123456890123abcdefcd', 'RootkitX', 'Critical', 'Rootkit', '2025-03-05', '2025-04-12'),
(10, 'j1k2l3m4n2345679012345abcdefef', 'PhishingTool', 'Low', 'Phishing', '2024-12-25', '2025-03-10');

INSERT INTO malware_variants (signature_id, variant_name) 
VALUES 
(1, 'TrojanDownloaderV2'),
(2, 'RansomwareX_v2'),
(3, 'SpybotLite'),
(4, 'Keylogger123A'),
(5, 'BackdoorBot_v2'),
(6, 'AdwareXPro'),
(7, 'WormBot_v2'),
(8, 'TrojanSpy_v2'),
(9, 'RootkitX_v2'),
(10, 'PhishingTool_v2');

INSERT INTO ai_analysis (ai_id, scan_id, model_version, severity_score, anomaly_type, confidence_level, explainability_report) 
VALUES 
(1, 1, 'v1.2', 0.95, 'Suspicious network activity', 0.98, 'Detected suspicious network traffic in outbound connections.'),
(2, 2, 'v1.3', 0.92, 'Malicious file modification', 0.97, 'Files were modified at unusual locations and timestamps.'),
(3, 3, 'v2.0', 0.85, 'Unusual system calls', 0.93, 'Several uncommon system calls detected, likely related to malware behavior.'),
(4, 4, 'v1.1', 0.80, 'Registry manipulation', 0.91, 'Suspicious changes in registry keys observed.'),
(5, 5, 'v1.0', 0.98, 'Fileless malware detection', 0.96, 'High confidence in detection of fileless malware based on behavioral analysis.'),
(6, 6, 'v1.2', 0.75, 'File system anomalies', 0.90, 'Detected anomalies in the file system operations related to persistence mechanisms.'),
(7, 7, 'v1.4', 0.88, 'Keylogging activity', 0.94, 'Suspicious keylogging activity detected based on user input patterns.');

INSERT INTO ai_features (ai_id, feature_name) 
VALUES 
(1, 'Network traffic analysis'),
(2, 'File modification detection'),
(3, 'System call analysis'),
(4, 'Registry monitoring'),
(5, 'Fileless malware detection'),
(6, 'File system anomaly detection'),
(7, 'Keylogging detection');

INSERT INTO report (report_id, scan_id, ai_id, file_id, user_id, generated_at, summary, detailed_findings) 
VALUES 
(1, 1, 1, 1, 1, '2025-04-10 12:00:00', 'Suspicious activity detected in network traffic.', 'Detailed analysis of network traffic revealed multiple outgoing connections to known malicious IPs.'),
(2, 2, 2, 2, 2, '2025-04-11 14:30:00', 'File modification detected in system files.', 'Modified system files found in the Windows directory, possibly due to a rootkit.'),
(3, 3, 3, 3, 3, '2025-04-12 16:45:00', 'Unusual system call behavior detected.', 'Multiple system calls outside the norm observed, likely indicating malware activity.'),
(4, 4, 4, 4, 4, '2025-04-13 18:00:00', 'Registry manipulation detected.', 'Registry keys related to malware persistence were modified during the scan.'),
(5, 5, 5, 5, 5, '2025-04-14 20:15:00', 'Fileless malware detected.', 'Behavioral analysis detected indicators of fileless malware execution.'),
(6, 6, 6, 6, 6, '2025-04-15 22:30:00', 'File system anomalies detected.', 'Anomalous file system operations were logged, indicating potential persistence mechanisms.'),
(7, 7, 7, 7, 7, '2025-04-16 11:00:00', 'Keylogging activity detected.', 'Patterns suggestive of keylogging activity were detected during the scan.');

INSERT INTO report_actions (report_id, action) 
VALUES 
(1, 'Quarantine suspicious file'),
(2, 'Perform full system scan'),
(3, 'Terminate suspicious process'),
(4, 'Revert registry changes'),
(5, 'Block fileless malware execution'),
(6, 'Isolate infected system'),
(7, 'Disable keylogging activity');

SELECT * FROM USERS;
SELECT * FROM FILES;
SELECT * FROM SCANS;
SELECT * FROM static_scans;
SELECT * FROM SECTION_ENTROPY;
SELECT * FROM SUSPICIOUS_SECTIONS;
SELECT * FROM IMPORTED_LIBRARIES;
SELECT * FROM EXPORTED_FUNCTIONS;
SELECT * FROM SYSCALLS_USED;
SELECT * FROM ASSEMBLY_INSTRUCTIONS;
SELECT * FROM ASCII_STRINGS;
SELECT * FROM SUSPICIOUS_STRINGS;
select * from dynamic_scan;
SELECT * FROM processes_spawned;
SELECT * FROM network_activity;
SELECT * FROM files_modified;
SELECT * FROM dll_loaded;
SELECT * FROM command_executed;
SELECT * FROM mutexes_created;
SELECT * FROM persistence_mechanisms;
SELECT * FROM system_calls;
SELECT * FROM registry_changes;
SELECT * FROM sandbox;
SELECT * FROM sandbox_tools;
SELECT * FROM malware;
SELECT * FROM malware_variants;
SELECT * FROM ai_analysis;
SELECT * FROM ai_features;
SELECT * FROM report;
SELECT * FROM report_actions;

DELIMITER //
CREATE PROCEDURE AddMalwareSignatureWithFlagging (
    IN signature_id BIGINT,
    IN signature_hash VARCHAR(255),
    IN malware_name VARCHAR(255),
    IN severity_level VARCHAR(50),
    IN malware_type VARCHAR(100),
    IN first_seen DATE,
    IN last_seen DATE
)
BEGIN
    -- Insert the new malware signature
    INSERT INTO malware (signature_id, signature_hash, malware_name, severity_level, malware_type, first_seen, last_seen)
    VALUES (signature_id, signature_hash, malware_name, severity_level, malware_type, first_seen, last_seen);

    -- Flag matching files as malicious
    UPDATE files
    SET isMalicious = TRUE 
    WHERE fileHash = signature_hash;

    -- Output flagged file details
    SELECT fileId, fileName, isMalicious
    FROM files
    WHERE fileHash = signature_hash;
END //
DELIMITER ;

CALL AddMalwareSignatureWithFlagging(15, 'a248f8d578e2d89b4b054aa786fb7c45e7f343a31cf6367a79b5a86a88cfef55', 'WormBot', 'High', 'Worm', '2024-10-15', '2025-03-01');


DELIMITER //
CREATE PROCEDURE RetrieveCompleteScanReport(IN scan_id_input BIGINT)
BEGIN
    SELECT s.file_id, s.scan_id, s.scan_start_time, s.scan_end_time,
           ss.entropyScore AS static_details_entropy,
           ds.anti_debugging_detected AS dynamic_behavior_anti_debugging,
           r.summary AS report_summary
    FROM scans s
    LEFT JOIN static_scans ss ON s.scan_id = ss.scanId
    LEFT JOIN dynamic_scan ds ON s.scan_id = ds.scan_id
    LEFT JOIN report r ON s.scan_id = r.scan_id
    WHERE s.scan_id = scan_id_input;
END //
DELIMITER ;

CALL RetrieveCompleteScanReport(5);

DELIMITER //
CREATE PROCEDURE DeleteScanById(IN sid BIGINT)
BEGIN
    DELETE FROM dynamic_scan WHERE scan_id = sid;
    DELETE FROM static_scans WHERE scanId = sid;
    DELETE FROM report WHERE scan_id = sid;
    DELETE FROM scans WHERE scan_id = sid;
END //
DELIMITER ;

CALL DeleteScanById(8);

DELIMITER //
CREATE FUNCTION GetScanDuration(scanId BIGINT)
RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE startT DATETIME;
    DECLARE endT DATETIME;
    SELECT scan_start_time, scan_end_time INTO startT, endT FROM scans WHERE scan_id = scanId;
    RETURN TIMESTAMPDIFF(SECOND, startT, endT);
END //
DELIMITER ;
SELECT scan_id, GetScanDuration(5) AS Scan_Duration_In_Seconds FROM Scans WHERE scan_id=5;


DELIMITER //
CREATE FUNCTION EntropyRiskLevel(entropy DECIMAL(10,2))
RETURNS VARCHAR(10)
DETERMINISTIC
BEGIN
    IF entropy < 4 THEN
        RETURN 'Low';
    ELSEIF entropy < 7 THEN
        RETURN 'Medium';
    ELSE
        RETURN 'High';
    END IF;
END //
DELIMITER ;

SELECT f.fileId, f.fileName, f.fileSize, s.entropyScore, EntropyRiskLevel(s.entropyScore) 
FROM files f JOIN static_scans s ON f.fileId=s.fileId;


DELIMITER //
CREATE FUNCTION calculate_risk_score(p_scan_id BIGINT) RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE v_score INT DEFAULT 0;
    DECLARE v_dynamic_scan_id BIGINT;
    DECLARE v_static_id BIGINT;
    DECLARE v_file_type VARCHAR(255);
    DECLARE v_digital_signature TEXT;
    DECLARE v_network_count INT DEFAULT 0;
    DECLARE v_registry_count INT DEFAULT 0;
    DECLARE v_persistence_count INT DEFAULT 0;
    DECLARE v_permissions_count INT DEFAULT 0;
    -- Get static scan id
    SELECT staticid INTO v_static_id
    FROM static_scans
    WHERE scanId = p_scan_id
    LIMIT 1;
    -- Get file type and digital signature
    IF v_static_id IS NOT NULL THEN
        SELECT fileType, digitalSignature INTO v_file_type, v_digital_signature
        FROM static_scans
        WHERE staticid = v_static_id;
    END IF;
    -- Get dynamic scan id
    SELECT id INTO v_dynamic_scan_id
    FROM dynamic_scan
    WHERE scan_id = p_scan_id
    LIMIT 1;
    -- If no dynamic scan, static scan scoring
    IF v_dynamic_scan_id IS NULL THEN
        IF v_file_type = 'EXE' THEN
            SET v_score = v_score + 20;
        ELSEIF v_file_type = 'APK' THEN
            SET v_score = v_score + 15;
        ELSEIF v_file_type = 'DLL' THEN
            SET v_score = v_score + 10;
        END IF;
        -- No digital signature = suspicious
        IF v_digital_signature='Not Signed' THEN
            SET v_score = v_score + 15;
        END IF;
        -- APK-specific: permission count
        IF v_file_type = 'APK' THEN
            SELECT COUNT(*) INTO v_permissions_count
            FROM permissions_requested pr
            JOIN apk_static_scan a ON pr.apk_scan_id = a.scan_id
            WHERE a.scan_id = p_scan_id;
            IF v_permissions_count > 5 THEN
                SET v_score = v_score + 15;
            ELSEIF v_permissions_count > 3 THEN
                SET v_score = v_score + 10;
            END IF;
        END IF;
    ELSE
        -- Dynamic scan score
        SELECT 
            (CASE WHEN anti_debugging_detected THEN 20 ELSE 0 END) +
            (CASE WHEN anti_vm_detected THEN 20 ELSE 0 END)
        INTO v_score
        FROM dynamic_scan
        WHERE id = v_dynamic_scan_id;
        -- Network activity
        SELECT COUNT(*) INTO v_network_count
        FROM network_activity
        WHERE dynamic_scan_id = v_dynamic_scan_id;
        IF v_network_count > 3 THEN
            SET v_score = v_score + 15;
        ELSEIF v_network_count > 0 THEN
            SET v_score = v_score + 10;
        END IF;
        -- Registry changes
        SELECT COUNT(*) INTO v_registry_count
        FROM registry_changes
        WHERE dynamic_scan_id = v_dynamic_scan_id;
        IF v_registry_count > 5 THEN
            SET v_score = v_score + 25;
        ELSEIF v_registry_count > 0 THEN
            SET v_score = v_score + 15;
        END IF;
        -- Persistence mechanisms
        SELECT COUNT(*) INTO v_persistence_count
        FROM persistence_mechanisms
        WHERE dynamic_scan_id = v_dynamic_scan_id;
        IF v_persistence_count > 0 THEN
            SET v_score = v_score + 30;
        END IF;
    END IF;
    -- Cap score at 100
    IF v_score > 100 THEN
        SET v_score = 100;
    END IF;
    RETURN v_score;
END //
DELIMITER ;
SELECT scan_id, calculate_risk_score(5) AS MalwareRiskScore FROM Scans WHERE scan_id=5;


SELECT u.username, COUNT(f.fileId) AS total_files
FROM users u
LEFT JOIN files f ON u.userId = f.userId
GROUP BY u.userId;

SELECT f.fileName, u.username, a.severity_score, a.confidence_level
FROM files f
JOIN users u ON f.userId = u.userId
JOIN scans s ON s.file_id = f.fileId
JOIN ai_analysis a ON a.scan_id = s.scan_id
WHERE f.isMalicious = TRUE;

SELECT f.fileName, ss.string_value
FROM files f
JOIN static_scans ssc ON f.fileId = ssc.fileId
JOIN suspicious_strings ss ON ss.staticid = ssc.staticid;

SELECT f.fileName, ps.process_name, ce.command
FROM files f
JOIN scans s ON f.fileId = s.file_id
JOIN dynamic_scan ds ON ds.scan_id = s.scan_id
LEFT JOIN processes_spawned ps ON ps.dynamic_scan_id = ds.id
LEFT JOIN command_executed ce ON ce.dynamic_scan_id = ds.id;

SELECT r.report_id, u.username, r.summary, ra.action
FROM report r
JOIN users u ON r.user_id = u.userId
JOIN report_actions ra ON ra.report_id = r.report_id;

SELECT f.fileName, f.fileHash, s.os_version, ds.id AS dynamic_scan_id
FROM files f
JOIN scans sc ON f.fileId = sc.file_id
JOIN dynamic_scan ds ON sc.scan_id = ds.scan_id
JOIN sandbox s ON ds.sandbox_id = s.sandbox_id
WHERE ds.anti_debugging_detected = TRUE OR ds.anti_vm_detected = TRUE;

SELECT sandbox_id, os_version, cpu_architecture, memory_allocated
FROM sandbox
ORDER BY memory_allocated DESC;

DELIMITER $$
CREATE TRIGGER before_malware_insert
BEFORE INSERT ON malware
FOR EACH ROW
BEGIN
    -- Check if the signature_hash already exists
    IF EXISTS (SELECT 1 FROM malware WHERE signature_hash = NEW.signature_hash) THEN
        -- Delete the existing record with the same signature_hash
        DELETE FROM malware WHERE signature_hash = NEW.signature_hash;
    END IF;
END $$
DELIMITER ;
DROP TRIGGER before_malware_insert;

DELIMITER //
CREATE TRIGGER auto_flag_high_entropy
AFTER INSERT ON static_scans
FOR EACH ROW
BEGIN
    IF NEW.entropyScore > 7.5 THEN
        UPDATE files SET isMalicious = TRUE WHERE fileId = NEW.fileId;
    END IF;
END //
DELIMITER ;

DELIMITER //
CREATE TABLE malware_history (
    history_id INT AUTO_INCREMENT PRIMARY KEY,
    signature_id BIGINT,
    signature_hash VARCHAR(255) UNIQUE,
    log_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (signature_id) REFERENCES malware(signature_id)
);

CREATE TRIGGER log_malware_signature_insert
AFTER INSERT ON malware
FOR EACH ROW
BEGIN
   INSERT INTO malware_history (malware_id, signature_id)
    VALUES (
        NEW.signature_id,
        NEW.signature_hash
    );
END //
DELIMITER ;

DELIMITER //
CREATE TRIGGER prevent_duplicate_file_hash
BEFORE INSERT ON files
FOR EACH ROW
BEGIN
    IF NEW.fileHash IS NOT NULL AND NEW.fileHash NOT REGEXP '^[0-9a-fA-F]{64}$' THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid SHA256 hash format';
    END IF;

    IF EXISTS (SELECT 1 FROM files WHERE fileHash = NEW.fileHash) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'File with same hash already exists!';
    END IF;
END //
DELIMITER ;