# @File: backend/models/whatsapp.py
# @Desc: SQLAlchemy ORM models for the WhatsApp Business Reseller Manager
#        module. Mirrors the structure of the uptimerobot module: DoItEasy
#        owns a single upstream WhatsApp server (https://wasms.doiteasy.net)
#        and resells access to clients through plan-gated, proxied APIs.
#
#        Master API credentials live in `api_hub_credentials` under
#        provider_id="whatsapp_server" — they are never stored here.
#
# Conventions:
#   - `created_at` / `updated_at` managed by DB (server_default=now()).
#   - All tables are idempotent in the matching Alembic migration so
#     repeated bootstraps never fail.
#   - Text-encoded JSON for flexible payloads (SQLite-compatible).
from __future__ import annotations

from core.database import Base
from sqlalchemy import (
    Boolean,
    Column,
    DateTime,
    ForeignKey,
    Index,
    Integer,
    String,
    Text,
    UniqueConstraint,
)
from sqlalchemy.sql import func


# ---------------------------------------------------------------------------
# Packages / subscriptions
# ---------------------------------------------------------------------------


class WhatsappPackage(Base):
    """Admin-defined WhatsApp reseller package with all plan limits."""

    __tablename__ = "whatsapp_packages"
    __table_args__ = (
        UniqueConstraint("slug", name="uq_whatsapp_packages_slug"),
        {"extend_existing": True},
    )

    id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    slug = Column(String(64), nullable=False, index=True)
    name = Column(String(128), nullable=False)
    description = Column(Text, nullable=True)
    tagline = Column(String(255), nullable=True)

    # ---- quotas ("-1" or *_unlimited flag = unlimited) ----
    max_devices = Column(Integer, nullable=False, default=1)
    devices_unlimited = Column(Boolean, nullable=False, default=False)
    messages_per_month = Column(Integer, nullable=False, default=1000)
    messages_unlimited = Column(Boolean, nullable=False, default=False)
    max_contacts = Column(Integer, nullable=False, default=1000)
    contacts_unlimited = Column(Boolean, nullable=False, default=False)
    max_campaigns = Column(Integer, nullable=False, default=5)
    campaigns_unlimited = Column(Boolean, nullable=False, default=False)
    max_workflows = Column(Integer, nullable=False, default=5)
    workflows_unlimited = Column(Boolean, nullable=False, default=False)
    max_team_operators = Column(Integer, nullable=False, default=1)
    team_operators_unlimited = Column(Boolean, nullable=False, default=False)
    chat_retention_days = Column(Integer, nullable=False, default=90)

    # ---- feature flags ----
    auto_reply_allowed = Column(Boolean, nullable=False, default=False)
    ai_assistant_allowed = Column(Boolean, nullable=False, default=False)
    workflow_automation_allowed = Column(Boolean, nullable=False, default=False)
    webhook_access_allowed = Column(Boolean, nullable=False, default=False)
    api_access_allowed = Column(Boolean, nullable=False, default=False)
    media_sending_allowed = Column(Boolean, nullable=False, default=True)
    template_messages_allowed = Column(Boolean, nullable=False, default=False)

    # Optional mapping to a WHMCS product id so provisioning hooks can resolve
    # this package automatically on product activation.
    whmcs_product_id = Column(Integer, nullable=True, index=True)

    # archive | delete — what happens on WHMCS termination.
    terminate_action = Column(String(16), nullable=False, default="archive")

    is_active = Column(Boolean, nullable=False, default=True)
    is_featured = Column(Boolean, nullable=False, default=False)
    price_per_month = Column(String(32), nullable=True)  # display-only
    sort_order = Column(Integer, nullable=False, default=0)

    created_at = Column(DateTime(timezone=True), server_default=func.now(), nullable=False)
    updated_at = Column(
        DateTime(timezone=True),
        server_default=func.now(),
        onupdate=func.now(),
        nullable=False,
    )


