site_aegisone/py_service/app/models/models.py

"""
ORM-модели для service portal (SQLAlchemy 2.0).

Основные таблицы:
  - users: сотрудники (owner/engineer/technician)
  - customers: клиенты
  - objects: объекты обслуживания
  - tasks / incidents: задачи и инциденты
  - leads: лиды (обращения из бота, звонков, сайта, email)
  - call_records: звонки (CDR из Yeastar S20)
  - client_notifications: уведомления
  - pbx_settings: настройки АТС
  - blog/cases/ideas: контент
"""
from datetime import datetime
from sqlalchemy import (
    Column, Integer, String, Boolean, Text, DateTime, Numeric, Date, BigInteger, SmallInteger, JSON, ForeignKey, CheckConstraint, func
)
from sqlalchemy.orm import relationship, declarative_base

Base = declarative_base()


class User(Base):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True, autoincrement=True)
    login = Column(String(50), unique=True, nullable=False)
    password_hash = Column(String(255), nullable=False)
    role = Column(String(20), nullable=False, default="technician")
    full_name = Column(String(255), nullable=False)
    phone = Column(String(50), default="")
    email = Column(String(255), default="")
    max_user_id = Column(BigInteger, nullable=True)
    extension = Column(String(10), default="")  # Внутренний номер АТС (1001, 1002...)
    is_active = Column(Boolean, nullable=False, default=True)
    created_at = Column(DateTime, nullable=False, server_default="NOW()")
    updated_at = Column(DateTime, nullable=False, server_default="NOW()", onupdate=func.now())

    __table_args__ = (
        CheckConstraint("role IN ('owner','engineer','technician')", name="ck_users_role"),
    )

    objects_created = relationship("Object", foreign_keys="Object.created_by", back_populates="creator")
    tasks_created = relationship("Task", foreign_keys="Task.created_by", back_populates="creator")
    tasks_assigned = relationship("Task", foreign_keys="Task.assigned_to", back_populates="assignee")


class LoginAttempt(Base):
    __tablename__ = "login_attempts"

    id = Column(Integer, primary_key=True, autoincrement=True)
    ip_address = Column(String(45), nullable=False)
    login = Column(String(50), nullable=False, default="")
    attempt_time = Column(DateTime, nullable=False, server_default="NOW()")


class AuditLog(Base):
    """Лог действий пользователей (аудит).

    Записывает все изменения в системе: кто, что, когда изменил.
    Используется для отслеживания дедупликации и ручных правок.
    """
    __tablename__ = "audit_log"

    id = Column(BigInteger, primary_key=True, autoincrement=True)
    user_id = Column(Integer, ForeignKey("users.id", ondelete="SET NULL"))
    action = Column(String(255), nullable=False)
    entity_type = Column(String(50), default="")   # customer, lead, call_record, task
    entity_id = Column(Integer, default=0)
    field = Column(String(100), default="")        # какое поле изменено
    old_value = Column(Text, default="")           # старое значение
    new_value = Column(Text, default="")           # новое значение
    details = Column(Text)
    ip_address = Column(String(45), default="")
    created_at = Column(DateTime, nullable=False, server_default="NOW()")


class Customer(Base):
    """Клиент (контрагент).

    Хранит контактные данные, ИНН/КПП, статус.
    Статус 'draft' — автосоздан системой из лида/звонка, ожидает заполнения.
    Статус 'merged' — объединён с другим клиентом.
    """
    __tablename__ = "customers"

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(255), nullable=False)
    inn = Column(String(12), default="")
    kpp = Column(String(9), default="")
    legal_address = Column(String(500), default="")
    contact_person = Column(String(255), default="")
    contact_phone = Column(String(50), default="")
    contact_email = Column(String(255), default="")
    status = Column(String(20), nullable=False, default="active")
    notes = Column(Text)
    lead_source = Column(String(20), default="")     # Откуда пришёл клиент
    last_call_at = Column(DateTime)                   # Последний звонок
    total_calls = Column(Integer, default=0)          # Количество звонков
    total_leads = Column(Integer, default=0)          # Количество лидов
    merged_into = Column(Integer, nullable=True)      # ID клиента, с которым слили
    created_by = Column(Integer, ForeignKey("users.id", ondelete="SET NULL"))
    created_at = Column(DateTime, nullable=False, server_default="NOW()")
    updated_at = Column(DateTime, nullable=False, server_default="NOW()", onupdate=func.now())

    __table_args__ = (
        CheckConstraint(
            "status IN ('active','inactive','prospect','draft','merged')",
            name="ck_customers_status"
        ),
    )

    objects = relationship("Object", back_populates="customer")
    sla_contracts = relationship("SLAContract", back_populates="customer")


