import sqlite3

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

    with open('schedules_report.txt', 'w', encoding='utf-8') as f:
        f.write("REPORT SCHEDULES DATAHUB\n")
        f.write("=" * 80 + "\n\n")

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

        f.write(f"Jobs trovati: {len(jobs)}\n\n")

        for job in jobs:
            f.write(f"Job ID: {job['id']}\n")
            f.write(f"Name: {job['name']}\n")
            f.write(f"Schedule Type: {job['schedule_type']}\n")
            f.write(f"Schedule Config: {job['schedule_config']}\n")
            f.write(f"Enabled: {job['enabled']}\n\n")

            job_id = job['id']

            # Schedules diretti
            cursor.execute("""
                SELECT * FROM schedules
                WHERE target_type = 'job' AND target_id = ?
            """, (job_id,))

            schedules = cursor.fetchall()
            f.write(f"Schedule diretti per questo job: {len(schedules)}\n")

            for sched in schedules:
                f.write(f"  - Schedule ID: {sched['id']}, Name: {sched['name']}\n")
                f.write(f"    Type: {sched['schedule_type']}, Config: {sched['schedule_config']}\n")
                f.write(f"    Enabled: {sched['enabled']}\n")

            f.write("\n")

            # Groups
            cursor.execute("""
                SELECT jg.*, 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()
            f.write(f"Gruppi contenenti questo job: {len(groups)}\n")

            for grp in groups:
                f.write(f"  - Group ID: {grp['id']}, Name: {grp['name']}\n")
                f.write(f"    Order: {grp['execution_order']}\n")

                # Schedules del gruppo
                cursor.execute("""
                    SELECT * FROM schedules
                    WHERE target_type = 'group' AND target_id = ?
                """, (grp['id'],))

                gs_list = cursor.fetchall()
                for gs in gs_list:
                    f.write(f"    Schedule: ID={gs['id']}, Name={gs['name']}\n")
                    f.write(f"    Type={gs['schedule_type']}, Config={gs['schedule_config']}, Enabled={gs['enabled']}\n")

            f.write("\n" + "=" * 80 + "\n\n")

        # Esecuzioni oggi
        f.write("ESECUZIONI OGGI (2025-12-03)\n")
        f.write("=" * 80 + "\n\n")

        cursor.execute("""
            SELECT * FROM executions
            WHERE date(start_time) = '2025-12-03'
            AND schedule_name LIKE '%DataHub%'
            ORDER BY start_time DESC
            LIMIT 20
        """)

        execs = cursor.fetchall()
        f.write(f"Esecuzioni trovate: {len(execs)}\n\n")

        for ex in execs:
            f.write(f"ID: {ex['id']}, Schedule: {ex['schedule_name']}\n")
            f.write(f"Time: {ex['start_time']}, Status: {ex['status']}, Triggered: {ex['triggered_by']}\n\n")

    conn.close()

    print("Report creato: schedules_report.txt")

except Exception as e:
    print(f"Errore: {e}")
    import traceback
    traceback.print_exc()