class WhatsappClientSubscription(Base):
    """Ties a DoItEasy user (optionally with a WHMCS service id) to a WhatsappPackage."""

    __tablename__ = "whatsapp_client_subscriptions"
    __table_args__ = (
        Index("ix_wa_subs_user_id", "user_id"),
        Index("ix_wa_subs_status", "status"),
        {"extend_existing": True},
    )

    id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    user_id = Column(String(255), nullable=False)
    user_email = Column(String(255), nullable=True, index=True)

    package_id = Column(Integer, nullable=False, index=True)
    whmcs_service_id = Column(Integer, nullable=True, index=True)
    whmcs_product_id = Column(Integer, nullable=True)

    # active | suspended | terminated
    status = Column(String(16), nullable=False, default="active")

    # cached usage counters, refreshed on writes and by cron
    devices_used = Column(Integer, nullable=False, default=0)
    messages_used_month = Column(Integer, nullable=False, default=0)
    contacts_used = Column(Integer, nullable=False, default=0)
    campaigns_used = Column(Integer, nullable=False, default=0)
    workflows_used = Column(Integer, nullable=False, default=0)
    usage_period_start = Column(DateTime(timezone=True), nullable=True)

    notes = Column(Text, nullable=True)
    activated_at = Column(DateTime(timezone=True), nullable=True)
    suspended_at = Column(DateTime(timezone=True), nullable=True)
    terminated_at = Column(DateTime(timezone=True), nullable=True)

    created_at = Column(DateTime(timezone=True), server_default=func.now(), nullable=False)
    updated_at = Column(
        DateTime(timezone=True),
        server_default=func.now(),
        onupdate=func.now(),
        nullable=False,
    )


# ---------------------------------------------------------------------------
# Devices / sessions
# ---------------------------------------------------------------------------


class WhatsappDevice(Base):
    """A WhatsApp device slot owned by a client subscription. Mirrors upstream
    session id once paired."""

    __tablename__ = "whatsapp_devices"
    __table_args__ = (
        Index("ix_wa_devices_subscription", "subscription_id"),
        Index("ix_wa_devices_user", "user_id"),
        Index("ix_wa_devices_upstream", "upstream_session_id"),
        {"extend_existing": True},
    )

    id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    subscription_id = Column(Integer, nullable=False)
    user_id = Column(String(255), nullable=False)

    # Stable local identifier exposed to the client UI.
    device_slug = Column(String(64), nullable=False, index=True)
    display_name = Column(String(128), nullable=False, default="WhatsApp Device")
    phone_number = Column(String(40), nullable=True)
    # pending | qr_ready | qr_expired | connected | disconnected | archived
    status = Column(String(24), nullable=False, default="pending")
    # Upstream session id returned by wasms.doiteasy.net once created.
    upstream_session_id = Column(String(128), nullable=True)
    qr_payload = Column(Text, nullable=True)  # raw QR string from upstream
    qr_expires_at = Column(DateTime(timezone=True), nullable=True)
    last_seen_at = Column(DateTime(timezone=True), nullable=True)
    last_error = Column(Text, nullable=True)
    archived = Column(Boolean, nullable=False, default=False)

    created_at = Column(DateTime(timezone=True), server_default=func.now(), nullable=False)
    updated_at = Column(
        DateTime(timezone=True),
        server_default=func.now(),
        onupdate=func.now(),
        nullable=False,
    )


class WhatsappDeviceSession(Base):
    """Historical session / connection attempts per device — audit & support."""

    __tablename__ = "whatsapp_device_sessions"
    __table_args__ = (
        Index("ix_wa_device_sessions_device", "device_id"),
        {"extend_existing": True},
    )

    id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    device_id = Column(Integer, nullable=False)
    # qr_generated | qr_scanned | connected | disconnected | error
    event = Column(String(32), nullable=False)
    detail = Column(Text, nullable=True)
    occurred_at = Column(DateTime(timezone=True), server_default=func.now(), nullable=False)


# ---------------------------------------------------------------------------
# Messages / contacts
# ---------------------------------------------------------------------------


class WhatsappContact(Base):
    """A contact belonging to a client subscription."""

    __tablename__ = "whatsapp_contacts"
    __table_args__ = (
        Index("ix_wa_contacts_subscription", "subscription_id"),
        Index("ix_wa_contacts_phone", "phone_number"),
        {"extend_existing": True},
    )

    id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    subscription_id = Column(Integer, nullable=False)
    user_id = Column(String(255), nullable=False)

    phone_number = Column(String(40), nullable=False)
    display_name = Column(String(128), nullable=True)
    tags = Column(String(255), nullable=True)  # comma-separated
    notes = Column(Text, nullable=True)
    is_opted_out = Column(Boolean, nullable=False, default=False)
    last_message_at = Column(DateTime(timezone=True), nullable=True)

    created_at = Column(DateTime(timezone=True), server_default=func.now(), nullable=False)
    updated_at = Column(
        DateTime(timezone=True),
        server_default=func.now(),
        onupdate=func.now(),
        nullable=False,
    )


