import pandas as pd
import csv
from flask import Flask, render_template, jsonify, request, session, redirect, url_for, make_response
from flask_cors import CORS
import os
from datetime import datetime
import threading
import requests
from dotenv import load_dotenv
from flask import send_file

# ------------------------------------------------------------
# 基本設定（★最重要：app を route 定義より先に生成）
# ------------------------------------------------------------
load_dotenv()
app = Flask(__name__)

# ★ Electron(file://) からでもセッションCookieを送れるようにする
app.config.update(
    SESSION_COOKIE_SAMESITE="None",
    SESSION_COOKIE_SECURE=True
)

CORS(app, supports_credentials=True,
     origins=["https://kec-apps.com:443", "https://kec-apps.com"])

app.secret_key = os.getenv("FLASK_SECRET_KEY", "kecapps_default_key_2025")

HTML_DIR = '/var/www/html/templates'
CSV_DIR = '/var/www/html'
members_csv_path = os.path.join(CSV_DIR, 'members.csv')
csv_lock = threading.Lock()

# ------------------------------------------------------------
# ★ 全レスポンスを no-store にする（スーパーリロード相当）
# ------------------------------------------------------------
@app.after_request
def add_no_store_headers(response):
    response.headers.setdefault(
        "Cache-Control",
        "no-store, no-cache, must-revalidate, max-age=0"
    )
    response.headers.setdefault("Pragma", "no-cache")
    response.headers.setdefault("Expires", "0")
    return response

# ------------------------------------------------------------
# ダウンロード用ルート（dmg, exe, zipファイル）
# ------------------------------------------------------------
@app.route('/downloads/<filename>')
def download_file(filename):
    """
    ダウンロードファイル用の専用ルート
    gzip圧縮を無効化し、適切なヘッダーを設定
    """
    # 許可するファイル拡張子
    allowed_extensions = {'.dmg', '.exe', '.zip', '.pkg', '.app'}
    file_ext = os.path.splitext(filename)[1].lower()

    if file_ext not in allowed_extensions:
        return jsonify({"error": "File type not allowed"}), 403

    # ファイルパス
    filepath = os.path.join('/var/www/html/static/downloads', filename)

    # ファイル存在チェック
    if not os.path.exists(filepath):
        return jsonify({"error": "File not found"}), 404

    # レスポンスを作成
    response = make_response(send_file(
        filepath,
        as_attachment=True,
        download_name=filename,
        mimetype='application/octet-stream'
    ))
    # ★ 重要：ダウンロード用の適切なヘッダーを設定
    response.headers['Content-Type'] = 'application/octet-stream'
    response.headers['Content-Disposition'] = f'attachment; filename="{filename}"'
    response.headers['Content-Transfer-Encoding'] = 'binary'
    response.headers['Cache-Control'] = 'no-cache, no-store, must-revalidate'
    # ★ gzip圧縮を無効化（これが重要！）
    response.direct_passthrough = True
    return response


# ------------------------------------------------------------
# CSV操作
# ------------------------------------------------------------
def load_csv():
    user_id = session.get('user_id')
    if not user_id:
        return []

    if len(str(user_id)) <= 10:
        target_csv = os.path.join(CSV_DIR, 'scriptdata.csv')
    else:
        target_csv = os.path.join(CSV_DIR, 'scriptdata2.csv')

    try:
        df = pd.read_csv(target_csv, encoding='utf-8')
        df = df.fillna('')
        return df.to_dict(orient='records')
    except:
        return []


# ------------------------------------------------------------
# members.csv 固定列定義（列名・順序は常にこの定義に従う）
# ------------------------------------------------------------
MEMBERS_COLUMNS = [
    'user_id', 'password', 'name1', 'name2',
    'first_login', 'last_login', 'last_logout',
    'Unnamed: 7', 'Unnamed: 8', 'Unnamed: 9',
    'webspeech_monthly_duration', 'webspeech_daily_duration',
    'webspeech_daily_duration_limit',
    'api_monthly_duration', 'api_daily_duration',
    'api_daily_duration_limit',
    'expire_date', 'version'
]

