-- Migration: Add Route-Based System
-- Purpose: Allow same bus to serve different sets of students on different routes
-- Date: 2026-02-02

-- Step 1: Create routes table
CREATE TABLE IF NOT EXISTS `routes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `bus_id` int(11) NOT NULL,
  `route_name` varchar(100) NOT NULL,
  `shift` enum('morning','afternoon','evening') NOT NULL,
  `start_time` time DEFAULT NULL,
  `end_time` time DEFAULT NULL,
  `active` tinyint(1) DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `bus_id` (`bus_id`),
  KEY `idx_active` (`active`),
  FOREIGN KEY (`bus_id`) REFERENCES `buses` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Step 2: Add route_id to students table
ALTER TABLE `students` 
ADD COLUMN IF NOT EXISTS `route_id` int(11) DEFAULT NULL AFTER `bus_id`,
ADD KEY IF NOT EXISTS `idx_route_id` (`route_id`);

-- Add foreign key constraint (only if not exists)
SET @constraint_exists = (SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS 
    WHERE CONSTRAINT_SCHEMA = DATABASE() 
    AND TABLE_NAME = 'students' 
    AND CONSTRAINT_NAME = 'fk_students_route');

SET @sql = IF(@constraint_exists = 0, 
    'ALTER TABLE `students` ADD CONSTRAINT `fk_students_route` FOREIGN KEY (`route_id`) REFERENCES `routes` (`id`) ON DELETE SET NULL', 
    'SELECT "Constraint fk_students_route already exists" as message');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Step 3: Add active_route_id to drivers table for session management
ALTER TABLE `drivers` 
ADD COLUMN IF NOT EXISTS `active_route_id` int(11) DEFAULT NULL AFTER `bus_id`,
ADD KEY IF NOT EXISTS `idx_active_route` (`active_route_id`);

-- Add foreign key constraint (only if not exists)
SET @constraint_exists = (SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS 
    WHERE CONSTRAINT_SCHEMA = DATABASE() 
    AND TABLE_NAME = 'drivers' 
    AND CONSTRAINT_NAME = 'fk_drivers_active_route');

SET @sql = IF(@constraint_exists = 0, 
    'ALTER TABLE `drivers` ADD CONSTRAINT `fk_drivers_active_route` FOREIGN KEY (`active_route_id`) REFERENCES `routes` (`id`) ON DELETE SET NULL', 
    'SELECT "Constraint fk_drivers_active_route already exists" as message');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Step 4: Create default routes for existing buses (migration for existing data)
INSERT INTO `routes` (`bus_id`, `route_name`, `shift`, `start_time`, `end_time`)
SELECT 
    id, 
    CONCAT('Default Route - Bus ', bus_number), 
    'morning',
    '07:00:00',
    '09:00:00'
FROM `buses`
WHERE NOT EXISTS (
    SELECT 1 FROM `routes` WHERE `routes`.`bus_id` = `buses`.`id`
);

-- Step 5: Assign existing students to default routes
UPDATE `students` s
JOIN `routes` r ON s.bus_id = r.bus_id
SET s.route_id = r.id
WHERE s.route_id IS NULL
AND r.shift = 'morning'
LIMIT 1000;

-- Verification queries (commented out - uncomment to test)
-- SELECT * FROM routes;
-- SELECT id, name, bus_id, route_id FROM students;
-- SELECT id, name, bus_id, active_route_id FROM drivers;