class WhatsappMessage(Base):
    """A single WhatsApp message (inbound or outbound)."""

    __tablename__ = "whatsapp_messages"
    __table_args__ = (
        Index("ix_wa_messages_subscription", "subscription_id"),
        Index("ix_wa_messages_device", "device_id"),
        Index("ix_wa_messages_contact", "contact_id"),
        Index("ix_wa_messages_created", "created_at"),
        {"extend_existing": True},
    )

    id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    subscription_id = Column(Integer, nullable=False)
    device_id = Column(Integer, nullable=True)
    contact_id = Column(Integer, nullable=True)
    user_id = Column(String(255), nullable=False)

    # inbound | outbound
    direction = Column(String(16), nullable=False)
    # text | image | video | audio | document | template | system
    message_type = Column(String(24), nullable=False, default="text")
    body = Column(Text, nullable=True)
    media_url = Column(Text, nullable=True)
    media_mime = Column(String(64), nullable=True)
    # queued | sent | delivered | read | failed | received
    delivery_status = Column(String(24), nullable=False, default="queued")
    upstream_message_id = Column(String(128), nullable=True, index=True)
    error_detail = Column(Text, nullable=True)
    is_read = Column(Boolean, nullable=False, default=False)

    created_at = Column(DateTime(timezone=True), server_default=func.now(), nullable=False)
    updated_at = Column(
        DateTime(timezone=True),
        server_default=func.now(),
        onupdate=func.now(),
        nullable=False,
    )


# ---------------------------------------------------------------------------
# Campaigns
# ---------------------------------------------------------------------------


class WhatsappCampaign(Base):
    __tablename__ = "whatsapp_campaigns"
    __table_args__ = (
        Index("ix_wa_campaigns_subscription", "subscription_id"),
        {"extend_existing": True},
    )

    id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    subscription_id = Column(Integer, nullable=False)
    user_id = Column(String(255), nullable=False)

    name = Column(String(128), nullable=False)
    message_body = Column(Text, nullable=False)
    media_url = Column(Text, nullable=True)
    # draft | scheduled | running | paused | completed | failed
    status = Column(String(24), nullable=False, default="draft")
    scheduled_at = Column(DateTime(timezone=True), nullable=True)
    started_at = Column(DateTime(timezone=True), nullable=True)
    completed_at = Column(DateTime(timezone=True), nullable=True)

    # counters (cached for dashboards)
    total_recipients = Column(Integer, nullable=False, default=0)
    sent_count = Column(Integer, nullable=False, default=0)
    delivered_count = Column(Integer, nullable=False, default=0)
    replied_count = Column(Integer, nullable=False, default=0)
    failed_count = Column(Integer, nullable=False, default=0)

    throttle_per_minute = Column(Integer, nullable=False, default=30)
    last_error = Column(Text, nullable=True)

    created_at = Column(DateTime(timezone=True), server_default=func.now(), nullable=False)
    updated_at = Column(
        DateTime(timezone=True),
        server_default=func.now(),
        onupdate=func.now(),
        nullable=False,
    )


class WhatsappCampaignRecipient(Base):
    __tablename__ = "whatsapp_campaign_recipients"
    __table_args__ = (
        Index("ix_wa_campaign_recipients_campaign", "campaign_id"),
        {"extend_existing": True},
    )

    id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    campaign_id = Column(Integer, nullable=False)
    contact_id = Column(Integer, nullable=True)
    phone_number = Column(String(40), nullable=False)
    # pending | sent | delivered | replied | failed | skipped
    status = Column(String(16), nullable=False, default="pending")
    error_detail = Column(Text, nullable=True)
    sent_at = Column(DateTime(timezone=True), nullable=True)


# ---------------------------------------------------------------------------
# Workflow automation
# ---------------------------------------------------------------------------


class WhatsappAutomationWorkflow(Base):
    __tablename__ = "whatsapp_automation_workflows"
    __table_args__ = (
        Index("ix_wa_workflows_subscription", "subscription_id"),
        {"extend_existing": True},
    )

    id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    subscription_id = Column(Integer, nullable=False)
    user_id = Column(String(255), nullable=False)

    name = Column(String(128), nullable=False)
    description = Column(Text, nullable=True)
    is_active = Column(Boolean, nullable=False, default=True)

    # trigger descriptor (JSON string)
    # {"type": "new_message" | "keyword" | "first_message"
    #          | "outside_hours" | "no_reply_after" | "added_to_campaign",
    #  "keyword": "...", "minutes": 30, "campaign_id": 12}
    trigger_json = Column(Text, nullable=False, default="{}")
    # ordered list of action descriptors (JSON array)
    # [{"type": "send_message", "body": "..."}, {"type": "assign_tag", ...}]
    actions_json = Column(Text, nullable=False, default="[]")

    last_run_at = Column(DateTime(timezone=True), nullable=True)
    run_count = Column(Integer, nullable=False, default=0)

    created_at = Column(DateTime(timezone=True), server_default=func.now(), nullable=False)
    updated_at = Column(
        DateTime(timezone=True),
        server_default=func.now(),
        onupdate=func.now(),
        nullable=False,
    )


