<?php
// 파일 위치: /var/www/html/i/yellow.php
// IG Sort Yellow v46 API endpoint
// 구조: ig_media 전역 마스터 + ig_media_user_download 사용자별 다운로드 상태 + ig_media_collect_event 통계 이벤트
// 호출 예:
//   GET  /i/yellow.php?mode=health
//   POST /i/yellow.php token=...&mode=media_exists&shortcode=...&collector_id=...
//   Header: Authorization: Bearer TOKEN 도 지원

require_once dirname(__DIR__) . '/i/yellow_func.php';

function yellow_first_str($keys, $default = '')
{
    foreach ($keys as $k) {
        $v = yellow_str($k, '');
        if ($v !== '') {
            return $v;
        }
    }
    return $default;
}

function yellow_norm_ig_account($account_name = '')
{
    $v = yellow_first_str(array('ig_account_id', 'owner_username', 'account_name', 'account'), $account_name);
    $v = trim($v);
    if (strlen($v) > 0 && $v[0] === '@') {
        $v = substr($v, 1);
    }
    return $v;
}

function yellow_save_folder_from_path($file_path)
{
    $file_path = trim((string)$file_path);
    if ($file_path === '') {
        return '';
    }
    return preg_replace('#[\\/][^\\/]*$#', '', $file_path);
}

function yellow_asset_kind_from_request($file_name, $file_path)
{
    $kind = strtolower(yellow_first_str(array('asset_kind', 'media_kind', 'download_kind', 'file_kind'), ''));
    if ($kind === 'jpg' || $kind === 'jpeg' || $kind === 'png' || $kind === 'webp' || $kind === 'avif' || $kind === 'image') {
        return 'image';
    }
    if ($kind === 'mp4' || $kind === 'mov' || $kind === 'webm' || $kind === 'video') {
        return 'video';
    }

    $name = strtolower((string)$file_name . ' ' . (string)$file_path);
    if (preg_match('/\.(jpg|jpeg|png|webp|avif)(\?|#|$)/i', $name)) {
        return 'image';
    }
    if (preg_match('/\.(mp4|mov|webm|m4v)(\?|#|$)/i', $name)) {
        return 'video';
    }

    return '';
}


function yellow_sort_field_from_request()
{
    $field = strtolower(yellow_first_str(array('sort_field', 'sort_by_field', 'sort_by', 'sort'), ''));
    $map = array(
        'view' => 'play_count',
        'views' => 'play_count',
        'play' => 'play_count',
        'plays' => 'play_count',
        'play_count' => 'play_count',
        'like' => 'likes',
        'likes' => 'likes',
        'heart' => 'likes',
        'hearts' => 'likes',
        'comment' => 'comments',
        'comments' => 'comments',
        'reply' => 'comments',
        'repost' => 'reposts',
        'reposts' => 'reposts',
        'share' => 'reposts',
        'shares' => 'reposts',
        'created' => 'created_at',
        'created_at' => 'created_at',
        'upload' => 'upload_at',
        'upload_at' => 'upload_at',
        'date' => 'upload_at',
    );
    return isset($map[$field]) ? $map[$field] : $field;
}

function yellow_sort_value_from_fields($sort_field, $fields)
{
    $v = yellow_int('sort_value', yellow_int('sort_by_value', -1));
    if ($v >= 0) {
        return $v;
    }
    if ($sort_field === 'play_count') {
        return (int)$fields['views'];
    }
    if ($sort_field === 'likes') {
        return (int)$fields['likes'];
    }
    if ($sort_field === 'comments') {
        return (int)$fields['comments'];
    }
    if ($sort_field === 'reposts') {
        return (int)$fields['reposts'];
    }
    return 0;
}

function yellow_rank_label_from_no($rank_no, $fallback = '')
{
    $fallback = trim((string)$fallback);
    if ($fallback !== '') {
        return $fallback;
    }
    $rank_no = (int)$rank_no;
    if ($rank_no > 0) {
        return 'No' . str_pad((string)$rank_no, 3, '0', STR_PAD_LEFT);
    }
    return 'No없음';
}

function yellow_event_key($fields, $shortcode)
{
    // v45/v179a1: mp4/jpg of the same filename_stem should merge into one event row.
    // No000 is a pass/snapshot label, not a stable duplicate key; shortcode remains the stable media key.
    $parts = array(
        (string)$fields['collector_id'],
        (string)$shortcode,
        (string)$fields['media_index'],
        (string)$fields['filename_stem'],
        (string)$fields['rank_label'],
        (string)$fields['sort_field'],
        (string)$fields['source_type'],
    );
    return sha1(implode('|', $parts));
}

