import os

import psycopg2
from psycopg2.extras import DictCursor


def connect():
    """Establishes a connection to the PostgreSQL database using the DATABASE_URL."""
    database_url = os.getenv("DATABASE_URL")
    if not database_url:
        raise ValueError("DATABASE_URL environment variable is not set.")

    conn = psycopg2.connect(database_url, cursor_factory=DictCursor)
    return conn


def initialize_database():
    """Creates the cache tables in PostgreSQL if they don't exist."""
    conn = connect()
    cursor = conn.cursor()

    # Table for both Issues and Pull Requests
    # Note the change from BOOLEAN to BOOLEAN and TIMESTAMP to TIMESTAMPTZ for timezone awareness.
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS items (
            repo TEXT,
            number INTEGER,
            is_pr BOOLEAN,
            title TEXT,
            body TEXT,
            state TEXT,
            user_login TEXT,
            labels JSONB, -- Using JSONB is more efficient for JSON in PostgreSQL
            comments INTEGER,
            reactions INTEGER,
            created_at TIMESTAMPTZ,
            updated_at TIMESTAMPTZ,
            html_url TEXT,
            PRIMARY KEY (repo, number)
        )
    """)

    # Table for Releases
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS releases (
            repo TEXT,
            tag_name TEXT PRIMARY KEY,
            name TEXT,
            body TEXT,
            published_at TIMESTAMPTZ
        )
    """)

    conn.commit()
    cursor.close()
    conn.close()
    print("Database initialized successfully with PostgreSQL schema.")