import sys
import asyncio
import os
import csv
import json
import subprocess
from datetime import datetime

SCRIPTS_DIR = os.path.dirname(os.path.abspath(__file__))
ROOT_DIR = os.path.dirname(SCRIPTS_DIR)

if ROOT_DIR not in sys.path:
    sys.path.insert(0, ROOT_DIR)

EXCLUDE_TEMPLATES = ['article.php', 'layout.php', 'index.php']


def get_available_templates():
    templates_dir = os.path.join(ROOT_DIR, 'templates')
    templates = []
    for f in sorted(os.listdir(templates_dir)):
        if f.endswith('.php') and f not in EXCLUDE_TEMPLATES:
            templates.append(f.replace('.php', ''))
    return templates


def get_config():
    config_path = os.path.join(ROOT_DIR, 'config.json')
    with open(config_path, 'r') as f:
        return json.load(f)


def run_export(auto=False, split_size=None, selected_template=None):
    config = get_config()
    templates = get_available_templates()
    default_split = config.get('split_size', 5000)
    default_template = config.get('template', 'modern')

    if split_size is not None and selected_template is not None:
        pass
    elif auto:
        split_size = default_split
        selected_template = default_template
    else:
        print("=" * 40)
        print("   Export Articles")
        print("=" * 40)
        print()

        split_input = input(f"Split size [{default_split}]: ").strip()
        if split_input.isdigit():
            split_size = int(split_input)
        else:
            split_size = default_split

        print()
        print("Available templates:")
        for i, t in enumerate(templates, 1):
            marker = " (current)" if t == default_template else ""
            print(f"  {i}. {t}{marker}")
        print()

        template_input = input(f"Template number [1-{len(templates)}] (default: {default_template}): ").strip()
        if template_input.isdigit() and 1 <= int(template_input) <= len(templates):
            selected_template = templates[int(template_input) - 1]
        elif template_input in templates:
            selected_template = template_input
        else:
            selected_template = default_template

        print()
        print(f"Split size : {split_size}")
        print(f"Template   : {selected_template}")
        print()

        confirm = input("Proceed with export? [Y/n]: ").strip().lower()
        if confirm == 'n':
            print("Export cancelled.")
            return

        print()
        print("Starting export...")
        print()

    exporter_path = os.path.join(ROOT_DIR, 'php', 'exporter.php')
    cmd = ['php', exporter_path, f'--split-size={split_size}', f'--template={selected_template}']
    result = subprocess.run(cmd, cwd=ROOT_DIR)

    if result.returncode != 0:
        print(f"Export failed with exit code {result.returncode}")
        sys.exit(result.returncode)


def run_deploy(auto=False):
    if auto:
        pass
    else:
        print("=" * 40)
        print("   Create & Upload to GCS")
        print("=" * 40)
        print()

        confirm = input("Proceed with deployment? [Y/n]: ").strip().lower()
        if confirm == 'n':
            print("Deployment cancelled.")
            return

        print()
        print("Starting deployment...")
        print()

    deploy_path = os.path.join(SCRIPTS_DIR, 'deploy.py')
    result = subprocess.run(
        [sys.executable, deploy_path],
        cwd=ROOT_DIR
    )

    if result.returncode != 0:
        print(f"Deployment failed with exit code {result.returncode}")
        sys.exit(result.returncode)


def run_submit(auto=False):
    if auto:
        pass
    else:
        print("=" * 40)
        print("   Submit to IndexNow")
        print("=" * 40)
        print()

        confirm = input("Proceed with submission? [Y/n]: ").strip().lower()
        if confirm == 'n':
            print("Submission cancelled.")
            return

        print()
        print("Starting submission...")
        print()

    submit_path = os.path.join(SCRIPTS_DIR, 'submit_now.py')
    result = subprocess.run(
        [sys.executable, submit_path],
        cwd=ROOT_DIR
    )

    if result.returncode != 0:
        print(f"Submission failed with exit code {result.returncode}")
        sys.exit(result.returncode)


