-- ════════════════════════════════════════════════════════════════
-- AUTH0 INTEGRATION — Additional Schema for Client Workspaces
-- Extends existing schema - client-facing authentication only
-- ════════════════════════════════════════════════════════════════

-- Auth provider type for client workspaces
CREATE TYPE auth_provider AS ENUM ('internal','auth0','saml','google_workspace','microsoft_entra');

-- Per-workspace Auth0 configuration
CREATE TABLE workspace_auth_config (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    workspace_id        UUID NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE UNIQUE,
    provider            auth_provider NOT NULL DEFAULT 'internal',
    -- Auth0 specific
    auth0_domain        VARCHAR(255),       -- e.g. finco.au.auth0.com
    auth0_client_id     VARCHAR(255),
    auth0_client_secret TEXT,               -- encrypted at rest
    auth0_audience      VARCHAR(255),       -- API audience identifier
    auth0_agent_role_claim VARCHAR(100) DEFAULT 'https://support.client.io/role',
    auth0_customer_id_claim VARCHAR(100) DEFAULT 'https://support.client.io/account_id',
    -- Customer portal URL
    portal_domain       VARCHAR(255),       -- e.g. support.finco.io
    portal_enabled      BOOLEAN DEFAULT TRUE,
    -- Allowed roles from Auth0 token that map to ClientAgent
    agent_role_values   TEXT[] DEFAULT '{"support_agent","agent","support-agent"}',
    -- Customer role values (everything else treated as customer)
    customer_role_values TEXT[] DEFAULT '{"customer","user","account_holder"}',
    is_active           BOOLEAN DEFAULT TRUE,
    created_at          TIMESTAMPTZ DEFAULT now(),
    updated_at          TIMESTAMPTZ DEFAULT now()
);

-- Map Auth0 sub → local customer record (prevents duplicate customer creation)
CREATE TABLE auth0_customer_map (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    workspace_id    UUID NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
    auth0_sub       VARCHAR(255) NOT NULL,  -- Auth0 user_id e.g. "auth0|abc123"
    customer_id     UUID NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
    email           VARCHAR(255),
    name            VARCHAR(200),
    last_seen       TIMESTAMPTZ DEFAULT now(),
    created_at      TIMESTAMPTZ DEFAULT now(),
    UNIQUE(workspace_id, auth0_sub)
);
CREATE INDEX idx_auth0_map_ws  ON auth0_customer_map(workspace_id, auth0_sub);
CREATE INDEX idx_auth0_map_cid ON auth0_customer_map(customer_id);

-- Map Auth0 sub → local agent/user record
CREATE TABLE auth0_agent_map (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    workspace_id    UUID NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
    auth0_sub       VARCHAR(255) NOT NULL,
    user_id         UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    email           VARCHAR(255),
    last_seen       TIMESTAMPTZ DEFAULT now(),
    created_at      TIMESTAMPTZ DEFAULT now(),
    UNIQUE(workspace_id, auth0_sub)
);

-- Refresh token store for Auth0 sessions
CREATE TABLE auth0_sessions (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    workspace_id    UUID NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
    auth0_sub       VARCHAR(255) NOT NULL,
    actor_type      VARCHAR(20) NOT NULL CHECK (actor_type IN ('customer','agent')),
    actor_id        UUID NOT NULL,          -- customer_id or user_id
    refresh_token   TEXT,
    expires_at      TIMESTAMPTZ,
    ip_address      INET,
    user_agent      TEXT,
    created_at      TIMESTAMPTZ DEFAULT now()
);
CREATE INDEX idx_auth0_sess_sub ON auth0_sessions(workspace_id, auth0_sub);

-- Customer portal ticket view (customers only see their own tickets)
-- Enforced via portal_context in API middleware
CREATE VIEW vw_customer_ticket_portal AS
SELECT
    t.id, t.ticket_number, t.subject, t.status, t.priority, t.channel,
    t.sla_breach_status, t.created_at, t.updated_at,
    t.resolved_at, t.csat_score, t.csat_comment,
    t.customer_id,
    cat.name AS category_name,
    -- Agents name shown only for non-internal replies
    COUNT(r.id) FILTER (WHERE NOT r.is_internal) AS reply_count,
    MAX(r.created_at) FILTER (WHERE NOT r.is_internal) AS last_reply_at
FROM tickets t
LEFT JOIN categories cat ON cat.id = t.category_id
LEFT JOIN ticket_replies r ON r.ticket_id = t.id
-- customer_id filtered in application layer via JWT claim
GROUP BY t.id, cat.name;

-- ════════════════════════════════════════════════════════════════════
-- ARTHA FINTECH — Unified Support System — PostgreSQL 16 Schema v1.0
-- ════════════════════════════════════════════════════════════════════

-- ENUMS
CREATE TYPE workspace_type    AS ENUM ('artha','client');
CREATE TYPE workspace_tier    AS ENUM ('tier1','tier2','tier3');
CREATE TYPE user_role         AS ENUM ('artha_admin','artha_supervisor','artha_agent_l2','artha_agent_l1','client_admin','client_agent','client_viewer');
CREATE TYPE ticket_status     AS ENUM ('open','pending','on_hold','resolved','closed');
CREATE TYPE ticket_priority   AS ENUM ('critical','high','medium','low');
CREATE TYPE ticket_channel    AS ENUM ('app','email','chat','whatsapp','phone','manual','api');
CREATE TYPE sla_breach_status AS ENUM ('within','at_risk','breached');
CREATE TYPE article_status    AS ENUM ('draft','review','published','archived');
CREATE TYPE escalation_reason AS ENUM ('sla_breach','customer_request','technical_complexity','legal_compliance','senior_approval','repeat_issue','other');
CREATE TYPE audit_action      AS ENUM ('created','status_changed','priority_changed','assigned','reassigned','escalated','de_escalated','merged','split','linked','replied','note_added','closed','reopened','sla_breach','workspace_switched','login','logout');
CREATE TYPE notification_type AS ENUM ('sla_at_risk','sla_breach','ticket_assigned','ticket_escalated','client_escalation','mention','reply','csat','system');

-- ───────────────────────────────
-- WORKSPACES
-- ───────────────────────────────
CREATE TABLE workspaces (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name                VARCHAR(150) NOT NULL,
    slug                VARCHAR(60)  NOT NULL UNIQUE,
    type                workspace_type NOT NULL DEFAULT 'client',
    tier                workspace_tier,
    logo_url            TEXT,
    primary_color       VARCHAR(7) DEFAULT '#1a4480',
    sla_policy_id       UUID,
    auto_close_days     INT DEFAULT 7,
    mfa_required        BOOLEAN DEFAULT FALSE,
    session_timeout_min INT DEFAULT 30,
    dashboard_refresh_s INT DEFAULT 60,
    is_active           BOOLEAN DEFAULT TRUE,
    created_at          TIMESTAMPTZ DEFAULT now(),
    updated_at          TIMESTAMPTZ DEFAULT now(),
    CONSTRAINT slug_fmt CHECK (slug ~ '^[a-z0-9_-]+$')
);

-- ───────────────────────────────
-- SLA POLICIES
-- ───────────────────────────────
CREATE TABLE sla_policies (
    id                      UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    workspace_id            UUID NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
    name                    VARCHAR(150) NOT NULL,
    priority                ticket_priority NOT NULL,
    first_response_minutes  INT NOT NULL,
    resolution_minutes      INT NOT NULL,
    business_hours_only     BOOLEAN DEFAULT FALSE,
    business_start_hour     INT DEFAULT 9,
    business_end_hour       INT DEFAULT 18,
    business_days           INT[] DEFAULT '{1,2,3,4,5,6}',
    at_risk_pct             INT DEFAULT 80,
    is_active               BOOLEAN DEFAULT TRUE,
    created_at              TIMESTAMPTZ DEFAULT now(),
    UNIQUE(workspace_id, name, priority)
);
ALTER TABLE workspaces ADD CONSTRAINT fk_ws_sla
    FOREIGN KEY (sla_policy_id) REFERENCES sla_policies(id) ON DELETE SET NULL;

-- ───────────────────────────────
-- USERS
-- ───────────────────────────────
CREATE TABLE users (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    workspace_id    UUID NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
    email           VARCHAR(255) NOT NULL UNIQUE,
    name            VARCHAR(150) NOT NULL,
    avatar_url      TEXT,
    role            user_role NOT NULL,
    password_hash   TEXT,
    mfa_enabled     BOOLEAN DEFAULT FALSE,
    mfa_secret      TEXT,
    is_active       BOOLEAN DEFAULT TRUE,
    last_login_at   TIMESTAMPTZ,
    created_at      TIMESTAMPTZ DEFAULT now(),
    updated_at      TIMESTAMPTZ DEFAULT now()
);
CREATE INDEX idx_users_workspace ON users(workspace_id);
CREATE INDEX idx_users_role      ON users(workspace_id, role);

-- Cross-workspace access grants
CREATE TABLE user_workspace_access (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id         UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    workspace_id    UUID NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
    can_reply       BOOLEAN DEFAULT FALSE,
    granted_by      UUID REFERENCES users(id),
    granted_at      TIMESTAMPTZ DEFAULT now(),
    UNIQUE(user_id, workspace_id)
);

-- ───────────────────────────────
-- CUSTOMERS
-- ───────────────────────────────
CREATE TABLE customers (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    workspace_id        UUID NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
    external_account_id VARCHAR(100),
    name                VARCHAR(200) NOT NULL,
    email               VARCHAR(255),
    phone               VARCHAR(20),
    metadata            JSONB DEFAULT '{}',
    total_tickets       INT DEFAULT 0,
    csat_avg            NUMERIC(3,2),
    created_at          TIMESTAMPTZ DEFAULT now(),
    updated_at          TIMESTAMPTZ DEFAULT now()
);
CREATE INDEX idx_customers_workspace ON customers(workspace_id);
CREATE INDEX idx_customers_email     ON customers(email);
CREATE INDEX idx_customers_ext_id    ON customers(workspace_id, external_account_id);

-- ───────────────────────────────
-- CATEGORIES
-- ───────────────────────────────
CREATE TABLE categories (
    id                    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    workspace_id          UUID NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
    parent_id             UUID REFERENCES categories(id),
    name                  VARCHAR(100) NOT NULL,
    default_priority      ticket_priority DEFAULT 'medium',
    default_sla_policy_id UUID REFERENCES sla_policies(id),
    sort_order            INT DEFAULT 0,
    is_active             BOOLEAN DEFAULT TRUE,
    UNIQUE(workspace_id, name, parent_id)
);

-- ───────────────────────────────
-- TICKETS
-- ───────────────────────────────
CREATE TABLE tickets (
    id                    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    workspace_id          UUID NOT NULL REFERENCES workspaces(id) ON DELETE RESTRICT,
    ticket_number         VARCHAR(20) NOT NULL UNIQUE,
    subject               VARCHAR(500) NOT NULL,
    description           TEXT,
    status                ticket_status NOT NULL DEFAULT 'open',
    priority              ticket_priority NOT NULL DEFAULT 'medium',
    channel               ticket_channel NOT NULL,
    category_id           UUID REFERENCES categories(id),
    sub_category_id       UUID REFERENCES categories(id),
    customer_id           UUID REFERENCES customers(id),
    assignee_id           UUID REFERENCES users(id),
    created_by            UUID REFERENCES users(id),
    -- SLA
    sla_policy_id         UUID REFERENCES sla_policies(id),
    sla_breach_status     sla_breach_status DEFAULT 'within',
    first_response_due_at TIMESTAMPTZ,
    resolution_due_at     TIMESTAMPTZ,
    first_responded_at    TIMESTAMPTZ,
    resolved_at           TIMESTAMPTZ,
    closed_at             TIMESTAMPTZ,
    sla_paused_at         TIMESTAMPTZ,
    sla_paused_minutes    INT DEFAULT 0,
    -- Cross-workspace escalation
    escalated_to_artha    BOOLEAN DEFAULT FALSE,
    escalated_at          TIMESTAMPTZ,
    escalated_by          UUID REFERENCES users(id),
    escalation_reason     escalation_reason,
    escalation_note       TEXT,
    artha_assignee_id     UUID REFERENCES users(id),
    artha_resolved_at     TIMESTAMPTZ,
    -- Merge/split
    merged_into_id        UUID REFERENCES tickets(id),
    parent_ticket_id      UUID REFERENCES tickets(id),
    -- CSAT
    csat_sent_at          TIMESTAMPTZ,
    csat_score            INT CHECK (csat_score BETWEEN 1 AND 5),
    csat_comment          TEXT,
    -- Misc
    tags                  TEXT[] DEFAULT '{}',
    metadata              JSONB DEFAULT '{}',
    created_at            TIMESTAMPTZ DEFAULT now(),
    updated_at            TIMESTAMPTZ DEFAULT now()
);
CREATE INDEX idx_tickets_workspace   ON tickets(workspace_id);
CREATE INDEX idx_tickets_status      ON tickets(workspace_id, status);
CREATE INDEX idx_tickets_priority    ON tickets(workspace_id, priority);
CREATE INDEX idx_tickets_assignee    ON tickets(assignee_id);
CREATE INDEX idx_tickets_customer    ON tickets(customer_id);
CREATE INDEX idx_tickets_escalated   ON tickets(escalated_to_artha) WHERE escalated_to_artha = TRUE;
CREATE INDEX idx_tickets_sla         ON tickets(workspace_id, sla_breach_status);
CREATE INDEX idx_tickets_created     ON tickets(workspace_id, created_at DESC);
CREATE INDEX idx_tickets_tags        ON tickets USING GIN(tags);
CREATE INDEX idx_tickets_fts         ON tickets USING GIN(
    to_tsvector('english', subject || ' ' || COALESCE(description,''))
);

