"""
Database manager for graphics type definitions
"""
import duckdb
import time
import logging
import os
from pathlib import Path
from typing import Dict, List, Optional, Tuple, Union

# Create data directory if it doesn't exist
os.makedirs("data", exist_ok=True)

class GraphicsTypeDB:
    _instance = None
    DB_FILE = "data/graphics_types.db"

    def __new__(cls):
        if cls._instance is None:
            cls._instance = super(GraphicsTypeDB, cls).__new__(cls)
            cls._instance.init_db()
        return cls._instance

    def init_db(self, max_retries: int = 3):
        self.max_retries = max_retries
        self._connect_with_retries()
        
    def _connect_with_retries(self):
        """Establish database connection with retry logic"""
        for attempt in range(self.max_retries):
            try:
                self.conn = self._init_db_connection()
                self._init_tables()
                self._init_enums()
                return
            except Exception as e:
                if attempt == self.max_retries - 1:
                    raise RuntimeError(f"Failed to initialize database after {self.max_retries} attempts: {str(e)}")
                time.sleep(1)
        
    def _init_db_connection(self) -> duckdb.DuckDBPyConnection:
        """Initialize database connection"""
        return duckdb.connect(self.DB_FILE)
        con.execute(f"SET s3_access_key_id='{self.HF_TOKEN}';")
        con.execute(f"SET s3_secret_access_key='{self.HF_TOKEN}';")
        
        return con

    def _init_tables(self):
        # Shader types
        self.conn.execute("""
            CREATE TABLE IF NOT EXISTS shader_types (
                id VARCHAR PRIMARY KEY,
                name VARCHAR UNIQUE,
                description TEXT
            )
        """)

        # Primitive types
        self.conn.execute("""
            CREATE TABLE IF NOT EXISTS primitive_types (
                id INTEGER PRIMARY KEY,
                name VARCHAR UNIQUE
            )
        """)

        # Format definitions
        self.conn.execute("""
            CREATE TABLE IF NOT EXISTS format_types (
                id INTEGER PRIMARY KEY,
                name VARCHAR UNIQUE,
                size_bits INTEGER,
                components INTEGER,
                is_float BOOLEAN
            )
        """)

    def ensure_connection(self):
        """Ensure database connection is active and valid"""
        try:
            self.conn.execute("SELECT 1")
        except:
            logging.warning("Database connection lost, attempting to reconnect...")
            self._connect_with_retries()

    def _init_enums(self):
        """Initialize shader types"""
        self.ensure_connection()
        shader_types = [
            ("vertex", "VERTEX", "Vertex shader stage"),
            ("fragment", "FRAGMENT", "Fragment/pixel shader stage"),
            ("geometry", "GEOMETRY", "Geometry shader stage"), 
            ("compute", "COMPUTE", "Compute shader stage"),
            ("tess_control", "TESSELLATION_CONTROL", "Tessellation control shader"),
            ("tess_eval", "TESSELLATION_EVALUATION", "Tessellation evaluation shader")
        ]
        
        self.conn.execute("""
            INSERT OR REPLACE INTO shader_types (id, name, description)
            VALUES (?, ?, ?)
        """, shader_types)

        # Initialize primitive types
        primitive_types = [
            (1, "POINTS"),
            (2, "LINES"),
            (3, "LINE_STRIP"),
            (4, "TRIANGLES"),
            (5, "TRIANGLE_STRIP"),
            (6, "TRIANGLE_FAN"),
            (7, "PATCHES")
        ]
        
        self.conn.execute("""
            INSERT OR REPLACE INTO primitive_types (id, name) 
            VALUES (?, ?)
        """, primitive_types)

        # Initialize format types
        format_types = [
            (1, "FLOAT32", 32, 1, True),
            (2, "VEC2", 32, 2, True),
            (3, "VEC3", 32, 3, True),
            (4, "VEC4", 32, 4, True),
            (5, "INT32", 32, 1, False),
            (6, "UINT32", 32, 1, False),
            (7, "RGBA8_UNORM", 8, 4, False)
        ]

        self.conn.execute("""
            INSERT OR REPLACE INTO format_types (id, name, size_bits, components, is_float)
            VALUES (?, ?, ?, ?, ?)
        """, format_types)

    def get_shader_types(self) -> List[Tuple[str, str, str]]:
        self.ensure_connection()
        return self.conn.execute(
            "SELECT id, name, description FROM shader_types"
        ).fetchall()

    def get_primitive_types(self) -> List[Tuple[int, str]]:
        self.ensure_connection()
        return self.conn.execute(
            "SELECT id, name FROM primitive_types"
        ).fetchall()

    def get_format_types(self) -> List[Tuple[int, str, int, int, bool]]:
        self.ensure_connection()
        return self.conn.execute(
            "SELECT id, name, size_bits, components, is_float FROM format_types"
        ).fetchall()

    def get_shader_type(self, id_or_name: str) -> Optional[Tuple[str, str, str]]:
        self.ensure_connection()
        return self.conn.execute("""
            SELECT id, name, description FROM shader_types
            WHERE id = ? OR name = ?
        """, [id_or_name, id_or_name]).fetchone()

    def get_primitive_type(self, id_or_name: Union[int, str]) -> Optional[Tuple[int, str]]:
        self.ensure_connection()
        return self.conn.execute("""
            SELECT id, name FROM primitive_types
            WHERE id = ? OR name = ?
        """, [id_or_name, id_or_name]).fetchone()

    def get_format_type(self, id_or_name: Union[int, str]) -> Optional[Tuple[int, str, int, int, bool]]:
        self.ensure_connection()
        return self.conn.execute("""
            SELECT id, name, size_bits, components, is_float FROM format_types
            WHERE id = ? OR name = ?
        """, [id_or_name, id_or_name]).fetchone()