def print_summary():
    report_folder = os.path.join(ROOT_DIR, 'report')
    deploy_json = os.path.join(report_folder, 'deploy-summary.json')
    submit_json = os.path.join(report_folder, 'submit-summary.json')

    deploy_data = {}
    if os.path.exists(deploy_json):
        with open(deploy_json, 'r', encoding='utf-8') as f:
            for item in json.load(f):
                deploy_data[item['bucket']] = item

    submit_data = {}
    if os.path.exists(submit_json):
        with open(submit_json, 'r', encoding='utf-8') as f:
            for item in json.load(f):
                submit_data[item['bucket']] = item

    all_buckets = list(deploy_data.keys()) or list(submit_data.keys())
    if not all_buckets:
        return

    def _folder_sort_key(bucket):
        d = deploy_data.get(bucket, {})
        s = submit_data.get(bucket, {})
        foldering = d.get('foldering', '') or s.get('foldering', '')
        parts = foldering.replace('folder_', '').split('_')
        try:
            return int(parts[0])
        except (ValueError, IndexError):
            return foldering

    all_buckets.sort(key=_folder_sort_key)

    print()
    print("=" * 130)
    print("  FULL DEPLOY SUMMARY")
    print("=" * 130)
    print(f"  {'No':>3} | {'Bucket Name':25s} | {'Source Database':20s} | {'Site URL':40s} | {'Files':>5} | {'Status':7s} | {'Submit':7s}")
    print("-" * 130)

    ok_count = 0
    skip_count = 0
    fail_count = 0

    for i, bucket in enumerate(all_buckets, 1):
        d = deploy_data.get(bucket, {})
        s = submit_data.get(bucket, {})

        bucket_display = (bucket[:22] + '...') if len(bucket) > 25 else bucket
        source = d.get('source', '') or s.get('source', '')
        source_display = (source[:17] + '...') if len(source) > 20 else source
        url = d.get('url', '')
        url_display = (url[:37] + '...') if len(url) > 40 else url
        files = d.get('files', 0)
        status = d.get('status', '-')
        submit = s.get('submit', '-')

        if status == 'SKIP':
            skip_count += 1
        elif status == 'FAILED' or submit == 'FAILED':
            fail_count += 1
        else:
            ok_count += 1

        print(f"  {i:3d} | {bucket_display:25s} | {source_display:20s} | {url_display:40s} | {files:5d} | {status:7s} | {submit:7s}")

    print("-" * 130)
    print(f"  Total: {len(all_buckets)} | OK: {ok_count} | SKIP: {skip_count} | FAIL: {fail_count}")
    print("=" * 130)


def generate_unified_report():
    report_folder = os.path.join(ROOT_DIR, 'report')
    deploy_json = os.path.join(report_folder, 'deploy-summary.json')
    submit_json = os.path.join(report_folder, 'submit-summary.json')

    deploy_data = {}
    if os.path.exists(deploy_json):
        with open(deploy_json, 'r', encoding='utf-8') as f:
            for item in json.load(f):
                deploy_data[item['bucket']] = item

    submit_data = {}
    if os.path.exists(submit_json):
        with open(submit_json, 'r', encoding='utf-8') as f:
            for item in json.load(f):
                submit_data[item['bucket']] = item

    all_buckets = list(deploy_data.keys()) or list(submit_data.keys())
    if not all_buckets:
        return None

    def _folder_sort_key(bucket):
        d = deploy_data.get(bucket, {})
        s = submit_data.get(bucket, {})
        foldering = d.get('foldering', '') or s.get('foldering', '')
        parts = foldering.replace('folder_', '').split('_')
        try:
            return int(parts[0])
        except (ValueError, IndexError):
            return foldering

    all_buckets.sort(key=_folder_sort_key)

    ts = datetime.now().strftime("%Y%m%d_%H%M%S")
    csv_file = os.path.join(report_folder, f"report-{ts}.csv")

    with open(csv_file, 'w', newline='', encoding='utf-8') as f:
        writer = csv.writer(f)
        writer.writerow([
            'No', 'Bucket', 'Folder', 'Name', 'Source Database', 'Files',
            'Status', 'Created', 'Public', 'Uploaded',
            'Submit', 'URLs', 'URL', 'Error', 'Duration'
        ])

        for i, bucket in enumerate(all_buckets, 1):
            d = deploy_data.get(bucket, {})
            s = submit_data.get(bucket, {})

            deploy_status = d.get('status', '-')
            created = d.get('create', '-')
            public = d.get('public', '-')
            uploaded = d.get('upload', '-')
            submit_status = s.get('submit', '-')
            url_count = s.get('url_count', 0)
            url = d.get('url', '')
            error = d.get('error', '')
            duration = d.get('duration', '')
            source = d.get('source', '') or s.get('source', '')

            if deploy_status == 'FAILED' or submit_status == 'FAILED':
                overall = 'FAILED'
            elif deploy_status == 'SKIP':
                overall = 'SKIP'
            elif deploy_status == 'SUCCESS' and submit_status == 'SUCCESS':
                overall = 'SUCCESS'
            elif deploy_status == 'SUCCESS':
                overall = 'SUCCESS'
            else:
                overall = 'FAILED'

            writer.writerow([
                i,
                bucket,
                d.get('foldering', ''),
                d.get('name', ''),
                source,
                d.get('files', 0),
                overall,
                created,
                public,
                uploaded,
                submit_status,
                url_count,
                url,
                error,
                duration,
            ])

    print(f"\nReport saved: {csv_file}")

    if os.path.exists(deploy_json):
        os.remove(deploy_json)
    if os.path.exists(submit_json):
        os.remove(submit_json)

    return csv_file


