from flask import Flask, jsonify, render_template, request, redirect, url_for
from flask_sqlalchemy import SQLAlchemy
import os
from werkzeug.utils import secure_filename

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = "postgresql://neondb_owner:npg_vz5FLSXfj2sp@ep-late-block-adp1o88t-pooler.c-2.us-east-1.aws.neon.tech/neondb?sslmode=require&channel_binding=require"
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False


app.config['SQLALCHEMY_ENGINE_OPTIONS'] = {
    "pool_pre_ping": True,
    "pool_recycle": 240,
}
# ---------------------------

UPLOAD_FOLDER = 'static/uploads'
app.config['UPLOAD_FOLDER'] = UPLOAD_FOLDER
if not os.path.exists(UPLOAD_FOLDER):
    os.makedirs(UPLOAD_FOLDER)
db = SQLAlchemy(app)

class Subject(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)
    icon_data = db.Column(db.LargeBinary, nullable=True)  # Image data stored in DB
    icon_filename = db.Column(db.String(200), nullable=True)  # Original filename
    icon_mimetype = db.Column(db.String(100), nullable=True)  # MIME type
    categories = db.relationship('Category', backref='subject', lazy=True)

class Category(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)
    subject_id = db.Column(db.Integer, db.ForeignKey('subject.id'), nullable=False)
    articles = db.relationship('Article', backref='category', lazy=True)

class Article(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(200), nullable=False)
    content = db.Column(db.Text, nullable=False)
    category_id = db.Column(db.Integer, db.ForeignKey('category.id'), nullable=False)
    icon_data = db.Column(db.LargeBinary, nullable=True)  # Image data stored in DB
    icon_filename = db.Column(db.String(200), nullable=True)  # Original filename
    icon_mimetype = db.Column(db.String(100), nullable=True)  # MIME type
    youtube_url = db.Column(db.String(300), nullable=True)  # Optional YouTube URL

