<?php
header('Content-Type: text/event-stream');
header('Cache-Control: no-cache');

// Configuração do banco de dados
$host = 'localhost';
$db = 'digupdog_FEED';
$user = 'digupdog_FEEDadmin';
$pass = 'Raimundinho1';
$charset = 'utf8mb4';

// Conexão com o banco de dados
try {
    $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,
    ];
    $pdo = new PDO($dsn, $user, $pass, $options);
} catch (PDOException $e) {
    sendProgress("Erro ao conectar ao banco de dados: " . $e->getMessage(), "error");
    exit;
}

// Função para enviar atualizações de progresso em tempo real
function sendProgress($message, $status = "progress") {
    echo "data: " . json_encode(["status" => $status, "message" => $message]) . "\n\n";
    ob_flush();
    flush();
}

// Função para processar e associar autores com user_id
function processAuthorAndUserId($pdo, $startId, $endId) {
    sendProgress("Iniciando processamento de autores com IDs entre $startId e $endId...");
    try {
        $pdo->beginTransaction();

        $stmt = $pdo->prepare("SELECT DISTINCT author FROM pinfeeds WHERE (user_id IS NULL OR user_id = 0) AND id BETWEEN ? AND ?");
        $stmt->execute([$startId, $endId]);
        $authors = $stmt->fetchAll(PDO::FETCH_COLUMN);
        $count = 0;

        foreach ($authors as $author) {
            if (!empty($author)) {
                $stmt = $pdo->prepare("SELECT ID FROM user_myhashtag WHERE username = ?");
                $stmt->execute([$author]);
                $userId = $stmt->fetchColumn();

                if (!$userId) {
                    $defaultEmail = strtolower($author) . "@digupdog.com";
                    $defaultSenha = password_hash('Raimundinho1', PASSWORD_BCRYPT);
                    $stmt = $pdo->prepare("INSERT INTO user_myhashtag (username, email, senha) VALUES (?, ?, ?)");
                    $stmt->execute([$author, $defaultEmail, $defaultSenha]);
                    $userId = $pdo->lastInsertId();
                    sendProgress("Novo usuário criado: $author com user_id $userId", "success");
                } else {
                    sendProgress("Usuário já existe para: $author com user_id $userId", "info");
                }

                $updateStmt = $pdo->prepare("UPDATE pinfeeds SET user_id = ? WHERE author = ?");
                $updateStmt->execute([$userId, $author]);
                $count++;
            }
        }
        $pdo->commit();
        sendProgress("Total de autores processados: $count", "success");
    } catch (PDOException $e) {
        $pdo->rollBack();
        sendProgress("Erro ao processar autores: " . $e->getMessage(), "error");
    }
}

// Função para atualizar o main_category_id e processar associações de source_website e source_domain_id
function updateMainCategoryAndAssociations($pdo, $startId, $endId) {
    sendProgress("Atualizando main_category_id e associações entre $startId e $endId...");
    try {
        $pdo->beginTransaction();

        $stmt = $pdo->prepare("SELECT DISTINCT source_website FROM pinfeeds WHERE source_website IS NOT NULL AND id BETWEEN ? AND ?");
        $stmt->execute([$startId, $endId]);
        $sourceWebsites = $stmt->fetchAll(PDO::FETCH_COLUMN);
        $count = 0;

        foreach ($sourceWebsites as $sourceWebsite) {
            if (!empty($sourceWebsite)) {
                $stmt = $pdo->prepare("SELECT id FROM feed_data WHERE website_base = ?");
                $stmt->execute([$sourceWebsite]);
                $websiteBaseId = $stmt->fetchColumn();

                if (!$websiteBaseId) {
                    $stmt = $pdo->prepare("INSERT INTO feed_data (website_base, main_category_id, is_active) VALUES (?, 0, 1)");
                    $stmt->execute([$sourceWebsite]);
                    $websiteBaseId = $pdo->lastInsertId();
                    sendProgress("Novo registro criado para source_website: $sourceWebsite com id $websiteBaseId", "success");
                } else {
                    sendProgress("source_website já existe: $sourceWebsite com id $websiteBaseId", "info");
                }

                $updateStmt = $pdo->prepare("UPDATE pinfeeds SET source_domain_id = ? WHERE source_website = ?");
                $updateStmt->execute([$websiteBaseId, $sourceWebsite]);
                $count++;
            }
        }

        $sql = "
        UPDATE pinfeeds
        JOIN feed_data ON pinfeeds.source_website = feed_data.website_base
        SET pinfeeds.main_category_id = IF(feed_data.main_category_id IS NOT NULL, feed_data.main_category_id, 0)
        WHERE (pinfeeds.main_category_id IS NULL OR pinfeeds.main_category_id = 0) AND pinfeeds.id BETWEEN $startId AND $endId";
        $pdo->exec($sql);
        $pdo->commit();
        sendProgress("main_category_id atualizado em $count registros.", "success");
    } catch (PDOException $e) {
        $pdo->rollBack();
        sendProgress("Erro ao atualizar main_category_id e associações: " . $e->getMessage(), "error");
    }
}

