from app.extensions import db


class TagCategory(db.Model):
    __tablename__ = "tag_categories"

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(120), unique=True, nullable=False)
    sort_order = db.Column(db.Integer, nullable=False, default=0)

    tags = db.relationship(
        "Tag", backref="category", cascade="all, delete-orphan",
        order_by="Tag.sort_order", lazy="selectin",
    )


class Tag(db.Model):
    __tablename__ = "tags"

    id = db.Column(db.Integer, primary_key=True)
    category_id = db.Column(db.Integer, db.ForeignKey("tag_categories.id", ondelete="CASCADE"), nullable=False)
    name = db.Column(db.String(120), nullable=False)
    sort_order = db.Column(db.Integer, nullable=False, default=0)

    __table_args__ = (db.UniqueConstraint("category_id", "name", name="uq_category_tag_name"),)


class CustomerTag(db.Model):
    __tablename__ = "customer_tags"

    id = db.Column(db.Integer, primary_key=True)
    customer_id = db.Column(db.Integer, db.ForeignKey("customers.id", ondelete="CASCADE"), nullable=False)
    tag_id = db.Column(db.Integer, db.ForeignKey("tags.id", ondelete="CASCADE"), nullable=False)
    assigned_by = db.Column(db.Integer, db.ForeignKey("users.id"))
    assigned_at = db.Column(db.DateTime, nullable=False, server_default=db.func.now())

    tag = db.relationship("Tag")
    __table_args__ = (db.UniqueConstraint("customer_id", "tag_id", name="uq_customer_tag"),)


class CallTag(db.Model):
    __tablename__ = "call_tags"

    id = db.Column(db.Integer, primary_key=True)
    call_id = db.Column(db.Integer, db.ForeignKey("calls.id", ondelete="CASCADE"), nullable=False)
    tag_id = db.Column(db.Integer, db.ForeignKey("tags.id", ondelete="CASCADE"), nullable=False)

    tag = db.relationship("Tag")
    __table_args__ = (db.UniqueConstraint("call_id", "tag_id", name="uq_call_tag"),)