"""
Database models. Designed for MySQL (utf8mb4) but works on SQLite for eval.

Schema overview:
  Issue          -> one publication day (a "daily issue"). You set expected count.
  Application    -> one trademark application within an issue (one per PDF).
  Subscriber     -> recipient of reports.
  Invoice        -> monthly/on-demand billing per subscriber.
  EmailLog       -> record of every email sent (one per day/issue per recipient).
"""
import datetime as dt
from sqlalchemy import (
    create_engine, Column, Integer, String, Text, Date, DateTime,
    Boolean, ForeignKey, Numeric, Enum,
)
from sqlalchemy.orm import declarative_base, relationship, sessionmaker
from .config import config

Base = declarative_base()
engine = create_engine(config.SQLALCHEMY_DATABASE_URI, pool_pre_ping=True, future=True)
SessionLocal = sessionmaker(bind=engine, autoflush=False, future=True)


class Issue(Base):
    __tablename__ = "issues"
    id = Column(Integer, primary_key=True)
    publication_date = Column(Date, nullable=True, index=True)
    title = Column(String(255))                       # e.g. "Iran TM Bulletin"
    expected_count = Column(Integer, default=0)       # # of apps you expect that day
    status = Column(
        Enum("open", "complete", "sent", name="issue_status"),
        default="open", nullable=False,
    )
    created_at = Column(DateTime, default=dt.datetime.utcnow)
    sent_at = Column(DateTime, nullable=True)

    applications = relationship(
        "Application", back_populates="issue",
        cascade="all, delete-orphan", order_by="Application.id",
    )

    @property
    def opposition_deadline(self):
        if not self.publication_date:
            return None
        return self.publication_date + dt.timedelta(days=config.OPPOSITION_WINDOW_DAYS)

    @property
    def loaded_count(self):
        return len(self.applications)

    @property
    def is_full(self):
        return self.expected_count and self.loaded_count >= self.expected_count


class Application(Base):
    __tablename__ = "applications"
    id = Column(Integer, primary_key=True)
    issue_id = Column(Integer, ForeignKey("issues.id"), nullable=False, index=True)

    # Publication identifiers — the public-facing number/date (شماره/تاریخ آگهی)
    pub_number = Column("adv_number", String(64), index=True)
    pub_date = Column("adv_date", Date, nullable=True)
    pub_date_raw = Column("adv_date_raw", String(32))

    # Core trademark fields
    app_number = Column(String(64), index=True)       # application (اظهارنامه) no.
    app_date = Column(Date, nullable=True)            # filing date (Gregorian)
    app_date_raw = Column(String(32))                 # original Jalali string

    # Mark — source (Farsi) + target-language transliteration & translation
    mark_text = Column(Text)                          # mark description (Farsi)
    mark_translit = Column("mark_text_en", String(512))   # transliteration (target lang)
    mark_translation = Column(String(512))                # translation/meaning (target lang)

    # Applicant — source (Farsi) + target-language transliteration
    applicant = Column("owner", String(512))              # applicant (Farsi)
    applicant_en = Column("owner_en", String(512))        # applicant (target lang, translit)
    applicant_type = Column(String(16))                   # 'individual' | 'company'
    applicant_entity = Column("owner_entity", String(255))  # نوع شخص حقوقی
    applicant_entity_en = Column(String(255))             # entity type (target lang)
    applicant_reg_no = Column("owner_reg_no", String(64))   # شماره ثبت
    nationality = Column(String(128))                 # تابعیت
    nationality_en = Column(String(128))               # nationality (target lang)
    legal_rep = Column(String(512))                   # نماینده قانونی
    address = Column(Text)                            # نشانی

    nice_class = Column(String(128))                  # e.g. "29, 35"
    goods_services = Column(Text)                     # source (Farsi)
    goods_services_en = Column(Text)                  # target-language translation
    disclaimer = Column(Text)                         # source (Farsi)
    disclaimer_en = Column(Text)                      # target-language translation

    # Mark image (logo) extracted from PDF
    mark_image_path = Column(String(512), nullable=True)

    # Source file
    source_pdf_path = Column(String(512))
    source_pdf_name = Column(String(255))

    # Workflow
    verified = Column(Boolean, default=False)
    extraction_ok = Column(Boolean, default=True)
    created_at = Column(DateTime, default=dt.datetime.utcnow)

    issue = relationship("Issue", back_populates="applications")
    transliterations = relationship(
        "Transliteration", back_populates="application",
        cascade="all, delete-orphan", order_by="Transliteration.id",
    )
    field_translations = relationship(
        "FieldTranslation", back_populates="application",
        cascade="all, delete-orphan",
    )

    def tr(self, field, lang):
        """Return the translation of `field` in `lang`, falling back to English
        columns for en, and to the source for fa."""
        if lang == "en":
            return {
                "mark_translit": self.mark_translit,
                "mark_translation": self.mark_translation,
                "applicant": self.applicant_en,
                "goods_services": self.goods_services_en,
                "disclaimer": self.disclaimer_en,
            }.get(field)
        if lang == "fa":
            return {
                "mark_translit": self.mark_text,
                "applicant": self.applicant,
                "goods_services": self.goods_services,
                "disclaimer": self.disclaimer,
            }.get(field)
        for ft in self.field_translations:
            if ft.lang == lang and ft.field == field:
                return ft.value
        return None

    @property
    def opposition_deadline(self):
        # Opposition runs 30 days from the advertisement (publication) date.
        base = self.pub_date or (self.issue.publication_date if self.issue else None)
        if not base:
            return None
        return base + dt.timedelta(days=config.OPPOSITION_WINDOW_DAYS)

    @property
    def primary_translit(self):
        for t in self.transliterations:
            if t.is_primary:
                return t.value
        return self.mark_translit or None

    def class_set(self):
        """Set of this application's Nice classes as strings."""
        if not self.nice_class:
            return set()
        return {c.strip() for c in self.nice_class.replace("،", ",").split(",")
                if c.strip()}

    def matches_classes(self, watched):
        """True if this app falls within `watched` (a set), or watched is None (all)."""
        if watched is None:
            return True
        return bool(self.class_set() & watched)


