import mysql.connector from mysql.connector import Error import hashlib # Database configuration - UPDATE THESE! DB_CONFIG = { 'host': 'server.prottyashi.org', 'user': 'drr_drruser', # Change to your MySQL username 'password': '2,0B-a!V+C80]JX$', # Change to your MySQL password 'database': 'drr_management', } def get_db_connection(): """Create and return a MySQL database connection""" try: connection = mysql.connector.connect(**DB_CONFIG) return connection except Error as e: print(f"Error connecting to MySQL: {e}") return None def hash_password(password): return hashlib.sha256(password.encode()).hexdigest() def init_db(): """Initialize database with tables and demo data""" # First connect without database to create it config_without_db = DB_CONFIG.copy() config_without_db.pop('database') try: connection = mysql.connector.connect(**config_without_db) cursor = connection.cursor() cursor.execute(f"CREATE DATABASE IF NOT EXISTS {DB_CONFIG['database']}") cursor.close() connection.close() except Error as e: print(f"Error creating database: {e}") # Now connect to the database connection = get_db_connection() if not connection: print("Failed to connect to database") return cursor = connection.cursor() # Use the database cursor.execute(f"USE {DB_CONFIG['database']}") # Users table with profile_pic column cursor.execute(""" CREATE TABLE IF NOT EXISTS users ( id INT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(50) UNIQUE NOT NULL, password VARCHAR(255) NOT NULL, full_name VARCHAR(100) NOT NULL, email VARCHAR(100) NOT NULL, phone VARCHAR(20) NOT NULL, nid VARCHAR(20) UNIQUE NOT NULL, district VARCHAR(50) NOT NULL, upazila VARCHAR(50) NOT NULL, latitude DECIMAL(10, 6), longitude DECIMAL(10, 6), profile_pic VARCHAR(255) DEFAULT 'default.png', role VARCHAR(20) DEFAULT 'volunteer', registered_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) """) # Reports table cursor.execute(""" CREATE TABLE IF NOT EXISTS reports ( id INT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL, disaster_type VARCHAR(50), title VARCHAR(255), description TEXT, location VARCHAR(255), latitude DECIMAL(10, 6), longitude DECIMAL(10, 6), affected_people INT, action_taken TEXT, report_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ) """) # Trainings table cursor.execute(""" CREATE TABLE IF NOT EXISTS trainings ( id INT PRIMARY KEY AUTO_INCREMENT, title VARCHAR(255) NOT NULL, description TEXT, content TEXT, duration_hours INT, created_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) """) # User training enrollment cursor.execute(""" CREATE TABLE IF NOT EXISTS user_trainings ( id INT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL, training_id INT NOT NULL, enrolled_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP, completed_date TIMESTAMP NULL, status VARCHAR(20) DEFAULT 'enrolled', certificate VARCHAR(255), FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, FOREIGN KEY (training_id) REFERENCES trainings(id) ON DELETE CASCADE, UNIQUE KEY unique_enrollment (user_id, training_id) ) """) # Messages table cursor.execute(""" CREATE TABLE IF NOT EXISTS messages ( id INT PRIMARY KEY AUTO_INCREMENT, sender_id INT, recipient_id INT, zone VARCHAR(50), subject VARCHAR(255), message TEXT, is_read BOOLEAN DEFAULT FALSE, sent_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (sender_id) REFERENCES users(id) ON DELETE SET NULL, FOREIGN KEY (recipient_id) REFERENCES users(id) ON DELETE CASCADE ) """) # Insert demo trainings cursor.execute("SELECT COUNT(*) FROM trainings") if cursor.fetchone()[0] == 0: trainings_data = [ ("Cyclone Preparedness & Response", "Learn how to prepare for and respond to cyclones in coastal Bangladesh", "Module 1: Early Warning Systems\nModule 2: Evacuation Procedures\nModule 3: Shelter Management\nModule 4: Post-Cyclone Relief", 4), ("Flood Rescue & First Aid", "Basic flood rescue techniques and emergency first aid", "Module 1: Water Safety\nModule 2: Rescue Equipment\nModule 3: CPR & First Aid\nModule 4: Flood-related Diseases", 6), ("Earthquake Safety & Building Assessment", "Earthquake preparedness and building safety assessment", "Module 1: Earthquake Science\nModule 2: Drop, Cover & Hold\nModule 3: Building Assessment\nModule 4: Search & Rescue", 5), ("Community Risk Assessment", "How to conduct community-based disaster risk assessment", "Module 1: Hazard Mapping\nModule 2: Vulnerability Assessment\nModule 3: Community Participation\nModule 4: DRR Planning", 3) ] cursor.executemany("INSERT INTO trainings (title, description, content, duration_hours) VALUES (%s, %s, %s, %s)", trainings_data) # Insert demo users cursor.execute("SELECT COUNT(*) FROM users WHERE role='volunteer' AND username LIKE 'demo_%'") if cursor.fetchone()[0] == 0: demo_users = [ ("demo_chittagong", hash_password("demo123"), "Rahim Uddin", "rahim@example.com", "01711111111", "1987654321", "Chittagong", "Kotwali", 22.3569, 91.7832), ("demo_sylhet", hash_password("demo123"), "Fatema Begum", "fatema@example.com", "01722222222", "2987654321", "Sylhet", "Sadar", 24.9045, 91.8611), ("demo_rajshahi", hash_password("demo123"), "Karim Ahmed", "karim@example.com", "01733333333", "3987654321", "Rajshahi", "Boalia", 24.3745, 88.6042), ("demo_barisal", hash_password("demo123"), "Shahida Akter", "shahida@example.com", "01744444444", "4987654321", "Barisal", "Sadar", 22.7010, 90.3535), ("demo_rangpur", hash_password("demo123"), "Jamal Hossain", "jamal@example.com", "01755555555", "5987654321", "Rangpur", "Sadar", 25.7439, 89.2752) ] cursor.executemany(""" INSERT INTO users (username, password, full_name, email, phone, nid, district, upazila, latitude, longitude, role) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, 'volunteer') """, demo_users) # Create admin user cursor.execute("SELECT COUNT(*) FROM users WHERE username='admin'") if cursor.fetchone()[0] == 0: cursor.execute(""" INSERT INTO users (username, password, full_name, email, phone, nid, district, upazila, role) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, 'admin') """, ('admin', hash_password('admin123'), 'System Admin', 'admin@drr.gov.bd', '01999999999', '1234567890', 'Dhaka', 'Motijheel')) connection.commit() cursor.close() connection.close() print("Database initialized successfully with MySQL!") if __name__ == "__main__": init_db()