from flask import Blueprint, render_template, request, flash, redirect, url_for, session, current_app
from extensions import get_db_connection
from decorators import login_required
import os
import json
import requests
import time
from werkzeug.utils import secure_filename

post_bp = Blueprint('post_bp', __name__)

# =========================================================
# 1. VIEW POSTS ROUTE (Uses posts_linkedin & user_id)
# =========================================================
@post_bp.route("/view_posts")
@login_required
def view_posts():
    user_id = session.get('user_id')
    user_pic = session.get('user_pic')

    conn = get_db_connection()
    cur = conn.cursor()
    # Query using the exact table structure
    cur.execute("SELECT * FROM posts_linkedin WHERE user_id = %s ORDER BY id DESC", (user_id,))
    posts = cur.fetchall()
    cur.close()

    for post in posts:
        # Map 'scheduled_at' to 'display_date' for the table
        post["display_date"] = post.get("scheduled_at")

        # Process 'media_urls' CSV into a list of full URLs
        image_data = post.get("media_urls")
        image_urls = []

        if image_data:
            try:
                # Split comma-separated filenames
                img_list = [img.strip() for img in image_data.split(',') if img.strip()]
                # Build correct static paths
                image_urls = [
                    url_for('static', filename=f"uploaded_post_img/{img_name}")
                    for img_name in img_list
                ]
            except Exception as e:
                print(f"[ERROR] Image parsing failed for post {post.get('id')}: {e}")

        post["image_urls"] = image_urls

    # We pass 'posts=posts' to the template
    return render_template("view_posts.html", posts=posts, user_pic=user_pic)


# =========================================================
# 2. UPDATE POST ROUTE
# =========================================================
import os
import json
import time
from flask import current_app, session, request, flash, redirect, url_for
from werkzeug.utils import secure_filename

@post_bp.route('/update_post/<int:post_id>', methods=['GET', 'POST'])
@login_required
def update_post(post_id):
    user_id = session.get('user_id')
    conn = get_db_connection()
    cursor = conn.cursor()

    if request.method == 'GET':
        cursor.execute("SELECT * FROM posts_linkedin WHERE id=%s AND user_id=%s", (post_id, user_id))
        post = cursor.fetchone()
        cursor.close()
        
        if not post:
            flash("❌ Post not found.", "danger")
            return redirect(url_for('post_bp.view_posts'))
            
        return render_template('update_post.html', post=post)

    if request.method == 'POST':
        new_date = request.form.get('post_date')
        new_content = request.form.get('content')
        new_image = request.files.get('image')

        try:
            sql = "UPDATE posts_linkedin SET scheduled_at=%s, content=%s"
            params = [new_date, new_content]

            if new_image and new_image.filename:
                # 1. DELETE OLD IMAGE
                cursor.execute("SELECT media_urls FROM posts_linkedin WHERE id=%s", (post_id,))
                old_post = cursor.fetchone()
                
                if old_post and old_post['media_urls']:
                    raw_media = old_post['media_urls']
                    
                    # Smart Delete: Check if it's a JSON list or a simple string
                    try:
                        if raw_media.startswith('['):
                            # It's a list like ["img.png"]
                            file_list = json.loads(raw_media)
                            for f in file_list:
                                old_path = os.path.join(current_app.root_path, 'static', 'uploaded_post_img', f)
                                if os.path.exists(old_path):
                                    os.remove(old_path)
                        else:
                            # It's a simple string like "img.png"
                            old_path = os.path.join(current_app.root_path, 'static', 'uploaded_post_img', raw_media)
                            if os.path.exists(old_path):
                                os.remove(old_path)
                    except Exception as e:
                        print(f"Cleanup Warning: {e}")

                # 2. SAVE NEW IMAGE (As a simple string)
                filename = secure_filename(new_image.filename)
                new_filename = f"updated_{int(time.time())}_{filename}"
                save_path = os.path.join(current_app.root_path, 'static', 'uploaded_post_img', new_filename)
                new_image.save(save_path)
                
                # FIX: Save just the filename, NOT json.dumps()
                sql += ", media_urls=%s"
                params.append(new_filename) 

            sql += " WHERE id=%s AND user_id=%s"
            params.extend([post_id, user_id])

            cursor.execute(sql, tuple(params))
            conn.commit()
            cursor.close()
            conn.close()
            
            flash("✅ Post updated successfully!", "success")
            return redirect(url_for('post_bp.view_posts'))

        except Exception as e:
            print(f"Update Error: {e}")
            flash("❌ Error updating post.", "danger")
            return redirect(url_for('post_bp.view_posts'))


