# routes/project_routes.py - Project Management Routes (v4.3)

import re
import os
import json
import base64
from flask import Blueprint, request, jsonify, send_file

from db import get_db, vacuum_db, get_db_stats
from auth import login_required
from media_storage import (
    save_audio, save_image, save_thumbnail,
    read_file_base64, get_safe_abs_path,
    delete_project_media, get_storage_usage_bytes,
    read_pending_thumbnail, delete_pending_thumbnail  # v4.4
)
from thumbnail_generator import generate_text_cover  # v4.4 backfill

project_bp = Blueprint('project', __name__)


# ============================================
# Helpers
# ============================================

_CONTROL_CHAR_RE = re.compile(r'[\x00-\x08\x0B\x0C\x0E-\x1F\x7F]')


def clean_str(s):
    if s is None:
        return ''
    if not isinstance(s, str):
        s = str(s)
    return _CONTROL_CHAR_RE.sub('', s)


def clean_transcription(transcription):
    if isinstance(transcription, list):
        for t in transcription:
            if isinstance(t, dict) and 'word' in t:
                t['word'] = clean_str(t.get('word', ''))
    return transcription


# ============================================
# Routes
# ============================================

@project_bp.route('/api/projects', methods=['GET'])
@login_required
def list_projects():
    """List all projects with publishing info."""
    db = get_db()
    cursor = db.cursor()
    
    cursor.execute('''
        SELECT p.id, p.name, p.created_at, p.updated_at,
               p.is_published, p.published_at, p.thumbnail_data, p.thumbnail_format,
               p.thumbnail_path,
               p.description, p.author, p.category, p.view_count,
               (SELECT COUNT(*) FROM chapters WHERE project_id = p.id) as chapter_count,
               (SELECT COUNT(*) FROM markdown_blocks mb 
                JOIN chapters c ON mb.chapter_id = c.id 
                WHERE c.project_id = p.id) as block_count,
               (SELECT COUNT(*) FROM markdown_blocks mb 
                JOIN chapters c ON mb.chapter_id = c.id 
                WHERE c.project_id = p.id 
                AND ((mb.audio_data IS NOT NULL AND mb.audio_data != '')
                     OR (mb.audio_path IS NOT NULL AND mb.audio_path != ''))) as audio_count
        FROM projects p
        ORDER BY p.updated_at DESC
    ''')
    
    projects = []
    for row in cursor.fetchall():
        # thumbnail: path থাকলে ফাইল থেকে, নইলে পুরোনো base64
        thumb_data = row['thumbnail_data']
        if row['thumbnail_path']:
            thumb_data = read_file_base64(row['thumbnail_path'])
        
        projects.append({
            'id': row['id'],
            'name': row['name'],
            'created_at': row['created_at'],
            'updated_at': row['updated_at'],
            'chapter_count': row['chapter_count'],
            'block_count': row['block_count'],
            'audio_count': row['audio_count'],
            'is_published': bool(row['is_published']),
            'published_at': row['published_at'],
            'thumbnail_data': thumb_data,
            'thumbnail_format': row['thumbnail_format'] or 'png',
            'description': row['description'] or '',
            'author': row['author'] or '',
            'category': row['category'] or '',
            'view_count': row['view_count'] or 0
        })
    
    return jsonify({'projects': projects})


@project_bp.route('/api/projects', methods=['POST'])
@login_required
def create_project():
    """Create a new project."""
    data = request.json
    name = data.get('name', '').strip()
    
    if not name:
        return jsonify({'error': 'Project name is required'}), 400
    
    db = get_db()
    cursor = db.cursor()
    
    try:
        cursor.execute('INSERT INTO projects (name) VALUES (?)', (name,))
        db.commit()
        
        return jsonify({
            'success': True,
            'project_id': cursor.lastrowid,
            'name': name
        })
    except Exception as e:
        if 'UNIQUE constraint' in str(e):
            return jsonify({'error': 'Project with this name already exists'}), 400
        return jsonify({'error': str(e)}), 500