class Object(Base):
    __tablename__ = "objects"

    id = Column(Integer, primary_key=True, autoincrement=True)
    customer_id = Column(Integer, ForeignKey("customers.id", ondelete="SET NULL"))
    name = Column(String(255), nullable=False)
    address = Column(String(500), default="")
    object_type = Column(String(100), default="")
    area_sqm = Column(Numeric(10, 2), default=0)
    employees_count = Column(Integer, default=0)
    contact_person = Column(String(255), default="")
    contact_phone = Column(String(50), default="")
    status = Column(String(20), nullable=False, default="active")
    notes = Column(Text)
    risk_score = Column(Numeric(5, 2), default=0)
    complexity_index = Column(Numeric(5, 2), default=0)
    infrastructure_load = Column(Numeric(5, 2), default=0)
    service_history = Column(Numeric(5, 2), default=0)
    object_index = Column(Numeric(5, 2), default=0)
    sla_price_monthly = Column(Numeric(12, 2), default=0)
    region_factor = Column(Numeric(3, 2), default=1.00)
    created_by = Column(Integer, ForeignKey("users.id", ondelete="SET NULL"))
    created_at = Column(DateTime, nullable=False, server_default="NOW()")
    updated_at = Column(DateTime, nullable=False, server_default="NOW()", onupdate=func.now())

    __table_args__ = (
        CheckConstraint("status IN ('active','inactive','audit','prospective')", name="ck_objects_status"),
    )

    customer = relationship("Customer", back_populates="objects")
    creator = relationship("User", foreign_keys=[created_by], back_populates="objects_created")
    assignments = relationship("ObjectAssignment", back_populates="object")
    tasks = relationship("Task", back_populates="object")
    reports = relationship("Report", back_populates="object")
    incidents = relationship("Incident", back_populates="object")
    sla_contracts = relationship("SLAContract", back_populates="object")
    passports = relationship("ObjectPassport", back_populates="object")


class ObjectAssignment(Base):
    __tablename__ = "object_assignments"

    id = Column(Integer, primary_key=True, autoincrement=True)
    object_id = Column(Integer, ForeignKey("objects.id", ondelete="CASCADE"), nullable=False)
    user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), nullable=False)
    role = Column(String(20), nullable=False)
    assigned_at = Column(DateTime, nullable=False, server_default="NOW()")
    unassigned_at = Column(DateTime)

    __table_args__ = (
        CheckConstraint("role IN ('engineer','technician')", name="ck_assignments_role"),
    )

    object = relationship("Object", back_populates="assignments")
    user = relationship("User")


