-- Qudurat AI - M11 Security + Audit + Performance
-- Run AFTER M1..M10
USE qudurat_ai;

CREATE TABLE audit_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NULL,
  action VARCHAR(120) NOT NULL,
  entity_type VARCHAR(190) NULL,
  entity_id VARCHAR(100) NULL,
  ip_address VARCHAR(64) NULL,
  user_agent TEXT NULL,
  request_id VARCHAR(80) NULL,
  before_json JSON NULL,
  after_json JSON NULL,
  metadata_json JSON NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_audit_action (action),
  INDEX idx_audit_entity_type (entity_type),
  INDEX idx_audit_request_id (request_id),
  INDEX idx_audit_entity (entity_type, entity_id),
  INDEX idx_audit_user_date (user_id, created_at),
  CONSTRAINT fk_audit_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE idempotency_keys (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NULL,
  `key` VARCHAR(120) NOT NULL,
  route VARCHAR(190) NOT NULL,
  request_hash VARCHAR(64) NOT NULL,
  status_code SMALLINT UNSIGNED NULL,
  response_json JSON NULL,
  expires_at TIMESTAMP NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_user_idempotency_key (user_id, `key`),
  INDEX idx_idempotency_route_expiry (route, expires_at),
  CONSTRAINT fk_idempotency_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE INDEX idx_attempt_student_question_date
ON student_question_attempts(student_profile_id, question_id, answered_at);

CREATE INDEX idx_questions_adaptive_hot
ON questions(status, skill_id, difficulty, published_at);

CREATE INDEX idx_test_session_student_submit
ON test_sessions(student_profile_id, submitted_at);

CREATE INDEX idx_daily_activity_student_date
ON student_daily_activity(student_profile_id, activity_date, questions_answered);