#!/usr/bin/env python3
"""
migrate_to_sqlite.py
====================
members.csv → members.db (SQLite) への一回限りの移行スクリプト。

使い方:
    python3 migrate_to_sqlite.py

実行すると /var/www/html/members.db が作成される。
既存の members.csv はそのままバックアップとして残る。
"""

import sqlite3
import pandas as pd
import os
import shutil
from datetime import datetime

CSV_PATH = '/var/www/html/members.csv'
DB_PATH  = '/var/www/html/members.db'

# ---- 1. CSVを読む ----
print("[1] members.csv を読み込み中...")
for enc in ('utf-8-sig', 'cp932', 'utf-8'):
    try:
        df = pd.read_csv(CSV_PATH, encoding=enc, dtype=str).fillna('')
        print(f"    エンコーディング: {enc}, 行数: {len(df)}")
        break
    except UnicodeDecodeError:
        continue

# Unnamed: 7/8/9 → memo1/memo2/memo3 にリネーム（備考列として保持）
rename_map = {}
for col in df.columns:
    if col.startswith('Unnamed:'):
        rename_map[col] = f'memo{len(rename_map)+1}'
if rename_map:
    df = df.rename(columns=rename_map)
    print(f"    備考列をリネーム: {rename_map}")

print("    列一覧:", df.columns.tolist())

# ---- 2. DBを作成 ----
print("[2] SQLiteデータベースを作成中...")

if os.path.exists(DB_PATH):
    bak = DB_PATH + '.' + datetime.now().strftime('%Y%m%d_%H%M%S') + '.bak'
    shutil.copy2(DB_PATH, bak)
    print(f"    既存DB → {bak} にバックアップ")

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

cur.executescript("""
PRAGMA journal_mode = WAL;
PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS members (
    user_id                        TEXT PRIMARY KEY,
    password                       TEXT NOT NULL DEFAULT '',
    name1                          TEXT NOT NULL DEFAULT '',
    name2                          TEXT NOT NULL DEFAULT '',
    first_login                    TEXT NOT NULL DEFAULT '',
    last_login                     TEXT NOT NULL DEFAULT '',
    last_logout                    TEXT NOT NULL DEFAULT '',
    memo1                          TEXT NOT NULL DEFAULT '',
    memo2                          TEXT NOT NULL DEFAULT '',
    memo3                          TEXT NOT NULL DEFAULT '',
    webspeech_monthly_duration     TEXT NOT NULL DEFAULT '',
    webspeech_daily_duration       TEXT NOT NULL DEFAULT '',
    webspeech_daily_duration_limit TEXT NOT NULL DEFAULT '',
    api_monthly_duration           TEXT NOT NULL DEFAULT '',
    api_daily_duration             TEXT NOT NULL DEFAULT '',
    api_daily_duration_limit       TEXT NOT NULL DEFAULT '',
    expire_date                    TEXT NOT NULL DEFAULT '',
    version                        TEXT NOT NULL DEFAULT ''
);
""")

# ---- 3. データをINSERT ----
print("[3] データをインポート中...")
cols = [
    'user_id', 'password', 'name1', 'name2',
    'first_login', 'last_login', 'last_logout',
    'memo1', 'memo2', 'memo3',
    'webspeech_monthly_duration', 'webspeech_daily_duration',
    'webspeech_daily_duration_limit',
    'api_monthly_duration', 'api_daily_duration',
    'api_daily_duration_limit',
    'expire_date', 'version'
]

inserted = 0
skipped  = 0
for _, row in df.iterrows():
    vals = [str(row.get(c, '') or '').strip() for c in cols]
    if not vals[0]:
        skipped += 1
        continue
    cur.execute(f"""
        INSERT OR REPLACE INTO members ({','.join(cols)})
        VALUES ({','.join(['?']*len(cols))})
    """, vals)
    inserted += 1

conn.commit()
conn.close()

print(f"    挿入: {inserted} 行, スキップ: {skipped} 行")
print(f"[完了] {DB_PATH} を作成しました。")
print()
print("次のステップ:")
print("  1. app.py を新バージョンに差し替える")
print("  2. sudo systemctl restart kec-app  (or gunicorn 再起動)")
print("  3. 動作確認後、members.csv.bak_* を削除してOK")