class Attachment(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    data = db.Column(db.LargeBinary, nullable=False)
    filename = db.Column(db.String(200), nullable=False)
    mimetype = db.Column(db.String(100), nullable=False)

@app.route('/')
def home():
    subjects = Subject.query.all()
    # Add icon_url to each subject for template use
    for subject in subjects:
        if subject.icon_data:
            subject.icon_url = url_for('get_subject_icon', subject_id=subject.id)
        else:
            subject.icon_url = None
    return render_template('home.html', subjects=subjects)

@app.route('/subjects/<int:subject_id>/categories')
def categories(subject_id):
    subject = Subject.query.get_or_404(subject_id)
    categories = Category.query.filter_by(subject_id=subject_id).all()
    return render_template('categories.html', subject=subject, categories=categories)

@app.route('/categories/<int:category_id>/articles')
def articles(category_id):
    category = Category.query.get_or_404(category_id)
    articles = Article.query.filter_by(category_id=category_id).all()
    return render_template('articles.html', category=category, articles=articles)

@app.route('/articles/<int:article_id>')
def article(article_id):
    article = Article.query.get_or_404(article_id)
    return render_template('article.html', article=article)

# API endpoints for JSON if needed
@app.route('/api/subjects')
def api_subjects():
    subjects = Subject.query.all()
    subject_list = []
    for s in subjects:
        icon_url = url_for('get_subject_icon', subject_id=s.id) if s.icon_data else None
        subject_list.append({'id': s.id, 'name': s.name, 'icon': icon_url})
    return jsonify(subject_list)

@app.route('/api/subjects/<int:subject_id>/categories')
def api_categories(subject_id):
    categories = Category.query.filter_by(subject_id=subject_id).all()
    return jsonify([{'id': c.id, 'name': c.name} for c in categories])

@app.route('/api/categories/<int:category_id>/articles')
def api_articles(category_id):
    articles = Article.query.filter_by(category_id=category_id).all()
    return jsonify([{'id': a.id, 'title': a.title} for a in articles])

@app.route('/api/articles/<int:article_id>')
def api_article(article_id):
    article = Article.query.get_or_404(article_id)
    return jsonify({'id': article.id, 'title': article.title, 'content': article.content})

# Admin routes
@app.route('/admin')
def admin_home():
    return render_template('admin_home.html')

@app.route('/admin/articles')
def admin_articles():
    articles = Article.query.all()
    return render_template('admin_articles.html', articles=articles)

@app.route('/admin/articles/edit/<int:article_id>', methods=['GET', 'POST'])
def admin_edit_article(article_id):
    article = Article.query.get_or_404(article_id)
    if request.method == 'POST':
        article.title = request.form['title']
        article.content = request.form['content']
        # store optional youtube url
        article.youtube_url = request.form.get('youtube_url') or None

        # Handle file upload if provided
        if 'icon' in request.files and request.files['icon'].filename:
            file = request.files['icon']
            file_data = file.read()
            filename = secure_filename(file.filename)
            article.icon_data = file_data
            article.icon_filename = filename
            article.icon_mimetype = file.mimetype

        db.session.commit()
        return redirect(url_for('admin_articles'))
    return render_template('admin_edit_article.html', article=article)

@app.route('/admin/articles/new', methods=['GET', 'POST'])
def admin_new_article():
    if request.method == 'POST':
        title = request.form['title']
        content = request.form['content']
        category_id = request.form['category_id']
        new_article = Article(title=title, content=content, category_id=category_id, youtube_url=(request.form.get('youtube_url') or None))
        db.session.add(new_article)
        db.session.commit()

        # Handle file upload if provided
        if 'icon' in request.files and request.files['icon'].filename:
            file = request.files['icon']
            file_data = file.read()
            filename = secure_filename(file.filename)
            new_article.icon_data = file_data
            new_article.icon_filename = filename
            new_article.icon_mimetype = file.mimetype
            db.session.commit()

        return redirect(url_for('admin_articles'))
    categories = Category.query.all()
    return render_template('admin_new_article.html', categories=categories)

@app.route('/admin/articles/delete/<int:article_id>', methods=['POST'])
def admin_delete_article(article_id):
    article = Article.query.get_or_404(article_id)
    db.session.delete(article)
    db.session.commit()
    return redirect(url_for('admin_articles'))

@app.route('/admin/subjects')
def admin_subjects():
    subjects = Subject.query.all()
    return render_template('admin_subjects.html', subjects=subjects)

@app.route('/admin/subjects/new', methods=['GET', 'POST'])
def admin_new_subject():
    if request.method == 'POST':
        name = request.form['name']
        new_subject = Subject(name=name)
        db.session.add(new_subject)
        db.session.commit()

        # Handle file upload if provided
        if 'icon' in request.files and request.files['icon'].filename:
            file = request.files['icon']
            file_data = file.read()
            filename = secure_filename(file.filename)
            new_subject.icon_data = file_data
            new_subject.icon_filename = filename
            new_subject.icon_mimetype = file.mimetype
            db.session.commit()

        return redirect(url_for('admin_subjects'))
    return render_template('admin_new_subject.html')

@app.route('/admin/subjects/edit/<int:subject_id>', methods=['GET', 'POST'])
def admin_edit_subject(subject_id):
    subject = Subject.query.get_or_404(subject_id)
    if request.method == 'POST':
        subject.name = request.form['name']

        # Handle file upload if provided
        if 'icon' in request.files and request.files['icon'].filename:
            file = request.files['icon']
            file_data = file.read()
            filename = secure_filename(file.filename)
            subject.icon_data = file_data
            subject.icon_filename = filename
            subject.icon_mimetype = file.mimetype

        db.session.commit()
        return redirect(url_for('admin_subjects'))
    return render_template('admin_edit_subject.html', subject=subject)

@app.route('/admin/subjects/delete/<int:subject_id>', methods=['POST'])
def admin_delete_subject(subject_id):
    subject = Subject.query.get_or_404(subject_id)
    # Supprimer toutes les catégories et articles associés au sujet
    categories = Category.query.filter_by(subject_id=subject_id).all()
    for category in categories:
        Article.query.filter_by(category_id=category.id).delete()
        db.session.delete(category)
    db.session.delete(subject)
    db.session.commit()
    return redirect(url_for('admin_subjects'))

@app.route('/admin/categories')
def admin_categories():
    categories = Category.query.all()
    return render_template('admin_categories.html', categories=categories)

@app.route('/admin/categories/new', methods=['GET', 'POST'])
def admin_new_category():
    if request.method == 'POST':
        name = request.form['name']
        subject_id = request.form['subject_id']
        new_category = Category(name=name, subject_id=subject_id)
        db.session.add(new_category)
        db.session.commit()
        return redirect(url_for('admin_categories'))
    subjects = Subject.query.all()
    return render_template('admin_new_category.html', subjects=subjects)

@app.route('/admin/categories/edit/<int:category_id>', methods=['GET', 'POST'])
def admin_edit_category(category_id):
    category = Category.query.get_or_404(category_id)
    if request.method == 'POST':
        category.name = request.form['name']
        category.subject_id = request.form['subject_id']
        db.session.commit()
        return redirect(url_for('admin_categories'))
    subjects = Subject.query.all()
    return render_template('admin_edit_category.html', category=category, subjects=subjects)

@app.route('/admin/categories/delete/<int:category_id>', methods=['POST'])
def admin_delete_category(category_id):
    category = Category.query.get_or_404(category_id)
    # Supprimer tous les articles de cette catégorie d'abord
    Article.query.filter_by(category_id=category_id).delete()
    db.session.delete(category)
    db.session.commit()
    return redirect(url_for('admin_categories'))

def add_sample_data():
    if Subject.query.count() == 0:
        math = Subject(name="Mathématiques")
        physics = Subject(name="Physique")
        db.session.add(math)
        db.session.add(physics)
        db.session.commit()

        algebra = Category(name="Algèbre", subject=math)
        geometry = Category(name="Géométrie", subject=math)
        mechanics = Category(name="Mécanique", subject=physics)
        db.session.add(algebra)
        db.session.add(geometry)
        db.session.add(mechanics)
        db.session.commit()

        Article(title="Équations linéaires", content="<p>Les équations linéaires sont de la forme ax + b = 0.</p>", category=algebra)
        Article(title="Triangles", content="<p>Un triangle a trois côtés.</p>", category=geometry)
        Article(title="Lois de Newton", content="<p>La première loi de Newton...</p>", category=mechanics)
        db.session.commit()


# Helper to convert a standard YouTube URL into an embeddable URL
from urllib.parse import urlparse, parse_qs
import re

def get_youtube_embed(url):
    if not url:
        return None
    try:
        parsed = urlparse(url)
        hostname = parsed.hostname or ''
        video_id = None
        if hostname.endswith('youtu.be'):
            video_id = parsed.path.lstrip('/')
        elif 'youtube' in hostname:
            # check query param v
            qs = parse_qs(parsed.query)
            video_id = qs.get('v', [None])[0]
            if not video_id:
                # maybe already an embed path
                parts = parsed.path.split('/')
                if 'embed' in parts:
                    idx = parts.index('embed')
                    if len(parts) > idx + 1:
                        video_id = parts[idx + 1]
        if not video_id:
            return None
        return f'https://www.youtube.com/embed/{video_id}'
    except Exception:
        return None


@app.template_filter('youtube_embed')
def youtube_embed_filter(url):
    return get_youtube_embed(url)


def render_embeds(content):
    if not content:
        return content
    # replace shortcodes like [youtube:URL] and [image:URL|opts]
    pattern = re.compile(r"\[(?P<tag>youtube|image):(?P<url>[^\]|]+)(?:\|(?P<opts>[^\]]+))?\]")

    def _repl(m):
        tag = m.group('tag')
        url = m.group('url').strip()
        opts = m.group('opts')

        # parse options like "left", "right", "width=300", "height=200"
        opt_map = {}
        if opts:
            parts = re.split(r"[\|,]", opts)
            for p in parts:
                p = p.strip()
                if not p:
                    continue
                if '=' in p:
                    k, v = p.split('=', 1)
                    opt_map[k.strip().lower()] = v.strip()
                else:
                    # shorthand for alignment
                    if p.lower() in ('left', 'right', 'center'):
                        opt_map['align'] = p.lower()
                    elif p.endswith('%') or p.isdigit():
                        opt_map['width'] = p
                    else:
                        # unknown token; ignore
                        pass

        if tag == 'youtube':
            embed = get_youtube_embed(url)
            if embed:
                attrs = 'width="100%" height="480"'
                return f'<iframe {attrs} src="{embed}" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture" allowfullscreen></iframe>'
            return m.group(0)

        if tag == 'image':
            # build img tag with optional attributes
            classes = []
            styles = []
            attr_list = []
            if 'align' in opt_map:
                if opt_map['align'] == 'left':
                    classes.append('float-left')
                elif opt_map['align'] == 'right':
                    classes.append('float-right')
                elif opt_map['align'] == 'center':
                    styles.append('display:block;margin-left:auto;margin-right:auto')
            if 'width' in opt_map:
                w = opt_map['width']
                # allow percent or number
                if w.endswith('%'):
                    styles.append(f'width:{w}')
                else:
                    # assume pixels
                    if w.isdigit():
                        attr_list.append(f'width="{w}"')
            if 'height' in opt_map:
                h = opt_map['height']
                if h.isdigit():
                    attr_list.append(f'height="{h}"')

            class_attr = f' class="{" ".join(classes)}"' if classes else ''
            style_attr = f' style="{";".join(styles)}"' if styles else ''
            attrs = ' '.join(attr_list)
            return f'<img src="{url}" alt=""{class_attr}{style_attr} {attrs} />'

        return m.group(0)

    return pattern.sub(_repl, content)


@app.template_filter('render_embeds')
def render_embeds_filter(content):
    return render_embeds(content)

@app.route('/upload/subject/<int:subject_id>', methods=['POST'])
def upload_subject_icon(subject_id):
    subject = Subject.query.get_or_404(subject_id)
    if 'file' not in request.files:
        return jsonify({'error': 'No file'}), 400
    file = request.files['file']
    if file.filename == '':
        return jsonify({'error': 'No selected file'}), 400

    # Read file data
    file_data = file.read()
    filename = secure_filename(file.filename)

    # Store in database
    subject.icon_data = file_data
    subject.icon_filename = filename
    subject.icon_mimetype = file.mimetype
    db.session.commit()

    return jsonify({'success': True, 'filename': filename})

@app.route('/upload/article/<int:article_id>', methods=['POST'])
def upload_article_image(article_id):
    article = Article.query.get_or_404(article_id)
    if 'file' not in request.files:
        return jsonify({'error': 'No file'}), 400
    file = request.files['file']
    if file.filename == '':
        return jsonify({'error': 'No selected file'}), 400

    # Read file data
    file_data = file.read()
    filename = secure_filename(file.filename)

    # Store in database
    article.icon_data = file_data
    article.icon_filename = filename
    article.icon_mimetype = file.mimetype
    db.session.commit()

    return jsonify({'success': True, 'filename': filename})

@app.route('/upload', methods=['POST'])
def upload_attachment():
    if 'file' not in request.files:
        return jsonify({'error': 'No file'}), 400
    file = request.files['file']
    if file.filename == '':
        return jsonify({'error': 'No selected file'}), 400

    # Read file data
    file_data = file.read()
    filename = secure_filename(file.filename)

    # Store in database
    attachment = Attachment(data=file_data, filename=filename, mimetype=file.mimetype)
    db.session.add(attachment)
    db.session.commit()

    url = url_for('get_attachment', attachment_id=attachment.id)
    return jsonify({'url': url})

@app.route('/subject/<int:subject_id>/icon')
def get_subject_icon(subject_id):
    subject = Subject.query.get_or_404(subject_id)
    if subject.icon_data:
        return subject.icon_data, 200, {'Content-Type': subject.icon_mimetype or 'image/png'}
    else:
        return '', 404

@app.route('/article/<int:article_id>/image')
def get_article_image(article_id):
    article = Article.query.get_or_404(article_id)
    if article.icon_data:
        return article.icon_data, 200, {'Content-Type': article.icon_mimetype or 'image/png'}
    else:
        return '', 404

@app.route('/attachment/<int:attachment_id>')
def get_attachment(attachment_id):
    attachment = Attachment.query.get_or_404(attachment_id)
    return attachment.data, 200, {'Content-Type': attachment.mimetype}

if __name__ == '__main__':
    with app.app_context():
        # create tables if they don't exist
        db.create_all()

        # Attempt a tiny, safe migration for SQLite: if the youtube_url column
        # is missing on the articles table, add it. This avoids OperationalError
        # when the DB was created before the model change.
        try:
            from sqlalchemy import inspect, text
            inspector = inspect(db.engine)
            table_name = Article.__table__.name
            existing_cols = [c['name'] for c in inspector.get_columns(table_name)]
            if 'youtube_url' not in existing_cols:
                with db.engine.connect() as conn:
                    # SQLite allows simple ALTER TABLE ADD COLUMN
                    conn.execute(text("ALTER TABLE %s ADD COLUMN youtube_url VARCHAR(300)" % table_name))
                    conn.commit()
                    print('Migration: added youtube_url column to', table_name)
        except Exception as e:
            # If anything goes wrong, log and continue; user can perform a manual migration
            print('Migration skipped or failed:', e)

        
    app.run(debug=True)