@project_bp.route('/api/projects/<int:project_id>', methods=['GET'])
@login_required
def get_project(project_id):
    """
    Get project metadata WITHOUT audio_data (lazy-loaded separately).
    Images served as base64 from files (editor compatibility).
    """
    db = get_db()
    cursor = db.cursor()
    
    cursor.execute('SELECT * FROM projects WHERE id = ?', (project_id,))
    project = cursor.fetchone()
    
    if not project:
        return jsonify({'error': 'Project not found'}), 404
    
    cursor.execute('''
        SELECT * FROM chapters WHERE project_id = ? ORDER BY chapter_number
    ''', (project_id,))
    chapters = cursor.fetchall()
    
    chapters_data = []
    for chapter in chapters:
        cursor.execute('''
            SELECT id, block_order, block_type, content, tts_text,
                   audio_format, audio_path, transcription,
                   ((audio_data IS NOT NULL AND audio_data != '') 
                    OR (audio_path IS NOT NULL AND audio_path != '')) as has_audio
            FROM markdown_blocks WHERE chapter_id = ? ORDER BY block_order
        ''', (chapter['id'],))
        blocks = cursor.fetchall()
        
        blocks_data = []
        for block in blocks:
            cursor.execute('''
                SELECT id, image_data, image_format, alt_text, position, image_path
                FROM block_images WHERE block_id = ? ORDER BY id
            ''', (block['id'],))
            images = cursor.fetchall()
            
            transcription = []
            if block['transcription']:
                try:
                    transcription = json.loads(block['transcription'])
                    transcription = clean_transcription(transcription)
                except (json.JSONDecodeError, TypeError):
                    transcription = []
            
            images_data = []
            for img in images:
                # path থাকলে ফাইল থেকে, নইলে পুরোনো base64
                img_data = ''
                if img['image_path']:
                    img_data = read_file_base64(img['image_path'])
                elif img['image_data']:
                    img_data = clean_str(img['image_data'])
                images_data.append({
                    'id': img['id'],
                    'data': img_data,
                    'format': clean_str(img['image_format']) or 'png',
                    'alt_text': clean_str(img['alt_text']),
                    'position': clean_str(img['position']) or 'before'
                })
            
            blocks_data.append({
                'id': block['id'],
                'block_order': block['block_order'],
                'block_type': clean_str(block['block_type']),
                'content': clean_str(block['content']),
                'tts_text': clean_str(block['tts_text']),
                'audio_data': '',
                'audio_format': clean_str(block['audio_format']) or 'mp3',
                'has_audio': bool(block['has_audio']),
                'transcription': transcription,
                'images': images_data
            })
        
        chapters_data.append({
            'id': chapter['id'],
            'chapter_number': chapter['chapter_number'],
            'title': clean_str(chapter['title']),
            'voice': clean_str(chapter['voice']),
            'blocks': blocks_data
        })
    
    return jsonify({
        'id': project['id'],
        'name': clean_str(project['name']),
        'created_at': clean_str(project['created_at']),
        'updated_at': clean_str(project['updated_at']),
        'chapters': chapters_data
    })


@project_bp.route('/api/projects/<int:project_id>/audio/<int:block_id>', methods=['GET'])
@login_required
def get_block_audio(project_id, block_id):
    """Return audio as base64 JSON (v4.3: read from file, no send_file/Range)."""
    db = get_db()
    cursor = db.cursor()
    
    cursor.execute('''
        SELECT mb.audio_data, mb.audio_path, mb.audio_format
        FROM markdown_blocks mb
        JOIN chapters c ON mb.chapter_id = c.id
        WHERE mb.id = ? AND c.project_id = ?
    ''', (block_id, project_id))
    row = cursor.fetchone()
    
    if not row:
        return jsonify({'error': 'Block not found'}), 404
    
    audio_format = clean_str(row['audio_format']) or 'mp3'
    
    # নতুন: ফাইল থেকে পড়ে base64 হিসেবে পাঠাই (gunicorn Range সমস্যা এড়াতে)
    if row['audio_path']:
        from media_storage import read_file_base64
        b64 = read_file_base64(row['audio_path'])
        if b64:
            return jsonify({'audio_data': b64, 'audio_format': audio_format})
    
    # পুরোনো: DB-তে থাকা base64 (মাইগ্রেট না হওয়া)
    if row['audio_data']:
        return jsonify({
            'audio_data': clean_str(row['audio_data']),
            'audio_format': audio_format
        })
    
    return jsonify({'audio_data': '', 'audio_format': audio_format})


