from datetime import datetime
import uuid
from app.extensions import db


# ═══════════════════════════════════════════════════════════════════════════════
#  Course Category Model
# ═══════════════════════════════════════════════════════════════════════════════

class CourseCategory(db.Model):
    __tablename__ = "course_categories"

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(255), nullable=False)
    name_ar = db.Column(db.String(255), nullable=False)
    slug = db.Column(db.String(255), unique=True, nullable=False, index=True)
    description = db.Column(db.Text, nullable=True)
    description_ar = db.Column(db.Text, nullable=True)
    icon = db.Column(db.String(50), nullable=True)
    color = db.Column(db.String(20), default="#D2AF20")
    display_order = db.Column(db.Integer, default=0)
    is_active = db.Column(db.Boolean, default=True)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)

    # Relationships
    courses = db.relationship("Course", backref="category", lazy="dynamic")

    @property
    def course_count(self):
        return self.courses.filter_by(status="published", is_active=True).count()

    def __repr__(self):
        return f"<CourseCategory {self.name}>"


# ═══════════════════════════════════════════════════════════════════════════════
#  Trainer Model
# ═══════════════════════════════════════════════════════════════════════════════

class Trainer(db.Model):
    __tablename__ = "trainers"

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(255), nullable=False)
    name_ar = db.Column(db.String(255), nullable=True)
    bio = db.Column(db.Text, nullable=True)
    bio_ar = db.Column(db.Text, nullable=True)
    title = db.Column(db.String(255), nullable=True)  # Job title
    title_ar = db.Column(db.String(255), nullable=True)
    avatar_url = db.Column(db.String(500), nullable=True)
    email = db.Column(db.String(255), nullable=True)
    linkedin_url = db.Column(db.String(500), nullable=True)
    twitter_url = db.Column(db.String(500), nullable=True)
    website_url = db.Column(db.String(500), nullable=True)
    is_active = db.Column(db.Boolean, default=True)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)

    # Relationships
    courses = db.relationship("Course", backref="trainer", lazy="dynamic")

    @property
    def active_courses_count(self):
        return self.courses.filter_by(status="published", is_active=True).count()

    @property
    def display_name(self):
        """Return Arabic name if available, otherwise English name."""
        return self.name_ar or self.name

    def __repr__(self):
        return f"<Trainer {self.name}>"


# ═══════════════════════════════════════════════════════════════════════════════
#  Certificate Template Model
# ═══════════════════════════════════════════════════════════════════════════════

class CertificateTemplate(db.Model):
    __tablename__ = "certificate_templates"

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(255), nullable=False)
    name_ar = db.Column(db.String(255), nullable=False)

    # Template Design
    background_image = db.Column(db.String(500), nullable=False)
    logo_position = db.Column(db.String(20), default="top")  # top, top-left, top-right, center
    text_color = db.Column(db.String(20), default="#1a1a1a")
    accent_color = db.Column(db.String(20), default="#D2AF20")
    font_family = db.Column(db.String(100), default="Amiri")

    # Certificate Text
    title_text = db.Column(db.String(255), default="شهادة إتمام")
    title_text_en = db.Column(db.String(255), default="Certificate of Completion")
    body_template = db.Column(db.Text, default="تشهد أكاديمية جدوى بأن الطالب {name} أتم بنجاح الدورة التدريبية {course}")
    body_template_en = db.Column(db.Text, nullable=True)

    # Signature
    signature_text = db.Column(db.String(255), nullable=True)
    signature_text_ar = db.Column(db.String(255), nullable=True)
    signature_image = db.Column(db.String(500), nullable=True)

    # Additional settings
    show_seal = db.Column(db.Boolean, default=True)
    show_date = db.Column(db.Boolean, default=True)
    show_certificate_number = db.Column(db.Boolean, default=True)

    is_active = db.Column(db.Boolean, default=True)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)

    # Relationships
    courses = db.relationship("Course", backref="certificate_template")

    def __repr__(self):
        return f"<CertificateTemplate {self.name}>"


# ═══════════════════════════════════════════════════════════════════════════════
#  Course Model
# ═══════════════════════════════════════════════════════════════════════════════