-- ───────────────────────────────
-- TICKET REPLIES
-- ───────────────────────────────
CREATE TABLE ticket_replies (
    id               UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    ticket_id        UUID NOT NULL REFERENCES tickets(id) ON DELETE CASCADE,
    author_id        UUID NOT NULL REFERENCES users(id),
    body             TEXT NOT NULL,
    body_html        TEXT,
    is_internal      BOOLEAN DEFAULT FALSE,
    email_message_id VARCHAR(500),
    created_at       TIMESTAMPTZ DEFAULT now(),
    updated_at       TIMESTAMPTZ DEFAULT now()
);
CREATE INDEX idx_replies_ticket ON ticket_replies(ticket_id, created_at);

-- ───────────────────────────────
-- ATTACHMENTS
-- ───────────────────────────────
CREATE TABLE attachments (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    ticket_id       UUID REFERENCES tickets(id) ON DELETE CASCADE,
    reply_id        UUID REFERENCES ticket_replies(id) ON DELETE CASCADE,
    file_name       VARCHAR(255) NOT NULL,
    file_size_bytes BIGINT,
    mime_type       VARCHAR(100),
    storage_key     TEXT NOT NULL,
    uploaded_by     UUID REFERENCES users(id),
    created_at      TIMESTAMPTZ DEFAULT now(),
    CHECK (ticket_id IS NOT NULL OR reply_id IS NOT NULL)
);

-- ───────────────────────────────
-- AUDIT LOGS (partitioned by year)
-- ───────────────────────────────
CREATE TABLE audit_logs (
    id           UUID DEFAULT gen_random_uuid(),
    workspace_id UUID NOT NULL REFERENCES workspaces(id),
    ticket_id    UUID REFERENCES tickets(id) ON DELETE SET NULL,
    actor_id     UUID REFERENCES users(id) ON DELETE SET NULL,
    action       audit_action NOT NULL,
    payload      JSONB DEFAULT '{}',
    ip_address   INET,
    user_agent   TEXT,
    created_at   TIMESTAMPTZ DEFAULT now()
) PARTITION BY RANGE (created_at);

CREATE TABLE audit_logs_2026 PARTITION OF audit_logs FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');
CREATE TABLE audit_logs_2027 PARTITION OF audit_logs FOR VALUES FROM ('2027-01-01') TO ('2028-01-01');
CREATE TABLE audit_logs_2028 PARTITION OF audit_logs FOR VALUES FROM ('2028-01-01') TO ('2029-01-01');

CREATE INDEX idx_audit_ws     ON audit_logs(workspace_id, created_at DESC);
CREATE INDEX idx_audit_ticket ON audit_logs(ticket_id, created_at DESC);
CREATE INDEX idx_audit_actor  ON audit_logs(actor_id, created_at DESC);

-- ───────────────────────────────
-- NOTIFICATIONS
-- ───────────────────────────────
CREATE TABLE notifications (
    id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id    UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    type       notification_type NOT NULL,
    ticket_id  UUID REFERENCES tickets(id) ON DELETE SET NULL,
    payload    JSONB DEFAULT '{}',
    is_read    BOOLEAN DEFAULT FALSE,
    read_at    TIMESTAMPTZ,
    created_at TIMESTAMPTZ DEFAULT now()
);
CREATE INDEX idx_notif_user   ON notifications(user_id, is_read, created_at DESC);
CREATE INDEX idx_notif_ticket ON notifications(ticket_id);

-- ───────────────────────────────
-- KNOWLEDGE BASE
-- ───────────────────────────────
CREATE TABLE kb_categories (
    id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    workspace_id UUID NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
    parent_id    UUID REFERENCES kb_categories(id),
    name         VARCHAR(150) NOT NULL,
    sort_order   INT DEFAULT 0
);

CREATE TABLE kb_articles (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    workspace_id    UUID NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
    kb_category_id  UUID REFERENCES kb_categories(id),
    title           VARCHAR(500) NOT NULL,
    body            TEXT NOT NULL,
    body_html       TEXT,
    status          article_status DEFAULT 'draft',
    author_id       UUID REFERENCES users(id),
    views           INT DEFAULT 0,
    deflections     INT DEFAULT 0,
    thumbs_up       INT DEFAULT 0,
    thumbs_down     INT DEFAULT 0,
    tags            TEXT[] DEFAULT '{}',
    created_at      TIMESTAMPTZ DEFAULT now(),
    updated_at      TIMESTAMPTZ DEFAULT now()
);
CREATE INDEX idx_kb_ws   ON kb_articles(workspace_id, status);
CREATE INDEX idx_kb_tags ON kb_articles USING GIN(tags);
CREATE INDEX idx_kb_fts  ON kb_articles USING GIN(to_tsvector('english', title || ' ' || body));

CREATE TABLE kb_article_shares (
    id             UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    article_id     UUID NOT NULL REFERENCES kb_articles(id) ON DELETE CASCADE,
    target_ws_id   UUID NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
    shared_by      UUID REFERENCES users(id),
    shared_at      TIMESTAMPTZ DEFAULT now(),
    UNIQUE(article_id, target_ws_id)
);

-- ───────────────────────────────
-- CANNED RESPONSES
-- ───────────────────────────────
CREATE TABLE canned_responses (
    id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    workspace_id UUID NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
    author_id    UUID REFERENCES users(id),
    title        VARCHAR(200) NOT NULL,
    shortcode    VARCHAR(50),
    body         TEXT NOT NULL,
    is_shared    BOOLEAN DEFAULT FALSE,
    created_at   TIMESTAMPTZ DEFAULT now()
);

-- ───────────────────────────────
-- WEBHOOKS
-- ───────────────────────────────
CREATE TABLE webhooks (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    workspace_id    UUID NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
    url             TEXT NOT NULL,
    secret          TEXT,
    events          TEXT[] DEFAULT '{}',
    is_active       BOOLEAN DEFAULT TRUE,
    last_triggered  TIMESTAMPTZ,
    created_at      TIMESTAMPTZ DEFAULT now()
);

-- ───────────────────────────────
-- TICKET LINKS
-- ───────────────────────────────
CREATE TABLE ticket_links (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    ticket_id   UUID NOT NULL REFERENCES tickets(id) ON DELETE CASCADE,
    linked_id   UUID NOT NULL REFERENCES tickets(id) ON DELETE CASCADE,
    link_type   VARCHAR(50) DEFAULT 'related',
    created_by  UUID REFERENCES users(id),
    created_at  TIMESTAMPTZ DEFAULT now(),
    UNIQUE(ticket_id, linked_id)
);

-- ───────────────────────────────
-- HELPER VIEWS
-- ───────────────────────────────
CREATE VIEW vw_ticket_summary AS
SELECT
    t.id, t.ticket_number, t.subject, t.status, t.priority, t.channel,
    t.sla_breach_status, t.escalated_to_artha, t.escalated_at,
    t.first_response_due_at, t.resolution_due_at,
    t.created_at, t.updated_at,
    w.name AS workspace_name, w.slug AS workspace_slug, w.type AS workspace_type,
    c.name AS customer_name, c.email AS customer_email,
    u.name AS assignee_name, u.role AS assignee_role,
    cat.name AS category_name,
    au.name AS artha_assignee_name,
    EXTRACT(EPOCH FROM (now() - t.created_at))/3600 AS age_hours,
    CASE WHEN t.first_response_due_at < now() AND t.first_responded_at IS NULL
         THEN TRUE ELSE FALSE END AS frt_breached,
    CASE WHEN t.resolution_due_at < now() AND t.resolved_at IS NULL
         THEN TRUE ELSE FALSE END AS resolution_breached
FROM tickets t
JOIN workspaces w ON w.id = t.workspace_id
LEFT JOIN customers c ON c.id = t.customer_id
LEFT JOIN users u ON u.id = t.assignee_id
LEFT JOIN users au ON au.id = t.artha_assignee_id
LEFT JOIN categories cat ON cat.id = t.category_id;

CREATE VIEW vw_client_health AS
SELECT
    w.id AS workspace_id, w.name, w.slug, w.tier,
    COUNT(t.id) FILTER (WHERE t.status IN ('open','pending','on_hold')) AS open_tickets,
    COUNT(t.id) FILTER (WHERE t.escalated_to_artha AND t.status NOT IN ('resolved','closed')) AS escalated_to_artha,
    COUNT(t.id) FILTER (WHERE t.sla_breach_status = 'breached') AS sla_breached,
    ROUND(100.0 * COUNT(t.id) FILTER (WHERE t.sla_breach_status != 'breached') /
          NULLIF(COUNT(t.id) FILTER (WHERE t.status NOT IN ('resolved','closed')), 0), 1) AS sla_compliance_pct,
    ROUND(AVG(t.csat_score) FILTER (WHERE t.csat_score IS NOT NULL), 2) AS avg_csat,
    COUNT(t.id) FILTER (WHERE t.created_at > now() - INTERVAL '30 days') AS volume_30d,
    MAX(t.updated_at) AS last_activity
FROM workspaces w
LEFT JOIN tickets t ON t.workspace_id = w.id
WHERE w.type = 'client' AND w.is_active = TRUE
GROUP BY w.id, w.name, w.slug, w.tier;

-- ───────────────────────────────
-- ROW-LEVEL SECURITY
-- ───────────────────────────────
ALTER TABLE tickets ENABLE ROW LEVEL SECURITY;
CREATE POLICY ticket_workspace_isolation ON tickets
    USING (workspace_id = current_setting('app.workspace_id')::UUID);

ALTER TABLE customers ENABLE ROW LEVEL SECURITY;
CREATE POLICY customer_workspace_isolation ON customers
    USING (workspace_id = current_setting('app.workspace_id')::UUID);

-- Seed: Artha workspace
INSERT INTO workspaces (name, slug, type) VALUES ('Artha Fintech', 'artha', 'artha');

// ArthaSupport.Core/Entities/ — Base + All Domain Entities

public abstract class BaseEntity
{
    public Guid Id { get; set; } = Guid.NewGuid();
    public DateTime CreatedAt { get; set; } = DateTime.UtcNow;
    public DateTime UpdatedAt { get; set; } = DateTime.UtcNow;
}

// ── Workspace ────────────────────────────────────────────
public class Workspace : BaseEntity
{
    public string Name { get; set; } = string.Empty;
    public string Slug { get; set; } = string.Empty;
    public WorkspaceType Type { get; set; }
    public WorkspaceTier? Tier { get; set; }
    public string? LogoUrl { get; set; }
    public string PrimaryColor { get; set; } = "#1a4480";
    public Guid? SLAPolicyId { get; set; }
    public int AutoCloseDays { get; set; } = 7;
    public bool MfaRequired { get; set; }
    public int SessionTimeoutMinutes { get; set; } = 30;
    public int DashboardRefreshSeconds { get; set; } = 60;
    public bool IsActive { get; set; } = true;
    // Navigation
    public SLAPolicy? DefaultSLAPolicy { get; set; }
    public ICollection<User> Users { get; set; } = new List<User>();
    public ICollection<Ticket> Tickets { get; set; } = new List<Ticket>();
    public ICollection<SLAPolicy> SLAPolicies { get; set; } = new List<SLAPolicy>();
    public ICollection<Category> Categories { get; set; } = new List<Category>();
    public ICollection<KBArticle> KBArticles { get; set; } = new List<KBArticle>();
}

// ── User ─────────────────────────────────────────────────
public class User : BaseEntity
{
    public Guid WorkspaceId { get; set; }
    public string Email { get; set; } = string.Empty;
    public string Name { get; set; } = string.Empty;
    public string? AvatarUrl { get; set; }
    public UserRole Role { get; set; }
    public string? PasswordHash { get; set; }
    public bool MfaEnabled { get; set; }
    public string? MfaSecret { get; set; }
    public bool IsActive { get; set; } = true;
    public DateTime? LastLoginAt { get; set; }
    // Navigation
    public Workspace Workspace { get; set; } = null!;
    public ICollection<UserWorkspaceAccess> AdditionalAccess { get; set; } = new List<UserWorkspaceAccess>();
    public ICollection<Ticket> AssignedTickets { get; set; } = new List<Ticket>();
}

// ── UserWorkspaceAccess ───────────────────────────────────
public class UserWorkspaceAccess : BaseEntity
{
    public Guid UserId { get; set; }
    public Guid WorkspaceId { get; set; }
    public bool CanReply { get; set; }
    public Guid? GrantedById { get; set; }
    public DateTime GrantedAt { get; set; } = DateTime.UtcNow;
    public User User { get; set; } = null!;
    public Workspace Workspace { get; set; } = null!;
}

// ── Customer ─────────────────────────────────────────────
public class Customer : BaseEntity
{
    public Guid WorkspaceId { get; set; }
    public string? ExternalAccountId { get; set; }
    public string Name { get; set; } = string.Empty;
    public string? Email { get; set; }
    public string? Phone { get; set; }
    public Dictionary<string, object> Metadata { get; set; } = new();
    public int TotalTickets { get; set; }
    public decimal? CsatAvg { get; set; }
    public Workspace Workspace { get; set; } = null!;
    public ICollection<Ticket> Tickets { get; set; } = new List<Ticket>();
}

