from __future__ import annotations

import os
import sqlite3
from typing import Optional, Dict, Any

import bcrypt

class DB:
    def __init__(self, path: str):
        self.path = path
        os.makedirs(os.path.dirname(path), exist_ok=True)

    def _conn(self) -> sqlite3.Connection:
        conn = sqlite3.connect(self.path)
        conn.row_factory = sqlite3.Row
        return conn

    def user_exists(self, username: str) -> bool:
        with self._conn() as c:
            r = c.execute("SELECT 1 FROM users WHERE username = ?", (username,)).fetchone()
            return r is not None

    def create_user(self, username: str, password: str) -> int:
        pw_hash = bcrypt.hashpw(password.encode("utf-8"), bcrypt.gensalt()).decode("utf-8")
        with self._conn() as c:
            cur = c.execute("INSERT INTO users (username, password) VALUES (?, ?)", (username, pw_hash))
            return int(cur.lastrowid)

    def verify_user(self, username: str, password: str) -> Optional[Dict[str, Any]]:
        with self._conn() as c:
            row = c.execute("SELECT id, username, password FROM users WHERE username = ?", (username,)).fetchone()
            if not row:
                return None
            stored = (row["password"] or "")
            # Backwards compat: if old DB has plaintext, accept it then upgrade to bcrypt.
            if stored.startswith("$2"):
                ok = bcrypt.checkpw(password.encode("utf-8"), stored.encode("utf-8"))
                return dict(row) if ok else None
            else:
                if password == stored:
                    new_hash = bcrypt.hashpw(password.encode("utf-8"), bcrypt.gensalt()).decode("utf-8")
                    c.execute("UPDATE users SET password = ? WHERE id = ?", (new_hash, row["id"]))
                    return dict(row)
                return None

    def ensure_balance_row(self, user_id: int) -> None:
        with self._conn() as c:
            r = c.execute("SELECT 1 FROM balances WHERE user_id = ?", (user_id,)).fetchone()
            if not r:
                c.execute("INSERT INTO balances (user_id, balance) VALUES (?, 1000.0)", (user_id,))

    def get_balance(self, user_id: int) -> float:
        with self._conn() as c:
            r = c.execute("SELECT balance FROM balances WHERE user_id = ?", (user_id,)).fetchone()
            return float(r["balance"]) if r else 0.0

    def add_balance(self, user_id: int, delta: float) -> None:
        with self._conn() as c:
            c.execute("UPDATE balances SET balance = balance + ?, last_updated = CURRENT_TIMESTAMP WHERE user_id = ?",
                      (delta, user_id))

    def insert_transaction(self, user_id: int, amount: float, tx_type: str, game: str) -> None:
        with self._conn() as c:
            c.execute(
                "INSERT INTO transactions (user_id, amount, type, game) VALUES (?, ?, ?, ?)",
                (user_id, amount, tx_type, game),
            )

    def get_user_stats(self, user_id: int) -> Dict[str, float]:
        """Return (win_rate, games_played, biggest_win) derived from transactions."""
        with self._conn() as c:
            gp_row = c.execute(
                """
                SELECT
                  (SELECT COUNT(*) FROM transactions WHERE user_id=? AND type LIKE '%\\_bet' ESCAPE '\\') +
                  (SELECT COUNT(*) FROM transactions WHERE user_id=? AND game='rocket' AND type IN ('win','loss'))
                AS games_played
                """
                , (user_id, user_id)
            ).fetchone()
            games_played = int(gp_row[0] or 0)

            wins = int(c.execute(
                """
                SELECT COUNT(*) FROM transactions
                WHERE user_id=? AND (
                  type LIKE '%\\_win' ESCAPE '\\'
                  OR (game='rocket' AND type='win')
                )
                """
                , (user_id,)
            ).fetchone()[0] or 0)

            losses = int(c.execute(
                """
                SELECT COUNT(*) FROM transactions
                WHERE user_id=? AND (
                  type LIKE '%\\_loss' ESCAPE '\\'
                  OR (game='rocket' AND type='loss')
                )
                """
                , (user_id,)
            ).fetchone()[0] or 0)

            biggest = float(c.execute(
                """
                SELECT COALESCE(MAX(amount), 0) FROM transactions
                WHERE user_id=? AND amount > 0 AND (
                  type LIKE '%\\_win' ESCAPE '\\'
                  OR (game='rocket' AND type='win')
                )
                """
                , (user_id,)
            ).fetchone()[0] or 0.0)

        denom = wins + losses
        win_rate = (wins / denom) if denom > 0 else 0.0
        return {"win_rate": float(win_rate), "games_played": games_played, "biggest_win": float(biggest)}

    def get_game_settings(self, game: str) -> Dict[str, Any]:
        """Read admin-configured settings for a game (enabled/min/max/house_edge)."""
        game = (game or "").strip().lower()
        if not game:
            return {"game": "", "enabled": 1, "min_bet": 0.0, "max_bet": 0.0, "house_edge": 0.0}

        defaults = {"game": game, "enabled": 1, "min_bet": 0.0, "max_bet": 0.0, "house_edge": 0.0}

        with self._conn() as c:
            row = c.execute(
                "SELECT game, enabled, min_bet, max_bet, house_edge FROM game_settings WHERE game = ?",
                (game,),
            ).fetchone()

            if not row:
                c.execute(
                    "INSERT OR IGNORE INTO game_settings (game, enabled, min_bet, max_bet, house_edge, updated_at) "
                    "VALUES (?, ?, ?, ?, ?, CURRENT_TIMESTAMP)",
                    (game, defaults["enabled"], defaults["min_bet"], defaults["max_bet"], defaults["house_edge"]),
                )
                return defaults

            out = dict(row)
            out["enabled"] = int(out.get("enabled") or 0)
            out["min_bet"] = float(out.get("min_bet") or 0.0)
            out["max_bet"] = float(out.get("max_bet") or 0.0)
            out["house_edge"] = float(out.get("house_edge") or 0.0)
            return out

    def get_plinko_multipliers(self, rows: int, risk: str):
        """
        Return Plinko *return* multipliers (includes stake) for the given rows + risk.

        Stored in SQLite table `plinko_tables` as JSON text in column `multipliers`.
        Falls back to built-in defaults (fair RTP ~= 1.0 for 50/50 left-right).
        """
        import json as _json

        rows = int(rows or 12)
        if rows < 8: rows = 8
        if rows > 16: rows = 16

        risk = (risk or "medium").strip().lower()
        if risk not in ("low","medium","high"):
            risk = "medium"

        DEFAULTS = {
  "8_low": [
    0.4624,
    0.6474,
    0.8324,
    1.0173,
    1.2023,
    1.0173,
    0.8324,
    0.6474,
    0.4624
  ],
  "8_medium": [
    0.1913,
    0.3061,
    0.5357,
    0.9184,
    1.6837,
    0.9184,
    0.5357,
    0.3061,
    0.1913
  ],
  "8_high": [
    0.0,
    0.1051,
    0.2102,
    0.5887,
    2.523,
    0.5887,
    0.2102,
    0.1051,
    0.0
  ],
  "12_low": [
    0.2621,
    0.4368,
    0.6115,
    0.7862,
    0.9173,
    1.0483,
    1.1793,
    1.0483,
    0.9173,
    0.7862,
    0.6115,
    0.4368,
    0.2621
  ],
  "12_medium": [
    0.0,
    0.138,
    0.276,
    0.4416,
    0.6624,
    0.9936,
    1.7665,
    0.9936,
    0.6624,
    0.4416,
    0.276,
    0.138,
    0.0
  ],
  "12_high": [
    0.0,
    0.0,
    0.0557,
    0.1115,
    0.3345,
    0.7804,
    2.6757,
    0.7804,
    0.3345,
    0.1115,
    0.0557,
    0.0,
    0.0
  ],
  "16_low": [
    0.1718,
    0.3006,
    0.4294,
    0.5583,
    0.6871,
    0.8159,
    0.9447,
    1.0736,
    1.2024,
    1.0736,
    0.9447,
    0.8159,
    0.6871,
    0.5583,
    0.4294,
    0.3006,
    0.1718
  ],
  "16_medium": [
    0.0,
    0.0709,
    0.1418,
    0.2364,
    0.3545,
    0.4964,
    0.6618,
    0.9454,
    2.1272,
    0.9454,
    0.6618,
    0.4964,
    0.3545,
    0.2364,
    0.1418,
    0.0709,
    0.0
  ],
  "16_high": [
    0.0,
    0.0,
    0.019,
    0.0317,
    0.0634,
    0.1523,
    0.3553,
    0.7613,
    3.1722,
    0.7613,
    0.3553,
    0.1523,
    0.0634,
    0.0317,
    0.019,
    0.0,
    0.0
  ]
}

        key = f"{rows}_{risk}"
        fallback = DEFAULTS.get(key)

        with self._conn() as c:
            row = c.execute(
                "SELECT multipliers FROM plinko_tables WHERE rows=? AND risk=?",
                (rows, risk)
            ).fetchone()

        raw = None
        if row is not None:
            raw = row["multipliers"] if isinstance(row, dict) else row[0]

        if raw is None or str(raw).strip() == "":
            return list(fallback) if fallback else [0.0]*(rows+1)

        try:
            arr = _json.loads(raw)
            if not isinstance(arr, list):
                raise ValueError("not list")
            arr = [float(x) for x in arr]
        except Exception:
            return list(fallback) if fallback else [0.0]*(rows+1)

        if len(arr) != rows + 1:
            return list(fallback) if fallback else [0.0]*(rows+1)

        return arr



        def _parse(val):
            if val is None:
                return None
            if isinstance(val, (bytes, bytearray)):
                try:
                    val = val.decode("utf-8")
                except Exception:
                    return None
            if not isinstance(val, str):
                return None
            val = val.strip()
            if not val:
                return None
            try:
                arr = _json.loads(val)
                if isinstance(arr, str):
                    arr = _json.loads(arr)
                if isinstance(arr, list):
                    return [float(x) for x in arr]
            except Exception:
                return None
            return None

        with self._conn() as c:
            row = c.execute(
                "SELECT multipliers_json FROM plinko_tables WHERE rows=? AND risk=?",
                (rows, risk),
            ).fetchone()

            if row:
                parsed = _parse(row["multipliers_json"])
                if parsed is not None:
                    return parsed

            row = c.execute(
                "SELECT multipliers_json FROM plinko_tables WHERE risk=? ORDER BY ABS(rows-?) ASC LIMIT 1",
                (risk, rows),
            ).fetchone()

            if row:
                parsed = _parse(row["multipliers_json"])
                if parsed is not None:
                    return parsed

        return DEFAULTS.get(rows, DEFAULTS[12]).get(risk, DEFAULTS[12]["medium"])
