setup_database.py

import sqlite3
import random
from datetime import datetime, timedelta

def setup_database():
    
    conn = sqlite3.connect('clinic.db')
    cursor = conn.cursor()

    cursor.executescript('''
    CREATE TABLE IF NOT EXISTS patients (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        first_name TEXT NOT NULL,
        last_name TEXT NOT NULL,
        email TEXT,
        phone TEXT,
        date_of_birth DATE,
        gender TEXT,
        city TEXT,
        registered_date DATE
    );

    CREATE TABLE IF NOT EXISTS doctors (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        specialization TEXT,
        department TEXT,
        phone TEXT
    );

    CREATE TABLE IF NOT EXISTS appointments (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        patient_id INTEGER,
        doctor_id INTEGER,
        appointment_date DATETIME,
        status TEXT,
        notes TEXT,
        FOREIGN KEY(patient_id) REFERENCES patients(id),
        FOREIGN KEY(doctor_id) REFERENCES doctors(id)
    );

    CREATE TABLE IF NOT EXISTS treatments (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        appointment_id INTEGER,
        treatment_name TEXT,
        cost REAL,
        duration_minutes INTEGER,
        FOREIGN KEY(appointment_id) REFERENCES appointments(id)
    );

    CREATE TABLE IF NOT EXISTS invoices (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        patient_id INTEGER,
        invoice_date DATE,
        total_amount REAL,
        paid_amount REAL,
        status TEXT,
        FOREIGN KEY(patient_id) REFERENCES patients(id)
    );
    ''')

    specializations = ['Dermatology', 'Cardiology', 'Orthopedics', 'General', 'Pediatrics']
    cities = ['New York', 'London', 'Mumbai', 'Tokyo', 'Berlin', 'Paris', 'Dubai', 'Sydney']
    
    # Insert 15 Doctors
    for i in range(15):
        spec = random.choice(specializations)
        cursor.execute("INSERT INTO doctors (name, specialization, department, phone) VALUES (?, ?, ?, ?)",
                       (f"Dr. Smith_{i}", spec, f"{spec} Dept", f"555-010{i}"))

    # Insert 200 Patients
    for i in range(1, 201):
        cursor.execute("INSERT INTO patients (first_name, last_name, email, city, gender, registered_date) VALUES (?, ?, ?, ?, ?, ?)",
                       (f"Patient_{i}", f"Last_{i}", f"p{i}@clinic.com", random.choice(cities), random.choice(['M', 'F']), 
                        (datetime.now() - timedelta(days=random.randint(0, 365))).date()))

    # Insert 500 Appointments, 350 Treatments, and 300 Invoices
    for i in range(1, 501):
        p_id = random.randint(1, 200)
        d_id = random.randint(1, 15)
        date = datetime.now() - timedelta(days=random.randint(0, 365))
        status = random.choice(['Completed', 'Scheduled', 'Cancelled', 'No-Show'])
        cursor.execute("INSERT INTO appointments (patient_id, doctor_id, appointment_date, status) VALUES (?, ?, ?, ?)",
                       (p_id, d_id, date, status))
        
        # Link treatments to completed appointments
        if status == 'Completed' and i <= 350:
            cursor.execute("INSERT INTO treatments (appointment_id, treatment_name, cost, duration_minutes) VALUES (?, ?, ?, ?)",
                           (i, "Medical Procedure", random.uniform(50, 5000), random.randint(15, 120)))
        
        # Link invoices
        if i <= 300:
            total = random.uniform(100, 5000)
            inv_status = random.choice(['Paid', 'Pending', 'Overdue'])
            paid = total if inv_status == 'Paid' else 0
            cursor.execute("INSERT INTO invoices (patient_id, invoice_date, total_amount, paid_amount, status) VALUES (?, ?, ?, ?, ?)",
                           (p_id, date.date(), total, paid, inv_status))

    conn.commit()
    print(f"Created 200 patients, 15 doctors, 500 appointments, 350 treatments, and 300 invoices.")
    conn.close()

if __name__ == "__main__":
    setup_database()