#!/usr/bin/env python3
import sqlite3, csv, os, time, glob

DATA_DIR = '/var/www/api-own-data/V1data'
REGIONS = ['incheon', 'jeju', 'jeonbuk', 'jeonnam', 'sejong', 'seoul', 'ulsan']

print(f'남은 지역 변환: {REGIONS}')

for region in REGIONS:
    csv_path = f'{DATA_DIR}/V1data-{region}.csv'
    db_path  = f'{DATA_DIR}/V1data-{region}.db'
    if not os.path.exists(csv_path):
        print(f'  SKIP: {region} (CSV 없음)')
        continue
    if os.path.exists(db_path):
        print(f'  SKIP: {region} (DB 이미 있음)')
        continue

    size_mb = os.path.getsize(csv_path)/1024/1024
    print(f'\n[{region}] {size_mb:.0f}MB')
    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; 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:
                import csv as _csv
                c=next(_csv.reader([line]))
            except: continue
            if len(c)<17: continue
            pnu=c[0].strip()
            if len(pnu)!=19: continue
            try: price=int(c[16].strip().replace('\r','').replace(',',''))
            except: 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=[]
                if count%200000==0: print(f'  {count:,}건 {time.time()-t:.0f}s')
    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()
    cur.execute('CREATE INDEX idx_pnu ON v1 (pnu)')
    conn.commit(); conn.close()
    elapsed=time.time()-t
    db_mb=os.path.getsize(db_path)/1024/1024
    print(f'  완료: {count:,}건 | DB {db_mb:.0f}MB | {elapsed:.0f}s')
    # CSV 삭제 (공간 확보)
    os.remove(csv_path)
    print(f'  CSV 삭제 완료')

print('\n전체 완료!')