# usage/log で書き換えてよい列のみ（それ以外は絶対に書き換えない）
DURATION_WRITABLE_COLS = {
    'webspeech_monthly_duration', 'webspeech_daily_duration',
    'api_monthly_duration', 'api_daily_duration',
}


# ------------------------------------------------------------
# CSV 読み書きヘルパー
# ------------------------------------------------------------
def read_members_csv():
    """
    members.csv を読み込む。
    UTF-8(BOM付き) → Shift-JIS(cp932) → UTF-8 の順に試みる。
    列名は固定（MEMBERS_COLUMNS）のため正規化不要。
    """
    for enc in ('utf-8-sig', 'cp932', 'utf-8'):
        try:
            return pd.read_csv(members_csv_path, encoding=enc, dtype=str).fillna('')
        except UnicodeDecodeError:
            continue
    raise ValueError(f"Cannot decode members.csv")


def write_members_csv(df):
    """
    members.csv をBOM付きUTF-8で保存する。
    列順をMEMBERS_COLUMNSに固定して書き出す。
    """
    # 固定列順で書き出す（余分な列は無視、足りない列は空文字で補完）
    for col in MEMBERS_COLUMNS:
        if col not in df.columns:
            df[col] = ''
    df[MEMBERS_COLUMNS].to_csv(members_csv_path, index=False, encoding="utf-8-sig")


# 後方互換
def read_csv_auto(path):
    if path == members_csv_path:
        return read_members_csv()
    for enc in ('utf-8-sig', 'cp932', 'utf-8'):
        try:
            return pd.read_csv(path, encoding=enc)
        except UnicodeDecodeError:
            continue
    raise ValueError(f"Cannot decode CSV: {path}")


def load_members_csv():
    return read_members_csv()


# 認証
# ------------------------------------------------------------
def check_credentials(user_id, password):
    df = read_csv_auto(members_csv_path)
    user = df[df['user_id'] == user_id]
    if not user.empty and user.iloc[0]['password'] == password:
        return True
    return False


def update_login_time(user_id):
    with csv_lock:
        df = read_members_csv()
        if user_id not in df['user_id'].values:
            return False
        now = _jst_now().strftime('%Y-%m-%d %H:%M:%S')
        mask = df['user_id'] == user_id
        df.loc[mask, 'last_login'] = now
        # dtype=str で読むと空セルは '' になるため pd.isna() ではなく空文字チェック
        if str(df.loc[mask, 'first_login'].iloc[0]).strip() == '':
            df.loc[mask, 'first_login'] = now
        write_members_csv(df)
    return True


def update_logout_time(user_id):
    with csv_lock:
        df = read_csv_auto(members_csv_path)
        if user_id not in df['user_id'].values:
            return False
        now = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        df.loc[df['user_id'] == user_id, 'last_logout'] = now
        write_members_csv(df)
    return True


# ------------------------------------------------------------
# ルート
# ------------------------------------------------------------
@app.route('/')
def index():
    if 'user_id' in session:
        return render_template('index.html')
    return redirect(url_for('login'))


@app.route('/login', methods=['GET', 'POST'])
def login():
    if request.method == 'POST':
        user_id = request.form['user_id'].strip()
        password = request.form['password'].strip()

        if check_credentials(user_id, password):
            session['user_id'] = user_id
            update_login_time(user_id)
            return redirect(url_for('index'))
        return "ユーザーIDまたはパスワードが違います。", 401
    return render_template('login.html')


@app.route('/logout', methods=['POST'])
def logout():
    if 'user_id' in session:
        update_logout_time(session['user_id'])
        session.pop('user_id', None)
        return jsonify({'message': 'ログアウト成功'}), 200
    return jsonify({'message': 'ログインしていません'}), 401


