-- ============================================
-- Parental Monitor - Database Schema
-- MySQL / MariaDB Compatible
-- Complete with ALL 310 Features
-- ============================================

CREATE DATABASE IF NOT EXISTS parental_monitor;
USE parental_monitor;

-- ============================================
-- Users Table
-- ============================================
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    phone VARCHAR(20) DEFAULT NULL,
    password VARCHAR(255) NOT NULL,
    status ENUM('active', 'inactive', 'suspended') DEFAULT 'active',
    email_verified_at TIMESTAMP NULL DEFAULT NULL,
    two_factor_enabled TINYINT(1) DEFAULT 0,
    two_factor_secret VARCHAR(255) DEFAULT NULL,
    profile_image VARCHAR(500) DEFAULT NULL,
    language VARCHAR(10) DEFAULT 'en',
    timezone VARCHAR(50) DEFAULT 'UTC',
    last_login_at TIMESTAMP NULL DEFAULT NULL,
    last_login_ip VARCHAR(45) DEFAULT NULL,
    failed_login_attempts INT DEFAULT 0,
    locked_until TIMESTAMP NULL DEFAULT NULL,
    password_reset_token VARCHAR(255) DEFAULT NULL,
    password_reset_expires TIMESTAMP NULL DEFAULT NULL,
    referral_code VARCHAR(50) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- Auth Tokens Table
