<?php
/**
 * 상권 분석 대시보드 API
 * /store/dashboard_api.php
 * action 파라미터로 각 데이터 반환
 */

header('Content-Type: application/json; charset=utf-8');
header('Access-Control-Allow-Origin: *');

// ── DB 설정 ──────────────────────────────────────
define('DB_HOST', 'localhost');
define('DB_NAME', 'store');
define('DB_USER', 'store_user');
define('DB_PASS', 'StorePass1!');
define('DB_PORT', 3306);

// ── DB 연결 ──────────────────────────────────────
$socket_list = array(
    '/var/run/mysqld/mysqld.sock',
    '/tmp/mysql.sock',
    '/var/lib/mysql/mysql.sock',
    '/run/mysqld/mysqld.sock',
);
$socket = null;
foreach ($socket_list as $s) {
    if (file_exists($s)) { $socket = $s; break; }
}

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
try {
    if ($socket) {
        $db = new mysqli('localhost', DB_USER, DB_PASS, DB_NAME, 0, $socket);
    } else {
        $db = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME, DB_PORT);
    }
    $db->set_charset('utf8mb4');
} catch (Exception $e) {
    http_response_code(500);
    echo json_encode(array('error' => 'DB 연결 실패: ' . $e->getMessage()));
    exit;
}

// ── 공통 함수 ─────────────────────────────────────
function q($db, $sql) {
    $res = $db->query($sql);
    if (!$res) return array();
    $rows = array();
    while ($r = $res->fetch_assoc()) $rows[] = $r;
    return $rows;
}

function esc($db, $v) {
    return $db->real_escape_string($v);
}

function resp($data) {
    echo json_encode($data, JSON_UNESCAPED_UNICODE | JSON_NUMERIC_CHECK);
    exit;
}

// ── 파라미터 ──────────────────────────────────────
$action    = isset($_GET['action'])    ? $_GET['action']    : '';
$zone1     = isset($_GET['zone1'])     ? esc($db, $_GET['zone1']) : '';
$zone2     = isset($_GET['zone2'])     ? esc($db, $_GET['zone2']) : '';
$zone3     = isset($_GET['zone3'])     ? esc($db, $_GET['zone3']) : '';
$class1    = isset($_GET['class1'])    ? esc($db, $_GET['class1']) : '';
$class2    = isset($_GET['class2'])    ? esc($db, $_GET['class2']) : '';
$stat_date = isset($_GET['stat_date']) ? esc($db, $_GET['stat_date']) : '2026-05';
$cym_from  = isset($_GET['cym_from'])  ? esc($db, $_GET['cym_from']) : '202601';
$cym_to    = isset($_GET['cym_to'])    ? esc($db, $_GET['cym_to'])   : '202612';

// zone 조건 빌더
function zone_where($zone1, $zone2, $zone3, $tbl = 's') {
    $w = array();
    if ($zone1) $w[] = "{$tbl}.zone1_name = '{$zone1}'";
    if ($zone2) $w[] = "{$tbl}.zone2_name = '{$zone2}'";
    if ($zone3) $w[] = "{$tbl}.zone3_name = '{$zone3}'";
    return $w ? implode(' AND ', $w) : '1=1';
}