# ------------------------------------------------------------
# 名前取得
# ------------------------------------------------------------
@app.route('/members')
def get_members():
    if 'user_id' in session:
        df = load_members_csv()
        user_row = df[df['user_id'] == session['user_id']]
        if user_row.empty:
            return jsonify([])
        def _safe(val):
            import math, re
            if val is None:
                return ''
            try:
                if math.isnan(float(val)):
                    return ''
            except (TypeError, ValueError):
                pass
            s = str(val).strip()
            if s.lower() == 'nan':
                return ''
            # ★ duration値のゴミデータ（日付パターンを含む）を除外
            if re.search(r'\d{4}-\d{2}-\d{2}', s) or re.search(r'\(\d{4}-\d{2}', s):
                return ''
            return s
        def _safe_date(val):
            import math
            if val is None:
                return ''
            try:
                if math.isnan(float(val)):
                    return ''
            except (TypeError, ValueError):
                pass
            s = str(val).strip()
            if s.lower() in ('nan', 'none', ''):
                return ''
            return s
        name1       = _safe(user_row.iloc[0].get('name1', ''))
        name2       = _safe(user_row.iloc[0].get('name2', ''))
        version     = str(user_row.iloc[0].get('version', '') or '').strip().lower()
        expire_date = _safe_date(user_row.iloc[0].get('expire_date', ''))
        return jsonify([{'name1': name1, 'name2': name2, 'version': version, 'expire_date': expire_date}])
    return "Unauthorized", 401


# ------------------------------------------------------------
# 名前登録
# ------------------------------------------------------------
@app.route('/submit', methods=['POST'])
def submit_name():
    if 'user_id' not in session:
        return jsonify({"error": "Unauthorized"}), 401

    data = request.get_json(silent=True)
    if not data:
        return jsonify({"error": "No data"}), 400

    name1 = str(data.get('name1', '')).strip()
    name2 = str(data.get('name2', '')).strip()

    if not name1 or not name2:
        return jsonify({"error": "Both name1 and name2 are required"}), 400

    with csv_lock:
        df = read_csv_auto(members_csv_path)
        mask = df['user_id'] == session['user_id']
        if not mask.any():
            return jsonify({"error": "User not found"}), 404
        df.loc[mask, 'name1'] = name1
        df.loc[mask, 'name2'] = name2
        write_members_csv(df)

    return jsonify({"message": "名前登録完了", "name1": name1, "name2": name2}), 200


# ------------------------------------------------------------
# Free Conversation
# ------------------------------------------------------------
@app.route('/grant_access', methods=['POST'])
def grant_access():
    session['access_granted'] = True
    return redirect(url_for('conv2'))


@app.route('/conv2')
def conv2():
    if not session.get('access_granted', False):
        return redirect('https://kec-apps.com:443/')
    return render_template('conv2.html')


# ------------------------------------------------------------
# ★ SPA対応：レッスンページ（完全なHTML + キャッシュ無効化）
# ------------------------------------------------------------
@app.route("/intro1")
@app.route("/intro2")
@app.route("/basic1")
@app.route("/basic2")
@app.route("/beginner1-1")
@app.route("/beginner1-2")
@app.route("/beginner1-3")
@app.route("/beginner2")
@app.route("/beginner3")
@app.route("/beginner4")
@app.route("/beginner5")
@app.route("/beginner6")
def render_lesson():
    """
    SPA版用：完全なHTMLを返す
    キャッシュを無効化してService Workerの問題を回避
    """
    page = request.path.strip("/")
    html_path = os.path.join(HTML_DIR, f"{page}.html")

    if not os.path.exists(html_path):
        return jsonify({"error": "Lesson file not found"}), 404

    # 完全なHTMLをレンダリング
    response = make_response(render_template(f"{page}.html"))

    # ★ キャッシュを完全無効化（Service Worker対策）
    response.headers['Cache-Control'] = 'no-cache, no-store, must-revalidate'
    response.headers['Pragma'] = 'no-cache'
    response.headers['Expires'] = '0'

    return response


# ------------------------------------------------------------
# Manual ページ（同様にキャッシュ無効化）
# ------------------------------------------------------------
@app.route("/manual")
def manual():
    response = make_response(render_template("manual.html"))
    response.headers['Cache-Control'] = 'no-cache, no-store, must-revalidate'
    response.headers['Pragma'] = 'no-cache'
    response.headers['Expires'] = '0'
    return response