class Course(db.Model):
    __tablename__ = "courses"

    id = db.Column(db.Integer, primary_key=True)
    category_id = db.Column(db.Integer, db.ForeignKey("course_categories.id"), nullable=False)
    trainer_id = db.Column(db.Integer, db.ForeignKey("trainers.id"), nullable=False)

    # Basic Info
    title = db.Column(db.String(500), nullable=False)
    title_ar = db.Column(db.String(500), nullable=False)
    slug = db.Column(db.String(255), unique=True, nullable=False, index=True)
    subtitle = db.Column(db.String(500), nullable=True)
    subtitle_ar = db.Column(db.String(500), nullable=True)

    # Description
    description = db.Column(db.Text, nullable=False)
    description_ar = db.Column(db.Text, nullable=False)

    # Media
    cover_image = db.Column(db.String(500), nullable=True)
    promo_video_url = db.Column(db.String(500), nullable=True)

    # Course Details
    level = db.Column(db.String(50), default="beginner")  # beginner, intermediate, advanced, all
    language = db.Column(db.String(10), default="ar")  # ar, en, both
    duration_hours = db.Column(db.Float, default=0)

    # Pricing
    price = db.Column(db.Float, default=0)
    currency = db.Column(db.String(10), default="SAR")
    is_free = db.Column(db.Boolean, default=False)

    # Enrollment & Limits
    max_students = db.Column(db.Integer, nullable=True)  # null = unlimited
    current_enrollments = db.Column(db.Integer, default=0)

    # Requirements (stored as JSON array)
    requirements_json = db.Column(db.JSON, default=list)
    requirements_ar_json = db.Column(db.JSON, default=list)

    # What you'll learn (stored as JSON array)
    learning_objectives_json = db.Column(db.JSON, default=list)
    learning_objectives_ar_json = db.Column(db.JSON, default=list)

    # Target audience (stored as JSON array)
    target_audience_json = db.Column(db.JSON, default=list)
    target_audience_ar_json = db.Column(db.JSON, default=list)

    # Certificate
    certificate_template_id = db.Column(db.Integer, db.ForeignKey("certificate_templates.id"), nullable=True)
    certificate_threshold = db.Column(db.Float, default=80.0)  # % completion to get certificate

    # Status
    status = db.Column(db.String(20), default="draft")  # draft, published, archived
    featured = db.Column(db.Boolean, default=False)
    is_active = db.Column(db.Boolean, default=True)

    # SEO
    meta_title = db.Column(db.String(255), nullable=True)
    meta_description = db.Column(db.Text, nullable=True)

    # Timestamps
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    published_at = db.Column(db.DateTime, nullable=True)

    # Relationships
    videos = db.relationship("CourseVideo", backref="course", lazy="dynamic", order_by="CourseVideo.position",
                           cascade="all, delete-orphan")
    enrollments = db.relationship("CourseEnrollment", backref="course", lazy="dynamic",
                                cascade="all, delete-orphan")
    reviews = db.relationship("CourseReview", backref="course", lazy="dynamic",
                             cascade="all, delete-orphan")

    @property
    def display_title(self):
        """Return Arabic title if language is ar, otherwise English title."""
        return self.title_ar if self.language in ("ar", "both") else self.title

    @property
    def display_description(self):
        """Return Arabic description if language is ar, otherwise English description."""
        return self.description_ar if self.language in ("ar", "both") else self.description

    @property
    def requirements(self):
        """Return requirements in appropriate language."""
        return self.requirements_ar_json or self.requirements_json

    @property
    def learning_objectives(self):
        """Return learning objectives in appropriate language."""
        return self.learning_objectives_ar_json or self.learning_objectives_json

    @property
    def target_audience(self):
        """Return target audience in appropriate language."""
        return self.target_audience_ar_json or self.target_audience_json

    @property
    def total_videos(self):
        return self.videos.filter_by(is_active=True).count()

    @property
    def total_duration(self):
        """Return total duration in hours."""
        total_seconds = sum(v.duration_seconds for v in self.videos.filter_by(is_active=True))
        return round(total_seconds / 3600, 1)

    @property
    def avg_rating(self):
        reviews = self.reviews.filter_by(is_approved=True).all()
        if not reviews:
            return 0
        return round(sum(r.rating for r in reviews) / len(reviews), 1)

    @property
    def review_count(self):
        return self.reviews.filter_by(is_approved=True).count()

    @property
    def level_label(self):
        labels = {
            "beginner": "مبتدئ",
            "intermediate": "متوسط",
            "advanced": "متقدم",
            "all": "جميع المستويات"
        }
        return labels.get(self.level, self.level)

    @property
    def is_published(self):
        return self.status == "published" and self.is_active

    @property
    def has_certificate(self):
        return self.certificate_template_id is not None

    def __repr__(self):
        return f"<Course {self.title}>"