class Task(Base):
    """Задача (привязана к объекту).

    Owner/engineer создают и назначают на любого.
    Technician может создать только себе напоминание.
    lead_id — связь задачи с лидом (если задача создана из лида).
    """
    __tablename__ = "tasks"

    id = Column(Integer, primary_key=True, autoincrement=True)
    object_id = Column(Integer, ForeignKey("objects.id", ondelete="CASCADE"), nullable=False)
    assigned_to = Column(Integer, ForeignKey("users.id", ondelete="SET NULL"))
    created_by = Column(Integer, ForeignKey("users.id", ondelete="SET NULL"))
    lead_id = Column(Integer, ForeignKey("leads.id", ondelete="SET NULL"))  # Связь с лидом
    title = Column(String(500), nullable=False)
    description = Column(Text)
    priority = Column(String(4), nullable=False, default="P3")
    status = Column(String(20), nullable=False, default="open")
    deadline = Column(DateTime)
    sla_remaining_hours = Column(Numeric(6, 1), default=0)
    response_time_minutes = Column(Integer, default=0)
    resolution_time_minutes = Column(Integer, default=0)
    sla_compliant = Column(Boolean)
    checklist_data = Column(JSON)
    result_notes = Column(Text)
    closed_at = Column(DateTime)
    created_at = Column(DateTime, nullable=False, server_default="NOW()")
    updated_at = Column(DateTime, nullable=False, server_default="NOW()", onupdate=func.now())

    __table_args__ = (
        CheckConstraint("priority IN ('P1','P2','P3','P4')", name="ck_tasks_priority"),
        CheckConstraint("status IN ('open','in_progress','completed','cancelled')", name="ck_tasks_status"),
    )

    object = relationship("Object", back_populates="tasks")
    creator = relationship("User", foreign_keys=[created_by], back_populates="tasks_created")
    assignee = relationship("User", foreign_keys=[assigned_to], back_populates="tasks_assigned")
    comments = relationship("TaskComment", back_populates="task")
    photos = relationship("TaskPhoto", back_populates="task")


class TaskComment(Base):
    __tablename__ = "task_comments"

    id = Column(Integer, primary_key=True, autoincrement=True)
    task_id = Column(Integer, ForeignKey("tasks.id", ondelete="CASCADE"), nullable=False)
    user_id = Column(Integer, ForeignKey("users.id", ondelete="SET NULL"))
    comment = Column(Text, nullable=False)
    created_at = Column(DateTime, nullable=False, server_default="NOW()")

    task = relationship("Task", back_populates="comments")
    user = relationship("User")


class TaskPhoto(Base):
    __tablename__ = "task_photos"

    id = Column(Integer, primary_key=True, autoincrement=True)
    task_id = Column(Integer, ForeignKey("tasks.id", ondelete="SET NULL"))
    report_id = Column(Integer, ForeignKey("reports.id", ondelete="SET NULL"))
    file_path = Column(String(500), nullable=False)
    description = Column(String(500), default="")
    type = Column(String(20), default="other")
    uploaded_by = Column(Integer, ForeignKey("users.id", ondelete="SET NULL"))
    created_at = Column(DateTime, nullable=False, server_default="NOW()")

    __table_args__ = (
        CheckConstraint("type IN ('before','after','evidence','other')", name="ck_photos_type"),
    )

    task = relationship("Task", back_populates="photos")
    uploader = relationship("User")


class Report(Base):
    __tablename__ = "reports"

    id = Column(Integer, primary_key=True, autoincrement=True)
    object_id = Column(Integer, ForeignKey("objects.id", ondelete="CASCADE"), nullable=False)
    created_by = Column(Integer, ForeignKey("users.id", ondelete="SET NULL"))
    report_type = Column(String(20), nullable=False, default="service")
    title = Column(String(500), default="")
    description = Column(Text)
    findings = Column(Text)
    recommendations = Column(Text)
    work_done = Column(Text)
    cause_classification = Column(String(100))
    result_status = Column(String(20), default="ok")
    status = Column(String(20), nullable=False, default="draft")
    client_confirmed = Column(Boolean, default=False)
    created_at = Column(DateTime, nullable=False, server_default="NOW()")
    updated_at = Column(DateTime, nullable=False, server_default="NOW()", onupdate=func.now())

    __table_args__ = (
        CheckConstraint("report_type IN ('inspection','service','emergency','audit','monthly')", name="ck_reports_type"),
        CheckConstraint("result_status IN ('fixed','partial','revisit','ok')", name="ck_reports_result"),
        CheckConstraint("status IN ('draft','final','cancelled')", name="ck_reports_status"),
    )

    object = relationship("Object", back_populates="reports")
    creator = relationship("User")