@project_bp.route('/api/projects/<int:project_id>', methods=['PUT'])
@login_required
def update_project(project_id):
    """Update project name and metadata (author/description/category)."""
    data = request.json
    name = data.get('name', '').strip()
    
    if not name:
        return jsonify({'error': 'Project name is required'}), 400
    
    db = get_db()
    cursor = db.cursor()
    
    # মেটাডেটা ফিল্ড — পাঠানো হলেই আপডেট হবে
    updates = ['name = ?']
    params = [name]
    
    if 'author' in data:
        updates.append('author = ?')
        params.append((data.get('author') or '').strip())
    if 'description' in data:
        updates.append('description = ?')
        params.append((data.get('description') or '').strip())
    if 'category' in data:
        updates.append('category = ?')
        params.append((data.get('category') or '').strip())
    
    updates.append('updated_at = CURRENT_TIMESTAMP')
    params.append(project_id)
    
    try:
        cursor.execute(
            f"UPDATE projects SET {', '.join(updates)} WHERE id = ?",
            params
        )
        db.commit()
        
        if cursor.rowcount == 0:
            return jsonify({'error': 'Project not found'}), 404
        
        return jsonify({'success': True})
    except Exception as e:
        if 'UNIQUE constraint' in str(e):
            return jsonify({'error': 'A project with this name already exists'}), 400
        return jsonify({'error': str(e)}), 500


@project_bp.route('/api/projects/<int:project_id>', methods=['DELETE'])
@login_required
def delete_project(project_id):
    """Delete a project, all DB data, AND its media folder (v4.3, no auto-vacuum)."""
    db = get_db()
    cursor = db.cursor()
    
    cursor.execute('SELECT id FROM projects WHERE id = ?', (project_id,))
    if not cursor.fetchone():
        return jsonify({'error': 'Project not found'}), 404
    
    cursor.execute('''
        DELETE FROM block_images WHERE block_id IN (
            SELECT mb.id FROM markdown_blocks mb
            JOIN chapters c ON mb.chapter_id = c.id
            WHERE c.project_id = ?
        )
    ''', (project_id,))
    
    cursor.execute('''
        DELETE FROM markdown_blocks WHERE chapter_id IN (
            SELECT id FROM chapters WHERE project_id = ?
        )
    ''', (project_id,))
    
    cursor.execute('DELETE FROM chapters WHERE project_id = ?', (project_id,))
    cursor.execute('DELETE FROM projects WHERE id = ?', (project_id,))
    
    db.commit()
    
    # v4.3: প্রজেক্টের সব মিডিয়া ফাইল মুছি
    delete_project_media(project_id)
    
    # NOTE: vacuum আর অটোমেটিক চলে না — ইউজার সেটিংস থেকে ম্যানুয়ালি করবে
    
    return jsonify({'success': True})