// ── Category ─────────────────────────────────────────────
public class Category : BaseEntity
{
    public Guid WorkspaceId { get; set; }
    public Guid? ParentId { get; set; }
    public string Name { get; set; } = string.Empty;
    public TicketPriority DefaultPriority { get; set; } = TicketPriority.Medium;
    public Guid? DefaultSLAPolicyId { get; set; }
    public int SortOrder { get; set; }
    public bool IsActive { get; set; } = true;
    public Workspace Workspace { get; set; } = null!;
    public Category? Parent { get; set; }
    public ICollection<Category> SubCategories { get; set; } = new List<Category>();
}

// ── SLAPolicy ────────────────────────────────────────────
public class SLAPolicy : BaseEntity
{
    public Guid WorkspaceId { get; set; }
    public string Name { get; set; } = string.Empty;
    public TicketPriority Priority { get; set; }
    public int FirstResponseMinutes { get; set; }
    public int ResolutionMinutes { get; set; }
    public bool BusinessHoursOnly { get; set; }
    public int BusinessStartHour { get; set; } = 9;
    public int BusinessEndHour { get; set; } = 18;
    public int[] BusinessDays { get; set; } = { 1, 2, 3, 4, 5, 6 };
    public int AtRiskPercentage { get; set; } = 80;
    public bool IsActive { get; set; } = true;
    public Workspace Workspace { get; set; } = null!;
}

// ── Ticket ───────────────────────────────────────────────
public class Ticket : BaseEntity
{
    public Guid WorkspaceId { get; set; }
    public string TicketNumber { get; set; } = string.Empty;
    public string Subject { get; set; } = string.Empty;
    public string? Description { get; set; }
    public TicketStatus Status { get; set; } = TicketStatus.Open;
    public TicketPriority Priority { get; set; } = TicketPriority.Medium;
    public TicketChannel Channel { get; set; }
    public Guid? CategoryId { get; set; }
    public Guid? SubCategoryId { get; set; }
    public Guid? CustomerId { get; set; }
    public Guid? AssigneeId { get; set; }
    public Guid? CreatedById { get; set; }
    // SLA
    public Guid? SLAPolicyId { get; set; }
    public SLABreachStatus SLABreachStatus { get; set; } = SLABreachStatus.Within;
    public DateTime? FirstResponseDueAt { get; set; }
    public DateTime? ResolutionDueAt { get; set; }
    public DateTime? FirstRespondedAt { get; set; }
    public DateTime? ResolvedAt { get; set; }
    public DateTime? ClosedAt { get; set; }
    public DateTime? SLAPausedAt { get; set; }
    public int SLAPausedMinutes { get; set; }
    // Escalation (cross-workspace)
    public bool EscalatedToArtha { get; set; }
    public DateTime? EscalatedAt { get; set; }
    public Guid? EscalatedById { get; set; }
    public EscalationReason? EscalationReason { get; set; }
    public string? EscalationNote { get; set; }
    public Guid? ArthaAssigneeId { get; set; }
    public DateTime? ArthaResolvedAt { get; set; }
    // Merge/Split
    public Guid? MergedIntoId { get; set; }
    public Guid? ParentTicketId { get; set; }
    // CSAT
    public DateTime? CsatSentAt { get; set; }
    public int? CsatScore { get; set; }
    public string? CsatComment { get; set; }
    public string[] Tags { get; set; } = Array.Empty<string>();
    public Dictionary<string, object> Metadata { get; set; } = new();
    // Navigation
    public Workspace Workspace { get; set; } = null!;
    public Customer? Customer { get; set; }
    public User? Assignee { get; set; }
    public User? CreatedBy { get; set; }
    public User? ArthaAssignee { get; set; }
    public Category? Category { get; set; }
    public Category? SubCategory { get; set; }
    public SLAPolicy? SLAPolicy { get; set; }
    public Ticket? MergedInto { get; set; }
    public ICollection<TicketReply> Replies { get; set; } = new List<TicketReply>();
    public ICollection<Attachment> Attachments { get; set; } = new List<Attachment>();
    public ICollection<AuditLog> AuditLogs { get; set; } = new List<AuditLog>();
    public ICollection<Notification> Notifications { get; set; } = new List<Notification>();
}

// ── TicketReply ──────────────────────────────────────────
public class TicketReply : BaseEntity
{
    public Guid TicketId { get; set; }
    public Guid AuthorId { get; set; }
    public string Body { get; set; } = string.Empty;
    public string? BodyHtml { get; set; }
    public bool IsInternal { get; set; }
    public string? EmailMessageId { get; set; }
    public Ticket Ticket { get; set; } = null!;
    public User Author { get; set; } = null!;
    public ICollection<Attachment> Attachments { get; set; } = new List<Attachment>();
}

// ── Attachment ───────────────────────────────────────────
public class Attachment : BaseEntity
{
    public Guid? TicketId { get; set; }
    public Guid? ReplyId { get; set; }
    public string FileName { get; set; } = string.Empty;
    public long? FileSizeBytes { get; set; }
    public string? MimeType { get; set; }
    public string StorageKey { get; set; } = string.Empty;
    public Guid? UploadedById { get; set; }
    public Ticket? Ticket { get; set; }
    public TicketReply? Reply { get; set; }
}

// ── AuditLog ─────────────────────────────────────────────
public class AuditLog : BaseEntity
{
    public Guid WorkspaceId { get; set; }
    public Guid? TicketId { get; set; }
    public Guid? ActorId { get; set; }
    public AuditAction Action { get; set; }
    public Dictionary<string, object> Payload { get; set; } = new();
    public string? IpAddress { get; set; }
    public string? UserAgent { get; set; }
    public Workspace Workspace { get; set; } = null!;
    public Ticket? Ticket { get; set; }
    public User? Actor { get; set; }
}

// ── Notification ─────────────────────────────────────────
public class Notification : BaseEntity
{
    public Guid UserId { get; set; }
    public NotificationType Type { get; set; }
    public Guid? TicketId { get; set; }
    public Dictionary<string, object> Payload { get; set; } = new();
    public bool IsRead { get; set; }
    public DateTime? ReadAt { get; set; }
    public User User { get; set; } = null!;
    public Ticket? Ticket { get; set; }
}

// ── KBArticle ────────────────────────────────────────────
public class KBArticle : BaseEntity
{
    public Guid WorkspaceId { get; set; }
    public Guid? KBCategoryId { get; set; }
    public string Title { get; set; } = string.Empty;
    public string Body { get; set; } = string.Empty;
    public string? BodyHtml { get; set; }
    public ArticleStatus Status { get; set; } = ArticleStatus.Draft;
    public Guid? AuthorId { get; set; }
    public int Views { get; set; }
    public int Deflections { get; set; }
    public int ThumbsUp { get; set; }
    public int ThumbsDown { get; set; }
    public string[] Tags { get; set; } = Array.Empty<string>();
    public Workspace Workspace { get; set; } = null!;
    public User? Author { get; set; }
    public ICollection<KBArticleShare> Shares { get; set; } = new List<KBArticleShare>();
}

public class KBArticleShare : BaseEntity
{
    public Guid ArticleId { get; set; }
    public Guid TargetWorkspaceId { get; set; }
    public Guid? SharedById { get; set; }
    public DateTime SharedAt { get; set; } = DateTime.UtcNow;
    public KBArticle Article { get; set; } = null!;
    public Workspace TargetWorkspace { get; set; } = null!;
}

public class Webhook : BaseEntity
{
    public Guid WorkspaceId { get; set; }
    public string Url { get; set; } = string.Empty;
    public string? Secret { get; set; }
    public string[] Events { get; set; } = Array.Empty<string>();
    public bool IsActive { get; set; } = true;
    public DateTime? LastTriggered { get; set; }
    public Workspace Workspace { get; set; } = null!;
}

public class CannedResponse : BaseEntity
{
    public Guid WorkspaceId { get; set; }
    public Guid? AuthorId { get; set; }
    public string Title { get; set; } = string.Empty;
    public string? Shortcode { get; set; }
    public string Body { get; set; } = string.Empty;
    public bool IsShared { get; set; }
    public Workspace Workspace { get; set; } = null!;
}

public class TicketLink : BaseEntity
{
    public Guid TicketId { get; set; }
    public Guid LinkedId { get; set; }
    public string LinkType { get; set; } = "related";
    public Guid? CreatedById { get; set; }
    public Ticket Ticket { get; set; } = null!;
    public Ticket Linked { get; set; } = null!;
}

// ── ENUMS ────────────────────────────────────────────────
public enum WorkspaceType   { Artha, Client }
public enum WorkspaceTier   { Tier1, Tier2, Tier3 }
public enum UserRole        { ArthaAdmin, ArthaSupervisor, ArthaAgentL2, ArthaAgentL1, ClientAdmin, ClientAgent, ClientViewer }
public enum TicketStatus    { Open, Pending, OnHold, Resolved, Closed }
public enum TicketPriority  { Critical, High, Medium, Low }
public enum TicketChannel   { App, Email, Chat, Whatsapp, Phone, Manual, Api }
public enum SLABreachStatus { Within, AtRisk, Breached }
public enum ArticleStatus   { Draft, Review, Published, Archived }
public enum EscalationReason{ SLABreach, CustomerRequest, TechnicalComplexity, LegalCompliance, SeniorApproval, RepeatIssue, Other }
public enum AuditAction     { Created, StatusChanged, PriorityChanged, Assigned, Reassigned, Escalated, DeEscalated, Merged, Split, Linked, Replied, NoteAdded, Closed, Reopened, SLABreach, WorkspaceSwitched, Login, Logout }
public enum NotificationType{ SLAAtRisk, SLABreach, TicketAssigned, TicketEscalated, ClientEscalation, Mention, Reply, Csat, System }

// AppDbContext + EF Configurations

public class AppDbContext : DbContext
{
    public AppDbContext(DbContextOptions<AppDbContext> options) : base(options) { }

    public DbSet<Workspace> Workspaces => Set<Workspace>();
    public DbSet<User> Users => Set<User>();
    public DbSet<UserWorkspaceAccess> UserWorkspaceAccess => Set<UserWorkspaceAccess>();
    public DbSet<Customer> Customers => Set<Customer>();
    public DbSet<Category> Categories => Set<Category>();
    public DbSet<SLAPolicy> SLAPolicies => Set<SLAPolicy>();
    public DbSet<Ticket> Tickets => Set<Ticket>();
    public DbSet<TicketReply> TicketReplies => Set<TicketReply>();
    public DbSet<Attachment> Attachments => Set<Attachment>();
    public DbSet<AuditLog> AuditLogs => Set<AuditLog>();
    public DbSet<Notification> Notifications => Set<Notification>();
    public DbSet<KBArticle> KBArticles => Set<KBArticle>();
    public DbSet<KBArticleShare> KBArticleShares => Set<KBArticleShare>();
    public DbSet<CannedResponse> CannedResponses => Set<CannedResponse>();
    public DbSet<Webhook> Webhooks => Set<Webhook>();
    public DbSet<TicketLink> TicketLinks => Set<TicketLink>();

    protected override void OnModelCreating(ModelBuilder mb)
    {
        mb.HasPostgresEnum<WorkspaceType>()
          .HasPostgresEnum<WorkspaceTier>()
          .HasPostgresEnum<UserRole>()
          .HasPostgresEnum<TicketStatus>()
          .HasPostgresEnum<TicketPriority>()
          .HasPostgresEnum<TicketChannel>()
          .HasPostgresEnum<SLABreachStatus>()
          .HasPostgresEnum<ArticleStatus>()
          .HasPostgresEnum<EscalationReason>()
          .HasPostgresEnum<AuditAction>()
          .HasPostgresEnum<NotificationType>();

        mb.ApplyConfigurationsFromAssembly(typeof(AppDbContext).Assembly);
        base.OnModelCreating(mb);
    }

    public override Task<int> SaveChangesAsync(CancellationToken ct = default)
    {
        foreach (var e in ChangeTracker.Entries<BaseEntity>()
                 .Where(e => e.State == EntityState.Modified))
            e.Entity.UpdatedAt = DateTime.UtcNow;
        return base.SaveChangesAsync(ct);
    }
}

// WorkspaceConfiguration
public class WorkspaceConfiguration : IEntityTypeConfiguration<Workspace>
{
    public void Configure(EntityTypeBuilder<Workspace> b)
    {
        b.ToTable("workspaces");
        b.HasKey(x => x.Id);
        b.Property(x => x.Name).HasMaxLength(150).IsRequired();
        b.Property(x => x.Slug).HasMaxLength(60).IsRequired();
        b.HasIndex(x => x.Slug).IsUnique();
        b.Property(x => x.PrimaryColor).HasMaxLength(7).HasDefaultValue("#1a4480");
        b.HasOne(x => x.DefaultSLAPolicy).WithMany()
         .HasForeignKey(x => x.SLAPolicyId).OnDelete(DeleteBehavior.SetNull);
        b.HasMany(x => x.Users).WithOne(u => u.Workspace).HasForeignKey(u => u.WorkspaceId);
        b.HasMany(x => x.Tickets).WithOne(t => t.Workspace).HasForeignKey(t => t.WorkspaceId);
    }
}