class Transliteration(Base):
    __tablename__ = "transliterations"
    id = Column(Integer, primary_key=True)
    application_id = Column(Integer, ForeignKey("applications.id"),
                            nullable=False, index=True)
    value = Column(String(255), nullable=False, index=True)
    kind = Column(String(16), default="auto")   # latin_pdf|auto|manual|translation
    is_primary = Column(Boolean, default=False)
    created_at = Column(DateTime, default=dt.datetime.utcnow)

    application = relationship("Application", back_populates="transliterations")


class FieldTranslation(Base):
    """Per-application, per-language translation of a named field.
    Lets us store Chinese/Arabic/etc. translations without a column per language.
    field is one of: mark_translit | mark_translation | applicant | goods_services | disclaimer
    """
    __tablename__ = "field_translations"
    id = Column(Integer, primary_key=True)
    application_id = Column(Integer, ForeignKey("applications.id"),
                            nullable=False, index=True)
    lang = Column(String(8), nullable=False, index=True)   # zh|ar|...
    field = Column(String(32), nullable=False)
    value = Column(Text)
    created_at = Column(DateTime, default=dt.datetime.utcnow)

    application = relationship("Application", back_populates="field_translations")


class WatchedMark(Base):
    """An admin-managed mark to watch on behalf of a client. Each incoming
    application is scored against these for similarity/conflict alerts."""
    __tablename__ = "watched_marks"
    id = Column(Integer, primary_key=True)
    client_name = Column(String(255))                 # whose mark this is
    label = Column(String(255), nullable=False)       # the mark text (Latin or Farsi)
    farsi = Column(String(255))                        # optional Farsi form
    classes = Column(String(255))                      # watched Nice classes; blank=all
    subscriber_id = Column(Integer, ForeignKey("subscribers.id"), nullable=True)
    active = Column(Boolean, default=True)
    created_at = Column(DateTime, default=dt.datetime.utcnow)

    alerts = relationship("ConflictAlert", back_populates="watched",
                          cascade="all, delete-orphan")

    def class_set(self):
        if not self.classes or not self.classes.strip():
            return None
        return {c.strip() for c in self.classes.replace("،", ",").split(",") if c.strip()}