# ------------------------------------------------------------
# CSVデータ取得
# ------------------------------------------------------------
@app.route("/get_script/<lesson>")
def get_script(lesson):
    filename = f"script {lesson}.csv"
    path = os.path.join(CSV_DIR, filename)
    if not os.path.exists(path):
        return jsonify({"error": f"{filename} not found"}), 404

    for enc in ('utf-8-sig', 'utf-8', 'cp932'):
        try:
            with open(path, newline='', encoding=enc) as f:
                reader = csv.DictReader(f)
                data = [row for row in reader]
            return jsonify(data)
        except (UnicodeDecodeError, Exception):
            continue

    return jsonify({"error": f"{filename} could not be decoded"}), 500

# ------------------------------------------------------------
# Whisper API
# ------------------------------------------------------------
@app.route("/whisper", methods=["POST"])
def whisper_api():
    f = request.files['audio']
    r = requests.post(
        "http://127.0.0.1:8080/inference",
        files={"file": (f.filename, f, "audio/wav")},
        data={"temperature": "0.0", "response_format": "json"}
    )
    return r.json()


# ------------------------------------------------------------
# OpenAI Transcribe API プロキシ
# フロントから file / model / language / prompt を受け取り
# OpenAI Audio Transcriptions エンドポイントに転送する
# ------------------------------------------------------------
@app.route("/transcribe", methods=["POST"])
def transcribe_api():
    api_key = os.getenv("OPENAI_API_KEY", "")
    if not api_key:
        return jsonify({"error": "OPENAI_API_KEY not set"}), 500

    audio_file = request.files.get("file")
    if not audio_file:
        return jsonify({"error": "No audio file"}), 400

    model    = request.form.get("model", "gpt-4o-mini-transcribe")
    language = request.form.get("language", "en")
    prompt   = request.form.get("prompt", "")

    try:
        files_payload = {
            "file": (audio_file.filename or "recording.wav",
                     audio_file.stream,
                     audio_file.mimetype or "audio/wav")
        }
        data_payload = {
            "model": model,
            "language": language,
        }
        if prompt:
            data_payload["prompt"] = prompt

        r = requests.post(
            "https://api.openai.com/v1/audio/transcriptions",
            headers={"Authorization": f"Bearer {api_key}"},
            files=files_payload,
            data=data_payload,
            timeout=30
        )
        # OpenAI がエラーを返した場合もJSONで返す
        try:
            result = r.json()
        except Exception:
            result = {"error": r.text}

        return jsonify(result), r.status_code

    except requests.exceptions.Timeout:
        return jsonify({"error": "OpenAI API timeout"}), 504
    except Exception as e:
        return jsonify({"error": str(e)}), 500


# ------------------------------------------------------------
# APIキー取得エンドポイント
# ------------------------------------------------------------
@app.route("/get_api_key")
def get_api_key():
    """
    フロントエンドが fetch('/get_api_key') で呼び出す用
    """
    api_key = os.getenv("OPENAI_API_KEY", "")
    if not api_key:
        # .env にない場合はエラー返す（フロントで catch される）
        return jsonify({"error": "API key not found"}), 404

    return jsonify({"api_key": api_key})


# ------------------------------------------------------------
# 利用状況チェック / 利用時間ログ記録  ヘルパー関数
# ------------------------------------------------------------
def _jst_now():
    """日本標準時の現在時刻を返す"""
    from datetime import timezone, timedelta
    JST = timezone(timedelta(hours=9))
    return datetime.now(JST)

def _parse_duration_cell(cell_value):
    """
    "(YYYY-MM-DD)秒数" 形式 → (date_str, seconds)
    例: "(2026-03-09)91" → ("2026-03-09", 91)
    """
    import re
    if not cell_value:
        return (None, 0)
    s = str(cell_value).strip()
    m = re.match(r'^\((\d{4}-\d{2}-\d{2})\)(\d+)$', s)
    if m:
        return (m.group(1), int(m.group(2)))
    try:
        return (None, int(float(s)))
    except (ValueError, TypeError):
        return (None, 0)

