"""SQLAlchemy ORM models — matches the table spec exactly.

  product   ── 1:N ──>   system   ── 1:N ──>   support
                            │
                            └────────── referenced by ────────── ticket

A product has many systems.
A system has many support contacts (people responsible for it).
A ticket references both system and product; on create, the support email
registered for the system is used as the Azure DevOps AssignedTo.
"""
from datetime import datetime
from sqlalchemy import Column, Integer, String, Text, ForeignKey, DateTime
from sqlalchemy.orm import relationship

from database import Base


class Product(Base):
    __tablename__ = "product"

    product_code = Column(String(50), primary_key=True, index=True)
    product_name = Column(String(200), nullable=False)

    systems = relationship(
        "System", back_populates="product", cascade="all, delete-orphan"
    )


class System(Base):
    __tablename__ = "system"

    system_code = Column(String(50), primary_key=True, index=True)
    system_name = Column(String(200), nullable=False)
    product_code = Column(
        String(50), ForeignKey("product.product_code"), nullable=False, index=True
    )

    product = relationship("Product", back_populates="systems")
    supports = relationship(
        "Support", back_populates="system", cascade="all, delete-orphan"
    )


class Support(Base):
    __tablename__ = "support"

    id = Column(Integer, primary_key=True, autoincrement=True)
    support_email = Column(String(200), nullable=False, index=True)
    system_code = Column(
        String(50), ForeignKey("system.system_code"), nullable=False, index=True
    )

    system = relationship("System", back_populates="supports")


class Ticket(Base):
    __tablename__ = "ticket"

    id = Column(Integer, primary_key=True, autoincrement=True)

    # Customer info
    customer_company = Column(String(200), nullable=False)
    customer_name = Column(String(200), nullable=False)
    customer_mobilephone = Column(String(50), nullable=False)

    # Routing
    system_code = Column(
        String(50), ForeignKey("system.system_code"), nullable=False, index=True
    )
    product_code = Column(
        String(50), ForeignKey("product.product_code"), nullable=False, index=True
    )

    # The issue itself
    issue = Column(Text, nullable=False)

    # Azure DevOps linkage (populated after the API call succeeds)
    devops_work_item_id = Column(Integer, nullable=True, index=True)
    devops_url = Column(String(500), nullable=True)
    assigned_to = Column(String(200), nullable=True)

    created_at = Column(DateTime, default=datetime.utcnow, nullable=False)