-- Advanced User Management System Database Setup

-- Create user_profiles table for extended user information
CREATE TABLE IF NOT EXISTS user_profiles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    full_name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE,
    phone VARCHAR(50),
    address TEXT,
    emergency_contact VARCHAR(255),
    emergency_phone VARCHAR(50),
    notes TEXT,
    avatar VARCHAR(255),
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Create groups table
CREATE TABLE IF NOT EXISTS user_groups (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    color VARCHAR(7) DEFAULT '#007bff', -- For UI color coding
    is_active TINYINT(1) DEFAULT 1,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
);

-- Create user_group_memberships table
CREATE TABLE IF NOT EXISTS user_group_memberships (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    group_id INT NOT NULL,
    role_in_group ENUM('member', 'manager', 'owner') DEFAULT 'member',
    joined_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_user_group (user_id, group_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (group_id) REFERENCES user_groups(id) ON DELETE CASCADE
);

-- Create group_event_access table
CREATE TABLE IF NOT EXISTS group_event_access (
    id INT AUTO_INCREMENT PRIMARY KEY,
    group_id INT NOT NULL,
    event_id INT NOT NULL,
    access_level ENUM('view', 'judge', 'manage', 'own') DEFAULT 'view',
    granted_by INT,
    granted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_group_event (group_id, event_id),
    FOREIGN KEY (group_id) REFERENCES user_groups(id) ON DELETE CASCADE,
    FOREIGN KEY (granted_by) REFERENCES users(id) ON DELETE SET NULL
);

-- Add group_id column to events table if it doesn't exist
ALTER TABLE events ADD COLUMN IF NOT EXISTS owner_group_id INT NULL,
ADD FOREIGN KEY (owner_group_id) REFERENCES user_groups(id) ON DELETE SET NULL;

-- Update users table to include more role options and missing columns
ALTER TABLE users MODIFY COLUMN role ENUM('super_admin', 'admin', 'judge', 'head_judge', 'office', 'media', 'participant') DEFAULT 'participant';

-- Add last_login column if it doesn't exist
ALTER TABLE users ADD COLUMN IF NOT EXISTS last_login TIMESTAMP NULL;

-- Create user_permissions table for granular permissions
CREATE TABLE IF NOT EXISTS user_permissions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    permission VARCHAR(100) NOT NULL,
    granted_by INT,
    granted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP NULL,
    UNIQUE KEY unique_user_permission (user_id, permission),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (granted_by) REFERENCES users(id) ON DELETE SET NULL
);

-- Insert default groups
INSERT IGNORE INTO user_groups (name, description, color) VALUES 
('Default Judges', 'Default group for all judges', '#28a745'),
('Event Organizers', 'Users who can create and manage events', '#fd7e14'),
('Media Team', 'Media personnel with access to photos/videos', '#6f42c1'),
('Office Staff', 'Administrative office personnel', '#20c997'),
('VIP Access', 'Special access group for VIP users', '#ffc107');

-- Create default permissions list
INSERT IGNORE INTO user_permissions (user_id, permission, granted_by) 
SELECT id, 'login', 1 FROM users WHERE role != 'participant';

-- Create indexes for better performance
CREATE INDEX idx_user_profiles_user_id ON user_profiles(user_id);
CREATE INDEX idx_user_profiles_email ON user_profiles(email);
CREATE INDEX idx_group_memberships_user_id ON user_group_memberships(user_id);
CREATE INDEX idx_group_memberships_group_id ON user_group_memberships(group_id);
CREATE INDEX idx_group_event_access_group ON group_event_access(group_id);
CREATE INDEX idx_group_event_access_event ON group_event_access(event_id);
CREATE INDEX idx_user_permissions_user ON user_permissions(user_id);