// ═══════════════════════════════════════════════════
// action 라우팅
// ═══════════════════════════════════════════════════
switch ($action) {

// ── 1. 시도 목록 ─────────────────────────────────
case 'zone1_list':
    resp(q($db, "
        SELECT DISTINCT zone1_name
        FROM store_tbl
        WHERE zone1_name IS NOT NULL AND zone1_name != ''
        ORDER BY zone1_name
    "));

// ── 2. 시군구 목록 ───────────────────────────────
case 'zone2_list':
    $w = $zone1 ? "WHERE zone1_name = '{$zone1}'" : "WHERE 1=1";
    resp(q($db, "
        SELECT DISTINCT zone2_name
        FROM store_tbl
        {$w} AND zone2_name IS NOT NULL AND zone2_name != ''
        ORDER BY zone2_name
    "));

// ── 3. 읍면동 목록 ───────────────────────────────
case 'zone3_list':
    $w = array("zone3_name IS NOT NULL", "zone3_name != ''");
    if ($zone1) $w[] = "zone1_name = '{$zone1}'";
    if ($zone2) $w[] = "zone2_name = '{$zone2}'";
    resp(q($db, "
        SELECT DISTINCT zone3_name
        FROM store_tbl
        WHERE " . implode(' AND ', $w) . "
        ORDER BY zone3_name
    "));

// ── 4. 업종 대분류 목록 ──────────────────────────
case 'class1_list':
    resp(q($db, "
        SELECT DISTINCT store_class1_name AS name
        FROM store_tbl
        WHERE store_class1_name IS NOT NULL AND store_class1_name != ''
        ORDER BY store_class1_name
    "));

// ── 5. 업종 중분류 목록 ──────────────────────────
case 'class2_list':
    $w = $class1 ? "AND store_class1_name = '{$class1}'" : '';
    resp(q($db, "
        SELECT DISTINCT store_class2_name AS name
        FROM store_tbl
        WHERE store_class2_name IS NOT NULL {$w}
        ORDER BY store_class2_name
    "));

// ── 6. 요약 KPI ─────────────────────────────────
case 'kpi':
    $zw  = zone_where($zone1, $zone2, $zone3);
    $cw1 = $class1 ? "AND store_class1_name = '{$class1}'" : '';
    $cw2 = $class2 ? "AND store_class2_name = '{$class2}'" : '';

    // 업소 수
    $store_cnt = q($db, "
        SELECT COUNT(DISTINCT store_no) AS cnt
        FROM store_tbl s
        WHERE {$zw} {$cw1} {$cw2}
    ");

    // 인구
    // zone3 선택 시: 해당 읍면동(admin_level=3) 단일 행
    // zone2 선택 시: 해당 시군구의 읍면동 합계(admin_level=3, zone2_name=zone2)
    // zone1 선택 시: 해당 시도 내 시군구 합계행(admin_level=2, zone3_name='') 합산
    // 전체 선택 시: 전국 시군구 합계행(admin_level=2, zone3_name='') 합산
    if ($zone3) {
        // 읍면동 단위 조회
        $pop_where = "zone3_name = '{$zone3}' AND admin_level = 3";
        $pop_sum   = "total_pop, age_20_29+age_30_39 AS pop_2030";
        $pop_agg   = false;
    } elseif ($zone2) {
        // 시군구 내 읍면동 합산 (admin_level=3, zone3_name != '')
        $pop_where = "zone2_name = '{$zone2}' AND admin_level = 3 AND zone3_name != ''";
        $pop_sum   = "SUM(total_pop) AS total_pop, SUM(age_20_29+age_30_39) AS pop_2030";
        $pop_agg   = true;
    } elseif ($zone1) {
        // 시도 내 시군구 합계행 합산 (admin_level=2, zone3_name='')
        $pop_where = "zone1_name = '{$zone1}' AND admin_level = 2 AND zone3_name = ''";
        $pop_sum   = "SUM(total_pop) AS total_pop, SUM(age_20_29+age_30_39) AS pop_2030";
        $pop_agg   = true;
    } else {
        // 전국: 시군구 합계행 합산 (admin_level=2, zone3_name='')
        $pop_where = "admin_level = 2 AND zone3_name = ''";
        $pop_sum   = "SUM(total_pop) AS total_pop, SUM(age_20_29+age_30_39) AS pop_2030";
        $pop_agg   = true;
    }
    $pop = q($db, "
        SELECT {$pop_sum}
        FROM population_tbl
        WHERE stat_date = '{$stat_date}' AND {$pop_where}
    ");
    $pop_total  = isset($pop[0]['total_pop'])  ? (int)$pop[0]['total_pop']  : 0;
    $pop_2030   = isset($pop[0]['pop_2030'])   ? (int)$pop[0]['pop_2030']   : 0;

    // 매매 건수/평균가
    $trade_cond = array();
    if ($zone1) $trade_cond[] = "zone1_name = '{$zone1}'";
    if ($zone3) $trade_cond[] = "zone3_name = '{$zone3}'";
    $trade_where = $trade_cond ? implode(' AND ', $trade_cond) : '1=1';
    $trade = q($db, "
        SELECT COUNT(*) AS cnt, ROUND(AVG(price_man)) AS avg_price
        FROM commercial_trade_tbl
        WHERE {$trade_where}
          AND contract_ym BETWEEN '{$cym_from}' AND '{$cym_to}'
          AND price_man IS NOT NULL
    ");

    // 포화도 (전체 기준)
    $sat = q($db, "
        SELECT
            s.zone3_name,
            COUNT(DISTINCT s.store_no) AS store_cnt,
            MAX(p.total_pop) AS pop,
            ROUND(COUNT(DISTINCT s.store_no)*1000.0/NULLIF(MAX(p.total_pop),0),2) AS saturation
        FROM store_tbl s
        LEFT JOIN population_tbl p
            ON s.zone3_name = p.zone3_name
           AND p.stat_date = '{$stat_date}'
           AND p.admin_level = 3
           AND p.zone3_name != ''
        WHERE {$zw} {$cw1} {$cw2}
        GROUP BY s.zone3_name
        ORDER BY saturation DESC
        LIMIT 1
    ");

    resp(array(
        'store_cnt'    => isset($store_cnt[0]['cnt'])     ? (int)$store_cnt[0]['cnt']     : 0,
        'total_pop'    => $pop_total,
        'pop_2030'     => $pop_2030,
        'trade_cnt'    => isset($trade[0]['cnt'])         ? (int)$trade[0]['cnt']         : 0,
        'avg_price'    => isset($trade[0]['avg_price'])   ? (int)$trade[0]['avg_price']   : 0,
        'top_sat_zone' => isset($sat[0]['zone3_name'])    ? $sat[0]['zone3_name']         : '-',
        'top_sat_val'  => isset($sat[0]['saturation'])    ? (float)$sat[0]['saturation']  : 0,
    ));

// ── 7. 업종 대분류별 업소 수 (도넛 차트) ────────
case 'class1_chart':
    $zw = zone_where($zone1, $zone2, $zone3);
    resp(q($db, "
        SELECT store_class1_name AS label, COUNT(DISTINCT store_no) AS cnt
        FROM store_tbl s
        WHERE {$zw}
          AND store_class1_name IS NOT NULL
        GROUP BY store_class1_name
        ORDER BY cnt DESC
    "));

// ── 8. 읍면동별 포화도 (Bar 차트) ───────────────
case 'saturation_chart':
    $cw1 = $class1 ? "AND s.store_class1_name = '{$class1}'" : '';
    $cw2 = $class2 ? "AND s.store_class2_name = '{$class2}'" : '';
    $zw  = $zone1  ? "AND s.zone1_name = '{$zone1}'"         : '';
    resp(q($db, "
        SELECT
            s.zone3_name,
            COUNT(DISTINCT s.store_no)                                          AS store_cnt,
            MAX(p.total_pop)                                                    AS pop,
            ROUND(COUNT(DISTINCT s.store_no)*1000.0/NULLIF(MAX(p.total_pop),0),2) AS saturation,
            CASE
                WHEN COUNT(DISTINCT s.store_no)*1000.0/NULLIF(MAX(p.total_pop),0) >= 5 THEN '포화'
                WHEN COUNT(DISTINCT s.store_no)*1000.0/NULLIF(MAX(p.total_pop),0) >= 3 THEN '주의'
                ELSE '안정'
            END AS grade
        FROM store_tbl s
        LEFT JOIN population_tbl p
            ON s.zone3_name = p.zone3_name
           AND p.stat_date = '{$stat_date}'
           AND p.admin_level = 3
           AND p.zone3_name != ''
        WHERE 1=1 {$zw} {$cw1} {$cw2}
          AND s.zone3_name IS NOT NULL
          AND p.total_pop > 0
        GROUP BY s.zone3_name
        ORDER BY saturation DESC
        LIMIT 20
    "));

// ── 9. 월별 업소 증감 트렌드 (Line 차트) ────────
case 'trend_chart':
    $cw1 = $class1 ? "AND store_class1_name = '{$class1}'" : '';
    $zw  = $zone1  ? "AND zone1_name = '{$zone1}'"         : '';
    // store_tbl create_date 기준 월별 집계
    resp(q($db, "
        SELECT
            DATE_FORMAT(create_date, '%Y-%m') AS ym,
            store_class1_name                 AS label,
            COUNT(DISTINCT store_no)          AS cnt
        FROM store_tbl
        WHERE 1=1 {$zw} {$cw1}
        GROUP BY ym, store_class1_name
        ORDER BY ym, cnt DESC
    "));

// ── 10. 읍면동별 매매가 현황 (Bar) ──────────────
case 'price_chart':
    $zw = $zone1 ? "AND zone1_name = '{$zone1}'" : '';
    resp(q($db, "
        SELECT
            zone3_name,
            COUNT(*)               AS cnt,
            ROUND(AVG(price_man))  AS avg_price,
            MIN(price_man)         AS min_price,
            MAX(price_man)         AS max_price
        FROM commercial_trade_tbl
        WHERE zone3_name IS NOT NULL AND zone3_name != ''
          AND price_man IS NOT NULL
          AND contract_ym BETWEEN '{$cym_from}' AND '{$cym_to}'
          {$zw}
        GROUP BY zone3_name
        ORDER BY avg_price DESC
        LIMIT 15
    "));

// ── 11. 지도용 마커 데이터 ───────────────────────
case 'map_markers':
    $zw  = zone_where($zone1, $zone2, $zone3);
    $cw1 = $class1 ? "AND store_class1_name = '{$class1}'" : '';
    $cw2 = $class2 ? "AND store_class2_name = '{$class2}'" : '';
    $limit = isset($_GET['limit']) ? (int)$_GET['limit'] : 500;
    if ($limit > 2000) $limit = 2000;

    resp(q($db, "
        SELECT
            store_no, store_name,
            store_class1_name, store_class2_name,
            zone3_name, road_addr,
            lng, lat
        FROM store_tbl s
        WHERE {$zw} {$cw1} {$cw2}
          AND lng IS NOT NULL AND lat IS NOT NULL
          AND lng != 0 AND lat != 0
        ORDER BY RAND()
        LIMIT {$limit}
    "));

// ── 12. 읍면동 클릭 팝업 상세 ───────────────────
case 'zone_detail':
    if (!$zone3) { resp(array('error' => 'zone3 필요')); }

    $z3 = $zone3;

    $stores = q($db, "
        SELECT store_class1_name AS label, COUNT(DISTINCT store_no) AS cnt
        FROM store_tbl
        WHERE zone3_name = '{$z3}'
        GROUP BY store_class1_name ORDER BY cnt DESC
    ");

    $pop = q($db, "
        SELECT total_pop, male_pop, female_pop,
               age_20_29, age_30_39, age_40_49, age_50_59,
               age_60_69, age_70_79
        FROM population_tbl
        WHERE zone3_name = '{$z3}'
          AND stat_date = '{$stat_date}'
          AND admin_level = 3
          AND zone3_name != ''
        LIMIT 1
    ");

    $trade = q($db, "
        SELECT COUNT(*) AS cnt, ROUND(AVG(price_man)) AS avg_price
        FROM commercial_trade_tbl
        WHERE zone3_name = '{$z3}'
          AND contract_ym BETWEEN '{$cym_from}' AND '{$cym_to}'
          AND price_man IS NOT NULL
    ");

    resp(array(
        'zone3'  => $z3,
        'stores' => $stores,
        'pop'    => isset($pop[0]) ? $pop[0] : null,
        'trade'  => isset($trade[0]) ? $trade[0] : null,
    ));

// ── 13. 창업 적합도 TOP10 ────────────────────────
case 'suitability':
    $cw1 = $class1 ? "AND s.store_class1_name = '{$class1}'" : '';
    $zw  = $zone1  ? "AND s.zone1_name = '{$zone1}'"         : '';
    resp(q($db, "
        SELECT
            s.zone3_name,
            COUNT(DISTINCT s.store_no)                                               AS store_cnt,
            MAX(p.total_pop)                                                         AS pop,
            MAX(p.age_20_29)+MAX(p.age_30_39)                                        AS pop_2030,
            ROUND(COUNT(DISTINCT s.store_no)*1000.0/NULLIF(MAX(p.total_pop),0), 2)  AS saturation,
            /* 적합도: 포화도 낮을수록 + 2030인구 많을수록 높음 */
            ROUND(
                (1 - LEAST(COUNT(DISTINCT s.store_no)*1000.0/NULLIF(MAX(p.total_pop),0)/10, 1)) * 60
                + LEAST((MAX(p.age_20_29)+MAX(p.age_30_39)) / 5000.0, 1) * 40
            , 1) AS score
        FROM store_tbl s
        LEFT JOIN population_tbl p
            ON s.zone3_name = p.zone3_name
           AND p.stat_date = '{$stat_date}'
           AND p.admin_level = 3
           AND p.zone3_name != ''
        WHERE p.total_pop > 500
          AND s.zone3_name IS NOT NULL {$zw} {$cw1}
        GROUP BY s.zone3_name
        HAVING saturation IS NOT NULL
        ORDER BY score DESC
        LIMIT 10
    "));

default:
    resp(array('error' => '잘못된 action: ' . $action));
}