// TicketConfiguration
public class TicketConfiguration : IEntityTypeConfiguration<Ticket>
{
    public void Configure(EntityTypeBuilder<Ticket> b)
    {
        b.ToTable("tickets");
        b.HasKey(x => x.Id);
        b.HasIndex(x => x.TicketNumber).IsUnique();
        b.HasIndex(x => new { x.WorkspaceId, x.Status });
        b.HasIndex(x => new { x.WorkspaceId, x.SLABreachStatus });
        b.HasIndex(x => x.EscalatedToArtha).HasFilter("escalated_to_artha = true");
        b.Property(x => x.Tags).HasColumnType("text[]");
        b.Property(x => x.Metadata).HasColumnType("jsonb");
        b.HasMany(x => x.Replies).WithOne(r => r.Ticket).HasForeignKey(r => r.TicketId);
        b.HasOne(x => x.Assignee).WithMany(u => u.AssignedTickets)
         .HasForeignKey(x => x.AssigneeId).OnDelete(DeleteBehavior.SetNull);
        b.HasOne(x => x.ArthaAssignee).WithMany()
         .HasForeignKey(x => x.ArthaAssigneeId).OnDelete(DeleteBehavior.SetNull);
        b.HasOne(x => x.MergedInto).WithMany()
         .HasForeignKey(x => x.MergedIntoId).OnDelete(DeleteBehavior.SetNull);
    }
}

// AuditLogConfiguration (partitioned table)
public class AuditLogConfiguration : IEntityTypeConfiguration<AuditLog>
{
    public void Configure(EntityTypeBuilder<AuditLog> b)
    {
        b.ToTable("audit_logs");
        b.HasKey(x => x.Id);
        b.HasIndex(x => new { x.WorkspaceId, x.CreatedAt });
        b.HasIndex(x => x.TicketId);
        b.Property(x => x.Payload).HasColumnType("jsonb");
    }
}

// ArthaSupport.Application/Services/

// ── ITicketService ───────────────────────────────────────
public interface ITicketService
{
    Task<PagedResult<TicketListDto>> GetTicketsAsync(TicketFilterDto filter, CancellationToken ct = default);
    Task<TicketDetailDto> GetTicketByIdAsync(Guid ticketId, Guid userId, CancellationToken ct = default);
    Task<TicketDetailDto> CreateTicketAsync(CreateTicketDto dto, Guid createdById, CancellationToken ct = default);
    Task<TicketDetailDto> UpdateTicketAsync(Guid ticketId, UpdateTicketDto dto, Guid updatedById, CancellationToken ct = default);
    Task<TicketReplyDto> ReplyAsync(Guid ticketId, CreateReplyDto dto, Guid authorId, CancellationToken ct = default);
    Task AssignAsync(Guid ticketId, Guid assigneeId, Guid assignedById, string? note = null, CancellationToken ct = default);
    Task EscalateAsync(Guid ticketId, EscalateTicketDto dto, Guid escalatedById, CancellationToken ct = default);
    Task EscalateToArthaAsync(Guid ticketId, EscalateToArthaDto dto, Guid escalatedById, CancellationToken ct = default);
    Task ResolveAsync(Guid ticketId, Guid resolvedById, CancellationToken ct = default);
    Task CloseAsync(Guid ticketId, Guid closedById, CancellationToken ct = default);
    Task ReopenAsync(Guid ticketId, Guid reopenedById, CancellationToken ct = default);
    Task MergeAsync(Guid primaryId, Guid[] mergeIds, Guid mergedById, CancellationToken ct = default);
    Task BulkAssignAsync(Guid[] ticketIds, Guid assigneeId, Guid assignedById, CancellationToken ct = default);
    Task<string> GenerateTicketNumberAsync(Guid workspaceId, CancellationToken ct = default);
}

// ── TicketService ─────────────────────────────────────────
public class TicketService : ITicketService
{
    private readonly AppDbContext _db;
    private readonly ISLAService _sla;
    private readonly INotificationService _notifications;
    private readonly IAuditService _audit;
    private readonly IWebhookService _webhook;
    private readonly IMapper _mapper;

    public TicketService(AppDbContext db, ISLAService sla, INotificationService notifications,
        IAuditService audit, IWebhookService webhook, IMapper mapper)
    { _db=db; _sla=sla; _notifications=notifications; _audit=audit; _webhook=webhook; _mapper=mapper; }

    public async Task<TicketDetailDto> CreateTicketAsync(CreateTicketDto dto, Guid createdById, CancellationToken ct)
    {
        var policy = await _sla.GetApplicablePolicyAsync(dto.WorkspaceId, dto.Priority ?? TicketPriority.Medium, ct);
        var ticket = new Ticket
        {
            WorkspaceId   = dto.WorkspaceId,
            TicketNumber  = await GenerateTicketNumberAsync(dto.WorkspaceId, ct),
            Subject       = dto.Subject,
            Description   = dto.Description,
            Priority      = dto.Priority ?? TicketPriority.Medium,
            Channel       = dto.Channel,
            CategoryId    = dto.CategoryId,
            SubCategoryId = dto.SubCategoryId,
            CustomerId    = dto.CustomerId,
            AssigneeId    = dto.AssigneeId,
            CreatedById   = createdById,
            Tags          = dto.Tags ?? Array.Empty<string>(),
            SLAPolicyId   = policy?.Id,
        };
        if (policy != null)
        {
            ticket.FirstResponseDueAt = _sla.CalculateDueDate(DateTime.UtcNow, policy.FirstResponseMinutes, policy);
            ticket.ResolutionDueAt    = _sla.CalculateDueDate(DateTime.UtcNow, policy.ResolutionMinutes, policy);
        }
        _db.Tickets.Add(ticket);
        await _db.SaveChangesAsync(ct);
        await _audit.LogAsync(ticket.WorkspaceId, ticket.Id, createdById, AuditAction.Created,
            new { ticket.Priority, ticket.Channel, ticket.TicketNumber }, ct);
        if (ticket.AssigneeId.HasValue)
            await _notifications.NotifyAssignmentAsync(ticket, ticket.AssigneeId.Value, ct);
        await _webhook.TriggerAsync(ticket.WorkspaceId, "ticket.created",
            new { ticket.Id, ticket.TicketNumber }, ct);
        return await GetTicketByIdAsync(ticket.Id, createdById, ct);
    }

    public async Task EscalateToArthaAsync(Guid ticketId, EscalateToArthaDto dto, Guid escalatedById, CancellationToken ct)
    {
        var ticket = await _db.Tickets.Include(t => t.Workspace)
            .FirstOrDefaultAsync(t => t.Id == ticketId, ct)
            ?? throw new NotFoundException("Ticket not found");

        if (ticket.Workspace.Type != WorkspaceType.Client)
            throw new BusinessException("Only client workspace tickets can be escalated to Artha");

        ticket.EscalatedToArtha = true;
        ticket.EscalatedAt      = DateTime.UtcNow;
        ticket.EscalatedById    = escalatedById;
        ticket.EscalationReason = dto.Reason;
        ticket.EscalationNote   = dto.Note;
        await _db.SaveChangesAsync(ct);

        await _audit.LogAsync(ticket.WorkspaceId, ticket.Id, escalatedById, AuditAction.Escalated,
            new { dto.Reason, dto.Note, Target = "Artha" }, ct);

        // Notify all Artha supervisors
        var arthaWs = await _db.Workspaces.FirstAsync(w => w.Type == WorkspaceType.Artha, ct);
        var supervisors = await _db.Users
            .Where(u => u.WorkspaceId == arthaWs.Id
                    && (u.Role == UserRole.ArthaSupervisor || u.Role == UserRole.ArthaAdmin)
                    && u.IsActive)
            .ToListAsync(ct);
        foreach (var sup in supervisors)
            await _notifications.NotifyClientEscalationAsync(ticket, sup.Id, ct);
    }

    public async Task<PagedResult<TicketListDto>> GetTicketsAsync(TicketFilterDto filter, CancellationToken ct)
    {
        var q = _db.Tickets.Where(t => t.WorkspaceId == filter.WorkspaceId);
        if (filter.Status != null)           q = q.Where(t => t.Status == filter.Status);
        if (filter.Priority != null)         q = q.Where(t => t.Priority == filter.Priority);
        if (filter.AssigneeId.HasValue)      q = q.Where(t => t.AssigneeId == filter.AssigneeId);
        if (filter.EscalatedToArthaOnly)     q = q.Where(t => t.EscalatedToArtha);
        if (filter.SLAStatus.HasValue)       q = q.Where(t => t.SLABreachStatus == filter.SLAStatus);
        if (!string.IsNullOrWhiteSpace(filter.Search))
            q = q.Where(t => EF.Functions.ToTsVector("english", t.Subject + " " + t.Description)
                             .Matches(EF.Functions.ToTsQuery("english", filter.Search)));
        var total = await q.CountAsync(ct);
        var items = await q.OrderBy(t => t.ResolutionDueAt)
            .Skip((filter.Page - 1) * filter.PageSize).Take(filter.PageSize)
            .ProjectTo<TicketListDto>(_mapper.ConfigurationProvider).ToListAsync(ct);
        return new PagedResult<TicketListDto>(items, total, filter.Page, filter.PageSize);
    }

    public async Task<string> GenerateTicketNumberAsync(Guid workspaceId, CancellationToken ct)
    {
        var ws = await _db.Workspaces.FindAsync(new object[] { workspaceId }, ct)!;
        var prefix = ws!.Type == WorkspaceType.Artha ? "ARTHA"
            : ws.Slug.ToUpper().Replace("-","")[..Math.Min(5, ws.Slug.Length)];
        var seq = await _db.Tickets.CountAsync(t => t.WorkspaceId == workspaceId, ct) + 1;
        return $"{prefix}-{seq:D5}";
    }

    public async Task ResolveAsync(Guid ticketId, Guid resolvedById, CancellationToken ct)
    {
        var t = await _db.Tickets.FindAsync(new object[] { ticketId }, ct)
            ?? throw new NotFoundException("Ticket not found");
        t.Status     = TicketStatus.Resolved;
        t.ResolvedAt = DateTime.UtcNow;
        await _db.SaveChangesAsync(ct);
        await _audit.LogAsync(t.WorkspaceId, t.Id, resolvedById, AuditAction.StatusChanged,
            new { From = "open", To = "resolved" }, ct);
        // Queue CSAT survey after 5 minutes
        // (handled by CsatBackgroundService)
    }
}

// ── ISLAService ──────────────────────────────────────────
public interface ISLAService
{
    Task<SLAPolicy?> GetApplicablePolicyAsync(Guid workspaceId, TicketPriority priority, CancellationToken ct = default);
    DateTime CalculateDueDate(DateTime from, int targetMinutes, SLAPolicy policy);
    Task EvaluateSLAStatusAsync(Guid ticketId, CancellationToken ct = default);
    Task RunSLAWatcherAsync(CancellationToken ct = default);
}

public class SLAService : ISLAService
{
    private readonly AppDbContext _db;
    private readonly INotificationService _notifs;
    public SLAService(AppDbContext db, INotificationService notifs) { _db=db; _notifs=notifs; }

    public DateTime CalculateDueDate(DateTime from, int targetMinutes, SLAPolicy policy)
    {
        if (!policy.BusinessHoursOnly) return from.AddMinutes(targetMinutes);
        var remaining = targetMinutes;
        var current = from;
        while (remaining > 0)
        {
            if (IsBusinessHour(current, policy)) { current = current.AddMinutes(1); remaining--; }
            else current = NextBusinessStart(current, policy);
        }
        return current;
    }

    private bool IsBusinessHour(DateTime dt, SLAPolicy p)
    {
        int dow = (int)dt.DayOfWeek == 0 ? 7 : (int)dt.DayOfWeek;
        return p.BusinessDays.Contains(dow) && dt.Hour >= p.BusinessStartHour && dt.Hour < p.BusinessEndHour;
    }

    private DateTime NextBusinessStart(DateTime dt, SLAPolicy p)
    {
        var next = dt.Date.AddHours(p.BusinessStartHour).AddDays(1);
        while (!p.BusinessDays.Contains((int)next.DayOfWeek == 0 ? 7 : (int)next.DayOfWeek))
            next = next.AddDays(1);
        return next;
    }

    public async Task RunSLAWatcherAsync(CancellationToken ct)
    {
        var openTickets = await _db.Tickets
            .Include(t => t.SLAPolicy)
            .Where(t => t.Status != TicketStatus.Resolved && t.Status != TicketStatus.Closed
                     && t.ResolutionDueAt.HasValue)
            .ToListAsync(ct);

        foreach (var ticket in openTickets)
            await EvaluateSLAStatusAsync(ticket, ct);
    }

    private async Task EvaluateSLAStatusAsync(Ticket ticket, CancellationToken ct)
    {
        if (ticket.Status == TicketStatus.OnHold) return; // SLA paused
        var now = DateTime.UtcNow;
        var dueAt = ticket.ResolutionDueAt!.Value;
        var totalMins = (dueAt - ticket.CreatedAt).TotalMinutes;
        var elapsedMins = (now - ticket.CreatedAt).TotalMinutes - ticket.SLAPausedMinutes;
        var pct = elapsedMins / totalMins * 100;
        var threshold = ticket.SLAPolicy?.AtRiskPercentage ?? 80;

        var newStatus = now > dueAt ? SLABreachStatus.Breached
            : pct >= threshold ? SLABreachStatus.AtRisk : SLABreachStatus.Within;

        if (newStatus != ticket.SLABreachStatus)
        {
            ticket.SLABreachStatus = newStatus;
            if (newStatus == SLABreachStatus.AtRisk && ticket.AssigneeId.HasValue)
                await _notifs.NotifySLAAtRiskAsync(ticket, ct);
            if (newStatus == SLABreachStatus.Breached)
                await _notifs.NotifySLABreachAsync(ticket, ct);
        }
    }

