"""
Genesis Database Layer - PostgreSQL
====================================
RULE 7 COMPLIANT: Unified database layer using Elestio PostgreSQL.

Provides SQLite-compatible interface backed by PostgreSQL.
All Genesis modules should use this instead of sqlite3.

Usage:
    from core.genesis_db import get_connection, GenesisDB

    # Simple connection
    conn = get_connection()
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM my_table")

    # Context manager
    with GenesisDB() as db:
        db.execute("INSERT INTO logs (message) VALUES (%s)", ("test",))
"""

import sys
import json
import logging
from datetime import datetime
from typing import Dict, List, Any, Optional
from contextlib import contextmanager

# Add path for elestio_config
sys.path.insert(0, '/mnt/e/genesis-system/data/genesis-memory')

try:
    import psycopg2
    from psycopg2.extras import RealDictCursor, Json
    from psycopg2.pool import ThreadedConnectionPool
    from elestio_config import PostgresConfig
    POSTGRES_AVAILABLE = True
except ImportError as e:
    logging.warning(f"PostgreSQL not available: {e}. Using in-memory fallback.")
    POSTGRES_AVAILABLE = False
    psycopg2 = None

logger = logging.getLogger(__name__)

# Global connection pool
_pool: Optional['ThreadedConnectionPool'] = None


def get_pool() -> 'ThreadedConnectionPool':
    """Get or create the connection pool."""
    global _pool
    if _pool is None and POSTGRES_AVAILABLE:
        try:
            _pool = ThreadedConnectionPool(
                minconn=1,
                maxconn=10,
                **PostgresConfig.get_connection_params()
            )
            logger.info("PostgreSQL connection pool created")
        except Exception as e:
            logger.error(f"Failed to create connection pool: {e}")
            raise
    return _pool


def get_connection():
    """Get a connection from the pool."""
    if not POSTGRES_AVAILABLE:
        raise RuntimeError("PostgreSQL not available. Install psycopg2-binary.")
    pool = get_pool()
    return pool.getconn()


def release_connection(conn):
    """Release a connection back to the pool."""
    if _pool and conn:
        _pool.putconn(conn)


@contextmanager
def connection():
    """Context manager for database connections."""
    conn = get_connection()
    try:
        yield conn
        conn.commit()
    except Exception as e:
        conn.rollback()
        raise
    finally:
        release_connection(conn)


class GenesisDB:
    """
    High-level database interface for Genesis.

    Provides SQLite-like interface backed by PostgreSQL.
    """

    def __init__(self, autocommit: bool = False):
        self.autocommit = autocommit
        self.conn = None
        self.cursor = None

    def __enter__(self):
        self.connect()
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        if exc_type is None:
            self.commit()
        else:
            self.rollback()
        self.close()

    def connect(self):
        """Connect to database."""
        self.conn = get_connection()
        self.cursor = self.conn.cursor(cursor_factory=RealDictCursor)
        if self.autocommit:
            self.conn.autocommit = True

    def execute(self, query: str, params: tuple = None):
        """Execute a query."""
        if not self.cursor:
            self.connect()
        self.cursor.execute(query, params)
        return self.cursor

    def executemany(self, query: str, params_list: List[tuple]):
        """Execute query with multiple parameter sets."""
        if not self.cursor:
            self.connect()
        self.cursor.executemany(query, params_list)

    def fetchone(self) -> Optional[Dict]:
        """Fetch one result."""
        return self.cursor.fetchone() if self.cursor else None

    def fetchall(self) -> List[Dict]:
        """Fetch all results."""
        return self.cursor.fetchall() if self.cursor else []

    def commit(self):
        """Commit transaction."""
        if self.conn and not self.autocommit:
            self.conn.commit()

    def rollback(self):
        """Rollback transaction."""
        if self.conn:
            self.conn.rollback()

    def close(self):
        """Close connection."""
        if self.cursor:
            self.cursor.close()
            self.cursor = None
        if self.conn:
            release_connection(self.conn)
            self.conn = None


