CREATE DATABASE IF NOT EXISTS media_recording_app
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE media_recording_app;

CREATE TABLE IF NOT EXISTS users (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  username VARCHAR(100) NOT NULL,
  password VARCHAR(255) NOT NULL,
  role ENUM('admin','viewer','cameraman') NOT NULL DEFAULT 'viewer',
  status ENUM('Active','Inactive') NOT NULL DEFAULT 'Active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_users_username (username),
  KEY idx_users_role (role),
  KEY idx_users_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS teachers (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(150) NOT NULL,
  email VARCHAR(150) DEFAULT NULL,
  department VARCHAR(150) DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_teachers_email (email),
  KEY idx_teachers_name (name),
  KEY idx_teachers_department (department)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS cameramen (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(150) NOT NULL,
  username VARCHAR(100) NOT NULL,
  password VARCHAR(255) NOT NULL,
  phone VARCHAR(50) DEFAULT NULL,
  availability ENUM('Available','Busy','Leave','Inactive') NOT NULL DEFAULT 'Available',
  status ENUM('Active','Inactive') NOT NULL DEFAULT 'Active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_cameramen_username (username),
  KEY idx_cameramen_availability (availability),
  KEY idx_cameramen_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS cameras (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  model VARCHAR(150) NOT NULL,
  status ENUM('Available','Assigned','Maintenance') NOT NULL DEFAULT 'Available',
  PRIMARY KEY (id),
  KEY idx_cameras_model (model),
  KEY idx_cameras_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS rooms (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  room_name VARCHAR(150) NOT NULL,
  capacity INT UNSIGNED DEFAULT NULL,
  type ENUM('Classroom','Studio','Lab','Hall','Other') NOT NULL DEFAULT 'Classroom',
  PRIMARY KEY (id),
  UNIQUE KEY uq_rooms_room_name (room_name),
  KEY idx_rooms_type (type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS batches (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(150) NOT NULL,
  course VARCHAR(150) NOT NULL,
  teacher_id INT UNSIGNED NOT NULL,
  start_date DATE NOT NULL,
  end_date DATE DEFAULT NULL,
  timing VARCHAR(100) DEFAULT NULL,
  days VARCHAR(150) DEFAULT NULL,
  mode VARCHAR(100) DEFAULT NULL,
  status ENUM('Active','Completed','Inactive') NOT NULL DEFAULT 'Active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_batches_teacher_id (teacher_id),
  KEY idx_batches_status (status),
  KEY idx_batches_dates (start_date, end_date),
  CONSTRAINT fk_batches_teacher
    FOREIGN KEY (teacher_id) REFERENCES teachers(id)
    ON UPDATE CASCADE
    ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS schedule (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  batch_id INT UNSIGNED DEFAULT NULL,
  class_name VARCHAR(200) NOT NULL,
  teacher_id INT UNSIGNED DEFAULT NULL,
  cameraman_id INT UNSIGNED NOT NULL,
  camera_id INT UNSIGNED NOT NULL,
  room_id INT UNSIGNED NOT NULL,
  recording_date DATE NOT NULL,
  start_time TIME NOT NULL,
  end_time TIME NOT NULL,
  suggested_start_date DATE DEFAULT NULL,
  suggested_end_date DATE DEFAULT NULL,
  actual_end_date DATE DEFAULT NULL,
  recording_type ENUM('Regular','Unscheduled','Occasional','Replacement','Emergency','Sunday Recording') NOT NULL DEFAULT 'Regular',
  status ENUM('Scheduled','Started','Completed','Cancelled','Issue Reported','Unscheduled') 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,
  remarks TEXT DEFAULT NULL,
  created_by_role VARCHAR(50) DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_schedule_batch_id (batch_id),
  KEY idx_schedule_teacher_id (teacher_id),
  KEY idx_schedule_cameraman_id (cameraman_id),
  KEY idx_schedule_camera_id (camera_id),
  KEY idx_schedule_room_id (room_id),
  KEY idx_schedule_status (status),
  KEY idx_schedule_recording_type (recording_type),
  KEY idx_schedule_date_time_status (recording_date, start_time, end_time, status),
  CONSTRAINT fk_schedule_batch
    FOREIGN KEY (batch_id) REFERENCES batches(id)
    ON UPDATE CASCADE
    ON DELETE SET NULL,
  CONSTRAINT fk_schedule_teacher
    FOREIGN KEY (teacher_id) REFERENCES teachers(id)
    ON UPDATE CASCADE
    ON DELETE SET NULL,
  CONSTRAINT fk_schedule_cameraman
    FOREIGN KEY (cameraman_id) REFERENCES cameramen(id)
    ON UPDATE CASCADE
    ON DELETE RESTRICT,
  CONSTRAINT fk_schedule_camera
    FOREIGN KEY (camera_id) REFERENCES cameras(id)
    ON UPDATE CASCADE
    ON DELETE RESTRICT,
  CONSTRAINT fk_schedule_room
    FOREIGN KEY (room_id) REFERENCES rooms(id)
    ON UPDATE CASCADE
    ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS cameraman_attendance (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  cameraman_id INT UNSIGNED NOT NULL,
  schedule_id INT UNSIGNED DEFAULT NULL,
  attendance_date DATE NOT NULL,
  check_in TIME DEFAULT NULL,
  check_out TIME DEFAULT NULL,
  status ENUM('Present','Late','Absent','Issue Reported') NOT NULL DEFAULT 'Present',
  issue_report TEXT DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_attendance_cameraman_date (cameraman_id, attendance_date),
  KEY idx_attendance_schedule_id (schedule_id),
  KEY idx_attendance_status (status),
  CONSTRAINT fk_attendance_cameraman
    FOREIGN KEY (cameraman_id) REFERENCES cameramen(id)
    ON UPDATE CASCADE
    ON DELETE CASCADE,
  CONSTRAINT fk_attendance_schedule
    FOREIGN KEY (schedule_id) REFERENCES schedule(id)
    ON UPDATE CASCADE
    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS schedule_actions (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  schedule_id INT UNSIGNED NOT NULL,
  cameraman_id INT UNSIGNED DEFAULT NULL,
  action_type ENUM('Scheduled','Started','Completed','Cancelled','Issue Reported','Marked Free','Unscheduled Created','Resource Changed') NOT NULL,
  remarks TEXT DEFAULT NULL,
  action_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_actions_schedule_id (schedule_id),
  KEY idx_actions_cameraman_id (cameraman_id),
  KEY idx_actions_action_type (action_type),
  KEY idx_actions_action_time (action_time),
  CONSTRAINT fk_actions_schedule
    FOREIGN KEY (schedule_id) REFERENCES schedule(id)
    ON UPDATE CASCADE
    ON DELETE CASCADE,
  CONSTRAINT fk_actions_cameraman
    FOREIGN KEY (cameraman_id) REFERENCES cameramen(id)
    ON UPDATE CASCADE
    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS teacher_leaves (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  teacher_id INT UNSIGNED NOT NULL,
  leave_date DATE NOT NULL,
  reason TEXT DEFAULT NULL,
  status ENUM('Approved','Pending','Cancelled') NOT NULL DEFAULT 'Pending',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_teacher_leaves_teacher_date (teacher_id, leave_date),
  KEY idx_teacher_leaves_status (status),
  CONSTRAINT fk_teacher_leaves_teacher
    FOREIGN KEY (teacher_id) REFERENCES teachers(id)
    ON UPDATE CASCADE
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS calendar_events (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  title VARCHAR(200) NOT NULL,
  event_type ENUM('Class Recording','Teacher Leave','Cancelled Class','Unscheduled Recording','Maintenance','Issue') NOT NULL,
  related_schedule_id INT UNSIGNED DEFAULT NULL,
  teacher_id INT UNSIGNED DEFAULT NULL,
  cameraman_id INT UNSIGNED DEFAULT NULL,
  camera_id INT UNSIGNED DEFAULT NULL,
  room_id INT UNSIGNED DEFAULT NULL,
  start_datetime DATETIME NOT NULL,
  end_datetime DATETIME NOT NULL,
  status ENUM('Scheduled','Started','Completed','Cancelled','Issue Reported') NOT NULL DEFAULT 'Scheduled',
  remarks TEXT DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_calendar_schedule_id (related_schedule_id),
  KEY idx_calendar_teacher_id (teacher_id),
  KEY idx_calendar_cameraman_id (cameraman_id),
  KEY idx_calendar_camera_id (camera_id),
  KEY idx_calendar_room_id (room_id),
  KEY idx_calendar_event_type (event_type),
  KEY idx_calendar_status (status),
  KEY idx_calendar_range (start_datetime, end_datetime),
  CONSTRAINT fk_calendar_schedule
    FOREIGN KEY (related_schedule_id) REFERENCES schedule(id)
    ON UPDATE CASCADE
    ON DELETE SET NULL,
  CONSTRAINT fk_calendar_teacher
    FOREIGN KEY (teacher_id) REFERENCES teachers(id)
    ON UPDATE CASCADE
    ON DELETE SET NULL,
  CONSTRAINT fk_calendar_cameraman
    FOREIGN KEY (cameraman_id) REFERENCES cameramen(id)
    ON UPDATE CASCADE
    ON DELETE SET NULL,
  CONSTRAINT fk_calendar_camera
    FOREIGN KEY (camera_id) REFERENCES cameras(id)
    ON UPDATE CASCADE
    ON DELETE SET NULL,
  CONSTRAINT fk_calendar_room
    FOREIGN KEY (room_id) REFERENCES rooms(id)
    ON UPDATE CASCADE
    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS issues (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  schedule_id INT UNSIGNED DEFAULT NULL,
  cameraman_id INT UNSIGNED DEFAULT NULL,
  issue_type ENUM('Camera Issue','Room Issue','Teacher Issue','Power Issue','Internet Issue','Class Cancelled','Other') NOT NULL DEFAULT 'Other',
  description TEXT NOT NULL,
  status ENUM('Open','Resolved','Ignored') NOT NULL DEFAULT 'Open',
  reported_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  resolved_at DATETIME DEFAULT NULL,
  PRIMARY KEY (id),
  KEY idx_issues_schedule_id (schedule_id),
  KEY idx_issues_cameraman_id (cameraman_id),
  KEY idx_issues_issue_type (issue_type),
  KEY idx_issues_status (status),
  KEY idx_issues_reported_at (reported_at),
  CONSTRAINT fk_issues_schedule
    FOREIGN KEY (schedule_id) REFERENCES schedule(id)
    ON UPDATE CASCADE
    ON DELETE SET NULL,
  CONSTRAINT fk_issues_cameraman
    FOREIGN KEY (cameraman_id) REFERENCES cameramen(id)
    ON UPDATE CASCADE
    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;