app.py

import os
import sqlite3
import json
from flask import Flask, render_template, request, redirect, url_for, abort, flash, jsonify
from flask_httpauth import HTTPBasicAuth
from werkzeug.security import generate_password_hash, check_password_hash
from werkzeug.utils import secure_filename
from huggingface_hub import hf_hub_download, upload_file
import logging
import tempfile
import fcntl
from datetime import datetime

app = Flask(__name__)
app.config['SECRET_KEY'] = os.getenv("FLASK_SECRET_KEY", "your-secret-key-here")
app.config['UPLOAD_FOLDER'] = 'static/uploads'
auth = HTTPBasicAuth()

# Thiết lập logging
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

# Đảm bảo thư mục upload tồn tại
os.makedirs(app.config['UPLOAD_FOLDER'], exist_ok=True)

# Cấu hình người dùng
ADMIN_USERNAME = os.getenv("ADMIN_USERNAME", "admin")
ADMIN_PASSWORD_HASH = generate_password_hash(os.getenv("ADMIN_PASSWORD", "password"))

@auth.verify_password
def verify_password(username, password):
    if username == ADMIN_USERNAME and check_password_hash(ADMIN_PASSWORD_HASH, password):
        return username
    return None

def get_db_connection():
    temp_db_path = '/tmp/database.db'
    cache_dir = '/tmp/hf_cache'
    os.makedirs(cache_dir, exist_ok=True)
    os.environ['HF_HOME'] = cache_dir

    repo_id = "huylaughmad/web"
    filename = "database.db"
    repo_type = "dataset"

    hf_token = os.getenv("HF_TOKEN")
    if not hf_token:
        raise RuntimeError("HF_TOKEN không được thiết lập trong biến môi trường")

    lock_file_path = '/tmp/database.lock'
    with open(lock_file_path, 'a') as lock_file:
        fcntl.flock(lock_file.fileno(), fcntl.LOCK_EX)
        try:
            if os.path.exists(temp_db_path):
                logger.info(f"File {temp_db_path} đã tồn tại cục bộ, sử dụng file này")
            else:
                try:
                    logger.info(f"Kiểm tra file {filename} trên dataset {repo_id}")
                    downloaded_path = hf_hub_download(
                        repo_id=repo_id,
                        filename=filename,
                        repo_type=repo_type,
                        token=hf_token,
                        cache_dir=cache_dir
                    )
                    logger.info(f"Tải file thành công từ {downloaded_path}")
                    with open(downloaded_path, 'rb') as src, open(temp_db_path, 'wb') as dst:
                        dst.write(src.read())
                except Exception as e:
                    logger.warning(f"Không tìm thấy file {filename} trên dataset hoặc lỗi tải: {str(e)}")
                    logger.info(f"Tạo file database mới tại {temp_db_path}")
                    open(temp_db_path, 'a').close()
                    logger.info("Khởi tạo cơ sở dữ liệu với các bảng và dữ liệu mẫu")
                    init_db()
                    logger.info(f"Đẩy file {temp_db_path} lên dataset {repo_id}")
                    upload_file(
                        path_or_fileobj=temp_db_path,
                        path_in_repo=filename,
                        repo_id=repo_id,
                        repo_type=repo_type,
                        token=hf_token
                    )
                    logger.info(f"File {filename} đã được đẩy lên dataset thành công")

            if not os.access(os.path.dirname(temp_db_path), os.W_OK):
                raise RuntimeError(f"Không có quyền ghi tại {os.path.dirname(temp_db_path)}")

            conn = sqlite3.connect(temp_db_path)
            conn.row_factory = sqlite3.Row
            return conn
        except sqlite3.OperationalError as sql_error:
            raise RuntimeError(f"Không thể mở file cơ sở dữ liệu tại {temp_db_path}: {str(sql_error)}")
        except Exception as e:
            raise RuntimeError(f"Lỗi khi xử lý cơ sở dữ liệu: {str(e)}")
        finally:
            fcntl.flock(lock_file.fileno(), fcntl.LOCK_UN)

def update_db_to_huggingface():
    try:
        cache_dir = '/tmp/hf_cache'
        os.makedirs(cache_dir, exist_ok=True)
        os.environ['HF_HOME'] = cache_dir
        upload_file(
            path_or_fileobj="/tmp/database.db",
            path_in_repo="database.db",
            repo_id="huylaughmad/web",
            repo_type="dataset",
            token=os.getenv("HF_TOKEN")
        )
        logger.info("Cập nhật database.db lên dataset thành công")
    except Exception as e:
        logger.error(f"Lỗi khi cập nhật database.db lên dataset: {str(e)}")

