backend/migrate_db.py

import sqlite3
import os

db_path = os.path.join(os.path.dirname(os.path.abspath(__file__)), "andesops.db")

def migrate():
    if not os.path.exists(db_path):
        print(f"Database not found at {db_path}")
        return

    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()

    columns_to_add = [
        ("status_code", "VARCHAR(10)"),
        ("type", "VARCHAR(20)"),
        ("currency", "VARCHAR(10)"),
        ("publication_date", "DATETIME"),
        ("buyer_region", "VARCHAR(100)")
    ]

    for col_name, col_type in columns_to_add:
        try:
            cursor.execute(f"ALTER TABLE tenders ADD COLUMN {col_name} {col_type}")
            print(f"Added column {col_name}")
        except sqlite3.OperationalError as e:
            if "duplicate column name" in str(e).lower():
                print(f"Column {col_name} already exists.")
            else:
                print(f"Error adding {col_name}: {e}")

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

if __name__ == "__main__":
    migrate()