-- Triggers for updatedAt
-- Use timestamptz for timezone-safe syncs
CREATE OR REPLACE FUNCTION set_updatedat() RETURNS trigger AS $$
BEGIN
  NEW.updatedat := now();  -- transaction_timestamp()
  RETURN NEW;
END
$$ LANGUAGE plpgsql;

DO $$
DECLARE r record;
BEGIN
  FOR r IN
    SELECT n.nspname AS schemaname, c.relname AS tablename
    FROM pg_attribute a
    JOIN pg_class     c ON c.oid = a.attrelid
    JOIN pg_namespace n ON n.oid = c.relnamespace
    WHERE a.attname = 'updatedat'
      AND a.attnum > 0 AND NOT a.attisdropped
      AND c.relkind IN ('r','p') -- ordinary and partitioned tables
      AND n.nspname NOT IN ('pg_catalog','information_schema')
  LOOP
    EXECUTE format('DROP TRIGGER IF EXISTS trg_set_updatedat ON %I.%I',
                   r.schemaname, r.tablename);
    EXECUTE format($fmt$
      CREATE TRIGGER trg_set_updatedat
      BEFORE UPDATE ON %I.%I
      FOR EACH ROW
      EXECUTE FUNCTION set_updatedat()
    $fmt$, r.schemaname, r.tablename);
  END LOOP;
END $$;

CREATE OR REPLACE FUNCTION attach_updatedat_trigger() RETURNS event_trigger AS $$
DECLARE cmd record;
DECLARE has_col boolean;
DECLARE table_name text;
DECLARE schema_name text;
BEGIN
  FOR cmd IN SELECT * FROM pg_event_trigger_ddl_commands()
  LOOP
    IF cmd.command_tag IN ('CREATE TABLE','CREATE TABLE AS','ALTER TABLE') THEN
      -- Only handle real tables in user schemas
      IF cmd.schema_name IS NOT NULL
         AND cmd.schema_name NOT IN ('pg_catalog','information_schema') THEN

        -- Parse object_identity to get table name
        table_name := split_part(cmd.object_identity, '.', 2);

        SELECT EXISTS (
          SELECT 1
          FROM pg_attribute a
          JOIN pg_class     c ON c.oid = a.attrelid
          JOIN pg_namespace n ON n.oid = c.relnamespace
          WHERE n.nspname = cmd.schema_name
            AND c.relname = table_name
            AND a.attname = 'updatedat'
            AND a.attnum > 0 AND NOT a.attisdropped
        ) INTO has_col;

        IF has_col THEN
          EXECUTE format('DROP TRIGGER IF EXISTS trg_set_updatedat ON %I.%I',
                         cmd.schema_name, table_name);
          EXECUTE format($fmt$
            CREATE TRIGGER trg_set_updatedat
            BEFORE UPDATE ON %I.%I
            FOR EACH ROW
            EXECUTE FUNCTION set_updatedat()
          $fmt$, cmd.schema_name, table_name);
        END IF;
      END IF;
    END IF;
  END LOOP;
END
$$ LANGUAGE plpgsql;

DROP EVENT TRIGGER IF EXISTS auto_attach_updatedat_trigger;
CREATE EVENT TRIGGER auto_attach_updatedat_trigger
ON ddl_command_end
WHEN TAG IN ('CREATE TABLE','CREATE TABLE AS','ALTER TABLE')
EXECUTE FUNCTION attach_updatedat_trigger();

--END Triggers

-- 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
            -- 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 LOOP;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Trigger to automatically create groups when org enrolls
CREATE TRIGGER trigger_create_default_program_groups
    AFTER INSERT OR UPDATE ON program_enrollments
    FOR EACH ROW
    EXECUTE FUNCTION create_default_program_groups();