<?php
// 파일 위치: /var/www/html/i/yellow_func.php
// IG Sort Yellow v46 공통 함수
// - 무조건 JSON 응답용 API helper
// - token 인증
// - ig_sort DB 연결
// - ig_media 전역 마스터 + ig_media_user_download 사용자별 다운로드 상태 + ig_media_collect_event 통계 이벤트 기준

if (!defined('YELLOW_FUNC_LOADED')) {
    define('YELLOW_FUNC_LOADED', true);
}

if (!defined('YELLOW_VERSION')) {
    define('YELLOW_VERSION', 'v46.0.0-dev');
}

if (function_exists('date_default_timezone_set')) {
    date_default_timezone_set('Asia/Seoul');
}

// API 응답이 HTML warning으로 깨지지 않도록 기본은 숨김.
// 디버그가 필요하면 db_conf.php에서 define('YELLOW_DEBUG', true); 설정.
if (!defined('YELLOW_DEBUG')) {
    define('YELLOW_DEBUG', false);
}
if (!defined('YELLOW_API_MODE')) {
    define('YELLOW_API_MODE', true);
}
error_reporting(E_ALL);
ini_set('display_errors', YELLOW_DEBUG ? '1' : '0');

// 기존 DB 함수 파일을 우선 사용합니다.
// 이 파일은 같은 폴더의 db_conf.php를 require 합니다.
$__yellow_db_lib = __DIR__ . '/yy_db_conn.php';
$__yellow_db_conf = __DIR__ . '/db_config.php';

if (is_file($__yellow_db_lib)) {
    require_once $__yellow_db_lib;
} elseif (is_file($__yellow_db_conf)) {
    require_once $__yellow_db_conf;
} else {
    // 실제 에러 출력은 yellow_json_error에서 처리합니다.
}

