#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
V1data CSV → SQLite 변환 (서버 실행용)
=========================================
- 각 지역별 CSV → V1data-{region}.db 생성
- pnu 컬럼에 인덱스 → 밀리초 조회
- 실행 위치: 1서버 (myland114.com)
"""
import sqlite3, csv, os, time, glob, sys

DATA_DIR = '/var/www/api-own-data/V1data'
csv_files = sorted(glob.glob(os.path.join(DATA_DIR, 'V1data-*.csv')))

print(f'★ V1data CSV → SQLite 변환 시작')
print(f'  대상: {len(csv_files)}개 파일')
print(f'  경로: {DATA_DIR}')
print('=' * 60)

total_start = time.time()
grand_total = 0

for csv_path in csv_files:
    region = os.path.basename(csv_path).replace('V1data-', '').replace('.csv', '')
    db_path = csv_path.replace('.csv', '.db')
    file_size_mb = os.path.getsize(csv_path) / 1024 / 1024

    print(f'\n[{region}] {file_size_mb:.0f}MB CSV → SQLite')

    # 기존 DB 삭제
    if os.path.exists(db_path):
        os.remove(db_path)

    conn = sqlite3.connect(db_path)
    cur  = conn.cursor()

    cur.execute('''
        CREATE TABLE v1 (
            id         INTEGER PRIMARY KEY AUTOINCREMENT,
            pnu        TEXT NOT NULL,
            dong_code  TEXT,
            dong_name  TEXT,
            ledger     TEXT,
            jibun      TEXT,
            zone_code  TEXT,
            zone_name  TEXT,
            jimok      TEXT,
            price_m2   INTEGER
        )
    ''')

    BATCH = 50000
    batch = []
    count = 0
    err_count = 0
    t = time.time()

    with open(csv_path, 'r', encoding='utf-8', errors='replace') as f:
        for line in f:
            line = line.strip()
            if not line:
                continue
            try:
                c = next(csv.reader([line]))
            except Exception:
                err_count += 1
                continue

            if len(c) < 17:
                err_count += 1
                continue

            pnu = c[0].strip()
            if len(pnu) != 19:
                err_count += 1
                continue

            try:
                price = int(c[16].strip().replace('\r', '').replace(',', ''))
            except Exception:
                price = 0

            batch.append((
                pnu,
                c[1].strip(),
                c[2].strip(),
                c[4].strip(),
                c[5].strip(),
                c[9].strip(),
                c[10].strip(),
                c[15].strip(),
                price
            ))

            if len(batch) >= BATCH:
                cur.executemany(
                    'INSERT INTO v1 (pnu,dong_code,dong_name,ledger,jibun,zone_code,zone_name,jimok,price_m2) VALUES (?,?,?,?,?,?,?,?,?)',
                    batch
                )
                count += len(batch)
                batch = []
                # 진행상황 출력 (10만건마다)
                if count % 100000 == 0:
                    elapsed = time.time() - t
                    speed = count / elapsed / 1000
                    print(f'  {count:>8,}건  {elapsed:.0f}초  {speed:.1f}k/s')

    # 나머지 INSERT
    if batch:
        cur.executemany(
            'INSERT INTO v1 (pnu,dong_code,dong_name,ledger,jibun,zone_code,zone_name,jimok,price_m2) VALUES (?,?,?,?,?,?,?,?,?)',
            batch
        )
        count += len(batch)

    conn.commit()

    # PNU 인덱스 생성
    print(f'  인덱스 생성 중...')
    cur.execute('CREATE INDEX idx_pnu ON v1 (pnu)')
    conn.commit()
    conn.close()

    elapsed = time.time() - t
    db_size_mb = os.path.getsize(db_path) / 1024 / 1024
    grand_total += count

    print(f'  ✅ 완료: {count:,}건 | DB {db_size_mb:.0f}MB | {elapsed:.0f}초')
    if err_count > 0:
        print(f'  ⚠️  오류 라인: {err_count}건 (건너뜀)')

    # 빠른 검색 테스트
    conn = sqlite3.connect(db_path)
    cur = conn.cursor()
    cur.execute('SELECT COUNT(DISTINCT pnu) FROM v1')
    unique_pnu = cur.fetchone()[0]
    # 첫 번째 PNU로 속도 테스트
    cur.execute('SELECT pnu FROM v1 LIMIT 1')
    row = cur.fetchone()
    if row:
        test_pnu = row[0]
        t2 = time.time()
        cur.execute('SELECT * FROM v1 WHERE pnu=?', (test_pnu,))
        results = cur.fetchall()
        ms = (time.time() - t2) * 1000
        print(f'  📊 고유PNU: {unique_pnu:,}개 | 검색테스트: {ms:.2f}ms ({len(results)}건)')
    conn.close()

total_elapsed = time.time() - total_start
print(f'\n{"=" * 60}')
print(f'★ 전체 완료!')
print(f'  총 레코드: {grand_total:,}건')
print(f'  소요 시간: {total_elapsed/60:.1f}분')
print(f'  저장 위치: {DATA_DIR}/V1data-*.db')
print(f'{"=" * 60}')