# =========================================================
# 3. DELETE POST ROUTE
# =========================================================
@post_bp.route('/delete_post/<int:post_id>', methods=['GET', 'POST'])
@login_required
def delete_post(post_id):
    user_id = session.get('user_id')
    conn = get_db_connection()
    cursor = conn.cursor()
    
    # 1. FIX: Query 'media_url' instead of 'image'
    cursor.execute("SELECT media_urls FROM posts_linkedin WHERE id=%s AND user_id=%s", (post_id, user_id))
    post = cursor.fetchone()
    
    if not post:
        cursor.close()
        flash("❌ Post not found or permission denied.", "danger")
        return redirect(url_for('post_bp.view_posts'))
    
    # 2. FIX: File Deletion Logic (Handles JSON lists or Simple Strings)
    if post and post['media_urls']:
        raw_media = post['media_urls']
        try:
            # Check if it's a JSON list (e.g., '["img1.png", "img2.png"]')
            if raw_media.startswith('[') and raw_media.endswith(']'):
                file_list = json.loads(raw_media)
                for f in file_list:
                    file_path = os.path.join(current_app.root_path, 'static', 'uploaded_post_img', f.strip())
                    if os.path.exists(file_path):
                        os.remove(file_path)
            else:
                # It's a simple string (comma-separated or single file)
                # Use split(',') just in case it's a legacy CSV format
                images = raw_media.split(',')
                for img in images:
                    file_path = os.path.join(current_app.root_path, 'static', 'uploaded_post_img', img.strip())
                    if os.path.exists(file_path):
                        os.remove(file_path)

        except Exception as e:
            print(f"[ERROR] Could not delete image file: {e}")

    # 3. Delete the record from DB
    cursor.execute("DELETE FROM posts_linkedin WHERE id=%s AND user_id=%s", (post_id, user_id))
    conn.commit()
    cursor.close()
    conn.close()
    
    flash("🗑️ Post deleted successfully.", "success")
    return redirect(url_for('post_bp.view_posts'))


# =========================================================
# 4. POST TO LINKEDIN ROUTE (Uses posts_linkedin)
# =========================================================
@post_bp.route('/post_to_linkedin', methods=['POST'])
@login_required
def post_to_linkedin():
    access_token = session.get("linkedin_token")
    user_id = session.get('user_id')
    post_id = request.form.get('post_id')
    content = request.form.get('content', '').strip()
    
    if not access_token:
        flash("⚠️ Please connect to LinkedIn first.", "warning")
        return redirect(url_for('social_bp.connect_hub'))

    conn = get_db_connection()
    cursor = conn.cursor()
    
    # 1. Fetch using CORRECT column names from your table structure
    cursor.execute("SELECT media_urls, posted_urn FROM posts_linkedin WHERE id=%s AND user_id=%s", (post_id, user_id))
    post_data = cursor.fetchone()
    
    if not post_data:
        flash("❌ Post not found.", "danger")
        cursor.close()
        conn.close()
        return redirect(url_for('post_bp.view_posts'))

    media_data_str = post_data.get('media_urls') 
    author_urn = post_data.get('posted_urn')
    person_urn = f"urn:li:person:{author_urn}"

    uploaded_asset_urns = []
    
    # 2. STEP 1: Multi-Image Upload Logic
    if media_data_str:
        image_list = [img.strip() for img in media_data_str.split(',') if img.strip()]
        
        for img_name in image_list:
            image_path = os.path.join(current_app.root_path, 'static', 'uploaded_post_img', img_name)
            
            if os.path.exists(image_path):
                # A. Register the Upload
                register_url = "https://api.linkedin.com/v2/assets?action=registerUpload"
                register_json = {
                    "registerUploadRequest": {
                        "recipes": ["urn:li:digitalmediaRecipe:feedshare-image"],
                        "owner": person_urn,
                        "serviceRelationships": [{"relationshipType": "OWNER", "identifier": "urn:li:userGeneratedContent"}]
                    }
                }
                headers = {"Authorization": f"Bearer {access_token}", "Content-Type": "application/json"}
                reg_resp = requests.post(register_url, headers=headers, json=register_json)
                
                if reg_resp.status_code == 200:
                    reg_data = reg_resp.json()
                    upload_url = reg_data['value']['uploadMechanism']['com.linkedin.digitalmedia.uploading.MediaUploadHttpRequest']['uploadUrl']
                    asset_urn = reg_data['value']['asset']
                    
                    # B. Upload Binary File Data
                    with open(image_path, 'rb') as img_file:
                        upload_resp = requests.put(upload_url, data=img_file, headers={"Authorization": f"Bearer {access_token}"})
                    
                    if upload_resp.status_code == 201:
                        uploaded_asset_urns.append(asset_urn)

    # 3. STEP 2: Create the Post
    post_url = "https://api.linkedin.com/v2/ugcPosts"
    post_body = {
        "author": person_urn,
        "lifecycleState": "PUBLISHED",
        "specificContent": {
            "com.linkedin.ugc.ShareContent": {
                "shareCommentary": {"text": content},
                "shareMediaCategory": "NONE"
            }
        },
        "visibility": {"com.linkedin.ugc.MemberNetworkVisibility": "PUBLIC"}
    }

    # Attach images if they were successfully uploaded
    if uploaded_asset_urns:
        post_body["specificContent"]["com.linkedin.ugc.ShareContent"]["shareMediaCategory"] = "IMAGE"
        post_body["specificContent"]["com.linkedin.ugc.ShareContent"]["media"] = [
            {"status": "READY", "media": asset, "title": {"text": "LearnTrail AI Post"}} 
            for asset in uploaded_asset_urns
        ]

    headers = {
        "Authorization": f"Bearer {access_token}", 
        "Content-Type": "application/json",
        "X-Restli-Protocol-Version": "2.0.0"
    }
    
    final_resp = requests.post(post_url, headers=headers, json=post_body)
    
    # 4. STEP 3: Final Status Update (FIXED SQL SYNTAX)
    if final_resp.status_code in [200, 201]:
        # REMOVED extra comma before WHERE clause
        cursor.execute("UPDATE posts_linkedin SET status='posted' WHERE id=%s", (post_id,))
        conn.commit()
        flash("✅ Successfully posted to LinkedIn!", "success")
    else:
        print(f"[ERROR] LinkedIn API: {final_resp.text}")
        flash("❌ LinkedIn failed to publish. Please try again.", "danger")

    cursor.close()
    conn.close()
    return redirect(url_for('post_bp.view_posts'))