def init_db():
    conn = get_db_connection()
    cursor = conn.cursor()

    # Tạo bảng case_studies
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS case_studies (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            title TEXT NOT NULL,
            treatment TEXT NOT NULL,
            description TEXT,
            before_image TEXT,
            after_image TEXT,
            patient_name TEXT,
            patient_age TEXT,
            patient_rating TEXT DEFAULT '5',
            patient_avatar TEXT,
            status TEXT DEFAULT 'draft',
            date TEXT NOT NULL,
            duration TEXT,
            visits TEXT,
            case_id TEXT NOT NULL UNIQUE,
            seo_title TEXT,
            seo_description TEXT,
            seo_keywords TEXT,
            category TEXT,
            tag TEXT,
            progress_images TEXT
        )
    ''')

    # Chèn dữ liệu mẫu cho case_studies
    cursor.execute('SELECT COUNT(*) FROM case_studies')
    if cursor.fetchone()[0] == 0:
        cursor.execute('''
            INSERT INTO case_studies (title, treatment, description, before_image, after_image, patient_name, patient_age, patient_rating, patient_avatar, status, date, duration, visits, case_id, category, tag) VALUES
            ('Hành trình niềng răng Invisalign của Chị Anh', 'Niềng răng Invisalign', 'Chị Anh, 28 tuổi, đã lựa chọn niềng răng Invisalign để cải thiện tình trạng răng khấp khểnh.', 'https://images.unsplash.com/photo-1588776814546-1ffcf47267a5', 'https://images.unsplash.com/photo-1622253692010-333f2da6031d', 'Chị Anh', '28', '5', 'https://randomuser.me/api/portraits/women/32.jpg', 'published', '2022-07-15', '18 tháng', '6 lần', 'CASE001', 'Chỉnh nha', 'Invisalign,Niềng răng')
        ''')

    # Tạo bảng pages
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS pages (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            title TEXT NOT NULL,
            slug TEXT NOT NULL UNIQUE,
            content TEXT,
            status TEXT DEFAULT 'draft',
            template TEXT DEFAULT 'default',
            parent_id INTEGER,
            seo_title TEXT,
            seo_description TEXT,
            seo_keywords TEXT,
            featured_image TEXT,
            last_modified TEXT
        )
    ''')

    # Chèn dữ liệu mẫu cho pages
    cursor.execute('SELECT COUNT(*) FROM pages')
    if cursor.fetchone()[0] == 0:
        cursor.execute('''
            INSERT INTO pages (title, slug, content, status, template, parent_id, seo_title, seo_description, seo_keywords, featured_image, last_modified) VALUES
            ('Home Page', 'home', '<h1>Welcome to Our Dental Clinic</h1><p>Your smile is our priority.</p>', 'published', 'homepage', 0, 'Home - Dental Clinic', 'Visit our dental clinic for top-quality care.', 'dental, clinic, care', 'https://images.unsplash.com/photo-1588776814546-1ffcf47267a5', '2025-04-28'),
            ('About Us', 'about', '<h1>About Our Clinic</h1><p>We are dedicated to providing excellent dental care.</p>', 'published', 'default', 0, 'About Us - Dental Clinic', 'Learn about our dental clinic and team.', 'dental, about, team', 'https://images.unsplash.com/photo-1622253692010-333f2da6031d', '2025-04-28')
        ''')

    # Tạo bảng posts
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS posts (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            title TEXT NOT NULL,
            slug TEXT NOT NULL UNIQUE,
            content TEXT,
            status TEXT DEFAULT 'draft',
            categories TEXT,
            tags TEXT,
            excerpt TEXT,
            seo_title TEXT,
            seo_description TEXT,
            seo_keywords TEXT,
            featured_image TEXT,
            publish_date TEXT
        )
    ''')

    # Chèn dữ liệu mẫu cho posts
    cursor.execute('SELECT COUNT(*) FROM posts')
    if cursor.fetchone()[0] == 0:
        cursor.execute('''
            INSERT INTO posts (title, slug, content, status, categories, tags, excerpt, seo_title, seo_description, seo_keywords, featured_image, publish_date) VALUES
            ('10 Dental Care Tips', 'dental-care-tips', '<p>Learn how to maintain a healthy smile with these tips...</p>', 'published', 'Dental Care', 'teeth,care', 'Top 10 dental care tips for a healthy smile.', 'Dental Care Tips', 'Learn dental care tips.', 'dental,care,tips', 'https://images.unsplash.com/photo-1588776814546-1ffcf47267a5', '2025-04-28')
        ''')

    # Tạo các bảng khác
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS our_story (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            history TEXT NOT NULL
        )
    ''')
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS timeline (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            year TEXT NOT NULL,
            title TEXT NOT NULL,
            description TEXT NOT NULL
        )
    ''')
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS mission_values (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            title TEXT NOT NULL,
            icon TEXT,
            description TEXT NOT NULL
        )
    ''')
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS team (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            specialty TEXT NOT NULL,
            photo TEXT,
            description TEXT,
            facebook_url TEXT,
            linkedin_url TEXT,
            instagram_url TEXT
        )
    ''')
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS technologies (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            title TEXT NOT NULL,
            description TEXT NOT NULL,
            icon TEXT,
            image TEXT
        )
    ''')
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS testimonials (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            patient_name TEXT NOT NULL,
            treatment_type TEXT NOT NULL,
            avatar TEXT,
            content TEXT NOT NULL,
            rating INTEGER DEFAULT 5
        )
    ''')
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS contact_info (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            address TEXT NOT NULL,
            phone TEXT NOT NULL,
            email TEXT NOT NULL,
            working_hours_mon_fri TEXT,
            working_hours_sat TEXT,
            working_hours_sun TEXT,
            map_iframe TEXT
        )
    ''')
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS faqs (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            question TEXT NOT NULL,
            answer TEXT NOT NULL
        )
    ''')
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS services (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            description TEXT NOT NULL,
            duration TEXT NOT NULL,
            visits_required TEXT NOT NULL,
            before_image TEXT NOT NULL,
            after_image TEXT NOT NULL
        )
    ''')
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS patient_journey_milestones (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            case_id INTEGER NOT NULL,
            milestone_title TEXT NOT NULL,
            milestone_description TEXT NOT NULL,
            milestone_date TEXT NOT NULL,
            tags TEXT,
            FOREIGN KEY (case_id) REFERENCES case_studies(id)
        )
    ''')
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS comments (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            post_id INTEGER NOT NULL,
            author_name TEXT NOT NULL,
            author_email TEXT NOT NULL,
            content TEXT NOT NULL,
            created_at TEXT DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (post_id) REFERENCES posts(id)
        )
    ''')
    cursor.execute('CREATE INDEX IF NOT EXISTS idx_pjm_case_id ON patient_journey_milestones(case_id)')

    conn.commit()
    conn.close()