@project_bp.route('/api/projects/<int:project_id>/save', methods=['POST'])
@login_required
def save_project_content(project_id):
    """Save all chapters and blocks. Audio/images stored as FILES (v4.3)."""
    data = request.json
    chapters = data.get('chapters', [])
    
    db = get_db()
    cursor = db.cursor()
    
    cursor.execute('SELECT id FROM projects WHERE id = ?', (project_id,))
    if not cursor.fetchone():
        return jsonify({'error': 'Project not found'}), 404
    
    # পুরোনো DB রেকর্ড মুছি (ফাইলগুলো নতুন করে লেখা হবে)
    cursor.execute('''
        DELETE FROM block_images WHERE block_id IN (
            SELECT mb.id FROM markdown_blocks mb
            JOIN chapters c ON mb.chapter_id = c.id
            WHERE c.project_id = ?
        )
    ''', (project_id,))
    
    cursor.execute('''
        DELETE FROM markdown_blocks WHERE chapter_id IN (
            SELECT id FROM chapters WHERE project_id = ?
        )
    ''', (project_id,))
    
    cursor.execute('DELETE FROM chapters WHERE project_id = ?', (project_id,))
    
    for chapter in chapters:
        cursor.execute('''
            INSERT INTO chapters (project_id, chapter_number, title, voice)
            VALUES (?, ?, ?, ?)
        ''', (
            project_id,
            chapter['chapter_number'],
            clean_str(chapter.get('title', 'Section')),
            clean_str(chapter.get('voice', 'af_heart'))
        ))
        
        chapter_id = cursor.lastrowid
        
        for block in chapter.get('blocks', []):
            transcription = clean_transcription(block.get('transcription', []))
            audio_format = clean_str(block.get('audio_format', 'mp3')) or 'mp3'
            
            cursor.execute('''
                INSERT INTO markdown_blocks 
                (chapter_id, block_order, block_type, content, tts_text, 
                 audio_data, audio_path, audio_format, transcription)
                VALUES (?, ?, ?, ?, ?, '', NULL, ?, ?)
            ''', (
                chapter_id,
                block['block_order'],
                clean_str(block.get('block_type', 'paragraph')),
                clean_str(block.get('content', '')),
                clean_str(block.get('tts_text', '')),
                audio_format,
                json.dumps(transcription)
            ))
            
            block_id = cursor.lastrowid
            
            # অডিও ফাইলে সেভ করি
            audio_b64 = block.get('audio_data', '')
            if audio_b64:
                rel_path = save_audio(project_id, block_id, audio_b64, audio_format)
                if rel_path:
                    cursor.execute(
                        'UPDATE markdown_blocks SET audio_path = ? WHERE id = ?',
                        (rel_path, block_id)
                    )
            
            # ইমেজগুলো ফাইলে সেভ করি
            for img in block.get('images', []):
                img_format = clean_str(img.get('format', 'png')) or 'png'
                cursor.execute('''
                    INSERT INTO block_images 
                    (block_id, image_data, image_path, image_format, alt_text, position)
                    VALUES (?, '', NULL, ?, ?, ?)
                ''', (
                    block_id,
                    img_format,
                    clean_str(img.get('alt_text', '')),
                    clean_str(img.get('position', 'before'))
                ))
                image_id = cursor.lastrowid
                
                img_b64 = img.get('data', '')
                if img_b64:
                    img_rel = save_image(project_id, image_id, img_b64, img_format)
                    if img_rel:
                        cursor.execute(
                            'UPDATE block_images SET image_path = ? WHERE id = ?',
                            (img_rel, image_id)
                        )
    
    # --- v4.4: pending auto-thumbnail commit করা (শুধু যদি প্রজেক্টে থাম্বনেইল না থাকে) ---
    pending_token = clean_str(data.get('pending_thumbnail', ''))
    if pending_token:
        cursor.execute(
            'SELECT thumbnail_path, thumbnail_data FROM projects WHERE id = ?',
            (project_id,)
        )
        prow = cursor.fetchone()
        already_has_thumb = prow and (prow['thumbnail_path'] or prow['thumbnail_data'])
        
        if not already_has_thumb:
            thumb_bytes, thumb_fmt = read_pending_thumbnail(pending_token)
            if thumb_bytes:
                rel_path = save_thumbnail(project_id, thumb_bytes, thumb_fmt)
                if rel_path:
                    cursor.execute('''
                        UPDATE projects 
                        SET thumbnail_path = ?, thumbnail_data = NULL, thumbnail_format = ?,
                            thumbnail_auto = 1
                        WHERE id = ?
                    ''', (rel_path, thumb_fmt, project_id))
                    print(f"  🖼️ Auto-thumbnail applied to project {project_id}")
        
        # commit বা বাতিল — যেভাবেই হোক pending ফাইল মুছে ফেলি
        delete_pending_thumbnail(pending_token)
    
    cursor.execute('''
        UPDATE projects SET updated_at = CURRENT_TIMESTAMP WHERE id = ?
    ''', (project_id,))
    
    db.commit()
    
    return jsonify({'success': True, 'message': 'Project saved successfully'})


# ============================================
# v4.2: Publishing Endpoints
# ============================================

