-- Complete recreation of permission tables for production
-- This will fix corrupted table structures

-- ===========================================
-- BACKUP EXISTING DATA (OPTIONAL)
-- ===========================================
-- If you have important data, run these first to backup:
-- CREATE TABLE users_user_permissions_backup AS SELECT * FROM users_user_permissions;
-- CREATE TABLE users_groups_backup AS SELECT * FROM users_groups;

-- ===========================================
-- DROP EXISTING TABLES
-- ===========================================
-- This will remove any corrupted tables
DROP TABLE IF EXISTS users_user_permissions;
DROP TABLE IF EXISTS users_groups;

-- ===========================================
-- CREATE USERS_USER_PERMISSIONS TABLE
-- ===========================================
CREATE TABLE users_user_permissions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customuser_id CHAR(36) NOT NULL,
    permission_id INT NOT NULL,
    UNIQUE KEY users_user_permissions_customuser_id_permission_id_uniq (customuser_id, permission_id),
    KEY users_user_permissions_customuser_id (customuser_id),
    KEY users_user_permissions_permission_id (permission_id),
    CONSTRAINT users_user_permissions_customuser_id_fk 
        FOREIGN KEY (customuser_id) REFERENCES users (id) ON DELETE CASCADE,
    CONSTRAINT users_user_permissions_permission_id_fk 
        FOREIGN KEY (permission_id) REFERENCES auth_permission (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ===========================================
-- CREATE USERS_GROUPS TABLE
-- ===========================================
CREATE TABLE users_groups (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customuser_id CHAR(36) NOT NULL,
    group_id INT NOT NULL,
    UNIQUE KEY users_groups_customuser_id_group_id_uniq (customuser_id, group_id),
    KEY users_groups_customuser_id (customuser_id),
    KEY users_groups_group_id (group_id),
    CONSTRAINT users_groups_customuser_id_fk 
        FOREIGN KEY (customuser_id) REFERENCES users (id) ON DELETE CASCADE,
    CONSTRAINT users_groups_group_id_fk 
        FOREIGN KEY (group_id) REFERENCES auth_group (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ===========================================
-- VERIFY THE CREATION
-- ===========================================
-- Check that tables were created successfully
SHOW TABLES LIKE 'users%';

-- Check the structure
DESCRIBE users_user_permissions;
DESCRIBE users_groups;

-- ===========================================
-- RESTORE BACKED UP DATA (IF NEEDED)
-- ===========================================
-- If you backed up data earlier, you can restore it like this:
-- INSERT INTO users_user_permissions (customuser_id, permission_id)
-- SELECT customuser_id, permission_id FROM users_user_permissions_backup;

-- INSERT INTO users_groups (customuser_id, group_id)
-- SELECT customuser_id, group_id FROM users_groups_backup;

-- ===========================================
-- TEST INSERT (OPTIONAL)
-- ===========================================
-- Test that the tables work (replace with actual IDs from your database)
-- INSERT INTO users_user_permissions (customuser_id, permission_id) 
-- VALUES ('your-user-id-here', 1);

-- Clean up test record
-- DELETE FROM users_user_permissions WHERE customuser_id = 'your-user-id-here' AND permission_id = 1;