import sqlite3
import json

conn = sqlite3.connect('scheduler.db')
conn.row_factory = sqlite3.Row
cursor = conn.cursor()

print("=" * 80)
print("JOB 'DataHub Weekly Refresh'")
print("=" * 80)

# Find the job
cursor.execute("SELECT * FROM jobs WHERE name LIKE '%DataHub%Weekly%'")
jobs = cursor.fetchall()

for job in jobs:
    print(f"\nJob ID: {job['id']}")
    print(f"Name: {job['name']}")
    print(f"Schedule Type: {job['schedule_type']}")
    print(f"Schedule Config: {job['schedule_config']}")
    print(f"Enabled: {job['enabled']}")
    job_id = job['id']

    print("\n" + "=" * 80)
    print("SCHEDULES CHE ESEGUONO QUESTO JOB (DIRETTI)")
    print("=" * 80)

    # Find schedules targeting this job directly
    cursor.execute("""
        SELECT id, name, schedule_type, schedule_config, enabled, target_type
        FROM schedules
        WHERE target_type = 'job' AND target_id = ?
    """, (job_id,))

    schedules = cursor.fetchall()
    print(f"\nTrovati {len(schedules)} schedule diretti:\n")

    for sched in schedules:
        print(f"  Schedule ID: {sched['id']}")
        print(f"  Name: {sched['name']}")
        print(f"  Type: {sched['schedule_type']}")
        print(f"  Config: {sched['schedule_config']}")
        print(f"  Enabled: {sched['enabled']}")
        print("-" * 40)

    print("\n" + "=" * 80)
    print("GRUPPI CHE CONTENGONO QUESTO JOB")
    print("=" * 80)

    # Find groups containing this job
    cursor.execute("""
        SELECT jg.id, jg.name, jg.description, jgm.execution_order
        FROM job_groups jg
        JOIN job_group_members jgm ON jg.id = jgm.group_id
        WHERE jgm.job_id = ?
    """, (job_id,))

    groups = cursor.fetchall()
    print(f"\nTrovati {len(groups)} gruppi:\n")

    for grp in groups:
        print(f"  Group ID: {grp['id']}")
        print(f"  Name: {grp['name']}")
        print(f"  Execution Order: {grp['execution_order']}")

        # Find schedules for this group
        cursor.execute("""
            SELECT id, name, schedule_type, schedule_config, enabled
            FROM schedules
            WHERE target_type = 'group' AND target_id = ?
        """, (grp['id'],))

        group_schedules = cursor.fetchall()

        if group_schedules:
            print(f"  Schedules per questo gruppo:")
            for gs in group_schedules:
                print(f"    - ID: {gs['id']}, Name: {gs['name']}")
                print(f"      Type: {gs['schedule_type']}")
                print(f"      Config: {gs['schedule_config']}")
                print(f"      Enabled: {gs['enabled']}")
        print("-" * 40)

    print("\n" + "=" * 80)
    print("ESECUZIONI DI OGGI (2025-12-03)")
    print("=" * 80)

    # Get today's executions
    cursor.execute("""
        SELECT id, schedule_id, schedule_name, start_time, status, triggered_by
        FROM executions
        WHERE date(start_time) = '2025-12-03'
        AND schedule_name LIKE '%DataHub%Weekly%'
        ORDER BY start_time DESC
    """)

    execs = cursor.fetchall()
    print(f"\nTrovate {len(execs)} esecuzioni:\n")

    for ex in execs:
        print(f"  Execution ID: {ex['id']}")
        print(f"  Schedule Name: {ex['schedule_name']}")
        print(f"  Start Time: {ex['start_time']}")
        print(f"  Status: {ex['status']}")
        print(f"  Triggered By: {ex['triggered_by']}")
        print("-" * 40)

conn.close()