@project_bp.route('/api/projects/<int:project_id>/publish', methods=['POST'])
@login_required
def publish_project(project_id):
    """Publish a project to public homepage."""
    data = request.json or {}
    
    db = get_db()
    cursor = db.cursor()
    
    cursor.execute('SELECT id, name FROM projects WHERE id = ?', (project_id,))
    project = cursor.fetchone()
    if not project:
        return jsonify({'error': 'Project not found'}), 404
    
    cursor.execute('''
        SELECT COUNT(*) as cnt FROM markdown_blocks mb
        JOIN chapters c ON mb.chapter_id = c.id
        WHERE c.project_id = ? 
        AND ((mb.audio_data IS NOT NULL AND mb.audio_data != '')
             OR (mb.audio_path IS NOT NULL AND mb.audio_path != ''))
    ''', (project_id,))
    audio_count = cursor.fetchone()['cnt']
    
    if audio_count == 0:
        return jsonify({'error': 'Cannot publish: no audio generated yet'}), 400
    
    description = (data.get('description') or '').strip()
    author = (data.get('author') or '').strip()
    category = (data.get('category') or '').strip()
    
    cursor.execute('''
        UPDATE projects 
        SET is_published = 1, 
            published_at = CURRENT_TIMESTAMP,
            description = ?,
            author = ?,
            category = ?
        WHERE id = ?
    ''', (description, author, category, project_id))
    db.commit()
    
    return jsonify({
        'success': True,
        'message': f'"{project["name"]}" published successfully!'
    })


@project_bp.route('/api/projects/<int:project_id>/unpublish', methods=['POST'])
@login_required
def unpublish_project(project_id):
    """Unpublish a project."""
    db = get_db()
    cursor = db.cursor()
    
    cursor.execute('SELECT id FROM projects WHERE id = ?', (project_id,))
    if not cursor.fetchone():
        return jsonify({'error': 'Project not found'}), 404
    
    cursor.execute('UPDATE projects SET is_published = 0 WHERE id = ?', (project_id,))
    db.commit()
    
    return jsonify({'success': True, 'message': 'Project unpublished'})


@project_bp.route('/api/projects/<int:project_id>/thumbnail', methods=['POST'])
@login_required
def upload_thumbnail(project_id):
    """Upload a thumbnail image (v4.3: stored as file)."""
    if 'file' not in request.files:
        return jsonify({'error': 'No file provided'}), 400
    
    img_file = request.files['file']
    if not img_file or not img_file.filename:
        return jsonify({'error': 'Invalid file'}), 400
    
    filename = img_file.filename.lower()
    if not any(filename.endswith(ext) for ext in ('.png', '.jpg', '.jpeg', '.webp', '.gif')):
        return jsonify({'error': 'File must be an image (PNG/JPG/WEBP/GIF)'}), 400
    
    img_bytes = img_file.read()
    if len(img_bytes) > 5 * 1024 * 1024:
        return jsonify({'error': 'Image too large (max 5MB)'}), 400
    
    fmt = filename.rsplit('.', 1)[-1]
    if fmt == 'jpg':
        fmt = 'jpeg'
    
    db = get_db()
    cursor = db.cursor()
    cursor.execute('SELECT id FROM projects WHERE id = ?', (project_id,))
    if not cursor.fetchone():
        return jsonify({'error': 'Project not found'}), 404
    
    rel_path = save_thumbnail(project_id, img_bytes, fmt)
    
    cursor.execute('''
        UPDATE projects SET thumbnail_path = ?, thumbnail_data = NULL, thumbnail_format = ?,
            thumbnail_auto = 0
        WHERE id = ?
    ''', (rel_path, fmt, project_id))
    db.commit()
    
    b64 = read_file_base64(rel_path)
    return jsonify({
        'success': True,
        'thumbnail_data': b64,
        'thumbnail_format': fmt
    })


@project_bp.route('/api/projects/<int:project_id>/thumbnail', methods=['DELETE'])
@login_required
def delete_thumbnail(project_id):
    """Remove project thumbnail (DB + file)."""
    db = get_db()
    cursor = db.cursor()
    cursor.execute('SELECT thumbnail_path FROM projects WHERE id = ?', (project_id,))
    row = cursor.fetchone()
    if row and row['thumbnail_path']:
        from media_storage import delete_file
        delete_file(row['thumbnail_path'])
    cursor.execute('UPDATE projects SET thumbnail_data = NULL, thumbnail_path = NULL WHERE id = ?',
                   (project_id,))
    db.commit()
    return jsonify({'success': True})


# ============================================
# v4.3: Database Maintenance (VACUUM + stats)
# ============================================