def _parse_month_cell(cell_value):
    """
    "(YYYY-MM)秒数" 形式 → (month_str, seconds)
    例: "(2026-03)91" → ("2026-03", 91)
    """
    import re
    if not cell_value:
        return (None, 0)
    s = str(cell_value).strip()
    m = re.match(r'^\((\d{4}-\d{2})\)(\d+)$', s)
    if m:
        return (m.group(1), int(m.group(2)))
    try:
        return (None, int(float(s)))
    except (ValueError, TypeError):
        return (None, 0)

def _get_daily_used(row_series, col_daily, today_str):
    """members.csv の daily列から本日の累積秒数を取得（日付が違えば0）"""
    date_in_cell, sec = _parse_duration_cell(row_series.get(col_daily, ''))
    return sec if date_in_cell == today_str else 0

def _get_monthly_used(row_series, col_monthly, this_month_str):
    """members.csv の monthly列から今月の累積秒数を取得（月が違えば0）"""
    month_in_cell, sec = _parse_month_cell(row_series.get(col_monthly, ''))
    return sec if month_in_cell == this_month_str else 0


# ------------------------------------------------------------
# 利用状況チェック（LessonStart前にフロントエンドが呼ぶ）
# ------------------------------------------------------------
@app.route("/usage/check")
def usage_check():
    """
    ユーザーの利用可否を返す（JSTで判定）。
    返り値:
      { "ok": true }
      { "ok": false, "reason": "expired" }
      { "ok": false, "reason": "daily_limit" }
    """
    if 'user_id' not in session:
        return jsonify({"error": "Unauthorized"}), 401

    df = read_csv_auto(members_csv_path)
    row_df = df[df['user_id'] == session['user_id']]
    if row_df.empty:
        return jsonify({"error": "User not found"}), 404

    r = row_df.iloc[0]
    now_jst   = _jst_now()
    today_str = now_jst.strftime('%Y-%m-%d')

    # ─── expire_date チェック（JST） ───
    expire_date = str(r.get('expire_date', '') or '').strip()
    if expire_date:
        try:
            # "YYYY/M/D" "YYYY-MM-DD" "YYYY/MM/DD HH:MM:SS" など各書式に対応
            from datetime import datetime as _dt
            for fmt in ('%Y-%m-%d %H:%M:%S', '%Y/%m/%d %H:%M:%S',
                        '%Y-%m-%d', '%Y/%m/%d'):
                try:
                    expire_day = _dt.strptime(expire_date, fmt).strftime('%Y-%m-%d')
                    break
                except ValueError:
                    continue
            else:
                expire_day = None
            if expire_day and today_str > expire_day:
                return jsonify({"ok": False, "reason": "expired"})
        except Exception:
            pass  # パース失敗時は無制限扱い

    # ─── daily_limit チェック ───
    # members.csv の duration列から本日の使用秒数を直接読む
    ws_used  = _get_daily_used(r, 'webspeech_daily_duration', today_str)
    api_used = _get_daily_used(r, 'api_daily_duration',       today_str)

    ws_limit_str  = str(r.get('webspeech_daily_duration_limit', '') or '').strip()
    api_limit_str = str(r.get('api_daily_duration_limit',       '') or '').strip()

    if ws_limit_str:
        try:
            if ws_used >= int(float(ws_limit_str)):
                return jsonify({"ok": False, "reason": "daily_limit"})
        except ValueError:
            pass

    if api_limit_str:
        try:
            if api_used >= int(float(api_limit_str)):
                return jsonify({"ok": False, "reason": "daily_limit"})
        except ValueError:
            pass

    return jsonify({"ok": True})