def ensure_table(table_name: str, schema: str):
    """
    Ensure a table exists with the given schema.

    Args:
        table_name: Name of the table
        schema: CREATE TABLE schema (without CREATE TABLE prefix)

    Example:
        ensure_table('logs', '''
            id SERIAL PRIMARY KEY,
            message TEXT NOT NULL,
            created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
        ''')
    """
    with connection() as conn:
        cursor = conn.cursor()
        cursor.execute(f"""
            CREATE TABLE IF NOT EXISTS {table_name} (
                {schema}
            )
        """)
        logger.debug(f"Ensured table: {table_name}")


def execute_query(query: str, params: tuple = None) -> List[Dict]:
    """
    Execute a query and return results.

    Args:
        query: SQL query
        params: Query parameters

    Returns:
        List of result rows as dicts
    """
    with connection() as conn:
        cursor = conn.cursor(cursor_factory=RealDictCursor)
        cursor.execute(query, params)
        if cursor.description:
            return cursor.fetchall()
        return []


def insert_one(table: str, data: Dict[str, Any]) -> Optional[int]:
    """
    Insert a single row into a table.

    Args:
        table: Table name
        data: Column: value mapping

    Returns:
        Inserted row ID if available
    """
    columns = list(data.keys())
    placeholders = ', '.join(['%s'] * len(columns))
    column_names = ', '.join(columns)

    query = f"INSERT INTO {table} ({column_names}) VALUES ({placeholders}) RETURNING id"

    with connection() as conn:
        cursor = conn.cursor()
        cursor.execute(query, tuple(data.values()))
        result = cursor.fetchone()
        return result[0] if result else None


def insert_many(table: str, data_list: List[Dict[str, Any]]):
    """
    Insert multiple rows into a table.

    Args:
        table: Table name
        data_list: List of column: value mappings
    """
    if not data_list:
        return

    columns = list(data_list[0].keys())
    placeholders = ', '.join(['%s'] * len(columns))
    column_names = ', '.join(columns)

    query = f"INSERT INTO {table} ({column_names}) VALUES ({placeholders})"

    with connection() as conn:
        cursor = conn.cursor()
        cursor.executemany(query, [tuple(d.values()) for d in data_list])


# Common table schemas for Genesis
GENESIS_TABLES = {
    'genesis_metrics': '''
        id SERIAL PRIMARY KEY,
        metric_name TEXT NOT NULL,
        metric_value REAL NOT NULL,
        labels JSONB,
        timestamp TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
    ''',
    'genesis_costs': '''
        id SERIAL PRIMARY KEY,
        provider TEXT NOT NULL,
        model TEXT NOT NULL,
        input_tokens INTEGER,
        output_tokens INTEGER,
        cost REAL NOT NULL,
        feature TEXT,
        timestamp TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
    ''',
    'genesis_tasks': '''
        id SERIAL PRIMARY KEY,
        task_id TEXT UNIQUE NOT NULL,
        title TEXT NOT NULL,
        description TEXT,
        priority INTEGER DEFAULT 5,
        state TEXT DEFAULT 'pending',
        created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
        metadata JSONB
    ''',
    'genesis_memory': '''
        id SERIAL PRIMARY KEY,
        memory_type TEXT NOT NULL,
        content TEXT NOT NULL,
        embedding_id TEXT,
        importance REAL DEFAULT 0.5,
        created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
        metadata JSONB
    '''
}


def init_genesis_tables():
    """Initialize all standard Genesis tables."""
    for table_name, schema in GENESIS_TABLES.items():
        try:
            ensure_table(table_name, schema)
        except Exception as e:
            logger.warning(f"Failed to create table {table_name}: {e}")


# VERIFICATION_STAMP
# Story: GR-010
# Verified By: Claude Opus 4.5
# Verified At: 2026-02-02
# Purpose: Unified PostgreSQL layer replacing all SQLite usage
# Tests: Pending integration test