# Route cho trang CMS
@app.route('/cms')
@auth.login_required
def cms():
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM case_studies')
    cases = [dict(row) for row in cursor.fetchall()]
    cursor.execute('SELECT * FROM pages')
    pages = [dict(row) for row in cursor.fetchall()]
    cursor.execute('SELECT * FROM posts')
    posts = [dict(row) for row in cursor.fetchall()]
    conn.close()
    return render_template('cms.html', cases=cases, pages=pages, posts=posts, section='dashboard-section')

# Route để thêm case study mới
@app.route('/cms/cases/new', methods=['GET', 'POST'])
@auth.login_required
def add_case():
    conn = get_db_connection()
    cursor = conn.cursor()

    cursor.execute('SELECT * FROM case_studies')
    cases = [dict(row) for row in cursor.fetchall()]
    cursor.execute('SELECT * FROM pages')
    pages = [dict(row) for row in cursor.fetchall()]
    cursor.execute('SELECT * FROM posts')
    posts = [dict(row) for row in cursor.fetchall()]

    if request.method == 'POST':
        # Lấy dữ liệu từ form
        title = request.form.get('case-title')
        treatment = request.form.get('case-treatment')
        description = request.form.get('case-description')
        status = request.form.get('case-status', 'draft')
        date = request.form.get('case-date')
        category = request.form.get('case-category')
        patient_name = request.form.get('case-patient-name', '')
        patient_age = request.form.get('case-patient-age', '')
        patient_rating = request.form.get('case-patient-rating', '5')
        duration = request.form.get('case-duration', '')
        visits = request.form.get('case-visits', '')
        case_id = request.form.get('case-id')
        seo_title = request.form.get('case-seo-title', '')
        seo_description = request.form.get('case-seo-description', '')
        seo_keywords = request.form.get('case-seo-keywords', '')

        # Kiểm tra các trường bắt buộc
        if not all([title, treatment, case_id, date]):
            flash('Vui lòng điền đầy đủ các trường bắt buộc.', 'error')
            conn.close()
            return render_template('cms.html', section='case-editor-section', case=None, cases=cases, pages=pages, posts=posts)

        # Kiểm tra case_id duy nhất
        cursor.execute('SELECT id FROM case_studies WHERE case_id = ?', (case_id,))
        if cursor.fetchone():
            flash('Case ID đã tồn tại. Vui lòng chọn ID khác.', 'error')
            conn.close()
            return render_template('cms.html', section='case-editor-section', case=None, cases=cases, pages=pages, posts=posts)

        # Xử lý upload hình ảnh
        before_image_path = ''
        if 'case-before-image' in request.files and request.files['case-before-image'].filename:
            before_image = request.files['case-before-image']
            filename = secure_filename(before_image.filename)
            before_image.save(os.path.join(app.config['UPLOAD_FOLDER'], filename))
            before_image_path = f"/{app.config['UPLOAD_FOLDER']}/{filename}"

        after_image_path = ''
        if 'case-after-image' in request.files and request.files['case-after-image'].filename:
            after_image = request.files['case-after-image']
            filename = secure_filename(after_image.filename)
            after_image.save(os.path.join(app.config['UPLOAD_FOLDER'], filename))
            after_image_path = f"/{app.config['UPLOAD_FOLDER']}/{filename}"

        patient_avatar_path = ''
        if 'case-patient-avatar' in request.files and request.files['case-patient-avatar'].filename:
            patient_avatar = request.files['case-patient-avatar']
            filename = secure_filename(patient_avatar.filename)
            patient_avatar.save(os.path.join(app.config['UPLOAD_FOLDER'], filename))
            patient_avatar_path = f"/{app.config['UPLOAD_FOLDER']}/{filename}"

        # Chèn dữ liệu vào database
        cursor.execute('''
            INSERT INTO case_studies (
                title, treatment, description, before_image, after_image,
                patient_name, patient_age, patient_rating, patient_avatar,
                status, date, duration, visits, case_id,
                seo_title, seo_description, seo_keywords, category
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ''', (
            title, treatment, description, before_image_path, after_image_path,
            patient_name, patient_age, patient_rating, patient_avatar_path,
            status, date, duration, visits, case_id,
            seo_title, seo_description, seo_keywords, category
        ))
        conn.commit()
        update_db_to_huggingface()
        conn.close()
        flash('Case study đã được thêm thành công!', 'success')
        return redirect(url_for('cms', section='cases-section'))

    conn.close()
    return render_template('cms.html', section='case-editor-section', case=None, cases=cases, pages=pages, posts=posts)

