import database from "../config/db";

export async function createRolesTable() {
  try {
    const query = `
      -- Roles Table: Defines different roles in the system
      CREATE TABLE IF NOT EXISTS roles (
        -- Primary Key
        id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
        
        -- Role Information
        name VARCHAR(50) UNIQUE NOT NULL 
          CHECK (name IN ('super_admin', 'admin', 'moderator', 'company_admin', 'verified_user', 'user', 'guest')),
        
        display_name VARCHAR(100) NOT NULL,
        description TEXT,
        
        -- Role Hierarchy (higher number = higher privilege)
        level INTEGER NOT NULL DEFAULT 0 
          CHECK (level >= 0 AND level <= 100),
        
        -- Permissions (stored as JSON for flexibility)
        permissions JSONB DEFAULT '{}'::JSONB,
        
        -- Role Settings
        is_system_role BOOLEAN DEFAULT false,
        is_default BOOLEAN DEFAULT false,
        is_active BOOLEAN DEFAULT true,
        
        -- Timestamps
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        
        -- Constraints
        -- CONSTRAINT unique_system_role UNIQUE(name, is_system_role) WHERE is_system_role = true
      );

      -- Role Assignments Table: Links users to roles
      CREATE TABLE IF NOT EXISTS role_assignments (
        -- Primary Key
        id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
        
        -- Relationship
        account_id UUID NOT NULL,
        role_id UUID NOT NULL,
        
        -- Assignment Details
        assigned_by UUID,
        assignment_reason TEXT,
        
        -- Scope (for company-specific roles)
        scope_type VARCHAR(50) 
          CHECK (scope_type IN ('global', 'company', 'department', 'project')),
        scope_id UUID,
        
        -- Status
        is_active BOOLEAN DEFAULT true,
        is_primary BOOLEAN DEFAULT false,
        
        -- Time-bound assignments
        starts_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        expires_at TIMESTAMP,
        
        -- Timestamps
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        
        -- Constraints
        CONSTRAINT fk_account FOREIGN KEY (account_id) 
          REFERENCES accounts(id) ON DELETE CASCADE,
        CONSTRAINT fk_role FOREIGN KEY (role_id) 
          REFERENCES roles(id) ON DELETE CASCADE,
        CONSTRAINT fk_assigned_by FOREIGN KEY (assigned_by) 
          REFERENCES accounts(id) ON DELETE SET NULL,
        -- CONSTRAINT unique_active_primary_per_account 
          -- UNIQUE(account_id) WHERE is_active = true AND is_primary = true,
        CONSTRAINT check_expiry CHECK (expires_at IS NULL OR expires_at > starts_at)
      );

      -- Permissions Table: Detailed permissions system
      CREATE TABLE IF NOT EXISTS permissions (
        -- Primary Key
        id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
        
        -- Permission Information
        category VARCHAR(50) NOT NULL 
          CHECK (category IN ('user', 'company', 'content', 'system', 'financial', 'analytics')),
        
        name VARCHAR(100) UNIQUE NOT NULL,
        display_name VARCHAR(200) NOT NULL,
        description TEXT,
        
        -- Permission Details
        action VARCHAR(50) NOT NULL 
          CHECK (action IN ('create', 'read', 'update', 'delete', 'manage', 'approve', 'export')),
        resource VARCHAR(100) NOT NULL,
        
        -- Scope
        is_global BOOLEAN DEFAULT false,
        is_sensitive BOOLEAN DEFAULT false,
        
        -- Metadata
        required_privilege_level INTEGER DEFAULT 0,
        
        -- Timestamps
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      );

      -- Role-Permission Mapping Table
      CREATE TABLE IF NOT EXISTS role_permissions (
        -- Primary Key
        id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
        
        -- Relationship
        role_id UUID NOT NULL,
        permission_id UUID NOT NULL,
        
        -- Permission Settings
        is_allowed BOOLEAN DEFAULT true,
        constraints JSONB DEFAULT '{}'::JSONB,
        
        -- Timestamps
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        
        -- Constraints
        CONSTRAINT fk_role FOREIGN KEY (role_id) 
          REFERENCES roles(id) ON DELETE CASCADE,
        CONSTRAINT fk_permission FOREIGN KEY (permission_id) 
          REFERENCES permissions(id) ON DELETE CASCADE,
        CONSTRAINT unique_role_permission UNIQUE(role_id, permission_id)
      );

      -- Audit Log Table: Tracks permission and role changes
      CREATE TABLE IF NOT EXISTS permission_audit_log (
        -- Primary Key
        id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
        
        -- Action Information
        action VARCHAR(50) NOT NULL 
          CHECK (action IN ('assign_role', 'remove_role', 'grant_permission', 
                           'revoke_permission', 'update_role', 'update_permission')),
        
        -- Target
        target_account_id UUID,
        target_role_id UUID,
        target_permission_id UUID,
        
        -- Changes
        previous_value JSONB,
        new_value JSONB,
        
        -- Performer
        performed_by UUID NOT NULL,
        performed_by_ip INET,
        user_agent TEXT,
        
        -- Metadata
        reason TEXT,
        
        -- Timestamps
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        
        -- Constraints
        CONSTRAINT fk_performed_by FOREIGN KEY (performed_by) 
          REFERENCES accounts(id) ON DELETE SET NULL,
        CONSTRAINT fk_target_account FOREIGN KEY (target_account_id) 
          REFERENCES accounts(id) ON DELETE SET NULL,
        CONSTRAINT fk_target_role FOREIGN KEY (target_role_id) 
          REFERENCES roles(id) ON DELETE SET NULL,
        CONSTRAINT fk_target_permission FOREIGN KEY (target_permission_id) 
          REFERENCES permissions(id) ON DELETE SET NULL
      );
    `;

    await database.query(query);

    // Create indexes
    const indexes = [
      // Roles indexes
      `CREATE INDEX IF NOT EXISTS idx_roles_name ON roles(name);`,
      `CREATE INDEX IF NOT EXISTS idx_roles_level ON roles(level);`,
      `CREATE INDEX IF NOT EXISTS idx_roles_is_active ON roles(is_active) WHERE is_active = true;`,

      // System roles uniqueness
      `CREATE UNIQUE INDEX IF NOT EXISTS uq_system_role_name
        ON roles(name)
        WHERE is_system_role = true;`,

      // One active primary role per account
      `CREATE UNIQUE INDEX IF NOT EXISTS uq_active_primary_role_per_account
        ON role_assignments(account_id)
        WHERE is_active = true AND is_primary = true;`,

      // Role assignments indexes
      `CREATE INDEX IF NOT EXISTS idx_role_assignments_account_id ON role_assignments(account_id);`,
      `CREATE INDEX IF NOT EXISTS idx_role_assignments_role_id ON role_assignments(role_id);`,
      `CREATE INDEX IF NOT EXISTS idx_role_assignments_active ON role_assignments(is_active) WHERE is_active = true;`,
      `CREATE INDEX IF NOT EXISTS idx_role_assignments_expires ON role_assignments(expires_at) WHERE expires_at IS NOT NULL;`,

      // Permissions indexes
      `CREATE INDEX IF NOT EXISTS idx_permissions_category ON permissions(category);`,
      `CREATE INDEX IF NOT EXISTS idx_permissions_action_resource ON permissions(action, resource);`,

      // Role-permissions indexes
      `CREATE INDEX IF NOT EXISTS idx_role_permissions_role_id ON role_permissions(role_id);`,
      `CREATE INDEX IF NOT EXISTS idx_role_permissions_permission_id ON role_permissions(permission_id);`,

      // Audit log indexes
      `CREATE INDEX IF NOT EXISTS idx_audit_log_action ON permission_audit_log(action);`,
      `CREATE INDEX IF NOT EXISTS idx_audit_log_performed_by ON permission_audit_log(performed_by);`,
      `CREATE INDEX IF NOT EXISTS idx_audit_log_created_at ON permission_audit_log(created_at DESC);`,
    ];

    for (const indexQuery of indexes) {
      try {
        await database.query(indexQuery);
      } catch (err) {
        if (
          !(err instanceof Error) ||
          !err.message.includes("already exists")
        ) {
          throw err;
        }
      }
    }

    // Insert default system roles
    const defaultRoles = `
      INSERT INTO roles (name, display_name, description, level, permissions, is_system_role, is_default) VALUES
      -- Super Admin (Highest level)
      ('super_admin', 'Super Administrator', 'Full system access with all permissions', 100, 
       '{"can_manage_system": true, "can_manage_users": true, "can_manage_companies": true, "can_manage_content": true}', 
       true, false),
      
      -- Admin
      ('admin', 'Administrator', 'System administrator with extensive permissions', 90, 
       '{"can_manage_users": true, "can_manage_companies": true, "can_manage_content": true}', 
       true, false),
      
      -- Moderator
      ('moderator', 'Moderator', 'Content and user moderator', 70, 
       '{"can_moderate_content": true, "can_moderate_users": true, "can_view_reports": true}', 
       true, false),
      
      -- Company Admin
      ('company_admin', 'Company Administrator', 'Administrator for company accounts', 80, 
       '{"can_manage_company": true, "can_manage_company_users": true, "can_manage_company_content": true}', 
       true, false),
      
      -- Verified User
      ('verified_user', 'Verified User', 'Verified user with additional privileges', 50, 
       '{"can_verify_content": true, "can_access_premium": true}', 
       true, false),
      
      -- Regular User (Default for new accounts)
      ('user', 'User', 'Regular user account', 30, 
       '{"can_create_content": true, "can_follow_users": true, "can_comment": true}', 
       true, true),
      
      -- Guest (Limited access)
      ('guest', 'Guest', 'Guest user with minimal access', 10, 
       '{"can_view_public_content": true}', 
       true, false)
      
      ON CONFLICT (name) DO UPDATE SET
        display_name = EXCLUDED.display_name,
        description = EXCLUDED.description,
        level = EXCLUDED.level,
        permissions = EXCLUDED.permissions,
        updated_at = CURRENT_TIMESTAMP;
    `;

    await database.query(defaultRoles);

    // Insert default permissions
    const defaultPermissions = `
      INSERT INTO permissions (category, name, display_name, description, action, resource, is_global, required_privilege_level) VALUES
      -- User Management Permissions
      ('user', 'user.create', 'Create Users', 'Create new user accounts', 'create', 'users', true, 80),
      ('user', 'user.read', 'View Users', 'View user profiles and information', 'read', 'users', false, 10),
      ('user', 'user.update', 'Update Users', 'Update user information', 'update', 'users', false, 70),
      ('user', 'user.delete', 'Delete Users', 'Delete user accounts', 'delete', 'users', true, 90),
      ('user', 'user.manage_roles', 'Manage User Roles', 'Assign and remove roles from users', 'manage', 'user_roles', true, 80),
      ('user', 'user.verify', 'Verify Users', 'Verify user accounts and identities', 'approve', 'user_verification', true, 70),
      
      -- Company Management Permissions
      ('company', 'company.create', 'Create Companies', 'Create new company accounts', 'create', 'companies', true, 80),
      ('company', 'company.read', 'View Companies', 'View company profiles and information', 'read', 'companies', false, 10),
      ('company', 'company.update', 'Update Companies', 'Update company information', 'update', 'companies', false, 70),
      ('company', 'company.delete', 'Delete Companies', 'Delete company accounts', 'delete', 'companies', true, 90),
      ('company', 'company.manage_members', 'Manage Company Members', 'Add/remove members from companies', 'manage', 'company_members', false, 60),
      ('company', 'company.manage_projects', 'Manage Company Projects', 'Create and manage company projects', 'manage', 'company_projects', false, 50),
      
      -- Content Management Permissions
      ('content', 'content.create', 'Create Content', 'Create new content (posts, articles, etc.)', 'create', 'content', false, 20),
      ('content', 'content.read', 'View Content', 'View content', 'read', 'content', false, 10),
      ('content', 'content.update', 'Update Content', 'Update existing content', 'update', 'content', false, 40),
      ('content', 'content.delete', 'Delete Content', 'Delete content', 'delete', 'content', false, 60),
      ('content', 'content.moderate', 'Moderate Content', 'Approve/reject content', 'approve', 'content_moderation', true, 60),
      ('content', 'content.manage_comments', 'Manage Comments', 'Moderate and manage comments', 'manage', 'comments', false, 50),
      
      -- System Management Permissions
      ('system', 'system.manage_settings', 'Manage System Settings', 'Update system-wide settings', 'manage', 'system_settings', true, 90),
      ('system', 'system.view_logs', 'View System Logs', 'Access system audit logs', 'read', 'system_logs', true, 80),
      ('system', 'system.manage_roles', 'Manage Roles', 'Create, update, and delete roles', 'manage', 'roles', true, 90),
      ('system', 'system.manage_permissions', 'Manage Permissions', 'Create, update, and delete permissions', 'manage', 'permissions', true, 90),
      ('system', 'system.export_data', 'Export Data', 'Export system data', 'export', 'system_data', true, 70),
      
      -- Financial Permissions
      ('financial', 'financial.view_reports', 'View Financial Reports', 'View financial reports and analytics', 'read', 'financial_reports', true, 70),
      ('financial', 'financial.manage_subscriptions', 'Manage Subscriptions', 'Manage user subscriptions and payments', 'manage', 'subscriptions', true, 60),
      ('financial', 'financial.process_refunds', 'Process Refunds', 'Process payment refunds', 'update', 'refunds', true, 80),
      
      -- Analytics Permissions
      ('analytics', 'analytics.view_dashboard', 'View Analytics Dashboard', 'Access analytics dashboard', 'read', 'analytics_dashboard', false, 40),
      ('analytics', 'analytics.export_reports', 'Export Analytics Reports', 'Export analytics data', 'export', 'analytics_reports', false, 50),
      ('analytics', 'analytics.manage_tracking', 'Manage Tracking', 'Configure analytics tracking', 'manage', 'analytics_tracking', true, 60)
      
      ON CONFLICT (name) DO UPDATE SET
        display_name = EXCLUDED.display_name,
        description = EXCLUDED.description,
        action = EXCLUDED.action,
        resource = EXCLUDED.resource,
        updated_at = CURRENT_TIMESTAMP;
    `;

    await database.query(defaultPermissions);

    // Assign default permissions to roles
    const assignPermissions = `
      -- Assign permissions to super_admin (all permissions)
      INSERT INTO role_permissions (role_id, permission_id)
      SELECT r.id, p.id
      FROM roles r
      CROSS JOIN permissions p
      WHERE r.name = 'super_admin'
      ON CONFLICT (role_id, permission_id) DO NOTHING;
      
      -- Assign permissions to admin (most permissions except sensitive ones)
      INSERT INTO role_permissions (role_id, permission_id)
      SELECT r.id, p.id
      FROM roles r
      CROSS JOIN permissions p
      WHERE r.name = 'admin'
        AND p.name NOT IN ('system.manage_settings', 'system.manage_roles', 'system.manage_permissions')
      ON CONFLICT (role_id, permission_id) DO NOTHING;
      
      -- Assign permissions to moderator
      INSERT INTO role_permissions (role_id, permission_id)
      SELECT r.id, p.id
      FROM roles r
      CROSS JOIN permissions p
      WHERE r.name = 'moderator'
        AND p.category IN ('user', 'content')
        AND p.action IN ('read', 'update', 'delete', 'approve', 'manage')
      ON CONFLICT (role_id, permission_id) DO NOTHING;
      
      -- Assign permissions to company_admin
      INSERT INTO role_permissions (role_id, permission_id)
      SELECT r.id, p.id
      FROM roles r
      CROSS JOIN permissions p
      WHERE r.name = 'company_admin'
        AND (
          (p.category = 'company' AND p.action IN ('read', 'update', 'manage'))
          OR (p.category = 'content' AND p.action IN ('create', 'read', 'update', 'delete', 'manage'))
          OR (p.category = 'analytics' AND p.action IN ('read', 'export'))
        )
      ON CONFLICT (role_id, permission_id) DO NOTHING;
      
      -- Assign permissions to verified_user
      INSERT INTO role_permissions (role_id, permission_id)
      SELECT r.id, p.id
      FROM roles r
      CROSS JOIN permissions p
      WHERE r.name = 'verified_user'
        AND (
          (p.category = 'content' AND p.action IN ('create', 'read', 'update', 'delete'))
          OR (p.category = 'analytics' AND p.action = 'read')
        )
      ON CONFLICT (role_id, permission_id) DO NOTHING;
      
      -- Assign permissions to user (default)
      INSERT INTO role_permissions (role_id, permission_id)
      SELECT r.id, p.id
      FROM roles r
      CROSS JOIN permissions p
      WHERE r.name = 'user'
        AND (
          (p.category = 'content' AND p.action IN ('create', 'read', 'update'))
          OR (p.name = 'user.read')
        )
      ON CONFLICT (role_id, permission_id) DO NOTHING;
      
      -- Assign permissions to guest
      INSERT INTO role_permissions (role_id, permission_id)
      SELECT r.id, p.id
      FROM roles r
      CROSS JOIN permissions p
      WHERE r.name = 'guest'
        AND p.name IN ('user.read', 'company.read', 'content.read')
      ON CONFLICT (role_id, permission_id) DO NOTHING;
    `;

    await database.query(assignPermissions);

    // Create functions
    const createFunctions = `
      -- Function to check if user has permission
      CREATE OR REPLACE FUNCTION has_permission(
        user_id UUID,
        permission_name VARCHAR,
        resource_id UUID DEFAULT NULL
      )
      RETURNS BOOLEAN AS $$
      DECLARE
        has_permission BOOLEAN := false;
        user_level INTEGER;
        permission_record RECORD;
      BEGIN
        -- Get user's highest role level
        SELECT COALESCE(MAX(r.level), 0) INTO user_level
        FROM role_assignments ra
        INNER JOIN roles r ON ra.role_id = r.id
        WHERE ra.account_id = user_id 
          AND ra.is_active = true
          AND (ra.expires_at IS NULL OR ra.expires_at > CURRENT_TIMESTAMP);
        
        -- Get permission details
        SELECT * INTO permission_record
        FROM permissions 
        WHERE name = permission_name;
        
        IF NOT FOUND THEN
          RETURN false;
        END IF;
        
        -- Check privilege level
        IF user_level < permission_record.required_privilege_level THEN
          RETURN false;
        END IF;
        
        -- Check if user has the permission through roles
        SELECT EXISTS (
          SELECT 1
          FROM role_assignments ra
          INNER JOIN role_permissions rp ON ra.role_id = rp.role_id
          INNER JOIN permissions p ON rp.permission_id = p.id
          WHERE ra.account_id = user_id
            AND ra.is_active = true
            AND (ra.expires_at IS NULL OR ra.expires_at > CURRENT_TIMESTAMP)
            AND p.name = permission_name
            AND rp.is_allowed = true
        ) INTO has_permission;
        
        RETURN has_permission;
      END;
      $$ LANGUAGE plpgsql;

      -- Function to get user permissions
      CREATE OR REPLACE FUNCTION get_user_permissions(user_id UUID)
      RETURNS TABLE(
        permission_name VARCHAR,
        category VARCHAR,
        action VARCHAR,
        resource VARCHAR,
        is_allowed BOOLEAN,
        constraints JSONB
      ) AS $$
      BEGIN
        RETURN QUERY
        SELECT DISTINCT
          p.name as permission_name,
          p.category,
          p.action,
          p.resource,
          rp.is_allowed,
          rp.constraints
        FROM role_assignments ra
        INNER JOIN role_permissions rp ON ra.role_id = rp.role_id
        INNER JOIN permissions p ON rp.permission_id = p.id
        WHERE ra.account_id = user_id
          AND ra.is_active = true
          AND (ra.expires_at IS NULL OR ra.expires_at > CURRENT_TIMESTAMP)
          AND rp.is_allowed = true
        ORDER BY p.category, p.name;
      END;
      $$ LANGUAGE plpgsql;

      -- Function to get user roles
      CREATE OR REPLACE FUNCTION get_user_roles(user_id UUID)
      RETURNS TABLE(
        role_id UUID,
        role_name VARCHAR,
        display_name VARCHAR,
        level INTEGER,
        is_primary BOOLEAN,
        assigned_at TIMESTAMP,
        expires_at TIMESTAMP
      ) AS $$
      BEGIN
        RETURN QUERY
        SELECT
          r.id as role_id,
          r.name as role_name,
          r.display_name,
          r.level,
          ra.is_primary,
          ra.created_at as assigned_at,
          ra.expires_at
        FROM role_assignments ra
        INNER JOIN roles r ON ra.role_id = r.id
        WHERE ra.account_id = user_id
          AND ra.is_active = true
          AND (ra.expires_at IS NULL OR ra.expires_at > CURRENT_TIMESTAMP)
        ORDER BY r.level DESC, ra.is_primary DESC;
      END;
      $$ LANGUAGE plpgsql;

      -- Function to assign role to user
      CREATE OR REPLACE FUNCTION assign_role_to_user(
        target_user_id UUID,
        role_name VARCHAR,
        assigned_by_user_id UUID,
        is_primary BOOLEAN DEFAULT false,
        expires_at TIMESTAMP DEFAULT NULL,
        reason TEXT DEFAULT NULL
      )
      RETURNS JSON AS $$
      DECLARE
        role_record RECORD;
        existing_assignment RECORD;
        result JSON;
      BEGIN
        -- Get role
        SELECT * INTO role_record FROM roles WHERE name = role_name;
        
        IF NOT FOUND THEN
          RETURN json_build_object(
            'success', false,
            'message', 'Role not found'
          );
        END IF;
        
        -- Check if user exists
        IF NOT EXISTS (SELECT 1 FROM accounts WHERE id = target_user_id) THEN
          RETURN json_build_object(
            'success', false,
            'message', 'User not found'
          );
        END IF;
        
        -- Check for existing active assignment
        SELECT * INTO existing_assignment
        FROM role_assignments
        WHERE account_id = target_user_id
          AND role_id = role_record.id
          AND is_active = true
          AND (expires_at IS NULL OR expires_at > CURRENT_TIMESTAMP);
        
        IF FOUND THEN
          RETURN json_build_object(
            'success', false,
            'message', 'User already has this role'
          );
        END IF;
        
        -- If this should be primary, deactivate other primary roles
        IF is_primary THEN
          UPDATE role_assignments
          SET is_primary = false, updated_at = CURRENT_TIMESTAMP
          WHERE account_id = target_user_id
            AND is_active = true
            AND is_primary = true;
        END IF;
        
        -- Insert new assignment
        INSERT INTO role_assignments (
          account_id,
          role_id,
          assigned_by,
          is_primary,
          expires_at,
          assignment_reason,
          created_at
        ) VALUES (
          target_user_id,
          role_record.id,
          assigned_by_user_id,
          is_primary,
          assign_role_to_user.expires_at,
          reason,
          CURRENT_TIMESTAMP
        );
        
        -- Log the action
        INSERT INTO permission_audit_log (
          action,
          target_account_id,
          target_role_id,
          performed_by,
          reason,
          new_value
        ) VALUES (
          'assign_role',
          target_user_id,
          role_record.id,
          assigned_by_user_id,
          reason,
          json_build_object(
            'role_name', role_record.name,
            'is_primary', is_primary,
            'expires_at', assign_role_to_user.expires_at
          )
        );
        
        result := json_build_object(
          'success', true,
          'message', 'Role assigned successfully',
          'data', json_build_object(
            'user_id', target_user_id,
            'role_name', role_record.name,
            'assigned_by', assigned_by_user_id,
            'assigned_at', CURRENT_TIMESTAMP
          )
        );
        
        RETURN result;
      END;
      $$ LANGUAGE plpgsql;

      -- Function to remove role from user
      CREATE OR REPLACE FUNCTION remove_role_from_user(
        target_user_id UUID,
        role_name VARCHAR,
        removed_by_user_id UUID,
        reason TEXT DEFAULT NULL
      )
      RETURNS JSON AS $$
      DECLARE
        role_record RECORD;
        assignment_record RECORD;
        result JSON;
      BEGIN
        -- Get role
        SELECT * INTO role_record FROM roles WHERE name = role_name;
        
        IF NOT FOUND THEN
          RETURN json_build_object(
            'success', false,
            'message', 'Role not found'
          );
        END IF;
        
        -- Get assignment
        SELECT * INTO assignment_record
        FROM role_assignments
        WHERE account_id = target_user_id
          AND role_id = role_record.id
          AND is_active = true;
        
        IF NOT FOUND THEN
          RETURN json_build_object(
            'success', false,
            'message', 'User does not have this role'
          );
        END IF;
        
        -- Deactivate the assignment
        UPDATE role_assignments
        SET is_active = false, updated_at = CURRENT_TIMESTAMP
        WHERE id = assignment_record.id;
        
        -- Log the action
        INSERT INTO permission_audit_log (
          action,
          target_account_id,
          target_role_id,
          performed_by,
          reason,
          previous_value
        ) VALUES (
          'remove_role',
          target_user_id,
          role_record.id,
          removed_by_user_id,
          reason,
          json_build_object(
            'assignment_id', assignment_record.id,
            'was_primary', assignment_record.is_primary,
            'assigned_at', assignment_record.created_at
          )
        );
        
        result := json_build_object(
          'success', true,
          'message', 'Role removed successfully',
          'data', json_build_object(
            'user_id', target_user_id,
            'role_name', role_record.name,
            'removed_by', removed_by_user_id,
            'removed_at', CURRENT_TIMESTAMP
          )
        );
        
        RETURN result;
      END;
      $$ LANGUAGE plpgsql;

      -- Trigger function for role assignment updates
      CREATE OR REPLACE FUNCTION update_role_assignment_timestamp()
      RETURNS TRIGGER AS $$
      BEGIN
        NEW.updated_at = CURRENT_TIMESTAMP;
        RETURN NEW;
      END;
      $$ LANGUAGE plpgsql;
    `;

    await database.query(createFunctions);

    // Create triggers
    await database.query(`
      DROP TRIGGER IF EXISTS set_role_assignments_updated_at ON role_assignments;
      CREATE TRIGGER set_role_assignments_updated_at
      BEFORE UPDATE ON role_assignments
      FOR EACH ROW
      EXECUTE FUNCTION update_role_assignment_timestamp();
      
      DROP TRIGGER IF EXISTS set_roles_updated_at ON roles;
      CREATE TRIGGER set_roles_updated_at
      BEFORE UPDATE ON roles
      FOR EACH ROW
      EXECUTE FUNCTION update_role_assignment_timestamp();
      
      DROP TRIGGER IF EXISTS set_permissions_updated_at ON permissions;
      CREATE TRIGGER set_permissions_updated_at
      BEFORE UPDATE ON permissions
      FOR EACH ROW
      EXECUTE FUNCTION update_role_assignment_timestamp();
    `);

    console.log("✅ RBAC tables created successfully");
  } catch (err) {
    console.error("❌ Failed To Create RBAC Tables.", err);
    process.exit(1);
  }
}
