-- Phase 3 of the CRM upgrade program: shared-inbox collaboration on top of the existing Mail
-- module. Both tables key off fact_mail_message.id, never off dim_contacts/fact_leads, so this
-- migration carries zero risk to Contact data — it's purely additive UX on messages that already
-- exist.
-- Idempotent (information_schema-guarded), additive only.
-- Deploy: mysql -u root -p db_abos_v0.1 < db/mail_collaboration.sql

USE db_abos_v0.1;

-- A short-lived "someone is drafting a reply" marker. One row per (message, user) at a time —
-- the app upserts on acquire and deletes on release/expiry rather than leaving stale rows around.
CREATE TABLE IF NOT EXISTS fact_mail_message_locks (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    mail_message_id BIGINT NOT NULL,
    locked_by_user_id INT NOT NULL,
    locked_by_label VARCHAR(255) NULL COMMENT 'Display name/email snapshot, so the UI has something to show without a join',
    expires_at TIMESTAMP(6) NOT NULL COMMENT 'Locks are advisory and self-expiring — no cleanup job required',
    created_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    UNIQUE KEY uq_mail_message_locks_message (mail_message_id),
    INDEX idx_mail_message_locks_tenant (tenant_id, expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Team-only notes threaded on a message — never sent, never visible to the external recipient.
CREATE TABLE IF NOT EXISTS fact_mail_internal_comments (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    mail_message_id BIGINT NOT NULL,
    author_user_id INT NOT NULL,
    author_label VARCHAR(255) NULL,
    body TEXT NOT NULL,
    created_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    is_deleted TINYINT(1) NOT NULL DEFAULT 0,
    INDEX idx_mail_internal_comments_message (tenant_id, mail_message_id, is_deleted)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;