-- Signal-Learn database schema
-- Generated for MySQL/MariaDB import on cPanel.
-- This file contains schema only. No users, credentials, or local test data.

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE IF NOT EXISTS `users` (
  `id` char(36) NOT NULL,
  `google_id` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `profile_picture_url` varchar(500) DEFAULT NULL,
  `role` enum('teacher','admin') NOT NULL DEFAULT 'teacher',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `is_active` boolean NOT NULL DEFAULT TRUE,
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_google_id_unique` (`google_id`),
  UNIQUE KEY `users_email_unique` (`email`),
  KEY `idx_users_role` (`role`),
  KEY `idx_users_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `courses` (
  `id` char(36) NOT NULL,
  `instructor_id` char(36) NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` text,
  `date` date NOT NULL,
  `start_time` time NOT NULL,
  `duration_minutes` int NOT NULL,
  `session_code` varchar(20) NOT NULL,
  `allow_anonymous` boolean NOT NULL DEFAULT FALSE,
  `status` enum('draft','active','completed') NOT NULL DEFAULT 'draft',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `version` int NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`),
  UNIQUE KEY `courses_session_code_unique` (`session_code`),
  KEY `idx_courses_instructor_id` (`instructor_id`),
  KEY `idx_courses_status` (`status`),
  KEY `idx_courses_date` (`date`),
  KEY `idx_courses_deleted_at` (`deleted_at`),
  CONSTRAINT `courses_instructor_id_users_id_fk` FOREIGN KEY (`instructor_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `course_sessions` (
  `id` char(36) NOT NULL,
  `course_id` char(36) NOT NULL,
  `session_start_time` datetime NOT NULL,
  `session_end_time` datetime DEFAULT NULL,
  `is_active` boolean NOT NULL DEFAULT TRUE,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_course_sessions_course_id` (`course_id`),
  KEY `idx_course_sessions_is_active` (`is_active`),
  KEY `idx_course_sessions_start_time` (`session_start_time`),
  CONSTRAINT `course_sessions_course_id_courses_id_fk` FOREIGN KEY (`course_id`) REFERENCES `courses` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `join_codes` (
  `id` char(36) NOT NULL,
  `session_id` char(36) NOT NULL,
  `code` varchar(50) NOT NULL,
  `is_custom` boolean NOT NULL DEFAULT FALSE,
  `status` enum('active','revoked') NOT NULL DEFAULT 'active',
  `generated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `revoked_at` timestamp NULL DEFAULT NULL,
  `created_by` char(36) NOT NULL,
  `usage_count` int NOT NULL DEFAULT 0,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_join_codes_unique` (`session_id`, `code`),
  KEY `idx_join_codes_session_id` (`session_id`),
  KEY `idx_join_codes_status` (`status`),
  KEY `idx_join_codes_created_by` (`created_by`),
  CONSTRAINT `join_codes_session_id_course_sessions_id_fk` FOREIGN KEY (`session_id`) REFERENCES `course_sessions` (`id`) ON DELETE CASCADE,
  CONSTRAINT `join_codes_created_by_users_id_fk` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `participants` (
  `id` char(36) NOT NULL,
  `session_id` char(36) NOT NULL,
  `join_code_id` char(36) DEFAULT NULL,
  `join_method` enum('code','link') NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `join_timestamp` datetime NOT NULL,
  `leave_timestamp` datetime DEFAULT NULL,
  `is_active` boolean NOT NULL DEFAULT TRUE,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_participants_session_id` (`session_id`),
  KEY `idx_participants_join_code_id` (`join_code_id`),
  KEY `idx_participants_join_method` (`join_method`),
  KEY `idx_participants_is_active` (`is_active`),
  CONSTRAINT `participants_session_id_course_sessions_id_fk` FOREIGN KEY (`session_id`) REFERENCES `course_sessions` (`id`) ON DELETE CASCADE,
  CONSTRAINT `participants_join_code_id_join_codes_id_fk` FOREIGN KEY (`join_code_id`) REFERENCES `join_codes` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `session_dashboard` (
  `id` char(36) NOT NULL,
  `session_id` char(36) NOT NULL,
  `red_count` int NOT NULL DEFAULT 0,
  `yellow_count` int NOT NULL DEFAULT 0,
  `green_count` int NOT NULL DEFAULT 0,
  `total_participants` int NOT NULL DEFAULT 0,
  `active_participants` int NOT NULL DEFAULT 0,
  `elapsed_seconds` int NOT NULL DEFAULT 0,
  `last_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `version` int NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`),
  UNIQUE KEY `session_dashboard_session_id_unique` (`session_id`),
  KEY `idx_session_dashboard_last_updated` (`last_updated`),
  CONSTRAINT `session_dashboard_session_id_course_sessions_id_fk` FOREIGN KEY (`session_id`) REFERENCES `course_sessions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `session_summaries` (
  `id` char(36) NOT NULL,
  `session_id` char(36) NOT NULL,
  `total_participants` int NOT NULL,
  `participants_via_link` int NOT NULL DEFAULT 0,
  `participants_via_code` int NOT NULL DEFAULT 0,
  `codes_generated_count` int NOT NULL DEFAULT 0,
  `codes_revoked_count` int NOT NULL DEFAULT 0,
  `final_red_count` int NOT NULL DEFAULT 0,
  `final_yellow_count` int NOT NULL DEFAULT 0,
  `final_green_count` int NOT NULL DEFAULT 0,
  `duration_seconds` int NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `session_summaries_session_id_unique` (`session_id`),
  KEY `idx_session_summaries_created_at` (`created_at`),
  CONSTRAINT `session_summaries_session_id_course_sessions_id_fk` FOREIGN KEY (`session_id`) REFERENCES `course_sessions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `status_events` (
  `id` char(36) NOT NULL,
  `participant_id` char(36) NOT NULL,
  `status` enum('red','yellow','green') NOT NULL,
  `triggered_at` datetime NOT NULL,
  `auto_reset_at` datetime NOT NULL,
  `is_reset` boolean NOT NULL DEFAULT FALSE,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_status_events_participant_id` (`participant_id`),
  KEY `idx_status_events_status` (`status`),
  KEY `idx_status_events_triggered_at` (`triggered_at`),
  KEY `idx_status_events_is_reset` (`is_reset`),
  CONSTRAINT `status_events_participant_id_participants_id_fk` FOREIGN KEY (`participant_id`) REFERENCES `participants` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;