"""Create the trigger_jobs table for tracking face matching pipeline jobs."""

from config.db_config import get_db_connection


def setup_trigger_jobs_table():
    """Create trigger_jobs table if it doesn't exist."""
    connection = None
    cursor = None

    try:
        connection = get_db_connection()
        cursor = connection.cursor()

        create_table_query = """
        CREATE TABLE IF NOT EXISTS trigger_jobs (
            id INT AUTO_INCREMENT PRIMARY KEY,
            status VARCHAR(50) NOT NULL DEFAULT 'pending',
            result TEXT NULL,
            started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            completed_at TIMESTAMP NULL,
            INDEX idx_status (status),
            INDEX idx_started_at (started_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        """

        cursor.execute(create_table_query)
        connection.commit()

        print("✓ trigger_jobs table created successfully")
        print("  Columns: id, status, result, created_at, completed_at")
        return True

    except Exception as exc:
        print(f"✗ Failed to create trigger_jobs table: {exc}")
        return False

    finally:
        if cursor:
            cursor.close()
        if connection and connection.is_connected():
            connection.close()


if __name__ == "__main__":
    print("Setting up trigger_jobs table...")
    setup_trigger_jobs_table()