import sys
import os

# Add backend directory to sys.path for db import
sys.path.append(os.path.dirname(os.path.abspath(__file__)))

import db
import mysql.connector

def migrate():
    print("Starting migration: Adding review columns to orders table...")
    
    queries = [
        "ALTER TABLE orders ADD COLUMN review_text TEXT DEFAULT NULL",
        "ALTER TABLE orders ADD COLUMN rating INT DEFAULT 0",
        "ALTER TABLE orders ADD COLUMN review_approved TINYINT(1) DEFAULT 0"
    ]
    
    for q in queries:
        try:
            db.execute_commit(q)
            col_name = q.split("ADD COLUMN ")[1].split(" ")[0]
            print(f"  [SUCCESS] Column '{col_name}' added.")
        except Exception as e:
            if "Duplicate column" in str(e):
                col_name = q.split("ADD COLUMN ")[1].split(" ")[0]
                print(f"  [INFO] Column '{col_name}' already exists.")
            else:
                print(f"  [ERROR] Failed to execute query: {q}. Error: {e}")
                
    print("Migration finished.")

if __name__ == "__main__":
    migrate()