# Route để chỉnh sửa case study
@app.route('/cms/cases/edit/<int:id>', methods=['GET', 'POST'])
@auth.login_required
def edit_case(id):
    conn = get_db_connection()
    cursor = conn.cursor()

    cursor.execute('SELECT * FROM case_studies')
    cases = [dict(row) for row in cursor.fetchall()]
    cursor.execute('SELECT * FROM pages')
    pages = [dict(row) for row in cursor.fetchall()]
    cursor.execute('SELECT * FROM posts')
    posts = [dict(row) for row in cursor.fetchall()]

    cursor.execute('SELECT * FROM case_studies WHERE id = ?', (id,))
    case = cursor.fetchone()
    if not case:
        conn.close()
        return "Case not found", 404
    case = dict(case)

    if request.method == 'POST':
        # Lấy dữ liệu từ form
        title = request.form.get('case-title')
        treatment = request.form.get('case-treatment')
        description = request.form.get('case-description')
        status = request.form.get('case-status', 'draft')
        date = request.form.get('case-date')
        category = request.form.get('case-category')
        patient_name = request.form.get('case-patient-name', '')
        patient_age = request.form.get('case-patient-age', '')
        patient_rating = request.form.get('case-patient-rating', '5')
        duration = request.form.get('case-duration', '')
        visits = request.form.get('case-visits', '')
        case_id = request.form.get('case-id')
        seo_title = request.form.get('case-seo-title', '')
        seo_description = request.form.get('case-seo-description', '')
        seo_keywords = request.form.get('case-seo-keywords', '')

        # Kiểm tra các trường bắt buộc
        if not all([title, treatment, case_id, date]):
            flash('Vui lòng điền đầy đủ các trường bắt buộc.', 'error')
            conn.close()
            return render_template('cms.html', section='case-editor-section', case=case, cases=cases, pages=pages, posts=posts)

        # Kiểm tra case_id duy nhất (ngoại trừ case hiện tại)
        cursor.execute('SELECT id FROM case_studies WHERE case_id = ? AND id != ?', (case_id, id))
        if cursor.fetchone():
            flash('Case ID đã tồn tại. Vui lòng chọn ID khác.', 'error')
            conn.close()
            return render_template('cms.html', section='case-editor-section', case=case, cases=cases, pages=pages, posts=posts)

        # Xử lý upload hình ảnh
        before_image_path = case['before_image']
        if 'case-before-image' in request.files and request.files['case-before-image'].filename:
            before_image = request.files['case-before-image']
            filename = secure_filename(before_image.filename)
            before_image.save(os.path.join(app.config['UPLOAD_FOLDER'], filename))
            before_image_path = f"/{app.config['UPLOAD_FOLDER']}/{filename}"

        after_image_path = case['after_image']
        if 'case-after-image' in request.files and request.files['case-after-image'].filename:
            after_image = request.files['case-after-image']
            filename = secure_filename(after_image.filename)
            after_image.save(os.path.join(app.config['UPLOAD_FOLDER'], filename))
            after_image_path = f"/{app.config['UPLOAD_FOLDER']}/{filename}"

        patient_avatar_path = case['patient_avatar']
        if 'case-patient-avatar' in request.files and request.files['case-patient-avatar'].filename:
            patient_avatar = request.files['case-patient-avatar']
            filename = secure_filename(patient_avatar.filename)
            patient_avatar.save(os.path.join(app.config['UPLOAD_FOLDER'], filename))
            patient_avatar_path = f"/{app.config['UPLOAD_FOLDER']}/{filename}"

        # Cập nhật dữ liệu trong database
        cursor.execute('''
            UPDATE case_studies
            SET title = ?, treatment = ?, description = ?, before_image = ?, after_image = ?,
                patient_name = ?, patient_age = ?, patient_rating = ?, patient_avatar = ?,
                status = ?, date = ?, duration = ?, visits = ?, case_id = ?,
                seo_title = ?, seo_description = ?, seo_keywords = ?, category = ?
            WHERE id = ?
        ''', (
            title, treatment, description, before_image_path, after_image_path,
            patient_name, patient_age, patient_rating, patient_avatar_path,
            status, date, duration, visits, case_id,
            seo_title, seo_description, seo_keywords, category,
            id
        ))
        conn.commit()
        update_db_to_huggingface()
        conn.close()
        flash('Case study đã được cập nhật thành công!', 'success')
        return redirect(url_for('cms', section='cases-section'))

    conn.close()
    return render_template('cms.html', section='case-editor-section', case=case, cases=cases, pages=pages, posts=posts)

# Route để xóa case study
@app.route('/cms/cases/delete/<int:id>')
@auth.login_required
def delete_case(id):
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute('DELETE FROM case_studies WHERE id = ?', (id,))
    conn.commit()
    update_db_to_huggingface()
    conn.close()
    flash('Case study đã được xóa thành công!', 'success')
    return redirect(url_for('cms', section='cases-section'))

