-- Monetization & Content Strategy
ALTER TABLE `courses`
ADD COLUMN `tier` ENUM('free', 'premium') NOT NULL DEFAULT 'free' AFTER `description`,
ADD COLUMN `price` DECIMAL(10, 2) DEFAULT NULL AFTER `tier`,
ADD COLUMN `type` ENUM('course', 'micro-lesson') NOT NULL DEFAULT 'course' AFTER `price`;

-- Scalability & Compliance
ALTER TABLE `users`
ADD COLUMN `language` VARCHAR(5) NOT NULL DEFAULT 'en' AFTER `email`,
ADD COLUMN `parental_controls_enabled` BOOLEAN NOT NULL DEFAULT FALSE AFTER `language`;

-- Community & Engagement: Gamification
CREATE TABLE IF NOT EXISTS `badges` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(255) NOT NULL,
  `description` TEXT,
  `icon` VARCHAR(255) -- e.g., path to an image or a font-awesome class
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `user_badges` (
  `user_id` INT NOT NULL,
  `badge_id` INT NOT NULL,
  `awarded_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`, `badge_id`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`badge_id`) REFERENCES `badges`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Community & Engagement: Discussion Boards
CREATE TABLE IF NOT EXISTS `discussion_forums` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `course_id` INT DEFAULT NULL, -- Can be null for general forums
    `title` VARCHAR(255) NOT NULL,
    `description` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`course_id`) REFERENCES `courses`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `discussion_threads` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `forum_id` INT NOT NULL,
    `user_id` INT NOT NULL,
    `title` VARCHAR(255) NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`forum_id`) REFERENCES `discussion_forums`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `discussion_posts` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `thread_id` INT NOT NULL,
    `user_id` INT NOT NULL,
    `parent_post_id` INT DEFAULT NULL, -- For replies
    `content` TEXT NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`thread_id`) REFERENCES `discussion_threads`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`parent_post_id`) REFERENCES `discussion_posts`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;