#!/usr/bin/env python3

"""
Database seeding script for Employee Directory System
Seeds the database with initial users and departments to support the application
"""

import os
import sys
import sqlite3
import hashlib
import datetime

# Add the repo directory to the path so we can import modules
repo_dir = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
sys.path.insert(0, repo_dir)

from database_things import init_db, get_db_connection

def hash_password(password):
    """Hash password using MD5 (matches application logic)"""
    return hashlib.md5(password.encode()).hexdigest()

def seed_database():
    """Seed the database with initial data"""
    print("Initializing database...")
    init_db()  # This creates default departments automatically
    
    conn = get_db_connection()
    cursor = conn.cursor()
    
    # Check if database already has data
    cursor.execute("SELECT COUNT(*) FROM users")
    user_count = cursor.fetchone()[0]
    
    if user_count > 0:
        print("Database already contains users. Skipping seed.")
        conn.close()
        return
    
    print("Seeding database with initial data...")
    
    # Create initial users
    users = [
        ('admin', 'admin123', 'admin', 'System', 'Administrator', 'admin@company.com'),
        ('hr_manager', 'hr2023', 'hr', 'Sarah', 'Johnson', 'sarah.johnson@company.com'),
        ('john_doe', 'password456', 'manager', 'John', 'Doe', 'john.doe@company.com'),
        ('jane_smith', 'jane2023', 'employee', 'Jane', 'Smith', 'jane.smith@company.com'),
        ('mike_wilson', 'mike123', 'employee', 'Mike', 'Wilson', 'mike.wilson@company.com'),
        ('alice_brown', 'alice2023', 'manager', 'Alice', 'Brown', 'alice.brown@company.com')
    ]
    
    for username, password, role, first_name, last_name, email in users:
        # Hash password
        hashed_pw = hash_password(password)
        
        # Insert user
        cursor.execute("""
            INSERT INTO users (username, password_hash, role, first_name, last_name, email, created_at)
            VALUES (?, ?, ?, ?, ?, ?, ?)
        """, (username, hashed_pw, role, first_name, last_name, email, datetime.datetime.now()))
        
        user_id = cursor.lastrowid
        
        # Create corresponding employee record if not admin
        if role != 'admin':
            # Get department IDs from database
            if role == 'hr':
                cursor.execute("SELECT id FROM departments WHERE name = 'Human Resources'")
                dept_id = cursor.fetchone()[0]
                position = 'HR Manager'
                salary = 75000
            elif role == 'manager':
                if 'john' in username.lower():
                    cursor.execute("SELECT id FROM departments WHERE name = 'Engineering'")
                else:
                    cursor.execute("SELECT id FROM departments WHERE name = 'Sales'")
                dept_id = cursor.fetchone()[0]
                position = 'Team Manager'
                salary = 85000
            else:  # employee
                if 'jane' in username.lower():
                    cursor.execute("SELECT id FROM departments WHERE name = 'Engineering'")
                    position = 'Software Developer'
                else:
                    cursor.execute("SELECT id FROM departments WHERE name = 'Marketing'")
                    position = 'Marketing Specialist'
                dept_id = cursor.fetchone()[0]
                salary = 65000
            
            cursor.execute("""
                INSERT INTO employees (first_name, last_name, email, phone, position, 
                                     department_id, hire_date, salary, status)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
            """, (first_name, last_name, email, f'+1-555-{1000 + user_id}', position,
                  dept_id, datetime.date.today() - datetime.timedelta(days=30 * user_id),
                  salary, 'active'))
            
            # Link user to employee
            emp_id = cursor.lastrowid
            cursor.execute("UPDATE users SET employee_id = ? WHERE id = ?", (emp_id, user_id))
    
    conn.commit()
    conn.close()
    
    print("Database seeding completed successfully!")
    print("\nSeeded users:")
    print("  admin / admin123 (Administrator)")
    print("  hr_manager / hr2023 (HR Manager)")
    print("  john_doe / password456 (Team Manager)")
    print("  jane_smith / jane2023 (Software Developer)")
    print("  mike_wilson / mike123 (Marketing Specialist)")
    print("  alice_brown / alice2023 (Team Manager)")

if __name__ == "__main__":
    seed_database()