    public Task EvaluateSLAStatusAsync(Guid ticketId, CancellationToken ct) => Task.CompletedTask;
    public Task<SLAPolicy?> GetApplicablePolicyAsync(Guid workspaceId, TicketPriority priority, CancellationToken ct)
        => _db.SLAPolicies.FirstOrDefaultAsync(p => p.WorkspaceId == workspaceId && p.Priority == priority && p.IsActive, ct);
}

// ── SLA Background Service ────────────────────────────────
public class SLAWatcherBackgroundService : BackgroundService
{
    private readonly IServiceScopeFactory _scopeFactory;
    private readonly ILogger<SLAWatcherBackgroundService> _log;
    public SLAWatcherBackgroundService(IServiceScopeFactory f, ILogger<SLAWatcherBackgroundService> log) { _scopeFactory=f; _log=log; }

    protected override async Task ExecuteAsync(CancellationToken stoppingToken)
    {
        while (!stoppingToken.IsCancellationRequested)
        {
            try
            {
                using var scope = _scopeFactory.CreateScope();
                var sla = scope.ServiceProvider.GetRequiredService<ISLAService>();
                await sla.RunSLAWatcherAsync(stoppingToken);
            }
            catch (Exception ex) { _log.LogError(ex, "SLA watcher error"); }
            await Task.Delay(TimeSpan.FromMinutes(5), stoppingToken);
        }
    }
}

// ── IDashboardService ────────────────────────────────────
public class DashboardService : IDashboardService
{
    private readonly AppDbContext _db;
    private readonly IMapper _mapper;
    public DashboardService(AppDbContext db, IMapper mapper) { _db=db; _mapper=mapper; }

    public async Task<DashboardDto> GetDashboardAsync(Guid workspaceId, Guid userId, CancellationToken ct)
    {
        var q = _db.Tickets.Where(t => t.WorkspaceId == workspaceId);
        var today = DateTime.UtcNow.Date;
        return new DashboardDto
        {
            OpenTickets      = await q.CountAsync(t => t.Status == TicketStatus.Open, ct),
            SLABreached      = await q.CountAsync(t => t.SLABreachStatus == SLABreachStatus.Breached, ct),
            AwaitingCustomer = await q.CountAsync(t => t.Status == TicketStatus.Pending, ct),
            ResolvedToday    = await q.CountAsync(t => t.ResolvedAt >= today, ct),
            AgentWorkload    = await _db.Users
                .Where(u => u.WorkspaceId == workspaceId && u.IsActive)
                .Select(u => new AgentWorkloadDto
                {
                    AgentId   = u.Id, AgentName = u.Name,
                    OpenCount = _db.Tickets.Count(t => t.AssigneeId == u.Id && t.Status == TicketStatus.Open)
                }).ToListAsync(ct),
            RecentEscalations = await q
                .Where(t => t.EscalatedToArtha && t.Status != TicketStatus.Resolved)
                .OrderByDescending(t => t.EscalatedAt).Take(5)
                .ProjectTo<TicketListDto>(_mapper.ConfigurationProvider).ToListAsync(ct),
        };
    }

    public async Task<CrossClientOverviewDto> GetCrossClientOverviewAsync(CancellationToken ct)
    {
        var clients = await _db.Workspaces.Where(w => w.Type == WorkspaceType.Client && w.IsActive)
            .Include(w => w.Tickets).ToListAsync(ct);
        return new CrossClientOverviewDto
        {
            TotalClients = clients.Count,
            TotalEscalatedToArtha = clients.Sum(c =>
                c.Tickets.Count(t => t.EscalatedToArtha && t.Status != TicketStatus.Resolved)),
            Clients = clients.Select(c => new ClientHealthDto
            {
                WorkspaceId = c.Id, Name = c.Name, Tier = c.Tier?.ToString() ?? "Standard",
                OpenTickets = c.Tickets.Count(t => new[] { TicketStatus.Open, TicketStatus.Pending, TicketStatus.OnHold }.Contains(t.Status)),
                EscalatedToArtha = c.Tickets.Count(t => t.EscalatedToArtha && t.Status != TicketStatus.Resolved),
                AvgCsat = c.Tickets.Where(t => t.CsatScore.HasValue).Average(t => (double?)t.CsatScore),
            }).ToList()
        };
    }
}

// ArthaSupport.API/Controllers/

[ApiController]
[Route("api/v1/workspaces/{workspaceId}/tickets")]
[Authorize]
public class TicketsController : ControllerBase
{
    private readonly ITicketService _tickets;
    private readonly IMapper _mapper;
    private readonly IWorkspaceAccessGuard _guard;
    public TicketsController(ITicketService t, IMapper m, IWorkspaceAccessGuard g) { _tickets=t; _mapper=m; _guard=g; }

    [HttpGet]
    public async Task<IActionResult> List(Guid workspaceId, [FromQuery] TicketFilterDto filter, CancellationToken ct)
    { await _guard.EnsureAccessAsync(workspaceId, UserId(), ct); filter.WorkspaceId = workspaceId; return Ok(await _tickets.GetTicketsAsync(filter, ct)); }

    [HttpGet("{id:guid}")]
    public async Task<IActionResult> Get(Guid workspaceId, Guid id, CancellationToken ct)
    { await _guard.EnsureAccessAsync(workspaceId, UserId(), ct); return Ok(await _tickets.GetTicketByIdAsync(id, UserId(), ct)); }

    [HttpPost]
    [RequireRole(UserRole.ArthaAdmin, UserRole.ArthaSupervisor, UserRole.ArthaAgentL2, UserRole.ArthaAgentL1, UserRole.ClientAdmin, UserRole.ClientAgent)]
    public async Task<IActionResult> Create(Guid workspaceId, [FromBody] CreateTicketDto dto, CancellationToken ct)
    {
        await _guard.EnsureWriteAccessAsync(workspaceId, UserId(), ct);
        dto.WorkspaceId = workspaceId;
        var ticket = await _tickets.CreateTicketAsync(dto, UserId(), ct);
        return CreatedAtAction(nameof(Get), new { workspaceId, id = ticket.Id }, ticket);
    }

    [HttpPatch("{id:guid}")]
    public async Task<IActionResult> Update(Guid workspaceId, Guid id, [FromBody] UpdateTicketDto dto, CancellationToken ct)
    { await _guard.EnsureWriteAccessAsync(workspaceId, UserId(), ct); return Ok(await _tickets.UpdateTicketAsync(id, dto, UserId(), ct)); }

    [HttpPost("{id:guid}/replies")]
    public async Task<IActionResult> Reply(Guid workspaceId, Guid id, [FromBody] CreateReplyDto dto, CancellationToken ct)
    { await _guard.EnsureReplyAccessAsync(workspaceId, id, UserId(), ct); return Ok(await _tickets.ReplyAsync(id, dto, UserId(), ct)); }

    [HttpPost("{id:guid}/assign")]
    [RequireRole(UserRole.ArthaAdmin, UserRole.ArthaSupervisor, UserRole.ClientAdmin)]
    public async Task<IActionResult> Assign(Guid workspaceId, Guid id, [FromBody] AssignTicketDto dto, CancellationToken ct)
    { await _guard.EnsureWriteAccessAsync(workspaceId, UserId(), ct); await _tickets.AssignAsync(id, dto.AssigneeId, UserId(), dto.Note, ct); return NoContent(); }

    [HttpPost("{id:guid}/escalate")]
    public async Task<IActionResult> Escalate(Guid workspaceId, Guid id, [FromBody] EscalateTicketDto dto, CancellationToken ct)
    { await _guard.EnsureWriteAccessAsync(workspaceId, UserId(), ct); await _tickets.EscalateAsync(id, dto, UserId(), ct); return NoContent(); }

    [HttpPost("{id:guid}/escalate-to-artha")]
    [RequireRole(UserRole.ClientAdmin)]
    public async Task<IActionResult> EscalateToArtha(Guid workspaceId, Guid id, [FromBody] EscalateToArthaDto dto, CancellationToken ct)
    { await _tickets.EscalateToArthaAsync(id, dto, UserId(), ct); return NoContent(); }

    [HttpPost("{id:guid}/resolve")]
    public async Task<IActionResult> Resolve(Guid workspaceId, Guid id, CancellationToken ct)
    { await _guard.EnsureWriteAccessAsync(workspaceId, UserId(), ct); await _tickets.ResolveAsync(id, UserId(), ct); return NoContent(); }

    [HttpPost("{id:guid}/close")]
    public async Task<IActionResult> Close(Guid workspaceId, Guid id, CancellationToken ct)
    { await _guard.EnsureWriteAccessAsync(workspaceId, UserId(), ct); await _tickets.CloseAsync(id, UserId(), ct); return NoContent(); }

    [HttpPost("{id:guid}/reopen")]
    public async Task<IActionResult> Reopen(Guid workspaceId, Guid id, CancellationToken ct)
    { await _guard.EnsureWriteAccessAsync(workspaceId, UserId(), ct); await _tickets.ReopenAsync(id, UserId(), ct); return NoContent(); }

    [HttpPost("{id:guid}/merge")]
    [RequireRole(UserRole.ArthaAdmin, UserRole.ArthaSupervisor)]
    public async Task<IActionResult> Merge(Guid workspaceId, Guid id, [FromBody] MergeTicketsDto dto, CancellationToken ct)
    { await _tickets.MergeAsync(id, dto.MergeIds, UserId(), ct); return NoContent(); }

    [HttpPost("bulk-assign")]
    [RequireRole(UserRole.ArthaAdmin, UserRole.ArthaSupervisor, UserRole.ClientAdmin)]
    public async Task<IActionResult> BulkAssign(Guid workspaceId, [FromBody] BulkAssignDto dto, CancellationToken ct)
    { await _tickets.BulkAssignAsync(dto.TicketIds, dto.AssigneeId, UserId(), ct); return NoContent(); }

    [HttpGet("export")]
    public async Task<IActionResult> Export(Guid workspaceId, [FromQuery] TicketFilterDto filter, CancellationToken ct)
    { await _guard.EnsureAccessAsync(workspaceId, UserId(), ct); filter.WorkspaceId = workspaceId; return Ok(); /* stream CSV */ }

    private Guid UserId() => Guid.Parse(User.FindFirstValue(ClaimTypes.NameIdentifier)!);
}

[ApiController] [Route("api/v1/workspaces")] [Authorize]
public class WorkspacesController : ControllerBase
{
    [HttpGet] public async Task<IActionResult> List(CancellationToken ct) => Ok();
    [HttpPost] [RequireRole(UserRole.ArthaAdmin)]
    public async Task<IActionResult> Create([FromBody] CreateWorkspaceDto dto, CancellationToken ct) => Ok();
    [HttpGet("{id:guid}")] public async Task<IActionResult> Get(Guid id, CancellationToken ct) => Ok();
    [HttpPatch("{id:guid}")] public async Task<IActionResult> Update(Guid id, [FromBody] UpdateWorkspaceDto dto, CancellationToken ct) => Ok();
    [HttpGet("cross-client")] [RequireRole(UserRole.ArthaAdmin, UserRole.ArthaSupervisor)]
    public async Task<IActionResult> CrossClientOverview(CancellationToken ct) => Ok();
    [HttpGet("{id:guid}/health")] [RequireRole(UserRole.ArthaAdmin, UserRole.ArthaSupervisor)]
    public async Task<IActionResult> ClientHealth(Guid id, CancellationToken ct) => Ok();
}

[ApiController] [Route("api/v1/workspaces/{workspaceId}/dashboard")] [Authorize]
public class DashboardController : ControllerBase
{
    private readonly IDashboardService _dashboard;
    public DashboardController(IDashboardService d) { _dashboard=d; }
    [HttpGet] public async Task<IActionResult> Get(Guid workspaceId, CancellationToken ct)
        => Ok(await _dashboard.GetDashboardAsync(workspaceId, Guid.Parse(User.FindFirstValue(ClaimTypes.NameIdentifier)!), ct));
    [HttpGet("agents")] public async Task<IActionResult> Agents(Guid workspaceId, CancellationToken ct) => Ok();
    [HttpGet("volume")] public async Task<IActionResult> Volume(Guid workspaceId, [FromQuery] int days = 7, CancellationToken ct = default) => Ok();
}

[ApiController] [Route("api/v1/workspaces/{workspaceId}/reports")] [Authorize]
public class ReportsController : ControllerBase
{
    [HttpGet] public async Task<IActionResult> List(Guid workspaceId, CancellationToken ct) => Ok();
    [HttpPost("generate")] public async Task<IActionResult> Generate(Guid workspaceId, [FromBody] GenerateReportDto dto, CancellationToken ct) => Ok();
    [HttpGet("jobs/{jobId:guid}")] public async Task<IActionResult> PollJob(Guid workspaceId, Guid jobId, CancellationToken ct) => Ok();
    [HttpPost("schedule")] [RequireRole(UserRole.ArthaAdmin, UserRole.ArthaSupervisor, UserRole.ClientAdmin)]
    public async Task<IActionResult> Schedule(Guid workspaceId, [FromBody] ScheduleReportDto dto, CancellationToken ct) => Ok();
}

