<?php
include_once('./_common.php');

if ($is_guest) {
    alert('판매자센터만 이용가능합니다.', APMS_PARTNER_URL . '/login.php');
}

if ($is_admin !== 'super') {
    alert('최고관리자만 실행할 수 있습니다.', APMS_PARTNER_URL);
}

@set_time_limit(0);

$item_table = $g5['g5_shop_item_table'];

$dry_run = isset($_GET['dry_run']) ? (int)$_GET['dry_run'] : 1; // 기본은 dry-run
$apply = isset($_GET['apply']) ? (int)$_GET['apply'] : 0;
$with_backup = isset($_GET['with_backup']) ? (int)$_GET['with_backup'] : 1;
$limit = isset($_GET['limit']) ? (int)$_GET['limit'] : 0;
$owner_id = isset($_GET['owner_id']) ? trim((string)$_GET['owner_id']) : '';

if ($apply === 1) {
    $dry_run = 0;
}

$where = array();
$where[] = "(it_spec_sales_type = 'vpn-mall' or it_name like 'VPN-%' or ca_id in ('30','40') or ca_id2 like '30%' or ca_id2 like '40%' or ca_id3 like '30%' or ca_id3 like '40%')";
$where[] = "it_price >= 0";
$where[] = "(it_is_hour_sale_price <> it_price or it_is_hour_sale_price is null)";

if ($owner_id !== '') {
    $owner_id_sql = sql_real_escape_string($owner_id);
    $where[] = "(pt_id = '{$owner_id_sql}' or it_spec_partner_id = '{$owner_id_sql}')";
}

$where_sql = implode(' and ', $where);
$limit_sql = ($limit > 0) ? " limit {$limit}" : '';

$preview_sql = "
    select
        it_id,
        it_name,
        pt_id,
        it_spec_partner_id,
        it_billing_plan,
        it_price,
        it_is_hour_sale_price,
        it_update_time
    from {$item_table}
    where {$where_sql}
    order by it_update_time desc, it_id desc
    {$limit_sql}
";

$result = sql_query($preview_sql);
$rows = array();
while ($row = sql_fetch_array($result)) {
    $rows[] = $row;
}

$target_count = count($rows);

header('Content-Type: text/plain; charset=utf-8');

echo "=== VPN Hourly Canonical Migration ===\n";
echo "time       : " . G5_TIME_YMDHIS . "\n";
echo "table      : {$item_table}\n";
echo "dry_run    : {$dry_run}\n";
echo "apply      : {$apply}\n";
echo "with_backup: {$with_backup}\n";
echo "owner_id   : " . ($owner_id !== '' ? $owner_id : '-') . "\n";
echo "limit      : " . ($limit > 0 ? $limit : 'none') . "\n";
echo "targets    : {$target_count}\n";

if ($target_count < 1) {
    echo "\nNo target rows found. Nothing to do.\n";
    exit;
}

echo "\n--- Sample (up to 20 rows) ---\n";
$sample_max = min(20, $target_count);
for ($i = 0; $i < $sample_max; $i++) {
    $r = $rows[$i];
    echo sprintf(
        "[%d] it_id=%s | it_price=%s | it_is_hour_sale_price=%s | billing=%s | owner=%s\n",
        $i + 1,
        $r['it_id'],
        $r['it_price'],
        $r['it_is_hour_sale_price'],
        $r['it_billing_plan'],
        ($r['pt_id'] !== '' ? $r['pt_id'] : $r['it_spec_partner_id'])
    );
}

if ($dry_run === 1 || $apply !== 1) {
    echo "\n[DRY-RUN] No data changed.\n";
    echo "\n실행 가이드:\n";
    echo "1) 우선 dry-run 확인 (기본): ?dry_run=1\n";
    echo "2) 적용 실행: ?apply=1&with_backup=1\n";
    echo "3) 특정 판매자만 적용: ?apply=1&owner_id=셀러ID\n";
    echo "4) 일부만 시험 적용: ?apply=1&limit=100\n";
    exit;
}

$backup_table = '';
if ($with_backup === 1) {
    $backup_table = $item_table . '_vpn_hourly_bak_' . date('YmdHis');

    $sql_create_backup = "create table {$backup_table} like {$item_table}";
    sql_query($sql_create_backup);

    $sql_insert_backup = "
        insert into {$backup_table}
        select *
        from {$item_table}
        where {$where_sql}
        {$limit_sql}
    ";
    sql_query($sql_insert_backup);

    $backup_cnt_row = sql_fetch("select count(*) as cnt from {$backup_table}");
    $backup_cnt = (int)$backup_cnt_row['cnt'];

    echo "\nbackup_table: {$backup_table}\n";
    echo "backup_rows : {$backup_cnt}\n";
}

$update_sql = "
    update {$item_table}
       set it_is_hour_sale_price = it_price,
           it_billing_plan = case
               when lower(trim(it_billing_plan)) in ('hourly','monthly','yearly','usage') then lower(trim(it_billing_plan))
               when trim(it_billing_plan) = '' then 'monthly'
               else 'monthly'
           end,
           it_update_time = '" . G5_TIME_YMDHIS . "'
     where {$where_sql}
     {$limit_sql}
";
sql_query($update_sql);

$changed = sql_fetch("select row_count() as cnt");
$changed_cnt = (int)$changed['cnt'];

echo "\nupdated_rows: {$changed_cnt}\n";

if ($backup_table !== '') {
    echo "\n--- Rollback SQL (필요시) ---\n";
    echo "update {$item_table} a\n";
    echo "join {$backup_table} b on a.it_id = b.it_id\n";
    echo "   set a.it_price = b.it_price,\n";
    echo "       a.it_is_hour_sale_price = b.it_is_hour_sale_price,\n";
    echo "       a.it_billing_plan = b.it_billing_plan,\n";
    echo "       a.it_update_time = b.it_update_time;\n";
}

echo "\nDone.\n";