@project_bp.route('/api/projects/<int:project_id>/generate-thumbnail', methods=['POST'])
@login_required
def generate_single_thumbnail(project_id):
    """
    একটি নির্দিষ্ট প্রজেক্টের থাম্বনেইল auto-generate/regenerate করে (v4.4)।
    সোর্স: (১) প্রথম embedded image block, (২) fallback হিসেবে text-cover।
    """
    import base64
    from thumbnail_generator import _optimize_image_bytes

    db = get_db()
    cursor = db.cursor()

    cursor.execute('SELECT id, name, author FROM projects WHERE id = ?', (project_id,))
    proj = cursor.fetchone()
    if not proj:
        return jsonify({'error': 'Project not found'}), 404

    thumb_bytes = None
    thumb_fmt = None
    source = None

    # সোর্স ১: প্রথম embedded image
    cursor.execute('''
        SELECT bi.image_data, bi.image_path, bi.image_format
        FROM block_images bi
        JOIN markdown_blocks mb ON bi.block_id = mb.id
        JOIN chapters c ON mb.chapter_id = c.id
        WHERE c.project_id = ?
          AND ((bi.image_path IS NOT NULL AND bi.image_path != '')
               OR (bi.image_data IS NOT NULL AND bi.image_data != ''))
        ORDER BY c.chapter_number, mb.block_order, bi.id
        LIMIT 1
    ''', (project_id,))
    img_row = cursor.fetchone()

    if img_row:
        raw = None
        if img_row['image_path']:
            b64 = read_file_base64(img_row['image_path'])
            if b64:
                try:
                    raw = base64.b64decode(b64)
                except Exception:
                    raw = None
        elif img_row['image_data']:
            try:
                raw = base64.b64decode(clean_str(img_row['image_data']))
            except Exception:
                raw = None

        if raw and len(raw) > 4000:
            thumb_bytes, thumb_fmt = _optimize_image_bytes(
                raw, img_row['image_format'] or 'png'
            )
            if thumb_bytes:
                source = 'image'

    # সোর্স ২: text-cover fallback
    if not thumb_bytes:
        thumb_bytes, thumb_fmt = generate_text_cover(proj['name'], proj['author'] or '')
        if thumb_bytes:
            source = 'text'

    if not thumb_bytes:
        return jsonify({'error': 'Could not generate thumbnail (Pillow may be missing)'}), 500

    rel_path = save_thumbnail(project_id, thumb_bytes, thumb_fmt)
    if not rel_path:
        return jsonify({'error': 'Failed to save thumbnail'}), 500

    cursor.execute('''
        UPDATE projects
        SET thumbnail_path = ?, thumbnail_data = NULL, thumbnail_format = ?,
            thumbnail_auto = 1
        WHERE id = ?
    ''', (rel_path, thumb_fmt, project_id))
    db.commit()

    b64 = read_file_base64(rel_path)
    return jsonify({
        'success': True,
        'source': source,
        'thumbnail_data': b64,
        'thumbnail_format': thumb_fmt,
        'message': f'Thumbnail generated from {"first image" if source == "image" else "text cover"}'
    })


