<?php
$host = 'localhost';
$db = 'digupdog_FEED';
$user = 'digupdog_FEEDadmin';
$pass = 'Raimundinho1';
$charset = 'utf8';  // Utilizando utf8 para garantir compatibilidade com índices e tamanho de colunas

$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);

    // Lista de colunas a serem adicionadas
    $columns_to_add = [
        'domain' => "VARCHAR(255)",
        'website_source' => "VARCHAR(255)",
        'channel_id' => "INT",
        'file_id' => "INT",
        'location' => "VARCHAR(255)",
        'language' => "ENUM('en', 'es', 'fr', 'de', 'zh', 'ar', 'pt')",
        'duration_time' => "INT",
        'topic_tags' => "VARCHAR(255)",
        'engagement_level' => "ENUM('low', 'medium', 'high')",
        'content_format' => "ENUM('text', 'image', 'video', 'audio', 'live_stream', 'file')",
        'ad_categories' => "VARCHAR(255)",
        'expiry_date' => "DATETIME",
        'last_updated' => "DATETIME",
        'reaction_type' => "ENUM('like', 'dislike', 'love', 'haha', 'wow', 'sad', 'angry')",
        'publication_status' => "ENUM('draft', 'published', 'archived')",
        'popularity_score' => "DECIMAL(5,2) DEFAULT 0.00",
        'device_type' => "ENUM('desktop', 'mobile', 'tablet')",
        'privacy_settings' => "ENUM('public', 'private', 'friends_only')",
        'referrer_source' => "VARCHAR(255)",
        'registration_date' => "DATETIME",
        'subscription_expiry_date' => "DATETIME",
        'feed_type' => "ENUM('RSS', 'Atom', 'JSON')",
        'language_code' => "CHAR(2)",
        'meta_tags' => "TEXT",
        'access_level' => "ENUM('public', 'restricted', 'paid')",
        'quality_score' => "DECIMAL(4,2) DEFAULT 0.00",
        'social_interaction_count' => "INT UNSIGNED",
        'accessibility_score' => "DECIMAL(3,2) DEFAULT 0.00",
        'age_rating' => "ENUM('G', 'PG', 'PG-13', 'R', 'NC-17')",
        'upload_date' => "DATETIME",
        'peak_time' => "TINYINT",
        'traffic_source' => "ENUM('organic', 'paid', 'direct')",
        'analytics_date' => "DATETIME",
        'data_source' => "VARCHAR(255)",
        'location_tags' => "VARCHAR(255)",
        'interaction_type' => "ENUM('click', 'hover', 'scroll')",
        'relevance_score' => "DECIMAL(4,2) DEFAULT 0.00",
        'edit_history' => "TEXT",
        'video_resolution' => "ENUM('480p', '720p', '1080p', '4K')",
        'file_size' => "BIGINT",
        'content_medium' => "ENUM('digital', 'physical')",
        'update_indicator' => "BOOLEAN DEFAULT FALSE",
        'ab_test_id' => "INT",
        'interaction_score' => "DECIMAL(5,2) DEFAULT 0.00",
        'peak_engagement_time' => "TINYINT",
        'referrer_url' => "VARCHAR(512)",
        'organic_search' => "BOOLEAN DEFAULT FALSE",
        'impressions' => "INT UNSIGNED DEFAULT 0",
        'click_through_rate' => "DECIMAL(4,2) DEFAULT 0.00",
        'bounce_rate' => "DECIMAL(4,2) DEFAULT 0.00",
        'average_time_on_page' => "INT UNSIGNED DEFAULT 0",
        'pages_per_session' => "INT UNSIGNED DEFAULT 0",
        'user_retention_rate' => "DECIMAL(4,2) DEFAULT 0.00",
        'churn_rate' => "DECIMAL(4,2) DEFAULT 0.00",
        'return_on_investment' => "DECIMAL(5,2) DEFAULT 0.00",
        'customer_lifetime_value' => "DECIMAL(8,2) DEFAULT 0.00",
        'cost_per_acquisition' => "DECIMAL(8,2) DEFAULT 0.00",
        'ad_spend' => "DECIMAL(8,2) DEFAULT 0.00",
        'ad_revenue' => "DECIMAL(8,2) DEFAULT 0.00",
        'ad_conversion_rate' => "DECIMAL(4,2) DEFAULT 0.00",
        'ad_cost_per_click' => "DECIMAL(4,2) DEFAULT 0.00",
        'ad_cost_per_thousand_impressions' => "DECIMAL(4,2) DEFAULT 0.00",
        'ad_revenue_per_thousand_impressions' => "DECIMAL(4,2) DEFAULT 0.00",
        'ad_placement' => "ENUM('top', 'middle', 'bottom', 'sidebar')",
        'ad_frequency_cap' => "INT UNSIGNED DEFAULT 0",
        'ad_viewability_rate' => "DECIMAL(4,2) DEFAULT 0.00",
        'ad_block_rate' => "DECIMAL(4,2) DEFAULT 0.00",
        'ad_targeting_accuracy' => "DECIMAL(4,2) DEFAULT 0.00",
        'ad_recall_rate' => "DECIMAL(4,2) DEFAULT 0.00",
        'ad_engagement_rate' => "DECIMAL(4,2) DEFAULT 0.00",
        'ad_brand_lift' => "DECIMAL(4,2) DEFAULT 0.00",
        'ad_purchase_intent' => "DECIMAL(4,2) DEFAULT 0.00",
        'ad_awareness' => "DECIMAL(4,2) DEFAULT 0.00",
        'ad_sentiment' => "ENUM('positive', 'neutral', 'negative')",
        'ad_preference' => "ENUM('personalized', 'non-personalized')",
        'ad_frequency' => "INT UNSIGNED DEFAULT 0"
    ];

    // Verifica a existência de cada coluna e a adiciona se necessário
    foreach ($columns_to_add as $column => $definition) {
        $result = $pdo->query("SHOW COLUMNS FROM links LIKE '$column'")->fetch(PDO::FETCH_ASSOC);
        if (!$result) {
            $sql = "ALTER TABLE links ADD COLUMN $column $definition";
            $pdo->exec($sql);
            echo "Coluna '$column' adicionada com sucesso.<br>";
        } else {
            echo "Coluna '$column' já existe, não foi adicionada.<br>";
        }
    }

    echo "Todas as verificações e adições de colunas foram concluídas!";
} catch (PDOException $e) {
    echo 'Erro ao conectar ao banco de dados: ' . $e->getMessage();
}
?>
