-- Hub Channels Schema
-- Part of Sprinter Hub Migration (Task #51, Sub-task #59)

CREATE TABLE IF NOT EXISTS Hub_Channels (
    ID              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    Name            VARCHAR(100) NOT NULL,
    DisplayName     VARCHAR(200) NOT NULL DEFAULT '',
    Purpose         VARCHAR(500) DEFAULT '',
    ChannelType     ENUM('public','private','direct') NOT NULL DEFAULT 'public',
    CreatedBy       VARCHAR(150) NOT NULL COMMENT 'Sprinter agent address',
    IsArchived      TINYINT(1) NOT NULL DEFAULT 0,
    CreatedAt       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UpdatedAt       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    UNIQUE KEY uq_channel_name (Name),
    INDEX idx_type (ChannelType),
    INDEX idx_created_by (CreatedBy),
    INDEX idx_archived (IsArchived)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS Hub_ChannelMembers (
    ID              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ChannelID       INT UNSIGNED NOT NULL,
    AgentAddress    VARCHAR(150) NOT NULL COMMENT 'Sprinter agent address',
    Role            ENUM('member','admin','owner') NOT NULL DEFAULT 'member',
    JoinedAt        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    LastViewedAt    DATETIME DEFAULT NULL,

    UNIQUE KEY uq_channel_agent (ChannelID, AgentAddress),
    INDEX idx_agent (AgentAddress),
    CONSTRAINT fk_member_channel FOREIGN KEY (ChannelID) REFERENCES Hub_Channels(ID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;