[ApiController] [Route("api/v1/workspaces/{workspaceId}/kb")] [Authorize]
public class KnowledgeBaseController : ControllerBase
{
    [HttpGet] public async Task<IActionResult> List(Guid workspaceId, CancellationToken ct) => Ok();
    [HttpPost] public async Task<IActionResult> Create(Guid workspaceId, [FromBody] CreateArticleDto dto, CancellationToken ct) => Ok();
    [HttpGet("{id:guid}")] public async Task<IActionResult> Get(Guid workspaceId, Guid id, CancellationToken ct) => Ok();
    [HttpPatch("{id:guid}")] public async Task<IActionResult> Update(Guid workspaceId, Guid id, [FromBody] UpdateArticleDto dto, CancellationToken ct) => Ok();
    [HttpPost("{id:guid}/share")] [RequireRole(UserRole.ArthaAdmin, UserRole.ArthaSupervisor)]
    public async Task<IActionResult> Share(Guid workspaceId, Guid id, [FromBody] ShareArticleDto dto, CancellationToken ct) => Ok();
    [HttpGet("suggest")] public async Task<IActionResult> Suggest(Guid workspaceId, [FromQuery] Guid ticketId, CancellationToken ct) => Ok();
}

[ApiController] [Route("api/v1/notifications")] [Authorize]
public class NotificationsController : ControllerBase
{
    [HttpGet] public async Task<IActionResult> List([FromQuery] int page = 1, CancellationToken ct = default) => Ok();
    [HttpPost("{id:guid}/read")] public async Task<IActionResult> MarkRead(Guid id, CancellationToken ct) => NoContent();
    [HttpPost("mark-all-read")] public async Task<IActionResult> MarkAllRead(CancellationToken ct) => NoContent();
}

[ApiController] [Route("api/v1/workspaces/{workspaceId}/users")] [Authorize]
public class UsersController : ControllerBase
{
    [HttpGet] [RequireRole(UserRole.ArthaAdmin, UserRole.ClientAdmin)]
    public async Task<IActionResult> List(Guid workspaceId, CancellationToken ct) => Ok();
    [HttpPost] [RequireRole(UserRole.ArthaAdmin, UserRole.ClientAdmin)]
    public async Task<IActionResult> Invite(Guid workspaceId, [FromBody] InviteUserDto dto, CancellationToken ct) => Ok();
    [HttpPatch("{id:guid}")] [RequireRole(UserRole.ArthaAdmin, UserRole.ClientAdmin)]
    public async Task<IActionResult> Update(Guid workspaceId, Guid id, [FromBody] UpdateUserDto dto, CancellationToken ct) => Ok();
    [HttpDelete("{id:guid}")] [RequireRole(UserRole.ArthaAdmin, UserRole.ClientAdmin)]
    public async Task<IActionResult> Deactivate(Guid workspaceId, Guid id, CancellationToken ct) => NoContent();
}

[ApiController] [Route("api/v1/workspaces/{workspaceId}/sla-policies")] [Authorize]
public class SLAPoliciesController : ControllerBase
{
    [HttpGet] public async Task<IActionResult> List(Guid workspaceId, CancellationToken ct) => Ok();
    [HttpPost] [RequireRole(UserRole.ArthaAdmin, UserRole.ArthaSupervisor, UserRole.ClientAdmin)]
    public async Task<IActionResult> Create(Guid workspaceId, [FromBody] CreateSLAPolicyDto dto, CancellationToken ct) => Ok();
    [HttpPatch("{id:guid}")] [RequireRole(UserRole.ArthaAdmin, UserRole.ArthaSupervisor, UserRole.ClientAdmin)]
    public async Task<IActionResult> Update(Guid workspaceId, Guid id, [FromBody] UpdateSLAPolicyDto dto, CancellationToken ct) => Ok();
    [HttpDelete("{id:guid}")] [RequireRole(UserRole.ArthaAdmin)]
    public async Task<IActionResult> Delete(Guid workspaceId, Guid id, CancellationToken ct) => NoContent();
}

// DTOs — ArthaSupport.Application/DTOs/

public class TicketFilterDto
{
    public Guid WorkspaceId { get; set; }
    public TicketStatus? Status { get; set; }
    public TicketPriority? Priority { get; set; }
    public Guid? CategoryId { get; set; }
    public Guid? AssigneeId { get; set; }
    public TicketChannel? Channel { get; set; }
    public SLABreachStatus? SLAStatus { get; set; }
    public bool EscalatedToArthaOnly { get; set; }
    public string? Search { get; set; }
    public DateTime? DateFrom { get; set; }
    public DateTime? DateTo { get; set; }
    public string[]? Tags { get; set; }
    public int Page { get; set; } = 1;
    public int PageSize { get; set; } = 25;
    public string SortBy { get; set; } = "resolution_due_at";
    public bool SortDesc { get; set; }
}

public class CreateTicketDto
{
    public Guid WorkspaceId { get; set; }
    [Required, MaxLength(500)] public string Subject { get; set; } = string.Empty;
    public string? Description { get; set; }
    [Required] public TicketChannel Channel { get; set; }
    public Guid? CategoryId { get; set; }
    public Guid? SubCategoryId { get; set; }
    public Guid? CustomerId { get; set; }
    public Guid? AssigneeId { get; set; }
    public TicketPriority? Priority { get; set; }
    public string[]? Tags { get; set; }
}

public class UpdateTicketDto
{
    public TicketStatus? Status { get; set; }
    public TicketPriority? Priority { get; set; }
    public Guid? CategoryId { get; set; }
    public Guid? AssigneeId { get; set; }
    public string[]? Tags { get; set; }
}

public class CreateReplyDto
{
    [Required] public string Body { get; set; } = string.Empty;
    public bool IsInternal { get; set; }
    public List<Guid>? AttachmentIds { get; set; }
    public TicketStatus? ChangeStatusTo { get; set; }
}

public class EscalateTicketDto
{
    [Required] public EscalationReason Reason { get; set; }
    [Required] public string Note { get; set; } = string.Empty;
    public Guid? EscalateTo { get; set; }
}

public class EscalateToArthaDto
{
    [Required] public EscalationReason Reason { get; set; }
    [Required, MaxLength(1000)] public string Note { get; set; } = string.Empty;
}

public class AssignTicketDto { public Guid AssigneeId { get; set; } public string? Note { get; set; } }
public class MergeTicketsDto { public Guid[] MergeIds { get; set; } = Array.Empty<Guid>(); }
public class BulkAssignDto   { public Guid[] TicketIds { get; set; } = Array.Empty<Guid>(); public Guid AssigneeId { get; set; } }

// Response DTOs
public class TicketListDto
{
    public Guid Id { get; set; }
    public string TicketNumber { get; set; } = string.Empty;
    public string Subject { get; set; } = string.Empty;
    public string? CustomerName { get; set; }
    public string? CategoryName { get; set; }
    public TicketStatus Status { get; set; }
    public TicketPriority Priority { get; set; }
    public SLABreachStatus SLABreachStatus { get; set; }
    public DateTime? ResolutionDueAt { get; set; }
    public string? AssigneeName { get; set; }
    public TicketChannel Channel { get; set; }
    public bool EscalatedToArtha { get; set; }
    public DateTime CreatedAt { get; set; }
    public DateTime UpdatedAt { get; set; }
}

public class TicketDetailDto : TicketListDto
{
    public string? Description { get; set; }
    public Guid? CustomerId { get; set; }
    public string? CustomerEmail { get; set; }
    public Guid? AssigneeId { get; set; }
    public DateTime? FirstResponseDueAt { get; set; }
    public DateTime? FirstRespondedAt { get; set; }
    public DateTime? ResolvedAt { get; set; }
    public int? CsatScore { get; set; }
    public string[] Tags { get; set; } = Array.Empty<string>();
    public List<TicketReplyDto> Replies { get; set; } = new();
    public List<AuditLogDto> AuditLogs { get; set; } = new();
    public CustomerProfileDto? Customer { get; set; }
}

public class TicketReplyDto
{
    public Guid Id { get; set; }
    public string Body { get; set; } = string.Empty;
    public string? BodyHtml { get; set; }
    public bool IsInternal { get; set; }
    public string AuthorName { get; set; } = string.Empty;
    public Guid AuthorId { get; set; }
    public DateTime CreatedAt { get; set; }
}

public class DashboardDto
{
    public int OpenTickets { get; set; }
    public int SLABreached { get; set; }
    public int AwaitingCustomer { get; set; }
    public int ResolvedToday { get; set; }
    public double AvgFirstResponseHours { get; set; }
    public List<AgentWorkloadDto> AgentWorkload { get; set; } = new();
    public List<TicketListDto> RecentEscalations { get; set; } = new();
    public List<VolumeDataPointDto> VolumeChart { get; set; } = new();
}

public class CrossClientOverviewDto
{
    public int TotalClients { get; set; }
    public int TotalEscalatedToArtha { get; set; }
    public double AvgSLACompliance { get; set; }
    public List<ClientHealthDto> Clients { get; set; } = new();
}

public class ClientHealthDto
{
    public Guid WorkspaceId { get; set; }
    public string Name { get; set; } = string.Empty;
    public string Tier { get; set; } = string.Empty;
    public int OpenTickets { get; set; }
    public int EscalatedToArtha { get; set; }
    public double SLACompliancePct { get; set; }
    public double? AvgCsat { get; set; }
    public DateTime? LastActivity { get; set; }
}

public class AgentWorkloadDto { public Guid AgentId { get; set; } public string AgentName { get; set; } = string.Empty; public int OpenCount { get; set; } }
public class VolumeDataPointDto { public DateTime Date { get; set; } public int Created { get; set; } public int Resolved { get; set; } }
public class AuditLogDto { public Guid Id { get; set; } public string? ActorName { get; set; } public AuditAction Action { get; set; } public Dictionary<string, object> Payload { get; set; } = new(); public DateTime CreatedAt { get; set; } }
public class WorkspaceDto { public Guid Id { get; set; } public string Name { get; set; } = string.Empty; public string Slug { get; set; } = string.Empty; public WorkspaceType Type { get; set; } public string? LogoUrl { get; set; } public string PrimaryColor { get; set; } = string.Empty; public bool IsActive { get; set; } }
public class UserDto { public Guid Id { get; set; } public string Name { get; set; } = string.Empty; public string Email { get; set; } = string.Empty; public UserRole Role { get; set; } public bool IsActive { get; set; } public DateTime? LastLoginAt { get; set; } }
public class CustomerProfileDto { public Guid Id { get; set; } public string Name { get; set; } = string.Empty; public string? Email { get; set; } public string? Phone { get; set; } public string? ExternalAccountId { get; set; } public int TotalTickets { get; set; } public decimal? CsatAvg { get; set; } }
public record PagedResult<T>(List<T> Items, int Total, int Page, int PageSize) { public int TotalPages => (int)Math.Ceiling((double)Total / PageSize); }
// Additional request DTOs
public class CreateWorkspaceDto { [Required] public string Name { get; set; } = string.Empty; [Required] public string Slug { get; set; } = string.Empty; public WorkspaceTier? Tier { get; set; } public string? AdminEmail { get; set; } }
public class UpdateWorkspaceDto { public string? Name { get; set; } public string? PrimaryColor { get; set; } public int? AutoCloseDays { get; set; } }
public class InviteUserDto { [Required] public string Email { get; set; } = string.Empty; [Required] public string Name { get; set; } = string.Empty; [Required] public UserRole Role { get; set; } }
public class UpdateUserDto { public UserRole? Role { get; set; } public bool? IsActive { get; set; } public bool? AllWorkspaces { get; set; } }
public class GenerateReportDto { public string ReportType { get; set; } = string.Empty; public DateTime DateFrom { get; set; } public DateTime DateTo { get; set; } public string Format { get; set; } = "csv"; }
public class ScheduleReportDto { public string ReportType { get; set; } = string.Empty; public string Cron { get; set; } = string.Empty; public string[] Recipients { get; set; } = Array.Empty<string>(); }
public class CreateArticleDto { [Required] public string Title { get; set; } = string.Empty; [Required] public string Body { get; set; } = string.Empty; public string? KBCategoryId { get; set; } public string[]? Tags { get; set; } }
public class UpdateArticleDto { public string? Title { get; set; } public string? Body { get; set; } public ArticleStatus? Status { get; set; } }
public class ShareArticleDto { public Guid[] WorkspaceIds { get; set; } = Array.Empty<Guid>(); }
public class CreateSLAPolicyDto { [Required] public string Name { get; set; } = string.Empty; [Required] public TicketPriority Priority { get; set; } public int FirstResponseMinutes { get; set; } public int ResolutionMinutes { get; set; } public bool BusinessHoursOnly { get; set; } }
public class UpdateSLAPolicyDto { public string? Name { get; set; } public int? FirstResponseMinutes { get; set; } public int? ResolutionMinutes { get; set; } public bool? IsActive { get; set; } }

// AutoMapper Profile + DI Registration