-- ============================================
CREATE TABLE auth_tokens (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    token VARCHAR(500) NOT NULL,
    device_info VARCHAR(255) DEFAULT NULL,
    ip_address VARCHAR(45) DEFAULT NULL,
    user_agent VARCHAR(500) DEFAULT NULL,
    expires_at TIMESTAMP NOT NULL,
    last_used_at TIMESTAMP NULL DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- Login History Table
-- ============================================
CREATE TABLE login_history (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    ip_address VARCHAR(45) NOT NULL,
    user_agent VARCHAR(500) DEFAULT NULL,
    location VARCHAR(255) DEFAULT NULL,
    status ENUM('success', 'failed', 'blocked') NOT NULL,
    failure_reason VARCHAR(255) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_created (user_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- Security Questions Table
-- ============================================
CREATE TABLE security_questions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    question VARCHAR(255) NOT NULL,
    answer_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- Pair Codes Table
-- ============================================
CREATE TABLE pair_codes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    code VARCHAR(6) NOT NULL,
    qr_code_data VARCHAR(500) DEFAULT NULL,
    status ENUM('active', 'used', 'expired') DEFAULT 'active',
    used_by_device_id INT DEFAULT NULL,
    expires_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- Child Devices Table
-- ============================================
CREATE TABLE child_devices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    device_name VARCHAR(100) NOT NULL,
    device_model VARCHAR(100) DEFAULT NULL,
    android_version VARCHAR(20) DEFAULT NULL,
    device_id VARCHAR(255) NOT NULL,
    pair_code_id INT DEFAULT NULL,
    child_name VARCHAR(100) DEFAULT NULL,
    child_age INT DEFAULT NULL,
    child_photo VARCHAR(500) DEFAULT NULL,
    device_group VARCHAR(100) DEFAULT NULL,
    device_label VARCHAR(100) DEFAULT NULL,
    status ENUM('pending', 'active', 'inactive', 'blocked') DEFAULT 'pending',
    monitoring_status ENUM('active', 'partial', 'inactive') DEFAULT 'inactive',
    stealth_mode TINYINT(1) DEFAULT 0,
    kiosk_mode TINYINT(1) DEFAULT 0,
    last_sync_at TIMESTAMP NULL DEFAULT NULL,
    last_ip_address VARCHAR(45) DEFAULT NULL,
    battery_level INT DEFAULT NULL,
    is_online TINYINT(1) DEFAULT 0,
    is_charging TINYINT(1) DEFAULT 0,
    storage_total BIGINT DEFAULT NULL,
    storage_used BIGINT DEFAULT NULL,
    ram_total BIGINT DEFAULT NULL,
    ram_used BIGINT DEFAULT NULL,
    cpu_temperature FLOAT DEFAULT NULL,
    screen_on TINYINT(1) DEFAULT 0,
    wifi_connected TINYINT(1) DEFAULT 0,
    wifi_ssid VARCHAR(100) DEFAULT NULL,
    mobile_data_enabled TINYINT(1) DEFAULT 0,
    bluetooth_enabled TINYINT(1) DEFAULT 0,
    vpn_active TINYINT(1) DEFAULT 0,
    sim_operator VARCHAR(100) DEFAULT NULL,
    sim_serial VARCHAR(100) DEFAULT NULL,
    imei VARCHAR(50) DEFAULT NULL,
    fcm_token VARCHAR(500) DEFAULT NULL,
    app_version VARCHAR(20) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (pair_code_id) REFERENCES pair_codes(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- Device Approval Requests Table
-- ============================================
CREATE TABLE device_approval_requests (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    device_id INT NOT NULL,
    pair_code_id INT NOT NULL,
    device_name VARCHAR(100) NOT NULL,
    device_model VARCHAR(100) DEFAULT NULL,
    android_version VARCHAR(20) DEFAULT NULL,
    status ENUM('pending', 'approved', 'denied') DEFAULT 'pending',
    responded_at TIMESTAMP NULL DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (device_id) REFERENCES child_devices(id) ON DELETE CASCADE,
    FOREIGN KEY (pair_code_id) REFERENCES pair_codes(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- Device Permissions Table
-- ============================================
CREATE TABLE device_permissions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    device_id INT NOT NULL,
    accessibility_service TINYINT(1) DEFAULT 0,
    usage_stats_access TINYINT(1) DEFAULT 0,
    notification_access TINYINT(1) DEFAULT 0,
    device_admin TINYINT(1) DEFAULT 0,
    location_permission TINYINT(1) DEFAULT 0,
    overlay_permission TINYINT(1) DEFAULT 0,
    battery_optimization TINYINT(1) DEFAULT 0,
    background_activity TINYINT(1) DEFAULT 0,
    auto_start TINYINT(1) DEFAULT 0,
    storage_permission TINYINT(1) DEFAULT 0,
    camera_permission TINYINT(1) DEFAULT 0,
    microphone_permission TINYINT(1) DEFAULT 0,
    phone_permission TINYINT(1) DEFAULT 0,
    sms_permission TINYINT(1) DEFAULT 0,
    contacts_permission TINYINT(1) DEFAULT 0,
    total_granted INT DEFAULT 0,
    total_required INT DEFAULT 15,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (device_id) REFERENCES child_devices(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- App Usage Logs Table
-- ============================================
CREATE TABLE app_usage_logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    device_id INT NOT NULL,
    package_name VARCHAR(255) NOT NULL,
    app_name VARCHAR(255) DEFAULT NULL,
    category VARCHAR(100) DEFAULT NULL,
    usage_duration INT DEFAULT 0,
    data_usage_bytes BIGINT DEFAULT 0,
    start_time TIMESTAMP NULL DEFAULT NULL,
    end_time TIMESTAMP NULL DEFAULT NULL,
    date DATE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (device_id) REFERENCES child_devices(id) ON DELETE CASCADE,
    INDEX idx_device_date (device_id, date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- Location Logs Table
-- ============================================
CREATE TABLE location_logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    device_id INT NOT NULL,
    latitude DECIMAL(10, 8) NOT NULL,
    longitude DECIMAL(11, 8) NOT NULL,
    accuracy FLOAT DEFAULT NULL,
    altitude FLOAT DEFAULT NULL,
    speed FLOAT DEFAULT NULL,
    address VARCHAR(500) DEFAULT NULL,
    location_type ENUM('gps', 'network', 'wifi', 'manual') DEFAULT 'gps',
    recorded_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (device_id) REFERENCES child_devices(id) ON DELETE CASCADE,
    INDEX idx_device_recorded (device_id, recorded_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- Notification Logs Table
-- ============================================
CREATE TABLE notification_logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    device_id INT NOT NULL,
    package_name VARCHAR(255) NOT NULL,
    app_name VARCHAR(255) DEFAULT NULL,
    title VARCHAR(500) DEFAULT NULL,
    content TEXT DEFAULT NULL,
    category VARCHAR(100) DEFAULT NULL,
    posted_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (device_id) REFERENCES child_devices(id) ON DELETE CASCADE,
    INDEX idx_device_posted (device_id, posted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- Call Logs Table
-- ============================================
CREATE TABLE call_logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    device_id INT NOT NULL,
    phone_number VARCHAR(20) NOT NULL,
    contact_name VARCHAR(255) DEFAULT NULL,
    call_type ENUM('incoming', 'outgoing', 'missed', 'rejected', 'blocked') NOT NULL,
    duration INT DEFAULT 0,
    is_blocked TINYINT(1) DEFAULT 0,
    recording_path VARCHAR(500) DEFAULT NULL,
    call_time TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (device_id) REFERENCES child_devices(id) ON DELETE CASCADE,
    INDEX idx_device_calltime (device_id, call_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- SMS Logs Table
-- ============================================
CREATE TABLE sms_logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    device_id INT NOT NULL,
    phone_number VARCHAR(20) NOT NULL,
    contact_name VARCHAR(255) DEFAULT NULL,
    message_type ENUM('inbox', 'sent', 'blocked') NOT NULL,
    body TEXT DEFAULT NULL,
    is_blocked TINYINT(1) DEFAULT 0,
    has_keyword_match TINYINT(1) DEFAULT 0,
    matched_keywords TEXT DEFAULT NULL,
    message_time TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (device_id) REFERENCES child_devices(id) ON DELETE CASCADE,
    INDEX idx_device_msgtime (device_id, message_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- Screenshots Table
-- ============================================
CREATE TABLE screenshots (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    device_id INT NOT NULL,
    file_path VARCHAR(500) NOT NULL,
    file_size INT DEFAULT NULL,
    trigger_type ENUM('manual', 'scheduled', 'keyword', 'auto') DEFAULT 'manual',
    captured_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (device_id) REFERENCES child_devices(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- Remote Commands Table
-- ============================================
CREATE TABLE remote_commands (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    device_id INT NOT NULL,
    user_id INT NOT NULL,
    command_type VARCHAR(50) NOT NULL,
    command_data JSON DEFAULT NULL,
    status ENUM('pending', 'sent', 'executed', 'failed', 'cancelled') DEFAULT 'pending',
    priority ENUM('low', 'normal', 'high', 'urgent') DEFAULT 'normal',
    result TEXT DEFAULT NULL,
    sent_at TIMESTAMP NULL DEFAULT NULL,
    executed_at TIMESTAMP NULL DEFAULT NULL,
    expires_at TIMESTAMP NULL DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (device_id) REFERENCES child_devices(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_device_status (device_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- System Logs Table
-- ============================================
CREATE TABLE system_logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id INT DEFAULT NULL,
    device_id INT DEFAULT NULL,
    log_type ENUM('api_error', 'device_event', 'remote_command', 'security', 'login', 'permission_change', 'app_block', 'geofence', 'keyword_alert', 'emergency', 'subscription') NOT NULL,
    severity ENUM('info', 'warning', 'error', 'critical') DEFAULT 'info',
    message TEXT NOT NULL,
    metadata JSON DEFAULT NULL,
    ip_address VARCHAR(45) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_type_created (log_type, created_at),
    INDEX idx_user_created (user_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- Installed Apps Table
-- ============================================
CREATE TABLE installed_apps (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    device_id INT NOT NULL,
    package_name VARCHAR(255) NOT NULL,
    app_name VARCHAR(255) DEFAULT NULL,
    version_name VARCHAR(50) DEFAULT NULL,
    version_code INT DEFAULT NULL,
    category VARCHAR(100) DEFAULT NULL,
    is_system_app TINYINT(1) DEFAULT 0,
    is_blocked TINYINT(1) DEFAULT 0,
    is_sideloaded TINYINT(1) DEFAULT 0,
    data_usage_bytes BIGINT DEFAULT 0,
    install_source VARCHAR(255) DEFAULT NULL,
    installed_at TIMESTAMP NULL DEFAULT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (device_id) REFERENCES child_devices(id) ON DELETE CASCADE,
    UNIQUE KEY uk_device_package (device_id, package_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- Contacts Table
-- ============================================
CREATE TABLE contacts (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    device_id INT NOT NULL,
    contact_name VARCHAR(255) NOT NULL,
    phone_number VARCHAR(20) DEFAULT NULL,
    email VARCHAR(255) DEFAULT NULL,
    is_whitelisted TINYINT(1) DEFAULT 0,
    is_blacklisted TINYINT(1) DEFAULT 0,
    is_emergency TINYINT(1) DEFAULT 0,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (device_id) REFERENCES child_devices(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- Uploads Table
-- ============================================
CREATE TABLE uploads (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id INT DEFAULT NULL,
    device_id INT DEFAULT NULL,
    file_type ENUM('screenshot', 'report', 'log', 'profile_image', 'call_recording', 'audio_recording', 'photo', 'video', 'backup', 'other') NOT NULL,
    file_name VARCHAR(255) NOT NULL,
    file_path VARCHAR(500) NOT NULL,
    file_size INT DEFAULT NULL,
    mime_type VARCHAR(100) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- Geofence Zones Table
-- ============================================
CREATE TABLE geofence_zones (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    device_id INT NOT NULL,
    zone_name VARCHAR(100) NOT NULL,
    zone_type ENUM('home', 'school', 'custom', 'unsafe') DEFAULT 'custom',
    latitude DECIMAL(10, 8) NOT NULL,
    longitude DECIMAL(11, 8) NOT NULL,
    radius INT NOT NULL DEFAULT 200,
    address VARCHAR(500) DEFAULT NULL,
    is_active TINYINT(1) DEFAULT 1,
    alert_on_enter TINYINT(1) DEFAULT 1,
    alert_on_exit TINYINT(1) DEFAULT 1,
    schedule_rules JSON DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (device_id) REFERENCES child_devices(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- Geofence Events Table
-- ============================================
CREATE TABLE geofence_events (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    device_id INT NOT NULL,
    zone_id INT NOT NULL,
    event_type ENUM('enter', 'exit', 'dwell') NOT NULL,
    latitude DECIMAL(10, 8) NOT NULL,
    longitude DECIMAL(11, 8) NOT NULL,
    recorded_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (device_id) REFERENCES child_devices(id) ON DELETE CASCADE,
    FOREIGN KEY (zone_id) REFERENCES geofence_zones(id) ON DELETE CASCADE,
    INDEX idx_device_zone (device_id, zone_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- Alerts Table
-- ============================================
CREATE TABLE alerts (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    device_id INT NOT NULL,
    alert_type VARCHAR(50) NOT NULL,
    alert_category ENUM('security', 'location', 'app', 'content', 'communication', 'device', 'emergency', 'screen_time', 'network') DEFAULT 'device',
    priority ENUM('low', 'medium', 'high', 'critical') DEFAULT 'medium',
    title VARCHAR(255) NOT NULL,
    message TEXT DEFAULT NULL,
    is_read TINYINT(1) DEFAULT 0,
    is_emailed TINYINT(1) DEFAULT 0,
    is_pushed TINYINT(1) DEFAULT 0,
    metadata JSON DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (device_id) REFERENCES child_devices(id) ON DELETE CASCADE,
    INDEX idx_user_read (user_id, is_read),
    INDEX idx_priority (priority, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- App Rules Table
-- ============================================
CREATE TABLE app_rules (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    device_id INT NOT NULL,
    package_name VARCHAR(255) DEFAULT NULL,
    app_category VARCHAR(100) DEFAULT NULL,
    rule_type ENUM('block', 'time_limit', 'schedule', 'whitelist', 'approval_required') NOT NULL,
    is_active TINYINT(1) DEFAULT 1,
    daily_limit_minutes INT DEFAULT NULL,
    schedule_data JSON DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (device_id) REFERENCES child_devices(id) ON DELETE CASCADE,
    INDEX idx_device_package (device_id, package_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- App Install Requests Table
-- ============================================
CREATE TABLE app_install_requests (
    id INT AUTO_INCREMENT PRIMARY KEY,
    device_id INT NOT NULL,
    user_id INT NOT NULL,
    package_name VARCHAR(255) NOT NULL,
    app_name VARCHAR(255) DEFAULT NULL,
    status ENUM('pending', 'approved', 'denied') DEFAULT 'pending',
    responded_at TIMESTAMP NULL DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (device_id) REFERENCES child_devices(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- Screen Time Rules Table
-- ============================================
CREATE TABLE screen_time_rules (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    device_id INT NOT NULL,
    rule_name VARCHAR(100) NOT NULL,
    rule_type ENUM('daily_limit', 'bedtime', 'school_mode', 'homework_mode', 'custom_schedule', 'break_reminder') NOT NULL,
    is_active TINYINT(1) DEFAULT 1,
    daily_limit_minutes INT DEFAULT NULL,
    start_time TIME DEFAULT NULL,
    end_time TIME DEFAULT NULL,
    days_of_week VARCHAR(20) DEFAULT '0123456',
    break_interval_minutes INT DEFAULT NULL,
    break_duration_minutes INT DEFAULT NULL,
    allowed_apps JSON DEFAULT NULL,
    blocked_apps JSON DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (device_id) REFERENCES child_devices(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- Screen Time Logs Table
-- ============================================
CREATE TABLE screen_time_logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    device_id INT NOT NULL,
    total_screen_time_minutes INT DEFAULT 0,
    total_unlocks INT DEFAULT 0,
    first_unlock_at TIMESTAMP NULL DEFAULT NULL,
    last_lock_at TIMESTAMP NULL DEFAULT NULL,
    longest_session_minutes INT DEFAULT 0,
    average_session_minutes INT DEFAULT 0,
    date DATE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (device_id) REFERENCES child_devices(id) ON DELETE CASCADE,
    UNIQUE KEY uk_device_date (device_id, date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- Screen Time Extensions Table
-- ============================================
CREATE TABLE screen_time_extensions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    device_id INT NOT NULL,
    user_id INT NOT NULL,
    requested_minutes INT NOT NULL,
    reason VARCHAR(500) DEFAULT NULL,
    status ENUM('pending', 'approved', 'denied') DEFAULT 'pending',
    responded_at TIMESTAMP NULL DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (device_id) REFERENCES child_devices(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- Browser History Table
-- ============================================
CREATE TABLE browser_history (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    device_id INT NOT NULL,
    url VARCHAR(2000) NOT NULL,
    title VARCHAR(500) DEFAULT NULL,
    domain VARCHAR(255) DEFAULT NULL,
    is_incognito TINYINT(1) DEFAULT 0,
    browser_package VARCHAR(255) DEFAULT NULL,
    duration_seconds INT DEFAULT 0,
    visited_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (device_id) REFERENCES child_devices(id) ON DELETE CASCADE,
    INDEX idx_device_visited (device_id, visited_at),
    INDEX idx_domain (domain)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- Web Filter Rules Table
-- ============================================
CREATE TABLE web_filter_rules (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    device_id INT NOT NULL,
    rule_type ENUM('block_url', 'block_domain', 'block_category', 'whitelist_url', 'whitelist_domain', 'safe_search', 'block_incognito') NOT NULL,
    value VARCHAR(500) NOT NULL,
    category VARCHAR(100) DEFAULT NULL,
    is_active TINYINT(1) DEFAULT 1,
    custom_block_message VARCHAR(500) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (device_id) REFERENCES child_devices(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- Search Query Logs Table
-- ============================================
CREATE TABLE search_query_logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    device_id INT NOT NULL,
    search_engine VARCHAR(100) DEFAULT NULL,
    query TEXT NOT NULL,
    browser_package VARCHAR(255) DEFAULT NULL,
    searched_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (device_id) REFERENCES child_devices(id) ON DELETE CASCADE,
    INDEX idx_device_searched (device_id, searched_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- Download Logs Table
-- ============================================
CREATE TABLE download_logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    device_id INT NOT NULL,
    file_name VARCHAR(500) NOT NULL,
    file_url VARCHAR(2000) DEFAULT NULL,
    file_size BIGINT DEFAULT NULL,
    mime_type VARCHAR(100) DEFAULT NULL,
    source_app VARCHAR(255) DEFAULT NULL,
    downloaded_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (device_id) REFERENCES child_devices(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- Contact Rules Table
-- ============================================
CREATE TABLE contact_rules (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    device_id INT NOT NULL,
    phone_number VARCHAR(20) NOT NULL,
    contact_name VARCHAR(255) DEFAULT NULL,
    rule_type ENUM('whitelist', 'blacklist', 'emergency') NOT NULL,
    block_calls TINYINT(1) DEFAULT 0,
    block_sms TINYINT(1) DEFAULT 0,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (device_id) REFERENCES child_devices(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- Call Restrictions Table
-- ============================================
CREATE TABLE call_restrictions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    device_id INT NOT NULL,
    restriction_type ENUM('block_unknown', 'block_international', 'time_restriction', 'duration_limit', 'block_all_incoming', 'block_all_outgoing') NOT NULL,
    max_duration_minutes INT DEFAULT NULL,
    start_time TIME DEFAULT NULL,
    end_time TIME DEFAULT NULL,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (device_id) REFERENCES child_devices(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- Social Media Logs Table
-- ============================================
CREATE TABLE social_media_logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    device_id INT NOT NULL,
    platform VARCHAR(50) NOT NULL,
    activity_type ENUM('message_sent', 'message_received', 'post', 'comment', 'like', 'share', 'story', 'call', 'video_call', 'friend_added', 'friend_removed', 'group_joined', 'login', 'other') NOT NULL,
    contact_name VARCHAR(255) DEFAULT NULL,
    content TEXT DEFAULT NULL,
    is_group TINYINT(1) DEFAULT 0,
    group_name VARCHAR(255) DEFAULT NULL,
    has_media TINYINT(1) DEFAULT 0,
    media_type VARCHAR(50) DEFAULT NULL,
    recorded_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (device_id) REFERENCES child_devices(id) ON DELETE CASCADE,
    INDEX idx_device_platform (device_id, platform),
    INDEX idx_device_recorded (device_id, recorded_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- Social Media Rules Table
-- ============================================
CREATE TABLE social_media_rules (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    device_id INT NOT NULL,
    platform VARCHAR(50) NOT NULL,
    rule_type ENUM('block', 'time_limit', 'monitor_messages', 'monitor_posts', 'alert_new_friend', 'alert_new_group') NOT NULL,
    daily_limit_minutes INT DEFAULT NULL,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (device_id) REFERENCES child_devices(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- Keyword Rules Table
-- ============================================
CREATE TABLE keyword_rules (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    keyword VARCHAR(255) NOT NULL,
    category ENUM('profanity', 'drugs', 'violence', 'self_harm', 'sexual', 'bullying', 'predator', 'custom') NOT NULL,
    severity ENUM('low', 'medium', 'high', 'critical') DEFAULT 'medium',
    language VARCHAR(10) DEFAULT 'en',
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- Keyword Matches Table
-- ============================================
CREATE TABLE keyword_matches (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    device_id INT NOT NULL,
    keyword_id INT NOT NULL,
    source_type ENUM('sms', 'notification', 'browser', 'social_media', 'search', 'clipboard', 'app_content') NOT NULL,
    source_app VARCHAR(255) DEFAULT NULL,
    matched_text TEXT DEFAULT NULL,
    context TEXT DEFAULT NULL,
    severity ENUM('low', 'medium', 'high', 'critical') DEFAULT 'medium',
    is_reviewed TINYINT(1) DEFAULT 0,
    detected_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (device_id) REFERENCES child_devices(id) ON DELETE CASCADE,
    FOREIGN KEY (keyword_id) REFERENCES keyword_rules(id) ON DELETE CASCADE,
    INDEX idx_device_detected (device_id, detected_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- Notification Preferences Table
-- ============================================
CREATE TABLE notification_preferences (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    alert_type VARCHAR(50) NOT NULL,
    push_enabled TINYINT(1) DEFAULT 1,
    email_enabled TINYINT(1) DEFAULT 0,
    sms_enabled TINYINT(1) DEFAULT 0,
    telegram_enabled TINYINT(1) DEFAULT 0,
    whatsapp_enabled TINYINT(1) DEFAULT 0,
    min_priority ENUM('low', 'medium', 'high', 'critical') DEFAULT 'medium',
    quiet_hours_start TIME DEFAULT NULL,
    quiet_hours_end TIME DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY uk_user_alert (user_id, alert_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- Notification Queue Table
-- ============================================
CREATE TABLE notification_queue (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    device_id INT DEFAULT NULL,
    channel ENUM('push', 'email', 'sms', 'telegram', 'whatsapp') NOT NULL,
    title VARCHAR(255) NOT NULL,
    message TEXT NOT NULL,
    priority ENUM('low', 'medium', 'high', 'critical') DEFAULT 'medium',
    status ENUM('pending', 'sent', 'failed', 'cancelled') DEFAULT 'pending',
    retry_count INT DEFAULT 0,
    sent_at TIMESTAMP NULL DEFAULT NULL,
    error_message TEXT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_status_created (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- Bot Configurations Table
-- ============================================
CREATE TABLE bot_configurations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    bot_type ENUM('telegram', 'whatsapp') NOT NULL,
    bot_token VARCHAR(500) DEFAULT NULL,
    chat_id VARCHAR(100) DEFAULT NULL,
    phone_number VARCHAR(20) DEFAULT NULL,
    is_active TINYINT(1) DEFAULT 1,
    last_verified_at TIMESTAMP NULL DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- Media Files Table
-- ============================================
CREATE TABLE media_files (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    device_id INT NOT NULL,
    file_type ENUM('photo', 'video', 'audio', 'document') NOT NULL,
    file_name VARCHAR(500) NOT NULL,
    file_path VARCHAR(500) DEFAULT NULL,
    file_size BIGINT DEFAULT NULL,
    mime_type VARCHAR(100) DEFAULT NULL,
    source_app VARCHAR(255) DEFAULT NULL,
    is_uploaded TINYINT(1) DEFAULT 0,
    is_flagged TINYINT(1) DEFAULT 0,
    flag_reason VARCHAR(255) DEFAULT NULL,
    thumbnail_path VARCHAR(500) DEFAULT NULL,
    media_date TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (device_id) REFERENCES child_devices(id) ON DELETE CASCADE,
    INDEX idx_device_type (device_id, file_type),
    INDEX idx_device_date (device_id, media_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- Network Logs Table
-- ============================================
CREATE TABLE network_logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    device_id INT NOT NULL,
    connection_type ENUM('wifi', 'mobile_data', 'bluetooth', 'hotspot', 'vpn', 'usb') NOT NULL,
    event_type ENUM('connected', 'disconnected', 'data_usage') NOT NULL,
    network_name VARCHAR(255) DEFAULT NULL,
    ip_address VARCHAR(45) DEFAULT NULL,
    data_sent_bytes BIGINT DEFAULT 0,
    data_received_bytes BIGINT DEFAULT 0,
    recorded_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (device_id) REFERENCES child_devices(id) ON DELETE CASCADE,
    INDEX idx_device_recorded (device_id, recorded_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- Network Rules Table
-- ============================================
CREATE TABLE network_rules (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    device_id INT NOT NULL,
    rule_type ENUM('wifi_whitelist', 'wifi_blacklist', 'block_vpn', 'block_bluetooth', 'block_hotspot', 'data_limit', 'bandwidth_limit') NOT NULL,
    value VARCHAR(255) DEFAULT NULL,
    daily_limit_mb INT DEFAULT NULL,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (device_id) REFERENCES child_devices(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- Data Usage Logs Table
-- ============================================
CREATE TABLE data_usage_logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    device_id INT NOT NULL,
    package_name VARCHAR(255) DEFAULT NULL,
    app_name VARCHAR(255) DEFAULT NULL,
    connection_type ENUM('wifi', 'mobile') NOT NULL,
    bytes_sent BIGINT DEFAULT 0,
    bytes_received BIGINT DEFAULT 0,
    date DATE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (device_id) REFERENCES child_devices(id) ON DELETE CASCADE,
    INDEX idx_device_date (device_id, date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- Emergency Events Table
-- ============================================
CREATE TABLE emergency_events (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    device_id INT NOT NULL,
    user_id INT NOT NULL,
    trigger_type ENUM('sos_button', 'shake', 'keyword', 'auto') NOT NULL,
    latitude DECIMAL(10, 8) DEFAULT NULL,
    longitude DECIMAL(11, 8) DEFAULT NULL,
    address VARCHAR(500) DEFAULT NULL,
    battery_level INT DEFAULT NULL,
    status ENUM('active', 'acknowledged', 'resolved', 'false_alarm') DEFAULT 'active',
    acknowledged_at TIMESTAMP NULL DEFAULT NULL,
    resolved_at TIMESTAMP NULL DEFAULT NULL,
    notes TEXT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (device_id) REFERENCES child_devices(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- Emergency Contacts Table
-- ============================================
CREATE TABLE emergency_contacts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    phone_number VARCHAR(20) NOT NULL,
    email VARCHAR(255) DEFAULT NULL,
    relationship VARCHAR(50) DEFAULT NULL,
    notify_on_sos TINYINT(1) DEFAULT 1,
    is_active TINYINT(1) DEFAULT 1,
    sort_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- Safety Check-Ins Table
-- ============================================
CREATE TABLE safety_checkins (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    device_id INT NOT NULL,
    user_id INT NOT NULL,
    checkin_type ENUM('scheduled', 'requested', 'voluntary') NOT NULL,
    status ENUM('pending', 'checked_in', 'missed', 'late') DEFAULT 'pending',
    due_at TIMESTAMP NOT NULL,
    checked_in_at TIMESTAMP NULL DEFAULT NULL,
    latitude DECIMAL(10, 8) DEFAULT NULL,
    longitude DECIMAL(11, 8) DEFAULT NULL,
    message VARCHAR(500) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (device_id) REFERENCES child_devices(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- Messages Table (Parent-Child)
-- ============================================
CREATE TABLE messages (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    sender_type ENUM('parent', 'child') NOT NULL,
    user_id INT NOT NULL,
    device_id INT NOT NULL,
    message_type ENUM('text', 'voice', 'image', 'announcement') DEFAULT 'text',
    content TEXT NOT NULL,
    media_path VARCHAR(500) DEFAULT NULL,
    is_read TINYINT(1) DEFAULT 0,
    read_at TIMESTAMP NULL DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (device_id) REFERENCES child_devices(id) ON DELETE CASCADE,
    INDEX idx_device_created (device_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- Tasks Table
-- ============================================
CREATE TABLE tasks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    device_id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    description TEXT DEFAULT NULL,
    category ENUM('homework', 'chore', 'exercise', 'reading', 'custom') DEFAULT 'custom',
    priority ENUM('low', 'medium', 'high') DEFAULT 'medium',
    reward_points INT DEFAULT 0,
    status ENUM('pending', 'in_progress', 'completed', 'overdue', 'cancelled') DEFAULT 'pending',
    due_date TIMESTAMP NULL DEFAULT NULL,
    completed_at TIMESTAMP NULL DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (device_id) REFERENCES child_devices(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- Reward Points Table
-- ============================================
CREATE TABLE reward_points (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    device_id INT NOT NULL,
    user_id INT NOT NULL,
    points INT NOT NULL,
    reason VARCHAR(255) NOT NULL,
    task_id INT DEFAULT NULL,
    point_type ENUM('earned', 'redeemed', 'bonus', 'penalty') NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (device_id) REFERENCES child_devices(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- Achievements Table
-- ============================================
CREATE TABLE achievements (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description VARCHAR(500) NOT NULL,
    icon VARCHAR(100) DEFAULT NULL,
    badge_type ENUM('screen_time', 'task', 'streak', 'safety', 'custom') NOT NULL,
    requirement_value INT DEFAULT 0,
    reward_points INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- User Achievements Table
-- ============================================
CREATE TABLE user_achievements (
    id INT AUTO_INCREMENT PRIMARY KEY,
    device_id INT NOT NULL,
    achievement_id INT NOT NULL,
    earned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (device_id) REFERENCES child_devices(id) ON DELETE CASCADE,
    FOREIGN KEY (achievement_id) REFERENCES achievements(id) ON DELETE CASCADE,
    UNIQUE KEY uk_device_achievement (device_id, achievement_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- Family Events Table
-- ============================================
CREATE TABLE family_events (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    description TEXT DEFAULT NULL,
    event_type ENUM('appointment', 'school', 'activity', 'reminder', 'custom') DEFAULT 'custom',
    start_date TIMESTAMP NOT NULL,
    end_date TIMESTAMP NULL DEFAULT NULL,
    is_all_day TINYINT(1) DEFAULT 0,
    is_recurring TINYINT(1) DEFAULT 0,
    recurrence_rule VARCHAR(255) DEFAULT NULL,
    color VARCHAR(7) DEFAULT '#4A90D9',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- Reports Table
-- ============================================
CREATE TABLE reports (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    device_id INT DEFAULT NULL,
    report_type ENUM('daily', 'weekly', 'monthly', 'custom') NOT NULL,
    report_format ENUM('json', 'pdf', 'csv', 'html') DEFAULT 'json',
    title VARCHAR(255) NOT NULL,
    data JSON DEFAULT NULL,
    file_path VARCHAR(500) DEFAULT NULL,
    date_from DATE NOT NULL,
    date_to DATE NOT NULL,
    is_scheduled TINYINT(1) DEFAULT 0,
    status ENUM('generating', 'ready', 'failed', 'expired') DEFAULT 'generating',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- Report Schedules Table
-- ============================================
CREATE TABLE report_schedules (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    device_id INT DEFAULT NULL,
    report_type ENUM('daily', 'weekly', 'monthly') NOT NULL,
    report_format ENUM('json', 'pdf', 'csv') DEFAULT 'pdf',
    delivery_method ENUM('email', 'in_app', 'both') DEFAULT 'both',
    delivery_email VARCHAR(255) DEFAULT NULL,
    send_time TIME DEFAULT '20:00:00',
    send_day_of_week INT DEFAULT NULL,
    send_day_of_month INT DEFAULT NULL,
    is_active TINYINT(1) DEFAULT 1,
    last_sent_at TIMESTAMP NULL DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- Audit Trail Table
-- ============================================
CREATE TABLE audit_trail (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    action VARCHAR(100) NOT NULL,
    entity_type VARCHAR(50) NOT NULL,
    entity_id INT DEFAULT NULL,
    old_value JSON DEFAULT NULL,
    new_value JSON DEFAULT NULL,
    ip_address VARCHAR(45) DEFAULT NULL,
    user_agent VARCHAR(500) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_action (user_id, action),
    INDEX idx_entity (entity_type, entity_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- IP Whitelist Table
-- ============================================
CREATE TABLE ip_whitelist (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    ip_address VARCHAR(45) NOT NULL,
    description VARCHAR(255) DEFAULT NULL,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- Data Retention Policies Table
-- ============================================
CREATE TABLE data_retention_policies (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    data_type VARCHAR(50) NOT NULL,
    retention_days INT NOT NULL DEFAULT 90,
    auto_delete TINYINT(1) DEFAULT 1,
    is_active TINYINT(1) DEFAULT 1,
    last_cleanup_at TIMESTAMP NULL DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- Subscriptions Table
-- ============================================
CREATE TABLE subscriptions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    plan_type ENUM('free', 'basic', 'premium', 'family', 'enterprise') DEFAULT 'free',
    status ENUM('active', 'cancelled', 'expired', 'trial', 'past_due') DEFAULT 'trial',
    max_devices INT DEFAULT 1,
    trial_ends_at TIMESTAMP NULL DEFAULT NULL,
    current_period_start TIMESTAMP NULL DEFAULT NULL,
    current_period_end TIMESTAMP NULL DEFAULT NULL,
    payment_method VARCHAR(50) DEFAULT NULL,
    payment_id VARCHAR(255) DEFAULT NULL,
    amount DECIMAL(10, 2) DEFAULT 0.00,
    currency VARCHAR(3) DEFAULT 'USD',
    auto_renew TINYINT(1) DEFAULT 1,
    cancelled_at TIMESTAMP NULL DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- Subscription Plans Table
-- ============================================
CREATE TABLE subscription_plans (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    plan_type ENUM('free', 'basic', 'premium', 'family', 'enterprise') NOT NULL,
    price_monthly DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    price_yearly DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    max_devices INT DEFAULT 1,
    features JSON NOT NULL,
    is_active TINYINT(1) DEFAULT 1,
    sort_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- Payment History Table
-- ============================================
CREATE TABLE payment_history (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    subscription_id INT DEFAULT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    currency VARCHAR(3) DEFAULT 'USD',
    payment_method VARCHAR(50) NOT NULL,
    transaction_id VARCHAR(255) DEFAULT NULL,
    status ENUM('pending', 'completed', 'failed', 'refunded') DEFAULT 'pending',
    description VARCHAR(500) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (subscription_id) REFERENCES subscriptions(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- Promo Codes Table
-- ============================================
CREATE TABLE promo_codes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(50) NOT NULL UNIQUE,
    discount_type ENUM('percentage', 'fixed') NOT NULL,
    discount_value DECIMAL(10, 2) NOT NULL,
    max_uses INT DEFAULT NULL,
    used_count INT DEFAULT 0,
    valid_from TIMESTAMP NOT NULL,
    valid_until TIMESTAMP NOT NULL,
    applicable_plans JSON DEFAULT NULL,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- Referrals Table
-- ============================================
CREATE TABLE referrals (
    id INT AUTO_INCREMENT PRIMARY KEY,
    referrer_user_id INT NOT NULL,
    referred_user_id INT DEFAULT NULL,
    referral_code VARCHAR(50) NOT NULL UNIQUE,
    status ENUM('pending', 'registered', 'rewarded') DEFAULT 'pending',
    reward_type VARCHAR(50) DEFAULT 'free_month',
    rewarded_at TIMESTAMP NULL DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (referrer_user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- Device Profiles Table
-- ============================================
CREATE TABLE device_profiles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    profile_name VARCHAR(100) NOT NULL,
    profile_type ENUM('school', 'home', 'bedtime', 'weekend', 'custom') NOT NULL,
    description VARCHAR(500) DEFAULT NULL,
    app_rules JSON DEFAULT NULL,
    screen_time_rules JSON DEFAULT NULL,
    web_filter_rules JSON DEFAULT NULL,
    network_rules JSON DEFAULT NULL,
    is_default TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- Device Profile Assignments Table
-- ============================================
CREATE TABLE device_profile_assignments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    device_id INT NOT NULL,
    profile_id INT NOT NULL,
    schedule_type ENUM('always', 'scheduled', 'location_based') DEFAULT 'always',
    schedule_data JSON DEFAULT NULL,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (device_id) REFERENCES child_devices(id) ON DELETE CASCADE,
    FOREIGN KEY (profile_id) REFERENCES device_profiles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- Webhooks Table
-- ============================================
CREATE TABLE webhooks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    url VARCHAR(500) NOT NULL,
    events JSON NOT NULL,
    secret_key VARCHAR(255) DEFAULT NULL,
    is_active TINYINT(1) DEFAULT 1,
    last_triggered_at TIMESTAMP NULL DEFAULT NULL,
    failure_count INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- Webhook Logs Table
-- ============================================
CREATE TABLE webhook_logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    webhook_id INT NOT NULL,
    event_type VARCHAR(50) NOT NULL,
    payload JSON NOT NULL,
    response_code INT DEFAULT NULL,
    response_body TEXT DEFAULT NULL,
    status ENUM('success', 'failed', 'pending') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (webhook_id) REFERENCES webhooks(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- Feedback Table
-- ============================================
CREATE TABLE feedback (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    feedback_type ENUM('bug', 'feature_request', 'complaint', 'praise', 'other') NOT NULL,
    subject VARCHAR(255) NOT NULL,
    message TEXT NOT NULL,
    rating INT DEFAULT NULL,
    status ENUM('new', 'in_progress', 'resolved', 'closed') DEFAULT 'new',
    admin_response TEXT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- Help Articles Table
-- ============================================
CREATE TABLE help_articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category VARCHAR(100) NOT NULL,
    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    sort_order INT DEFAULT 0,
    is_published TINYINT(1) DEFAULT 1,
    views INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- User Settings Table
-- ============================================
CREATE TABLE user_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    setting_key VARCHAR(100) NOT NULL,
    setting_value TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY uk_user_setting (user_id, setting_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- App Categories Table
-- ============================================
CREATE TABLE app_categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description VARCHAR(500) DEFAULT NULL,
    icon VARCHAR(100) DEFAULT NULL,
    is_educational TINYINT(1) DEFAULT 0,
    default_action ENUM('allow', 'block', 'monitor') DEFAULT 'allow',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- Insert Default Data
-- ============================================

INSERT INTO app_categories (name, description, is_educational, default_action) VALUES
('Social Media', 'Social networking and messaging apps', 0, 'monitor'),
('Games', 'Gaming applications', 0, 'monitor'),
('Education', 'Educational and learning apps', 1, 'allow'),
('Entertainment', 'Video, music, and entertainment apps', 0, 'monitor'),
('Communication', 'Phone, messaging, and email apps', 0, 'allow'),
('Productivity', 'Productivity and office apps', 1, 'allow'),
('Photography', 'Camera and photo editing apps', 0, 'allow'),
('News', 'News and magazine apps', 0, 'allow'),
('Shopping', 'Shopping and e-commerce apps', 0, 'monitor'),
('Dating', 'Dating and relationship apps', 0, 'block'),
('Adult', 'Adult content apps', 0, 'block'),
('VPN & Proxy', 'VPN and proxy apps', 0, 'block'),
('Browser', 'Web browser apps', 0, 'monitor'),
('Health & Fitness', 'Health and fitness apps', 0, 'allow'),
('Travel', 'Travel and navigation apps', 0, 'allow'),
('Finance', 'Banking and finance apps', 0, 'monitor'),
('System', 'System and utility apps', 0, 'allow'),
('Other', 'Uncategorized apps', 0, 'allow');

INSERT INTO achievements (name, description, badge_type, requirement_value, reward_points) VALUES
('Screen Time Star', 'Stayed within screen time limit for 7 consecutive days', 'screen_time', 7, 50),
('Homework Hero', 'Completed 10 homework tasks on time', 'task', 10, 100),
('Safety Streak', 'Checked in on time for 14 consecutive days', 'streak', 14, 75),
('Digital Balance', 'Maintained healthy usage pattern for 30 days', 'screen_time', 30, 200),
('Task Master', 'Completed 50 tasks', 'task', 50, 250),
('Super Safe', 'No alerts triggered for 30 consecutive days', 'safety', 30, 150),
('Week Warrior', '7-day streak of completing all tasks', 'streak', 7, 100),
('Month Champion', '30-day streak of meeting all goals', 'streak', 30, 500);

INSERT INTO subscription_plans (name, plan_type, price_monthly, price_yearly, max_devices, features, sort_order) VALUES
('Free', 'free', 0.00, 0.00, 1, '{"location_tracking": true, "app_monitoring": true, "screen_time_basic": true, "alerts_basic": true}', 1),
('Basic', 'basic', 4.99, 49.99, 3, '{"location_tracking": true, "app_monitoring": true, "app_blocking": true, "screen_time": true, "web_filtering": true, "alerts": true, "call_sms_monitoring": true, "daily_reports": true}', 2),
('Premium', 'premium', 9.99, 99.99, 10, '{"location_tracking": true, "app_monitoring": true, "app_blocking": true, "screen_time": true, "web_filtering": true, "alerts": true, "call_sms_monitoring": true, "social_media_monitoring": true, "keyword_monitoring": true, "geofencing": true, "remote_control": true, "daily_reports": true, "weekly_reports": true, "screenshots": true, "call_recording": true}', 3),
('Family', 'family', 14.99, 149.99, 25, '{"all_features": true, "priority_support": true, "advanced_analytics": true, "ai_insights": true, "custom_reports": true}', 4),
('Enterprise', 'enterprise', 29.99, 299.99, 100, '{"all_features": true, "api_access": true, "webhooks": true, "custom_branding": true, "dedicated_support": true, "sla": true}', 5);

INSERT INTO help_articles (category, title, content, sort_order) VALUES
('Getting Started', 'How to set up Parental Monitor', 'Step 1: Create your account.\nStep 2: Generate a pair code.\nStep 3: Install child app.\nStep 4: Enter pair code.\nStep 5: Approve device.\nStep 6: Grant permissions.', 1),
('Getting Started', 'How to pair a device', 'Go to Dashboard > Generate Pair Code. Enter the 6-digit code in the child app within 30 minutes. Then approve from Devices section.', 2),
('App Control', 'How to block an app', 'Go to App Control > select device > find app > click Block. App blocked within next sync cycle.', 3),
('Screen Time', 'How to set screen time limits', 'Go to Screen Time > select device > Add Rule. Set daily limits, bedtime schedules, and custom schedules.', 4),
('Location', 'How to set up geofences', 'Go to Geofencing > Add Zone. Set zone name, center point, and radius. Receive alerts on enter/exit.', 5),
('Safety', 'How to use SOS/Panic Button', 'SOS button available on child app. Sends emergency alert with location to all emergency contacts.', 6),
('Troubleshooting', 'Device shows offline', 'Check internet connection. Disable battery optimization for app. Verify all permissions granted.', 7),
('Troubleshooting', 'Not receiving alerts', 'Check notification preferences in Settings. Enable push notifications. Verify email address.', 8);