from datetime import datetime
from flask_sqlalchemy import SQLAlchemy
from config import db
from sqlalchemy import Computed


# Admin/Auth Model
class Admin(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    public_id = db.Column(db.String(50), unique=True)
    name = db.Column(db.String(100), nullable=False)
    email = db.Column(db.String(70), unique=True, nullable=False)
    password = db.Column(db.String(255), nullable=False)
    role = db.Column(db.String(50), nullable=False, default='teacher')  # Roles: 'teacher', 'superadmin', 'institute_admin', 'institute_owner', 'instructor'
    institute_id = db.Column(db.Integer, nullable=True)  # Link to institutes table
    instructor_id = db.Column(db.Integer, nullable=True)  # Link to instructors table
    
    def to_dict(self):
        return {
            'id': self.id,
            'public_id': self.public_id,
            'name': self.name,
            'email': self.email,
            'role': self.role,
            'institute_id': self.institute_id,
            'instructor_id': self.instructor_id
        }


class Courses(db.Model):
    __tablename__ = 'courses'

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    # Institute fields (from migration 004)
    institute_id = db.Column(db.Integer, nullable=True, comment='Institute offering this course')
    primary_instructor_id = db.Column(db.Integer, nullable=True, comment='Primary instructor for this course')
    title = db.Column(db.String(255), nullable=False)
    course_code = db.Column(db.String(50), comment='Unique course code within institute')
    subtitle = db.Column(db.String(500))
    description = db.Column(db.Text, nullable=False)
    price = db.Column(db.Numeric(10, 2), nullable=False)
    discount = db.Column(db.Numeric(10, 2), default=0.00)
    final_price = db.Column(db.Float, Computed(
        "price - (price * discount / 100)"))
    image_url = db.Column(db.String(1000))
    video_preview_url = db.Column(db.String(1000))
    level = db.Column(db.Enum('Beginner', 'Intermediate',
                      'Advanced', 'Expert'), nullable=False)
    category_id = db.Column(db.Integer, nullable=False)
    type = db.Column(
        db.Enum('Self-paced', 'Instructor-led', 'Hybrid'), nullable=False)
    language = db.Column(db.String(50), default='English')
    duration_hours = db.Column(db.Integer, default=0)
    total_lessons = db.Column(db.Integer, default=0)
    total_resources = db.Column(db.Integer, default=0)
    prerequisites = db.Column(db.Text)
    outcomes = db.Column(db.Text)
    target_audience = db.Column(db.Text)
    publication_date = db.Column(db.Date, nullable=False)
    status = db.Column(db.Enum('draft', 'active', 'inactive',
                       'archived'), default='active')
    rating = db.Column(db.Float, default=0)
    reviews_count = db.Column(db.Integer, default=0)
    enrollments_count = db.Column(db.Integer, default=0)
    is_featured = db.Column(db.Boolean, default=False)
    is_top_selling = db.Column(db.Boolean, default=False)
    is_trending = db.Column(db.Boolean, default=False)
    tags = db.Column(db.String(255))
    difficulty_score = db.Column(db.Float)
    # Institute-specific fields (from migration 004)
    max_students = db.Column(db.Integer, default=0, comment='Maximum students allowed')
    certificate_template = db.Column(db.String(500), comment='Certificate template URL')
    is_public = db.Column(db.Boolean, default=True, comment='Whether course is publicly visible')
    approval_status = db.Column(db.Enum('draft', 'pending_review', 'approved', 'rejected', 'archived'), default='draft')
    approved_by = db.Column(db.Integer, nullable=True, comment='Admin who approved the course')
    approved_at = db.Column(db.DateTime, nullable=True, comment='When course was approved')
    created_at = db.Column(db.DateTime, default=db.func.current_timestamp())
    updated_at = db.Column(db.DateTime, default=db.func.current_timestamp(
    ), onupdate=db.func.current_timestamp())
    instructor_id = db.Column(db.Integer, nullable=False)
    instructor_name = db.Column(
        db.String(1000), nullable=False, default='Skill Darbar')

    def to_dict(self):
        return {column.name: getattr(self, column.name) for column in self.__table__.columns}

    def calculate_final_price(self):
        return float(self.price) - float(self.discount)


class User(db.Model):
    __tablename__ = 'user'

    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(100), nullable=False)
    password = db.Column(db.String(1000), nullable=False, default='ashlyaacademy')
    password_hash = db.Column(db.String(255), nullable=True)
    email = db.Column(db.String(100), unique=True, nullable=False)
    # Institute fields (from migration 004)
    user_type = db.Column(db.Enum('student', 'instructor', 'institute_admin', 'super_admin'), default='student')
    institute_id = db.Column(db.Integer, nullable=True, comment='Primary institute association')
    is_verified = db.Column(db.Boolean, default=False, comment='Account verification status')
    verification_token = db.Column(db.String(100), comment='Email verification token')
    last_active_at = db.Column(db.DateTime, nullable=True, comment='Last activity timestamp')
    contactno = db.Column(db.String(1000), nullable=False)
    profile_img_url = db.Column(db.String(
        1000), nullable=False, default='https://media.istockphoto.com/id/1300845620/vector/user-icon-flat-isolated-on-white-background-user-symbol-vector-illustration.jpg?s=612x612&w=0&k=20&c=yBeyba0hUkh14_jgv1OKqIH0CCSWU_4ckRkAoy2p73o=')
    fullname = db.Column(db.String(100), nullable=False)
    doc = db.Column(db.String(100), nullable=False)
    last_login = db.Column(db.DateTime, nullable=True)
    profile_completed = db.Column(db.Integer, nullable=False, default=0)
    web_login_device = db.Column(db.String(1000), nullable=False, default='')
    logged_in = db.Column(db.Integer, nullable=False, default=0)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    login_type = db.Column(db.String(20), default='email')
    google_id = db.Column(db.String(255), nullable=True)
    preferred_ai_model = db.Column(db.String(100), default='llama-3.3-70b', comment='Default AI model')
    model_selection_synced_at = db.Column(db.DateTime, nullable=True, comment='Last model preference sync')
    ai_tokens_balance = db.Column(db.Numeric(10, 2), default=0.00, comment='Available token balance')
    ai_tokens_purchased = db.Column(db.Numeric(10, 2), default=0.00, comment='Total tokens purchased')
    ai_tokens_consumed = db.Column(db.Numeric(10, 2), default=0.00, comment='Total tokens consumed')

    def to_dict(self):
        return {column.name: getattr(self, column.name) for column in self.__table__.columns}


