-- Migration: workflow-completion tables
-- Idempotent. Re-runnable.

-- 1) Group membership (for My vs Discover, Join/Leave)
CREATE TABLE IF NOT EXISTS `community_memberships` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `group_id` BIGINT UNSIGNED NOT NULL,
  `role` ENUM('member','mod','owner') DEFAULT 'member',
  `joined_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `uq_user_group` (`user_id`,`group_id`),
  INDEX (`group_id`),
  FOREIGN KEY (`user_id`)  REFERENCES `users`(`id`)            ON DELETE CASCADE,
  FOREIGN KEY (`group_id`) REFERENCES `community_groups`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2) Post reactions (Pray / Encourage / Helpful)
CREATE TABLE IF NOT EXISTS `community_post_reactions` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `post_id` BIGINT UNSIGNED NOT NULL,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `kind` ENUM('pray','encourage','helpful') NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `uq_post_user_kind` (`post_id`,`user_id`,`kind`),
  INDEX (`post_id`),
  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;

-- 3) Quick "Request Help" form (mentor section)
CREATE TABLE IF NOT EXISTS `help_requests` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `category` VARCHAR(50) NOT NULL,
  `body` TEXT NOT NULL,
  `status` ENUM('pending','assigned','resolved','dismissed') DEFAULT 'pending',
  `assigned_mentor_id` BIGINT UNSIGNED NULL,
  `response` TEXT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `resolved_at` TIMESTAMP NULL,
  INDEX (`user_id`),
  INDEX (`status`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 4) Mentor chat (polling-based DM)
CREATE TABLE IF NOT EXISTS `mentor_chat_messages` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `session_id` BIGINT UNSIGNED NOT NULL,
  `sender_user_id` BIGINT UNSIGNED NOT NULL,
  `sender_role` ENUM('user','mentor') NOT NULL,
  `body` TEXT NOT NULL,
  `is_read` BOOLEAN DEFAULT FALSE,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX (`session_id`,`created_at`),
  FOREIGN KEY (`session_id`)     REFERENCES `mentor_sessions`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`sender_user_id`) REFERENCES `users`(`id`)           ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 5) Daily tasks (the home-dashboard "Today's Task" + weekly progress dots)
CREATE TABLE IF NOT EXISTS `user_daily_tasks` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `for_date` DATE NOT NULL,
  `task_ar` VARCHAR(255) NULL,
  `task_en` VARCHAR(255) NULL,
  `source` ENUM('home','challenge','custom') DEFAULT 'home',
  `is_done` BOOLEAN DEFAULT FALSE,
  `done_at` TIMESTAMP NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `uq_user_date_source` (`user_id`,`for_date`,`source`),
  INDEX (`user_id`,`for_date`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SELECT 'Workflows migration applied' AS msg,
  (SELECT COUNT(*) FROM community_memberships)    AS memberships,
  (SELECT COUNT(*) FROM community_post_reactions) AS reactions,
  (SELECT COUNT(*) FROM help_requests)            AS help_requests,
  (SELECT COUNT(*) FROM mentor_chat_messages)     AS chat,
  (SELECT COUNT(*) FROM user_daily_tasks)         AS daily_tasks;