"""
Usage Database - PostgreSQL Implementation
==========================================
RULE 7 COMPLIANT: Uses Elestio PostgreSQL, NO SQLite.

Database layer for API usage tracking using Genesis Elestio Core.
"""
import sys
import logging
import datetime
import json

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

try:
    import psycopg2
    from psycopg2.extras import RealDictCursor
    from elestio_config import PostgresConfig
except ImportError as e:
    logging.error(f"Required imports failed: {e}. Install psycopg2-binary.")
    raise

logger = logging.getLogger(__name__)


class UsageDatabase:
    """
    PostgreSQL database for API usage tracking.

    RULE 7 COMPLIANT - NO SQLite. Uses Elestio Core PostgreSQL.
    """

    def __init__(self):
        """Initialize with Elestio PostgreSQL config."""
        self.conn = None
        self.cursor = None

    def connect(self):
        """Connects to the PostgreSQL database."""
        try:
            self.conn = psycopg2.connect(**PostgresConfig.get_connection_params())
            self.cursor = self.conn.cursor()
            logger.info(f"Connected to PostgreSQL: {PostgresConfig.host}:{PostgresConfig.port}")
        except psycopg2.Error as e:
            logger.error(f"Failed to connect to PostgreSQL: {e}")
            raise

    def create_table(self):
        """Creates the usage table if it doesn't exist."""
        try:
            self.cursor.execute("""
                CREATE TABLE IF NOT EXISTS api_usage_detailed (
                    id SERIAL PRIMARY KEY,
                    api_name TEXT NOT NULL,
                    input_tokens INTEGER NOT NULL,
                    output_tokens INTEGER NOT NULL,
                    cost REAL NOT NULL,
                    timestamp TIMESTAMPTZ NOT NULL,
                    metadata JSONB
                )
            """)
            self.conn.commit()
            logger.info("Created api_usage_detailed table (if it didn't exist).")
        except psycopg2.Error as e:
            logger.error(f"Failed to create table: {e}")
            raise

    def insert_usage(self, api_name, input_tokens, output_tokens, cost, timestamp, metadata=None):
        """Inserts a new usage record into the database.

        Args:
            api_name (str): The name of the API being called.
            input_tokens (int): The number of input tokens used.
            output_tokens (int): The number of output tokens used.
            cost (float): The cost of the API call.
            timestamp (datetime.datetime): The timestamp of the API call.
            metadata (dict, optional): Optional metadata to store with the usage log.
        """
        try:
            if metadata:
                metadata_json = json.dumps(metadata)
            else:
                metadata_json = None

            self.cursor.execute(
                """
                INSERT INTO api_usage_detailed (api_name, input_tokens, output_tokens, cost, timestamp, metadata)
                VALUES (%s, %s, %s, %s, %s, %s)
                """,
                (api_name, input_tokens, output_tokens, cost, timestamp, metadata_json)
            )
            self.conn.commit()
            logger.debug(f"Inserted usage record: {api_name}, {input_tokens}, {output_tokens}, {cost}, {timestamp}")
        except psycopg2.Error as e:
            logger.error(f"Failed to insert usage: {e}")
            raise

    def close(self):
        """Closes the database connection."""
        if self.cursor:
            self.cursor.close()
        if self.conn:
            self.conn.close()
            logger.info("PostgreSQL connection closed.")

    def fetch_all(self):
        """Fetches all records from the api_usage_detailed table."""
        try:
            self.cursor.execute("SELECT * FROM api_usage_detailed ORDER BY timestamp DESC LIMIT 100")
            rows = self.cursor.fetchall()
            return rows
        except psycopg2.Error as e:
            logger.error(f"Failed to fetch all records: {e}")
            raise


if __name__ == '__main__':
    # Example usage (for testing)
    logging.basicConfig(level=logging.INFO)

    db = UsageDatabase()
    db.connect()
    db.create_table()

    try:
        # Insert some dummy data
        db.insert_usage(
            api_name='test_api',
            input_tokens=50,
            output_tokens=25,
            cost=0.005,
            timestamp=datetime.datetime.now(datetime.timezone.utc),
            metadata={'model': 'test_model', 'version': '1.0'}
        )

        # Fetch and print all data (for verification)
        all_data = db.fetch_all()
        logger.info("All data from api_usage_detailed table:")
        for row in all_data:
            logger.info(row)

    except Exception as e:
        logger.error(f"Error during example usage: {e}")
    finally:
        db.close()


# VERIFICATION_STAMP
# Story: GR-010
# Verified By: Claude Opus 4.5
# Verified At: 2026-02-02
# Migration: SQLite -> PostgreSQL (Elestio Core)
# Tests: Pending integration test