#!/usr/bin/env python
"""Import only the user table from Grazuri SQL file"""
import pymysql

conn = pymysql.connect(
    host='localhost',
    user='root',
    password='password',
    database='xygbfpsg_graz',
    charset='utf8mb4'
)

try:
    with conn.cursor() as cursor:
        # Drop user table if it exists
        print("Dropping existing user table if it exists...")
        cursor.execute("DROP TABLE IF EXISTS `user`")
        
        # Create user table
        print("Creating user table...")
        cursor.execute("""
            CREATE TABLE `user` (
              `userid` int NOT NULL,
              `name` varchar(200) NOT NULL,
              `email` varchar(200) NOT NULL,
              `gender` varchar(20) NOT NULL,
              `id_number` int NOT NULL,
              `phone` varchar(200) NOT NULL,
              `addr1` text NOT NULL,
              `addr2` text NOT NULL,
              `district` varchar(200) NOT NULL,
              `country` varchar(200) NOT NULL,
              `comment` varchar(200) NOT NULL,
              `username` varchar(200) NOT NULL,
              `password` varchar(200) NOT NULL,
              `id` varchar(200) NOT NULL,
              `image` text,
              `role` varchar(200) NOT NULL,
              `date_of_birth` date DEFAULT NULL,
              `passport` varchar(50) DEFAULT NULL,
              `branch` varchar(50) DEFAULT NULL,
              PRIMARY KEY (`userid`)
            ) ENGINE=InnoDB DEFAULT CHARSET=latin1
        """)
        
        # Insert user data
        print("Inserting user data...")
        cursor.execute("""
            INSERT INTO `user` (`userid`, `name`, `email`, `gender`, `id_number`, `phone`, `addr1`, `addr2`, `district`, `country`, `comment`, `username`, `password`, `id`, `image`, `role`, `date_of_birth`, `passport`, `branch`) VALUES
            (482, 'Admin', 'admin@admin.com', 'Male', 112233, '08101750845', 'address1', 'address2', 'city', 'KE', ' comment', 'admin', 'MTIzNDU2', 'Loan=21319580', 'img/ppt1.jpg', 'admin', NULL, NULL, '10280061'),
            (523, 'Gladys Njeri Kariuki', 'njerigladysk23@gmail.com', 'Male', 32928357, '0711925530', 'Thika town', 'P.O. Box 14428', 'Maseru', 'KE', '', 'gladys', 'MTIzMTIz', 'Loan=267784464', 'img/ppt1.jpg', 'Credit Controller', '1989-09-24', '', '10000001'),
            (524, 'Josphat Mwangi', 'josphatmwangi@gmail.com', 'Male', 27432964, '0706762830', 'Thika Town', 'P.O. Box 7699', 'Maseru', 'KE', '', 'jmwangi', 'Sm13YW5naTg4MTk=', 'Loan=13083007', 'img/PK_PPT.jpg', 'Manager', '1967-09-14', '', '10000001'),
            (527, 'Admin User', 'andmin@admin.com', 'Male', 0, '0000000000', 'N/A', 'N/A', 'N/A', 'Kenya', 'System Admin', 'andmin', 'YWRtaW4xMjM=', 'ADM001', NULL, 'Admin', NULL, NULL, '1'),
            (528, 'Admin User', 'gts1211@admin.com', 'Male', 0, '0757041431', 'N/A', 'N/A', 'N/A', 'Kenya', 'System Admin', 'gts', 'MTIxMQ==', 'ADM001', '', 'Admin', NULL, NULL, '1')
        """)
        
        conn.commit()
        
        # Verify
        cursor.execute("SELECT COUNT(*) FROM user")
        count = cursor.fetchone()[0]
        print(f"\n✓ Successfully imported {count} users from Grazuri database!")
        
        # Show imported users
        cursor.execute("SELECT userid, name, username, role FROM user")
        users = cursor.fetchall()
        print("\nImported users:")
        for user in users:
            print(f"  - {user[1]} ({user[2]}) - Role: {user[3]}")
        
except Exception as e:
    print(f"❌ Error: {str(e)}")
    conn.rollback()
finally:
    conn.close()