function yellow_upsert_collect_event($shortcode, $fields)
{
    $et = yellow_safe_table(yellow_media_collect_event_table());
    if (!yellow_table_exists($et)) {
        return array('skipped' => 1, 'reason' => 'event_table_missing', 'table' => $et);
    }

    $event_key = yellow_event_key($fields, $shortcode);
    $kind = (string)$fields['asset_kind'];
    $has_image = ($kind === 'image') ? 1 : 0;
    $has_video = ($kind === 'video') ? 1 : 0;
    $image_file_name = $has_image ? $fields['file_name'] : '';
    $video_file_name = $has_video ? $fields['file_name'] : '';
    $image_size = $has_image ? $fields['file_size'] : 0;
    $video_size = $has_video ? $fields['file_size'] : 0;
    $downloaded_at_sql = yellow_clean_asset_status($fields['asset_status'], ($fields['file_name'] !== '' || $fields['md5'] !== '' || $fields['sha256'] !== '')) === 'done' ? 'NOW()' : 'NULL';

    yellow_exec(
        "INSERT INTO {$et}
        (event_key, collector_id, shortcode, ig_account_id, account_name, folder_name, source_type, target_url, pass_id, top_n, threshold_value, threshold_label, rank_no, rank_label, rank_meaning, sort_field, sort_value, views, likes, comments, reposts, upload_at, collected_at, downloaded_at, filename_stem, media_index, has_image, has_video, image_file_name, video_file_name, image_size, video_size, created_at, updated_at)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, {$downloaded_at_sql}, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW())
        ON DUPLICATE KEY UPDATE
            ig_account_id = IF(VALUES(ig_account_id) <> '', VALUES(ig_account_id), ig_account_id),
            account_name = IF(VALUES(account_name) <> '', VALUES(account_name), account_name),
            folder_name = IF(VALUES(folder_name) <> '', VALUES(folder_name), folder_name),
            source_type = IF(VALUES(source_type) <> '', VALUES(source_type), source_type),
            target_url = IF(VALUES(target_url) <> '', VALUES(target_url), target_url),
            pass_id = IF(VALUES(pass_id) <> '', VALUES(pass_id), pass_id),
            top_n = IF(VALUES(top_n) > 0, VALUES(top_n), top_n),
            threshold_value = IF(VALUES(threshold_value) > 0, VALUES(threshold_value), threshold_value),
            threshold_label = IF(VALUES(threshold_label) <> '', VALUES(threshold_label), threshold_label),
            rank_no = IF(VALUES(rank_no) > 0, VALUES(rank_no), rank_no),
            rank_label = IF(VALUES(rank_label) <> '', VALUES(rank_label), rank_label),
            rank_meaning = IF(VALUES(rank_meaning) <> '', VALUES(rank_meaning), rank_meaning),
            sort_field = IF(VALUES(sort_field) <> '', VALUES(sort_field), sort_field),
            sort_value = IF(VALUES(sort_value) > 0, VALUES(sort_value), sort_value),
            views = IF(VALUES(views) > 0, VALUES(views), views),
            likes = IF(VALUES(likes) > 0, VALUES(likes), likes),
            comments = IF(VALUES(comments) > 0, VALUES(comments), comments),
            reposts = IF(VALUES(reposts) > 0, VALUES(reposts), reposts),
            upload_at = IF(VALUES(upload_at) IS NOT NULL, VALUES(upload_at), upload_at),
            collected_at = IF(VALUES(collected_at) IS NOT NULL, VALUES(collected_at), collected_at),
            downloaded_at = IFNULL(downloaded_at, VALUES(downloaded_at)),
            filename_stem = IF(VALUES(filename_stem) <> '', VALUES(filename_stem), filename_stem),
            has_image = GREATEST(has_image, VALUES(has_image)),
            has_video = GREATEST(has_video, VALUES(has_video)),
            image_file_name = IF(VALUES(image_file_name) <> '', VALUES(image_file_name), image_file_name),
            video_file_name = IF(VALUES(video_file_name) <> '', VALUES(video_file_name), video_file_name),
            image_size = IF(VALUES(image_size) > 0, VALUES(image_size), image_size),
            video_size = IF(VALUES(video_size) > 0, VALUES(video_size), video_size),
            updated_at = NOW()",
        array(
            $event_key,
            $fields['collector_id'],
            $shortcode,
            $fields['ig_account_id'],
            $fields['account_name'],
            $fields['folder_name'],
            $fields['source_type'],
            $fields['target_url'],
            $fields['pass_id'],
            $fields['top_n'],
            $fields['threshold_value'],
            $fields['threshold_label'],
            $fields['rank_no'],
            $fields['rank_label'],
            $fields['rank_meaning'],
            $fields['sort_field'],
            $fields['sort_value'],
            $fields['views'],
            $fields['likes'],
            $fields['comments'],
            $fields['reposts'],
            $fields['upload_at'],
            $fields['collected_at'],
            $fields['filename_stem'],
            $fields['media_index'],
            $has_image,
            $has_video,
            $image_file_name,
            $video_file_name,
            $image_size,
            $video_size
        )
    );

    $row = yellow_fetch_one('SELECT ' . yellow_collect_event_public_cols() . " FROM {$et} WHERE event_key = ? LIMIT 1", array($event_key));
    return $row ? $row : array('event_key' => $event_key);
}

function yellow_clean_asset_status($status, $has_file)
{
    $status = strtolower(trim((string)$status));
    $allowed = array('pending', 'done', 'failed', 'skipped');
    if (in_array($status, $allowed, true)) {
        return $status;
    }
    return $has_file ? 'done' : 'pending';
}

function yellow_calc_download_status($row)
{
    $image = isset($row['image_status']) ? (string)$row['image_status'] : 'pending';
    $video = isset($row['video_status']) ? (string)$row['video_status'] : 'pending';

    if ($image === 'done' && $video === 'done') {
        return 'complete';
    }
    if (($image === 'done' || $video === 'done') || ($image === 'skipped' && $video === 'done') || ($image === 'done' && $video === 'skipped')) {
        return 'partial';
    }
    if ($image === 'failed' || $video === 'failed') {
        return 'failed';
    }
    return 'pending';
}

