import sqlite3

def initialize_database(db_name='chat_assistant.db'):
    conn = None
    try:
        conn = sqlite3.connect(db_name)
        cursor = conn.cursor()

        # Create Employees table
        cursor.execute('''CREATE TABLE IF NOT EXISTS employees (
            ID INTEGER PRIMARY KEY,
            Name TEXT,
            Department TEXT,
            Salary REAL,
            Hire_Date TEXT
        )''')

        # Create Departments table
        cursor.execute('''CREATE TABLE IF NOT EXISTS departments (
            ID INTEGER PRIMARY KEY,
            Name TEXT,
            Manager TEXT
        )''')

        cursor.execute('''CREATE TABLE IF NOT EXISTS table_metadata (
            table_name TEXT PRIMARY KEY,
            description TEXT
        )''')

        cursor.execute('''CREATE TABLE IF NOT EXISTS column_metadata (
            table_name TEXT,
            column_name TEXT,
            data_type TEXT,
            description TEXT,
            PRIMARY KEY (table_name, column_name),
            FOREIGN KEY (table_name) references table_metadata (table_name)
            )''')
        # Insert sample data into Employees table
        employees_data = [
            (1, 'Alice', 'Sales', 50000, '2021-01-15'),
            (2, 'Bob', 'Engineering', 70000, '2020-06-10'),
            (3, 'Charlie', 'Marketing', 60000, '2022-03-20')
        ]
        cursor.executemany('INSERT INTO Employees VALUES (?, ?, ?, ?, ?)', employees_data)

        # Insert sample data into Departments table
        departments_data = [
            (1, 'Sales', 'Alice'),
            (2, 'Engineering', 'Bob'),
            (3, 'Marketing', 'Charlie')
        ]
        cursor.executemany('INSERT INTO Departments VALUES (?, ?, ?)', departments_data)
        cursor.execute("INSERT INTO table_metadata (table_name, description) VALUES ('Employees','Details of Employees in Department.')")
        cursor.execute("INSERT INTO table_metadata (table_name, description) VALUES ('Department','Details of Manager of the Department.')")

        cursor.execute("INSERT INTO column_metadata (table_name, column_name, data_type,description) VALUES ('Employees','id','INTEGER','Identification number of the Employee.')")
        cursor.execute("INSERT INTO column_metadata (table_name, column_name, data_type,description) VALUES ('Employees','name','TEXT','Name of the Employee.')")
        cursor.execute("INSERT INTO column_metadata (table_name, column_name, data_type,description) VALUES ('Employees','department','TEXT','Department of the Employee.')")
        cursor.execute("INSERT INTO column_metadata (table_name, column_name, data_type,description) VALUES ('Employees','Salary','INTEGER','Salary of the Employee.')")
        cursor.execute("INSERT INTO column_metadata (table_name, column_name, data_type,description) VALUES ('Employees','Hire_Date','DATE','Date in which the Employee was hired.')")

        cursor.execute("INSERT INTO column_metadata (table_name, column_name, data_type,description) VALUES ('Departments','id','INTEGER','Identification number of the Employee.')")
        cursor.execute("INSERT INTO column_metadata (table_name, column_name, data_type,description) VALUES ('Departments','name','TEXT','Name of the Department.')")
        cursor.execute("INSERT INTO column_metadata (table_name, column_name, data_type,description) VALUES ('Departments','manager','TEXT','Manager of the Department.')")
        conn.commit()
        
        print("Database initialized successfully.")

    except sqlite3.Error as e:
        print(f"Database initialization error: {e}")

    finally:
        if conn:
            conn.close()

if __name__ == "__main__":
    initialize_database()