-- User Entity Linking Migration
-- GHAMECC Choir Management Platform
-- Adds society_id, circuit_id, and diocese_id columns to users table
-- to link society-level, circuit-level, and diocesan-level roles to their respective entities

-- Add entity ID columns to users table
ALTER TABLE users 
ADD COLUMN society_id INT NULL,
ADD COLUMN circuit_id INT NULL,
ADD COLUMN diocese_id INT NULL;

-- Add foreign key constraints
-- Note: These constraints assume that societies, circuits, and dioceses tables exist
-- If they don't exist, you'll need to create them first or remove these constraints
ALTER TABLE users
ADD CONSTRAINT fk_user_society FOREIGN KEY (society_id) REFERENCES societies(id) ON DELETE SET NULL,
ADD CONSTRAINT fk_user_circuit FOREIGN KEY (circuit_id) REFERENCES circuits(id) ON DELETE SET NULL,
ADD CONSTRAINT fk_user_diocese FOREIGN KEY (diocese_id) REFERENCES dioceses(id) ON DELETE SET NULL;

-- Add indexes for better query performance
CREATE INDEX idx_users_society_id ON users(society_id);
CREATE INDEX idx_users_circuit_id ON users(circuit_id);
CREATE INDEX idx_users_diocese_id ON users(diocese_id);

-- Update existing users with society-level roles to have appropriate society_id values
-- This is a placeholder - you'll need to manually update existing users
-- Example: UPDATE users SET society_id = 1 WHERE id = 123 AND role_id = (SELECT id FROM roles WHERE role_key = 'choirmaster');
