-- Migration: Add default program groups and permissions
-- Date: September 8, 2025
-- Description: Adds tables and functions for automatic group creation when orgs enroll in programs

-- Create default groups table
CREATE TABLE IF NOT EXISTS program_default_groups (
  program_default_group_id    uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
  program_id                  uuid NOT NULL REFERENCES programs(program_id) ON DELETE CASCADE,
  group_name                  varchar(255) NOT NULL,
  group_description           text DEFAULT NULL,
  is_auto_create              boolean NOT NULL DEFAULT true,
  isDeleted                   BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt                   timestamptz NOT NULL DEFAULT now(),
  UNIQUE(program_id, group_name)
);

-- Create default permissions table
CREATE TABLE IF NOT EXISTS program_default_group_permissions (
  program_default_permission_id   uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
  program_default_group_id        uuid NOT NULL REFERENCES program_default_groups(program_default_group_id) ON DELETE CASCADE,
  module_id                       uuid NULL REFERENCES modules(module_id),
  permission_type                 varchar(50) NOT NULL DEFAULT 'module',
  action                          text NOT NULL DEFAULT 'allow',
  isDeleted                       BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt                       timestamptz NOT NULL DEFAULT now()
);

-- Function to automatically create default groups when org enrolls in program
CREATE OR REPLACE FUNCTION create_default_program_groups()
RETURNS TRIGGER AS $$
DECLARE
    default_group_record RECORD;
    new_group_id uuid;
BEGIN
    -- Only process if enrollment is active
    IF NEW.status = 'active' THEN
        -- Create default groups for this program enrollment
        FOR default_group_record IN
            SELECT pdg.program_default_group_id, pdg.group_name, pdg.group_description
            FROM program_default_groups pdg
            WHERE pdg.program_id = NEW.program_id
            AND pdg.is_auto_create = true
            AND pdg.isDeleted = false
        LOOP
            -- Check if group already exists for this org
            IF NOT EXISTS (
                SELECT 1 FROM groups
                WHERE name = default_group_record.group_name
                AND org_id = NEW.org_id
                AND isDeleted = false
            ) THEN
                -- Create the group for this org
                INSERT INTO groups (name, description, org_id)
                VALUES (default_group_record.group_name, default_group_record.group_description, NEW.org_id)
                RETURNING group_id INTO new_group_id;

                -- Create permissions for this group
                INSERT INTO group_module_permissions (group_id, module_id, action)
                SELECT new_group_id, pdgp.module_id, pdgp.action
                FROM program_default_group_permissions pdgp
                WHERE pdgp.program_default_group_id = default_group_record.program_default_group_id
                AND pdgp.permission_type = 'module'
                AND pdgp.isDeleted = false;
            END IF;
        END LOOP;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create trigger if it doesn't exist
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'trigger_create_default_program_groups') THEN
        CREATE TRIGGER trigger_create_default_program_groups
            AFTER INSERT OR UPDATE ON program_enrollments
            FOR EACH ROW
            EXECUTE FUNCTION create_default_program_groups();
    END IF;
END $$;

-- Add default data for Case Management program
INSERT INTO program_default_groups (program_id, group_name, group_description, is_auto_create) VALUES
    ('0194a8b2-5555-7123-8123-123456789ac4'::uuid, 'Managers', 'Case Management Managers with full access to employee and time tracking modules', true)
ON CONFLICT (program_id, group_name) DO NOTHING;

-- Add permissions for the default Managers group
INSERT INTO program_default_group_permissions (program_default_group_id, module_id, permission_type, action) VALUES
    ((SELECT program_default_group_id FROM program_default_groups WHERE program_id = '0194a8b2-5555-7123-8123-123456789ac4'::uuid AND group_name = 'Managers' LIMIT 1),
     '0194a8b2-7777-7123-8123-123456789ac6'::uuid, 'module', 'allow')
ON CONFLICT DO NOTHING;

INSERT INTO program_default_group_permissions (program_default_group_id, module_id, permission_type, action) VALUES
    ((SELECT program_default_group_id FROM program_default_groups WHERE program_id = '0194a8b2-5555-7123-8123-123456789ac4'::uuid AND group_name = 'Managers' LIMIT 1),
     '0194a8b2-9999-7123-8123-123456789ac8'::uuid, 'module', 'allow')
ON CONFLICT DO NOTHING;