# db.py - Database Configuration and Operations (v4.3)

import os
import sqlite3
from flask import g
from contextlib import contextmanager

from config import DATABASE


def get_db():
    if 'db' not in g:
        g.db = sqlite3.connect(DATABASE)
        g.db.row_factory = sqlite3.Row
    return g.db


def close_db(error=None):
    db = g.pop('db', None)
    if db is not None:
        db.close()


@contextmanager
def get_db_connection():
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row
    try:
        yield conn
    finally:
        conn.close()


def init_db():
    """Initialize database tables. Auto-creates parent directory + media storage."""
    # ফোল্ডার না থাকলে নিজে থেকেই তৈরি করবে (Coolify volume mount-এর জন্য জরুরি)
    db_dir = os.path.dirname(os.path.abspath(DATABASE))
    if db_dir and not os.path.exists(db_dir):
        os.makedirs(db_dir, exist_ok=True)
        print(f"📂 Created data directory: {db_dir}")

    # v4.3: media-storage ফোল্ডার তৈরি
    from config import MEDIA_STORAGE_DIR
    if not os.path.exists(MEDIA_STORAGE_DIR):
        os.makedirs(MEDIA_STORAGE_DIR, exist_ok=True)
        print(f"📂 Created media storage directory: {MEDIA_STORAGE_DIR}")

    with get_db_connection() as conn:
        cursor = conn.cursor()

        # Projects table
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS projects (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL UNIQUE,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ''')

        # Chapters table
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS chapters (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                project_id INTEGER NOT NULL,
                chapter_number INTEGER NOT NULL,
                title TEXT DEFAULT 'Section',
                voice TEXT DEFAULT 'af_heart',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
                UNIQUE(project_id, chapter_number)
            )
        ''')

        try:
            cursor.execute('ALTER TABLE chapters ADD COLUMN title TEXT DEFAULT "Section"')
        except sqlite3.OperationalError:
            pass

        # Markdown blocks table
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS markdown_blocks (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                chapter_id INTEGER NOT NULL,
                block_order INTEGER NOT NULL,
                block_type TEXT NOT NULL DEFAULT 'paragraph',
                content TEXT NOT NULL,
                tts_text TEXT,
                audio_data TEXT,
                audio_format TEXT DEFAULT 'mp3',
                transcription TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (chapter_id) REFERENCES chapters(id) ON DELETE CASCADE
            )
        ''')

        # Images table
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS block_images (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                block_id INTEGER NOT NULL,
                image_data TEXT NOT NULL,
                image_format TEXT DEFAULT 'png',
                alt_text TEXT,
                position TEXT DEFAULT 'before',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (block_id) REFERENCES markdown_blocks(id) ON DELETE CASCADE
            )
        ''')

        # PDF Documents table
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS pdf_documents (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                project_id INTEGER,
                filename TEXT NOT NULL,
                page_count INTEGER DEFAULT 0,
                metadata TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE SET NULL
            )
        ''')

        # v4.2 publishing migrations + v4.3 file-based media path columns
        migrations = [
            ('projects', 'is_published', 'INTEGER DEFAULT 0'),
            ('projects', 'published_at', 'TIMESTAMP'),
            ('projects', 'thumbnail_data', 'TEXT'),
            ('projects', 'thumbnail_format', 'TEXT DEFAULT "png"'),
            ('projects', 'description', 'TEXT DEFAULT ""'),
            ('projects', 'author', 'TEXT DEFAULT ""'),
            ('projects', 'category', 'TEXT DEFAULT ""'),
            ('projects', 'view_count', 'INTEGER DEFAULT 0'),
            # --- v4.3: file-based media path columns ---
            ('projects', 'thumbnail_path', 'TEXT'),
            ('markdown_blocks', 'audio_path', 'TEXT'),
            ('block_images', 'image_path', 'TEXT'),
            # --- v4.4: thumbnail auto-generated flag (1=auto, 0=user-uploaded) ---
            ('projects', 'thumbnail_auto', 'INTEGER DEFAULT 0'),
        ]

        for table, column, definition in migrations:
            try:
                cursor.execute(f'ALTER TABLE {table} ADD COLUMN {column} {definition}')
                print(f"  ✅ Added {table}.{column}")
            except sqlite3.OperationalError:
                pass

        conn.commit()
        print(f"✅ Database initialized at {DATABASE} (v4.3)")


def vacuum_db():
    """ম্যানুয়াল VACUUM (এখন আর অটোমেটিক চলে না)।"""
    with get_db_connection() as conn:
        conn.execute('VACUUM')


def get_db_stats():
    """
    ডেটাবেসের সাইজ এবং কত% ফাঁকা (reusable free) স্পেস আছে তা রিটার্ন করে।
    SQLite-এর freelist_count আর page_count দিয়ে হিসাব করা হয়।
    """
    file_size = os.path.getsize(DATABASE) if os.path.exists(DATABASE) else 0
    with get_db_connection() as conn:
        cur = conn.cursor()
        page_count = cur.execute('PRAGMA page_count').fetchone()[0]
        freelist_count = cur.execute('PRAGMA freelist_count').fetchone()[0]
        page_size = cur.execute('PRAGMA page_size').fetchone()[0]

    free_bytes = freelist_count * page_size
    free_percent = round((freelist_count / page_count) * 100, 1) if page_count > 0 else 0.0

    return {
        'file_size_bytes': file_size,
        'file_size_mb': round(file_size / (1024 * 1024), 2),
        'page_count': page_count,
        'freelist_count': freelist_count,
        'page_size': page_size,
        'free_bytes': free_bytes,
        'free_mb': round(free_bytes / (1024 * 1024), 2),
        'free_percent': free_percent,
    }


def init_app(app):
    app.teardown_appcontext(close_db)
    init_db()

    from auth import init_users_table
    init_users_table()