-- ============================================================
--  NurseSpark Live Classes Module — Database Schema
--  Run this on EACH tenant database
-- ============================================================

CREATE TABLE IF NOT EXISTS `live_classes` (
    `id`                INT AUTO_INCREMENT PRIMARY KEY,
    `institute_id`      INT NOT NULL,
    `title`             VARCHAR(255) NOT NULL,
    `description`       TEXT,
    `topic_tags`        VARCHAR(500) DEFAULT NULL COMMENT 'Comma-separated tags e.g. CAUTI,IV Therapy',
    `scheduled_at`      DATETIME NOT NULL,
    `duration_minutes`  INT NOT NULL DEFAULT 60,
    `room_name`         VARCHAR(120) NOT NULL UNIQUE COMMENT 'UUID-based private Jitsi room name',
    `created_by`        INT NOT NULL COMMENT 'Admin user ID',
    `max_participants`  INT DEFAULT 100,
    `status`            ENUM('scheduled','live','ended','cancelled') NOT NULL DEFAULT 'scheduled',
    `started_at`        DATETIME DEFAULT NULL,
    `ended_at`          DATETIME DEFAULT NULL,
    `recording_url`     VARCHAR(1000) DEFAULT NULL,
    `notes`             TEXT DEFAULT NULL COMMENT 'Post-class notes by instructor',
    `created_at`        TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at`        TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_institute` (`institute_id`),
    INDEX `idx_status`    (`status`),
    INDEX `idx_scheduled` (`scheduled_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
--  Attendance tracking
-- ============================================================
CREATE TABLE IF NOT EXISTS `live_class_attendance` (
    `id`            INT AUTO_INCREMENT PRIMARY KEY,
    `class_id`      INT NOT NULL,
    `user_id`       INT NOT NULL,
    `user_name`     VARCHAR(255) NOT NULL,
    `role`          ENUM('moderator','participant') NOT NULL DEFAULT 'participant',
    `joined_at`     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `left_at`       TIMESTAMP DEFAULT NULL,
    `duration_mins` INT DEFAULT NULL COMMENT 'Auto-calculated on leave',
    UNIQUE KEY `uq_class_user` (`class_id`, `user_id`),
    INDEX `idx_class`  (`class_id`),
    INDEX `idx_user`   (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
--  Chat messages during class (optional)
-- ============================================================
CREATE TABLE IF NOT EXISTS `live_class_messages` (
    `id`         INT AUTO_INCREMENT PRIMARY KEY,
    `class_id`   INT NOT NULL,
    `user_id`    INT NOT NULL,
    `user_name`  VARCHAR(255) NOT NULL,
    `message`    TEXT NOT NULL,
    `sent_at`    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_class_msgs` (`class_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
