<?php declare(strict_types=1);

function aa_bootstrap_table(mysqli $db): void {
  $db->query("CREATE TABLE IF NOT EXISTS lms_assessment_attachments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    assessment_id INT NOT NULL,
    file_path VARCHAR(255) NOT NULL,
    uploaded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX (assessment_id)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
}

function aa_save_attachment(mysqli $db, int $assessment_id, array $file): ?string {
  if (empty($file['name']) || !is_uploaded_file($file['tmp_name'])) return null;

  $root = realpath(__DIR__.'/../../..');               // -> /elms
  $dir  = $root.'/uploads/assessments';
  if (!is_dir($dir)) @mkdir($dir,0775,true);

  $ext  = strtolower(pathinfo($file['name'], PATHINFO_EXTENSION));
  $base = preg_replace('/[^A-Za-z0-9._-]+/','_', pathinfo($file['name'], PATHINFO_FILENAME));
  $name = date('Ymd_His')."_ass{$assessment_id}_{$base}".($ext?".$ext":'');
  $dest = rtrim($dir,'/\\').DIRECTORY_SEPARATOR.$name;

  if (!move_uploaded_file($file['tmp_name'],$dest)) return null;

  $web = '/uploads/assessments/'.$name;
  $stmt=$db->prepare("INSERT INTO lms_assessment_attachments(assessment_id,file_path) VALUES(?,?)");
  $stmt->bind_param('is',$assessment_id,$web);
  $stmt->execute();
  $stmt->close();
  return $web;
}

function aa_latest_attachment(mysqli $db, int $assessment_id): ?string {
  $stmt=$db->prepare("SELECT file_path FROM lms_assessment_attachments WHERE assessment_id=? ORDER BY id DESC LIMIT 1");
  $stmt->bind_param('i',$assessment_id);
  $stmt->execute();
  $stmt->bind_result($p);
  $ok=$stmt->fetch();
  $stmt->close();
  return $ok ? $p : null;
}