class QuestionnaireSession(Base):
    __tablename__ = "questionnaire_sessions"

    id = Column(Integer, primary_key=True, autoincrement=True)
    object_id = Column(Integer, ForeignKey("objects.id", ondelete="SET NULL"))
    client_name = Column(String(255), default="")
    created_by = Column(Integer, ForeignKey("users.id", ondelete="SET NULL"))
    status = Column(String(20), nullable=False, default="draft")
    current_step = Column(SmallInteger, nullable=False, default=1)
    risk_score = Column(Numeric(5, 2), default=0)
    complexity_index = Column(Numeric(5, 2), default=0)
    infrastructure_load = Column(Numeric(5, 2), default=0)
    service_history = Column(Numeric(5, 2), default=0)
    object_index = Column(Numeric(5, 2), default=0)
    sla_price_monthly = Column(Numeric(12, 2), default=0)
    created_at = Column(DateTime, nullable=False, server_default="NOW()")
    updated_at = Column(DateTime, nullable=False, server_default="NOW()", onupdate=func.now())

    __table_args__ = (
        CheckConstraint("status IN ('draft','completed','cancelled')", name="ck_qsessions_status"),
    )

    answers = relationship("QuestionnaireAnswer", back_populates="session")
    passport = relationship("ObjectPassport", back_populates="session")


class QuestionnaireAnswer(Base):
    __tablename__ = "questionnaire_answers"

    id = Column(Integer, primary_key=True, autoincrement=True)
    session_id = Column(Integer, ForeignKey("questionnaire_sessions.id", ondelete="CASCADE"), nullable=False)
    step = Column(SmallInteger, nullable=False)
    question_key = Column(String(100), nullable=False)
    answer_value = Column(Text)
    created_at = Column(DateTime, nullable=False, server_default="NOW()")

    session = relationship("QuestionnaireSession", back_populates="answers")


class QuestionnaireItem(Base):
    __tablename__ = "questionnaire_items"

    id = Column(Integer, primary_key=True, autoincrement=True)
    step = Column(Integer, nullable=False, default=1)
    section = Column(String(100), nullable=False, default="")
    question_key = Column(String(100), unique=True, nullable=False)
    label = Column(String(255), nullable=False)
    type = Column(String(20), nullable=False, default="text")
    options = Column(JSON)
    required = Column(Boolean, nullable=False, default=False)
    sort_order = Column(Integer, nullable=False, default=0)
    is_active = Column(Boolean, nullable=False, default=True)
    help_text = Column(String(500), default="")
    created_at = Column(DateTime, nullable=False, server_default="NOW()")
    updated_at = Column(DateTime, nullable=False, server_default="NOW()", onupdate=func.now())

    __table_args__ = (
        CheckConstraint("type IN ('text','number','select','radio','checkbox','textarea')", name="ck_qitems_type"),
    )


class ObjectPassport(Base):
    __tablename__ = "object_passports"

    id = Column(Integer, primary_key=True, autoincrement=True)
    session_id = Column(Integer, ForeignKey("questionnaire_sessions.id", ondelete="SET NULL"))
    object_id = Column(Integer, ForeignKey("objects.id", ondelete="SET NULL"))
    object_index = Column(Numeric(5, 2), default=0)
    risk_score = Column(Numeric(5, 2), default=0)
    complexity_index = Column(Numeric(5, 2), default=0)
    sla_price_monthly = Column(Numeric(12, 2), default=0)
    passport_data = Column(JSON)
    created_by = Column(Integer, ForeignKey("users.id", ondelete="SET NULL"))
    created_at = Column(DateTime, nullable=False, server_default="NOW()")

    session = relationship("QuestionnaireSession", back_populates="passport")
    object = relationship("Object", back_populates="passports")
    creator = relationship("User")


