<?php
$host = 'localhost';
$db = 'digupdog_FEED';
$user = 'digupdog_FEEDadmin';
$pass = 'Raimundinho1';
$charset = 'utf8';  // Alterado para utf8 para reduzir o tamanho dos índices

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];

try {
    $pdo = new PDO($dsn, $user, $pass, $options);

    // SQL para criar todas as tabelas com todas as funcionalidades
    $sql = "
    CREATE TABLE IF NOT EXISTS social_network_features (
        network_id INT AUTO_INCREMENT PRIMARY KEY,
        network_name VARCHAR(255) NOT NULL,
        media_types_supported SET('text', 'image', 'video', 'audio', 'live_stream', 'file') NOT NULL,
        privacy_settings ENUM('public', 'friends', 'private', 'groups', 'custom') DEFAULT 'public',
        engagement_methods SET('likes', 'comments', 'shares', 'reactions', 'polls', 'stories', 'status_updates', 'direct_messages', 'mentions') NOT NULL,
        monetization_methods SET('ads', 'subscriptions', 'in-app purchases', 'sponsored content', 'crowdfunding', 'affiliate marketing') DEFAULT 'ads',
        community_features SET('groups', 'pages', 'channels', 'subreddits', 'communities', 'events', 'forums') DEFAULT 'groups',
        content_discovery_features SET('hashtags', 'trending topics', 'search', 'explore', 'algorithmic feed', 'chronological feed', 'recommendations') DEFAULT 'hashtags',
        messaging_features SET('text', 'voice', 'video', 'encryption', 'self-destructing messages', 'stickers', 'gifs', 'file sharing') DEFAULT 'text',
        content_duration ENUM('permanent', 'temporary', 'user-defined', 'auto-delete after viewing') DEFAULT 'permanent',
        advertisement_types SET('display ads', 'video ads', 'sponsored posts', 'native ads', 'influencer marketing') DEFAULT 'display ads',
        file_types_supported SET('pdf', 'doc', 'xls', 'ppt', 'mp4', 'mp3', 'jpg', 'png', 'gif') DEFAULT 'jpg',
        user_interaction_features SET('tagging', 'pinning', 'replies', 'quotes', 'shares', 'reactions') DEFAULT 'shares',
        data_security_features SET('two-factor authentication', 'end-to-end encryption', 'data export', 'data deletion', 'privacy settings') DEFAULT 'two-factor authentication',
        verification_status BOOLEAN DEFAULT FALSE,
        subscription_levels SET('free', 'premium', 'business', 'enterprise') DEFAULT 'free',
        content_moderation ENUM('automated', 'manual', 'community-based') DEFAULT 'automated',
        ad_format_preferences SET('banner', 'interstitial', 'native', 'rewarded', 'playable') DEFAULT 'banner',
        user_generated_content BOOLEAN DEFAULT TRUE,
        algorithm_type ENUM('machine learning', 'rule-based', 'hybrid') DEFAULT 'hybrid',
        platform_compatibility SET('web', 'iOS', 'Android', 'Windows', 'MacOS') DEFAULT 'web',
        notification_types SET('push', 'email', 'sms', 'in-app') DEFAULT 'push',
        real_time_interaction BOOLEAN DEFAULT TRUE,
        analytics_tools BOOLEAN DEFAULT TRUE,
        api_access BOOLEAN DEFAULT TRUE,
        developer_tools BOOLEAN DEFAULT TRUE,
        additional_features TEXT,
        INDEX idx_network_name (network_name)
    ) ENGINE=InnoDB CHARSET=utf8;

    CREATE TABLE IF NOT EXISTS links (
        link_id BIGINT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        link_title VARCHAR(255),
        link_description TEXT,
        link_url VARCHAR(512) NOT NULL,
        category VARCHAR(100),
        tags VARCHAR(255),
        click_count INT UNSIGNED DEFAULT 0,
        view_count INT UNSIGNED DEFAULT 0,
        like_count INT UNSIGNED DEFAULT 0,
        dislike_count INT UNSIGNED DEFAULT 0,
        share_count INT UNSIGNED DEFAULT 0,
        comment_count INT UNSIGNED DEFAULT 0,
        contribution_count INT UNSIGNED DEFAULT 0,
        engagement_score DECIMAL(5,2) DEFAULT 0.00,
        follower_count INT UNSIGNED DEFAULT 0,
        following_count INT UNSIGNED DEFAULT 0,
        bookmark_count INT UNSIGNED DEFAULT 0,
        privacy_level ENUM('public', 'friends', 'private') DEFAULT 'public',
        content_type ENUM('user_generated', 'sponsored') DEFAULT 'user_generated',
        report_count INT UNSIGNED DEFAULT 0,
        user_level INT UNSIGNED DEFAULT 1,
        reward_points INT UNSIGNED DEFAULT 0,
        status ENUM('active', 'pending', 'removed', 'flagged') DEFAULT 'active',
        date_shared DATETIME DEFAULT CURRENT_TIMESTAMP,
        last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        author_name VARCHAR(255),
        source_name VARCHAR(255),
        source_url VARCHAR(512),
        link_type ENUM('article', 'video', 'file', 'community') DEFAULT 'article',
        file_type VARCHAR(50),
        is_featured TINYINT(1) DEFAULT 0,
        reported_count INT UNSIGNED DEFAULT 0,
        response_time SMALLINT UNSIGNED DEFAULT 0,
        link_length SMALLINT UNSIGNED DEFAULT 0,
        meta_keywords VARCHAR(512),
        meta_description TEXT,
        og_title VARCHAR(255),
        og_description TEXT,
        og_image VARCHAR(512),
        intro_text TEXT,
        notifications_count INT UNSIGNED DEFAULT 0,
        direct_message_count INT UNSIGNED DEFAULT 0,
        ad_engagement_count INT UNSIGNED DEFAULT 0,
        save_count INT UNSIGNED DEFAULT 0,
        notification_preference ENUM('all', 'mentions', 'none') DEFAULT 'all',
        mute_until DATETIME DEFAULT NULL,
        last_interaction DATETIME DEFAULT CURRENT_TIMESTAMP,
        pinned BOOLEAN DEFAULT FALSE,
        group_id INT DEFAULT NULL,
        event_id INT DEFAULT NULL,
        sponsored BOOLEAN DEFAULT FALSE,
        story_expiry DATETIME DEFAULT NULL,
        slug VARCHAR(255) UNIQUE,
        INDEX idx_user_id (user_id),
        INDEX idx_category (category),
        INDEX idx_link_type (link_type),
        INDEX idx_date_shared (date_shared),
        INDEX idx_status (status),
        INDEX idx_engagement_score (engagement_score),
        FULLTEXT idx_ft_link_title (link_title(100)), -- Limitar o tamanho do índice
        FULLTEXT idx_ft_link_description (link_description(100)), -- Limitar o tamanho do índice
        FULLTEXT idx_ft_tags (tags(100)) -- Limitar o tamanho do índice
    ) ENGINE=MyISAM CHARSET=utf8;  -- Alterado para MyISAM para suportar FULLTEXT

    CREATE TABLE IF NOT EXISTS `groups` (  -- Usando crases para o nome da tabela
        group_id INT AUTO_INCREMENT PRIMARY KEY,
        group_name VARCHAR(255) NOT NULL,
        description TEXT,
        created_by INT NOT NULL,
        date_created DATETIME DEFAULT CURRENT_TIMESTAMP,
        privacy_level ENUM('public', 'private', 'secret') DEFAULT 'public',
        member_count INT UNSIGNED DEFAULT 0,
        INDEX idx_created_by (created_by)
    ) ENGINE=InnoDB CHARSET=utf8;

    CREATE TABLE IF NOT EXISTS events (
        event_id INT AUTO_INCREMENT PRIMARY KEY,
        event_name VARCHAR(255) NOT NULL,
        description TEXT,
        created_by INT NOT NULL,
        date_created DATETIME DEFAULT CURRENT_TIMESTAMP,
        start_time DATETIME NOT NULL,
        end_time DATETIME,
        location VARCHAR(255),
        event_type ENUM('public', 'private') DEFAULT 'public',
        participant_count INT UNSIGNED DEFAULT 0,
        INDEX idx_created_by (created_by),
        INDEX idx_start_time (start_time)
    ) ENGINE=InnoDB CHARSET=utf8;

    CREATE TABLE IF NOT EXISTS followers (
        follower_id INT NOT NULL,
        followed_id INT NOT NULL,
        follow_date DATETIME DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (follower_id, followed_id),
        INDEX idx_follower_id (follower_id),
        INDEX idx_followed_id (followed_id)
    ) ENGINE=InnoDB CHARSET=utf8;

    CREATE TABLE IF NOT EXISTS user_activity_logs (
        log_id BIGINT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        activity_type ENUM('login', 'logout', 'link_click', 'like', 'share', 'comment') NOT NULL,
        activity_time DATETIME DEFAULT CURRENT_TIMESTAMP,
        ip_address VARCHAR(45),
        user_agent VARCHAR(255),
        INDEX idx_user_id (user_id),
        INDEX idx_activity_time (activity_time)
    ) ENGINE=InnoDB CHARSET=utf8;

    CREATE TABLE IF NOT EXISTS reactions (
        reaction_id BIGINT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        link_id BIGINT NOT NULL,
        reaction_type ENUM('like', 'dislike', 'love', 'haha', 'wow', 'sad', 'angry') NOT NULL,
        reaction_time DATETIME DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_user_id (user_id),
        INDEX idx_link_id (link_id)
    ) ENGINE=InnoDB CHARSET=utf8;

    CREATE TABLE IF NOT EXISTS user_preferences (
        preference_id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        preference_key VARCHAR(100),
        preference_value VARCHAR(255),
        INDEX idx_user_id (user_id)
    ) ENGINE=InnoDB CHARSET=utf8;

    CREATE TABLE IF NOT EXISTS user_ad_preferences (
        preference_id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        ad_category VARCHAR(100) NOT NULL,
        preference_level ENUM('low', 'medium', 'high') DEFAULT 'medium',
        last_updated DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        INDEX idx_user_id (user_id),
        INDEX idx_ad_category (ad_category)
    ) ENGINE=InnoDB CHARSET=utf8;

    CREATE TABLE IF NOT EXISTS ad_interactions (
        interaction_id BIGINT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        ad_id INT NOT NULL,
        interaction_type ENUM('view', 'click', 'like', 'share') NOT NULL,
        interaction_time DATETIME DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_user_id (user_id),
        INDEX idx_ad_id (ad_id),
        INDEX idx_interaction_type (interaction_type)
    ) ENGINE=InnoDB CHARSET=utf8;

    CREATE TABLE IF NOT EXISTS user_browsing_history (
        history_id BIGINT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        page_url VARCHAR(512) NOT NULL,
        search_terms VARCHAR(255) DEFAULT NULL,
        visit_time DATETIME DEFAULT CURRENT_TIMESTAMP,
        device_type ENUM('desktop', 'mobile', 'tablet') DEFAULT 'desktop',
        browser_info VARCHAR(255),
        INDEX idx_user_id (user_id),
        INDEX idx_visit_time (visit_time)
    ) ENGINE=InnoDB CHARSET=utf8;

    CREATE TABLE IF NOT EXISTS user_segments (
        segment_id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        demographic_info ENUM('age_18_24', 'age_25_34', 'age_35_44', 'age_45_plus', 'male', 'female', 'other', 'income_low', 'income_medium', 'income_high') NOT NULL,
        interest_category VARCHAR(100),
        engagement_score DECIMAL(5,2) DEFAULT 0.00,
        last_calculated DATETIME DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_user_id (user_id),
        INDEX idx_interest_category (interest_category)
    ) ENGINE=InnoDB CHARSET=utf8;

    CREATE TABLE IF NOT EXISTS user_data_consent (
        consent_id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        consent_type ENUM('ad_targeting', 'data_sharing', 'analytics') NOT NULL,
        consent_given BOOLEAN DEFAULT FALSE,
        consent_date DATETIME DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_user_id (user_id),
        INDEX idx_consent_type (consent_type)
    ) ENGINE=InnoDB CHARSET=utf8;

    CREATE TABLE IF NOT EXISTS user_usage_patterns (
        pattern_id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        session_start DATETIME NOT NULL,
        session_end DATETIME,
        pages_viewed INT UNSIGNED DEFAULT 0,
        interactions INT UNSIGNED DEFAULT 0,
        device_type ENUM('desktop', 'mobile', 'tablet') DEFAULT 'desktop',
        INDEX idx_user_id (user_id),
        INDEX idx_session_start (session_start)
    ) ENGINE=InnoDB CHARSET=utf8;

    CREATE TABLE IF NOT EXISTS click_heatmap (
        heatmap_id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        page_url VARCHAR(512) NOT NULL,
        click_position_x INT NOT NULL,
        click_position_y INT NOT NULL,
        click_time DATETIME DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_user_id (user_id),
        INDEX idx_page_url (page_url)
    ) ENGINE=InnoDB CHARSET=utf8;

    CREATE TABLE IF NOT EXISTS user_content_interests (
        interest_id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        content_type ENUM('article', 'video', 'infographic', 'podcast') NOT NULL,
        view_count INT UNSIGNED DEFAULT 0,
        last_viewed DATETIME DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_user_id (user_id),
        INDEX idx_content_type (content_type)
    ) ENGINE=InnoDB CHARSET=utf8;

    CREATE TABLE IF NOT EXISTS user_recommendations (
        recommendation_id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        recommendation_type ENUM('content', 'product', 'ad') NOT NULL,
        recommendation_content VARCHAR(512),
        recommendation_score DECIMAL(5,2) DEFAULT 0.00,
        generated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_user_id (user_id),
        INDEX idx_recommendation_type (recommendation_type)
    ) ENGINE=InnoDB CHARSET=utf8;

    CREATE TABLE IF NOT EXISTS ab_testing_groups (
        experiment_id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        variant ENUM('A', 'B') NOT NULL,
        experiment_start DATETIME DEFAULT CURRENT_TIMESTAMP,
        experiment_end DATETIME,
        result_metric VARCHAR(100),
        INDEX idx_user_id (user_id),
        INDEX idx_variant (variant)
    ) ENGINE=InnoDB CHARSET=utf8;

    CREATE TABLE IF NOT EXISTS ad_feedback (
        feedback_id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        ad_id INT NOT NULL,
        feedback_type ENUM('positive', 'negative', 'neutral') DEFAULT 'neutral',
        feedback_text TEXT,
        feedback_time DATETIME DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_user_id (user_id),
        INDEX idx_ad_id (ad_id)
    ) ENGINE=InnoDB CHARSET=utf8;

    CREATE TABLE IF NOT EXISTS user_pseudonyms (
        pseudonym_id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        pseudonym VARCHAR(255) NOT NULL,
        INDEX idx_user_id (user_id)
    ) ENGINE=InnoDB CHARSET=utf8;

    CREATE TABLE IF NOT EXISTS user_privacy_preferences (
        preference_id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        consent_type ENUM('behavioral_tracking', 'personalized_ads', 'third_party_sharing') NOT NULL,
        consent_given BOOLEAN DEFAULT FALSE,
        consent_date DATETIME DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_user_id (user_id),
        INDEX idx_consent_type (consent_type)
    ) ENGINE=InnoDB CHARSET=utf8;

    CREATE TABLE IF NOT EXISTS archived_user_browsing_history LIKE user_browsing_history;
    ";

    $pdo->exec($sql);
    echo "Todas as tabelas foram criadas com sucesso!";
} catch (PDOException $e) {
    echo 'Erro ao conectar ao banco de dados: ' . $e->getMessage();
}
?>