class WhatsappWorkflowRun(Base):
    __tablename__ = "whatsapp_workflow_runs"
    __table_args__ = (
        Index("ix_wa_workflow_runs_workflow", "workflow_id"),
        Index("ix_wa_workflow_runs_created", "created_at"),
        {"extend_existing": True},
    )

    id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    workflow_id = Column(Integer, nullable=False)
    subscription_id = Column(Integer, nullable=False)
    trigger_detail = Column(Text, nullable=True)
    result = Column(String(16), nullable=False, default="ok")  # ok | error | skipped
    error_detail = Column(Text, nullable=True)
    executed_actions = Column(Integer, nullable=False, default=0)
    created_at = Column(DateTime(timezone=True), server_default=func.now(), nullable=False)


# ---------------------------------------------------------------------------
# AI / API tokens / webhooks / usage / logs
# ---------------------------------------------------------------------------


class WhatsappAiSettings(Base):
    __tablename__ = "whatsapp_ai_settings"
    __table_args__ = (
        UniqueConstraint("subscription_id", name="uq_wa_ai_settings_subscription"),
        {"extend_existing": True},
    )

    id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    subscription_id = Column(Integer, nullable=False)

    auto_reply_enabled = Column(Boolean, nullable=False, default=False)
    knowledge_base = Column(Text, nullable=True)
    faq_training = Column(Text, nullable=True)
    tone = Column(String(32), nullable=False, default="friendly")  # friendly | professional | concise | casual
    language = Column(String(8), nullable=False, default="en")  # en | ar | auto
    confidence_threshold = Column(Integer, nullable=False, default=60)  # 0..100
    business_hours_only = Column(Boolean, nullable=False, default=False)
    business_hours_start = Column(String(8), nullable=False, default="09:00")
    business_hours_end = Column(String(8), nullable=False, default="18:00")
    human_handoff_enabled = Column(Boolean, nullable=False, default=True)
    preview_before_sending = Column(Boolean, nullable=False, default=True)
    sentiment_detection_enabled = Column(Boolean, nullable=False, default=True)
    suggested_replies_enabled = Column(Boolean, nullable=False, default=True)
    spam_detection_enabled = Column(Boolean, nullable=False, default=True)
    lead_qualification_enabled = Column(Boolean, nullable=False, default=False)

    created_at = Column(DateTime(timezone=True), server_default=func.now(), nullable=False)
    updated_at = Column(
        DateTime(timezone=True),
        server_default=func.now(),
        onupdate=func.now(),
        nullable=False,
    )


class WhatsappApiToken(Base):
    """Per-client API token. Only the SHA-256 hash is persisted."""

    __tablename__ = "whatsapp_api_tokens"
    __table_args__ = (
        UniqueConstraint("token_hash", name="uq_wa_api_tokens_hash"),
        Index("ix_wa_api_tokens_subscription", "subscription_id"),
        {"extend_existing": True},
    )

    id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    subscription_id = Column(Integer, nullable=False)
    user_id = Column(String(255), nullable=False)

    label = Column(String(64), nullable=False, default="Default")
    token_prefix = Column(String(16), nullable=False)  # e.g. "wa_ab12" (safe to display)
    token_hash = Column(String(128), nullable=False)
    last_used_at = Column(DateTime(timezone=True), nullable=True)
    revoked_at = Column(DateTime(timezone=True), nullable=True)

    created_at = Column(DateTime(timezone=True), server_default=func.now(), nullable=False)


class WhatsappWebhook(Base):
    __tablename__ = "whatsapp_webhooks"
    __table_args__ = (
        Index("ix_wa_webhooks_subscription", "subscription_id"),
        {"extend_existing": True},
    )

    id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    subscription_id = Column(Integer, nullable=False)
    user_id = Column(String(255), nullable=False)

    target_url = Column(Text, nullable=False)
    signing_secret = Column(String(128), nullable=True)
    # comma-separated: message.received,message.sent,device.status,campaign.progress
    events = Column(String(255), nullable=False, default="message.received,message.sent")
    is_active = Column(Boolean, nullable=False, default=True)
    last_delivery_at = Column(DateTime(timezone=True), nullable=True)
    last_delivery_status = Column(String(16), nullable=True)
    last_error = Column(Text, nullable=True)

    created_at = Column(DateTime(timezone=True), server_default=func.now(), nullable=False)
    updated_at = Column(
        DateTime(timezone=True),
        server_default=func.now(),
        onupdate=func.now(),
        nullable=False,
    )