function yellow_json($arr, $code = 200)
{
    if (!headers_sent()) {
        http_response_code((int)$code);
        header('Content-Type: application/json; charset=utf-8');
        header('Cache-Control: no-store, no-cache, must-revalidate, max-age=0');
    }

    echo json_encode($arr, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
    exit;
}

function yellow_ok($extra = array())
{
    if (!is_array($extra)) {
        $extra = array('data' => $extra);
    }

    yellow_json(array_merge(array(
        'ok' => 1,
        'version' => YELLOW_VERSION,
    ), $extra));
}

function yellow_error($error, $code = 400, $extra = array())
{
    if (!is_array($extra)) {
        $extra = array();
    }

    yellow_json(array_merge(array(
        'ok' => 0,
        'version' => YELLOW_VERSION,
        'error' => (string)$error,
    ), $extra), $code);
}

function yellow_read_json_body()
{
    static $cached = null;

    if ($cached !== null) {
        return $cached;
    }

    $cached = array();
    $raw = file_get_contents('php://input');

    if (is_string($raw) && trim($raw) !== '') {
        $data = json_decode($raw, true);
        if (is_array($data)) {
            $cached = $data;
        }
    }

    return $cached;
}

function yellow_req($key, $default = '')
{
    $body = yellow_read_json_body();

    if (isset($_POST[$key])) {
        return $_POST[$key];
    }
    if (isset($_GET[$key])) {
        return $_GET[$key];
    }
    if (isset($body[$key])) {
        return $body[$key];
    }

    return $default;
}

function yellow_req_array()
{
    $body = yellow_read_json_body();
    $out = array();

    foreach ($_GET as $k => $v) {
        $out[$k] = $v;
    }
    foreach ($_POST as $k => $v) {
        $out[$k] = $v;
    }
    if (is_array($body)) {
        foreach ($body as $k => $v) {
            $out[$k] = $v;
        }
    }

    return $out;
}

function yellow_str($key, $default = '')
{
    $v = yellow_req($key, $default);

    if (is_array($v) || is_object($v)) {
        return $default;
    }

    return trim((string)$v);
}

function yellow_int($key, $default = 0)
{
    $v = yellow_req($key, $default);

    if (is_array($v) || is_object($v)) {
        return (int)$default;
    }

    return (int)$v;
}

function yellow_token_from_header()
{
    $auth = '';

    if (isset($_SERVER['HTTP_AUTHORIZATION'])) {
        $auth = (string)$_SERVER['HTTP_AUTHORIZATION'];
    } elseif (isset($_SERVER['REDIRECT_HTTP_AUTHORIZATION'])) {
        $auth = (string)$_SERVER['REDIRECT_HTTP_AUTHORIZATION'];
    }

    if ($auth !== '' && preg_match('/Bearer\s+(.+)/i', $auth, $m)) {
        return trim($m[1]);
    }

    if (isset($_SERVER['HTTP_X_IG_TOKEN'])) {
        return trim((string)$_SERVER['HTTP_X_IG_TOKEN']);
    }

    return '';
}

function yellow_token()
{
    $token = yellow_str('token', '');

    if ($token !== '') {
        return $token;
    }

    return yellow_token_from_header();
}

function yellow_allowed_tokens()
{
    global $yellow_tokens, $YELLOW_TOKEN, $YELLOW_TOKENS;

    $tokens = array();

    if (defined('YELLOW_TOKEN')) {
        $tokens[] = (string)YELLOW_TOKEN;
    }

    if (defined('YELLOW_TOKENS')) {
        $parts = explode(',', (string)YELLOW_TOKENS);
        foreach ($parts as $p) {
            $p = trim($p);
            if ($p !== '') {
                $tokens[] = $p;
            }
        }
    }

    if (isset($YELLOW_TOKEN) && is_string($YELLOW_TOKEN) && trim($YELLOW_TOKEN) !== '') {
        $tokens[] = trim($YELLOW_TOKEN);
    }

    if (isset($YELLOW_TOKENS)) {
        if (is_array($YELLOW_TOKENS)) {
            foreach ($YELLOW_TOKENS as $p) {
                $p = trim((string)$p);
                if ($p !== '') {
                    $tokens[] = $p;
                }
            }
        } elseif (is_string($YELLOW_TOKENS)) {
            $parts = explode(',', $YELLOW_TOKENS);
            foreach ($parts as $p) {
                $p = trim($p);
                if ($p !== '') {
                    $tokens[] = $p;
                }
            }
        }
    }

    if (isset($yellow_tokens) && is_array($yellow_tokens)) {
        foreach ($yellow_tokens as $p) {
            $p = trim((string)$p);
            if ($p !== '') {
                $tokens[] = $p;
            }
        }
    }

    $tokens = array_values(array_unique(array_filter($tokens, function ($v) {
        return is_string($v) && trim($v) !== '';
    })));

    return $tokens;
}

function yellow_check_token($token)
{
    $token = trim((string)$token);

    if ($token === '') {
        return false;
    }

    $allowed = yellow_allowed_tokens();

    if (!$allowed) {
        return false;
    }

    foreach ($allowed as $a) {
        if (function_exists('hash_equals')) {
            if (hash_equals((string)$a, $token)) {
                return true;
            }
        } else {
            if ((string)$a === $token) {
                return true;
            }
        }
    }

    return false;
}

function yellow_must_token()
{
    $token = yellow_token();

    if (!yellow_check_token($token)) {
        yellow_error('invalid_token', 403, array(
            'token_configured' => count(yellow_allowed_tokens()) > 0 ? 1 : 0,
        ));
    }

    return $token;
}

function yellow_db_cfg_value($names, $default = '')
{
    global $dbconfig, $db_conf, $db_cfg, $DB_CONF;
    global $db_host, $db_user, $db_pass, $db_name, $db_port;

    $sources = array();

    if (isset($dbconfig) && is_array($dbconfig)) {
        $sources[] = $dbconfig;
    }
    if (isset($db_conf) && is_array($db_conf)) {
        $sources[] = $db_conf;
    }
    if (isset($db_cfg) && is_array($db_cfg)) {
        $sources[] = $db_cfg;
    }
    if (isset($DB_CONF) && is_array($DB_CONF)) {
        $sources[] = $DB_CONF;
    }

    foreach ($sources as $src) {
        foreach ($names as $n) {
            if (isset($src[$n]) && (string)$src[$n] !== '') {
                return $src[$n];
            }
        }
    }

    $var_map = array(
        'host' => isset($db_host) ? $db_host : null,
        'user' => isset($db_user) ? $db_user : null,
        'pass' => isset($db_pass) ? $db_pass : null,
        'password' => isset($db_pass) ? $db_pass : null,
        'db' => isset($db_name) ? $db_name : null,
        'name' => isset($db_name) ? $db_name : null,
        'port' => isset($db_port) ? $db_port : null,
    );

    foreach ($names as $n) {
        if (isset($var_map[$n]) && (string)$var_map[$n] !== '') {
            return $var_map[$n];
        }
    }

    $const_map = array(
        'host' => array('IG_DB_HOST', 'DB_HOST', 'MYSQL_HOST'),
        'user' => array('IG_DB_USER', 'DB_USER', 'MYSQL_USER'),
        'pass' => array('IG_DB_PASSWORD', 'DB_PASSWORD', 'MYSQL_PASSWORD', 'DB_PASS'),
        'password' => array('IG_DB_PASSWORD', 'DB_PASSWORD', 'MYSQL_PASSWORD', 'DB_PASS'),
        'db' => array('IG_DB_NAME', 'DB_NAME', 'MYSQL_DATABASE'),
        'name' => array('IG_DB_NAME', 'DB_NAME', 'MYSQL_DATABASE'),
        'port' => array('IG_DB_PORT', 'DB_PORT', 'MYSQL_PORT'),
    );

    foreach ($names as $n) {
        if (isset($const_map[$n])) {
            foreach ($const_map[$n] as $c) {
                if (defined($c) && (string)constant($c) !== '') {
                    return constant($c);
                }
            }
        }
    }

    return $default;
}

function yellow_db()
{
    static $m = null;
    global $g5;

    if ($m instanceof mysqli) {
        return $m;
    }

    if (isset($g5['connect_db']) && $g5['connect_db'] instanceof mysqli) {
        $m = $g5['connect_db'];
        return $m;
    }

    if (!function_exists('mysqli_connect')) {
        yellow_error('mysqli_not_available', 500);
    }

    $host = (string)yellow_db_cfg_value(array('host'), '127.0.0.1');
    $user = (string)yellow_db_cfg_value(array('user'), '');
    $pass = (string)yellow_db_cfg_value(array('pass', 'password'), '');
    $db   = (string)yellow_db_cfg_value(array('db', 'name'), 'ig_sort');
    $port = (int)yellow_db_cfg_value(array('port'), 3306);

    if ($user === '') {
        yellow_error('db_user_empty', 500);
    }

    if (function_exists('mysqli_report')) {
        mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
    }

    try {
        $m = new mysqli($host, $user, $pass, $db, $port);
        $m->set_charset('utf8mb4');
    } catch (Exception $e) {
        yellow_error('db_connect_failed', 500, array(
            'message' => YELLOW_DEBUG ? $e->getMessage() : 'hidden',
        ));
    }

    if (!isset($g5) || !is_array($g5)) {
        $g5 = array();
    }
    $g5['connect_db'] = $m;

    return $m;
}

function yellow_bind_params($stmt, $params)
{
    if (!$params) {
        return;
    }

    $types = '';
    $refs = array();

    foreach ($params as $k => $v) {
        if (is_int($v)) {
            $types .= 'i';
        } elseif (is_float($v)) {
            $types .= 'd';
        } else {
            // NULL은 MySQL NULL로 전달되어야 하므로 문자열로 강제 변환하지 않습니다.
            $types .= 's';
            if ($v !== null) {
                $params[$k] = (string)$v;
            }
        }
    }

    $refs[] = $types;
    foreach ($params as $k => $v) {
        $refs[] = &$params[$k];
    }

    call_user_func_array(array($stmt, 'bind_param'), $refs);
}

function yellow_fetch_all($sql, $params = array())
{
    $m = yellow_db();
    $stmt = $m->prepare($sql);
    yellow_bind_params($stmt, $params);
    $stmt->execute();
    $res = $stmt->get_result();

    if (!$res) {
        return array();
    }

    return $res->fetch_all(MYSQLI_ASSOC);
}

function yellow_fetch_one($sql, $params = array())
{
    $rows = yellow_fetch_all($sql, $params);
    return isset($rows[0]) ? $rows[0] : array();
}

function yellow_exec($sql, $params = array())
{
    $m = yellow_db();
    $stmt = $m->prepare($sql);
    yellow_bind_params($stmt, $params);
    $stmt->execute();
    return $stmt->affected_rows;
}

function yellow_table_exists($table)
{
    $table = yellow_safe_table($table);
    $row = yellow_fetch_one('SHOW TABLES LIKE ?', array($table));
    return !empty($row);
}


function yellow_table_columns($table)
{
    $table = yellow_safe_table($table);
    if (!yellow_table_exists($table)) {
        return array();
    }
    $rows = yellow_fetch_all("SHOW COLUMNS FROM {$table}");
    $out = array();
    foreach ($rows as $row) {
        if (!is_array($row) || !isset($row['Field'])) {
            continue;
        }
        $name = (string)$row['Field'];
        $out[$name] = array(
            'type' => isset($row['Type']) ? (string)$row['Type'] : '',
            'null' => isset($row['Null']) ? (string)$row['Null'] : '',
            'key' => isset($row['Key']) ? (string)$row['Key'] : '',
            'default' => array_key_exists('Default', $row) ? $row['Default'] : null,
            'extra' => isset($row['Extra']) ? (string)$row['Extra'] : '',
        );
    }
    return $out;
}

function yellow_schema_diagnostic($table, $required_columns)
{
    $table = yellow_safe_table($table);
    $required_columns = is_array($required_columns) ? $required_columns : array();
    $exists = yellow_table_exists($table);
    $columns = $exists ? yellow_table_columns($table) : array();
    $missing = array();
    foreach ($required_columns as $name) {
        $name = (string)$name;
        if ($name !== '' && !isset($columns[$name])) {
            $missing[] = $name;
        }
    }
    return array(
        'ok' => ($exists && count($missing) === 0) ? 1 : 0,
        'table' => $table,
        'table_exists' => $exists ? 1 : 0,
        'missing_columns' => $missing,
        'columns' => $columns,
    );
}

function yellow_media_schema_diagnostics()
{
    $master_required = array(
        'id', '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'
    );
    $download_required = array(
        'id', 'collector_id', 'shortcode', 'ig_account_id', 'save_folder', 'folder_name',
        'image_file_name', 'image_file_path', 'image_md5', 'image_sha256', 'image_size', 'image_status',
        'video_file_name', 'video_file_path', 'video_md5', 'video_sha256', 'video_size', 'video_status',
        'download_status', 'last_error', 'attempt_count', 'first_started_at', 'image_downloaded_at',
        'video_downloaded_at', 'completed_at', 'created_at', 'updated_at'
    );
    $event_required = array(
        'id', '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'
    );
    $tables = array(
        'master' => yellow_schema_diagnostic(yellow_media_base_table(), $master_required),
        'user_download' => yellow_schema_diagnostic(yellow_media_user_download_table(), $download_required),
        'collect_event' => yellow_schema_diagnostic(yellow_media_collect_event_table(), $event_required),
    );
    $missing = array();
    foreach ($tables as $stage => $diag) {
        if (empty($diag['ok'])) {
            $missing[$stage] = array(
                'table' => $diag['table'],
                'table_exists' => $diag['table_exists'],
                'missing_columns' => $diag['missing_columns'],
            );
        }
    }
    return array(
        'ok' => count($missing) === 0 ? 1 : 0,
        'tables' => $tables,
        'mismatches' => $missing,
    );
}

function yellow_exception_payload($e, $stage)
{
    $message = is_object($e) && method_exists($e, 'getMessage') ? (string)$e->getMessage() : 'unknown server error';
    $code = is_object($e) && method_exists($e, 'getCode') ? $e->getCode() : 0;
    return array(
        'error_stage' => (string)$stage,
        'error_class' => is_object($e) ? get_class($e) : gettype($e),
        'sql_error_code' => is_numeric($code) ? (int)$code : (string)$code,
        'message' => substr($message, 0, 2000),
    );
}

function yellow_safe_table($table)
{
    $table = (string)$table;

    if (!preg_match('/^[a-zA-Z0-9_]+$/', $table)) {
        yellow_error('bad_table_name', 500);
    }

    return $table;
}

function yellow_media_base_table()
{
    if (defined('YELLOW_MEDIA_TABLE') && constant('YELLOW_MEDIA_TABLE') !== '') {
        return yellow_safe_table((string)constant('YELLOW_MEDIA_TABLE'));
    }

    return 'ig_media';
}

function yellow_media_user_download_table()
{
    if (defined('YELLOW_MEDIA_USER_DOWNLOAD_TABLE') && constant('YELLOW_MEDIA_USER_DOWNLOAD_TABLE') !== '') {
        return yellow_safe_table((string)constant('YELLOW_MEDIA_USER_DOWNLOAD_TABLE'));
    }

    return 'ig_media_user_download';
}

function yellow_media_collect_event_table()
{
    if (defined('YELLOW_MEDIA_COLLECT_EVENT_TABLE') && constant('YELLOW_MEDIA_COLLECT_EVENT_TABLE') !== '') {
        return yellow_safe_table((string)constant('YELLOW_MEDIA_COLLECT_EVENT_TABLE'));
    }

    return 'ig_media_collect_event';
}

function yellow_media_table($shortcode = '')
{
    // 지금은 단일 테이블만 사용합니다.
    // 100만 건 이후 prefix 분리 시 이 함수만 수정하면 됩니다.
    // 예: if (strtoupper(substr($shortcode, 0, 1)) === 'S') return 'ig_media_s';
    return yellow_media_base_table();
}

function yellow_media_search_tables()
{
    // 향후 분할 테이블 사용 시 db_conf.php에서 배열로 지정 가능.
    // $yellow_media_tables = array('ig_media', 'ig_media_s');
    global $yellow_media_tables;

    $tables = array(yellow_media_base_table());

    if (isset($yellow_media_tables) && is_array($yellow_media_tables)) {
        foreach ($yellow_media_tables as $t) {
            $t = yellow_safe_table((string)$t);
            if ($t !== '') {
                $tables[] = $t;
            }
        }
    }

    return array_values(array_unique($tables));
}

function yellow_norm_datetime($v)
{
    $v = trim((string)$v);
    if ($v === '') {
        return null;
    }

    // ISO 문자열의 T/Z를 MySQL DATETIME 형태로 약하게 정리.
    $v = str_replace('T', ' ', $v);
    $v = preg_replace('/\.\d+Z$/', '', $v);
    $v = preg_replace('/Z$/', '', $v);

    return $v;
}

function yellow_norm_shortcode($shortcode, $media_url = '')
{
    $shortcode = trim((string)$shortcode);

    if ($shortcode !== '') {
        return $shortcode;
    }

    $media_url = trim((string)$media_url);

    if ($media_url !== '') {
        // URL에서 /p/, /reel/, /tv/ shortcode 추출 시도
        if (preg_match('#instagram\.com/(?:p|reel|tv)/([^/?#]+)#i', $media_url, $m)) {
            return trim($m[1]);
        }

        return 'url_' . substr(sha1($media_url), 0, 24);
    }

    return '';
}

function yellow_limit($value, $default = 100, $max = 500)
{
    $limit = (int)$value;
    if ($limit <= 0) {
        $limit = (int)$default;
    }
    if ($limit > $max) {
        $limit = (int)$max;
    }
    return $limit;
}

function yellow_sort_sql($sort)
{
    $sort = strtolower(trim((string)$sort));

    $map = array(
        'id' => 'id',
        'views' => 'views',
        'likes' => 'likes',
        'comments' => 'comments',
        'reposts' => 'reposts',
        'upload_at' => 'upload_at',
        'downloaded_at' => 'first_collected_at',
        'collected_at' => 'last_collected_at',
        'download_status' => 'download_status',
        'updated_at' => 'updated_at',
    );

    return isset($map[$sort]) ? $map[$sort] : 'id';
}

function yellow_media_public_cols()
{
    return 'id, 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';
}

function yellow_download_public_cols()
{
    return 'id, collector_id, shortcode, ig_account_id, save_folder, folder_name, image_file_name, image_file_path, image_md5, image_sha256, image_size, image_status, video_file_name, video_file_path, video_md5, video_sha256, video_size, video_status, download_status, last_error, attempt_count, first_started_at, image_downloaded_at, video_downloaded_at, completed_at, created_at, updated_at';
}

function yellow_collect_event_public_cols()
{
    return 'id, 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';
}
