from sqlalchemy import Column, Integer, String, Float, Boolean, DateTime, Text, ForeignKey, Index
from sqlalchemy.orm import relationship
from datetime import datetime, timezone
from database.db import Base


def _utcnow():
    return datetime.now(timezone.utc)


class Email(Base):
    __tablename__ = "emails"

    id = Column(Integer, primary_key=True)
    subject = Column(String(500))
    sender = Column(String(200))
    received_at = Column(DateTime)
    raw_body = Column(Text)
    category = Column(String(50))          # tonnage | cargo_vc | cargo_tc | unknown
    confidence = Column(Float, default=0.0)
    is_duplicate = Column(Boolean, default=False)
    fingerprint = Column(String(64), nullable=True, index=True)  # SHA-256 of normalised body
    created_at = Column(DateTime, default=_utcnow)

    tonnage = relationship("Tonnage", back_populates="email", cascade="all, delete-orphan")
    cargo_vc = relationship("CargoVC", back_populates="email", cascade="all, delete-orphan")
    cargo_tc = relationship("CargoTC", back_populates="email", cascade="all, delete-orphan")

    def to_dict(self, include_extracted=False):
        d = {
            "id": self.id,
            "subject": self.subject,
            "sender": self.sender,
            "received_at": self.received_at.isoformat() if self.received_at else None,
            "raw_body": self.raw_body,
            "category": self.category,
            "confidence": round(self.confidence or 0, 3),
            "is_duplicate": self.is_duplicate,
            "fingerprint": self.fingerprint,
            "created_at": self.created_at.isoformat() if self.created_at else None,
        }
        if include_extracted:
            d["tonnage"] = [t.to_dict() for t in self.tonnage]
            d["cargo_vc"] = [c.to_dict() for c in self.cargo_vc]
            d["cargo_tc"] = [c.to_dict() for c in self.cargo_tc]
        return d


class Tonnage(Base):
    __tablename__ = "tonnage"

    id = Column(Integer, primary_key=True)
    email_id = Column(Integer, ForeignKey("emails.id"))
    vessel_name = Column(String(200))
    account_name = Column(String(200))
    open_port = Column(String(200))
    open_date = Column(String(100))
    vessel_type = Column(String(100))
    vessel_size = Column(String(100))
    flag = Column(String(100))
    built_year = Column(String(20))
    class_society = Column(String(100))
    loa = Column(String(50))
    beam = Column(String(50))

    email = relationship("Email", back_populates="tonnage")

    def to_dict(self):
        return {
            "id": self.id,
            "email_id": self.email_id,
            "vessel_name": self.vessel_name,
            "account_name": self.account_name,
            "open_port": self.open_port,
            "open_date": self.open_date,
            "vessel_type": self.vessel_type,
            "vessel_size": self.vessel_size,
            "flag": self.flag,
            "built_year": self.built_year,
            "class_society": self.class_society,
            "loa": self.loa,
            "beam": self.beam,
        }


class CargoVC(Base):
    __tablename__ = "cargo_vc"

    id = Column(Integer, primary_key=True)
    email_id = Column(Integer, ForeignKey("emails.id"))
    account_name = Column(String(200))
    cargo_name = Column(String(200))
    loading_port = Column(String(200))
    discharge_port = Column(String(200))
    laycan = Column(String(100))
    cargo_type = Column(String(100))
    quantity = Column(String(100))

    email = relationship("Email", back_populates="cargo_vc")

    def to_dict(self):
        return {
            "id": self.id,
            "email_id": self.email_id,
            "account_name": self.account_name,
            "cargo_name": self.cargo_name,
            "loading_port": self.loading_port,
            "discharge_port": self.discharge_port,
            "laycan": self.laycan,
            "cargo_type": self.cargo_type,
            "quantity": self.quantity,
        }


class CargoTC(Base):
    __tablename__ = "cargo_tc"

    id = Column(Integer, primary_key=True)
    email_id = Column(Integer, ForeignKey("emails.id"))
    account_name = Column(String(200))
    cargo_name = Column(String(200))
    delivery_port = Column(String(200))
    redelivery_port = Column(String(200))
    duration = Column(String(100))
    laycan = Column(String(100))
    cargo_type = Column(String(100))

    email = relationship("Email", back_populates="cargo_tc")

    def to_dict(self):
        return {
            "id": self.id,
            "email_id": self.email_id,
            "account_name": self.account_name,
            "cargo_name": self.cargo_name,
            "delivery_port": self.delivery_port,
            "redelivery_port": self.redelivery_port,
            "duration": self.duration,
            "laycan": self.laycan,
            "cargo_type": self.cargo_type,
        }