# =========================================================
# 5. BULK DELETE ROUTE
# =========================================================
@post_bp.route('/bulk_delete_posts', methods=['POST'])
@login_required
def bulk_delete_posts():
    user_id = session.get('user_id')
    # Get list of IDs from the form checkboxes
    post_ids = request.form.getlist('post_ids') 
    
    if not post_ids:
        flash("⚠️ No posts selected.", "warning")
        return redirect(url_for('post_bp.view_posts'))

    try:
        # Convert all IDs to integers for safety
        clean_ids = [int(pid) for pid in post_ids]
        conn = get_db_connection()
        cursor = conn.cursor()
        
        # Create a placeholder string like %s, %s, %s for the SQL IN clause
        format_strings = ','.join(['%s'] * len(clean_ids))
        
        # 1. Fetch media_url (NOT image) for cleanup
        # We query the files first so we can delete them from the server
        query = f"SELECT media_urls FROM posts_linkedin WHERE user_id=%s AND id IN ({format_strings})"
        cursor.execute(query, [user_id] + clean_ids)
        posts_to_delete = cursor.fetchall()

        # 2. Iterate and Delete Files
        for post in posts_to_delete:
            if post and post['media_urls']:
                raw_media = post['media_urls']
                try:
                    # Check if it's a JSON list (New Format: ["img1.png"])
                    if raw_media.startswith('[') and raw_media.endswith(']'):
                        file_list = json.loads(raw_media)
                        for f in file_list:
                            file_path = os.path.join(current_app.root_path, 'static', 'uploaded_post_img', f.strip())
                            if os.path.exists(file_path):
                                os.remove(file_path)
                    else:
                        # It's a simple string (Old Format: img1.png)
                        # We split by comma just in case it's a legacy CSV
                        images = raw_media.split(',')
                        for img in images:
                            file_path = os.path.join(current_app.root_path, 'static', 'uploaded_post_img', img.strip())
                            if os.path.exists(file_path):
                                os.remove(file_path)
                except Exception as e:
                    print(f"[WARNING] Could not delete file: {e}")

        # 3. Delete the records from Database
        delete_query = f"DELETE FROM posts_linkedin WHERE user_id=%s AND id IN ({format_strings})"
        cursor.execute(delete_query, [user_id] + clean_ids)
        conn.commit()
        
        deleted_count = cursor.rowcount
        cursor.close()
        conn.close()
        
        flash(f"✅ Successfully deleted {deleted_count} posts.", "success")

    except Exception as e:
        print(f"[ERROR] Bulk delete failed: {e}")
        flash("❌ Delete failed due to a system error.", "danger")
    
    return redirect(url_for('post_bp.view_posts'))