function yellow_recalc_user_download($collector_id, $shortcode)
{
    $ut = yellow_safe_table(yellow_media_user_download_table());
    $row = yellow_fetch_one('SELECT ' . yellow_download_public_cols() . " FROM {$ut} WHERE collector_id = ? AND shortcode = ? LIMIT 1", array($collector_id, $shortcode));
    if (!$row) {
        return array();
    }

    $status = yellow_calc_download_status($row);
    $completed = ($status === 'complete') ? 'NOW()' : 'NULL';
    yellow_exec(
        "UPDATE {$ut}
         SET download_status = ?, completed_at = IF(? = 'complete', IFNULL(completed_at, NOW()), completed_at), updated_at = NOW()
         WHERE collector_id = ? AND shortcode = ?",
        array($status, $status, $collector_id, $shortcode)
    );

    return yellow_fetch_one('SELECT ' . yellow_download_public_cols() . " FROM {$ut} WHERE collector_id = ? AND shortcode = ? LIMIT 1", array($collector_id, $shortcode));
}

function yellow_upsert_master($shortcode, $fields)
{
    $table = yellow_safe_table(yellow_media_table($shortcode));

    yellow_exec(
        "INSERT INTO {$table}
        (shortcode, media_url, account_name, ig_account_id, upload_at, thumbnail_url, video_url, views, likes, comments, reposts, first_collector_id, first_collected_at, last_collected_at, created_at, updated_at)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW(), NOW(), NOW())
        ON DUPLICATE KEY UPDATE
            media_url = IF(VALUES(media_url) <> '', VALUES(media_url), media_url),
            account_name = IF(VALUES(account_name) <> '', VALUES(account_name), account_name),
            ig_account_id = IF(VALUES(ig_account_id) <> '', VALUES(ig_account_id), ig_account_id),
            upload_at = IF(VALUES(upload_at) IS NOT NULL, VALUES(upload_at), upload_at),
            thumbnail_url = IF(VALUES(thumbnail_url) <> '', VALUES(thumbnail_url), thumbnail_url),
            video_url = IF(VALUES(video_url) <> '', VALUES(video_url), video_url),
            views = IF(VALUES(views) > 0, VALUES(views), views),
            likes = IF(VALUES(likes) > 0, VALUES(likes), likes),
            comments = IF(VALUES(comments) > 0, VALUES(comments), comments),
            reposts = IF(VALUES(reposts) > 0, VALUES(reposts), reposts),
            first_collector_id = IF(first_collector_id = '' AND VALUES(first_collector_id) <> '', VALUES(first_collector_id), first_collector_id),
            first_collected_at = IFNULL(first_collected_at, NOW()),
            last_collected_at = NOW(),
            updated_at = NOW()",
        array(
            $shortcode,
            $fields['media_url'],
            $fields['account_name'],
            $fields['ig_account_id'],
            $fields['upload_at'],
            $fields['thumbnail_url'],
            $fields['video_url'],
            $fields['views'],
            $fields['likes'],
            $fields['comments'],
            $fields['reposts'],
            $fields['collector_id']
        )
    );

    return yellow_fetch_one('SELECT ' . yellow_media_public_cols() . " FROM {$table} WHERE shortcode = ? LIMIT 1", array($shortcode));
}