# Route để thêm page mới
@app.route('/cms/pages/new', methods=['GET', 'POST'])
@auth.login_required
def add_page():
    conn = get_db_connection()
    cursor = conn.cursor()

    cursor.execute('SELECT * FROM case_studies')
    cases = [dict(row) for row in cursor.fetchall()]
    cursor.execute('SELECT * FROM pages')
    pages = [dict(row) for row in cursor.fetchall()]
    cursor.execute('SELECT * FROM posts')
    posts = [dict(row) for row in cursor.fetchall()]

    if request.method == 'POST':
        title = request.form.get('page-title')
        slug = request.form.get('page-slug')
        content = request.form.get('page-content')
        status = request.form.get('page-status', 'draft')
        template = request.form.get('page-template', 'default')
        parent_id = request.form.get('page-parent', 0, type=int)
        seo_title = request.form.get('seo-title', '')
        seo_description = request.form.get('seo-description', '')
        seo_keywords = request.form.get('seo-keywords', '')
        featured_image = ''

        if 'page-featured-image' in request.files and request.files['page-featured-image'].filename:
            featured_image_file = request.files['page-featured-image']
            filename = secure_filename(featured_image_file.filename)
            featured_image_file.save(os.path.join(app.config['UPLOAD_FOLDER'], filename))
            featured_image = f"/{app.config['UPLOAD_FOLDER']}/{filename}"

        if not all([title, slug]):
            flash('Vui lòng điền đầy đủ tiêu đề và slug.', 'error')
            conn.close()
            return render_template('cms.html', section='page-editor-section', page=None, cases=cases, pages=pages, posts=posts)

        cursor.execute('SELECT id FROM pages WHERE slug = ?', (slug,))
        if cursor.fetchone():
            flash('Slug đã tồn tại. Vui lòng chọn slug khác.', 'error')
            conn.close()
            return render_template('cms.html', section='page-editor-section', page=None, cases=cases, pages=pages, posts=posts)

        cursor.execute('''
            INSERT INTO pages (
                title, slug, content, status, template, parent_id,
                seo_title, seo_description, seo_keywords, featured_image, last_modified
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ''', (
            title, slug, content, status, template, parent_id,
            seo_title, seo_description, seo_keywords, featured_image,
            datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        ))
        conn.commit()
        update_db_to_huggingface()
        conn.close()
        flash('Page đã được thêm thành công!', 'success')
        return redirect(url_for('cms', section='pages-section'))

    conn.close()
    return render_template('cms.html', section='page-editor-section', page=None, cases=cases, pages=pages, posts=posts)

# Route để chỉnh sửa page
@app.route('/cms/pages/edit/<int:id>', methods=['GET', 'POST'])
@auth.login_required
def edit_page(id):
    conn = get_db_connection()
    cursor = conn.cursor()

    cursor.execute('SELECT * FROM case_studies')
    cases = [dict(row) for row in cursor.fetchall()]
    cursor.execute('SELECT * FROM pages')
    pages = [dict(row) for row in cursor.fetchall()]
    cursor.execute('SELECT * FROM posts')
    posts = [dict(row) for row in cursor.fetchall()]

    cursor.execute('SELECT * FROM pages WHERE id = ?', (id,))
    page = cursor.fetchone()
    if not page:
        conn.close()
        return "Page not found", 404
    page = dict(page)

    if request.method == 'POST':
        title = request.form.get('page-title')
        slug = request.form.get('page-slug')
        content = request.form.get('page-content')
        status = request.form.get('page-status', 'draft')
        template = request.form.get('page-template', 'default')
        parent_id = request.form.get('page-parent', 0, type=int)
        seo_title = request.form.get('seo-title', '')
        seo_description = request.form.get('seo-description', '')
        seo_keywords = request.form.get('seo-keywords', '')
        featured_image = page['featured_image']

        if 'page-featured-image' in request.files and request.files['page-featured-image'].filename:
            featured_image_file = request.files['page-featured-image']
            filename = secure_filename(featured_image_file.filename)
            featured_image_file.save(os.path.join(app.config['UPLOAD_FOLDER'], filename))
            featured_image = f"/{app.config['UPLOAD_FOLDER']}/{filename}"

        if not all([title, slug]):
            flash('Vui lòng điền đầy đủ tiêu đề và slug.', 'error')
            conn.close()
            return render_template('cms.html', section='page-editor-section', page=page, cases=cases, pages=pages, posts=posts)

        cursor.execute('SELECT id FROM pages WHERE slug = ? AND id != ?', (slug, id))
        if cursor.fetchone():
            flash('Slug đã tồn tại. Vui lòng chọn slug khác.', 'error')
            conn.close()
            return render_template('cms.html', section='page-editor-section', page=page, cases=cases, pages=pages, posts=posts)

        cursor.execute('''
            UPDATE pages
            SET title = ?, slug = ?, content = ?, status = ?, template = ?, parent_id = ?,
                seo_title = ?, seo_description = ?, seo_keywords = ?, featured_image = ?,
                last_modified = ?
            WHERE id = ?
        ''', (
            title, slug, content, status, template, parent_id,
            seo_title, seo_description, seo_keywords, featured_image,
            datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
            id
        ))
        conn.commit()
        update_db_to_huggingface()
        conn.close()
        flash('Page đã được cập nhật thành công!', 'success')
        return redirect(url_for('cms', section='pages-section'))

    conn.close()
    return render_template('cms.html', section='page-editor-section', page=page, cases=cases, pages=pages, posts=posts)

# Route để xóa page
@app.route('/cms/pages/delete/<int:id>')
@auth.login_required
def delete_page(id):
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute('DELETE FROM pages WHERE id = ?', (id,))
    conn.commit()
    update_db_to_huggingface()
    conn.close()
    flash('Page đã được xóa thành công!', 'success')
    return redirect(url_for('cms', section='pages-section'))