class SLAContract(Base):
    __tablename__ = "sla_contracts"

    id = Column(Integer, primary_key=True, autoincrement=True)
    customer_id = Column(Integer, ForeignKey("customers.id", ondelete="SET NULL"))
    object_id = Column(Integer, ForeignKey("objects.id", ondelete="CASCADE"), nullable=False)
    contract_number = Column(String(50), default="")
    client_name = Column(String(255), default="")
    description = Column(Text)
    start_date = Column(Date, nullable=False)
    end_date = Column(Date)
    sla_price_monthly = Column(Numeric(12, 2), nullable=False, default=0)
    penalty_rate = Column(Numeric(5, 2), default=0)
    response_time_p1 = Column(Integer, default=4)
    response_time_p2 = Column(Integer, default=8)
    response_time_p3 = Column(Integer, default=24)
    response_time_hours = Column(Numeric(5, 1))
    service_level = Column(String(20), nullable=False, default="business")
    status = Column(String(20), nullable=False, default="negotiation")
    created_by = Column(Integer, ForeignKey("users.id", ondelete="SET NULL"))
    created_at = Column(DateTime, nullable=False, server_default="NOW()")
    updated_at = Column(DateTime, nullable=False, server_default="NOW()", onupdate=func.now())

    __table_args__ = (
        CheckConstraint("service_level IN ('start','business','enterprise')", name="ck_sla_level"),
        CheckConstraint("status IN ('active','expired','cancelled','negotiation')", name="ck_sla_status"),
    )

    customer = relationship("Customer", back_populates="sla_contracts")
    object = relationship("Object", back_populates="sla_contracts")
    creator = relationship("User")


class Incident(Base):
    __tablename__ = "incidents"

    id = Column(Integer, primary_key=True, autoincrement=True)
    object_id = Column(Integer, ForeignKey("objects.id", ondelete="CASCADE"), nullable=False)
    reported_by = Column(Integer, ForeignKey("users.id", ondelete="SET NULL"))
    assigned_to = Column(Integer, ForeignKey("users.id", ondelete="SET NULL"))
    title = Column(String(500), nullable=False)
    description = Column(Text)
    severity = Column(String(4), nullable=False, default="P3")
    status = Column(String(20), nullable=False, default="open")
    resolution = Column(Text)
    cause = Column(String(500), default="")
    resolution_time_minutes = Column(Integer, default=0)
    sla_breached = Column(Boolean, default=False)
    resolved_at = Column(DateTime)
    created_at = Column(DateTime, nullable=False, server_default="NOW()")
    updated_at = Column(DateTime, nullable=False, server_default="NOW()", onupdate=func.now())

    __table_args__ = (
        CheckConstraint("severity IN ('P1','P2','P3')", name="ck_incidents_severity"),
        CheckConstraint("status IN ('open','in_progress','resolved','closed')", name="ck_incidents_status"),
    )

    object = relationship("Object", back_populates="incidents")
    reporter = relationship("User", foreign_keys=[reported_by])
    assignee = relationship("User", foreign_keys=[assigned_to])


class EngineerKPI(Base):
    __tablename__ = "engineer_kpi"

    id = Column(Integer, primary_key=True, autoincrement=True)
    engineer_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), nullable=False)
    period_start = Column(Date, nullable=False)
    period_end = Column(Date, nullable=False)
    sla_compliance = Column(Numeric(5, 2), default=0)
    response_time_score = Column(Numeric(5, 2), default=0)
    resolution_time_score = Column(Numeric(5, 2), default=0)
    diagnosis_accuracy = Column(Numeric(5, 2), default=0)
    reopen_rate = Column(Numeric(5, 2), default=0)
    risk_coverage_score = Column(Numeric(5, 2), default=0)
    documentation_quality = Column(Numeric(5, 2), default=0)
    engineer_score = Column(Numeric(5, 2), default=0)
    grade = Column(String(20), default="")
    components_json = Column(JSON)
    created_at = Column(DateTime, nullable=False, server_default="NOW()")

    engineer = relationship("User")


