import sqlite3
import os

DB_PATH = os.path.join(os.path.dirname(__file__), "onever_drive.db")

def migrate():
    print(f"Connecting to database at {DB_PATH}...")
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()

    # 1. Create script_jobs table
    try:
        cursor.execute("""
        CREATE TABLE IF NOT EXISTS script_jobs (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name VARCHAR(255) NOT NULL,
            script_type VARCHAR(50) NOT NULL,
            script_path VARCHAR(1024) NOT NULL,
            schedule_cron VARCHAR(100) NOT NULL DEFAULT '0 2 * * *',
            is_active BOOLEAN NOT NULL DEFAULT 1,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
        )
        """)
        print("Table 'script_jobs' created successfully.")
    except sqlite3.OperationalError as e:
        print(f"Table 'script_jobs' could not be created: {e}")

    # 2. Create script_job_runs table
    try:
        cursor.execute("""
        CREATE TABLE IF NOT EXISTS script_job_runs (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            script_job_id INTEGER NOT NULL,
            started_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            completed_at DATETIME NULL,
            status VARCHAR(50) NOT NULL DEFAULT 'RUNNING',
            duration_seconds REAL NOT NULL DEFAULT 0.0,
            log_output TEXT NULL,
            FOREIGN KEY(script_job_id) REFERENCES script_jobs(id) ON DELETE CASCADE
        )
        """)
        print("Table 'script_job_runs' created successfully.")
    except sqlite3.OperationalError as e:
        print(f"Table 'script_job_runs' could not be created: {e}")

    conn.commit()
    conn.close()
    print("Migration finished.")

if __name__ == "__main__":
    migrate()