class ConflictAlert(Base):
    """A scored potential conflict between a watched mark and an application."""
    __tablename__ = "conflict_alerts"
    id = Column(Integer, primary_key=True)
    watched_id = Column(Integer, ForeignKey("watched_marks.id"), nullable=False, index=True)
    application_id = Column(Integer, ForeignKey("applications.id"), nullable=False, index=True)
    score = Column(Numeric(5, 2))                      # 0..100 similarity
    kind = Column(String(32))                          # exact|variant|fuzzy|phonetic
    matched_on = Column(String(255))                   # the strings that matched
    class_overlap = Column(Boolean, default=False)
    status = Column(String(16), default="open")        # open|dismissed|actioned
    created_at = Column(DateTime, default=dt.datetime.utcnow)

    watched = relationship("WatchedMark", back_populates="alerts")
    application = relationship("Application")


class Subscriber(Base):
    __tablename__ = "subscribers"
    id = Column(Integer, primary_key=True)
    name = Column(String(255), nullable=False)
    email = Column(String(255), nullable=False, unique=True, index=True)
    company = Column(String(255))
    active = Column(Boolean, default=True)
    # default delivery preferences
    pref_format = Column(String(16), default="pdf")   # pdf|docx|html|xlsx
    pref_design = Column(String(32), default="bulletin")
    pref_language = Column(String(16), default="en")  # en|fa (zh later)
    # Comma-separated Nice classes the subscriber watches; empty/None = all classes.
    classes_of_interest = Column(String(255))
    monthly_fee = Column(Numeric(10, 2), default=0)
    created_at = Column(DateTime, default=dt.datetime.utcnow)

    invoices = relationship("Invoice", back_populates="subscriber")

    def watched_classes(self):
        """Return a set of class strings, or None if the subscriber watches all."""
        if not self.classes_of_interest or not self.classes_of_interest.strip():
            return None
        return {c.strip() for c in self.classes_of_interest.replace("،", ",").split(",")
                if c.strip()}


class Invoice(Base):
    __tablename__ = "invoices"
    id = Column(Integer, primary_key=True)
    subscriber_id = Column(Integer, ForeignKey("subscribers.id"), nullable=False)
    number = Column(String(32), unique=True)
    period_start = Column(Date)
    period_end = Column(Date)
    amount = Column(Numeric(10, 2), default=0)
    issued_at = Column(DateTime, default=dt.datetime.utcnow)
    paid = Column(Boolean, default=False)

    subscriber = relationship("Subscriber", back_populates="invoices")


class EmailLog(Base):
    __tablename__ = "email_logs"
    id = Column(Integer, primary_key=True)
    issue_id = Column(Integer, ForeignKey("issues.id"))
    subscriber_id = Column(Integer, ForeignKey("subscribers.id"))
    to_email = Column(String(255))
    subject = Column(String(512))
    fmt = Column(String(16))
    status = Column(String(32))                        # sent|failed
    error = Column(Text, nullable=True)
    sent_at = Column(DateTime, default=dt.datetime.utcnow)


class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True)
    username = Column(String(64), nullable=False, unique=True, index=True)
    password_hash = Column(String(255), nullable=False)
    role = Column(String(16), default="admin")   # 'admin' | 'viewer'
    active = Column(Boolean, default=True)
    created_at = Column(DateTime, default=dt.datetime.utcnow)
    last_login = Column(DateTime, nullable=True)

    def set_password(self, raw):
        from werkzeug.security import generate_password_hash
        self.password_hash = generate_password_hash(raw)

    def check_password(self, raw):
        from werkzeug.security import check_password_hash
        return check_password_hash(self.password_hash, raw)

    @property
    def is_admin(self):
        return self.role == "admin"


def init_db():
    Base.metadata.create_all(engine)
    # Seed a default admin on first run if no users exist.
    s = SessionLocal()
    try:
        if s.query(User).count() == 0:
            import os
            u = User(username=os.environ.get("ADMIN_USER", "admin"), role="admin")
            u.set_password(os.environ.get("ADMIN_PASSWORD", "changeme"))
            s.add(u)
            s.commit()
    finally:
        s.close()