class SHSRecord(Base):
    __tablename__ = "shs_records"

    id = Column(Integer, primary_key=True, autoincrement=True)
    recorded_by = Column(Integer, ForeignKey("users.id", ondelete="SET NULL"))
    period_start = Column(Date, nullable=False)
    period_end = Column(Date, nullable=False)
    sla_stability = Column(Numeric(5, 2), default=0)
    revenue_stability = Column(Numeric(5, 2), default=0)
    retention = Column(Numeric(5, 2), default=0)
    engineer_performance = Column(Numeric(5, 2), default=0)
    incident_stability = Column(Numeric(5, 2), default=0)
    sales_flow = Column(Numeric(5, 2), default=0)
    operational_efficiency = Column(Numeric(5, 2), default=0)
    shs_score = Column(Numeric(5, 2), default=0)
    shs_status = Column(String(20), default="")
    components_json = Column(JSON)
    created_at = Column(DateTime, nullable=False, server_default="NOW()")

    recorder = relationship("User")


class BlogPost(Base):
    __tablename__ = "blog_posts"

    id = Column(Integer, primary_key=True, autoincrement=True)
    title = Column(String(500), nullable=False)
    slug = Column(String(500), unique=True, nullable=False)
    content = Column(Text, nullable=False)
    excerpt = Column(String(1000), default="")
    category = Column(String(50), nullable=False, default="audit")
    author_id = Column(Integer, ForeignKey("users.id", ondelete="SET NULL"))
    status = Column(String(20), nullable=False, default="draft")
    published_at = Column(DateTime)
    created_at = Column(DateTime, nullable=False, server_default="NOW()")
    updated_at = Column(DateTime, nullable=False, server_default="NOW()", onupdate=func.now())

    __table_args__ = (
        CheckConstraint("category IN ('audit','sla','incident','supervision','documentation','risk','cases')", name="ck_blog_category"),
        CheckConstraint("status IN ('draft','published','archived')", name="ck_blog_status"),
    )

    author = relationship("User")


class FormulaCoefficient(Base):
    __tablename__ = "formula_coefficients"

    id = Column(Integer, primary_key=True, autoincrement=True)
    key = Column(String(100), unique=True, nullable=False)
    value = Column(Numeric(10, 4), nullable=False, default=0)
    description = Column(String(500), default="")
    formula_ref = Column(String(255), default="")
    is_active = Column(Boolean, nullable=False, default=True)
    updated_by = Column(Integer, ForeignKey("users.id", ondelete="SET NULL"))
    updated_at = Column(DateTime, nullable=False, server_default="NOW()", onupdate=func.now())

    updater = relationship("User")


class Case(Base):
    __tablename__ = "cases"

    id = Column(Integer, primary_key=True, autoincrement=True)
    title = Column(String(500), nullable=False)
    text = Column(Text, nullable=False)
    effect = Column(Text, nullable=False)
    sort_order = Column(Integer, nullable=False, default=0)
    is_active = Column(Boolean, nullable=False, default=True)
    created_at = Column(DateTime, nullable=False, server_default="NOW()")
    updated_at = Column(DateTime, nullable=False, server_default="NOW()", onupdate=func.now())


class Idea(Base):
    __tablename__ = "ideas"

    id = Column(Integer, primary_key=True, autoincrement=True)
    title = Column(String(500), nullable=False)
    description = Column(Text, default="")
    status = Column(String(20), nullable=False, default="normal")
    created_by = Column(Integer, ForeignKey("users.id", ondelete="SET NULL"))
    created_at = Column(DateTime, nullable=False, server_default="NOW()")
    updated_at = Column(DateTime, nullable=False, server_default="NOW()", onupdate=func.now())

    __table_args__ = (
        CheckConstraint("status IN ('normal','in_progress','completed')", name="ck_ideas_status"),
    )

    creator = relationship("User")


class QuickMenuItem(Base):
    __tablename__ = "quick_menu_items"

    id = Column(Integer, primary_key=True, autoincrement=True)
    role = Column(String(20), nullable=False)
    label = Column(String(100), nullable=False)
    url = Column(String(500), nullable=False)
    sort_order = Column(Integer, nullable=False, default=0)
    created_by = Column(Integer, ForeignKey("users.id", ondelete="SET NULL"))
    updated_at = Column(DateTime, nullable=False, server_default="NOW()", onupdate=func.now())

    creator = relationship("User")