def run_full_deploy():
    print("=" * 40)
    print("   Full Deploy Pipeline")
    print("=" * 40)
    print()
    print("This will run the following steps sequentially:")
    print("  0. Generate CREATE.csv")
    print("  1. Export articles to HTML")
    print("  2. Create buckets & upload to GCS")
    print("  3. Submit to IndexNow")
    print()

    confirm = input("Proceed with full deploy? [Y/n]: ").strip().lower()
    if confirm == 'n':
        print("Pipeline cancelled.")
        return

    config = get_config()
    templates = get_available_templates()
    default_split = config.get('split_size', 5000)
    default_template = config.get('template', 'modern')

    print()
    print("Export settings:")
    print()

    split_input = input(f"Split size [{default_split}]: ").strip()
    split_size = int(split_input) if split_input.isdigit() else default_split

    print()
    print("Available templates:")
    for i, t in enumerate(templates, 1):
        marker = " (current)" if t == default_template else ""
        print(f"  {i}. {t}{marker}")
    print()

    template_input = input(f"Template number [1-{len(templates)}] (default: {default_template}): ").strip()
    if template_input.isdigit() and 1 <= int(template_input) <= len(templates):
        selected_template = templates[int(template_input) - 1]
    elif template_input in templates:
        selected_template = template_input
    else:
        selected_template = default_template

    print()
    print(f"Split size : {split_size}")
    print(f"Template   : {selected_template}")
    print()

    print("=" * 40)
    print("   Step 0/4: Generate CREATE.csv")
    print("=" * 40)
    print()
    from generate_create_csv import main as gen_main
    gen_main(split_size=split_size)

    print()
    print("=" * 40)
    print("   Step 1/4: Export Articles")
    print("=" * 40)
    print()
    run_export(auto=True, split_size=split_size, selected_template=selected_template)

    print()
    print("=" * 40)
    print("   Step 2/4: Create & Upload to GCS")
    print("=" * 40)
    print()
    run_deploy(auto=True)

    print()
    print("=" * 40)
    print("   Step 3/4: Submit to IndexNow")
    print("=" * 40)
    print()
    run_submit(auto=True)

    print_summary()
    generate_unified_report()


def generate_scrape_report():
    from pinscrape.database import get_all_db_files, get_db_connection

    db_files = get_all_db_files()
    if not db_files:
        print("No databases found for report.")
        return

    report_folder = os.path.join(ROOT_DIR, 'report')
    os.makedirs(report_folder, exist_ok=True)

    for db_file in db_files:
        db_path = str(db_file)
        db_name = os.path.splitext(os.path.basename(db_path))[0]

        conn = get_db_connection(db_path)
        total = conn.execute("SELECT COUNT(*) FROM posts").fetchone()[0]
        if total == 0:
            conn.close()
            continue

        images_done = conn.execute(
            "SELECT COUNT(*) FROM posts WHERE images IS NOT NULL AND images != '' AND images != '[]'"
        ).fetchone()[0]
        titles_done = conn.execute(
            "SELECT COUNT(*) FROM posts WHERE ai_title IS NOT NULL AND ai_title != ''"
        ).fetchone()[0]
        articles_done = conn.execute(
            "SELECT COUNT(*) FROM posts WHERE ai_content IS NOT NULL AND ai_content != ''"
        ).fetchone()[0]
        conn.close()

        img_pct = round(images_done / total * 100) if total else 0
        ttl_pct = round(titles_done / total * 100) if total else 0
        art_pct = round(articles_done / total * 100) if total else 0

        report_content = (
            f"Image Scrapped : {images_done}/{total} ({img_pct}%)\n"
            f"Title Created : {titles_done}/{total} ({ttl_pct}%)\n"
            f"Article Created : {articles_done}/{total} ({art_pct}%)\n"
        )

        report_path = os.path.join(report_folder, f"scrape-{db_name}.txt")
        with open(report_path, 'w', encoding='utf-8') as f:
            f.write(report_content)

        print(f"\nReport saved: {report_path}")
        print(report_content)