class Category(db.Model):
    __tablename__ = 'categories'

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String(255), nullable=False)
    description = db.Column(db.Text, default=None)
    created_at = db.Column(db.DateTime, nullable=False,
                           default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, nullable=False,
                           default=datetime.utcnow, onupdate=datetime.utcnow)

    def to_dict(self):
        return {column.name: getattr(self, column.name) for column in self.__table__.columns}


class Instructor(db.Model):
    __tablename__ = 'instructors'

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    # Institute fields (from migration 004)
    institute_id = db.Column(db.Integer, nullable=True, comment='Associated institute')
    user_id = db.Column(db.Integer, nullable=True, comment='Associated user account')
    name = db.Column(db.String(255), nullable=False)
    bio = db.Column(db.Text, default=None)
    # Enhanced instructor fields (from migration 004)
    specializations = db.Column(db.Text, comment='JSON: Teaching specializations')
    qualifications = db.Column(db.Text, comment='JSON: Educational qualifications')
    experience_years = db.Column(db.Integer, default=0, comment='Years of teaching experience')
    hourly_rate = db.Column(db.Numeric(10, 2), comment='Hourly teaching rate')
    availability = db.Column(db.Text, comment='JSON: Teaching availability schedule')
    linkedin_url = db.Column(db.String(255), comment='LinkedIn profile URL')
    github_url = db.Column(db.String(255), comment='GitHub profile URL')
    portfolio_url = db.Column(db.String(255), comment='Portfolio website URL')
    status = db.Column(db.Enum('active', 'inactive', 'pending', 'suspended'), default='pending')
    is_featured = db.Column(db.Boolean, default=False, comment='Featured instructor flag')
    profile_image_url = db.Column(db.String(1000), default=None)
    rating = db.Column(db.Float, default=0)
    total_courses = db.Column(db.Integer, default=0)
    created_at = db.Column(db.DateTime, nullable=False,
                           default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, nullable=False,
                           default=datetime.utcnow, onupdate=datetime.utcnow)

    def to_dict(self):
        return {column.name: getattr(self, column.name) for column in self.__table__.columns}