class RoleMenuPermission(Base):
    __tablename__ = "role_menu_permissions"

    id = Column(Integer, primary_key=True, autoincrement=True)
    role = Column(String(20), nullable=False)
    menu_key = Column(String(50), nullable=False)
    visible = Column(Boolean, nullable=False, default=True)

    __table_args__ = (
        CheckConstraint("role IN ('engineer','technician')", name="ck_role_menu_role"),
    )


# ─── Модели для лидов, звонков, уведомлений, настроек АТС ──────────────────────


class Lead(Base):
    """Лид — обращение из бота, звонка, сайта или email.

    Источники: bot, phone_in, phone_out, site, email, manual.
    Автопривязка к клиенту: ИНН → телефон → email → создание draft.
    """
    __tablename__ = "leads"

    id = Column(Integer, primary_key=True, autoincrement=True)
    customer_id = Column(Integer, ForeignKey("customers.id", ondelete="SET NULL"))

    # Источник обращения
    source = Column(String(20), nullable=False, default="manual")
    # bot | phone_in | phone_out | site | email | manual

    # Данные клиента
    company_name = Column(String(255), default="")
    contact_name = Column(String(255), default="")
    contact_phone = Column(String(50), default="")
    contact_email = Column(String(255), default="")
    inn = Column(String(12), default="")

    # Содержание обращения
    title = Column(String(500), default="")
    service_type = Column(String(255), default="")
    description = Column(Text, default="")
    urgency = Column(String(10), default="medium")
    # critical | high | medium | low

    # Статус
    status = Column(String(30), default="new")
    # new | contact | qualification | proposal | negotiation | approved | won | lost | merged

    # Привязка
    assigned_to = Column(Integer, ForeignKey("users.id", ondelete="SET NULL"))
    bot_ticket_id = Column(BigInteger)  # ID заявки из бота

    # Воронка
    deal_amount = Column(Numeric(12, 2), default=0)
    expected_close_date = Column(Date)

    # Мета
    created_by = Column(Integer, ForeignKey("users.id", ondelete="SET NULL"))
    created_at = Column(DateTime, nullable=False, server_default="NOW()")
    updated_at = Column(DateTime, nullable=False, server_default="NOW()", onupdate=func.now())

    __table_args__ = (
        CheckConstraint(
            "status IN ('new','contact','qualification','proposal','negotiation','approved','won','lost','merged')",
            name="ck_leads_status"
        ),
        CheckConstraint(
            "urgency IN ('critical','high','medium','low')",
            name="ck_leads_urgency"
        ),
        CheckConstraint(
            "source IN ('bot','phone_in','phone_out','site','email','manual')",
            name="ck_leads_source"
        ),
    )

    customer = relationship("Customer", foreign_keys=[customer_id])
    assignee = relationship("User", foreign_keys=[assigned_to])
    creator = relationship("User", foreign_keys=[created_by])


class CallRecord(Base):
    """Звонок (CDR из Yeastar S20 через MySQL).

    Синхронизируется каждые 5 минут из таблицы cdr.
    Автопривязка к клиенту по номеру телефона.
    Автопривязка к сотруднику по внутреннему номеру (extension).
    """
    __tablename__ = "call_records"

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

    # Данные из CDR
    cdr_uniqueid = Column(String(32), unique=True)  # Уникальный ID из CDR
    call_datetime = Column(DateTime, nullable=False)
    caller_number = Column(String(80), default="")  # Номер звонящего (src)
    callee_number = Column(String(80), default="")  # Номер получателя (dst)
    trunk = Column(String(80), default="")          # Транк (Mango, Tele2)
    duration = Column(Integer, default=0)            # Общая длительность (сек)
    billable_seconds = Column(Integer, default=0)    # Биллируемая (сек)
    disposition = Column(String(30), default="")     # ANSWERED, NO ANSWER, BUSY, VOICEMAIL
    call_type = Column(String(20), default="")       # Inbound, Outbound, Internal
    queue_name = Column(String(30), default="")      # Очередь (6700, 6701, 6702)

    # Привязка к клиенту
    customer_id = Column(Integer, ForeignKey("customers.id", ondelete="SET NULL"))
    lead_id = Column(Integer, ForeignKey("leads.id", ondelete="SET NULL"))

    # Инженер/техник кто принял звонок
    answered_by = Column(Integer, ForeignKey("users.id", ondelete="SET NULL"))
    answered_by_name = Column(String(100), default="")

    # Запись
    has_recording = Column(Boolean, default=False)
    recording_path = Column(String(500), default="")     # Путь на PBX
    recording_filename = Column(String(200), default="")  # Имя WAV-файла

    # Уведомления
    notification_sent = Column(Boolean, default=False)

    created_at = Column(DateTime, nullable=False, server_default="NOW()")

    customer = relationship("Customer", foreign_keys=[customer_id])
    lead = relationship("Lead", foreign_keys=[lead_id])
    employee = relationship("User", foreign_keys=[answered_by])