@project_bp.route('/api/maintenance/backfill-thumbnails', methods=['POST'])
@login_required
def backfill_thumbnails():
    """
    থাম্বনেইল জেনারেট করে (v4.4)।
    সোর্স: (১) প্রথম embedded image block, (২) fallback হিসেবে text-cover।

    force=False → শুধু থাম্বনেইল-বিহীন প্রজেক্ট।
    force=True  → auto-generated থাম্বনেইলও নতুন করে বানায় (user-uploaded রক্ষা পায়)।
    """
    data = request.json or {}
    force = bool(data.get('force', False))

    db = get_db()
    cursor = db.cursor()

    if force:
        # user-uploaded (thumbnail_auto=0 AND থাম্বনেইল আছে) ছাড়া সব
        cursor.execute('''
            SELECT id, name, author FROM projects
            WHERE (thumbnail_path IS NULL OR thumbnail_path = '')
               OR thumbnail_auto = 1
        ''')
    else:
        cursor.execute('''
            SELECT id, name, author FROM projects
            WHERE (thumbnail_path IS NULL OR thumbnail_path = '')
              AND (thumbnail_data IS NULL OR thumbnail_data = '')
        ''')
    projects = cursor.fetchall()

    generated = 0
    from_image = 0
    from_text = 0
    failed = 0

    for proj in projects:
        pid = proj['id']
        thumb_bytes = None
        thumb_fmt = None

        # সোর্স ১: প্রথম embedded image (path বা base64)
        cursor.execute('''
            SELECT bi.image_data, bi.image_path, bi.image_format
            FROM block_images bi
            JOIN markdown_blocks mb ON bi.block_id = mb.id
            JOIN chapters c ON mb.chapter_id = c.id
            WHERE c.project_id = ?
              AND ((bi.image_path IS NOT NULL AND bi.image_path != '')
                   OR (bi.image_data IS NOT NULL AND bi.image_data != ''))
            ORDER BY c.chapter_number, mb.block_order, bi.id
            LIMIT 1
        ''', (pid,))
        img_row = cursor.fetchone()

        if img_row:
            import base64
            raw = None
            if img_row['image_path']:
                raw = read_file_base64(img_row['image_path'])
                if raw:
                    try:
                        raw = base64.b64decode(raw)
                    except Exception:
                        raw = None
            elif img_row['image_data']:
                try:
                    raw = base64.b64decode(clean_str(img_row['image_data']))
                except Exception:
                    raw = None

            if raw and len(raw) > 4000:
                from thumbnail_generator import _optimize_image_bytes
                thumb_bytes, thumb_fmt = _optimize_image_bytes(
                    raw, img_row['image_format'] or 'png'
                )
                if thumb_bytes:
                    from_image += 1

        # সোর্স ২: text-cover fallback
        if not thumb_bytes:
            thumb_bytes, thumb_fmt = generate_text_cover(
                proj['name'], proj['author'] or ''
            )
            if thumb_bytes:
                from_text += 1

        if not thumb_bytes:
            failed += 1
            continue

        rel_path = save_thumbnail(pid, thumb_bytes, thumb_fmt)
        if rel_path:
            cursor.execute('''
                UPDATE projects
                SET thumbnail_path = ?, thumbnail_data = NULL, thumbnail_format = ?,
                    thumbnail_auto = 1
                WHERE id = ?
            ''', (rel_path, thumb_fmt, pid))
            generated += 1
        else:
            failed += 1

    db.commit()

    return jsonify({
        'success': True,
        'total_without_thumbnail': len(projects),
        'generated': generated,
        'from_image': from_image,
        'from_text': from_text,
        'failed': failed,
        'force': force,
        'message': f'{generated} thumbnails generated '
                   f'({from_image} from images, {from_text} text covers).'
    })


@project_bp.route('/api/maintenance/db-stats', methods=['GET'])
@login_required
def db_stats():
    """ডেটাবেস সাইজ, ফাঁকা স্পেস (%), এবং মিডিয়া স্টোরেজ সাইজ রিটার্ন করে।"""
    try:
        stats = get_db_stats()
    except Exception as e:
        print(f"⚠️ get_db_stats failed: {e}")
        return jsonify({'error': f'Database stats failed: {str(e)}'}), 500

    # মিডিয়া স্ক্যান আলাদা try/except — ফোল্ডার বিশাল/দুর্গম হলেও stats রিটার্ন হবে
    try:
        media_bytes = get_storage_usage_bytes()
    except Exception as e:
        print(f"⚠️ get_storage_usage_bytes failed: {e}")
        media_bytes = 0

    stats['media_size_bytes'] = media_bytes
    stats['media_size_mb'] = round(media_bytes / (1024 * 1024), 2)
    return jsonify(stats)


@project_bp.route('/api/maintenance/vacuum', methods=['POST'])
@login_required
def run_vacuum():
    """ম্যানুয়ালি ডেটাবেস VACUUM চালায় (ফাঁকা স্পেস reclaim করে)।"""
    before = get_db_stats()
    try:
        vacuum_db()
    except Exception as e:
        return jsonify({'error': f'VACUUM failed: {str(e)}'}), 500
    after = get_db_stats()
    
    reclaimed_mb = round(before['file_size_mb'] - after['file_size_mb'], 2)
    return jsonify({
        'success': True,
        'message': f'VACUUM complete. Reclaimed {reclaimed_mb} MB.',
        'before': before,
        'after': after,
        'reclaimed_mb': reclaimed_mb
    })