-- Database Schema Fixes for ashlya_ashlya_academy_new
-- Generated: 2025-11-24
-- Purpose: Add missing columns and ensure all required tables exist

-- Fix 1: Add missing columns to recently_watched table
ALTER TABLE `recently_watched` 
ADD COLUMN IF NOT EXISTS `date_watched` DATETIME DEFAULT CURRENT_TIMESTAMP,
ADD COLUMN IF NOT EXISTS `time_start` TIME DEFAULT NULL;

-- Fix 2: Add missing column to courses table  
ALTER TABLE `courses`
ADD COLUMN IF NOT EXISTS `level_name` VARCHAR(50) DEFAULT NULL;

-- Fix 3: Create skills table if not exists
CREATE TABLE IF NOT EXISTS `skills` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `user_id` INT(11) NOT NULL,
  `skill_name` VARCHAR(100) NOT NULL,
  `proficiency_level` ENUM('beginner','intermediate','advanced','expert') DEFAULT 'beginner',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Fix 4: Ensure enrollments table exists (should be there from SQL import)
-- If missing, create it
CREATE TABLE IF NOT EXISTS `enrollments` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `user_id` INT(11) NOT NULL,
  `course_id` INT(11) NOT NULL,
  `enrollment_date` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `status` ENUM('active','completed','dropped') DEFAULT 'active',
  `progress` INT(11) DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `course_id` (`course_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Fix 5: Ensure blog_posts table exists
CREATE TABLE IF NOT EXISTS `blog_posts` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(255) NOT NULL,
  `content` TEXT NOT NULL,
  `author_id` INT(11) DEFAULT NULL,
  `category_id` INT(11) DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `views` INT(11) DEFAULT 0,
  `likes` INT(11) DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `author_id` (`author_id`),
  KEY `category_id` (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Fix 6: Ensure blog_categories table exists
CREATE TABLE IF NOT EXISTS `blog_categories` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `slug` VARCHAR(100) NOT NULL,
  `description` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Fix 7: Create default news database if needed (optional)
CREATE DATABASE IF NOT EXISTS `a_news`;

-- Verification queries (run these to check if fixes worked)
-- SELECT COUNT(*) as skills_count FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'ashlya_ashlya_academy_new' AND TABLE_NAME = 'skills';
-- SHOW COLUMNS FROM recently_watched LIKE 'date_watched';
-- SHOW COLUMNS FROM courses LIKE 'level_name';
