CREATE TABLE IF NOT EXISTS active_recording_plans (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  batch_id INT UNSIGNED DEFAULT NULL,
  teacher_id INT UNSIGNED DEFAULT NULL,
  subject_name VARCHAR(150) NOT NULL,
  plan_title VARCHAR(200) NOT NULL,
  cameraman_id INT UNSIGNED NOT NULL,
  camera_id INT UNSIGNED NOT NULL,
  room_id INT UNSIGNED NOT NULL,
  start_date DATE NOT NULL,
  planned_days VARCHAR(100) DEFAULT NULL,
  default_start_time TIME NOT NULL,
  default_end_time TIME NOT NULL,
  status ENUM('Active','Ended','Paused') NOT NULL DEFAULT 'Active',
  ended_at DATETIME DEFAULT NULL,
  ended_by INT UNSIGNED DEFAULT NULL,
  end_reason TEXT DEFAULT NULL,
  remarks TEXT DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_active_plans_batch_id (batch_id),
  KEY idx_active_plans_teacher_id (teacher_id),
  KEY idx_active_plans_cameraman_id (cameraman_id),
  KEY idx_active_plans_camera_id (camera_id),
  KEY idx_active_plans_room_id (room_id),
  KEY idx_active_plans_status_start (status, start_date),
  CONSTRAINT fk_active_plans_batch
    FOREIGN KEY (batch_id) REFERENCES batches(id)
    ON UPDATE CASCADE
    ON DELETE SET NULL,
  CONSTRAINT fk_active_plans_teacher
    FOREIGN KEY (teacher_id) REFERENCES teachers(id)
    ON UPDATE CASCADE
    ON DELETE SET NULL,
  CONSTRAINT fk_active_plans_cameraman
    FOREIGN KEY (cameraman_id) REFERENCES cameramen(id)
    ON UPDATE CASCADE
    ON DELETE RESTRICT,
  CONSTRAINT fk_active_plans_camera
    FOREIGN KEY (camera_id) REFERENCES cameras(id)
    ON UPDATE CASCADE
    ON DELETE RESTRICT,
  CONSTRAINT fk_active_plans_room
    FOREIGN KEY (room_id) REFERENCES rooms(id)
    ON UPDATE CASCADE
    ON DELETE RESTRICT,
  CONSTRAINT fk_active_plans_ended_by
    FOREIGN KEY (ended_by) REFERENCES users(id)
    ON UPDATE CASCADE
    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS daily_recording_instances (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  plan_id INT UNSIGNED DEFAULT NULL,
  schedule_id INT UNSIGNED DEFAULT NULL,
  recording_date DATE NOT NULL,
  class_name VARCHAR(200) NOT NULL,
  subject_name VARCHAR(150) DEFAULT NULL,
  teacher_id INT UNSIGNED DEFAULT NULL,
  batch_id INT UNSIGNED DEFAULT NULL,
  cameraman_id INT UNSIGNED NOT NULL,
  camera_id INT UNSIGNED NOT NULL,
  room_id INT UNSIGNED NOT NULL,
  start_time TIME NOT NULL,
  end_time TIME NOT NULL,
  recording_type ENUM('Regular','Extra','Revision','Mock','Sunday','Replacement','Emergency','Unscheduled') NOT NULL DEFAULT 'Regular',
  status ENUM('Scheduled','Started','Completed','Cancelled','Issue Reported') NOT NULL DEFAULT 'Scheduled',
  started_at DATETIME DEFAULT NULL,
  completed_at DATETIME DEFAULT NULL,
  cancelled_at DATETIME DEFAULT NULL,
  cancellation_reason TEXT DEFAULT NULL,
  issue_report TEXT DEFAULT NULL,
  completed_by INT UNSIGNED DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_daily_instance_plan_date (plan_id, recording_date),
  KEY idx_daily_instances_schedule_id (schedule_id),
  KEY idx_daily_instances_date_status (recording_date, status),
  KEY idx_daily_instances_cameraman_date (cameraman_id, recording_date),
  KEY idx_daily_instances_camera_date (camera_id, recording_date),
  KEY idx_daily_instances_room_date (room_id, recording_date),
  KEY idx_daily_instances_teacher_id (teacher_id),
  KEY idx_daily_instances_batch_id (batch_id),
  CONSTRAINT fk_daily_instances_plan
    FOREIGN KEY (plan_id) REFERENCES active_recording_plans(id)
    ON UPDATE CASCADE
    ON DELETE RESTRICT,
  CONSTRAINT fk_daily_instances_schedule
    FOREIGN KEY (schedule_id) REFERENCES schedule(id)
    ON UPDATE CASCADE
    ON DELETE SET NULL,
  CONSTRAINT fk_daily_instances_teacher
    FOREIGN KEY (teacher_id) REFERENCES teachers(id)
    ON UPDATE CASCADE
    ON DELETE SET NULL,
  CONSTRAINT fk_daily_instances_batch
    FOREIGN KEY (batch_id) REFERENCES batches(id)
    ON UPDATE CASCADE
    ON DELETE SET NULL,
  CONSTRAINT fk_daily_instances_cameraman
    FOREIGN KEY (cameraman_id) REFERENCES cameramen(id)
    ON UPDATE CASCADE
    ON DELETE RESTRICT,
  CONSTRAINT fk_daily_instances_camera
    FOREIGN KEY (camera_id) REFERENCES cameras(id)
    ON UPDATE CASCADE
    ON DELETE RESTRICT,
  CONSTRAINT fk_daily_instances_room
    FOREIGN KEY (room_id) REFERENCES rooms(id)
    ON UPDATE CASCADE
    ON DELETE RESTRICT,
  CONSTRAINT fk_daily_instances_completed_by
    FOREIGN KEY (completed_by) REFERENCES cameramen(id)
    ON UPDATE CASCADE
    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;