import os
from datetime import datetime, timezone
from urllib.parse import quote_plus
from sqlalchemy import Column, Integer, String, Text, DateTime
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession
from sqlalchemy.orm import sessionmaker
from dotenv import load_dotenv

load_dotenv()

# Read Database credentials from .env
DB_HOST = os.getenv("DB_HOST", "localhost")
DB_PORT = os.getenv("DB_PORT", "5432")
DB_NAME = os.getenv("DB_NAME", "postgres")
DB_USER = os.getenv("DB_USER", "postgres")
DB_PASSWORD = os.getenv("DB_PASSWORD", "")

# Safely URL-encode the password to handle '#' and '@' special characters
ENCODED_PASSWORD = quote_plus(DB_PASSWORD)

# Construct PostgreSQL async connection string
DATABASE_URL = f"postgresql+asyncpg://{DB_USER}:{ENCODED_PASSWORD}@{DB_HOST}:{DB_PORT}/{DB_NAME}"

# Create Async Engine for PostgreSQL
engine = create_async_engine(DATABASE_URL, echo=False)

# Session factory for DB operations
AsyncSessionLocal = sessionmaker(
    bind=engine, class_=AsyncSession, expire_on_commit=False
)

Base = declarative_base()

# Chat Message Model
class ChatMessage(Base):
    __tablename__ = "chat_messages"

    id = Column(Integer, primary_key=True, index=True)
    whatsapp_id = Column(String(100), unique=True, nullable=True) # Meta's wamid
    sender_phone = Column(String(30), nullable=False, index=True)
    sender_name = Column(String(100), nullable=True)
    message_type = Column(String(20), default="text")
    message_body = Column(Text, nullable=True)
    direction = Column(String(10), nullable=False) # 'inbound' or 'outbound'
    timestamp = Column(DateTime(timezone=True), default=lambda: datetime.now(timezone.utc))

# Initialize database tables on startup
async def init_db():
    async with engine.begin() as conn:
        await conn.run_sync(Base.metadata.create_all)

# --- Function called from main.py to save messages ---
async def save_chat_message(
    direction: str,
    sender_phone: str,
    message_body: str,
    whatsapp_id: str = None,
    sender_name: str = None,
    message_type: str = "text"
):
    async with AsyncSessionLocal() as session:
        chat = ChatMessage(
            direction=direction,
            sender_phone=sender_phone,
            sender_name=sender_name,
            whatsapp_id=whatsapp_id,
            message_type=message_type,
            message_body=message_body
        )
        session.add(chat)
        await session.commit()