"""
Database registry for VRAM memory management using SQLite
"""

import json
import os
import threading
import logging
import time
from typing import Dict, Any, Optional, List
from pathlib import Path
import sqlite3

class LocalDatabase:
    _instance = None
    _lock = threading.Lock()

    def __new__(cls, db_path: str = "db/vram/registry.db"):
        with cls._lock:
            if cls._instance is None:
                cls._instance = super().__new__(cls)
                cls._instance._init_storage(db_path)
            return cls._instance

    def _init_storage(self, db_path: str):
        """Initialize the local database"""
        self.db_path = db_path
        os.makedirs(os.path.dirname(db_path), exist_ok=True)
        
        self.conn = sqlite3.connect(db_path, check_same_thread=False)
        self.data_lock = threading.Lock()
        self._setup_tables()
        self._load_data()

    def _setup_tables(self):
        """Set up database tables"""
        self.conn.execute("""
            CREATE TABLE IF NOT EXISTS registry (
                category TEXT PRIMARY KEY,
                data TEXT
            )
        """)
        self.conn.commit()

    def _load_data(self):
        """Load data from database"""
        with self.data_lock:
            self.data = {
                'cells': {},
                'blocks': {},
                'pages': {},
                'streams': {},
                'states': {},
                'metadata': {}
            }
            
            for category in self.data.keys():
                result = self.conn.execute(
                    "SELECT data FROM registry WHERE category = ?",
                    (category,)
                ).fetchone()
                
                if result:
                    self.data[category] = json.loads(result[0])

    def _save_data(self):
        """Save data to database"""
        with self.data_lock:
            for category, data in self.data.items():
                self.conn.execute("""
                    INSERT OR REPLACE INTO registry (category, data)
                    VALUES (?, ?)
                """, (category, json.dumps(data)))
            self.conn.commit()

    def store_cell_data(self, cell_id: str, data: Dict[str, Any]) -> bool:
        with self.data_lock:
            self.data['cells'][cell_id] = {
                'data': data,
                'timestamp': time.time()
            }
            self._save_data()
            return True

    def load_cell_data(self, cell_id: str) -> Optional[Dict[str, Any]]:
        with self.data_lock:
            if cell_id in self.data['cells']:
                return self.data['cells'][cell_id]['data']
            return None

    def store_block_data(self, block_id: str, data: Dict[str, Any]) -> bool:
        with self.data_lock:
            self.data['blocks'][block_id] = {
                'data': data,
                'timestamp': time.time()
            }
            self._save_data()
            return True

    def get_cell(self, cell_id: str) -> Optional[Dict[str, Any]]:
        """Get cell data"""
        with self.data_lock:
            return self.data['cells'].get(cell_id)

    def set_cell(self, cell_id: str, data: Dict[str, Any]):
        """Set cell data"""
        with self.data_lock:
            self.data['cells'][cell_id] = data
            self._save_data()

    def get_block(self, block_id: str) -> Optional[Dict[str, Any]]:
        """Get block data"""
        with self.data_lock:
            return self.data['blocks'].get(block_id)

    def set_block(self, block_id: str, data: Dict[str, Any]):
        """Set block data"""
        with self.data_lock:
            self.data['blocks'][block_id] = data
            self._save_data()

    def get_page(self, page_id: str) -> Optional[Dict[str, Any]]:
        """Get page data"""
        with self.data_lock:
            return self.data['pages'].get(page_id)

    def set_page(self, page_id: str, data: Dict[str, Any]):
        """Set page data"""
        with self.data_lock:
            self.data['pages'][page_id] = data
            self._save_data()

    def get_stream(self, stream_id: str) -> Optional[Dict[str, Any]]:
        """Get stream data"""
        with self.data_lock:
            return self.data['streams'].get(stream_id)

    def set_stream(self, stream_id: str, data: Dict[str, Any]):
        """Set stream data"""
        with self.data_lock:
            self.data['streams'][stream_id] = data
            self._save_data()

    def get_state(self, state_id: str) -> Optional[Dict[str, Any]]:
        """Get state data"""
        with self.data_lock:
            return self.data['states'].get(state_id)

    def set_state(self, state_id: str, data: Dict[str, Any]):
        """Set state data"""
        with self.data_lock:
            self.data['states'][state_id] = data
            self._save_data()

    def get_metadata(self, key: str) -> Optional[Dict[str, Any]]:
        """Get metadata"""
        with self.data_lock:
            return self.data['metadata'].get(key)

    def set_metadata(self, key: str, data: Dict[str, Any]):
        """Set metadata"""
        with self.data_lock:
            self.data['metadata'][key] = data
            self._save_data()

    def delete_data(self, component: str, data_id: str) -> bool:
        """Delete data by component and ID"""
        with self.data_lock:
            data_dict = self.data.get(f"{component}s", {})
            if data_id in data_dict:
                del data_dict[data_id]
                self._save_data()
                return True
            return False

    def list_data(self, component: str) -> List[str]:
        """List all IDs for a component"""
        with self.data_lock:
            return list(self.data.get(f"{component}s", {}).keys())

    def close(self):
        """Close database connection"""
        if hasattr(self, 'conn'):
            self.conn.close()

    def __del__(self):
        self.close()