function yellow_upsert_user_download($shortcode, $fields)
{
    $collector_id = (string)$fields['collector_id'];
    if ($collector_id === '') {
        return array();
    }

    $ut = yellow_safe_table(yellow_media_user_download_table());
    $kind = $fields['asset_kind'];
    $file_name = $fields['file_name'];
    $file_path = $fields['file_path'];
    $has_file = ($file_name !== '' || $file_path !== '' || $fields['md5'] !== '' || $fields['sha256'] !== '');
    $asset_status = yellow_clean_asset_status($fields['asset_status'], $has_file);

    $base_params = array(
        $collector_id,
        $shortcode,
        $fields['ig_account_id'],
        $fields['save_folder'],
        $fields['folder_name'],
    );

    if ($kind === 'image') {
        yellow_exec(
            "INSERT INTO {$ut}
            (collector_id, shortcode, ig_account_id, save_folder, folder_name, image_file_name, image_file_path, image_md5, image_sha256, image_size, image_status, image_downloaded_at, download_status, last_error, attempt_count, first_started_at, created_at, updated_at)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, IF(? = 'done', NOW(), NULL), 'partial', ?, 1, NOW(), NOW(), NOW())
            ON DUPLICATE KEY UPDATE
                ig_account_id = IF(VALUES(ig_account_id) <> '', VALUES(ig_account_id), ig_account_id),
                save_folder = IF(VALUES(save_folder) <> '', VALUES(save_folder), save_folder),
                folder_name = IF(VALUES(folder_name) <> '', VALUES(folder_name), folder_name),
                image_file_name = IF(VALUES(image_file_name) <> '', VALUES(image_file_name), image_file_name),
                image_file_path = IF(VALUES(image_file_path) <> '', VALUES(image_file_path), image_file_path),
                image_md5 = IF(VALUES(image_md5) <> '', VALUES(image_md5), image_md5),
                image_sha256 = IF(VALUES(image_sha256) <> '', VALUES(image_sha256), image_sha256),
                image_size = IF(VALUES(image_size) > 0, VALUES(image_size), image_size),
                image_status = VALUES(image_status),
                image_downloaded_at = IF(VALUES(image_status) = 'done', IFNULL(image_downloaded_at, NOW()), image_downloaded_at),
                last_error = IF(VALUES(last_error) <> '', VALUES(last_error), last_error),
                attempt_count = attempt_count + 1,
                first_started_at = IFNULL(first_started_at, NOW()),
                updated_at = NOW()",
            array_merge($base_params, array($file_name, $file_path, $fields['md5'], $fields['sha256'], $fields['file_size'], $asset_status, $asset_status, $fields['last_error']))
        );
    } elseif ($kind === 'video') {
        yellow_exec(
            "INSERT INTO {$ut}
            (collector_id, shortcode, ig_account_id, save_folder, folder_name, video_file_name, video_file_path, video_md5, video_sha256, video_size, video_status, video_downloaded_at, download_status, last_error, attempt_count, first_started_at, created_at, updated_at)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, IF(? = 'done', NOW(), NULL), 'partial', ?, 1, NOW(), NOW(), NOW())
            ON DUPLICATE KEY UPDATE
                ig_account_id = IF(VALUES(ig_account_id) <> '', VALUES(ig_account_id), ig_account_id),
                save_folder = IF(VALUES(save_folder) <> '', VALUES(save_folder), save_folder),
                folder_name = IF(VALUES(folder_name) <> '', VALUES(folder_name), folder_name),
                video_file_name = IF(VALUES(video_file_name) <> '', VALUES(video_file_name), video_file_name),
                video_file_path = IF(VALUES(video_file_path) <> '', VALUES(video_file_path), video_file_path),
                video_md5 = IF(VALUES(video_md5) <> '', VALUES(video_md5), video_md5),
                video_sha256 = IF(VALUES(video_sha256) <> '', VALUES(video_sha256), video_sha256),
                video_size = IF(VALUES(video_size) > 0, VALUES(video_size), video_size),
                video_status = VALUES(video_status),
                video_downloaded_at = IF(VALUES(video_status) = 'done', IFNULL(video_downloaded_at, NOW()), video_downloaded_at),
                last_error = IF(VALUES(last_error) <> '', VALUES(last_error), last_error),
                attempt_count = attempt_count + 1,
                first_started_at = IFNULL(first_started_at, NOW()),
                updated_at = NOW()",
            array_merge($base_params, array($file_name, $file_path, $fields['md5'], $fields['sha256'], $fields['file_size'], $asset_status, $asset_status, $fields['last_error']))
        );
    } else {
        yellow_exec(
            "INSERT INTO {$ut}
            (collector_id, shortcode, ig_account_id, save_folder, folder_name, download_status, last_error, attempt_count, first_started_at, created_at, updated_at)
            VALUES (?, ?, ?, ?, ?, 'pending', ?, 1, NOW(), NOW(), NOW())
            ON DUPLICATE KEY UPDATE
                ig_account_id = IF(VALUES(ig_account_id) <> '', VALUES(ig_account_id), ig_account_id),
                save_folder = IF(VALUES(save_folder) <> '', VALUES(save_folder), save_folder),
                folder_name = IF(VALUES(folder_name) <> '', VALUES(folder_name), folder_name),
                last_error = IF(VALUES(last_error) <> '', VALUES(last_error), last_error),
                attempt_count = attempt_count + 1,
                first_started_at = IFNULL(first_started_at, NOW()),
                updated_at = NOW()",
            array_merge($base_params, array($fields['last_error']))
        );
    }

    return yellow_recalc_user_download($collector_id, $shortcode);
}

function yellow_collect_fields($shortcode, $media_url)
{
    $account_name = yellow_first_str(array('account_name', 'owner_username', 'account'), '');
    $ig_account_id = yellow_norm_ig_account($account_name);
    $file_name = yellow_str('file_name', '');
    $file_path = yellow_str('file_path', '');
    $save_folder = yellow_first_str(array('save_folder', 'download_folder'), yellow_save_folder_from_path($file_path));
    $folder_name = yellow_first_str(array('folder_name', 'folder'), '');
    if ($folder_name === '' && $save_folder !== '') {
        $tmp = preg_split('#[\\/]#', $save_folder);
        $folder_name = end($tmp);
    }
    if ($folder_name === '') {
        $folder_name = $ig_account_id;
    }

    $rank_no = max(0, yellow_int('rank_no', yellow_int('rank', 0)));
    $rank_label = yellow_rank_label_from_no($rank_no, yellow_first_str(array('rank_label', 'rank_text', 'no_label'), ''));
    $fields = array(
        'collector_id' => yellow_first_str(array('collector_id', 'user_id'), ''),
        'ig_account_id' => $ig_account_id,
        'account_name' => $account_name !== '' ? $account_name : $ig_account_id,
        'media_url' => $media_url,
        'upload_at' => yellow_norm_datetime(yellow_str('upload_at', '')),
        'thumbnail_url' => yellow_first_str(array('thumbnail_url', 'image_url', 'cover_url'), ''),
        'video_url' => yellow_first_str(array('video_url', 'mp4_url'), ''),
        'views' => max(0, yellow_int('views', 0)),
        'likes' => max(0, yellow_int('likes', 0)),
        'comments' => max(0, yellow_int('comments', 0)),
        'reposts' => max(0, yellow_int('reposts', 0)),
        'file_name' => $file_name,
        'file_path' => $file_path,
        'save_folder' => $save_folder,
        'folder_name' => $folder_name,
        'md5' => strtolower(yellow_str('md5', '')),
        'sha256' => strtolower(yellow_str('sha256', '')),
        'file_size' => max(0, yellow_int('file_size', yellow_int('size', 0))),
        'asset_kind' => yellow_asset_kind_from_request($file_name, $file_path),
        'asset_status' => yellow_first_str(array('asset_status', 'file_status'), ''),
        'last_error' => yellow_str('last_error', yellow_str('error_message', '')),
        'rank_no' => $rank_no,
        'rank_label' => $rank_label,
        'rank_meaning' => yellow_first_str(array('rank_meaning'), 'sort_rank_at_collection_time'),
        'collected_at' => yellow_norm_datetime(yellow_first_str(array('collected_at', 'captured_at', 'snapshot_at'), '')),
        'filename_stem' => yellow_first_str(array('filename_stem'), preg_replace('/\.[^.]+$/', '', $file_name)),
        'media_index' => max(0, yellow_int('media_index', 0)),
        'source_type' => yellow_first_str(array('source_type', 'page_type', 'mode_name'), ''),
        'target_url' => yellow_first_str(array('target_url', 'instagram_url', 'page_url'), ''),
        'pass_id' => yellow_first_str(array('pass_id', 'snapshot_id', 'cycle_id'), ''),
        'top_n' => max(0, yellow_int('top_n', yellow_int('n', 0))),
        'threshold_value' => max(0, yellow_int('threshold_value', yellow_int('min_value', 0))),
        'threshold_label' => yellow_first_str(array('threshold_label', 'threshold_text'), ''),
    );
    $fields['sort_field'] = yellow_sort_field_from_request();
    $fields['sort_value'] = yellow_sort_value_from_fields($fields['sort_field'], $fields);
    if ($fields['sort_field'] === '' && $fields['sort_value'] > 0) {
        $fields['sort_field'] = 'unknown';
    }

    return $fields;
}
$yellow_error_stage = 'request';
$mode = yellow_str('mode', '');
if ($mode === '') {
    $mode = yellow_str('action', '');
}