# ═══════════════════════════════════════════════════════════════════════════════
#  Course Video Model
# ═══════════════════════════════════════════════════════════════════════════════

class CourseVideo(db.Model):
    __tablename__ = "course_videos"

    id = db.Column(db.Integer, primary_key=True)
    course_id = db.Column(db.Integer, db.ForeignKey("courses.id"), nullable=False)

    # Video Info
    title = db.Column(db.String(500), nullable=False)
    title_ar = db.Column(db.String(500), nullable=False)
    description = db.Column(db.Text, nullable=True)
    description_ar = db.Column(db.Text, nullable=True)

    # Video File
    video_path = db.Column(db.String(500), nullable=False)  # Path to uploaded video
    video_url = db.Column(db.String(500), nullable=True)  # External URL (YouTube, Vimeo)
    duration_seconds = db.Column(db.Integer, default=0)
    thumbnail_path = db.Column(db.String(500), nullable=True)

    # Organization
    section_name = db.Column(db.String(255), nullable=True)  # For grouping videos
    section_name_ar = db.Column(db.String(255), nullable=True)
    position = db.Column(db.Integer, default=0)

    # Access
    is_preview = db.Column(db.Boolean, default=False)  # Free preview video
    is_active = db.Column(db.Boolean, default=True)

    # Timestamps
    created_at = db.Column(db.DateTime, default=datetime.utcnow)

    @property
    def display_title(self):
        """Return Arabic title if available, otherwise English title."""
        return self.title_ar or self.title

    @property
    def display_section(self):
        """Return Arabic section name if available, otherwise English."""
        return self.section_name_ar or self.section_name

    @property
    def duration_minutes(self):
        """Return duration in minutes."""
        return self.duration_seconds // 60 if self.duration_seconds else 0

    @property
    def duration_display(self):
        """Return formatted duration (MM:SS or HH:MM:SS)."""
        if not self.duration_seconds:
            return "00:00"
        hours = self.duration_seconds // 3600
        minutes = (self.duration_seconds % 3600) // 60
        seconds = self.duration_seconds % 60
        if hours:
            return f"{hours:02d}:{minutes:02d}:{seconds:02d}"
        return f"{minutes:02d}:{seconds:02d}"

    def __repr__(self):
        return f"<CourseVideo {self.title}>"


# ═══════════════════════════════════════════════════════════════════════════════
#  Course Enrollment Model
# ═══════════════════════════════════════════════════════════════════════════════

