-- Migration: add tables for missing top-gap features
-- Run idempotently. Safe to re-execute.

-- 1) User Projects (project save/export)
CREATE TABLE IF NOT EXISTS `user_projects` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `project_type_id` BIGINT UNSIGNED NULL,
  `title_ar` VARCHAR(255) NULL,
  `title_en` VARCHAR(255) NULL,
  `summary` TEXT NULL,
  `data` JSON NOT NULL,
  `status` ENUM('draft','submitted','published','archived') DEFAULT 'draft',
  `is_public` BOOLEAN DEFAULT FALSE,
  `pdf_url` VARCHAR(500) NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX(`user_id`),
  INDEX(`status`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2) FCM / device push tokens
CREATE TABLE IF NOT EXISTS `device_tokens` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `token` VARCHAR(500) NOT NULL,
  `platform` ENUM('android','ios','web') NOT NULL,
  `app_version` VARCHAR(40) NULL,
  `last_seen_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `uq_device_token` (`token`),
  INDEX(`user_id`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3) Community post reports (moderation queue)
CREATE TABLE IF NOT EXISTS `post_reports` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `post_id` BIGINT UNSIGNED NOT NULL,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `reason` VARCHAR(80) NOT NULL,
  `details` TEXT NULL,
  `status` ENUM('pending','reviewed','dismissed','actioned') DEFAULT 'pending',
  `reviewed_by` BIGINT UNSIGNED NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `reviewed_at` TIMESTAMP NULL,
  INDEX(`post_id`),
  INDEX(`status`),
  FOREIGN KEY (`post_id`) REFERENCES `community_posts`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 4) Mentor session slots (separate from booked sessions)
CREATE TABLE IF NOT EXISTS `mentor_slots` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `mentor_id` BIGINT UNSIGNED NOT NULL,
  `starts_at` DATETIME NOT NULL,
  `duration_minutes` SMALLINT UNSIGNED DEFAULT 30,
  `is_booked` BOOLEAN DEFAULT FALSE,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX(`mentor_id`),
  INDEX(`starts_at`),
  FOREIGN KEY (`mentor_id`) REFERENCES `mentors`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 5) Seed a couple of slots for the existing mentors (next 7 days, 10am and 4pm local)
INSERT IGNORE INTO `mentor_slots` (mentor_id, starts_at, duration_minutes)
SELECT m.id,
       DATE_ADD(DATE_ADD(CURDATE(), INTERVAL d.n DAY), INTERVAL h.h HOUR) AS starts_at,
       30
FROM mentors m
JOIN (SELECT 1 AS n UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7) d
JOIN (SELECT 10 AS h UNION SELECT 16) h
WHERE m.is_active = 1;

SELECT 'Migration applied' AS msg,
  (SELECT COUNT(*) FROM user_projects) AS projects,
  (SELECT COUNT(*) FROM device_tokens) AS tokens,
  (SELECT COUNT(*) FROM post_reports) AS reports,
  (SELECT COUNT(*) FROM mentor_slots) AS slots;