-- SARMAAN II ePlaybook Database Schema (MySQL / MariaDB 10.4+)
CREATE DATABASE IF NOT EXISTS `esystems_sman`
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE `esystems_sman`;

SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS `activity_logs`;
DROP TABLE IF EXISTS `sync_batches`;
DROP TABLE IF EXISTS `material_downloads`;
DROP TABLE IF EXISTS `instructional_materials`;
DROP TABLE IF EXISTS `certificates`;
DROP TABLE IF EXISTS `assessment_attempts`;
DROP TABLE IF EXISTS `questions`;
DROP TABLE IF EXISTS `assessments`;
DROP TABLE IF EXISTS `learning_progress`;
DROP TABLE IF EXISTS `module_media`;
DROP TABLE IF EXISTS `module_translations`;
DROP TABLE IF EXISTS `modules`;
DROP TABLE IF EXISTS `courses`;
DROP TABLE IF EXISTS `consent_records`;
DROP TABLE IF EXISTS `privacy_notices`;
DROP TABLE IF EXISTS `notifications`;
DROP TABLE IF EXISTS `user_devices`;
DROP TABLE IF EXISTS `users`;
DROP TABLE IF EXISTS `roles`;
DROP TABLE IF EXISTS `departments`;

SET FOREIGN_KEY_CHECKS = 1;

-- 1. Departments (Training / Organizational Units)
CREATE TABLE `departments` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `code` VARCHAR(32) NOT NULL UNIQUE,
  `name` VARCHAR(120) NOT NULL,
  `description` TEXT NULL,
  `dashboard_theme` VARCHAR(32) NOT NULL DEFAULT 'default',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- 2. Roles (Mapped to Departments & Learning Pathways)
CREATE TABLE `roles` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `code` VARCHAR(32) NOT NULL UNIQUE,
  `name` VARCHAR(120) NOT NULL,
  `department_id` INT UNSIGNED NOT NULL,
  `is_learner_role` TINYINT(1) NOT NULL DEFAULT 1,
  `permissions_json` JSON NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `fk_roles_department` FOREIGN KEY (`department_id`) REFERENCES `departments`(`id`) ON DELETE RESTRICT
) ENGINE=InnoDB;

-- 3. Users (Learners, Trainers, M&E, Admins)
CREATE TABLE `users` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `unique_learner_code` VARCHAR(64) NOT NULL UNIQUE,
  `full_name` VARCHAR(150) NOT NULL,
  `phone` VARCHAR(32) NOT NULL UNIQUE,
  `email` VARCHAR(150) NULL,
  `password_hash` VARCHAR(255) NOT NULL,
  `pin_hash` VARCHAR(255) NULL,
  `offline_salt` VARCHAR(64) NOT NULL,
  `offline_verifier` VARCHAR(128) NOT NULL COMMENT 'PBKDF2-SHA256 verifier for subsequent offline logins',
  `role_id` INT UNSIGNED NOT NULL,
  `department_id` INT UNSIGNED NOT NULL,
  `state` VARCHAR(80) NOT NULL DEFAULT 'Kebbi',
  `lga` VARCHAR(100) NOT NULL DEFAULT 'Birnin Kebbi',
  `facility` VARCHAR(150) NULL,
  `implementing_org` VARCHAR(120) NOT NULL DEFAULT 'SARMAAN II',
  `preferred_lang` ENUM('en','ha','ff') NOT NULL DEFAULT 'en',
  `status` ENUM('active','pending','suspended') NOT NULL DEFAULT 'active',
  `last_login_at` DATETIME NULL,
  `last_sync_at` DATETIME NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT `fk_users_role` FOREIGN KEY (`role_id`) REFERENCES `roles`(`id`) ON DELETE RESTRICT,
  CONSTRAINT `fk_users_dept` FOREIGN KEY (`department_id`) REFERENCES `departments`(`id`) ON DELETE RESTRICT
) ENGINE=InnoDB;