// Função para associar categorias de title, description e tags na tabela pinfeeds
function updateCategoryAssociations($pdo, $startId, $endId) {
    sendProgress("Associando categorias para IDs entre $startId e $endId...");
    try {
        $pdo->beginTransaction();

        $stmt = $pdo->prepare("SELECT id, title, description FROM pinfeeds WHERE id BETWEEN ? AND ?");
        $stmt->execute([$startId, $endId]);
        $webInfoList = $stmt->fetchAll(PDO::FETCH_ASSOC);
        $count = 0;

        foreach ($webInfoList as $web_info) {
            $title_cat_id = 0;
            $description_cat_id = 0;
            $tag_cat_id = 0;

            if (!empty($web_info['title'])) {
                $titleQuery = $pdo->prepare("SELECT id FROM content_categories WHERE ? LIKE CONCAT('%', title, '%') COLLATE utf8mb4_unicode_ci LIMIT 1");
                $titleQuery->execute([$web_info['title']]);
                $title_cat_id = $titleQuery->fetchColumn() ?: 0;
            }

            if (!empty($web_info['description'])) {
                $descriptionQuery = $pdo->prepare("SELECT id FROM content_categories WHERE ? LIKE CONCAT('%', description, '%') COLLATE utf8mb4_unicode_ci LIMIT 1");
                $descriptionQuery->execute([$web_info['description']]);
                $description_cat_id = $descriptionQuery->fetchColumn() ?: 0;
            }

            $tags = explode(' ', $web_info['title']);
            foreach ($tags as $tag) {
                $tagQuery = $pdo->prepare("SELECT id FROM content_categories WHERE ? LIKE CONCAT('%', tag, '%') COLLATE utf8mb4_unicode_ci LIMIT 1");
                $tagQuery->execute([$tag]);
                $tag_id = $tagQuery->fetchColumn();
                if ($tag_id) {
                    $tag_cat_id = $tag_id;
                    break;
                }
            }

            $updateStmt = $pdo->prepare("UPDATE pinfeeds SET title_cat_id = ?, description_cat_id = ?, tag_cat_id = ? WHERE id = ?");
            $updateStmt->execute([$title_cat_id, $description_cat_id, $tag_cat_id, $web_info['id']]);
            $count++;
            sendProgress("Atualizado ID: " . $web_info['id'], "info");
        }

        $pdo->commit();
        sendProgress("Total de registros atualizados: $count", "success");
    } catch (PDOException $e) {
        $pdo->rollBack();
        sendProgress("Erro ao associar categorias: " . $e->getMessage(), "error");
    }
}

// Controle para chamada de função
$function = $data['functionToRun'] ?? 'all';
$startId = (int)($data['startId'] ?? 1);
$endId = (int)($data['endId'] ?? 1000);

switch ($function) {
    case 'processAuthorAndUserId':
        processAuthorAndUserId($pdo, $startId, $endId);
        break;
    case 'updateMainCategoryAndAssociations':
        updateMainCategoryAndAssociations($pdo, $startId, $endId);
        break;
    case 'updateCategoryAssociations':
        updateCategoryAssociations($pdo, $startId, $endId);
        break;
    case 'all':
        processAuthorAndUserId($pdo, $startId, $endId);
        updateMainCategoryAndAssociations($pdo, $startId, $endId);
        updateCategoryAssociations($pdo, $startId, $endId);
        break;
    default:
        sendProgress("Função não encontrada", "error");
}
?>
