"""Add video courses feature

Revision ID: 001_add_video_courses
Revises:
Create Date: 2025-03-07

"""
from alembic import op
import sqlalchemy as sa
from datetime import datetime

# revision identifiers, used by Alembic.
revision = '001_add_video_courses'
down_revision = None
branch_labels = None
depends_on = None


def upgrade():
    # Course Categories
    op.create_table('course_categories',
        sa.Column('id', sa.Integer(), primary_key=True),
        sa.Column('name', sa.String(255), nullable=False),
        sa.Column('name_ar', sa.String(255), nullable=False),
        sa.Column('slug', sa.String(255), nullable=False, unique=True),
        sa.Column('description', sa.Text(), nullable=True),
        sa.Column('description_ar', sa.Text(), nullable=True),
        sa.Column('icon', sa.String(50), nullable=True),
        sa.Column('color', sa.String(20), default='#D2AF20'),
        sa.Column('display_order', sa.Integer(), default=0),
        sa.Column('is_active', sa.Boolean(), default=True),
        sa.Column('created_at', sa.DateTime(), default=sa.func.utcnow()),
    )
    op.create_index('idx_course_categories_slug', 'course_categories', ['slug'])
    op.create_index('idx_course_categories_active', 'course_categories', ['is_active'])

    # Certificate Templates (create before courses for FK)
    op.create_table('certificate_templates',
        sa.Column('id', sa.Integer(), primary_key=True),
        sa.Column('name', sa.String(255), nullable=False),
        sa.Column('name_ar', sa.String(255), nullable=False),
        sa.Column('background_image', sa.String(500), nullable=False),
        sa.Column('logo_position', sa.String(20), default='top'),
        sa.Column('text_color', sa.String(20), default='#1a1a1a'),
        sa.Column('accent_color', sa.String(20), default='#D2AF20'),
        sa.Column('font_family', sa.String(100), default='Amiri'),
        sa.Column('title_text', sa.String(255), default='شهادة إتمام'),
        sa.Column('title_text_en', sa.String(255), default='Certificate of Completion'),
        sa.Column('body_template', sa.Text(), default='تشهد أكاديمية جدوى بأن الطالب {name} أتم بنجاح الدورة التدريبية {course}'),
        sa.Column('body_template_en', sa.Text(), nullable=True),
        sa.Column('signature_text', sa.String(255), nullable=True),
        sa.Column('signature_text_ar', sa.String(255), nullable=True),
        sa.Column('signature_image', sa.String(500), nullable=True),
        sa.Column('show_seal', sa.Boolean(), default=True),
        sa.Column('show_date', sa.Boolean(), default=True),
        sa.Column('show_certificate_number', sa.Boolean(), default=True),
        sa.Column('is_active', sa.Boolean(), default=True),
        sa.Column('created_at', sa.DateTime(), default=sa.func.utcnow()),
    )

    # Trainers (create before courses for FK)
    op.create_table('trainers',
        sa.Column('id', sa.Integer(), primary_key=True),
        sa.Column('name', sa.String(255), nullable=False),
        sa.Column('name_ar', sa.String(255), nullable=True),
        sa.Column('bio', sa.Text(), nullable=True),
        sa.Column('bio_ar', sa.Text(), nullable=True),
        sa.Column('title', sa.String(255), nullable=True),
        sa.Column('title_ar', sa.String(255), nullable=True),
        sa.Column('avatar_url', sa.String(500), nullable=True),
        sa.Column('email', sa.String(255), nullable=True),
        sa.Column('linkedin_url', sa.String(500), nullable=True),
        sa.Column('twitter_url', sa.String(500), nullable=True),
        sa.Column('website_url', sa.String(500), nullable=True),
        sa.Column('is_active', sa.Boolean(), default=True),
        sa.Column('created_at', sa.DateTime(), default=sa.func.utcnow()),
    )

    # Courses
    op.create_table('courses',
        sa.Column('id', sa.Integer(), primary_key=True),
        sa.Column('category_id', sa.Integer(), sa.ForeignKey('course_categories.id'), nullable=False),
        sa.Column('trainer_id', sa.Integer(), sa.ForeignKey('trainers.id'), nullable=False),
        sa.Column('title', sa.String(500), nullable=False),
        sa.Column('title_ar', sa.String(500), nullable=False),
        sa.Column('slug', sa.String(255), nullable=False, unique=True),
        sa.Column('subtitle', sa.String(500), nullable=True),
        sa.Column('subtitle_ar', sa.String(500), nullable=True),
        sa.Column('description', sa.Text(), nullable=False),
        sa.Column('description_ar', sa.Text(), nullable=False),
        sa.Column('cover_image', sa.String(500), nullable=True),
        sa.Column('promo_video_url', sa.String(500), nullable=True),
        sa.Column('level', sa.String(50), default='beginner'),
        sa.Column('language', sa.String(10), default='ar'),
        sa.Column('duration_hours', sa.Float(), default=0),
        sa.Column('price', sa.Float(), default=0),
        sa.Column('currency', sa.String(10), default='SAR'),
        sa.Column('is_free', sa.Boolean(), default=False),
        sa.Column('max_students', sa.Integer(), nullable=True),
        sa.Column('current_enrollments', sa.Integer(), default=0),
        sa.Column('requirements_json', sa.JSON(), default=list),
        sa.Column('requirements_ar_json', sa.JSON(), default=list),
        sa.Column('learning_objectives_json', sa.JSON(), default=list),
        sa.Column('learning_objectives_ar_json', sa.JSON(), default=list),
        sa.Column('target_audience_json', sa.JSON(), default=list),
        sa.Column('target_audience_ar_json', sa.JSON(), default=list),
        sa.Column('certificate_template_id', sa.Integer(), sa.ForeignKey('certificate_templates.id'), nullable=True),
        sa.Column('certificate_threshold', sa.Float(), default=80.0),
        sa.Column('status', sa.String(20), default='draft'),
        sa.Column('featured', sa.Boolean(), default=False),
        sa.Column('is_active', sa.Boolean(), default=True),
        sa.Column('meta_title', sa.String(255), nullable=True),
        sa.Column('meta_description', sa.Text(), nullable=True),
        sa.Column('created_at', sa.DateTime(), default=sa.func.utcnow()),
        sa.Column('updated_at', sa.DateTime(), default=sa.func.utcnow(), onupdate=sa.func.utcnow()),
        sa.Column('published_at', sa.DateTime(), nullable=True),
    )
    op.create_index('idx_courses_slug', 'courses', ['slug'])
    op.create_index('idx_courses_category', 'courses', ['category_id'])
    op.create_index('idx_courses_trainer', 'courses', ['trainer_id'])
    op.create_index('idx_courses_status', 'courses', ['status'])
    op.create_index('idx_courses_featured', 'courses', ['featured'])

    # Course Videos
    op.create_table('course_videos',
        sa.Column('id', sa.Integer(), primary_key=True),
        sa.Column('course_id', sa.Integer(), sa.ForeignKey('courses.id'), nullable=False),
        sa.Column('title', sa.String(500), nullable=False),
        sa.Column('title_ar', sa.String(500), nullable=False),
        sa.Column('description', sa.Text(), nullable=True),
        sa.Column('description_ar', sa.Text(), nullable=True),
        sa.Column('video_path', sa.String(500), nullable=False),
        sa.Column('video_url', sa.String(500), nullable=True),
        sa.Column('duration_seconds', sa.Integer(), default=0),
        sa.Column('thumbnail_path', sa.String(500), nullable=True),
        sa.Column('section_name', sa.String(255), nullable=True),
        sa.Column('section_name_ar', sa.String(255), nullable=True),
        sa.Column('position', sa.Integer(), default=0),
        sa.Column('is_preview', sa.Boolean(), default=False),
        sa.Column('is_active', sa.Boolean(), default=True),
        sa.Column('created_at', sa.DateTime(), default=sa.func.utcnow()),
    )
    op.create_index('idx_course_videos_course', 'course_videos', ['course_id'])
    op.create_index('idx_course_videos_position', 'course_videos', ['position'])

    # Certificates
    op.create_table('certificates',
        sa.Column('id', sa.Integer(), primary_key=True),
        sa.Column('user_id', sa.Integer(), sa.ForeignKey('users.id'), nullable=False),
        sa.Column('course_id', sa.Integer(), sa.ForeignKey('courses.id'), nullable=False),
        sa.Column('template_id', sa.Integer(), sa.ForeignKey('certificate_templates.id'), nullable=False),
        sa.Column('certificate_number', sa.String(100), nullable=False, unique=True),
        sa.Column('issued_at', sa.DateTime(), default=sa.func.utcnow()),
        sa.Column('verification_url', sa.String(500), nullable=True),
    )
    op.create_index('idx_certificates_number', 'certificates', ['certificate_number'])
    op.create_index('idx_certificates_user', 'certificates', ['user_id'])
    op.create_index('idx_certificates_course', 'certificates', ['course_id'])

    # Course Enrollments
    op.create_table('course_enrollments',
        sa.Column('id', sa.Integer(), primary_key=True),
        sa.Column('user_id', sa.Integer(), sa.ForeignKey('users.id'), nullable=False),
        sa.Column('course_id', sa.Integer(), sa.ForeignKey('courses.id'), nullable=False),
        sa.Column('progress_percentage', sa.Float(), default=0),
        sa.Column('completed_videos_json', sa.JSON(), default=list),
        sa.Column('last_watched_video_id', sa.Integer(), sa.ForeignKey('course_videos.id'), nullable=True),
        sa.Column('last_position_seconds', sa.Integer(), default=0),
        sa.Column('status', sa.String(20), default='enrolled'),
        sa.Column('enrolled_at', sa.DateTime(), default=sa.func.utcnow()),
        sa.Column('completed_at', sa.DateTime(), nullable=True),
        sa.Column('certificate_issued', sa.Boolean(), default=False),
        sa.Column('certificate_id', sa.Integer(), sa.ForeignKey('certificates.id'), nullable=True),
    )
    op.create_index('idx_enrollments_user', 'course_enrollments', ['user_id'])
    op.create_index('idx_enrollments_course', 'course_enrollments', ['course_id'])
    op.create_unique_constraint('uq_user_course', 'course_enrollments', ['user_id', 'course_id'])

    # Course Reviews
    op.create_table('course_reviews',
        sa.Column('id', sa.Integer(), primary_key=True),
        sa.Column('user_id', sa.Integer(), sa.ForeignKey('users.id'), nullable=False),
        sa.Column('course_id', sa.Integer(), sa.ForeignKey('courses.id'), nullable=False),
        sa.Column('rating', sa.Integer(), nullable=False),
        sa.Column('comment', sa.Text(), nullable=True),
        sa.Column('is_approved', sa.Boolean(), default=False),
        sa.Column('created_at', sa.DateTime(), default=sa.func.utcnow()),
    )
    op.create_index('idx_reviews_course', 'course_reviews', ['course_id'])
    op.create_unique_constraint('uq_user_course_review', 'course_reviews', ['user_id', 'course_id'])


def downgrade():
    op.drop_table('course_reviews')
    op.drop_table('course_enrollments')
    op.drop_table('certificates')
    op.drop_table('course_videos')
    op.drop_table('courses')
    op.drop_table('trainers')
    op.drop_table('certificate_templates')
    op.drop_table('course_categories')