class ClientNotification(Base):
    """Уведомление для сотрудника.

    Типы: пропущенный звонок, новый лид, изменение статуса лида,
    нарушение SLA, просроченная задача.
    """
    __tablename__ = "client_notifications"

    id = Column(Integer, primary_key=True, autoincrement=True)
    user_id = Column(Integer, ForeignKey("users.id", ondelete="SET NULL"))
    customer_id = Column(Integer, ForeignKey("customers.id", ondelete="SET NULL"))
    lead_id = Column(Integer, ForeignKey("leads.id", ondelete="SET NULL"))
    call_record_id = Column(Integer, ForeignKey("call_records.id", ondelete="SET NULL"))

    type = Column(String(30), nullable=False)
    # missed_call | new_lead | lead_status_change | sla_breach | task_overdue

    title = Column(String(500), nullable=False)
    message = Column(Text, default="")
    is_read = Column(Boolean, default=False)

    created_at = Column(DateTime, nullable=False, server_default="NOW()")

    user = relationship("User", foreign_keys=[user_id])
    customer = relationship("Customer", foreign_keys=[customer_id])
    lead = relationship("Lead", foreign_keys=[lead_id])
    call_record = relationship("CallRecord", foreign_keys=[call_record_id])


class PbxSettings(Base):
    """Настройки подключения к АТС (Yeastar S20).

    Всегда одна запись (id=1). Пароли шифруются через cryptography.fernet.
    """
    __tablename__ = "pbx_settings"

    id = Column(Integer, primary_key=True, default=1)

    # Подключение MySQL (CDR)
    mysql_host = Column(String(255), default="192.168.1.150")
    mysql_port = Column(Integer, default=3306)
    mysql_user = Column(String(100), default="")
    mysql_password = Column(String(255), default="")  # зашифровано
    mysql_database = Column(String(100), default="cdr")

    # AMI
    ami_host = Column(String(255), default="192.168.1.150")
    ami_port = Column(Integer, default=5038)
    ami_user = Column(String(100), default="")
    ami_password = Column(String(255), default="")  # зашифровано

    # FTP (записи WAV)
    ftp_host = Column(String(255), default="192.168.1.150")
    ftp_port = Column(Integer, default=21)
    ftp_user = Column(String(100), default="")
    ftp_password = Column(String(255), default="")  # зашифровано

    # Веб-интерфейс
    web_host = Column(String(255), default="192.168.1.150")
    web_port = Column(Integer, default=8088)
    web_user = Column(String(100), default="")
    web_password = Column(String(255), default="")  # зашифровано

    # Информация
    serial_number = Column(String(50), default="")   # Ручной ввод
    actual_serial = Column(String(50), default="")   # Из FTP

    # Статус подключения
    mysql_status = Column(String(20), default="unknown")  # ok | error | unknown
    ami_status = Column(String(20), default="unknown")
    ftp_status = Column(String(20), default="unknown")
    serial_match = Column(Boolean, default=False)

    # Синхронизация
    last_sync_at = Column(DateTime)
    sync_interval = Column(Integer, default=5)  # минут

    updated_at = Column(DateTime, nullable=False, server_default="NOW()", onupdate=func.now())