def init_db(db: DB) -> None:
    """Ensure core tables exist and plinko multipliers are available (safe idempotent init)."""
    with db._conn() as c:
        # Users
        c.execute(
            """
            CREATE TABLE IF NOT EXISTS users (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                username TEXT UNIQUE NOT NULL,
                password TEXT NOT NULL,
                created_at TEXT DEFAULT CURRENT_TIMESTAMP
            )
            """
        )

        # Balances (stored in 'millions' like the PHP frontend expects)
        c.execute(
            """
            CREATE TABLE IF NOT EXISTS balances (
                user_id INTEGER PRIMARY KEY,
                balance REAL NOT NULL DEFAULT 0,
                last_updated TEXT DEFAULT CURRENT_TIMESTAMP
            )
            """
        )

        # Transactions
        c.execute(
            """
            CREATE TABLE IF NOT EXISTS transactions (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id INTEGER NOT NULL,
                amount REAL NOT NULL,
                type TEXT NOT NULL,
                game TEXT NOT NULL,
                created_at TEXT DEFAULT CURRENT_TIMESTAMP
            )
            """
        )

        # Game settings (admin controlled)
        c.execute(
            """
            CREATE TABLE IF NOT EXISTS game_settings (
                game TEXT PRIMARY KEY,
                enabled INTEGER NOT NULL DEFAULT 1,
                min_bet REAL NOT NULL DEFAULT 0,
                max_bet REAL NOT NULL DEFAULT 0,
                house_edge REAL NOT NULL DEFAULT 0,
                updated_at TEXT DEFAULT CURRENT_TIMESTAMP
            )
            """
        )

                # Plinko multipliers table (return multipliers incl stake)
        c.execute('''
            CREATE TABLE IF NOT EXISTS plinko_tables (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                rows INTEGER NOT NULL,
                risk TEXT NOT NULL,
                multipliers TEXT NOT NULL,
                updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
                UNIQUE(rows, risk)
            )
        ''')

        # --- Migration for older plinko_tables schemas (e.g. multipliers_json) ---
        try:
            cols = [r[1] for r in c.execute('PRAGMA table_info(plinko_tables)').fetchall()]
            if 'multipliers' not in cols:
                c.execute('ALTER TABLE plinko_tables ADD COLUMN multipliers TEXT')
                cols.append('multipliers')
            if 'multipliers_json' in cols:
                c.execute("UPDATE plinko_tables SET multipliers = COALESCE(multipliers, multipliers_json) WHERE multipliers IS NULL OR TRIM(multipliers)='' ")
            if 'updated_at' not in cols:
                c.execute("ALTER TABLE plinko_tables ADD COLUMN updated_at TEXT DEFAULT CURRENT_TIMESTAMP")
        except Exception:
            pass

        # Seed default plinko multiplier tables (normalized RTP ~= 1.0 before house edge)
        import json as _json
        _defaults = {
            (8, 'low'): [0.4624, 0.6474, 0.8324, 1.0173, 1.2023, 1.0173, 0.8324, 0.6474, 0.4624],
            (8, 'medium'): [0.1913, 0.3061, 0.5357, 0.9184, 1.6837, 0.9184, 0.5357, 0.3061, 0.1913],
            (8, 'high'): [0.0, 0.1051, 0.2102, 0.5887, 2.523, 0.5887, 0.2102, 0.1051, 0.0],
            (12, 'low'): [0.2621, 0.4368, 0.6115, 0.7862, 0.9173, 1.0483, 1.1793, 1.0483, 0.9173, 0.7862, 0.6115, 0.4368, 0.2621],
            (12, 'medium'): [0.0, 0.138, 0.276, 0.4416, 0.6624, 0.9936, 1.7665, 0.9936, 0.6624, 0.4416, 0.276, 0.138, 0.0],
            (12, 'high'): [0.0, 0.0, 0.0557, 0.1115, 0.3345, 0.7804, 2.6757, 0.7804, 0.3345, 0.1115, 0.0557, 0.0, 0.0],
            (16, 'low'): [0.1718, 0.3006, 0.4294, 0.5583, 0.6871, 0.8159, 0.9447, 1.0736, 1.2024, 1.0736, 0.9447, 0.8159, 0.6871, 0.5583, 0.4294, 0.3006, 0.1718],
            (16, 'medium'): [0.0, 0.0709, 0.1418, 0.2364, 0.3545, 0.4964, 0.6618, 0.9454, 2.1272, 0.9454, 0.6618, 0.4964, 0.3545, 0.2364, 0.1418, 0.0709, 0.0],
            (16, 'high'): [0.0, 0.0, 0.019, 0.0317, 0.0634, 0.1523, 0.3553, 0.7613, 3.1722, 0.7613, 0.3553, 0.1523, 0.0634, 0.0317, 0.019, 0.0, 0.0],
        }
        for (rows, risk), arr in _defaults.items():
            mults = _json.dumps(arr)
            c.execute("INSERT OR IGNORE INTO plinko_tables (rows, risk, multipliers) VALUES (?, ?, ?)", (rows, risk, mults))
            c.execute("UPDATE plinko_tables SET multipliers=? WHERE rows=? AND risk=? AND (multipliers IS NULL OR TRIM(multipliers)='')", (mults, rows, risk))