def run_workflow():
    print("=" * 40)
    print("   Full Workflow")
    print("=" * 40)
    print()
    print("This will run the following steps sequentially:")
    print("  1. Scrape images from Pinterest")
    print("  2. Generate AI titles")
    print("  3. Generate AI articles")
    print()

    confirm = input("Proceed with full workflow? [Y/n]: ").strip().lower()
    if confirm == 'n':
        print("Workflow cancelled.")
        return

    print()
    print("=" * 40)
    print("   Step 1/3: Scrape Images")
    print("=" * 40)
    print()
    from scrape import main as scrape_main
    scrape_main()

    print()
    print("=" * 40)
    print("   Step 2/3: Generate AI Titles")
    print("=" * 40)
    print()
    from ai_generator import run_ai_generation
    asyncio.run(run_ai_generation("title"))

    print()
    print("=" * 40)
    print("   Step 3/3: Generate AI Articles")
    print("=" * 40)
    print()
    asyncio.run(run_ai_generation("article"))

    generate_scrape_report()

    print()
    print("=" * 40)
    print("   Workflow completed!")
    print("=" * 40)


def main():
    if len(sys.argv) < 2:
        print("Usage: python scripts/cli.py <command>")
        print()
        print("Commands:")
        print("  convert       - Convert keywords to database")
        print("  scrape        - Scrape images from Pinterest")
        print("  ai title      - Generate AI titles")
        print("  ai article    - Generate AI articles")
        print("  clean-title   - Clean quotes from AI titles")
        print("  delete-ai     - Delete all AI content")
        print("  workflow      - Full workflow (scrape + AI title + AI article)")
        print("  export        - Export articles to HTML")
        print("  deploy        - Create buckets & upload to GCS")
        print("  submit        - Submit to IndexNow")
        print("  full-deploy   - Export + Deploy + Submit")
        return

    cmd = sys.argv[1]

    if cmd == "convert":
        from convert import main as convert_main
        convert_main()

    elif cmd == "scrape":
        from scrape import main as scrape_main
        scrape_main()

    elif cmd == "ai":
        if len(sys.argv) < 3 or sys.argv[2] not in ("title", "article"):
            print("Usage: python scripts/cli.py ai <title|article>")
            return
        from ai_generator import run_ai_generation
        asyncio.run(run_ai_generation(sys.argv[2]))

    elif cmd == "workflow":
        run_workflow()

    elif cmd == "export":
        run_export()

    elif cmd == "deploy":
        auto = "--auto" in sys.argv
        run_deploy(auto=auto)

    elif cmd == "submit":
        auto = "--auto" in sys.argv
        run_submit(auto=auto)

    elif cmd == "clean-title":
        from cleaning_title import cleaning_title
        cleaning_title()

    elif cmd == "delete-ai":
        from delete_ai_content import delete_ai_content
        confirm = input("This will clear all AI Title and AI Content from all databases. Continue? (y/N): ")
        if confirm.lower() == 'y':
            delete_ai_content()
            print("Done.")
        else:
            print("Cancelled.")

    elif cmd == "full-deploy":
        run_full_deploy()

    elif cmd == "full-deploy-report":
        print_summary()
        generate_unified_report()

    elif cmd == "full-deploy-generate":
        from generate_create_csv import main as gen_main
        split = None
        if len(sys.argv) > 2 and sys.argv[2].isdigit():
            split = int(sys.argv[2])
        gen_main(split_size=split)

    else:
        print(f"Unknown command: {cmd}")
        print("Run 'python scripts/cli.py' for usage info.")


if __name__ == "__main__":
    main()