-- Qudurat AI - M8 Parent + Attendance + Email Reports
-- Run AFTER M1..M7
USE qudurat_ai;

CREATE TABLE student_daily_activity (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  student_profile_id BIGINT UNSIGNED NOT NULL,
  activity_date DATE NOT NULL,
  active_minutes INT UNSIGNED NOT NULL DEFAULT 0,
  questions_answered INT UNSIGNED NOT NULL DEFAULT 0,
  tests_started SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  tests_completed SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  training_sessions_started SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  training_sessions_completed SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  ai_tutor_messages INT UNSIGNED NOT NULL DEFAULT 0,
  last_activity_at TIMESTAMP NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_student_daily_activity (student_profile_id, activity_date),
  INDEX idx_activity_date_minutes (activity_date, active_minutes),
  CONSTRAINT fk_daily_activity_student FOREIGN KEY (student_profile_id) REFERENCES student_profiles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE student_attendance (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  student_profile_id BIGINT UNSIGNED NOT NULL,
  attendance_date DATE NOT NULL,
  planned_study_day TINYINT(1) NOT NULL DEFAULT 0,
  status ENUM('present','partial','absent','rest_day') NOT NULL,
  activity_minutes INT UNSIGNED NOT NULL DEFAULT 0,
  questions_answered INT UNSIGNED NOT NULL DEFAULT 0,
  reason_code VARCHAR(100) NULL,
  evaluated_at TIMESTAMP NOT NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_student_attendance (student_profile_id, attendance_date),
  INDEX idx_attendance_status (status),
  CONSTRAINT fk_attendance_student FOREIGN KEY (student_profile_id) REFERENCES student_profiles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE parent_notification_preferences (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  parent_profile_id BIGINT UNSIGNED NOT NULL,
  student_profile_id BIGINT UNSIGNED NOT NULL,
  daily_report_enabled TINYINT(1) NOT NULL DEFAULT 0,
  weekly_report_enabled TINYINT(1) NOT NULL DEFAULT 1,
  absence_alert_enabled TINYINT(1) NOT NULL DEFAULT 1,
  test_result_enabled TINYINT(1) NOT NULL DEFAULT 1,
  performance_drop_enabled TINYINT(1) NOT NULL DEFAULT 0,
  daily_report_time TIME NOT NULL DEFAULT '20:00:00',
  weekly_report_day TINYINT UNSIGNED NOT NULL DEFAULT 6,
  weekly_report_time TIME NOT NULL DEFAULT '20:00:00',
  timezone VARCHAR(64) NOT NULL DEFAULT 'Asia/Riyadh',
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_parent_student_notification_pref (parent_profile_id, student_profile_id),
  CONSTRAINT fk_parent_pref_parent FOREIGN KEY (parent_profile_id) REFERENCES parent_profiles(id) ON DELETE CASCADE,
  CONSTRAINT fk_parent_pref_student FOREIGN KEY (student_profile_id) REFERENCES student_profiles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE parent_reports (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  parent_profile_id BIGINT UNSIGNED NOT NULL,
  student_profile_id BIGINT UNSIGNED NOT NULL,
  report_type ENUM('daily','weekly','absence','test_result') NOT NULL,
  period_start DATE NOT NULL,
  period_end DATE NOT NULL,
  summary_json JSON NOT NULL,
  status ENUM('generated','queued','sent','failed') NOT NULL DEFAULT 'generated',
  generated_at TIMESTAMP NOT NULL,
  sent_at TIMESTAMP NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_parent_report_period (student_profile_id, period_start, period_end),
  INDEX idx_parent_report_type (report_type),
  CONSTRAINT fk_parent_reports_parent FOREIGN KEY (parent_profile_id) REFERENCES parent_profiles(id) ON DELETE CASCADE,
  CONSTRAINT fk_parent_reports_student FOREIGN KEY (student_profile_id) REFERENCES student_profiles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE notification_events (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NULL,
  student_profile_id BIGINT UNSIGNED NULL,
  event_type VARCHAR(100) NOT NULL,
  channel ENUM('email','in_app') NOT NULL DEFAULT 'email',
  payload_json JSON NULL,
  status ENUM('queued','processing','sent','failed','cancelled') NOT NULL DEFAULT 'queued',
  scheduled_for TIMESTAMP NULL,
  processed_at TIMESTAMP NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_notification_event_type (event_type),
  INDEX idx_notification_status (status),
  INDEX idx_notification_schedule (event_type, scheduled_for),
  CONSTRAINT fk_notification_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_notification_student FOREIGN KEY (student_profile_id) REFERENCES student_profiles(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE email_delivery_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  notification_event_id BIGINT UNSIGNED NULL,
  parent_report_id BIGINT UNSIGNED NULL,
  recipient_email VARCHAR(190) NOT NULL,
  subject VARCHAR(255) NOT NULL,
  provider_message_id VARCHAR(255) NULL,
  status ENUM('queued','sent','delivered','failed') NOT NULL DEFAULT 'queued',
  error_message TEXT NULL,
  queued_at TIMESTAMP NULL,
  sent_at TIMESTAMP NULL,
  delivered_at TIMESTAMP NULL,
  failed_at TIMESTAMP NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_email_status (status),
  INDEX idx_email_recipient_date (recipient_email, created_at),
  CONSTRAINT fk_email_notification_event FOREIGN KEY (notification_event_id) REFERENCES notification_events(id) ON DELETE SET NULL,
  CONSTRAINT fk_email_parent_report FOREIGN KEY (parent_report_id) REFERENCES parent_reports(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;