-- Groups schema. Server stores group name + member list plaintext (metadata),
-- but message content is E2E encrypted fan-out (one blob per member).

CREATE TABLE IF NOT EXISTS chat_groups (
    id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    group_uid     CHAR(36) UNIQUE NOT NULL,        -- UUID for cross-device reference
    name          VARCHAR(120) NOT NULL,
    creator_id    BIGINT UNSIGNED NOT NULL,
    created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (creator_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS group_members (
    group_id      BIGINT UNSIGNED NOT NULL,
    user_id       BIGINT UNSIGNED NOT NULL,
    is_admin      TINYINT(1) DEFAULT 0,
    joined_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (group_id, user_id),
    INDEX idx_user (user_id),
    FOREIGN KEY (group_id) REFERENCES chat_groups(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id)  REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Extend messages table to support group source.
-- (No schema change needed — recipient_id still points to a specific user.
-- We add group_uid to link fan-out blobs to a logical group message.)
ALTER TABLE messages
    ADD COLUMN group_uid CHAR(36) DEFAULT NULL AFTER msg_type,
    ADD INDEX idx_group_uid (group_uid);