#!/usr/bin/env python
"""
Create users_accessible_branches table
"""
import pymysql
import os
from dotenv import load_dotenv

load_dotenv()

connection = pymysql.connect(
    host=os.getenv('DB_HOST', 'localhost'),
    user=os.getenv('DB_USER'),
    password=os.getenv('DB_PASSWORD'),
    database=os.getenv('DB_NAME'),
    charset='utf8mb4',
    cursorclass=pymysql.cursors.DictCursor
)

print("=" * 80)
print("CREATING users_accessible_branches TABLE")
print("=" * 80)

try:
    with connection.cursor() as cursor:
        # Create the many-to-many table
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS `users_accessible_branches` (
              `id` bigint(20) NOT NULL AUTO_INCREMENT,
              `customuser_id` char(32) NOT NULL,
              `branch_id` char(32) NOT NULL,
              PRIMARY KEY (`id`),
              UNIQUE KEY `users_accessible_branches_customuser_id_branch_id` (`customuser_id`, `branch_id`),
              KEY `users_accessible_branches_customuser_id` (`customuser_id`),
              KEY `users_accessible_branches_branch_id` (`branch_id`),
              CONSTRAINT `users_accessible_branches_customuser_id_fk` FOREIGN KEY (`customuser_id`) REFERENCES `users` (`id`),
              CONSTRAINT `users_accessible_branches_branch_id_fk` FOREIGN KEY (`branch_id`) REFERENCES `users_branch` (`id`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        """)
        
        connection.commit()
        
        print("\n✓ users_accessible_branches table created successfully!")
        print("\nYou can now access the dashboard.")
        
except Exception as e:
    print(f"\n✗ Error: {str(e)}")
    connection.rollback()
finally:
    connection.close()