# Route để thêm post mới
@app.route('/cms/posts/new', methods=['GET', 'POST'])
@auth.login_required
def add_post():
    conn = get_db_connection()
    cursor = conn.cursor()

    cursor.execute('SELECT * FROM case_studies')
    cases = [dict(row) for row in cursor.fetchall()]
    cursor.execute('SELECT * FROM pages')
    pages = [dict(row) for row in cursor.fetchall()]
    cursor.execute('SELECT * FROM posts')
    posts = [dict(row) for row in cursor.fetchall()]

    if request.method == 'POST':
        title = request.form.get('post-title')
        slug = request.form.get('post-slug')
        content = request.form.get('post-content')
        status = request.form.get('post-status', 'draft')
        categories = ','.join(request.form.getlist('post-categories'))
        tags = request.form.get('post-tags', '')
        excerpt = request.form.get('post-excerpt', '')
        seo_title = request.form.get('post-seo-title', '')
        seo_description = request.form.get('post-seo-description', '')
        seo_keywords = request.form.get('post-seo-keywords', '')
        publish_date = request.form.get('post-publish-date', '')
        featured_image = ''

        if 'post-featured-image' in request.files and request.files['post-featured-image'].filename:
            featured_image_file = request.files['post-featured-image']
            filename = secure_filename(featured_image_file.filename)
            featured_image_file.save(os.path.join(app.config['UPLOAD_FOLDER'], filename))
            featured_image = f"/{app.config['UPLOAD_FOLDER']}/{filename}"

        if not all([title, slug]):
            flash('Vui lòng điền đầy đủ tiêu đề và slug.', 'error')
            conn.close()
            return render_template('cms.html', section='post-editor-section', post=None, cases=cases, pages=pages, posts=posts)

        cursor.execute('SELECT id FROM posts WHERE slug = ?', (slug,))
        if cursor.fetchone():
            flash('Slug đã tồn tại. Vui lòng chọn slug khác.', 'error')
            conn.close()
            return render_template('cms.html', section='post-editor-section', post=None, cases=cases, pages=pages, posts=posts)

        cursor.execute('''
            INSERT INTO posts (
                title, slug, content, status, categories, tags, excerpt,
                seo_title, seo_description, seo_keywords, featured_image, publish_date
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ''', (
            title, slug, content, status, categories, tags, excerpt,
            seo_title, seo_description, seo_keywords, featured_image, publish_date
        ))
        conn.commit()
        update_db_to_huggingface()
        conn.close()
        flash('Post đã được thêm thành công!', 'success')
        return redirect(url_for('cms', section='posts-section'))

    conn.close()
    return render_template('cms.html', section='post-editor-section', post=None, cases=cases, pages=pages, posts=posts)

# Route để chỉnh sửa post
@app.route('/cms/posts/edit/<int:id>', methods=['GET', 'POST'])
@auth.login_required
def edit_post(id):
    conn = get_db_connection()
    cursor = conn.cursor()

    cursor.execute('SELECT * FROM case_studies')
    cases = [dict(row) for row in cursor.fetchall()]
    cursor.execute('SELECT * FROM pages')
    pages = [dict(row) for row in cursor.fetchall()]
    cursor.execute('SELECT * FROM posts')
    posts = [dict(row) for row in cursor.fetchall()]

    cursor.execute('SELECT * FROM posts WHERE id = ?', (id,))
    post = cursor.fetchone()
    if not post:
        conn.close()
        return "Post not found", 404
    post = dict(post)

    if request.method == 'POST':
        title = request.form.get('post-title')
        slug = request.form.get('post-slug')
        content = request.form.get('post-content')
        status = request.form.get('post-status', 'draft')
        categories = ','.join(request.form.getlist('post-categories'))
        tags = request.form.get('post-tags', '')
        excerpt = request.form.get('post-excerpt', '')
        seo_title = request.form.get('post-seo-title', '')
        seo_description = request.form.get('post-seo-description', '')
        seo_keywords = request.form.get('post-seo-keywords', '')
        publish_date = request.form.get('post-publish-date', '')
        featured_image = post['featured_image']

        if 'post-featured-image' in request.files and request.files['post-featured-image'].filename:
            featured_image_file = request.files['post-featured-image']
            filename = secure_filename(featured_image_file.filename)
            featured_image_file.save(os.path.join(app.config['UPLOAD_FOLDER'], filename))
            featured_image = f"/{app.config['UPLOAD_FOLDER']}/{filename}"

        if not all([title, slug]):
            flash('Vui lòng điền đầy đủ tiêu đề và slug.', 'error')
            conn.close()
            return render_template('cms.html', section='post-editor-section', post=post, cases=cases, pages=pages, posts=posts)

        cursor.execute('SELECT id FROM posts WHERE slug = ? AND id != ?', (slug, id))
        if cursor.fetchone():
            flash('Slug đã tồn tại. Vui lòng chọn slug khác.', 'error')
            conn.close()
            return render_template('cms.html', section='post-editor-section', post=post, cases=cases, pages=pages, posts=posts)

        cursor.execute('''
            UPDATE posts
            SET title = ?, slug = ?, content = ?, status = ?, categories = ?, tags = ?,
                excerpt = ?, seo_title = ?, seo_description = ?, seo_keywords = ?,
                featured_image = ?, publish_date = ?
            WHERE id = ?
        ''', (
            title, slug, content, status, categories, tags, excerpt,
            seo_title, seo_description, seo_keywords, featured_image, publish_date,
            id
        ))
        conn.commit()
        update_db_to_huggingface()
        conn.close()
        flash('Post đã được cập nhật thành công!', 'success')
        return redirect(url_for('cms', section='posts-section'))

    conn.close()
    return render_template('cms.html', section='post-editor-section', post=post, cases=cases, pages=pages, posts=posts)

# Route để xóa post
@app.route('/cms/posts/delete/<int:id>')
@auth.login_required
def delete_post(id):
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute('DELETE FROM posts WHERE id = ?', (id,))
    conn.commit()
    update_db_to_huggingface()
    conn.close()
    flash('Post đã được xóa thành công!', 'success')
    return redirect(url_for('cms', section='posts-section'))

