-- Joseph App — MySQL 8.0 Schema
-- Charset: utf8mb4 for full Arabic support
-- Engine: InnoDB

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE DATABASE IF NOT EXISTS `joseph`
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `joseph`;

-- =============================================================
-- USERS
-- =============================================================
CREATE TABLE IF NOT EXISTS `users` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name_ar` VARCHAR(150) NULL,
  `name_en` VARCHAR(150) NULL,
  `email` VARCHAR(190) NOT NULL UNIQUE,
  `phone` VARCHAR(30) NULL,
  `password_hash` VARCHAR(255) NOT NULL,
  `avatar` VARCHAR(500) NULL,
  `gender` ENUM('male','female','other') NULL,
  `birth_year` SMALLINT NULL,
  `city` VARCHAR(100) NULL,
  `country` VARCHAR(100) NULL,
  `church_name` VARCHAR(200) NULL,
  `primary_language` ENUM('ar','en') DEFAULT 'ar',
  `notification_token` VARCHAR(255) NULL,
  `status` ENUM('pending','active','suspended') DEFAULT 'active',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_users_status` (`status`),
  INDEX `idx_users_city` (`city`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================================
-- ROUNDS
-- =============================================================
CREATE TABLE IF NOT EXISTS `rounds` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `round_number` INT UNSIGNED NOT NULL,
  `cohort_year` YEAR NOT NULL,
  `name_ar` VARCHAR(200) NOT NULL,
  `name_en` VARCHAR(200) NOT NULL,
  `description_ar` TEXT NULL,
  `description_en` TEXT NULL,
  `start_date` DATE NULL,
  `end_date` DATE NULL,
  `registration_deadline` DATE NULL,
  `max_participants` INT UNSIGNED DEFAULT 100,
  `status` ENUM('draft','open','active','closed','archived') DEFAULT 'draft',
  `tracks_enabled` JSON NULL,
  `created_by` BIGINT UNSIGNED NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_rounds_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `round_applications` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `round_id` BIGINT UNSIGNED NOT NULL,
  `motivation_text` TEXT NULL,
  `skills_summary` TEXT NULL,
  `goal_type` ENUM('job','project','calling','kingdom_project') NOT NULL,
  `status` ENUM('pending','approved','rejected','waitlisted') DEFAULT 'pending',
  `reviewed_by` BIGINT UNSIGNED NULL,
  `reviewed_at` TIMESTAMP NULL,
  `rejection_reason` TEXT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `uniq_user_round` (`user_id`,`round_id`),
  INDEX `idx_app_status` (`status`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`round_id`) REFERENCES `rounds`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `round_enrollments` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `round_id` BIGINT UNSIGNED NOT NULL,
  `track` ENUM('discover_self','start_project','kingdom_project') NOT NULL,
  `enrolled_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `progress_percentage` DECIMAL(5,2) DEFAULT 0.00,
  `completed_at` TIMESTAMP NULL,
  `certificate_issued` BOOLEAN DEFAULT FALSE,
  UNIQUE KEY `uniq_enroll` (`user_id`,`round_id`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`round_id`) REFERENCES `rounds`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =============================================================
-- JOSEPH MAP
-- =============================================================
CREATE TABLE IF NOT EXISTS `joseph_map_stages` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `stage_key` VARCHAR(50) NOT NULL UNIQUE,
  `order_index` TINYINT UNSIGNED NOT NULL,
  `name_ar` VARCHAR(150) NOT NULL,
  `name_en` VARCHAR(150) NOT NULL,
  `description_ar` TEXT NULL,
  `description_en` TEXT NULL,
  `icon` VARCHAR(100) NULL,
  `milestone_label_ar` VARCHAR(200) NULL,
  `milestone_label_en` VARCHAR(200) NULL,
  `unlocks_at_percentage` DECIMAL(5,2) DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `user_map_progress` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `current_stage_id` BIGINT UNSIGNED NOT NULL,
  `stage_started_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `stage_completed_at` TIMESTAMP NULL,
  `notes` TEXT NULL,
  UNIQUE KEY `uniq_user_progress` (`user_id`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`current_stage_id`) REFERENCES `joseph_map_stages`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =============================================================
-- SECTIONS & LESSONS
-- =============================================================
CREATE TABLE IF NOT EXISTS `sections` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `section_key` VARCHAR(50) NOT NULL UNIQUE,
  `name_ar` VARCHAR(150) NOT NULL,
  `name_en` VARCHAR(150) NOT NULL,
  `icon` VARCHAR(100) NULL,
  `color` VARCHAR(20) DEFAULT '#C9A84C',
  `description_ar` TEXT NULL,
  `description_en` TEXT NULL,
  `is_active` BOOLEAN DEFAULT TRUE,
  `order_index` TINYINT UNSIGNED DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `lessons` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `section_id` BIGINT UNSIGNED NOT NULL,
  `title_ar` VARCHAR(255) NOT NULL,
  `title_en` VARCHAR(255) NOT NULL,
  `body_ar` LONGTEXT NULL,
  `body_en` LONGTEXT NULL,
  `verse_ar` TEXT NULL,
  `verse_en` TEXT NULL,
  `verse_reference` VARCHAR(100) NULL,
  `practical_application_ar` TEXT NULL,
  `practical_application_en` TEXT NULL,
  `accountability_question_ar` TEXT NULL,
  `accountability_question_en` TEXT NULL,
  `content_type` ENUM('article','video','audio') DEFAULT 'article',
  `media_url` VARCHAR(500) NULL,
  `thumbnail_url` VARCHAR(500) NULL,
  `duration_seconds` INT UNSIGNED DEFAULT 0,
  `is_published` BOOLEAN DEFAULT FALSE,
  `is_premium` BOOLEAN DEFAULT FALSE,
  `order_index` INT DEFAULT 0,
  `created_by` BIGINT UNSIGNED NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`section_id`) REFERENCES `sections`(`id`) ON DELETE CASCADE,
  INDEX `idx_lessons_section_published` (`section_id`,`is_published`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `user_lesson_progress` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `lesson_id` BIGINT UNSIGNED NOT NULL,
  `completed_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `uniq_user_lesson` (`user_id`,`lesson_id`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`lesson_id`) REFERENCES `lessons`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =============================================================
-- CHALLENGES
-- =============================================================
CREATE TABLE IF NOT EXISTS `challenges` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `title_ar` VARCHAR(200) NOT NULL,
  `title_en` VARCHAR(200) NOT NULL,
  `description_ar` TEXT NULL,
  `description_en` TEXT NULL,
  `duration_days` SMALLINT UNSIGNED NOT NULL,
  `type` ENUM('7day','14day','30day','custom') DEFAULT '7day',
  `is_active` BOOLEAN DEFAULT TRUE,
  `start_date` DATE NULL,
  `related_section_id` BIGINT UNSIGNED NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`related_section_id`) REFERENCES `sections`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `challenge_days` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `challenge_id` BIGINT UNSIGNED NOT NULL,
  `day_number` SMALLINT UNSIGNED NOT NULL,
  `task_ar` TEXT NOT NULL,
  `task_en` TEXT NOT NULL,
  `verse_ar` TEXT NULL,
  `verse_en` TEXT NULL,
  `reflection_question_ar` TEXT NULL,
  `reflection_question_en` TEXT NULL,
  `tip_ar` TEXT NULL,
  `tip_en` TEXT NULL,
  UNIQUE KEY `uniq_chal_day` (`challenge_id`,`day_number`),
  FOREIGN KEY (`challenge_id`) REFERENCES `challenges`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `user_challenge_progress` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `challenge_id` BIGINT UNSIGNED NOT NULL,
  `round_id` BIGINT UNSIGNED NULL,
  `enrolled_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `current_day` SMALLINT UNSIGNED DEFAULT 1,
  `last_checkin_at` TIMESTAMP NULL,
  `status` ENUM('active','paused','completed') DEFAULT 'active',
  `completion_percentage` DECIMAL(5,2) DEFAULT 0.00,
  UNIQUE KEY `uniq_user_chal` (`user_id`,`challenge_id`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`challenge_id`) REFERENCES `challenges`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =============================================================
-- ASSESSMENT
-- =============================================================
CREATE TABLE IF NOT EXISTS `skill_assessment_questions` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `question_ar` TEXT NOT NULL,
  `question_en` TEXT NOT NULL,
  `question_type` ENUM('single','multi','scale') DEFAULT 'single',
  `order_index` INT DEFAULT 0,
  `category` ENUM('enjoyment','skill','style','market','teamwork','leadership') NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `skill_assessment_options` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `question_id` BIGINT UNSIGNED NOT NULL,
  `option_ar` VARCHAR(255) NOT NULL,
  `option_en` VARCHAR(255) NOT NULL,
  `tags` JSON NULL,
  `weight_score` DECIMAL(5,2) DEFAULT 1.00,
  FOREIGN KEY (`question_id`) REFERENCES `skill_assessment_questions`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `user_assessments` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `completed_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `raw_answers` JSON NULL,
  `personality_type` VARCHAR(100) NULL,
  `strength_tags` JSON NULL,
  `risk_tags` JSON NULL,
  `suitable_fields` JSON NULL,
  `project_type` VARCHAR(100) NULL,
  `next_steps` JSON NULL,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =============================================================
-- PROJECT TYPES & KINGDOM MODELS
-- =============================================================
CREATE TABLE IF NOT EXISTS `project_types` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `type_key` VARCHAR(50) NOT NULL UNIQUE,
  `name_ar` VARCHAR(150) NOT NULL,
  `name_en` VARCHAR(150) NOT NULL,
  `description_ar` TEXT NULL,
  `description_en` TEXT NULL,
  `icon` VARCHAR(100) NULL,
  `steps` JSON NULL,
  `templates` JSON NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `kingdom_models` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `model_key` VARCHAR(50) NOT NULL UNIQUE,
  `name_ar` VARCHAR(150) NOT NULL,
  `name_en` VARCHAR(150) NOT NULL,
  `description_ar` TEXT NULL,
  `description_en` TEXT NULL,
  `examples` JSON NULL,
  `is_featured` BOOLEAN DEFAULT FALSE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =============================================================
-- MENTORS & SESSIONS
-- =============================================================
CREATE TABLE IF NOT EXISTS `mentors` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` BIGINT UNSIGNED NOT NULL UNIQUE,
  `bio_ar` TEXT NULL,
  `bio_en` TEXT NULL,
  `expertise_tags` JSON NULL,
  `availability_schedule` JSON NULL,
  `session_price` DECIMAL(8,2) DEFAULT 0,
  `is_active` BOOLEAN DEFAULT TRUE,
  `rating_avg` DECIMAL(3,2) DEFAULT 0,
  `total_sessions` INT UNSIGNED DEFAULT 0,
  `approved_by` BIGINT UNSIGNED NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `mentor_sessions` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `mentor_id` BIGINT UNSIGNED NOT NULL,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `round_id` BIGINT UNSIGNED NULL,
  `session_type` ENUM('chat','video','group') DEFAULT 'chat',
  `scheduled_at` DATETIME NOT NULL,
  `duration_minutes` SMALLINT UNSIGNED DEFAULT 30,
  `status` ENUM('pending','confirmed','completed','cancelled') DEFAULT 'pending',
  `meeting_link` VARCHAR(500) NULL,
  `notes` TEXT NULL,
  `user_rating` TINYINT UNSIGNED NULL,
  `user_review` TEXT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`mentor_id`) REFERENCES `mentors`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =============================================================
