-- NCCM 2026 Conference Management System
-- Database Schema for MySQL 8.x / 9.x / MariaDB

CREATE DATABASE IF NOT EXISTS `nccm2026` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `nccm2026`;

-- 1. Users / Delegates Table
CREATE TABLE IF NOT EXISTS `users` (
    `id` VARCHAR(32) NOT NULL,
    `name` VARCHAR(150) NOT NULL,
    `pin` VARCHAR(10) NOT NULL,
    `role` ENUM('delegate', 'staff', 'admin') NOT NULL DEFAULT 'delegate',
    `designation` VARCHAR(150) DEFAULT NULL,
    `organization` VARCHAR(150) DEFAULT NULL,
    `category` VARCHAR(100) DEFAULT 'Delegate',
    `email` VARCHAR(150) DEFAULT NULL,
    `phone` VARCHAR(50) DEFAULT NULL,
    `registered_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_pin` (`pin`),
    INDEX `idx_role` (`role`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. Conference Agenda & Technical Sessions
CREATE TABLE IF NOT EXISTS `sessions` (
    `id` VARCHAR(32) NOT NULL,
    `day` VARCHAR(20) NOT NULL,
    `time` VARCHAR(50) NOT NULL,
    `title` VARCHAR(255) NOT NULL,
    `speaker` VARCHAR(150) NOT NULL,
    `speaker_designation` VARCHAR(255) DEFAULT NULL,
    `track` VARCHAR(100) NOT NULL,
    `hall` VARCHAR(100) NOT NULL,
    `status` ENUM('upcoming', 'ongoing', 'completed', 'delayed') NOT NULL DEFAULT 'upcoming',
    `delay_minutes` INT NOT NULL DEFAULT 0,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_day` (`day`),
    INDEX `idx_track` (`track`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. Transit & Fleet Shuttles
CREATE TABLE IF NOT EXISTS `shuttles` (
    `id` VARCHAR(32) NOT NULL,
    `route_name` VARCHAR(150) NOT NULL,
    `from_location` VARCHAR(150) NOT NULL,
    `to_location` VARCHAR(150) NOT NULL,
    `next_departure` VARCHAR(50) NOT NULL,
    `frequency` VARCHAR(50) NOT NULL,
    `vehicle_no` VARCHAR(50) NOT NULL,
    `driver_name` VARCHAR(100) NOT NULL,
    `driver_phone` VARCHAR(50) NOT NULL,
    `status` VARCHAR(50) NOT NULL DEFAULT 'On Schedule',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. Hospitality & Dining Meals
CREATE TABLE IF NOT EXISTS `meals` (
    `id` VARCHAR(32) NOT NULL,
    `title` VARCHAR(150) NOT NULL,
    `meal_type` VARCHAR(50) NOT NULL,
    `venue` VARCHAR(150) NOT NULL,
    `time_window` VARCHAR(50) NOT NULL,
    `menu` TEXT DEFAULT NULL,
    `status` ENUM('upcoming', 'now_serving', 'concluded') NOT NULL DEFAULT 'upcoming',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5. Campus Wayfinding & Venues
CREATE TABLE IF NOT EXISTS `venues` (
    `id` VARCHAR(32) NOT NULL,
    `name` VARCHAR(150) NOT NULL,
    `floor` VARCHAR(50) NOT NULL,
    `zone` VARCHAR(50) NOT NULL,
    `capacity` INT NOT NULL DEFAULT 100,
    `av_facilities` VARCHAR(255) DEFAULT NULL,
    `description` TEXT DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_floor` (`floor`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 6. Attendee Check-ins & Kit Tracking
CREATE TABLE IF NOT EXISTS `checkins` (
    `id` INT AUTO_INCREMENT NOT NULL,
    `delegate_id` VARCHAR(32) NOT NULL,
    `scanned_by_staff_id` VARCHAR(32) DEFAULT NULL,
    `gate` VARCHAR(50) DEFAULT 'Main Gate - North',
    `kit_collected` TINYINT(1) NOT NULL DEFAULT 1,
    `scanned_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_delegate` (`delegate_id`),
    INDEX `idx_scanned_at` (`scanned_at`),
    CONSTRAINT `fk_checkin_delegate` FOREIGN KEY (`delegate_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 7. Broadcast Announcements & Push Alerts
CREATE TABLE IF NOT EXISTS `broadcasts` (
    `id` INT AUTO_INCREMENT NOT NULL,
    `title` VARCHAR(255) NOT NULL,
    `message` TEXT NOT NULL,
    `category` ENUM('emergency', 'announcement', 'schedule') NOT NULL DEFAULT 'announcement',
    `priority` ENUM('high', 'normal') NOT NULL DEFAULT 'normal',
    `audience` VARCHAR(100) NOT NULL DEFAULT 'All Attendees',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_category` (`category`),
    INDEX `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;