$alias = array(
    'exists' => 'media_exists',
    'insert' => 'media_insert',
    'update' => 'media_update',
    'download_update' => 'media_download_update',
    'user_download' => 'media_download_update',
    'list' => 'media_list',
    'history' => 'media_history',
    'stats' => 'media_stats',
    'trend' => 'media_trend',
    'schema' => 'schema_sql',
);
if (isset($alias[$mode])) {
    $mode = $alias[$mode];
}

try {
    if ($mode === 'health') {
        yellow_ok(array(
            'service' => 'IG_Sort_Yellow',
            'time' => date('Y-m-d H:i:s'),
            'auth_required' => 1,
            'schema' => 'ig_media + ig_media_user_download + ig_media_collect_event',
        ));
    }

    if ($mode === 'token') {
        $token = yellow_token();
        if (!yellow_check_token($token)) {
            yellow_error('invalid_token', 403, array(
                'token_configured' => count(yellow_allowed_tokens()) > 0 ? 1 : 0,
            ));
        }
        yellow_ok(array(
            'token_ok' => 1,
            'service' => 'IG_Sort_Yellow',
            'time' => date('Y-m-d H:i:s'),
        ));
    }

    yellow_must_token();

    if ($mode === 'schema_sql') {
        $path = __DIR__ . '/yellow_schema_reset.sql';
        $update_path = __DIR__ . '/yellow_schema_v45_update.sql';
        $compat_path = __DIR__ . '/yellow_schema_v46_compat.sql';
        yellow_ok(array(
            'file' => 'yellow_schema_reset.sql',
            'update_file' => 'yellow_schema_v45_update.sql',
            'compat_file' => 'yellow_schema_v46_compat.sql',
            'warning' => 'reset_sql에는 DROP TABLE 포함. 운영 DB는 compat_sql로 필수 컬럼만 비파괴 보완하세요.',
            'sql' => is_file($path) ? file_get_contents($path) : '',
            'update_sql' => is_file($update_path) ? file_get_contents($update_path) : '',
            'compat_sql' => is_file($compat_path) ? file_get_contents($compat_path) : '',
        ));
    }

    if ($mode === 'schema_check') {
        $yellow_error_stage = 'schema_check';
        $schema = yellow_media_schema_diagnostics();
        yellow_ok(array(
            'schema_ok' => !empty($schema['ok']) ? 1 : 0,
            'schema' => $schema,
        ));
    }

    if ($mode === 'media_exists') {
        $media_url = yellow_str('media_url', '');
        $shortcode = yellow_norm_shortcode(yellow_str('shortcode', ''), $media_url);
        $collector_id = yellow_first_str(array('collector_id', 'user_id'), '');
        $account_name = yellow_first_str(array('account_name', 'owner_username', 'account'), '');
        $ig_account_id = yellow_norm_ig_account($account_name);
        $upload_at = yellow_norm_datetime(yellow_str('upload_at', ''));
        $md5 = strtolower(yellow_str('md5', ''));
        $sha256 = strtolower(yellow_str('sha256', ''));

        if ($shortcode === '' && $media_url === '' && $md5 === '' && $sha256 === '' && ($ig_account_id === '' || $upload_at === null)) {
            yellow_error('empty_exists_key', 400);
        }

        $mt = yellow_safe_table(yellow_media_base_table());
        $ut = yellow_safe_table(yellow_media_user_download_table());
        $master = array();
        $user = array();
        $match_by = '';

        if ($shortcode !== '') {
            $master = yellow_fetch_one('SELECT ' . yellow_media_public_cols() . " FROM {$mt} WHERE shortcode = ? LIMIT 1", array($shortcode));
            if ($master) { $match_by = 'shortcode'; }
        }
        if (!$master && $media_url !== '') {
            $master = yellow_fetch_one('SELECT ' . yellow_media_public_cols() . " FROM {$mt} WHERE media_url = ? LIMIT 1", array($media_url));
            if ($master) { $shortcode = $master['shortcode']; $match_by = 'media_url'; }
        }
        if (!$master && $ig_account_id !== '' && $upload_at !== null) {
            $master = yellow_fetch_one('SELECT ' . yellow_media_public_cols() . " FROM {$mt} WHERE ig_account_id = ? AND upload_at = ? LIMIT 1", array($ig_account_id, $upload_at));
            if ($master) { $shortcode = $master['shortcode']; $match_by = 'ig_upload_at'; }
        }

        if ($collector_id !== '' && $shortcode !== '') {
            $user = yellow_fetch_one('SELECT ' . yellow_download_public_cols() . " FROM {$ut} WHERE collector_id = ? AND shortcode = ? LIMIT 1", array($collector_id, $shortcode));
        }
        if (!$user && $collector_id !== '' && $md5 !== '') {
            $user = yellow_fetch_one('SELECT ' . yellow_download_public_cols() . " FROM {$ut} WHERE collector_id = ? AND (image_md5 = ? OR video_md5 = ?) LIMIT 1", array($collector_id, $md5, $md5));
            if ($user && !$master) {
                $shortcode = $user['shortcode'];
                $master = yellow_fetch_one('SELECT ' . yellow_media_public_cols() . " FROM {$mt} WHERE shortcode = ? LIMIT 1", array($shortcode));
            }
            if ($user) { $match_by = 'user_md5'; }
        }
        if (!$user && $collector_id !== '' && $sha256 !== '') {
            $user = yellow_fetch_one('SELECT ' . yellow_download_public_cols() . " FROM {$ut} WHERE collector_id = ? AND (image_sha256 = ? OR video_sha256 = ?) LIMIT 1", array($collector_id, $sha256, $sha256));
            if ($user && !$master) {
                $shortcode = $user['shortcode'];
                $master = yellow_fetch_one('SELECT ' . yellow_media_public_cols() . " FROM {$mt} WHERE shortcode = ? LIMIT 1", array($shortcode));
            }
            if ($user) { $match_by = 'user_sha256'; }
        }

        $global_exists = $master ? 1 : 0;
        $user_exists = $user ? 1 : 0;
        $download_status = $user ? (string)$user['download_status'] : '';
        $exists = 0;
        $status = 'NEW';

        if ($collector_id !== '') {
            if ($user && $download_status === 'complete') {
                $exists = 1;
                $status = 'PSSS';
            } elseif ($user) {
                $exists = 0;
                $status = strtoupper($download_status ? $download_status : 'partial');
            } elseif ($master) {
                $exists = 0;
                $status = 'GLOBAL_ONLY';
            }
        } else {
            $exists = $global_exists;
            $status = $global_exists ? 'PSSS' : 'NEW';
        }

        yellow_ok(array(
            'exists' => $exists,
            'global_exists' => $global_exists,
            'user_exists' => $user_exists,
            'status' => $status,
            'download_status' => $download_status,
            'match_by' => $match_by,
            'media_table' => $mt,
            'download_table' => $ut,
            'item' => $master ? $master : null,
            'user_download' => $user ? $user : null,
        ));
    }

    if ($mode === 'media_insert' || $mode === 'media_download_update') {
        $media_url = yellow_str('media_url', '');
        $shortcode = yellow_norm_shortcode(yellow_str('shortcode', ''), $media_url);
        if ($shortcode === '') {
            yellow_error('shortcode_empty', 400);
        }

        $fields = yellow_collect_fields($shortcode, $media_url);
        $yellow_error_stage = 'schema_check';
        $schema = yellow_media_schema_diagnostics();
        if (empty($schema['ok'])) {
            $first = reset($schema['mismatches']);
            yellow_error('schema_mismatch', 500, array(
                'error_stage' => 'schema_check',
                'table' => is_array($first) && isset($first['table']) ? $first['table'] : '',
                'missing_columns' => is_array($first) && isset($first['missing_columns']) ? $first['missing_columns'] : array(),
                'schema' => $schema,
                'message' => '운영 MySQL 구조와 yellow.php v46 필수 컬럼이 일치하지 않습니다.',
            ));
        }
        $yellow_error_stage = 'master_upsert';
        $master = yellow_upsert_master($shortcode, $fields);
        $yellow_error_stage = 'user_download_upsert';
        $user = yellow_upsert_user_download($shortcode, $fields);
        $yellow_error_stage = 'collect_event_upsert';
        $collect_event = yellow_upsert_collect_event($shortcode, $fields);

        yellow_ok(array(
            'status' => $user ? $user['download_status'] : 'MASTER_SAVED',
            'media_table' => yellow_media_base_table(),
            'download_table' => yellow_media_user_download_table(),
            'event_table' => yellow_media_collect_event_table(),
            'asset_kind' => $fields['asset_kind'],
            'item' => $master,
            'user_download' => $user ? $user : null,
            'collect_event' => $collect_event,
        ));
    }

    if ($mode === 'media_update') {
        $media_url = yellow_str('media_url', '');
        $shortcode = yellow_norm_shortcode(yellow_str('shortcode', ''), $media_url);
        if ($shortcode === '') {
            yellow_error('shortcode_empty', 400);
        }
        $fields = yellow_collect_fields($shortcode, $media_url);
        $master = yellow_upsert_master($shortcode, $fields);
        $collect_event = yellow_upsert_collect_event($shortcode, $fields);
        yellow_ok(array(
            'status' => 'UPDATED',
            'media_table' => yellow_media_base_table(),
            'event_table' => yellow_media_collect_event_table(),
            'item' => $master,
            'collect_event' => $collect_event,
        ));
    }

    if ($mode === 'media_list') {
        $limit = yellow_limit(yellow_req('limit', 100), 100, 500);
        $dir = strtolower(yellow_str('dir', 'desc')) === 'asc' ? 'ASC' : 'DESC';
        $collector_id = yellow_first_str(array('collector_id', 'user_id'), '');
        $account_name = yellow_first_str(array('account_name', 'owner_username', 'account'), '');
        $ig_account_id = yellow_norm_ig_account($account_name);
        $folder_name = yellow_first_str(array('folder_name', 'folder'), '');
        $download_status = strtolower(yellow_str('download_status', ''));
        $since_id = yellow_int('since_id', 0);
        $mt = yellow_safe_table(yellow_media_base_table());
        $ut = yellow_safe_table(yellow_media_user_download_table());

        if ($collector_id !== '') {
            $sort = strtolower(yellow_str('sort', 'id'));
            $sort_map = array(
                'id' => 'u.id',
                'updated_at' => 'u.updated_at',
                'created_at' => 'u.created_at',
                'downloaded_at' => 'u.completed_at',
                'completed_at' => 'u.completed_at',
                'views' => 'm.views',
                'likes' => 'm.likes',
                'comments' => 'm.comments',
                'reposts' => 'm.reposts',
                'upload_at' => 'm.upload_at',
            );
            $order = isset($sort_map[$sort]) ? $sort_map[$sort] : 'u.id';
            $where = array('u.collector_id = ?');
            $params = array($collector_id);
            if ($ig_account_id !== '') { $where[] = 'u.ig_account_id = ?'; $params[] = $ig_account_id; }
            if ($folder_name !== '') { $where[] = 'u.folder_name = ?'; $params[] = $folder_name; }
            if ($download_status !== '') { $where[] = 'u.download_status = ?'; $params[] = $download_status; }
            if ($since_id > 0) { $where[] = 'u.id > ?'; $params[] = $since_id; }

            $sql = "SELECT
                        u.id AS download_id, u.collector_id, u.shortcode, u.ig_account_id,
                        u.save_folder, u.folder_name,
                        u.image_file_name, u.image_file_path, u.image_md5, u.image_sha256, u.image_size, u.image_status,
                        u.video_file_name, u.video_file_path, u.video_md5, u.video_sha256, u.video_size, u.video_status,
                        u.download_status, u.last_error, u.attempt_count, u.first_started_at, u.image_downloaded_at, u.video_downloaded_at, u.completed_at,
                        u.created_at AS download_created_at, u.updated_at AS download_updated_at,
                        m.id AS media_id, m.media_url, m.account_name, m.upload_at, m.thumbnail_url, m.video_url,
                        m.views, m.likes, m.comments, m.reposts, m.first_collector_id, m.first_collected_at, m.last_collected_at
                    FROM {$ut} u
                    LEFT JOIN {$mt} m ON m.shortcode = u.shortcode
                    WHERE " . implode(' AND ', $where) . "
                    ORDER BY {$order} {$dir}
                    LIMIT {$limit}";
            $rows = yellow_fetch_all($sql, $params);
            yellow_ok(array('mode' => 'user_downloads', 'count' => count($rows), 'items' => $rows));
        }

        $sort = yellow_sort_sql(yellow_str('sort', 'id'));
        if ($sort === 'download_status') {
            $sort = 'id';
        }
        $where = array('1=1');
        $params = array();
        if ($ig_account_id !== '') { $where[] = 'ig_account_id = ?'; $params[] = $ig_account_id; }
        if ($account_name !== '') { $where[] = 'account_name = ?'; $params[] = $account_name; }
        if ($since_id > 0) { $where[] = 'id > ?'; $params[] = $since_id; }
        $sql = 'SELECT ' . yellow_media_public_cols() . " FROM {$mt} WHERE " . implode(' AND ', $where) . " ORDER BY {$sort} {$dir} LIMIT {$limit}";
        $rows = yellow_fetch_all($sql, $params);
        yellow_ok(array('mode' => 'media_master', 'table' => $mt, 'count' => count($rows), 'items' => $rows));
    }

    if ($mode === 'media_stats') {
        $collector_id = yellow_first_str(array('collector_id', 'user_id'), '');
        $mt = yellow_safe_table(yellow_media_base_table());
        $ut = yellow_safe_table(yellow_media_user_download_table());
        $master_stats = yellow_fetch_one(
            "SELECT
                COUNT(*) AS total,
                SUM(CASE WHEN DATE(created_at) = CURDATE() THEN 1 ELSE 0 END) AS today_created,
                SUM(CASE WHEN DATE(last_collected_at) = CURDATE() THEN 1 ELSE 0 END) AS today_collected,
                COUNT(DISTINCT ig_account_id) AS accounts,
                MAX(id) AS max_id
             FROM {$mt}"
        );
        $user_stats = null;
        if ($collector_id !== '') {
            $user_stats = yellow_fetch_one(
                "SELECT
                    COUNT(*) AS total,
                    SUM(CASE WHEN download_status = 'complete' THEN 1 ELSE 0 END) AS complete_count,
                    SUM(CASE WHEN download_status = 'partial' THEN 1 ELSE 0 END) AS partial_count,
                    SUM(CASE WHEN download_status = 'failed' THEN 1 ELSE 0 END) AS failed_count,
                    COUNT(DISTINCT ig_account_id) AS accounts,
                    MAX(id) AS max_id
                 FROM {$ut}
                 WHERE collector_id = ?",
                array($collector_id)
            );
        }
        $event_stats = null;
        $et = yellow_safe_table(yellow_media_collect_event_table());
        if (yellow_table_exists($et)) {
            if ($collector_id !== '') {
                $event_stats = yellow_fetch_one("SELECT COUNT(*) AS total_events, COUNT(DISTINCT shortcode) AS unique_media, COUNT(DISTINCT sort_field) AS sort_kinds, MIN(COALESCE(collected_at, downloaded_at, created_at)) AS first_event_at, MAX(COALESCE(collected_at, downloaded_at, created_at)) AS last_event_at FROM {$et} WHERE collector_id = ?", array($collector_id));
            } else {
                $event_stats = yellow_fetch_one("SELECT COUNT(*) AS total_events, COUNT(DISTINCT shortcode) AS unique_media, COUNT(DISTINCT sort_field) AS sort_kinds, MIN(COALESCE(collected_at, downloaded_at, created_at)) AS first_event_at, MAX(COALESCE(collected_at, downloaded_at, created_at)) AS last_event_at FROM {$et}");
            }
        }
        yellow_ok(array('media_table' => $mt, 'download_table' => $ut, 'event_table' => $et, 'stats' => $master_stats, 'user_stats' => $user_stats, 'event_stats' => $event_stats));
    }

    if ($mode === 'media_trend') {
        $collector_id = yellow_first_str(array('collector_id', 'user_id'), '');
        $et = yellow_safe_table(yellow_media_collect_event_table());
        if (!yellow_table_exists($et)) {
            yellow_error('event_table_missing', 400, array('event_table' => $et));
        }

        $date_by = strtolower(yellow_str('date_by', 'downloaded_at'));
        $date_map = array(
            'downloaded_at' => 'downloaded_at',
            'collected_at' => 'collected_at',
            'upload_at' => 'upload_at',
            'created_at' => 'created_at',
            'updated_at' => 'updated_at',
        );
        $date_col = isset($date_map[$date_by]) ? $date_map[$date_by] : 'downloaded_at';
        $bucket = strtolower(yellow_str('bucket', 'day'));
        $date_expr = ($bucket === 'month') ? "DATE_FORMAT({$date_col}, '%Y-%m')" : "DATE({$date_col})";
        $sort_field = yellow_sort_field_from_request();
        $days = yellow_limit(yellow_req('days', 365), 365, 5000);

        $where = array("{$date_col} IS NOT NULL", "{$date_col} >= DATE_SUB(NOW(), INTERVAL ? DAY)");
        $params = array($days);
        if ($collector_id !== '') { $where[] = 'collector_id = ?'; $params[] = $collector_id; }
        if ($sort_field !== '') { $where[] = 'sort_field = ?'; $params[] = $sort_field; }

        $sql = "SELECT
                    {$date_expr} AS date_key,
                    COALESCE(NULLIF(sort_field, ''), 'unknown') AS sort_field,
                    COUNT(*) AS event_count,
                    COUNT(DISTINCT shortcode) AS media_count,
                    SUM(has_video) AS video_count,
                    SUM(has_image) AS image_count,
                    SUM(views) AS views_sum,
                    AVG(NULLIF(views, 0)) AS views_avg,
                    MAX(views) AS views_max,
                    SUM(likes) AS likes_sum,
                    AVG(NULLIF(likes, 0)) AS likes_avg,
                    MAX(likes) AS likes_max,
                    SUM(comments) AS comments_sum,
                    AVG(NULLIF(comments, 0)) AS comments_avg,
                    MAX(comments) AS comments_max,
                    SUM(reposts) AS reposts_sum,
                    AVG(NULLIF(reposts, 0)) AS reposts_avg,
                    MAX(reposts) AS reposts_max,
                    MIN(rank_no) AS best_rank,
                    MAX(sort_value) AS max_sort_value
                FROM {$et}
                WHERE " . implode(' AND ', $where) . "
                GROUP BY date_key, sort_field
                ORDER BY date_key ASC, sort_field ASC";
        $rows = yellow_fetch_all($sql, $params);

        $running = array();
        foreach ($rows as &$r) {
            $sf = (string)$r['sort_field'];
            if (!isset($running[$sf])) { $running[$sf] = 0; }
            $running[$sf] += (int)$r['media_count'];
            $r['cumulative_media_count'] = $running[$sf];
        }
        unset($r);

        yellow_ok(array(
            'mode' => 'media_trend',
            'event_table' => $et,
            'date_by' => $date_col,
            'bucket' => ($bucket === 'month') ? 'month' : 'day',
            'days' => $days,
            'count' => count($rows),
            'items' => $rows,
        ));
    }

    if ($mode === 'media_history') {
        $media_url = yellow_str('media_url', '');
        $shortcode = yellow_norm_shortcode(yellow_str('shortcode', ''), $media_url);
        $collector_id = yellow_first_str(array('collector_id', 'user_id'), '');
        if ($shortcode === '') {
            yellow_error('shortcode_empty', 400);
        }
        $mt = yellow_safe_table(yellow_media_base_table());
        $ut = yellow_safe_table(yellow_media_user_download_table());
        $master = yellow_fetch_one('SELECT ' . yellow_media_public_cols() . " FROM {$mt} WHERE shortcode = ? LIMIT 1", array($shortcode));
        $user = array();
        if ($collector_id !== '') {
            $user = yellow_fetch_one('SELECT ' . yellow_download_public_cols() . " FROM {$ut} WHERE collector_id = ? AND shortcode = ? LIMIT 1", array($collector_id, $shortcode));
        }
        yellow_ok(array(
            'history_enabled' => 0,
            'message' => '현재는 master/user_download 현재 상태만 반환합니다.',
            'item' => $master ? $master : null,
            'user_download' => $user ? $user : null,
            'history' => array(),
        ));
    }

    yellow_error('invalid_mode', 400, array('mode' => $mode));

} catch (Exception $e) {
    yellow_error('server_exception', 500, yellow_exception_payload($e, $yellow_error_stage));
} catch (Throwable $e) {
    yellow_error('server_throwable', 500, yellow_exception_payload($e, $yellow_error_stage));
}