-- COMMUNITY
-- =============================================================
CREATE TABLE IF NOT EXISTS `community_groups` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name_ar` VARCHAR(150) NOT NULL,
  `name_en` VARCHAR(150) NOT NULL,
  `type` ENUM('field','city','project_type','prayer','accountability','round') NOT NULL,
  `description_ar` TEXT NULL,
  `description_en` TEXT NULL,
  `icon` VARCHAR(100) NULL,
  `is_private` BOOLEAN DEFAULT FALSE,
  `max_members` INT UNSIGNED DEFAULT 500,
  `moderator_id` BIGINT UNSIGNED NULL,
  `round_id` BIGINT UNSIGNED NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `community_posts` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `group_id` BIGINT UNSIGNED NOT NULL,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `content_ar` TEXT NULL,
  `content_en` TEXT NULL,
  `media_urls` JSON NULL,
  `is_pinned` BOOLEAN DEFAULT FALSE,
  `is_approved` BOOLEAN DEFAULT TRUE,
  `flagged_count` INT UNSIGNED DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`group_id`) REFERENCES `community_groups`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `community_post_replies` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `post_id` BIGINT UNSIGNED NOT NULL,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `content` TEXT NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`post_id`) REFERENCES `community_posts`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =============================================================
-- NOTIFICATIONS
-- =============================================================
CREATE TABLE IF NOT EXISTS `notifications` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` BIGINT UNSIGNED NULL,
  `title_ar` VARCHAR(255) NOT NULL,
  `title_en` VARCHAR(255) NOT NULL,
  `body_ar` TEXT NULL,
  `body_en` TEXT NULL,
  `type` ENUM('system','lesson','challenge','mentor','community','round') DEFAULT 'system',
  `related_id` BIGINT UNSIGNED NULL,
  `related_type` VARCHAR(50) NULL,
  `is_read` BOOLEAN DEFAULT FALSE,
  `sent_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_notif_user_read` (`user_id`,`is_read`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =============================================================
-- ADMIN
-- =============================================================
CREATE TABLE IF NOT EXISTS `admin_users` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(150) NOT NULL,
  `email` VARCHAR(190) NOT NULL UNIQUE,
  `password_hash` VARCHAR(255) NOT NULL,
  `role` ENUM('super_admin','editor','mentor','moderator','round_manager') DEFAULT 'editor',
  `preferred_language` ENUM('ar','en') DEFAULT 'en',
  `is_active` BOOLEAN DEFAULT TRUE,
  `last_login` TIMESTAMP NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `admin_activity_logs` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `admin_id` INT UNSIGNED NOT NULL,
  `action` VARCHAR(100) NOT NULL,
  `target_type` VARCHAR(50) NULL,
  `target_id` BIGINT UNSIGNED NULL,
  `details` JSON NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`admin_id`) REFERENCES `admin_users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `app_settings` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `key` VARCHAR(100) NOT NULL UNIQUE,
  `value` LONGTEXT NULL,
  `type` ENUM('text','json','boolean','number') DEFAULT 'text',
  `description` VARCHAR(255) NULL,
  `updated_by` INT UNSIGNED NULL,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;