from sqlalchemy import Column, Integer, String, TIMESTAMP, ForeignKey
from sqlalchemy.sql import func
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy.future import select
from .database import Base
from sqlalchemy.orm import relationship

class Transcription(Base):
    __tablename__ = "transcriptions"

    id = Column(Integer, primary_key=True, index=True)
    session_id = Column(String, ForeignKey("sessions.session_id",onupdate='CASCADE',ondelete='CASCADE'))
    chunk_number = Column(Integer)
    text = Column(String)
    language = Column(String)
    created_at = Column(TIMESTAMP(timezone=True), server_default=func.now())
    
    session = relationship("Session", back_populates="transcriptions")


# CRUD operations
async def create_transcription(db: AsyncSession, session_id: str, chunk_number: int, text: str, language: str):
    new_transcription = Transcription(
        session_id=session_id,
        chunk_number=chunk_number,
        text=text,
        language=language
    )
    db.add(new_transcription)
    await db.commit()
    await db.refresh(new_transcription)
    return new_transcription

async def get_transcriptions_by_session(db: AsyncSession, session_id: str):
    result = await db.execute(select(Transcription).where(Transcription.session_id == session_id))
    return result.scalars().all()

async def update_transcription_text(db: AsyncSession, transcription_id: int, new_text: str):
    result = await db.execute(select(Transcription).where(Transcription.id == transcription_id))
    transcription = result.scalar_one_or_none()
    if transcription:
        transcription.text = new_text
        await db.commit()
        await db.refresh(transcription)
    return transcription

async def delete_transcription(db: AsyncSession, transcription_id: int):
    result = await db.execute(select(Transcription).where(Transcription.id == transcription_id))
    transcription = result.scalar_one_or_none()
    if transcription:
        await db.delete(transcription)
        await db.commit()
    return transcription