public class MappingProfile : Profile
{
    public MappingProfile()
    {
        CreateMap<Ticket, TicketListDto>()
            .ForMember(d => d.CustomerName, o => o.MapFrom(s => s.Customer != null ? s.Customer.Name : null))
            .ForMember(d => d.CategoryName, o => o.MapFrom(s => s.Category != null ? s.Category.Name : null))
            .ForMember(d => d.AssigneeName, o => o.MapFrom(s => s.Assignee != null ? s.Assignee.Name : null));

        CreateMap<Ticket, TicketDetailDto>()
            .IncludeBase<Ticket, TicketListDto>()
            .ForMember(d => d.CustomerEmail, o => o.MapFrom(s => s.Customer != null ? s.Customer.Email : null))
            .ForMember(d => d.Customer, o => o.MapFrom(s => s.Customer))
            .ForMember(d => d.Replies, o => o.MapFrom(s => s.Replies.OrderBy(r => r.CreatedAt).ToList()))
            .ForMember(d => d.AuditLogs, o => o.MapFrom(s => s.AuditLogs.OrderByDescending(a => a.CreatedAt).Take(50).ToList()));

        CreateMap<TicketReply, TicketReplyDto>()
            .ForMember(d => d.AuthorName, o => o.MapFrom(s => s.Author.Name));

        CreateMap<Customer, CustomerProfileDto>();
        CreateMap<AuditLog, AuditLogDto>()
            .ForMember(d => d.ActorName, o => o.MapFrom(s => s.Actor != null ? s.Actor.Name : "System"));
        CreateMap<Workspace, WorkspaceDto>();
        CreateMap<User, UserDto>();
    }
}

// Program.cs — DI Registration
public static class ServiceRegistration
{
    public static IServiceCollection AddApplicationServices(this IServiceCollection services, IConfiguration config)
    {
        services.AddDbContext<AppDbContext>(opts =>
            opts.UseNpgsql(config.GetConnectionString("Default"),
                npg => { npg.EnableRetryOnFailure(3); npg.UseNetTopologySuite(); }));

        services.AddScoped<ITicketService, TicketService>();
        services.AddScoped<ISLAService, SLAService>();
        services.AddScoped<INotificationService, NotificationService>();
        services.AddScoped<IAuditService, AuditService>();
        services.AddScoped<IDashboardService, DashboardService>();
        services.AddScoped<IWebhookService, WebhookService>();
        services.AddScoped<IWorkspaceAccessGuard, WorkspaceAccessGuard>();

        services.AddAutoMapper(typeof(MappingProfile).Assembly);
        services.AddSignalR();

        services.AddHostedService<SLAWatcherBackgroundService>();
        services.AddHostedService<AutoCloseBackgroundService>();
        services.AddHostedService<CsatSurveyBackgroundService>();

        services.AddStackExchangeRedisCache(opts =>
            opts.Configuration = config.GetConnectionString("Redis"));

        services.AddRateLimiter(opts =>
        {
            opts.AddFixedWindowLimiter("api", o => { o.PermitLimit = 100; o.Window = TimeSpan.FromMinutes(1); });
        });

        return services;
    }
}

// ═══════════════════════════════════════════════════════════════════════
// AUTH0 INTEGRATION — Client-Facing Portal Authentication
// ArthaSupport.Infrastructure/Auth/Auth0/
// ═══════════════════════════════════════════════════════════════════════

// ── Auth0 Config Entity ───────────────────────────────────────────────
public class WorkspaceAuthConfig : BaseEntity
{
    public Guid WorkspaceId { get; set; }
    public AuthProvider Provider { get; set; } = AuthProvider.Internal;
    // Auth0 fields
    public string? Auth0Domain { get; set; }         // finco.au.auth0.com
    public string? Auth0ClientId { get; set; }
    public string? Auth0ClientSecret { get; set; }   // stored encrypted
    public string? Auth0Audience { get; set; }
    public string Auth0AgentRoleClaim { get; set; } = "https://support.client.io/role";
    public string Auth0CustomerIdClaim { get; set; } = "https://support.client.io/account_id";
    // Portal
    public string? PortalDomain { get; set; }        // support.finco.io
    public bool PortalEnabled { get; set; } = true;
    public string[] AgentRoleValues { get; set; } = { "support_agent","agent","support-agent" };
    public string[] CustomerRoleValues { get; set; } = { "customer","user","account_holder" };
    public bool IsActive { get; set; } = true;
    public Workspace Workspace { get; set; } = null!;
}

public class Auth0CustomerMap : BaseEntity
{
    public Guid WorkspaceId { get; set; }
    public string Auth0Sub { get; set; } = string.Empty;  // "auth0|abc123"
    public Guid CustomerId { get; set; }
    public string? Email { get; set; }
    public string? Name { get; set; }
    public DateTime LastSeen { get; set; } = DateTime.UtcNow;
    public Workspace Workspace { get; set; } = null!;
    public Customer Customer { get; set; } = null!;
}

public class Auth0AgentMap : BaseEntity
{
    public Guid WorkspaceId { get; set; }
    public string Auth0Sub { get; set; } = string.Empty;
    public Guid UserId { get; set; }
    public string? Email { get; set; }
    public DateTime LastSeen { get; set; } = DateTime.UtcNow;
    public Workspace Workspace { get; set; } = null!;
    public User User { get; set; } = null!;
}

public enum AuthProvider { Internal, Auth0, Saml, GoogleWorkspace, MicrosoftEntra }

// ── Auth0 Token Validation Service ────────────────────────────────────
public interface IAuth0Service
{
    Task<Auth0TokenResult> ValidateTokenAsync(string token, string workspaceSlug, CancellationToken ct = default);
    Task<Auth0Actor> ResolveActorAsync(Auth0TokenResult token, Guid workspaceId, CancellationToken ct = default);
}

public class Auth0TokenResult
{
    public string Sub { get; set; } = string.Empty;       // Auth0 user_id
    public string Email { get; set; } = string.Empty;
    public string Name { get; set; } = string.Empty;
    public string? Role { get; set; }                      // from custom claim
    public string? AccountId { get; set; }                 // client's internal account ID
    public bool IsAgent { get; set; }
    public bool IsCustomer { get; set; }
    public Dictionary<string, object> Claims { get; set; } = new();
}

public class Auth0Actor
{
    public ActorType Type { get; set; }
    public Guid ActorId { get; set; }        // CustomerId or UserId
    public Guid WorkspaceId { get; set; }
    public string Name { get; set; } = string.Empty;
    public string Email { get; set; } = string.Empty;
    public UserRole? AgentRole { get; set; } // only set if Type == Agent
    public string Auth0Sub { get; set; } = string.Empty;
}

public enum ActorType { Customer, Agent }

public class Auth0Service : IAuth0Service
{
    private readonly AppDbContext _db;
    private readonly IMemoryCache _cache;
    private readonly ILogger<Auth0Service> _log;
    private readonly IDataProtector _protector;

    public Auth0Service(AppDbContext db, IMemoryCache cache,
        ILogger<Auth0Service> log, IDataProtectionProvider dp)
    {
        _db = db; _cache = cache; _log = log;
        _protector = dp.CreateProtector("Auth0ClientSecret");
    }

    public async Task<Auth0TokenResult> ValidateTokenAsync(
        string token, string workspaceSlug, CancellationToken ct)
    {
        var config = await GetWorkspaceAuthConfigAsync(workspaceSlug, ct)
            ?? throw new AuthException("Auth0 not configured for this workspace");

        if (!config.IsActive || config.Provider != AuthProvider.Auth0)
            throw new AuthException("Auth0 not enabled for this workspace");

        // Validate JWT with Auth0 JWKS
        var cacheKey = $"auth0_jwks_{config.Auth0Domain}";
        var signingKeys = await _cache.GetOrCreateAsync(cacheKey, async entry => {
            entry.AbsoluteExpirationRelativeToNow = TimeSpan.FromHours(1);
            return await FetchJwksAsync(config.Auth0Domain!, ct);
        });

        var validationParams = new TokenValidationParameters
        {
            ValidIssuer            = $"https://{config.Auth0Domain}/",
            ValidAudience          = config.Auth0Audience,
            IssuerSigningKeys      = signingKeys,
            ValidateIssuerSigningKey = true,
            ValidateLifetime       = true,
            ClockSkew              = TimeSpan.FromSeconds(30),
        };

        var handler = new JsonWebTokenHandler();
        var result = await handler.ValidateTokenAsync(token, validationParams);
        if (!result.IsValid)
            throw new AuthException($"Invalid Auth0 token: {result.Exception?.Message}");

        var claims = result.ClaimsIdentity.Claims.ToDictionary(c => c.Type, c => (object)c.Value);
        var role = claims.TryGetValue(config.Auth0AgentRoleClaim, out var r) ? r?.ToString() : null;
        var accountId = claims.TryGetValue(config.Auth0CustomerIdClaim, out var a) ? a?.ToString() : null;
        var sub = result.ClaimsIdentity.FindFirst("sub")?.Value ?? string.Empty;
        var email = result.ClaimsIdentity.FindFirst("email")?.Value ?? string.Empty;
        var name = result.ClaimsIdentity.FindFirst("name")?.Value ?? email;

        bool isAgent = role != null && config.AgentRoleValues.Contains(role, StringComparer.OrdinalIgnoreCase);
        bool isCustomer = !isAgent;

        return new Auth0TokenResult
        {
            Sub = sub, Email = email, Name = name, Role = role,
            AccountId = accountId, IsAgent = isAgent, IsCustomer = isCustomer, Claims = claims
        };
    }

    public async Task<Auth0Actor> ResolveActorAsync(
        Auth0TokenResult token, Guid workspaceId, CancellationToken ct)
    {
        return token.IsAgent
            ? await ResolveAgentAsync(token, workspaceId, ct)
            : await ResolveCustomerAsync(token, workspaceId, ct);
    }

    // ── Resolve or create Agent from Auth0 token ──────────────────────
    private async Task<Auth0Actor> ResolveAgentAsync(
        Auth0TokenResult token, Guid workspaceId, CancellationToken ct)
    {
        // Check existing mapping
        var map = await _db.Set<Auth0AgentMap>()
            .Include(m => m.User)
            .FirstOrDefaultAsync(m => m.WorkspaceId == workspaceId && m.Auth0Sub == token.Sub, ct);

        if (map != null)
        {
            // Update last seen
            map.LastSeen = DateTime.UtcNow;
            await _db.SaveChangesAsync(ct);
            return new Auth0Actor
            {
                Type = ActorType.Agent, ActorId = map.UserId,
                WorkspaceId = workspaceId, Auth0Sub = token.Sub,
                Name = map.User.Name, Email = map.Email ?? token.Email,
                AgentRole = map.User.Role
            };
        }

        // Check if user exists by email
        var existingUser = await _db.Users
            .FirstOrDefaultAsync(u => u.WorkspaceId == workspaceId && u.Email == token.Email, ct);

        if (existingUser == null)
        {
            // Auto-provision agent from Auth0 — role defaults to ClientAgent
            existingUser = new User
            {
                WorkspaceId = workspaceId,
                Email       = token.Email,
                Name        = token.Name,
                Role        = UserRole.ClientAgent,
                IsActive    = true,
            };
            _db.Users.Add(existingUser);
            await _db.SaveChangesAsync(ct);
            _log.LogInformation("Auto-provisioned agent {Email} from Auth0 in workspace {WsId}",
                token.Email, workspaceId);
        }

        // Create mapping
        var newMap = new Auth0AgentMap
        {
            WorkspaceId = workspaceId, Auth0Sub = token.Sub,
            UserId = existingUser.Id, Email = token.Email
        };
        _db.Set<Auth0AgentMap>().Add(newMap);
        await _db.SaveChangesAsync(ct);

        return new Auth0Actor
        {
            Type = ActorType.Agent, ActorId = existingUser.Id,
            WorkspaceId = workspaceId, Auth0Sub = token.Sub,
            Name = existingUser.Name, Email = token.Email,
            AgentRole = existingUser.Role
        };
    }

    // ── Resolve or create Customer from Auth0 token ───────────────────
    private async Task<Auth0Actor> ResolveCustomerAsync(
        Auth0TokenResult token, Guid workspaceId, CancellationToken ct)
    {
        // Check existing mapping
        var map = await _db.Set<Auth0CustomerMap>()
            .Include(m => m.Customer)
            .FirstOrDefaultAsync(m => m.WorkspaceId == workspaceId && m.Auth0Sub == token.Sub, ct);

        if (map != null)
        {
            map.LastSeen = DateTime.UtcNow;
            await _db.SaveChangesAsync(ct);
            return new Auth0Actor
            {
                Type = ActorType.Customer, ActorId = map.CustomerId,
                WorkspaceId = workspaceId, Auth0Sub = token.Sub,
                Name = map.Name ?? token.Name, Email = map.Email ?? token.Email
            };
        }

        // Auto-provision customer — lookup by external account ID first
        Customer? customer = null;
        if (!string.IsNullOrEmpty(token.AccountId))
        {
            customer = await _db.Customers.FirstOrDefaultAsync(
                c => c.WorkspaceId == workspaceId && c.ExternalAccountId == token.AccountId, ct);
        }

        // Fallback: lookup by email
        customer ??= await _db.Customers.FirstOrDefaultAsync(
            c => c.WorkspaceId == workspaceId && c.Email == token.Email, ct);

        // Create new customer record if not found
        if (customer == null)
        {
            customer = new Customer
            {
                WorkspaceId         = workspaceId,
                ExternalAccountId   = token.AccountId,
                Name                = token.Name,
                Email               = token.Email,
            };
            _db.Customers.Add(customer);
            await _db.SaveChangesAsync(ct);
            _log.LogInformation("Auto-provisioned customer {Email} from Auth0 in workspace {WsId}",
                token.Email, workspaceId);
        }

        // Create Auth0 → Customer mapping
        var newMap = new Auth0CustomerMap
        {
            WorkspaceId = workspaceId, Auth0Sub = token.Sub,
            CustomerId = customer.Id, Email = token.Email, Name = token.Name
        };
        _db.Set<Auth0CustomerMap>().Add(newMap);
        await _db.SaveChangesAsync(ct);

        return new Auth0Actor
        {
            Type = ActorType.Customer, ActorId = customer.Id,
            WorkspaceId = workspaceId, Auth0Sub = token.Sub,
            Name = token.Name, Email = token.Email
        };
    }