# Seed default game settings entries (so admin panel can see them)
        for game in ("dice", "rocket", "mines", "blackjack", "plinko", "slots"):
            c.execute(
                "INSERT OR IGNORE INTO game_settings (game, enabled, min_bet, max_bet, house_edge, updated_at) "
                "VALUES (?, 1, 0, 0, 0, CURRENT_TIMESTAMP)",
                (game,),
            )

        # Seed default plinko multipliers (net multipliers) and fill NULLs
        seeds = {
            ("low"):    "[0, 0, 0.2, 0.2, 0.5, 0.5, 0.5, 0.5, 0.2, 0.2, 0, 0, 3.0]",
            ("medium"): "[0, 0, 0.2, 0.5, 1.0, 1.0, 1.0, 1.0, 0.5, 0.2, 0, 0, 5.0]",
            ("high"):   "[0, 0, 0, 0.2, 1.5, 2.5, 3.0, 2.5, 1.5, 0.2, 0, 0, 10.0]",
        }
        for risk, mults in seeds.items():
            c.execute(
                "INSERT OR IGNORE INTO plinko_tables (rows, risk, multipliers_json) VALUES (?, ?, ?)",
                (12, risk, mults),
            )
            c.execute(
                "UPDATE plinko_tables SET multipliers_json=? "
                "WHERE rows=? AND risk=? AND (multipliers_json IS NULL OR TRIM(multipliers_json)='')",
                (mults, 12, risk),
            )