# app/database.py
import asyncpg
from dotenv import load_dotenv
import os

load_dotenv()  # Carga las variables del .env

DB_HOST = os.getenv("DB_HOST")
DB_PORT = os.getenv("DB_PORT")
DB_NAME = os.getenv("DB_NAME")
DB_USER = os.getenv("DB_USER")
DB_PASSWORD = os.getenv("DB_PASSWORD")
DB_SCHEMA = os.getenv("DB_SCHEMA", "public")  # por defecto 'public'

DATABASE_URL = f"postgresql://{DB_USER}:{DB_PASSWORD}@{DB_HOST}:{DB_PORT}/{DB_NAME}"

class Database:
    def __init__(self):
        self.pool = None

    async def connect(self):
        self.pool = await asyncpg.create_pool(dsn=DATABASE_URL)
        # Cambiar el esquema por defecto (opcional)
        async with self.pool.acquire() as conn:
            await conn.execute(f'SET search_path TO {DB_SCHEMA};')
        print("✅ Conectado a PostgreSQL")

    async def disconnect(self):
        if self.pool:
            await self.pool.close()
            print("❌ Conexión cerrada")

    async def fetch_one(self, query: str, *args):
        async with self.pool.acquire() as conn:
            return await conn.fetchrow(query, *args)

    async def fetch_all(self, query: str, *args):
        async with self.pool.acquire() as conn:
            return await conn.fetch(query, *args)

    async def execute(self, query: str, *args):
        async with self.pool.acquire() as conn:
            return await conn.execute(query, *args)

# Instancia global
db = Database()