# Route để quản lý team
@app.route('/cms/team')
@auth.login_required
def cms_team():
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM team')
    team = [dict(row) for row in cursor.fetchall()]
    conn.close()
    return render_template('cms.html', section='team-section', team=team)

@app.route('/cms/team/new', methods=['GET', 'POST'])
@auth.login_required
def add_team_member():
    conn = get_db_connection()
    cursor = conn.cursor()
    if request.method == 'POST':
        name = request.form.get('name')
        specialty = request.form.get('specialty')
        description = request.form.get('description', '')
        facebook_url = request.form.get('facebook_url', '#')
        linkedin_url = request.form.get('linkedin_url', '#')
        instagram_url = request.form.get('instagram_url', '#')
        photo = ''

        if 'photo' in request.files and request.files['photo'].filename:
            photo_file = request.files['photo']
            filename = secure_filename(photo_file.filename)
            photo_file.save(os.path.join(app.config['UPLOAD_FOLDER'], filename))
            photo = f"/{app.config['UPLOAD_FOLDER']}/{filename}"

        if not all([name, specialty]):
            flash('Vui lòng điền đầy đủ tên và chuyên môn.', 'error')
            conn.close()
            return render_template('cms.html', section='team-editor-section', member=None)

        cursor.execute('''
            INSERT INTO team (name, specialty, photo, description, facebook_url, linkedin_url, instagram_url)
            VALUES (?, ?, ?, ?, ?, ?, ?)
        ''', (name, specialty, photo, description, facebook_url, linkedin_url, instagram_url))
        conn.commit()
        update_db_to_huggingface()
        conn.close()
        flash('Thành viên đã được thêm thành công!', 'success')
        return redirect(url_for('cms_team'))

    conn.close()
    return render_template('cms.html', section='team-editor-section', member=None)

@app.route('/cms/team/edit/<int:id>', methods=['GET', 'POST'])
@auth.login_required
def edit_team_member(id):
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM team WHERE id = ?', (id,))
    member = cursor.fetchone()
    if not member:
        conn.close()
        return "Team member not found", 404
    member = dict(member)

    if request.method == 'POST':
        name = request.form.get('name')
        specialty = request.form.get('specialty')
        description = request.form.get('description', '')
        facebook_url = request.form.get('facebook_url', '#')
        linkedin_url = request.form.get('linkedin_url', '#')
        instagram_url = request.form.get('instagram_url', '#')
        photo = member['photo']

        if 'photo' in request.files and request.files['photo'].filename:
            photo_file = request.files['photo']
            filename = secure_filename(photo_file.filename)
            photo_file.save(os.path.join(app.config['UPLOAD_FOLDER'], filename))
            photo = f"/{app.config['UPLOAD_FOLDER']}/{filename}"

        if not all([name, specialty]):
            flash('Vui lòng điền đầy đủ tên và chuyên môn.', 'error')
            conn.close()
            return render_template('cms.html', section='team-editor-section', member=member)

        cursor.execute('''
            UPDATE team
            SET name = ?, specialty = ?, photo = ?, description = ?, facebook_url = ?, linkedin_url = ?, instagram_url = ?
            WHERE id = ?
        ''', (name, specialty, photo, description, facebook_url, linkedin_url, instagram_url, id))
        conn.commit()
        update_db_to_huggingface()
        conn.close()
        flash('Thành viên đã được cập nhật thành công!', 'success')
        return redirect(url_for('cms_team'))

    conn.close()
    return render_template('cms.html', section='team-editor-section', member=member)

@app.route('/cms/team/delete/<int:id>')
@auth.login_required
def delete_team_member(id):
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute('DELETE FROM team WHERE id = ?', (id,))
    conn.commit()
    update_db_to_huggingface()
    conn.close()
    flash('Thành viên đã được xóa thành công!', 'success')
    return redirect(url_for('cms_team'))

# Các route công khai
@app.route('/test')
def test():
    return "Server is running!"

@app.route('/')
def index():
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM case_studies WHERE status = "published" LIMIT 6')
    cases = [dict(row) for row in cursor.fetchall()]
    conn.close()
    return render_template('index.html', cases=cases)

@app.route('/thuvien')
def thuvien():
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM case_studies')
    gallery = [dict(row) for row in cursor.fetchall()]
    cursor.execute('SELECT * FROM testimonials')
    testimonials = [dict(row) for row in cursor.fetchall()]
    cursor.execute('SELECT * FROM contact_info LIMIT 1')
    contact_info = cursor.fetchone()
    contact_info = dict(contact_info) if contact_info else {}
    conn.close()
    return render_template('thuvien.html', gallery=gallery, testimonials=testimonials, contact_info=contact_info)

@app.route('/bfat', defaults={'case_id': None})
@app.route('/bfat/<int:case_id>')
def bfat(case_id):
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM case_studies')
    cases = [dict(row) for row in cursor.fetchall()]
    if case_id is not None:
        cursor.execute('SELECT * FROM case_studies WHERE id = ?', (case_id,))
        selected_case = cursor.fetchone()
        if not selected_case:
            conn.close()
            abort(404)
        selected_case = dict(selected_case)
        conn.close()
        return render_template('bfat.html', cases=cases, selected_case=selected_case)
    conn.close()
    return render_template('bfat.html', cases=cases, selected_case=None)