class Topic(db.Model):
    __tablename__ = 'topics'

    id = db.Column(db.Integer, primary_key=True,
                   autoincrement=True, nullable=False)
    course_id = db.Column(db.Integer, db.ForeignKey(
        'courses.id'), nullable=False)
    title = db.Column(db.String(255), nullable=False)
    description = db.Column(db.Text, nullable=True)
    content_type = db.Column(db.Enum(
        'video', 'article', 'quiz', 'assignment'), nullable=False, default='video')
    content_url = db.Column(db.String(1000), nullable=True)
    order = db.Column(db.Integer, nullable=True)
    duration_minutes = db.Column(db.Integer, nullable=True)
    is_previewable = db.Column(db.Boolean, default=True)
    status = db.Column(db.Enum('active', 'inactive'), default='active')
    # Hierarchy fields (from add_topic_hierarchy.sql)
    parent_topic_id = db.Column(db.Integer, db.ForeignKey('topics.id'), nullable=True, default=None)
    level = db.Column(db.SmallInteger, default=0, comment='Topic hierarchy level (0=main, 1=sub, etc)')
    full_path = db.Column(db.String(500), nullable=True, comment='Full hierarchical path')
    created_at = db.Column(db.TIMESTAMP, nullable=False,
                           server_default=db.func.current_timestamp())
    updated_at = db.Column(
        db.TIMESTAMP,
        nullable=False,
        server_default=db.func.current_timestamp(),
        onupdate=db.func.current_timestamp()
    )

    # Relationships
    questions = db.relationship('Questions', back_populates='topic')
    children = db.relationship('Topic', backref=db.backref('parent', remote_side=[id]), lazy='dynamic')

    def to_dict(self):
        return {column.name: getattr(self, column.name) for column in self.__table__.columns}


class CourseNotesContent(db.Model):
    __tablename__ = 'course_notes_content'

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    topic_id = db.Column(db.Integer, db.ForeignKey('topics.id', ondelete='CASCADE'), nullable=False)
    content = db.Column(db.Text(length=4294967295), nullable=False) # longtext
    markdown_format = db.Column(db.Boolean, default=True)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

    def to_dict(self):
        return {
            'id': self.id,
            'topic_id': self.topic_id,
            'content': self.content,
            'markdown_format': self.markdown_format,
            'created_at': self.created_at.isoformat() if self.created_at else None,
            'updated_at': self.updated_at.isoformat() if self.updated_at else None
        }


class CourseAudioContent(db.Model):
    __tablename__ = 'course_audio_content'

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    topic_id = db.Column(db.Integer, db.ForeignKey('topics.id'), nullable=False)
    title = db.Column(db.String(255), nullable=False)
    file_path = db.Column(db.String(500), nullable=False)
    duration = db.Column(db.Integer, nullable=False, comment='Duration in seconds')
    file_size = db.Column(db.Integer, nullable=False, comment='File size in bytes')
    position = db.Column(db.Integer, default=1, comment='Order/position')
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    language = db.Column(db.String(10), default='English', comment='Audio language')

    def to_dict(self):
        return {column.name: getattr(self, column.name) for column in self.__table__.columns}


class NoteDiscussion(db.Model):
    __tablename__ = 'note_discussions'

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    topic_id = db.Column(db.Integer, db.ForeignKey('topics.id'), nullable=False)
    user_id = db.Column(db.Integer, nullable=False)
    title = db.Column(db.String(255), nullable=False)
    content = db.Column(db.Text, nullable=False)
    is_question = db.Column(db.Boolean, default=False)
    is_resolved = db.Column(db.Boolean, default=False)
    parent_id = db.Column(db.Integer, db.ForeignKey('note_discussions.id'), nullable=True)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

    replies = db.relationship('NoteDiscussion', backref=db.backref('parent_discussion', remote_side=[id]), lazy='dynamic')

    def to_dict(self):
        return {column.name: getattr(self, column.name) for column in self.__table__.columns}


class UserNotesProgress(db.Model):
    __tablename__ = 'user_notes_progress'

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    user_id = db.Column(db.Integer, nullable=False)
    topic_id = db.Column(db.Integer, db.ForeignKey('topics.id'), nullable=False)
    notes_completed = db.Column(db.Boolean, default=False)
    audio_progress = db.Column(db.Integer, default=0, comment='Seconds listened')
    last_accessed = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

    def to_dict(self):
        return {column.name: getattr(self, column.name) for column in self.__table__.columns}


class UserAudioGeneration(db.Model):
    __tablename__ = 'user_audio_generations'

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    user_id = db.Column(db.Integer, nullable=False)
    content = db.Column(db.Text)
    voice = db.Column(db.String(50))
    language = db.Column(db.String(10), default='en-US')
    filename = db.Column(db.String(255))
    file_path = db.Column(db.String(500))
    file_size = db.Column(db.Integer)
    format = db.Column(db.String(10), default='mp3')
    duration = db.Column(db.Integer)
    is_cached = db.Column(db.Boolean, default=False)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)

    def to_dict(self):
        return {column.name: getattr(self, column.name) for column in self.__table__.columns}


class UserAudioPreference(db.Model):
    __tablename__ = 'user_audio_preferences'

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    user_id = db.Column(db.Integer, nullable=False)
    preferred_language = db.Column(db.String(10), default='English')
    auto_switch_language = db.Column(db.Boolean, default=False, comment='Auto switch to available language')
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

    def to_dict(self):
        return {column.name: getattr(self, column.name) for column in self.__table__.columns}
