"""
Database manager for graphics type definitions using SQLite
"""
import sqlite3
import time
import logging
import os
from pathlib import Path
from typing import Dict, List, Optional, Tuple, Union
import json
import threading

class GraphicsTypeDB:
    _instance = None
    _lock = threading.Lock()
    DB_FILE = "db/graphics/types.db"

    def __new__(cls, db_path: str = None):
        with cls._lock:
            if cls._instance is None:
                cls._instance = super(GraphicsTypeDB, cls).__new__(cls)
                cls._instance.init_db(db_path or cls.DB_FILE)
            return cls._instance

    def init_db(self, db_path: str):
        """Initialize the database"""
        self.db_path = db_path
        os.makedirs(os.path.dirname(db_path), exist_ok=True)
        self._connect()
        
    def _connect(self):
        """Establish database connection"""
        try:
            self.conn = sqlite3.connect(self.db_path, check_same_thread=False)
            self._init_tables()
            self._init_enums()
        except Exception as e:
            raise RuntimeError(f"Failed to initialize database: {str(e)}")

    def _init_tables(self):
        """Initialize database tables"""
        with self._lock:
            # Shader types table
            self.conn.execute("""
                CREATE TABLE IF NOT EXISTS shader_types (
                    type_id TEXT PRIMARY KEY,
                    name TEXT NOT NULL,
                    description TEXT,
                    metadata TEXT
                )
            """)
            
            # Uniform types table
            self.conn.execute("""
                CREATE TABLE IF NOT EXISTS uniform_types (
                    type_id TEXT PRIMARY KEY,
                    name TEXT NOT NULL,
                    size INTEGER NOT NULL,
                    alignment INTEGER NOT NULL,
                    metadata TEXT
                )
            """)
            
            # Vertex attribute types table
            self.conn.execute("""
                CREATE TABLE IF NOT EXISTS vertex_attribute_types (
                    type_id TEXT PRIMARY KEY,
                    name TEXT NOT NULL,
                    components INTEGER NOT NULL,
                    component_type TEXT NOT NULL,
                    normalized BOOLEAN NOT NULL,
                    metadata TEXT
                )
            """)
            
            # Texture formats table
            self.conn.execute("""
                CREATE TABLE IF NOT EXISTS texture_formats (
                    format_id TEXT PRIMARY KEY,
                    name TEXT NOT NULL,
                    channels INTEGER NOT NULL,
                    bits_per_channel INTEGER NOT NULL,
                    compression TEXT,
                    metadata TEXT
                )
            """)
            
            # Buffer types table
            self.conn.execute("""
                CREATE TABLE IF NOT EXISTS buffer_types (
                    type_id TEXT PRIMARY KEY,
                    name TEXT NOT NULL,
                    usage_flags TEXT NOT NULL,
                    metadata TEXT
                )
            """)
            
            self.conn.commit()

    def _init_enums(self):
        """Initialize default enum values"""
        default_shader_types = [
            ('vertex', 'Vertex Shader', 'Processes vertices'),
            ('fragment', 'Fragment Shader', 'Processes fragments/pixels'),
            ('compute', 'Compute Shader', 'General purpose computation'),
            ('geometry', 'Geometry Shader', 'Processes primitives')
        ]
        
        default_uniform_types = [
            ('float', 'Float', 4, 4, None),
            ('vec2', 'Vector2', 8, 8, None),
            ('vec3', 'Vector3', 12, 16, None),
            ('vec4', 'Vector4', 16, 16, None),
            ('mat4', 'Matrix4x4', 64, 16, None),
            ('int', 'Integer', 4, 4, None),
            ('bool', 'Boolean', 4, 4, None)
        ]
        
        with self._lock:
            # Insert shader types
            self.conn.executemany("""
                INSERT OR IGNORE INTO shader_types (type_id, name, description)
                VALUES (?, ?, ?)
            """, default_shader_types)
            
            # Insert uniform types
            self.conn.executemany("""
                INSERT OR IGNORE INTO uniform_types (type_id, name, size, alignment)
                VALUES (?, ?, ?, ?)
            """, default_uniform_types)
            
            self.conn.commit()

    def get_shader_type(self, type_id: str) -> Optional[Dict]:
        """Get shader type information"""
        with self._lock:
            result = self.conn.execute("""
                SELECT name, description, metadata
                FROM shader_types
                WHERE type_id = ?
            """, (type_id,)).fetchone()
            
        if not result:
            return None
            
        return {
            'type_id': type_id,
            'name': result[0],
            'description': result[1],
            'metadata': json.loads(result[2]) if result[2] else {}
        }

    def get_uniform_type(self, type_id: str) -> Optional[Dict]:
        """Get uniform type information"""
        with self._lock:
            result = self.conn.execute("""
                SELECT name, size, alignment, metadata
                FROM uniform_types
                WHERE type_id = ?
            """, (type_id,)).fetchone()
            
        if not result:
            return None
            
        return {
            'type_id': type_id,
            'name': result[0],
            'size': result[1],
            'alignment': result[2],
            'metadata': json.loads(result[3]) if result[3] else {}
        }

    def add_shader_type(self, type_id: str, name: str, description: str = None,
                       metadata: Optional[Dict] = None):
        """Add new shader type"""
        with self._lock:
            self.conn.execute("""
                INSERT OR REPLACE INTO shader_types (type_id, name, description, metadata)
                VALUES (?, ?, ?, ?)
            """, (type_id, name, description, json.dumps(metadata) if metadata else None))
            self.conn.commit()

    def add_uniform_type(self, type_id: str, name: str, size: int, alignment: int,
                        metadata: Optional[Dict] = None):
        """Add new uniform type"""
        with self._lock:
            self.conn.execute("""
                INSERT OR REPLACE INTO uniform_types 
                (type_id, name, size, alignment, metadata)
                VALUES (?, ?, ?, ?, ?)
            """, (type_id, name, size, alignment, 
                 json.dumps(metadata) if metadata else None))
            self.conn.commit()

    def add_vertex_attribute_type(self, type_id: str, name: str, components: int,
                                component_type: str, normalized: bool,
                                metadata: Optional[Dict] = None):
        """Add new vertex attribute type"""
        with self._lock:
            self.conn.execute("""
                INSERT OR REPLACE INTO vertex_attribute_types
                (type_id, name, components, component_type, normalized, metadata)
                VALUES (?, ?, ?, ?, ?, ?)
            """, (type_id, name, components, component_type, normalized,
                 json.dumps(metadata) if metadata else None))
            self.conn.commit()

    def add_texture_format(self, format_id: str, name: str, channels: int,
                         bits_per_channel: int, compression: str = None,
                         metadata: Optional[Dict] = None):
        """Add new texture format"""
        with self._lock:
            self.conn.execute("""
                INSERT OR REPLACE INTO texture_formats
                (format_id, name, channels, bits_per_channel, compression, metadata)
                VALUES (?, ?, ?, ?, ?, ?)
            """, (format_id, name, channels, bits_per_channel, compression,
                 json.dumps(metadata) if metadata else None))
            self.conn.commit()

    def list_shader_types(self) -> List[Dict]:
        """List all shader types"""
        with self._lock:
            results = self.conn.execute("""
                SELECT type_id, name, description, metadata
                FROM shader_types
            """).fetchall()
            
        return [{
            'type_id': r[0],
            'name': r[1],
            'description': r[2],
            'metadata': json.loads(r[3]) if r[3] else {}
        } for r in results]

    def list_uniform_types(self) -> List[Dict]:
        """List all uniform types"""
        with self._lock:
            results = self.conn.execute("""
                SELECT type_id, name, size, alignment, metadata
                FROM uniform_types
            """).fetchall()
            
        return [{
            'type_id': r[0],
            'name': r[1],
            'size': r[2],
            'alignment': r[3],
            'metadata': json.loads(r[4]) if r[4] else {}
        } for r in results]

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

    def __del__(self):
        self.close()
