-- Support chat schema + legacy migration from issues/replies

CREATE TABLE IF NOT EXISTS support_tickets (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ticket_ref VARCHAR(32) NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    subject VARCHAR(255) NOT NULL,
    status ENUM('open', 'pending', 'resolved', 'closed') NOT NULL DEFAULT 'open',
    priority ENUM('low', 'normal', 'high', 'urgent') NOT NULL DEFAULT 'normal',
    last_message_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    closed_at DATETIME NULL,
    UNIQUE KEY uq_support_tickets_ticket_ref (ticket_ref),
    KEY idx_support_tickets_user_status_last (user_id, status, last_message_at),
    KEY idx_support_tickets_last_message_at (last_message_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS support_messages (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ticket_id BIGINT UNSIGNED NOT NULL,
    sender_type ENUM('user', 'admin', 'system') NOT NULL,
    sender_id BIGINT UNSIGNED NULL,
    body TEXT NULL,
    message_type ENUM('text', 'image', 'file', 'system') NOT NULL DEFAULT 'text',
    client_msg_id VARCHAR(64) NULL,
    delivered_at DATETIME NULL,
    read_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_support_messages_client_msg (ticket_id, client_msg_id),
    KEY idx_support_messages_ticket_created (ticket_id, created_at),
    KEY idx_support_messages_ticket_id (ticket_id, id),
    CONSTRAINT fk_support_messages_ticket FOREIGN KEY (ticket_id)
        REFERENCES support_tickets(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS support_attachments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    message_id BIGINT UNSIGNED NOT NULL,
    original_name VARCHAR(255) NOT NULL,
    stored_name VARCHAR(255) NOT NULL,
    mime_type VARCHAR(128) NOT NULL,
    size_bytes BIGINT UNSIGNED NOT NULL DEFAULT 0,
    url_path VARCHAR(1024) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_support_attachments_message (message_id),
    CONSTRAINT fk_support_attachments_message FOREIGN KEY (message_id)
        REFERENCES support_messages(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS support_ticket_reads (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ticket_id BIGINT UNSIGNED NOT NULL,
    reader_type ENUM('user', 'admin') NOT NULL,
    reader_id BIGINT UNSIGNED NOT NULL,
    last_read_message_id BIGINT UNSIGNED NULL,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_support_ticket_reads_unique (ticket_id, reader_type, reader_id),
    KEY idx_support_ticket_reads_lookup (ticket_id, reader_type, reader_id),
    CONSTRAINT fk_support_ticket_reads_ticket FOREIGN KEY (ticket_id)
        REFERENCES support_tickets(id) ON DELETE CASCADE,
    CONSTRAINT fk_support_ticket_reads_message FOREIGN KEY (last_read_message_id)
        REFERENCES support_messages(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS support_ws_tokens (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    jti VARCHAR(64) NOT NULL,
    subject_type ENUM('user', 'admin') NOT NULL,
    subject_id BIGINT UNSIGNED NOT NULL,
    expires_at DATETIME NOT NULL,
    revoked_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_support_ws_tokens_jti (jti),
    KEY idx_support_ws_tokens_subject (subject_type, subject_id, expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS support_migration_map (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    legacy_issue_id BIGINT UNSIGNED NOT NULL,
    support_ticket_id BIGINT UNSIGNED NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_support_migration_issue (legacy_issue_id),
    KEY idx_support_migration_ticket (support_ticket_id),
    CONSTRAINT fk_support_migration_ticket FOREIGN KEY (support_ticket_id)
        REFERENCES support_tickets(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Legacy migration: issues -> support_tickets
INSERT INTO support_tickets (ticket_ref, user_id, subject, status, priority, last_message_at, created_at, updated_at)
SELECT
    CONCAT('LEG-', i.id) AS ticket_ref,
    i.sId AS user_id,
    COALESCE(NULLIF(i.ref, ''), 'Support Ticket') AS subject,
    CASE
        WHEN IFNULL(i.user_read, 0) = 0 OR IFNULL(i.admin_read, 0) = 0 THEN 'open'
        ELSE 'pending'
    END AS status,
    'normal' AS priority,
    COALESCE(MAX(r.reply_date), i.add_date, NOW()) AS last_message_at,
    COALESCE(i.add_date, NOW()) AS created_at,
    COALESCE(i.reply_date, i.add_date, NOW()) AS updated_at
FROM issues i
LEFT JOIN replies r ON r.issue_id = i.id
LEFT JOIN support_migration_map sm ON sm.legacy_issue_id = i.id
WHERE sm.id IS NULL
GROUP BY i.id;

INSERT INTO support_migration_map (legacy_issue_id, support_ticket_id)
SELECT i.id, st.id
FROM issues i
INNER JOIN support_tickets st ON st.ticket_ref = CONCAT('LEG-', i.id)
LEFT JOIN support_migration_map sm ON sm.legacy_issue_id = i.id
WHERE sm.id IS NULL;

-- Legacy migration: replies -> support_messages
INSERT INTO support_messages (ticket_id, sender_type, sender_id, body, message_type, created_at, delivered_at, read_at)
SELECT
    sm.support_ticket_id,
    CASE
        WHEN LOWER(TRIM(r.replyby)) = 'user' THEN 'user'
        WHEN LOWER(TRIM(r.replyby)) = 'admin' THEN 'admin'
        ELSE 'system'
    END AS sender_type,
    NULL AS sender_id,
    r.reply AS body,
    'text' AS message_type,
    COALESCE(r.reply_date, NOW()) AS created_at,
    COALESCE(r.reply_date, NOW()) AS delivered_at,
    NULL AS read_at
FROM replies r
INNER JOIN support_migration_map sm ON sm.legacy_issue_id = r.issue_id
LEFT JOIN support_messages m
    ON m.ticket_id = sm.support_ticket_id
   AND m.body = r.reply
   AND m.created_at = COALESCE(r.reply_date, NOW())
WHERE m.id IS NULL;

-- Keep ticket last_message_at in sync after migration
UPDATE support_tickets t
INNER JOIN (
    SELECT ticket_id, MAX(created_at) AS max_created
    FROM support_messages
    GROUP BY ticket_id
) x ON x.ticket_id = t.id
SET t.last_message_at = x.max_created,
    t.updated_at = GREATEST(t.updated_at, x.max_created);