-- 4. User Devices (First-login binding, biometric tracking, remote revocation)
CREATE TABLE `user_devices` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT UNSIGNED NOT NULL,
  `device_uuid` VARCHAR(100) NOT NULL,
  `device_name` VARCHAR(120) NULL,
  `platform` VARCHAR(32) NULL,
  `biometric_enabled` TINYINT(1) NOT NULL DEFAULT 0,
  `otp_verified` TINYINT(1) NOT NULL DEFAULT 1 COMMENT 'Reserved for future OTP rollout',
  `refresh_token_hash` VARCHAR(128) NULL,
  `revoked_at` DATETIME NULL,
  `last_seen_at` DATETIME NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `uq_user_device` (`user_id`, `device_uuid`),
  CONSTRAINT `fk_devices_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- 5. Privacy Notices & Consent Records
CREATE TABLE `privacy_notices` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `version` VARCHAR(24) NOT NULL UNIQUE,
  `title_json` JSON NOT NULL,
  `body_json` JSON NOT NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE `consent_records` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `event_uuid` VARCHAR(64) NOT NULL UNIQUE,
  `user_id` INT UNSIGNED NOT NULL,
  `notice_version` VARCHAR(24) NOT NULL,
  `language` ENUM('en','ha','ff') NOT NULL DEFAULT 'en',
  `status` ENUM('accepted','declined') NOT NULL,
  `consented_at` DATETIME NOT NULL,
  `synced_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `fk_consent_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- 6. Courses & Modules (Role/Department Pathways)
CREATE TABLE `courses` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `code` VARCHAR(48) NOT NULL UNIQUE,
  `title_json` JSON NOT NULL,
  `description_json` JSON NOT NULL,
  `target_roles_json` JSON NOT NULL COMMENT 'e.g. ["CDD","OIC"]',
  `target_departments_json` JSON NOT NULL,
  `pass_mark` INT NOT NULL DEFAULT 70,
  `is_published` TINYINT(1) NOT NULL DEFAULT 1,
  `version` INT NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE `modules` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `course_id` INT UNSIGNED NOT NULL,
  `module_code` VARCHAR(48) NOT NULL UNIQUE,
  `sequence_order` INT NOT NULL,
  `role_scope` ENUM('ALL','CDD','OIC') NOT NULL DEFAULT 'ALL',
  `is_mandatory` TINYINT(1) NOT NULL DEFAULT 1,
  `estimated_minutes` INT NOT NULL DEFAULT 15,
  `min_learning_seconds` INT NOT NULL DEFAULT 120,
  `pass_mark` INT NOT NULL DEFAULT 70,
  `version` INT NOT NULL DEFAULT 1,
  `is_published` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `fk_modules_course` FOREIGN KEY (`course_id`) REFERENCES `courses`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE `module_translations` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `module_id` INT UNSIGNED NOT NULL,
  `lang` ENUM('en','ha','ff') NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  `objectives` TEXT NOT NULL,
  `content_markdown` LONGTEXT NOT NULL,
  `key_takeaways` TEXT NULL,
  `review_status` ENUM('approved','draft_review') NOT NULL DEFAULT 'approved',
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `uq_module_lang` (`module_id`, `lang`),
  CONSTRAINT `fk_modtrans_module` FOREIGN KEY (`module_id`) REFERENCES `modules`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE `module_media` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `module_id` INT UNSIGNED NOT NULL,
  `media_type` ENUM('video','audio','captions','slide_image') NOT NULL,
  `lang` ENUM('en','ha','ff') NOT NULL DEFAULT 'en',
  `title` VARCHAR(200) NOT NULL,
  `file_path` VARCHAR(255) NOT NULL,
  `duration_seconds` INT NULL,
  `file_size_bytes` BIGINT NULL,
  `sha256_hash` VARCHAR(64) NULL,
  `sort_order` INT NOT NULL DEFAULT 0,
  CONSTRAINT `fk_media_module` FOREIGN KEY (`module_id`) REFERENCES `modules`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- 7. Assessments & Question Bank
CREATE TABLE `assessments` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `course_id` INT UNSIGNED NOT NULL,
  `module_id` INT UNSIGNED NULL,
  `assessment_code` VARCHAR(64) NOT NULL UNIQUE,
  `assessment_type` ENUM('pre_test','knowledge_check','post_test','retention_30','retention_60','retention_90') NOT NULL,
  `title_json` JSON NOT NULL,
  `pass_mark` INT NOT NULL DEFAULT 70,
  `max_attempts` INT NOT NULL DEFAULT 3,
  `is_published` TINYINT(1) NOT NULL DEFAULT 1,
  CONSTRAINT `fk_assess_course` FOREIGN KEY (`course_id`) REFERENCES `courses`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_assess_module` FOREIGN KEY (`module_id`) REFERENCES `modules`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE `questions` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `assessment_id` INT UNSIGNED NOT NULL,
  `question_code` VARCHAR(64) NOT NULL UNIQUE,
  `prompt_json` JSON NOT NULL,
  `options_json` JSON NOT NULL COMMENT '{"en":[...],"ha":[...],"ff":[...]}',
  `correct_index` INT NOT NULL,
  `explanation_json` JSON NULL,
  `difficulty` ENUM('easy','medium','hard') NOT NULL DEFAULT 'medium',
  `sort_order` INT NOT NULL DEFAULT 0,
  CONSTRAINT `fk_questions_assess` FOREIGN KEY (`assessment_id`) REFERENCES `assessments`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- 8. Synced Learning Progress, Assessment Attempts & Certificates