    private async Task<WorkspaceAuthConfig?> GetWorkspaceAuthConfigAsync(
        string slug, CancellationToken ct)
    {
        return await _db.Set<WorkspaceAuthConfig>()
            .Include(c => c.Workspace)
            .FirstOrDefaultAsync(c => c.Workspace.Slug == slug && c.IsActive, ct);
    }

    private async Task<IEnumerable<SecurityKey>> FetchJwksAsync(string domain, CancellationToken ct)
    {
        using var http = new HttpClient();
        var json = await http.GetStringAsync($"https://{domain}/.well-known/jwks.json", ct);
        var jwks = new JsonWebKeySet(json);
        return jwks.GetSigningKeys();
    }
}

// ── Auth0 Middleware ──────────────────────────────────────────────────
// Registered before UseAuthorization in Program.cs
public class Auth0PortalMiddleware
{
    private readonly RequestDelegate _next;
    public Auth0PortalMiddleware(RequestDelegate next) { _next = next; }

    public async Task InvokeAsync(HttpContext ctx, IAuth0Service auth0Svc, AppDbContext db)
    {
        // Only intercept portal routes: /api/v1/portal/{workspaceSlug}/...
        var path = ctx.Request.Path.Value ?? "";
        if (!path.StartsWith("/api/v1/portal/")) { await _next(ctx); return; }

        var segments = path.Split('/', StringSplitOptions.RemoveEmptyEntries);
        if (segments.Length < 3) { ctx.Response.StatusCode = 400; return; }

        var workspaceSlug = segments[2]; // portal/{slug}/...
        var authHeader = ctx.Request.Headers["Authorization"].FirstOrDefault();
        if (string.IsNullOrEmpty(authHeader) || !authHeader.StartsWith("Bearer "))
        {
            ctx.Response.StatusCode = 401; return;
        }

        var token = authHeader["Bearer ".Length..];
        try
        {
            var tokenResult = await auth0Svc.ValidateTokenAsync(token, workspaceSlug, ctx.RequestAborted);
            var workspace = await db.Workspaces.FirstOrDefaultAsync(w => w.Slug == workspaceSlug);
            if (workspace == null) { ctx.Response.StatusCode = 404; return; }

            var actor = await auth0Svc.ResolveActorAsync(tokenResult, workspace.Id, ctx.RequestAborted);

            // Inject actor into HttpContext for controllers
            ctx.Items["Auth0Actor"] = actor;
            ctx.Items["PortalWorkspaceId"] = workspace.Id;
            ctx.Items["PortalWorkspaceSlug"] = workspaceSlug;
        }
        catch (AuthException ex)
        {
            ctx.Response.StatusCode = 401;
            await ctx.Response.WriteAsJsonAsync(new { error = ex.Message });
            return;
        }

        await _next(ctx);
    }
}

// ── Customer Portal Controller ────────────────────────────────────────
// All routes: /api/v1/portal/{workspaceSlug}/...
// Auth: Auth0 token (customer OR agent role)
[ApiController]
[Route("api/v1/portal/{workspaceSlug}")]
public class CustomerPortalController : ControllerBase
{
    private readonly AppDbContext _db;
    private readonly ITicketService _tickets;
    private readonly IMapper _mapper;

    public CustomerPortalController(AppDbContext db, ITicketService t, IMapper m)
    { _db=db; _tickets=t; _mapper=m; }

    // GET /api/v1/portal/{slug}/tickets — customer sees OWN tickets only
    [HttpGet("tickets")]
    public async Task<IActionResult> MyTickets(string workspaceSlug,
        [FromQuery] TicketStatus? status, [FromQuery] int page = 1, CancellationToken ct = default)
    {
        var actor = GetActor();
        var wsId = GetWorkspaceId();

        IQueryable<Ticket> q;
        if (actor.Type == ActorType.Customer)
        {
            // Customers: only their own tickets
            q = _db.Tickets.Where(t => t.WorkspaceId == wsId && t.CustomerId == actor.ActorId);
        }
        else
        {
            // Agents: all tickets in workspace — full list (same as internal agent view)
            q = _db.Tickets.Where(t => t.WorkspaceId == wsId);
        }

        if (status.HasValue) q = q.Where(t => t.Status == status);
        var total = await q.CountAsync(ct);
        var items = await q.OrderByDescending(t => t.UpdatedAt)
            .Skip((page-1)*20).Take(20)
            .ProjectTo<CustomerTicketDto>(_mapper.ConfigurationProvider).ToListAsync(ct);

        return Ok(new PagedResult<CustomerTicketDto>(items, total, page, 20));
    }

    // GET /api/v1/portal/{slug}/tickets/{id} — customer sees own ticket only
    [HttpGet("tickets/{id:guid}")]
    public async Task<IActionResult> GetTicket(string workspaceSlug, Guid id, CancellationToken ct)
    {
        var actor = GetActor();
        var wsId = GetWorkspaceId();

        var ticket = await _db.Tickets
            .Include(t => t.Replies.Where(r => !r.IsInternal))  // hide internal notes from customers
            .Include(t => t.Category)
            .FirstOrDefaultAsync(t => t.Id == id && t.WorkspaceId == wsId, ct);

        if (ticket == null) return NotFound();

        // Customer: must own the ticket
        if (actor.Type == ActorType.Customer && ticket.CustomerId != actor.ActorId)
            return Forbid();

        return Ok(_mapper.Map<CustomerTicketDetailDto>(ticket));
    }

    // POST /api/v1/portal/{slug}/tickets — customer creates ticket
    [HttpPost("tickets")]
    public async Task<IActionResult> CreateTicket(string workspaceSlug,
        [FromBody] CustomerCreateTicketDto dto, CancellationToken ct)
    {
        var actor = GetActor();
        var wsId = GetWorkspaceId();

        Guid? customerId = null;
        Guid? agentId = null;

        if (actor.Type == ActorType.Customer)
        {
            customerId = actor.ActorId;
        }
        else
        {
            // Agent creating on behalf of a customer
            customerId = dto.CustomerId;
            agentId = actor.ActorId;
        }

        var createDto = new CreateTicketDto
        {
            WorkspaceId = wsId,
            Subject     = dto.Subject,
            Description = dto.Description,
            Channel     = TicketChannel.App,           // portal = app channel
            CategoryId  = dto.CategoryId,
            CustomerId  = customerId,
            AssigneeId  = agentId,
            Priority    = dto.Priority ?? TicketPriority.Medium,
        };

        var ticket = await _tickets.CreateTicketAsync(createDto, actor.ActorId, ct);
        return CreatedAtAction(nameof(GetTicket), new { workspaceSlug, id = ticket.Id }, ticket);
    }

    // POST /api/v1/portal/{slug}/tickets/{id}/replies — customer replies
    [HttpPost("tickets/{id:guid}/replies")]
    public async Task<IActionResult> Reply(string workspaceSlug, Guid id,
        [FromBody] CustomerReplyDto dto, CancellationToken ct)
    {
        var actor = GetActor();
        var wsId = GetWorkspaceId();

        var ticket = await _db.Tickets.FirstOrDefaultAsync(
            t => t.Id == id && t.WorkspaceId == wsId, ct);
        if (ticket == null) return NotFound();

        // Customer can only reply to their own ticket
        if (actor.Type == ActorType.Customer && ticket.CustomerId != actor.ActorId)
            return Forbid();

        // Agents can also use this endpoint to reply
        bool isInternal = actor.Type == ActorType.Agent && dto.IsInternal;

        var replyDto = new CreateReplyDto
        {
            Body        = dto.Body,
            IsInternal  = isInternal,
            ChangeStatusTo = dto.ChangeStatusTo,
        };

        var reply = await _tickets.ReplyAsync(id, replyDto, actor.ActorId, ct);
        return Ok(reply);
    }

    // POST /api/v1/portal/{slug}/tickets/{id}/csat — customer submits CSAT
    [HttpPost("tickets/{id:guid}/csat")]
    public async Task<IActionResult> SubmitCsat(string workspaceSlug, Guid id,
        [FromBody] CsatSubmitDto dto, CancellationToken ct)
    {
        var actor = GetActor();
        var wsId = GetWorkspaceId();
        if (actor.Type != ActorType.Customer) return Forbid();

        var ticket = await _db.Tickets.FirstOrDefaultAsync(
            t => t.Id == id && t.WorkspaceId == wsId && t.CustomerId == actor.ActorId, ct);
        if (ticket == null) return NotFound();

        ticket.CsatScore   = dto.Score;
        ticket.CsatComment = dto.Comment;
        await _db.SaveChangesAsync(ct);
        return NoContent();
    }

    // GET /api/v1/portal/{slug}/me — return resolved actor info
    [HttpGet("me")]
    public IActionResult Me(string workspaceSlug)
    {
        var actor = GetActor();
        return Ok(new { actor.Type, actor.ActorId, actor.Name, actor.Email, actor.AgentRole });
    }

    // ── Agent-only portal routes ──────────────────────────────────────

    // GET /api/v1/portal/{slug}/agent/tickets — all tickets (agent view)
    [HttpGet("agent/tickets")]
    public async Task<IActionResult> AgentTickets(string workspaceSlug,
        [FromQuery] TicketFilterDto filter, CancellationToken ct)
    {
        var actor = GetActor();
        if (actor.Type != ActorType.Agent) return Forbid();
        filter.WorkspaceId = GetWorkspaceId();
        return Ok(await _tickets.GetTicketsAsync(filter, ct));
    }

    // POST /api/v1/portal/{slug}/agent/tickets/{id}/assign
    [HttpPost("agent/tickets/{id:guid}/assign")]
    public async Task<IActionResult> AgentAssign(string workspaceSlug, Guid id,
        [FromBody] AssignTicketDto dto, CancellationToken ct)
    {
        var actor = GetActor();
        if (actor.Type != ActorType.Agent) return Forbid();
        await _tickets.AssignAsync(id, dto.AssigneeId, actor.ActorId, dto.Note, ct);
        return NoContent();
    }

    // POST /api/v1/portal/{slug}/agent/tickets/{id}/resolve
    [HttpPost("agent/tickets/{id:guid}/resolve")]
    public async Task<IActionResult> AgentResolve(string workspaceSlug, Guid id, CancellationToken ct)
    {
        var actor = GetActor();
        if (actor.Type != ActorType.Agent) return Forbid();
        await _tickets.ResolveAsync(id, actor.ActorId, ct);
        return NoContent();
    }

    // ── Helpers ───────────────────────────────────────────────────────
    private Auth0Actor GetActor() => (Auth0Actor)HttpContext.Items["Auth0Actor"]!;
    private Guid GetWorkspaceId() => (Guid)HttpContext.Items["PortalWorkspaceId"]!;
}

// ── Portal DTOs ───────────────────────────────────────────────────────
public class CustomerTicketDto
{
    public Guid Id { get; set; }
    public string TicketNumber { get; set; } = string.Empty;
    public string Subject { get; set; } = string.Empty;
    public string? CategoryName { get; set; }
    public TicketStatus Status { get; set; }
    public TicketPriority Priority { get; set; }
    public int ReplyCount { get; set; }
    public DateTime? LastReplyAt { get; set; }
    public DateTime CreatedAt { get; set; }
    public DateTime UpdatedAt { get; set; }
}

public class CustomerTicketDetailDto : CustomerTicketDto
{
    public string? Description { get; set; }
    public List<CustomerReplyViewDto> Replies { get; set; } = new();
    public bool CsatPending { get; set; }
    public int? CsatScore { get; set; }
}

public class CustomerReplyViewDto
{
    public Guid Id { get; set; }
    public string Body { get; set; } = string.Empty;
    public string? BodyHtml { get; set; }
    public string AuthorName { get; set; } = string.Empty;
    public bool IsFromSupport { get; set; }   // true if author is an agent
    public DateTime CreatedAt { get; set; }
}

public class CustomerCreateTicketDto
{
    [Required, MaxLength(500)] public string Subject { get; set; } = string.Empty;
    public string? Description { get; set; }
    public Guid? CategoryId { get; set; }
    public TicketPriority? Priority { get; set; }
    public Guid? CustomerId { get; set; }     // only used by agents creating on behalf
}

public class CustomerReplyDto
{
    [Required] public string Body { get; set; } = string.Empty;
    public bool IsInternal { get; set; }
    public TicketStatus? ChangeStatusTo { get; set; }
}

public class CsatSubmitDto
{
    [Range(1,5)] public int Score { get; set; }
    public string? Comment { get; set; }
}

// ── Program.cs additions ──────────────────────────────────────────────
// Add after existing service registrations:
// services.AddDbSet<WorkspaceAuthConfig>();
// services.AddDbSet<Auth0CustomerMap>();
// services.AddDbSet<Auth0AgentMap>();
// services.AddScoped<IAuth0Service, Auth0Service>();
// app.UseMiddleware<Auth0PortalMiddleware>(); // before UseAuthorization