# ------------------------------------------------------------
# 利用時間ログ記録（セッション終了時にフロントエンドが呼ぶ）
# ------------------------------------------------------------
@app.route("/usage/log", methods=["POST"])
def usage_log():
    """
    セッションの音声認識使用秒数を members.csv に累積記録する。
    書式: daily   → "(YYYY-MM-DD)秒数"
          monthly → "(YYYY-MM)秒数"
    同日/同月は累積、日付/月が変わったらリセット。
    limit列・expire_date列は絶対に上書きしない。
    Body: { "duration_sec": <int>, "asr_mode": "webspeech"|"transcribe_api" }
    """
    if 'user_id' not in session:
        return jsonify({"error": "Unauthorized"}), 401

    data = request.get_json(silent=True)
    if not data or 'duration_sec' not in data:
        return jsonify({"error": "No data"}), 400

    try:
        duration_sec = int(data['duration_sec'])
    except (ValueError, TypeError):
        return jsonify({"error": "Invalid duration"}), 400

    if duration_sec <= 0:
        return jsonify({"ok": True, "skipped": True})

    user_id  = session['user_id']
    asr_mode = str(data.get('asr_mode', 'transcribe_api'))
    now_jst  = _jst_now()
    today_str      = now_jst.strftime('%Y-%m-%d')
    this_month_str = now_jst.strftime('%Y-%m')

    with csv_lock:
        # ─── usage_log.csv に行追加（記録用） ───
        log_path = os.path.join(CSV_DIR, 'usage_log.csv')
        log_cols = ['user_id', 'date', 'month', 'asr_mode', 'duration_sec', 'logged_at']
        if os.path.exists(log_path):
            log_df = pd.read_csv(log_path, encoding='utf-8')
            if 'asr_mode' not in log_df.columns:
                log_df['asr_mode'] = 'transcribe_api'
        else:
            log_df = pd.DataFrame(columns=log_cols)
        new_row = pd.DataFrame([{
            'user_id':      user_id,
            'date':         today_str,
            'month':        this_month_str,
            'asr_mode':     asr_mode,
            'duration_sec': duration_sec,
            'logged_at':    now_jst.strftime('%Y-%m-%d %H:%M:%S')
        }])
        log_df = pd.concat([log_df, new_row], ignore_index=True)
        log_df.to_csv(log_path, index=False, encoding='utf-8-sig')

        # ─── members.csv を読み込み、duration列のみ更新 ───
        df   = read_csv_auto(members_csv_path)
        mask = df['user_id'] == user_id
        if not mask.any():
            return jsonify({"error": "User not found"}), 404

        r = df[mask].iloc[0]

        # ASRモードに応じて更新する列を選択（列名はMEMBERS_COLUMNSで固定済み）
        if asr_mode == 'webspeech':
            col_daily   = 'webspeech_daily_duration'
            col_monthly = 'webspeech_monthly_duration'
        else:
            col_daily   = 'api_daily_duration'
            col_monthly = 'api_monthly_duration'

        # 対象列がDURATION_WRITABLE_COLSに含まれることを確認（安全装置）
        if col_daily not in DURATION_WRITABLE_COLS or col_monthly not in DURATION_WRITABLE_COLS:
            return jsonify({"error": "Invalid column"}), 500

        # daily: 同日なら累積、別日ならリセット
        date_in_cell, prev_daily = _parse_duration_cell(r.get(col_daily, ''))
        new_daily = (prev_daily + duration_sec) if date_in_cell == today_str else duration_sec
        df.loc[mask, col_daily] = f'({today_str}){new_daily}'

        # monthly: 同月なら累積、別月ならリセット
        month_in_cell, prev_monthly = _parse_month_cell(r.get(col_monthly, ''))
        new_monthly = (prev_monthly + duration_sec) if month_in_cell == this_month_str else duration_sec
        df.loc[mask, col_monthly] = f'({this_month_str}){new_monthly}'

        write_members_csv(df)

    return jsonify({
        "ok":            True,
        "duration_sec":  duration_sec,
        "daily_total":   new_daily,
        "monthly_total": new_monthly
    })




# 例：app.py のどこか（他の @app.route の並び）に追加
@app.route("/diagnose-all")
def diagnose_all():
    return render_template("diagnose-all.html")


# ------------------------------------------------------------
# メイン
# ------------------------------------------------------------
if __name__ == '__main__':
    app.run(debug=True)