class WhatsappUsageSnapshot(Base):
    """Daily usage snapshot per subscription (for charts/alerts)."""

    __tablename__ = "whatsapp_usage_snapshots"
    __table_args__ = (
        Index("ix_wa_usage_subscription_date", "subscription_id", "snapshot_date"),
        {"extend_existing": True},
    )

    id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    subscription_id = Column(Integer, nullable=False)
    snapshot_date = Column(String(10), nullable=False)  # ISO date YYYY-MM-DD

    devices_used = Column(Integer, nullable=False, default=0)
    messages_sent = Column(Integer, nullable=False, default=0)
    messages_received = Column(Integer, nullable=False, default=0)
    campaigns_run = Column(Integer, nullable=False, default=0)
    workflow_runs = Column(Integer, nullable=False, default=0)
    api_calls = Column(Integer, nullable=False, default=0)

    created_at = Column(DateTime(timezone=True), server_default=func.now(), nullable=False)


class WhatsappApiLog(Base):
    """Log of admin-side calls to the upstream WhatsApp server and of
    public API calls made by per-client tokens. Secrets are masked."""

    __tablename__ = "whatsapp_api_logs"
    __table_args__ = (
        Index("ix_wa_api_logs_created", "created_at"),
        Index("ix_wa_api_logs_subscription", "subscription_id"),
        {"extend_existing": True},
    )

    id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    subscription_id = Column(Integer, nullable=True)
    user_id = Column(String(255), nullable=True)

    # upstream | public_api | webhook_in | webhook_out
    channel = Column(String(16), nullable=False, default="upstream")
    method = Column(String(8), nullable=False, default="GET")
    endpoint = Column(String(255), nullable=False)
    status_code = Column(Integer, nullable=True)
    duration_ms = Column(Integer, nullable=True)
    error_detail = Column(Text, nullable=True)
    request_preview = Column(Text, nullable=True)  # masked
    response_preview = Column(Text, nullable=True)  # masked/truncated

    created_at = Column(DateTime(timezone=True), server_default=func.now(), nullable=False)


class WhatsappCampaignMetric(Base):
    """Hourly bucket of campaign counters for the analytics dashboard.

    One row per (campaign_id, bucket_ts) — upserted whenever a recipient
    transitions state. Also backfilled lazily on first analytics read
    from ``whatsapp_campaign_recipients`` so historical campaigns show
    up on charts.
    """

    __tablename__ = "whatsapp_campaign_metrics"
    __table_args__ = (
        UniqueConstraint(
            "campaign_id", "bucket_ts", name="ux_wa_metrics_campaign_bucket"
        ),
        Index("ix_wa_metrics_subscription", "subscription_id"),
        Index("ix_wa_metrics_bucket", "bucket_ts"),
        {"extend_existing": True},
    )

    id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    campaign_id = Column(Integer, nullable=False)
    subscription_id = Column(Integer, nullable=False)
    user_id = Column(String(255), nullable=False)

    bucket_ts = Column(DateTime(timezone=True), nullable=False)
    sent = Column(Integer, nullable=False, default=0)
    delivered = Column(Integer, nullable=False, default=0)
    read_count = Column(Integer, nullable=False, default=0)
    replied = Column(Integer, nullable=False, default=0)
    failed = Column(Integer, nullable=False, default=0)
    opted_out = Column(Integer, nullable=False, default=0)

    created_at = Column(DateTime(timezone=True), server_default=func.now(), nullable=False)
    updated_at = Column(
        DateTime(timezone=True),
        server_default=func.now(),
        onupdate=func.now(),
        nullable=False,
    )


class WhatsappAdminAuditLog(Base):
    __tablename__ = "whatsapp_admin_audit_logs"
    __table_args__ = (
        Index("ix_wa_audit_created", "created_at"),
        {"extend_existing": True},
    )

    id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    actor_user_id = Column(String(255), nullable=True)
    actor_email = Column(String(255), nullable=True)
    action = Column(String(64), nullable=False)  # e.g. package.create, subscription.suspend
    target_type = Column(String(64), nullable=True)
    target_id = Column(String(64), nullable=True)
    detail = Column(Text, nullable=True)
    created_at = Column(DateTime(timezone=True), server_default=func.now(), nullable=False)