-- database/schema.sql

CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    phone VARCHAR(50) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    bio TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS courses (
    id VARCHAR(50) PRIMARY KEY,
    title_en VARCHAR(255) NOT NULL,
    title_ar VARCHAR(255) NOT NULL,
    description_en TEXT NOT NULL,
    description_ar TEXT NOT NULL,
    category_en VARCHAR(100) NOT NULL,
    category_ar VARCHAR(100) NOT NULL,
    instructor_en VARCHAR(255) NOT NULL,
    instructor_ar VARCHAR(255) NOT NULL,
    rating DECIMAL(3, 1) DEFAULT 5.0,
    reviews_count INT DEFAULT 0,
    price_ngn INT NOT NULL,
    price_usd INT NOT NULL,
    duration_en VARCHAR(50) NOT NULL,
    duration_ar VARCHAR(50) NOT NULL,
    image TEXT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS course_modules (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    course_id VARCHAR(50) NOT NULL,
    module_index INT NOT NULL,
    title_en VARCHAR(255) NOT NULL,
    title_ar VARCHAR(255) NOT NULL,
    FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS enrollments (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INT NOT NULL,
    course_id VARCHAR(50) NOT NULL,
    enrolled_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    progress INT DEFAULT 0,
    status VARCHAR(20) DEFAULT 'in_progress',
    grade VARCHAR(10) DEFAULT 'A',
    completed_modules_json TEXT DEFAULT '[]',
    payment_reference VARCHAR(100),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE,
    UNIQUE(user_id, course_id)
);

CREATE TABLE IF NOT EXISTS live_classes (
    id VARCHAR(50) PRIMARY KEY,
    title_en VARCHAR(255) NOT NULL,
    title_ar VARCHAR(255) NOT NULL,
    class_date VARCHAR(50) NOT NULL,
    class_time VARCHAR(50) NOT NULL,
    duration_en VARCHAR(50) NOT NULL,
    duration_ar VARCHAR(50) NOT NULL,
    instructor_en VARCHAR(255) NOT NULL,
    platform VARCHAR(50) NOT NULL,
    link VARCHAR(255) NOT NULL,
    status VARCHAR(20) DEFAULT 'upcoming'
);
