"""
PostgreSQL database abstraction layer for the Hyperliquid trading toolkit.

Provides a thin wrapper around psycopg2 to centralize database operations,
handle table name sanitization, and abstract SQL dialect differences
from the SQLite-based codebase.
"""

import os
import psycopg2
from psycopg2.extras import execute_values

PG_CONN_STR = os.environ.get(
    "PG_CONN_STR",
    "postgresql://hyper:hyper@localhost:5432/hyper"
)


def get_connection():
    """Return a new psycopg2 connection to the PostgreSQL database."""
    return psycopg2.connect(PG_CONN_STR)


def sanitize_table_name(coin, timeframe):
    """
    Sanitize a coin/timeframe pair into a PostgreSQL-safe table name.

    Replaces colons with underscores (e.g., 'xyz:BRENTOIL' -> 'xyz_BRENTOIL')
    to ensure compatibility with PostgreSQL identifier rules.
    """
    return f"{coin.replace(':', '_')}_{timeframe}"


def create_candle_table(conn, table_name):
    """
    Create a candle table if it does not already exist.

    Schema matches the original SQLite layout:
      datetime_utc, timestamp_ms (PK), open, high, low, close, volume, number_of_trades
    Also creates an index on datetime_utc for time-range queries.
    """
    with conn.cursor() as cur:
        cur.execute(f'''
            CREATE TABLE IF NOT EXISTS "{table_name}" (
                datetime_utc TIMESTAMP,
                timestamp_ms BIGINT PRIMARY KEY,
                open REAL,
                high REAL,
                low REAL,
                close REAL,
                volume REAL,
                number_of_trades INTEGER
            )
        ''')
        cur.execute(
            f'CREATE INDEX IF NOT EXISTS "idx_{table_name}_time" ON "{table_name}"(datetime_utc)'
        )
        conn.commit()


def upsert_candles(conn, table_name, records):
    """
    Batch upsert candle records using PostgreSQL ON CONFLICT.

    Args:
        conn: psycopg2 connection
        table_name: sanitized table name (e.g., 'BTC_1m')
        records: list of tuples (datetime_utc, timestamp_ms, open, high,
                 low, close, volume, number_of_trades)

    Returns:
        Number of records upserted.
    """
    if not records:
        return 0

    with conn.cursor() as cur:
        execute_values(
            cur,
            f'''
                INSERT INTO "{table_name}"
                (datetime_utc, timestamp_ms, open, high, low, close, volume, number_of_trades)
                VALUES %s
                ON CONFLICT (timestamp_ms) DO UPDATE SET
                    datetime_utc = EXCLUDED.datetime_utc,
                    open = EXCLUDED.open,
                    high = EXCLUDED.high,
                    low = EXCLUDED.low,
                    close = EXCLUDED.close,
                    volume = EXCLUDED.volume,
                    number_of_trades = EXCLUDED.number_of_trades
            ''',
            records,
            page_size=1000
        )
        conn.commit()
    return len(records)


def get_last_timestamp(conn, table_name):
    """Return the most recent timestamp_ms from a table, or None."""
    with conn.cursor() as cur:
        cur.execute(f'SELECT MAX(timestamp_ms) FROM "{table_name}"')
        result = cur.fetchone()
        return result[0] if result and result[0] is not None else None


def get_table_count(conn, table_name):
    """Return the total row count of a table."""
    with conn.cursor() as cur:
        cur.execute(f'SELECT COUNT(*) FROM "{table_name}"')
        return cur.fetchone()[0]


def table_exists(conn, table_name):
    """Check if a table exists in the database."""
    with conn.cursor() as cur:
        cur.execute(
            "SELECT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = %s)",
            (table_name,)
        )
        return cur.fetchone()[0]


def get_table_columns(conn, table_name):
    """Return a list of column names for a table."""
    with conn.cursor() as cur:
        cur.execute(
            "SELECT column_name FROM information_schema.columns WHERE table_name = %s",
            (table_name,)
        )
        return [row[0] for row in cur.fetchall()]