@app.route('/dichvu')
def dichvu():
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM services LIMIT 1')
    service = cursor.fetchone()
    service = dict(service) if service else {}
    conn.close()
    return render_template('dichvu.html', service=service)

@app.route('/lienhe')
def lienhe():
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM contact_info LIMIT 1')
    contact_info = cursor.fetchone()
    contact_info = dict(contact_info) if contact_info else {}
    cursor.execute('SELECT * FROM faqs')
    faqs = [dict(row) for row in cursor.fetchall()]
    conn.close()
    return render_template('lienhe.html', contact_info=contact_info, faqs=faqs)

@app.route('/blog')
def blog():
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM posts WHERE status = "published" ORDER BY publish_date DESC')
    posts = [dict(row) for row in cursor.fetchall()]
    conn.close()
    return render_template('blog.html', posts=posts)

@app.route('/bloglist')
def bloglist():
    conn = get_db_connection()
    cursor = conn.cursor()
    page = request.args.get('page', 1, type=int)
    per_page = 6
    offset = (page - 1) * per_page
    cursor.execute('SELECT COUNT(*) FROM posts')
    total_posts = cursor.fetchone()[0]
    total_pages = (total_posts + per_page - 1) // per_page
    cursor.execute('SELECT * FROM posts WHERE status = "published" ORDER BY publish_date DESC LIMIT ? OFFSET ?', (per_page, offset))
    posts = [dict(row) for row in cursor.fetchall()]
    cursor.execute('SELECT categories, COUNT(*) as count FROM posts WHERE status = "published" GROUP BY categories')
    categories = [dict(row) for row in cursor.fetchall()]
    conn.close()
    return render_template('bloglist.html', posts=posts, categories=categories, page=page, total_pages=total_pages)

@app.route('/submit_comment', methods=['POST'])
def submit_comment():
    try:
        post_id = request.form.get('post_id')
        author_name = request.form.get('author_name')
        author_email = request.form.get('author_email')
        content = request.form.get('content')
        if not all([post_id, author_name, author_email, content]):
            flash('Vui lòng điền đầy đủ thông tin.', 'error')
            return redirect(url_for('bloglist'))
        if not post_id.isdigit():
            flash('Bài viết không hợp lệ.', 'error')
            return redirect(url_for('bloglist'))
        post_id = int(post_id)
        conn = get_db_connection()
        cursor = conn.cursor()
        cursor.execute('SELECT id FROM posts WHERE id = ?', (post_id,))
        if not cursor.fetchone():
            conn.close()
            flash('Bài viết không tồn tại.', 'error')
            return redirect(url_for('bloglist'))
        cursor.execute('''
            INSERT INTO comments (post_id, author_name, author_email, content)
            VALUES (?, ?, ?, ?)
        ''', (post_id, author_name, author_email, content))
        conn.commit()
        conn.close()
        flash('Bình luận của bạn đã được gửi thành công!', 'success')
    except Exception as e:
        flash(f'Có lỗi xảy ra: {str(e)}', 'error')
    return redirect(url_for('bloglist'))

@app.route('/get_post/<int:post_id>')
def get_post(post_id):
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM posts WHERE id = ?', (post_id,))
    post = cursor.fetchone()
    if not post:
        conn.close()
        return jsonify({'error': 'Post not found'}), 404
    post = dict(post)
    cursor.execute('SELECT * FROM comments WHERE post_id = ? ORDER BY created_at DESC', (post_id,))
    comments = [dict(row) for row in cursor.fetchall()]
    conn.close()
    return jsonify({'post': post, 'comments': comments})

@app.route('/vechungtoi')
def vechungtoi():
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM our_story LIMIT 1')
    our_story = cursor.fetchone()
    our_story = dict(our_story) if our_story else {}
    cursor.execute('SELECT * FROM timeline ORDER BY year ASC')
    timeline = [dict(row) for row in cursor.fetchall()]
    cursor.execute('SELECT * FROM mission_values')
    mission_values = [dict(row) for row in cursor.fetchall()]
    cursor.execute('SELECT * FROM team')
    team = [dict(row) for row in cursor.fetchall()]
    cursor.execute('SELECT * FROM technologies')
    technologies = [dict(row) for row in cursor.fetchall()]
    cursor.execute('SELECT * FROM contact_info LIMIT 1')
    contact_info = cursor.fetchone()
    contact_info = dict(contact_info) if contact_info else {}
    conn.close()
    return render_template('vechungtoi.html', our_story=our_story, timeline=timeline, mission_values=mission_values, team=team, technologies=technologies, contact_info=contact_info)

@app.route('/hanhtrinhkh/<int:case_id>')
def hanhtrinhkh(case_id):
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM case_studies WHERE id = ?', (case_id,))
    case = cursor.fetchone()
    if not case:
        conn.close()
        abort(404)
    case = dict(case)
    cursor.execute('SELECT * FROM patient_journey_milestones WHERE case_id = ?', (case_id,))
    milestones = [dict(row) for row in cursor.fetchall()]
    cursor.execute('SELECT * FROM team LIMIT 1')
    doctor = cursor.fetchone()
    doctor = dict(doctor) if doctor else {}
    conn.close()
    return render_template('hanhtrinhkh.html', case=case, milestones=milestones, doctor=doctor)

@app.errorhandler(401)
def unauthorized(e):
    return "Unauthorized - Vui lòng đăng nhập", 401

if __name__ == '__main__':
    app.run(host='0.0.0.0', port=7860, debug=True)