<?php
$host = G5_MYSQL_HOST;
$db = G5_MYSQL_DB;
$user = G5_MYSQL_USER;
$pass = G5_MYSQL_PASSWORD;
$charset = 'utf8mb4';

$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);
} catch (\PDOException $e) {
    throw new \PDOException($e->getMessage(), (int)$e->getCode());
}

// 1. 데이터베이스 내의 모든 테이블을 찾습니다.
$stmt = $pdo->query("
    SELECT table_name 
    FROM information_schema.columns 
    WHERE column_name = 'wr_content' 
    AND table_schema = DATABASE()
");

$tables = $stmt->fetchAll(PDO::FETCH_COLUMN);

if (empty($tables)) {
    echo "No tables found with 'wr_content' column.";
    exit;
}

// 2. 각 테이블의 wr_content 열에서 URL을 추출하는 동적 SQL 쿼리를 생성합니다.
$sqlPartsData = [];
$sqlPartsNoData = [];

foreach ($tables as $table) {
    $sqlPartsData[] = "
        SELECT 
            '$table' AS table_name,
            CASE 
                WHEN wr_content REGEXP 'https://[^ ]+/data/' THEN SUBSTRING_INDEX(SUBSTRING_INDEX(wr_content, 'https://', -1), ' ', 1)
                WHEN wr_content REGEXP 'http://[^ ]+/data/' THEN SUBSTRING_INDEX(SUBSTRING_INDEX(wr_content, 'http://', -1), ' ', 1)
            END AS url,
            CASE 
                WHEN wr_content REGEXP 'https://[^ ]+/data/' THEN 'https://'
                WHEN wr_content REGEXP 'http://[^ ]+/data/' THEN 'http://'
            END AS protocol
        FROM $table 
        WHERE wr_content REGEXP 'https?://[a-zA-Z0-9./?=_-]+/data/'
    ";

    $sqlPartsNoData[] = "
        SELECT 
            '$table' AS table_name,
            CASE 
                WHEN wr_content REGEXP 'https://' THEN SUBSTRING_INDEX(SUBSTRING_INDEX(wr_content, 'https://', -1), ' ', 1)
                WHEN wr_content REGEXP 'http://' THEN SUBSTRING_INDEX(SUBSTRING_INDEX(wr_content, 'http://', -1), ' ', 1)
            END AS url,
            CASE 
                WHEN wr_content REGEXP 'https://' THEN 'https://'
                WHEN wr_content REGEXP 'http://' THEN 'http://'
            END AS protocol
        FROM $table 
        WHERE wr_content REGEXP 'https?://[a-zA-Z0-9./?=_-]+' AND wr_content NOT REGEXP '/data/'
    ";
}

$sqlData = implode(' UNION ALL ', $sqlPartsData);
$sqlNoData = implode(' UNION ALL ', $sqlPartsNoData);

// 3. 추출한 URL을 결과로 표시합니다.
$stmtData = $pdo->query($sqlData);
$resultsData = $stmtData->fetchAll();

$stmtNoData = $pdo->query($sqlNoData);
$resultsNoData = $stmtNoData->fetchAll();

$G5_URL = G5_URL;
if ($resultsData) {
    foreach ($resultsData as $row) {
        $full_url = $row['protocol'] . $row['url'];
        if (strpos($full_url, $G5_URL) === false) {
           $string = htmlspecialchars($full_url);
           $pattern = '/(https?:\/\/.*?)\/data/';
           preg_match($pattern, $string, $matches);

           if (isset($matches[1])) {
               $url = $matches[1];
               echo $url;
               break;
           }
        }
    }
}