migrate_db.py

import os
import sys
import psycopg2
from dotenv import load_dotenv

def run_migration():
    print("=" * 60)
    print("🏥 City General Hospital — Supabase Migration Tool")
    print("=" * 60)

    load_dotenv()

    password = os.getenv("SUPABASE_DB_PASSWORD", "").strip()
    host = os.getenv("SUPABASE_DB_HOST", "").strip()

    if not password:
        password = input("🔑 Enter your Supabase DB password: ").strip()
        if not password:
            print("❌ Password cannot be empty.")
            sys.exit(1)

    if not host:
        host = input("🌐 Enter your Supabase DB host (e.g. db.xxxx.supabase.co): ").strip()

    print(f"\n⚡ Connecting to {host}...")

    try:
        conn = psycopg2.connect(
            host=host,
            port="5432",
            dbname="postgres",
            user="postgres",
            password=password
        )
        conn.autocommit = True
        cursor = conn.cursor()
        print("✅ Connected successfully!\n")

        sql = """
-- Drop tables if exist (fresh start)
DROP TABLE IF EXISTS appointments;
DROP TABLE IF EXISTS patients;

-- Create patients table
CREATE TABLE patients (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INTEGER,
    gender VARCHAR(10),
    disease VARCHAR(100),
    doctor VARCHAR(100),
    admission_date DATE,
    status VARCHAR(20) DEFAULT 'admitted'
);

-- Create appointments table
CREATE TABLE appointments (
    id SERIAL PRIMARY KEY,
    patient_name VARCHAR(100),
    doctor VARCHAR(100),
    date DATE,
    time VARCHAR(20),
    status VARCHAR(20) DEFAULT 'scheduled'
);

-- Insert patients
INSERT INTO patients (name, age, gender, disease, doctor, admission_date, status) VALUES
('Rahul Sharma', 34, 'Male', 'Diabetes', 'Dr. Mehta', '2025-05-01', 'admitted'),
('Priya Patel', 28, 'Female', 'Hypertension', 'Dr. Singh', '2025-05-03', 'admitted'),
('Amit Verma', 45, 'Male', 'Pneumonia', 'Dr. Mehta', '2025-05-05', 'admitted'),
('Sneha Joshi', 32, 'Female', 'Appendicitis', 'Dr. Rao', '2025-05-06', 'discharged'),
('Kiran Desai', 55, 'Male', 'Heart Disease', 'Dr. Singh', '2025-05-07', 'admitted'),
('Meera Nair', 41, 'Female', 'Diabetes', 'Dr. Mehta', '2025-05-08', 'admitted'),
('Rohan Gupta', 23, 'Male', 'Fracture', 'Dr. Rao', '2025-05-09', 'admitted'),
('Anita Shah', 60, 'Female', 'Arthritis', 'Dr. Singh', '2025-05-10', 'discharged'),
('Vijay Kumar', 38, 'Male', 'Typhoid', 'Dr. Mehta', '2025-05-11', 'admitted'),
('Pooja Iyer', 29, 'Female', 'Asthma', 'Dr. Rao', '2025-05-12', 'admitted'),
('Arjun Malhotra', 47, 'Male', 'Kidney Stone', 'Dr. Singh', '2025-05-13', 'admitted'),
('Divya Reddy', 35, 'Female', 'Migraine', 'Dr. Mehta', '2025-05-14', 'admitted'),
('Suresh Pillai', 52, 'Male', 'Diabetes', 'Dr. Rao', '2025-05-15', 'admitted'),
('Lakshmi Menon', 44, 'Female', 'Hypertension', 'Dr. Singh', '2025-05-16', 'admitted'),
('Nikhil Bansal', 31, 'Male', 'Dengue', 'Dr. Mehta', '2025-05-17', 'admitted');

-- Insert appointments
INSERT INTO appointments (patient_name, doctor, date, time, status) VALUES
('Rahul Sharma', 'Dr. Mehta', '2025-05-20', '10:00 AM', 'scheduled'),
('Priya Patel', 'Dr. Singh', '2025-05-20', '11:30 AM', 'scheduled'),
('Amit Verma', 'Dr. Mehta', '2025-05-21', '09:00 AM', 'scheduled'),
('Kiran Desai', 'Dr. Singh', '2025-05-21', '02:00 PM', 'scheduled'),
('Meera Nair', 'Dr. Mehta', '2025-05-22', '10:30 AM', 'scheduled'),
('Rohan Gupta', 'Dr. Rao', '2025-05-22', '03:00 PM', 'completed'),
('Vijay Kumar', 'Dr. Mehta', '2025-05-23', '08:30 AM', 'scheduled'),
('Pooja Iyer', 'Dr. Rao', '2025-05-23', '12:00 PM', 'cancelled'),
('Arjun Malhotra', 'Dr. Singh', '2025-05-24', '11:00 AM', 'scheduled'),
('Divya Reddy', 'Dr. Mehta', '2025-05-24', '04:00 PM', 'scheduled'),
('Suresh Pillai', 'Dr. Rao', '2025-05-25', '09:30 AM', 'scheduled'),
('Lakshmi Menon', 'Dr. Singh', '2025-05-25', '01:00 PM', 'scheduled'),
('Nikhil Bansal', 'Dr. Mehta', '2025-05-26', '10:00 AM', 'scheduled');
"""

        print("⚙️  Running migration...")
        cursor.execute(sql)

        cursor.execute("SELECT COUNT(*) FROM patients;")
        p_count = cursor.fetchone()[0]

        cursor.execute("SELECT COUNT(*) FROM appointments;")
        a_count = cursor.fetchone()[0]

        print("=" * 60)
        print(f"🎉 DONE! {p_count} patients | {a_count} appointments inserted.")
        print("=" * 60)

        cursor.close()
        conn.close()

    except Exception as e:
        print(f"\n❌ MIGRATION FAILED: {str(e)}")
        print("\n👉 Tips:")
        print("1. Check your DB password in Supabase dashboard")
        print("2. Make sure your IP is allowed in Supabase network settings")
        sys.exit(1)

if __name__ == "__main__":
    run_migration()