class CourseEnrollment(db.Model):
    __tablename__ = "course_enrollments"

    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.String, db.ForeignKey("users.id"), nullable=False)
    course_id = db.Column(db.Integer, db.ForeignKey("courses.id"), nullable=False)

    # Progress
    progress_percentage = db.Column(db.Float, default=0)
    completed_videos_json = db.Column(db.JSON, default=list)  # List of video IDs
    last_watched_video_id = db.Column(db.Integer, db.ForeignKey("course_videos.id"), nullable=True)
    last_position_seconds = db.Column(db.Integer, default=0)  # Resume position

    # Status
    status = db.Column(db.String(20), default="enrolled")  # enrolled, in_progress, completed, dropped
    enrolled_at = db.Column(db.DateTime, default=datetime.utcnow)
    completed_at = db.Column(db.DateTime, nullable=True)
    certificate_issued = db.Column(db.Boolean, default=False)
    certificate_id = db.Column(db.Integer, db.ForeignKey("certificates.id"), nullable=True)

    # Unique constraint
    __table_args__ = (db.UniqueConstraint("user_id", "course_id", name="uq_user_course"),)

    # Relationships
    user = db.relationship("User", backref="enrollments")
    last_watched_video = db.relationship("CourseVideo", foreign_keys=[last_watched_video_id])
    certificate = db.relationship("Certificate", foreign_keys=[certificate_id])

    @property
    def completed_videos(self):
        return self.completed_videos_json or []

    @property
    def is_completed(self):
        return self.status == "completed"

    @property
    def can_get_certificate(self):
        """Check if user meets certificate threshold."""
        return self.progress_percentage >= self.course.certificate_threshold if self.course else False

    def update_progress(self):
        """Recalculate progress based on completed videos."""
        if not self.course:
            return
        total_videos = self.course.videos.filter_by(is_active=True).count()
        if total_videos > 0:
            completed = len(self.completed_videos)
            self.progress_percentage = round((completed / total_videos) * 100, 1)

            # Update status
            if self.progress_percentage == 0:
                self.status = "enrolled"
            elif 0 < self.progress_percentage < 100:
                self.status = "in_progress"
            elif self.progress_percentage >= 100:
                self.status = "completed"
                if not self.completed_at:
                    from flask import current_app
                    self.completed_at = datetime.utcnow()
                    # Auto-generate certificate if eligible
                    if self.can_get_certificate and not self.certificate_issued:
                        try:
                            from app.helpers.certificate_generator import generate_certificate_pdf
                            generate_certificate_pdf(self)
                        except Exception as e:
                            current_app.logger.error(f"Certificate generation failed: {e}")

    def __repr__(self):
        return f"<CourseEnrollment user={self.user_id} course={self.course_id}>"


# ═══════════════════════════════════════════════════════════════════════════════
#  Certificate Model (Issued Certificates)
# ═══════════════════════════════════════════════════════════════════════════════

class Certificate(db.Model):
    __tablename__ = "certificates"

    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.String, db.ForeignKey("users.id"), nullable=False)
    course_id = db.Column(db.Integer, db.ForeignKey("courses.id"), nullable=False)
    template_id = db.Column(db.Integer, db.ForeignKey("certificate_templates.id"), nullable=False)

    # Certificate Details
    certificate_number = db.Column(db.String(100), unique=True, nullable=False, index=True)
    issued_at = db.Column(db.DateTime, default=datetime.utcnow)

    # Verification
    verification_url = db.Column(db.String(500), nullable=True)

    # Relationships
    user = db.relationship("User", backref="certificates_earned")
    course = db.relationship("Course", backref="issued_certificates")
    template = db.relationship("CertificateTemplate")

    @staticmethod
    def generate_certificate_number():
        """Generate unique certificate number."""
        date_part = datetime.utcnow().strftime("%Y%m%d")
        unique_part = uuid.uuid4().hex[:8].upper()
        return f"JC-{date_part}-{unique_part}"

    def __repr__(self):
        return f"<Certificate {self.certificate_number}>"


# ═══════════════════════════════════════════════════════════════════════════════
#  Course Review Model
# ═══════════════════════════════════════════════════════════════════════════════

class CourseReview(db.Model):
    __tablename__ = "course_reviews"

    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.String, db.ForeignKey("users.id"), nullable=False)
    course_id = db.Column(db.Integer, db.ForeignKey("courses.id"), nullable=False)

    rating = db.Column(db.Integer, nullable=False)  # 1-5
    comment = db.Column(db.Text, nullable=True)
    is_approved = db.Column(db.Boolean, default=False)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)

    # Relationships
    user = db.relationship("User", backref="course_reviews")

    # Unique constraint
    __table_args__ = (db.UniqueConstraint("user_id", "course_id", name="uq_user_course_review"),)

    def __repr__(self):
        return f"<CourseReview user={self.user_id} course={self.course_id} rating={self.rating}>"