CREATE TABLE `learning_progress` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT UNSIGNED NOT NULL,
  `module_id` INT UNSIGNED NOT NULL,
  `active_seconds` INT NOT NULL DEFAULT 0,
  `video_progress_pct` DECIMAL(5,2) NOT NULL DEFAULT 0.00,
  `text_scroll_pct` DECIMAL(5,2) NOT NULL DEFAULT 0.00,
  `completed` TINYINT(1) NOT NULL DEFAULT 0,
  `rapid_completion_flag` TINYINT(1) NOT NULL DEFAULT 0,
  `last_position_seconds` INT NOT NULL DEFAULT 0,
  `completed_at` DATETIME NULL,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `uq_progress_user_mod` (`user_id`, `module_id`),
  CONSTRAINT `fk_prog_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_prog_module` FOREIGN KEY (`module_id`) REFERENCES `modules`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE `assessment_attempts` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `attempt_uuid` VARCHAR(64) NOT NULL UNIQUE,
  `user_id` INT UNSIGNED NOT NULL,
  `assessment_id` INT UNSIGNED NOT NULL,
  `score_pct` DECIMAL(5,2) NOT NULL,
  `correct_count` INT NOT NULL,
  `total_questions` INT NOT NULL,
  `passed` TINYINT(1) NOT NULL,
  `answers_json` JSON NOT NULL,
  `duration_seconds` INT NOT NULL DEFAULT 0,
  `started_at` DATETIME NOT NULL,
  `completed_at` DATETIME NOT NULL,
  `synced_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `fk_att_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_att_assess` FOREIGN KEY (`assessment_id`) REFERENCES `assessments`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE `certificates` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `certificate_code` VARCHAR(64) NOT NULL UNIQUE,
  `user_id` INT UNSIGNED NOT NULL,
  `course_id` INT UNSIGNED NOT NULL,
  `role_code` VARCHAR(32) NOT NULL,
  `qr_payload` TEXT NOT NULL,
  `issued_at` DATETIME NOT NULL,
  `synced_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `uq_cert_user_course` (`user_id`, `course_id`),
  CONSTRAINT `fk_cert_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_cert_course` FOREIGN KEY (`course_id`) REFERENCES `courses`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- 9. Instructional Materials Library (Admin Uploaded, Role/Department Accessible)
CREATE TABLE `instructional_materials` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `material_code` VARCHAR(64) NOT NULL UNIQUE,
  `title` VARCHAR(200) NOT NULL,
  `description` TEXT NULL,
  `category` VARCHAR(64) NOT NULL DEFAULT 'Job Aid',
  `file_name` VARCHAR(255) NOT NULL,
  `file_path` VARCHAR(255) NOT NULL,
  `mime_type` VARCHAR(120) NOT NULL,
  `file_size_bytes` BIGINT NOT NULL,
  `sha256_hash` VARCHAR(64) NOT NULL,
  `department_id` INT UNSIGNED NULL COMMENT 'NULL = Accessible to all departments',
  `role_id` INT UNSIGNED NULL COMMENT 'NULL = Accessible to all roles',
  `version` INT NOT NULL DEFAULT 1,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `uploaded_by` INT UNSIGNED NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT `fk_mat_dept` FOREIGN KEY (`department_id`) REFERENCES `departments`(`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_mat_role` FOREIGN KEY (`role_id`) REFERENCES `roles`(`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_mat_uploader` FOREIGN KEY (`uploaded_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE `material_downloads` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `event_uuid` VARCHAR(64) NOT NULL UNIQUE,
  `material_id` INT UNSIGNED NOT NULL,
  `user_id` INT UNSIGNED NOT NULL,
  `device_uuid` VARCHAR(100) NULL,
  `downloaded_at` DATETIME NOT NULL,
  `synced_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `fk_matdl_material` FOREIGN KEY (`material_id`) REFERENCES `instructional_materials`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_matdl_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- 10. Sync Batches & Immutable Activity Logs
CREATE TABLE `sync_batches` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `batch_uuid` VARCHAR(64) NOT NULL UNIQUE,
  `user_id` INT UNSIGNED NOT NULL,
  `device_uuid` VARCHAR(100) NOT NULL,
  `events_received` INT NOT NULL DEFAULT 0,
  `events_processed` INT NOT NULL DEFAULT 0,
  `status` ENUM('completed','partial','failed') NOT NULL DEFAULT 'completed',
  `synced_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `fk_sync_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE `activity_logs` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `event_uuid` VARCHAR(64) NOT NULL UNIQUE,
  `user_id` INT UNSIGNED NULL,
  `role_code` VARCHAR(32) NULL,
  `department_code` VARCHAR(32) NULL,
  `source` ENUM('mobile_offline','mobile_online','admin_web') NOT NULL,
  `action` VARCHAR(80) NOT NULL,
  `entity_type` VARCHAR(64) NULL,
  `entity_id` VARCHAR(64) NULL,
  `device_uuid` VARCHAR(100) NULL,
  `ip_address` VARCHAR(45) NULL,
  `metadata_json` JSON NULL,
  `occurred_at` DATETIME NOT NULL,
  `logged_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_logs_user` (`user_id`),
  INDEX `idx_logs_action` (`action`),
  INDEX `idx_logs_occurred` (`occurred_at`),
  INDEX `idx_logs_role_dept` (`role_code`, `department_code`),
  CONSTRAINT `fk_logs_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- 11. Notifications
CREATE TABLE `notifications` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `target_role_code` VARCHAR(32) NULL,
  `target_department_code` VARCHAR(32) NULL,
  `title_json` JSON NOT NULL,
  `body_json` JSON NOT NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_by` INT UNSIGNED NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;
