"""
STARWASTE - Database Manager
SQLite3 persistence for world, chunks, entities, and players
"""

import sqlite3
import time
import threading
from typing import Optional, List, Dict, Tuple, Any
from pathlib import Path
from shared.utils import console

# 🔥 OPTIMIZED: Use orjson (C backend) for 3-10× faster JSON parsing
try:
    import orjson
    # orjson returns bytes, need to decode for compatibility
    def json_dumps(obj):
        return orjson.dumps(obj).decode('utf-8')
    def json_loads(s):
        return orjson.loads(s)
    JSON_BACKEND = "orjson (C++)"
except ImportError:
    import json
    json_dumps = json.dumps
    json_loads = json.loads
    JSON_BACKEND = "json (Python)"


class Database:
    """SQLite3 database manager for world persistence"""
    
    def __init__(self, db_path: str = "world.db"):
        """
        Initialize database connection
        
        Args:
            db_path: Path to SQLite database file
        """
        self.db_path = db_path
        self.conn: Optional[sqlite3.Connection] = None
        self._lock = threading.Lock()  # Protect SQLite access from multiple threads
        
    def connect(self):
        """Connect to database and create tables if needed"""
        self.conn = sqlite3.connect(self.db_path, check_same_thread=False)
        self.conn.row_factory = sqlite3.Row  # Access columns by name

        # Robustness: wait a bit on transient locks (especially on Windows).
        self.conn.execute("PRAGMA busy_timeout=5000")
        
        # 🔥 OPTIMIZATION: Enable WAL mode for better concurrent write performance
        # WAL = Write-Ahead Logging: writes go to separate file, no blocking reads
        self.conn.execute("PRAGMA journal_mode=WAL")
        self.conn.execute("PRAGMA synchronous=NORMAL")  # Faster, still safe
        
        # 🔥 DISK OPTIMIZATION: Reduce WAL checkpoint frequency (less disk I/O)
        # Default: checkpoint every 1000 pages (~4MB)
        # New: checkpoint every 10000 pages (~40MB) - 10x less disk writes!
        self.conn.execute("PRAGMA wal_autocheckpoint=10000")
        
        # 🔥 TRANSACTION CONTROL: Manual transaction management
        # Default: autocommit (every write hits disk)
        # We'll use BEGIN/COMMIT to batch writes in memory
        self._in_transaction = False
        
        self._create_tables()
        console.print(f"💾 Database connected: {self.db_path}")
        console.print(f"   JSON backend: {JSON_BACKEND}")
        console.print(f"   Journal mode: WAL (optimized)")
        
    def _create_tables(self):
        """Create database tables if they don't exist"""
        cursor = self.conn.cursor()
        
        # World metadata
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS world (
                id INTEGER PRIMARY KEY CHECK (id = 1),
                seed INTEGER NOT NULL,
                created_at REAL NOT NULL,
                last_save REAL NOT NULL,
                game_time REAL DEFAULT 360.0
            )
        """)
        
        # Chunks (tile data)
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS chunks (
                chunk_x INTEGER NOT NULL,
                chunk_y INTEGER NOT NULL,
                tiles_json TEXT NOT NULL,
                wang_indices_json TEXT,
                PRIMARY KEY (chunk_x, chunk_y)
            )
        """)
        
        # Migration: Add wang_indices_json column if it doesn't exist
        cursor.execute("PRAGMA table_info(chunks)")
        columns = [row[1] for row in cursor.fetchall()]
        if 'wang_indices_json' not in columns:
            cursor.execute("ALTER TABLE chunks ADD COLUMN wang_indices_json TEXT")
            console.print("✅ Migrated database: Added wang_indices_json column to chunks table")
        
        # Migration: Add game_time column to world table if it doesn't exist
        cursor.execute("PRAGMA table_info(world)")
        world_columns = [row[1] for row in cursor.fetchall()]
        if 'game_time' not in world_columns:
            cursor.execute("ALTER TABLE world ADD COLUMN game_time REAL DEFAULT 360.0")
            console.print("✅ Migrated database: Added game_time column to world table (default 6:00 AM)")
        
        # Entities (resource nodes, items, etc.)
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS entities (
                entity_id INTEGER PRIMARY KEY,
                entity_type TEXT NOT NULL,
                x REAL NOT NULL,
                y REAL NOT NULL,
                data_json TEXT NOT NULL
            )
        """)
        
        # Players (position, inventory, stats)
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS players (
                player_id INTEGER PRIMARY KEY,
                username TEXT NOT NULL UNIQUE,
                x REAL NOT NULL,
                y REAL NOT NULL,
                data_json TEXT NOT NULL
            )
        """)
        
        self.conn.commit()
        
    def world_exists(self) -> bool:
        """Check if world data exists in database - thread-safe"""
        if not Path(self.db_path).exists():
            return False
        
        with self._lock:
            cursor = self.conn.cursor()
            cursor.execute("SELECT COUNT(*) FROM world")
            count = cursor.fetchone()[0]
            return count > 0
        
    def save_world_metadata(self, seed: int, game_time: float = 360.0, *, commit: bool = True):
        """Save world metadata (seed, timestamps, game_time) - thread-safe

        Args:
            seed: World seed
            game_time: Minutes since midnight
            commit: If True (default), commit immediately. For batched saves,
                pass commit=False and call save_all() once at the end.
        """
        with self._lock:
            # Keep transaction handling consistent with other save_* methods.
            # If we want to batch (commit=False), ensure we explicitly BEGIN and
            # mark _in_transaction so later save_chunk/save_entity won't try to
            # BEGIN again (which would error).
            if not commit and not self._in_transaction:
                self.conn.execute("BEGIN")
                self._in_transaction = True

            cursor = self.conn.cursor()
            now = time.time()
            
            cursor.execute("""
                INSERT OR REPLACE INTO world (id, seed, created_at, last_save, game_time)
                VALUES (1, ?, COALESCE((SELECT created_at FROM world WHERE id = 1), ?), ?, ?)
            """, (seed, now, now, game_time))

            # If we're already in a manual transaction, do not commit here.
            if commit and not self._in_transaction:
                self.conn.commit()
        
    def load_world_metadata(self) -> Optional[Dict[str, Any]]:
        """Load world metadata - thread-safe"""
        with self._lock:
            cursor = self.conn.cursor()
            cursor.execute("SELECT * FROM world WHERE id = 1")
            row = cursor.fetchone()
            
            if row:
                return {
                    "seed": row["seed"],
                    "created_at": row["created_at"],
                    "last_save": row["last_save"],
                    "game_time": row["game_time"] if "game_time" in row.keys() else 360.0
                }
            return None
        
    def save_chunk(self, chunk_x: int, chunk_y: int, tiles: List[List[int]], corners: List[List[Tuple]] = None):
        """Save chunk tile data and corner data (thread-safe, NO auto-commit)"""
        with self._lock:
            # 🔥 BEGIN TRANSACTION if not already in one (keeps writes in memory!)
            if not self._in_transaction:
                self.conn.execute("BEGIN")
                self._in_transaction = True
            
            cursor = self.conn.cursor()
            tiles_json = json_dumps(tiles)
            wang_indices_json = json_dumps(corners) if corners else None
            
            cursor.execute("""
                INSERT OR REPLACE INTO chunks (chunk_x, chunk_y, tiles_json, wang_indices_json)
                VALUES (?, ?, ?, ?)
            """, (chunk_x, chunk_y, tiles_json, wang_indices_json))
            
            # 🔥 OPTIMIZATION: No commit here! Caller must call save_all() to commit batch
        
    def load_chunk(self, chunk_x: int, chunk_y: int) -> Optional[tuple]:
        """Load chunk tile data and corner data (thread-safe)
        Returns: (tiles, corners) tuple or None
        """
        with self._lock:
            cursor = self.conn.cursor()
            cursor.execute("""
                SELECT tiles_json, wang_indices_json FROM chunks WHERE chunk_x = ? AND chunk_y = ?
            """, (chunk_x, chunk_y))
            
            row = cursor.fetchone()
            if row and row["tiles_json"]:
                tiles = json_loads(row["tiles_json"])
                corners = json_loads(row["wang_indices_json"]) if row["wang_indices_json"] else None
                return (tiles, corners)
            return None
        
    def save_entity(self, entity_id: int, entity_type: str, x: float, y: float, data: Dict[str, Any]):
        """Save entity data - thread-safe (NO auto-commit)"""
        with self._lock:
            # 🔥 BEGIN TRANSACTION if not already in one (keeps writes in memory!)
            if not self._in_transaction:
                self.conn.execute("BEGIN")
                self._in_transaction = True
            
            cursor = self.conn.cursor()
            data_json = json_dumps(data)
            
            cursor.execute("""
                INSERT OR REPLACE INTO entities (entity_id, entity_type, x, y, data_json)
                VALUES (?, ?, ?, ?, ?)
            """, (entity_id, entity_type, x, y, data_json))
            
            # 🔥 OPTIMIZATION: No commit here! Caller must call save_all() to commit batch
        
    def load_entities(self) -> List[Dict[str, Any]]:
        """Load all entities - thread-safe"""
        with self._lock:
            cursor = self.conn.cursor()
            cursor.execute("SELECT * FROM entities")
            
            entities = []
            for row in cursor.fetchall():
                data = json_loads(row["data_json"])
                entities.append({
                    "entity_id": row["entity_id"],
                    "entity_type": row["entity_type"],
                    "x": row["x"],
                    "y": row["y"],
                    "data": data
                })
            
            return entities
        
    def delete_entity(self, entity_id: int):
        """Delete entity from database - thread-safe (NO auto-commit)"""
        with self._lock:
            # 🔥 BEGIN TRANSACTION if not already in one (keeps writes in memory!)
            if not self._in_transaction:
                self.conn.execute("BEGIN")
                self._in_transaction = True
            
            cursor = self.conn.cursor()
            cursor.execute("DELETE FROM entities WHERE entity_id = ?", (entity_id,))
            # 🔥 OPTIMIZATION: No commit here! Caller must call save_all() to commit batch
        
    def save_player(self, player_id: int, username: str, x: float, y: float, data: Dict[str, Any]):
        """Save player data - thread-safe (NO auto-commit)"""
        with self._lock:
            # 🔥 BEGIN TRANSACTION if not already in one (keeps writes in memory!)
            if not self._in_transaction:
                self.conn.execute("BEGIN")
                self._in_transaction = True
            
            cursor = self.conn.cursor()
            data_json = json_dumps(data)
            
            cursor.execute("""
                INSERT OR REPLACE INTO players (player_id, username, x, y, data_json)
                VALUES (?, ?, ?, ?, ?)
            """, (player_id, username, x, y, data_json))
            
            # 🔥 OPTIMIZATION: No commit here! Caller must call save_all() to commit batch
        
    def load_player(self, username: str) -> Optional[Dict[str, Any]]:
        """Load player data by username - thread-safe"""
        with self._lock:
            cursor = self.conn.cursor()
            cursor.execute("SELECT * FROM players WHERE username = ?", (username,))
            
            row = cursor.fetchone()
            if row:
                data = json_loads(row["data_json"])
                return {
                    "player_id": row["player_id"],
                    "username": row["username"],
                    "x": row["x"],
                    "y": row["y"],
                    "data": data
                }
            return None
        
    def save_all(self):
        """Force commit all pending changes - thread-safe
        
        🔥 THIS IS THE KEY: Commits transaction, flushes RAM to disk in ONE operation!
        """
        with self._lock:
            if self.conn and self._in_transaction:
                self.conn.commit()
                self._in_transaction = False
                console.print("💾 Database changes committed (batched writes flushed to disk)")
            elif self.conn:
                # No pending transaction, but ensure any stray writes are committed
                self.conn.commit()
            
    def close(self):
        """Close database connection - thread-safe"""
        with self._lock:
            if self.conn:
                # Commit any pending transaction before closing
                if self._in_transaction:
                    self.conn.commit()
                    self._in_transaction = False
                self.conn.close()
                console.print("💾 Database closed")
            
    def clear_all(self):
        """Clear all data from database (new world) - thread-safe"""
        with self._lock:
            cursor = self.conn.cursor()
            cursor.execute("DELETE FROM world")
            cursor.execute("DELETE FROM chunks")
            cursor.execute("DELETE FROM entities")
            cursor.execute("DELETE FROM players")
            self.conn.commit()
            console.print("🗑️ Database cleared (new world)")
