import sys
import os
import sqlite3
import logging
from datetime import datetime

# Add the path to your user-installed packages and project root
sys.path.insert(0, os.path.expanduser('~/.local/lib/python3.6/site-packages'))
sys.path.insert(0, os.path.dirname(os.path.dirname(os.path.abspath(__file__))))

from app import create_app
from app.models import BlogPost
from config import Config

# Set up logging
logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s')

def update_blog_post_table():
    try:
        app = create_app(Config)
        with app.app_context():
            # Connect to the SQLite database
            db_path = os.path.join(app.instance_path, 'lkgetaways.db')
            conn = sqlite3.connect(db_path)
            cursor = conn.cursor()

            logging.info(f"Connected to database: {db_path}")

            # Fetch all existing data
            cursor.execute("SELECT * FROM blog_post")
            existing_data = cursor.fetchall()
            logging.info(f"Fetched {len(existing_data)} existing blog posts")

            # Drop the existing table
            cursor.execute("DROP TABLE IF EXISTS blog_post")
            logging.info("Dropped existing blog_post table")

            # Create the new table with the updated schema
            cursor.execute("""
            CREATE TABLE blog_post (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                title VARCHAR(200) NOT NULL,
                subtitle VARCHAR(300),
                content TEXT NOT NULL,
                author VARCHAR(100) NOT NULL,
                created_at DATETIME,
                updated_at DATETIME,
                featured_image VARCHAR(200),
                slider_image1 VARCHAR(200),
                slider_image2 VARCHAR(200),
                slider_image3 VARCHAR(200),
                slug VARCHAR(200) UNIQUE NOT NULL,
                meta_description VARCHAR(160),
                meta_keywords VARCHAR(200)
            )
            """)
            logging.info("Created new blog_post table with updated schema")

            # Insert the existing data back into the new table
            cursor.executemany("""
            INSERT INTO blog_post (id, title, content, author, created_at, updated_at, featured_image, 
                                   slider_image1, slider_image2, slider_image3, slug, meta_description, meta_keywords)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            """, existing_data)
            logging.info(f"Inserted {len(existing_data)} rows into the new table")

            # Commit the changes and close the connection
            conn.commit()
            conn.close()

            logging.info("BlogPost table updated successfully!")

    except Exception as e:
        logging.error(f"An error occurred: {str(e)}")
        raise

if __name__ == "__main__":
    update_blog_post_table()