--
--

\restrict VrybCQ1cmIRcNe1eqD3ZbanGgFglCKBYTiFvInvlrIjuefBialDvdhacC1ry9yu


-- Started on 2026-04-02 14:36:50 IST

SET transaction_timeout = 0;
SELECT pg_catalog.set_config('search_path', '', false);

-- DROP DATABASE corplms;
--
-- TOC entry 5836 (class 1262 OID 48398)
-- Name: corplms; Type: DATABASE; Schema: -; Owner: postgres
--

-- CREATE DATABASE corplms WITH TEMPLATE = template0 ENCODING = 'UTF8' LOCALE_PROVIDER = libc LOCALE = 'en_US.UTF-8';


-- ALTER DATABASE corplms OWNER TO postgres;

\unrestrict VrybCQ1cmIRcNe1eqD3ZbanGgFglCKBYTiFvInvlrIjuefBialDvdhacC1ry9yu
\connect corplms
\restrict VrybCQ1cmIRcNe1eqD3ZbanGgFglCKBYTiFvInvlrIjuefBialDvdhacC1ry9yu

SET transaction_timeout = 0;
SELECT pg_catalog.set_config('search_path', '', false);

--
-- TOC entry 8 (class 2615 OID 53842)
-- Name: auth; Type: SCHEMA; Schema: -; Owner: mallik
--

CREATE SCHEMA auth;


ALTER SCHEMA auth OWNER TO mallik;

--
-- TOC entry 2 (class 3079 OID 48399)
-- Name: pgcrypto; Type: EXTENSION; Schema: -; Owner: -
--

CREATE EXTENSION IF NOT EXISTS pgcrypto WITH SCHEMA public;


--
-- TOC entry 5837 (class 0 OID 0)
-- Dependencies: 2
-- Name: EXTENSION pgcrypto; Type: COMMENT; Schema: -; Owner: 
--

COMMENT ON EXTENSION pgcrypto IS 'cryptographic functions';


--
-- TOC entry 3 (class 3079 OID 48436)
-- Name: uuid-ossp; Type: EXTENSION; Schema: -; Owner: -
--

CREATE EXTENSION IF NOT EXISTS "uuid-ossp" WITH SCHEMA public;


--
-- TOC entry 5838 (class 0 OID 0)
-- Dependencies: 3
-- Name: EXTENSION "uuid-ossp"; Type: COMMENT; Schema: -; Owner: 
--

COMMENT ON EXTENSION "uuid-ossp" IS 'generate universally unique identifiers (UUIDs)';


--
-- TOC entry 1099 (class 1247 OID 48455)
-- Name: app_role; Type: TYPE; Schema: public; Owner: postgres
--

CREATE TYPE public.app_role AS ENUM (
    'admin',
    'department_head',
    'trainer',
    'learner',
    'department_coordinator',
    'mentor',
    'manager',
    'external_customer',
    'contractor',
    'delivery_staff',
    'help_editor'
);


ALTER TYPE public.app_role OWNER TO postgres;

--
-- TOC entry 1102 (class 1247 OID 48478)
-- Name: content_type; Type: TYPE; Schema: public; Owner: postgres
--

CREATE TYPE public.content_type AS ENUM (
    'video',
    'pdf',
    'slides',
    'scorm',
    'quiz',
    'text',
    'audio',
    'infographic',
    'flashcard'
);


ALTER TYPE public.content_type OWNER TO postgres;

--
-- TOC entry 1105 (class 1247 OID 48498)
-- Name: point_trigger_event; Type: TYPE; Schema: public; Owner: postgres
--

CREATE TYPE public.point_trigger_event AS ENUM (
    'course_completed',
    'module_completed',
    'scorm_completed',
    'assessment_passed',
    'attendance_marked',
    'streak_achieved',
    'learning_path_completed',
    'daily_login',
    'badge_earned'
);


ALTER TYPE public.point_trigger_event OWNER TO postgres;

--
-- TOC entry 440 (class 1255 OID 53843)
-- Name: uid(); Type: FUNCTION; Schema: auth; Owner: mallik
--

CREATE FUNCTION auth.uid() RETURNS uuid
    LANGUAGE sql
    AS $$SELECT NULL::uuid$$;


ALTER FUNCTION auth.uid() OWNER TO mallik;

--
-- TOC entry 456 (class 1255 OID 48517)
-- Name: auto_assign_learning_path_on_failed_assessment(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.auto_assign_learning_path_on_failed_assessment() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    SET search_path TO 'public'
    AS $$
DECLARE
  _assessment RECORD;
  _learning_path RECORD;
BEGIN
  -- Only process failed attempts
  IF NEW.passed = false AND NEW.completed_at IS NOT NULL THEN
    -- Get the assessment's competency
    SELECT competency_id INTO _assessment 
    FROM public.competency_assessments 
    WHERE id = NEW.assessment_id;

    IF _assessment.competency_id IS NOT NULL THEN
      -- Find and assign learning paths targeting this competency
      FOR _learning_path IN 
        SELECT id FROM public.learning_paths 
        WHERE target_competency_id = _assessment.competency_id
      LOOP
        INSERT INTO public.learner_learning_paths (user_id, learning_path_id, status, assigned_by)
        VALUES (NEW.user_id, _learning_path.id, 'assigned', NULL)
        ON CONFLICT (user_id, learning_path_id) DO NOTHING;
      END LOOP;
    END IF;
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public.auto_assign_learning_path_on_failed_assessment() OWNER TO postgres;

--
-- TOC entry 373 (class 1255 OID 48518)
-- Name: auto_assign_learning_path_on_gap(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.auto_assign_learning_path_on_gap() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    SET search_path TO 'public'
    AS $$
DECLARE
  _learning_path RECORD;
  _expected_level INTEGER;
  _org_role_id UUID;
BEGIN
  -- Get user's organizational role
  SELECT organizational_role_id INTO _org_role_id 
  FROM public.profiles 
  WHERE id = NEW.user_id;

  -- Get expected level for this competency based on user's role
  IF _org_role_id IS NOT NULL THEN
    SELECT expected_level INTO _expected_level
    FROM public.competency_role_mapping
    WHERE organizational_role_id = _org_role_id 
    AND competency_id = NEW.competency_id;
  END IF;

  -- If user's current level is below expected, assign relevant learning paths
  IF _expected_level IS NOT NULL AND NEW.current_level < _expected_level THEN
    FOR _learning_path IN 
      SELECT id FROM public.learning_paths 
      WHERE target_competency_id = NEW.competency_id
    LOOP
      -- Insert if not already assigned
      INSERT INTO public.learner_learning_paths (user_id, learning_path_id, status, assigned_by)
      VALUES (NEW.user_id, _learning_path.id, 'assigned', NULL)
      ON CONFLICT (user_id, learning_path_id) DO NOTHING;
    END LOOP;
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public.auto_assign_learning_path_on_gap() OWNER TO postgres;

--
-- TOC entry 401 (class 1255 OID 48519)
-- Name: auto_enroll_learner_to_mandatory(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.auto_enroll_learner_to_mandatory() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    SET search_path TO 'public'
    AS $$
DECLARE
  learner_dept_id uuid;
BEGIN
  IF NEW.role = 'learner' THEN
    -- Get learner's department
    SELECT department_id INTO learner_dept_id FROM public.profiles WHERE id = NEW.user_id;

    INSERT INTO public.enrollments (user_id, course_id, status, enrolled_at)
    SELECT NEW.user_id, c.id, 'enrolled', now()
    FROM public.courses c
    WHERE c.is_mandatory = true
      AND c.is_published = true
      AND (
        -- Course has no department filter (applies to all)
        c.mandatory_department_ids IS NULL 
        OR array_length(c.mandatory_department_ids, 1) IS NULL
        -- OR learner's department is in the filter
        OR learner_dept_id = ANY(c.mandatory_department_ids)
      )
      AND NOT EXISTS (
        SELECT 1 FROM public.enrollments e 
        WHERE e.user_id = NEW.user_id AND e.course_id = c.id
      );
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public.auto_enroll_learner_to_mandatory() OWNER TO postgres;

--
-- TOC entry 454 (class 1255 OID 48520)
-- Name: auto_enroll_mandatory_course(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.auto_enroll_mandatory_course() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    SET search_path TO 'public'
    AS $$
BEGIN
  -- When a course is updated to mandatory
  IF NEW.is_mandatory = true AND (OLD.is_mandatory IS NULL OR OLD.is_mandatory = false) THEN
    -- If no department filter, enroll ALL learners
    IF NEW.mandatory_department_ids IS NULL OR array_length(NEW.mandatory_department_ids, 1) IS NULL THEN
      INSERT INTO public.enrollments (user_id, course_id, status, enrolled_at)
      SELECT ur.user_id, NEW.id, 'enrolled', now()
      FROM public.user_roles ur
      WHERE ur.role = 'learner'
        AND NOT EXISTS (
          SELECT 1 FROM public.enrollments e 
          WHERE e.user_id = ur.user_id AND e.course_id = NEW.id
        );
    ELSE
      -- Enroll only learners from specified departments
      INSERT INTO public.enrollments (user_id, course_id, status, enrolled_at)
      SELECT ur.user_id, NEW.id, 'enrolled', now()
      FROM public.user_roles ur
      JOIN public.profiles p ON p.id = ur.user_id
      WHERE ur.role = 'learner'
        AND p.department_id = ANY(NEW.mandatory_department_ids)
        AND NOT EXISTS (
          SELECT 1 FROM public.enrollments e 
          WHERE e.user_id = ur.user_id AND e.course_id = NEW.id
        );
    END IF;
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public.auto_enroll_mandatory_course() OWNER TO postgres;

--
-- TOC entry 419 (class 1255 OID 48521)
-- Name: auto_enroll_new_mandatory_course(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.auto_enroll_new_mandatory_course() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    SET search_path TO 'public'
    AS $$
BEGIN
  IF NEW.is_mandatory = true THEN
    -- If no department filter, enroll ALL learners
    IF NEW.mandatory_department_ids IS NULL OR array_length(NEW.mandatory_department_ids, 1) IS NULL THEN
      INSERT INTO public.enrollments (user_id, course_id, status, enrolled_at)
      SELECT ur.user_id, NEW.id, 'enrolled', now()
      FROM public.user_roles ur
      WHERE ur.role = 'learner'
        AND NOT EXISTS (
          SELECT 1 FROM public.enrollments e 
          WHERE e.user_id = ur.user_id AND e.course_id = NEW.id
        );
    ELSE
      -- Enroll only learners from specified departments
      INSERT INTO public.enrollments (user_id, course_id, status, enrolled_at)
      SELECT ur.user_id, NEW.id, 'enrolled', now()
      FROM public.user_roles ur
      JOIN public.profiles p ON p.id = ur.user_id
      WHERE ur.role = 'learner'
        AND p.department_id = ANY(NEW.mandatory_department_ids)
        AND NOT EXISTS (
          SELECT 1 FROM public.enrollments e 
          WHERE e.user_id = ur.user_id AND e.course_id = NEW.id
        );
    END IF;
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public.auto_enroll_new_mandatory_course() OWNER TO postgres;

--
-- TOC entry 475 (class 1255 OID 48522)
-- Name: award_assignment_points(uuid, uuid, uuid, integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.award_assignment_points(_user_id uuid, _assignment_id uuid, _submission_id uuid, _score integer) RETURNS integer
    LANGUAGE plpgsql SECURITY DEFINER
    SET search_path TO 'public'
    AS $$
DECLARE
  _assignment RECORD;
  _submission RECORD;
  _total_points INTEGER := 0;
  _points_earned INTEGER;
BEGIN
  -- Get assignment details
  SELECT * INTO _assignment FROM public.batch_assignments WHERE id = _assignment_id;

  IF _assignment IS NULL THEN
    RETURN 0;
  END IF;

  -- Get submission details
  SELECT * INTO _submission FROM public.assignment_submissions WHERE id = _submission_id;

  IF _submission IS NULL OR _submission.submitted_at IS NULL THEN
    RETURN 0;
  END IF;

  -- Check for early submission (within 24 hours of start date)
  IF _assignment.points_early_submission > 0 AND _assignment.start_date IS NOT NULL THEN
    IF _submission.submitted_at <= (_assignment.start_date::timestamp + interval '24 hours') THEN
      _points_earned := _assignment.points_early_submission;
      _total_points := _total_points + _points_earned;

      INSERT INTO public.point_transactions (user_id, trigger_event, points_earned, reference_type, reference_id, description)
      VALUES (_user_id, 'assessment_passed', _points_earned, 'early_submission', _submission_id, 'Early submission bonus for: ' || _assignment.title);
    END IF;
  END IF;

  -- Check for on-time completion (before due date)
  IF _assignment.points_on_time > 0 AND _assignment.due_date IS NOT NULL THEN
    IF _submission.submitted_at <= _assignment.due_date::timestamp THEN
      _points_earned := _assignment.points_on_time;
      _total_points := _total_points + _points_earned;

      INSERT INTO public.point_transactions (user_id, trigger_event, points_earned, reference_type, reference_id, description)
      VALUES (_user_id, 'assessment_passed', _points_earned, 'on_time_submission', _submission_id, 'On-time completion for: ' || _assignment.title);
    END IF;
  END IF;

  -- Check for full marks
  IF _assignment.points_full_marks > 0 AND _score IS NOT NULL AND _assignment.max_score IS NOT NULL THEN
    IF _score >= _assignment.max_score THEN
      _points_earned := _assignment.points_full_marks;
      _total_points := _total_points + _points_earned;

      INSERT INTO public.point_transactions (user_id, trigger_event, points_earned, reference_type, reference_id, description)
      VALUES (_user_id, 'assessment_passed', _points_earned, 'full_marks', _submission_id, 'Full marks bonus for: ' || _assignment.title);
    END IF;
  END IF;

  -- Update learner total points if any were earned
  IF _total_points > 0 THEN
    INSERT INTO public.learner_points (user_id, total_points, last_activity_date)
    VALUES (_user_id, _total_points, CURRENT_DATE)
    ON CONFLICT (user_id) DO UPDATE SET
      total_points = learner_points.total_points + _total_points,
      last_activity_date = CURRENT_DATE,
      updated_at = now();
  END IF;

  RETURN _total_points;
END;
$$;


ALTER FUNCTION public.award_assignment_points(_user_id uuid, _assignment_id uuid, _submission_id uuid, _score integer) OWNER TO postgres;

--
-- TOC entry 382 (class 1255 OID 48523)
-- Name: award_points(uuid, public.point_trigger_event, text, uuid, text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.award_points(_user_id uuid, _trigger_event public.point_trigger_event, _reference_type text DEFAULT NULL::text, _reference_id uuid DEFAULT NULL::uuid, _difficulty text DEFAULT 'beginner'::text) RETURNS integer
    LANGUAGE plpgsql SECURITY DEFINER
    SET search_path TO 'public'
    AS $$
DECLARE
  _rule RECORD;
  _points INTEGER := 0;
  _multiplier NUMERIC := 1.0;
  _diff_mult NUMERIC := 1.0;
BEGIN
  -- Get the active rule for this trigger event
  SELECT * INTO _rule FROM public.point_rules
  WHERE trigger_event = _trigger_event AND is_active = true
  LIMIT 1;

  IF _rule IS NULL THEN
    RETURN 0;
  END IF;

  -- Calculate difficulty multiplier
  IF _rule.difficulty_multiplier IS NOT NULL AND _difficulty IS NOT NULL THEN
    _diff_mult := COALESCE((_rule.difficulty_multiplier->>_difficulty)::NUMERIC, 1.0);
  END IF;

  -- Calculate total multiplier
  _multiplier := _diff_mult * COALESCE(_rule.competency_multiplier, 1.0) * COALESCE(_rule.priority_multiplier, 1.0);

  -- Calculate points
  _points := ROUND(_rule.base_points * _multiplier);

  -- Insert transaction
  INSERT INTO public.point_transactions (user_id, rule_id, trigger_event, points_earned, multiplier_applied, reference_type, reference_id, description)
  VALUES (_user_id, _rule.id, _trigger_event, _points, _multiplier, _reference_type, _reference_id, _rule.name);

  -- Update learner points
  INSERT INTO public.learner_points (user_id, total_points, last_activity_date)
  VALUES (_user_id, _points, CURRENT_DATE)
  ON CONFLICT (user_id) DO UPDATE SET
    total_points = learner_points.total_points + _points,
    last_activity_date = CURRENT_DATE,
    updated_at = now();

  RETURN _points;
END;
$$;


ALTER FUNCTION public.award_points(_user_id uuid, _trigger_event public.point_trigger_event, _reference_type text, _reference_id uuid, _difficulty text) OWNER TO postgres;

--
-- TOC entry 396 (class 1255 OID 48524)
-- Name: calculate_competency_score(uuid, uuid); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.calculate_competency_score(_user_id uuid, _competency_id uuid) RETURNS TABLE(weighted_score numeric, calculated_level integer, total_assessments integer)
    LANGUAGE plpgsql SECURITY DEFINER
    SET search_path TO 'public'
    AS $$
DECLARE
  _total_weighted_score numeric := 0;
  _total_weight numeric := 0;
  _assessment_count integer := 0;
  _final_score numeric := 0;
  _level integer := 0;
  _scale_id uuid;
BEGIN
  -- Get the proficiency scale for this competency
  SELECT proficiency_scale_id INTO _scale_id 
  FROM public.competencies 
  WHERE id = _competency_id;

  -- Calculate weighted average from all assessment attempts
  SELECT 
    COALESCE(SUM(caa.score * COALESCE(ca.weight, 1.0)), 0),
    COALESCE(SUM(COALESCE(ca.weight, 1.0)), 0),
    COUNT(DISTINCT caa.id)
  INTO _total_weighted_score, _total_weight, _assessment_count
  FROM public.competency_assessment_attempts caa
  JOIN public.competency_assessments ca ON ca.id = caa.assessment_id
  WHERE caa.user_id = _user_id 
    AND ca.competency_id = _competency_id
    AND caa.completed_at IS NOT NULL;

  -- Calculate final weighted score
  IF _total_weight > 0 THEN
    _final_score := _total_weighted_score / _total_weight;
  END IF;

  -- Convert score to level using the scale's mappings
  SELECT slm.level_number INTO _level
  FROM public.score_level_mappings slm
  WHERE slm.proficiency_scale_id = _scale_id
    AND _final_score >= slm.min_score
    AND _final_score <= slm.max_score
  ORDER BY slm.level_number DESC
  LIMIT 1;

  -- Default to level 1 if no mapping found
  IF _level IS NULL THEN
    _level := CASE 
      WHEN _final_score >= 90 THEN 5
      WHEN _final_score >= 80 THEN 4
      WHEN _final_score >= 65 THEN 3
      WHEN _final_score >= 50 THEN 2
      ELSE 1
    END;
  END IF;

  RETURN QUERY SELECT _final_score, _level, _assessment_count;
END;
$$;


ALTER FUNCTION public.calculate_competency_score(_user_id uuid, _competency_id uuid) OWNER TO postgres;

--
-- TOC entry 383 (class 1255 OID 48525)
-- Name: can_edit_help_content(uuid); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.can_edit_help_content(_user_id uuid) RETURNS boolean
    LANGUAGE sql STABLE SECURITY DEFINER
    SET search_path TO 'public'
    AS $$
  SELECT EXISTS (
    SELECT 1 FROM public.user_roles
    WHERE user_id = _user_id 
    AND role IN ('admin', 'help_editor')
  )
$$;


ALTER FUNCTION public.can_edit_help_content(_user_id uuid) OWNER TO postgres;

--
-- TOC entry 448 (class 1255 OID 48526)
-- Name: check_and_award_badges(uuid); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.check_and_award_badges(_user_id uuid) RETURNS SETOF uuid
    LANGUAGE plpgsql SECURITY DEFINER
    SET search_path TO 'public'
    AS $$
DECLARE
  _badge RECORD;
  _criteria JSONB;
  _earned BOOLEAN;
  _count INTEGER;
BEGIN
  FOR _badge IN 
    SELECT * FROM public.badge_definitions 
    WHERE is_active = true 
    AND id NOT IN (SELECT badge_id FROM public.learner_badges WHERE user_id = _user_id)
  LOOP
    _earned := false;
    _criteria := _badge.criteria;

    -- Check different criteria types
    IF _criteria->>'type' = 'modules_completed' THEN
      SELECT COUNT(*) INTO _count FROM public.content_progress
      WHERE user_id = _user_id AND status = 'completed';
      _earned := _count >= (_criteria->>'count')::INTEGER;

    ELSIF _criteria->>'type' = 'courses_completed' THEN
      SELECT COUNT(*) INTO _count FROM public.enrollments
      WHERE user_id = _user_id AND status = 'completed';
      _earned := _count >= (_criteria->>'count')::INTEGER;

    ELSIF _criteria->>'type' = 'points_reached' THEN
      SELECT total_points INTO _count FROM public.learner_points WHERE user_id = _user_id;
      _earned := COALESCE(_count, 0) >= (_criteria->>'points')::INTEGER;

    ELSIF _criteria->>'type' = 'streak_achieved' THEN
      SELECT current_streak INTO _count FROM public.streak_tracker WHERE user_id = _user_id;
      _earned := COALESCE(_count, 0) >= (_criteria->>'days')::INTEGER;
    END IF;

    IF _earned THEN
      INSERT INTO public.learner_badges (user_id, badge_id)
      VALUES (_user_id, _badge.id)
      ON CONFLICT DO NOTHING;

      -- Award bonus points for badge
      IF _badge.points_reward > 0 THEN
        PERFORM award_points(_user_id, 'badge_earned', 'badge', _badge.id);
      END IF;

      RETURN NEXT _badge.id;
    END IF;
  END LOOP;

  RETURN;
END;
$$;


ALTER FUNCTION public.check_and_award_badges(_user_id uuid) OWNER TO postgres;

--
-- TOC entry 386 (class 1255 OID 48527)
-- Name: check_competency_badges(uuid); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.check_competency_badges(_user_id uuid) RETURNS SETOF uuid
    LANGUAGE plpgsql SECURITY DEFINER
    SET search_path TO 'public'
    AS $$
DECLARE
  _badge RECORD;
  _criteria JSONB;
  _earned BOOLEAN;
  _count INTEGER;
  _profile RECORD;
  _gaps_count INTEGER;
BEGIN
  -- Get user's profile and role
  SELECT organizational_role_id INTO _profile FROM public.profiles WHERE id = _user_id;

  FOR _badge IN 
    SELECT * FROM public.badge_definitions 
    WHERE is_active = true 
    AND id NOT IN (SELECT badge_id FROM public.learner_badges WHERE user_id = _user_id)
  LOOP
    _earned := false;
    _criteria := _badge.criteria;

    -- Check competency-specific criteria
    IF _criteria->>'type' = 'competency_level_reached' THEN
      SELECT COUNT(*) INTO _count FROM public.learner_competency_progress
      WHERE user_id = _user_id AND current_level >= COALESCE((_criteria->>'level')::INTEGER, 3);
      _earned := _count >= COALESCE((_criteria->>'count')::INTEGER, 1);

    ELSIF _criteria->>'type' = 'all_gaps_closed' THEN
      -- Check if user has closed all competency gaps for their role
      IF _profile.organizational_role_id IS NOT NULL THEN
        SELECT COUNT(*) INTO _gaps_count
        FROM public.competency_role_mapping crm
        LEFT JOIN public.learner_competency_progress lcp ON lcp.competency_id = crm.competency_id AND lcp.user_id = _user_id
        WHERE crm.organizational_role_id = _profile.organizational_role_id
        AND COALESCE(lcp.current_level, 0) < crm.expected_level;
        _earned := _gaps_count = 0;
      END IF;

    ELSIF _criteria->>'type' = 'competency_improvement' THEN
      -- Check total levels improved
      SELECT COALESCE(SUM(level_after - level_before), 0) INTO _count
      FROM public.competency_history WHERE user_id = _user_id;
      _earned := _count >= COALESCE((_criteria->>'levels')::INTEGER, 5);
    END IF;

    IF _earned THEN
      INSERT INTO public.learner_badges (user_id, badge_id)
      VALUES (_user_id, _badge.id)
      ON CONFLICT DO NOTHING;

      -- Award bonus points for badge
      IF _badge.points_reward > 0 THEN
        PERFORM award_points(_user_id, 'badge_earned', 'badge', _badge.id);
      END IF;

      RETURN NEXT _badge.id;
    END IF;
  END LOOP;

  RETURN;
END;
$$;


ALTER FUNCTION public.check_competency_badges(_user_id uuid) OWNER TO postgres;

--
-- TOC entry 459 (class 1255 OID 48528)
-- Name: check_question_answer(uuid, text, text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.check_question_answer(_question_id uuid, _question_source text, _user_answer text) RETURNS boolean
    LANGUAGE plpgsql SECURITY DEFINER
    SET search_path TO 'public'
    AS $$
DECLARE
  _correct_answer text;
BEGIN
  IF auth.uid() IS NULL THEN
    RAISE EXCEPTION 'Not authenticated';
  END IF;

  IF _question_source = 'inline' THEN
    SELECT correct_answer INTO _correct_answer
    FROM public.competency_assessment_questions
    WHERE id = _question_id;
  ELSIF _question_source = 'question_bank' THEN
    SELECT correct_answer INTO _correct_answer
    FROM public.question_bank
    WHERE id = _question_id;
  ELSE
    RAISE EXCEPTION 'Invalid question source';
  END IF;

  RETURN _correct_answer = _user_answer;
END;
$$;


ALTER FUNCTION public.check_question_answer(_question_id uuid, _question_source text, _user_answer text) OWNER TO postgres;

--
-- TOC entry 458 (class 1255 OID 48529)
-- Name: create_external_user_profile(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.create_external_user_profile() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    SET search_path TO 'public'
    AS $$
BEGIN
  -- Check if the new role is an external user type
  IF NEW.role IN ('external_customer', 'contractor', 'delivery_staff') THEN
    -- Create external user profile if it doesn't exist
    INSERT INTO public.external_user_profiles (user_id, external_user_type)
    VALUES (NEW.user_id, NEW.role::text)
    ON CONFLICT (user_id) DO UPDATE SET
      external_user_type = NEW.role::text,
      updated_at = now();
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public.create_external_user_profile() OWNER TO postgres;

--
-- TOC entry 402 (class 1255 OID 48530)
-- Name: expand_assessment_assignment(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.expand_assessment_assignment() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    SET search_path TO 'public'
    AS $$
DECLARE
  _learner_id UUID;
BEGIN
  -- Expand based on assignment type
  IF NEW.assignment_type = 'individual' THEN
    -- Direct assignment to specific users
    FOREACH _learner_id IN ARRAY NEW.target_ids
    LOOP
      INSERT INTO public.assessment_assignment_learners (assignment_id, learner_id)
      VALUES (NEW.id, _learner_id)
      ON CONFLICT (assignment_id, learner_id) DO NOTHING;
    END LOOP;

  ELSIF NEW.assignment_type = 'department' THEN
    -- Assign to all learners in specified departments
    INSERT INTO public.assessment_assignment_learners (assignment_id, learner_id)
    SELECT NEW.id, p.id
    FROM public.profiles p
    JOIN public.user_roles ur ON ur.user_id = p.id
    WHERE p.department_id = ANY(NEW.target_ids)
    AND ur.role = 'learner'
    ON CONFLICT (assignment_id, learner_id) DO NOTHING;

  ELSIF NEW.assignment_type = 'job_family' THEN
    -- Assign to all learners in specified job families
    INSERT INTO public.assessment_assignment_learners (assignment_id, learner_id)
    SELECT NEW.id, p.id
    FROM public.profiles p
    JOIN public.user_roles ur ON ur.user_id = p.id
    JOIN public.organizational_roles orl ON orl.id = p.organizational_role_id
    WHERE orl.job_family_id = ANY(NEW.target_ids)
    AND ur.role = 'learner'
    ON CONFLICT (assignment_id, learner_id) DO NOTHING;

  ELSIF NEW.assignment_type = 'all' THEN
    -- Assign to all learners
    INSERT INTO public.assessment_assignment_learners (assignment_id, learner_id)
    SELECT NEW.id, ur.user_id
    FROM public.user_roles ur
    WHERE ur.role = 'learner'
    ON CONFLICT (assignment_id, learner_id) DO NOTHING;
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public.expand_assessment_assignment() OWNER TO postgres;

--
-- TOC entry 391 (class 1255 OID 48531)
-- Name: generate_batch_code(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.generate_batch_code() RETURNS trigger
    LANGUAGE plpgsql
    SET search_path TO 'public'
    AS $_$
DECLARE
  course_prefix text;
  batch_prefix text;
  seq_num integer;
  new_code text;
BEGIN
  -- Get course name prefix (first 3 chars uppercase)
  SELECT UPPER(SUBSTRING(REGEXP_REPLACE(title, '[^a-zA-Z0-9]', '', 'g'), 1, 3))
  INTO course_prefix
  FROM public.courses
  WHERE id = NEW.course_id;

  -- If no course, use 'GEN'
  IF course_prefix IS NULL OR course_prefix = '' THEN
    course_prefix := 'GEN';
  END IF;

  -- Get batch name prefix (first 3 chars uppercase)
  batch_prefix := UPPER(SUBSTRING(REGEXP_REPLACE(NEW.name, '[^a-zA-Z0-9]', '', 'g'), 1, 3));

  IF batch_prefix IS NULL OR batch_prefix = '' THEN
    batch_prefix := 'BAT';
  END IF;

  -- Get next sequence number for this prefix combination
  SELECT COALESCE(MAX(
    CAST(SUBSTRING(batch_code FROM '[0-9]+$') AS integer)
  ), 0) + 1
  INTO seq_num
  FROM public.batches
  WHERE batch_code LIKE course_prefix || '-' || batch_prefix || '-%';

  -- Generate the code
  new_code := course_prefix || '-' || batch_prefix || '-' || LPAD(seq_num::text, 4, '0');

  NEW.batch_code := new_code;
  RETURN NEW;
END;
$_$;


ALTER FUNCTION public.generate_batch_code() OWNER TO postgres;

--
-- TOC entry 371 (class 1255 OID 48532)
-- Name: get_assessment_questions_for_learner(uuid); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.get_assessment_questions_for_learner(_assessment_id uuid) RETURNS TABLE(id uuid, question_text text, question_type text, options jsonb, points integer, difficulty text, media_url text, media_type text, explanation text, order_index integer)
    LANGUAGE plpgsql SECURITY DEFINER
    SET search_path TO 'public'
    AS $$
BEGIN
  IF auth.uid() IS NULL THEN
    RAISE EXCEPTION 'Not authenticated';
  END IF;

  RETURN QUERY
  SELECT 
    q.id,
    q.question_text,
    q.question_type,
    q.options,
    q.points,
    q.difficulty,
    q.media_url,
    q.media_type,
    q.explanation,
    q.order_index
  FROM public.competency_assessment_questions q
  WHERE q.assessment_id = _assessment_id;
END;
$$;


ALTER FUNCTION public.get_assessment_questions_for_learner(_assessment_id uuid) OWNER TO postgres;

--
-- TOC entry 461 (class 1255 OID 48533)
-- Name: get_hrms_config_safe(uuid); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.get_hrms_config_safe(_config_id uuid) RETURNS TABLE(id uuid, name text, hrms_type text, is_active boolean, last_sync_at timestamp with time zone, created_at timestamp with time zone, has_api_key boolean, has_api_secret boolean, base_url text)
    LANGUAGE plpgsql SECURITY DEFINER
    SET search_path TO 'public'
    AS $$
BEGIN
  -- Only admins can access
  IF NOT has_role(auth.uid(), 'admin'::app_role) THEN
    RAISE EXCEPTION 'Access denied';
  END IF;

  RETURN QUERY
  SELECT 
    hc.id,
    hc.name,
    hc.hrms_type,
    hc.is_active,
    hc.last_sync_at,
    hc.created_at,
    (hc.config_data->>'api_key' IS NOT NULL) as has_api_key,
    (hc.config_data->>'api_secret' IS NOT NULL) as has_api_secret,
    hc.config_data->>'base_url' as base_url
  FROM public.hrms_configurations hc
  WHERE hc.id = _config_id;
END;
$$;


ALTER FUNCTION public.get_hrms_config_safe(_config_id uuid) OWNER TO postgres;

--
-- TOC entry 411 (class 1255 OID 48534)
-- Name: get_question_bank_questions_for_assessment(uuid); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.get_question_bank_questions_for_assessment(_assessment_id uuid) RETURNS TABLE(id uuid, question_text text, question_type text, options jsonb, points integer, difficulty text, media_url text, media_type text, explanation text, order_index integer)
    LANGUAGE plpgsql SECURITY DEFINER
    SET search_path TO 'public'
    AS $$
BEGIN
  IF auth.uid() IS NULL THEN
    RAISE EXCEPTION 'Not authenticated';
  END IF;

  RETURN QUERY
  SELECT 
    qb.id,
    qb.question_text,
    qb.question_type,
    qb.options,
    COALESCE(aql.points_override, qb.points) as points,
    qb.difficulty,
    qb.media_url,
    qb.media_type,
    qb.explanation,
    aql.order_index
  FROM public.assessment_question_links aql
  JOIN public.question_bank qb ON qb.id = aql.question_bank_id
  WHERE aql.assessment_id = _assessment_id
    AND qb.is_active = true;
END;
$$;


ALTER FUNCTION public.get_question_bank_questions_for_assessment(_assessment_id uuid) OWNER TO postgres;

--
-- TOC entry 415 (class 1255 OID 48535)
-- Name: get_safe_profile_data(uuid); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.get_safe_profile_data(profile_id uuid) RETURNS TABLE(id uuid, full_name text, avatar_url text, department_id uuid, designation text, organizational_role_id uuid, is_manager boolean, email text, employee_id text)
    LANGUAGE plpgsql SECURITY DEFINER
    SET search_path TO 'public'
    AS $$
DECLARE
  requesting_user_id uuid;
  is_admin boolean;
BEGIN
  requesting_user_id := auth.uid();

  -- Check if requesting user is admin
  SELECT EXISTS (
    SELECT 1 FROM public.user_roles 
    WHERE user_id = requesting_user_id 
    AND role IN ('admin', 'department_head')
  ) INTO is_admin;

  -- If user is viewing their own profile or is admin, return full data
  IF requesting_user_id = profile_id OR is_admin THEN
    RETURN QUERY
    SELECT 
      p.id,
      p.full_name,
      p.avatar_url,
      p.department_id,
      p.designation,
      p.organizational_role_id,
      p.is_manager,
      p.email,
      p.employee_id
    FROM public.profiles p
    WHERE p.id = profile_id;
  ELSE
    -- For trainers and others, mask sensitive fields
    RETURN QUERY
    SELECT 
      p.id,
      p.full_name,
      p.avatar_url,
      p.department_id,
      p.designation,
      p.organizational_role_id,
      p.is_manager,
      -- Mask email: show only first 2 chars and domain
      CASE 
        WHEN p.email IS NOT NULL AND p.email LIKE '%@%' THEN
          CONCAT(
            LEFT(SPLIT_PART(p.email, '@', 1), 2),
            '***@',
            SPLIT_PART(p.email, '@', 2)
          )
        ELSE NULL
      END::text as email,
      -- Mask employee_id: show only last 4 characters
      CASE 
        WHEN p.employee_id IS NOT NULL AND LENGTH(p.employee_id) > 4 THEN
          CONCAT('***', RIGHT(p.employee_id, 4))
        ELSE '****'
      END::text as employee_id
    FROM public.profiles p
    WHERE p.id = profile_id;
  END IF;
END;
$$;


ALTER FUNCTION public.get_safe_profile_data(profile_id uuid) OWNER TO postgres;

--
-- TOC entry 5839 (class 0 OID 0)
-- Dependencies: 415
-- Name: FUNCTION get_safe_profile_data(profile_id uuid); Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON FUNCTION public.get_safe_profile_data(profile_id uuid) IS 'Secure function to get profile data with role-based masking. 
Admins and department heads see full data, other users see masked emails and employee IDs.
This replaces the profiles_safe view which lacked proper RLS.';


--
-- TOC entry 416 (class 1255 OID 48536)
-- Name: get_user_group_ids(uuid); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.get_user_group_ids(_user_id uuid) RETURNS SETOF uuid
    LANGUAGE sql STABLE SECURITY DEFINER
    SET search_path TO 'public'
    AS $$
  SELECT group_id FROM public.assignment_group_members
  WHERE learner_id = _user_id
$$;


ALTER FUNCTION public.get_user_group_ids(_user_id uuid) OWNER TO postgres;

--
-- TOC entry 436 (class 1255 OID 48537)
-- Name: get_user_organization(uuid); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.get_user_organization(_user_id uuid) RETURNS uuid
    LANGUAGE sql STABLE SECURITY DEFINER
    SET search_path TO 'public'
    AS $$
  SELECT organization_id FROM public.external_user_profiles
  WHERE user_id = _user_id
  LIMIT 1
$$;


ALTER FUNCTION public.get_user_organization(_user_id uuid) OWNER TO postgres;

--
-- TOC entry 410 (class 1255 OID 48538)
-- Name: handle_new_user(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.handle_new_user() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    SET search_path TO 'public'
    AS $$
BEGIN
  INSERT INTO public.profiles (id, email, full_name)
  VALUES (new.id, new.email, new.raw_user_meta_data ->> 'full_name');

  INSERT INTO public.user_roles (user_id, role)
  VALUES (new.id, 'learner');

  RETURN new;
END;
$$;


ALTER FUNCTION public.handle_new_user() OWNER TO postgres;

--
-- TOC entry 414 (class 1255 OID 48539)
-- Name: has_role(uuid, public.app_role); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.has_role(_user_id uuid, _role public.app_role) RETURNS boolean
    LANGUAGE sql STABLE SECURITY DEFINER
    SET search_path TO 'public'
    AS $$
  SELECT EXISTS (
    SELECT 1 FROM public.user_roles
    WHERE user_id = _user_id AND role = _role
  )
$$;


ALTER FUNCTION public.has_role(_user_id uuid, _role public.app_role) OWNER TO postgres;

--
-- TOC entry 420 (class 1255 OID 48540)
-- Name: increment_question_usage(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.increment_question_usage() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    SET search_path TO 'public'
    AS $$
BEGIN
  IF NEW.question_bank_id IS NOT NULL THEN
    UPDATE public.question_bank 
    SET usage_count = usage_count + 1 
    WHERE id = NEW.question_bank_id;
  END IF;
  RETURN NEW;
END;
$$;


ALTER FUNCTION public.increment_question_usage() OWNER TO postgres;

--
-- TOC entry 404 (class 1255 OID 48541)
-- Name: is_admin_or_dept_head(uuid); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.is_admin_or_dept_head(_user_id uuid) RETURNS boolean
    LANGUAGE sql STABLE SECURITY DEFINER
    SET search_path TO 'public'
    AS $$
  SELECT EXISTS (
    SELECT 1 FROM public.user_roles
    WHERE user_id = _user_id AND role IN ('admin', 'department_head')
  )
$$;


ALTER FUNCTION public.is_admin_or_dept_head(_user_id uuid) OWNER TO postgres;

--
-- TOC entry 378 (class 1255 OID 48542)
-- Name: is_batch_trainer(uuid, uuid); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.is_batch_trainer(_user_id uuid, _batch_id uuid) RETURNS boolean
    LANGUAGE sql STABLE SECURITY DEFINER
    SET search_path TO 'public'
    AS $$
  SELECT EXISTS (
    SELECT 1
    FROM public.batch_trainers bt
    JOIN public.trainers t ON bt.trainer_id = t.id
    WHERE bt.batch_id = _batch_id
      AND t.user_id = _user_id
  )
  OR EXISTS (
    SELECT 1
    FROM public.batches b
    WHERE b.id = _batch_id
      AND b.trainer_id = _user_id
  )
$$;


ALTER FUNCTION public.is_batch_trainer(_user_id uuid, _batch_id uuid) OWNER TO postgres;

--
-- TOC entry 453 (class 1255 OID 48543)
-- Name: is_external_user(uuid); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.is_external_user(_user_id uuid) RETURNS boolean
    LANGUAGE sql STABLE SECURITY DEFINER
    SET search_path TO 'public'
    AS $$
  SELECT EXISTS (
    SELECT 1 FROM public.user_roles
    WHERE user_id = _user_id 
    AND role IN ('external_customer', 'contractor', 'delivery_staff')
  )
$$;


ALTER FUNCTION public.is_external_user(_user_id uuid) OWNER TO postgres;

--
-- TOC entry 444 (class 1255 OID 48544)
-- Name: is_group_member(uuid, uuid); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.is_group_member(_user_id uuid, _group_id uuid) RETURNS boolean
    LANGUAGE sql STABLE SECURITY DEFINER
    SET search_path TO 'public'
    AS $$
  SELECT EXISTS (
    SELECT 1 FROM public.assignment_group_members
    WHERE learner_id = _user_id 
    AND group_id = _group_id
  )
$$;


ALTER FUNCTION public.is_group_member(_user_id uuid, _group_id uuid) OWNER TO postgres;

--
-- TOC entry 376 (class 1255 OID 48545)
-- Name: is_mentor_of_learner(uuid, uuid); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.is_mentor_of_learner(_mentor_id uuid, _learner_id uuid) RETURNS boolean
    LANGUAGE sql STABLE SECURITY DEFINER
    SET search_path TO 'public'
    AS $$
  SELECT EXISTS (
    SELECT 1 FROM public.mentor_learners
    WHERE mentor_id = _mentor_id 
    AND learner_id = _learner_id
  )
$$;


ALTER FUNCTION public.is_mentor_of_learner(_mentor_id uuid, _learner_id uuid) OWNER TO postgres;

--
-- TOC entry 389 (class 1255 OID 48546)
-- Name: is_trainer(uuid); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.is_trainer(_user_id uuid) RETURNS boolean
    LANGUAGE sql STABLE SECURITY DEFINER
    SET search_path TO 'public'
    AS $$
  SELECT EXISTS (
    SELECT 1 FROM public.user_roles
    WHERE user_id = _user_id AND role = 'trainer'
  )
$$;


ALTER FUNCTION public.is_trainer(_user_id uuid) OWNER TO postgres;

--
-- TOC entry 470 (class 1255 OID 48547)
-- Name: log_audit_event(text, text, uuid, jsonb, jsonb); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.log_audit_event(_action_type text, _entity_type text, _entity_id uuid DEFAULT NULL::uuid, _old_values jsonb DEFAULT NULL::jsonb, _new_values jsonb DEFAULT NULL::jsonb) RETURNS uuid
    LANGUAGE plpgsql SECURITY DEFINER
    SET search_path TO 'public'
    AS $$
DECLARE
  _log_id UUID;
BEGIN
  INSERT INTO public.audit_logs (user_id, action_type, entity_type, entity_id, old_values, new_values)
  VALUES (auth.uid(), _action_type, _entity_type, _entity_id, _old_values, _new_values)
  RETURNING id INTO _log_id;

  RETURN _log_id;
END;
$$;


ALTER FUNCTION public.log_audit_event(_action_type text, _entity_type text, _entity_id uuid, _old_values jsonb, _new_values jsonb) OWNER TO postgres;

--
-- TOC entry 478 (class 1255 OID 48548)
-- Name: log_org_structure_change(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.log_org_structure_change() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    AS $$
 BEGIN
     IF TG_OP = 'INSERT' THEN
         INSERT INTO org_structure_audit (entity_type, entity_id, action, new_values, changed_by)
         VALUES (TG_TABLE_NAME, CAST(NEW.id AS uuid), 'create', to_jsonb(NEW), auth.uid());
         RETURN NEW;
     ELSIF TG_OP = 'UPDATE' THEN
         INSERT INTO org_structure_audit (entity_type, entity_id, action, old_values, new_values, changed_by)
         VALUES (TG_TABLE_NAME, CAST(NEW.id AS uuid), 'update', to_jsonb(OLD), to_jsonb(NEW), auth.uid());
         RETURN NEW;
     ELSIF TG_OP = 'DELETE' THEN
         INSERT INTO org_structure_audit (entity_type, entity_id, action, old_values, changed_by)
         VALUES (TG_TABLE_NAME, CAST(OLD.id AS uuid), 'delete', to_jsonb(OLD), auth.uid());
         RETURN OLD;
     END IF;
     RETURN NULL;
 END;
 $$;


ALTER FUNCTION public.log_org_structure_change() OWNER TO postgres;

--
-- TOC entry 464 (class 1255 OID 48549)
-- Name: log_submission_status_change(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.log_submission_status_change() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    SET search_path TO 'public'
    AS $$
BEGIN
  -- Only log if status changed or this is a new submission
  IF TG_OP = 'INSERT' OR (TG_OP = 'UPDATE' AND OLD.status IS DISTINCT FROM NEW.status) THEN
    INSERT INTO public.assignment_submission_history (
      submission_id,
      action,
      action_by,
      previous_status,
      new_status,
      notes,
      submission_snapshot
    ) VALUES (
      NEW.id,
      CASE 
        WHEN NEW.status = 'submitted' AND (OLD IS NULL OR OLD.status = 'rejected') THEN 'submitted'
        WHEN NEW.status = 'approved' THEN 'approved'
        WHEN NEW.status = 'rejected' THEN 'rejected'
        WHEN NEW.status = 'completed' THEN 'auto_completed'
        ELSE NEW.status
      END,
      COALESCE(auth.uid(), NEW.student_id),
      CASE WHEN TG_OP = 'UPDATE' THEN OLD.status ELSE NULL END,
      NEW.status,
      CASE 
        WHEN NEW.status = 'rejected' THEN NEW.rejection_reason
        WHEN NEW.status = 'approved' THEN NEW.feedback
        ELSE NULL
      END,
      jsonb_build_object(
        'submission_text', NEW.submission_text,
        'submission_url', NEW.submission_url,
        'file_url', NEW.file_url,
        'file_name', NEW.file_name,
        'score', NEW.score
      )
    );
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public.log_submission_status_change() OWNER TO postgres;

--
-- TOC entry 413 (class 1255 OID 48550)
-- Name: process_competency_gap_assignment(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.process_competency_gap_assignment() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    SET search_path TO 'public'
    AS $$
DECLARE
  _expected_level integer;
  _org_role_id uuid;
  _gap integer;
  _rule RECORD;
  _learning_path RECORD;
BEGIN
  -- Get user's organizational role
  SELECT organizational_role_id INTO _org_role_id 
  FROM public.profiles 
  WHERE id = NEW.user_id;

  IF _org_role_id IS NULL THEN
    RETURN NEW;
  END IF;

  -- Get expected level for this competency based on user's role
  SELECT expected_level INTO _expected_level
  FROM public.competency_role_mapping
  WHERE organizational_role_id = _org_role_id 
    AND competency_id = NEW.competency_id;

  IF _expected_level IS NULL THEN
    RETURN NEW;
  END IF;

  -- Calculate gap
  _gap := _expected_level - COALESCE(NEW.current_level, 0);

  IF _gap <= 0 THEN
    RETURN NEW; -- No gap, nothing to assign
  END IF;

  -- Find matching gap assignment rule
  SELECT * INTO _rule
  FROM public.gap_assignment_rules
  WHERE _gap >= gap_min AND _gap <= gap_max
    AND auto_assign_enabled = true
  ORDER BY gap_min DESC
  LIMIT 1;

  IF _rule IS NOT NULL THEN
    -- Assign learning paths targeting this competency
    FOR _learning_path IN 
      SELECT id, title FROM public.learning_paths 
      WHERE target_competency_id = NEW.competency_id
    LOOP
      INSERT INTO public.learner_learning_paths (
        user_id, learning_path_id, status, assigned_by, notes
      ) VALUES (
        NEW.user_id, _learning_path.id, 'assigned', NULL,
        'Auto-assigned due to competency gap of ' || _gap || ' levels (' || _rule.assignment_intensity || ' intensity)'
      )
      ON CONFLICT (user_id, learning_path_id) DO NOTHING;
    END LOOP;

    -- Create AI recommendation if enabled
    IF _rule.include_ai_recommendations THEN
      INSERT INTO public.ai_learning_recommendations (
        user_id, recommendation_type, target_id, target_title, reason, priority_score, context
      ) VALUES (
        NEW.user_id, 
        'competency_gap', 
        NEW.competency_id,
        (SELECT name FROM public.competencies WHERE id = NEW.competency_id),
        'Gap of ' || _gap || ' levels detected. ' || _rule.description,
        _gap * 20, -- Higher gap = higher priority
        jsonb_build_object(
          'gap_size', _gap,
          'current_level', NEW.current_level,
          'expected_level', _expected_level,
          'intensity', _rule.assignment_intensity
        )
      );
    END IF;
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public.process_competency_gap_assignment() OWNER TO postgres;

--
-- TOC entry 377 (class 1255 OID 48551)
-- Name: process_enrollment_approval(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.process_enrollment_approval() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    SET search_path TO 'public'
    AS $$
BEGIN
  -- When request is approved, create the enrollment
  IF NEW.status = 'approved' AND OLD.status = 'pending' THEN
    INSERT INTO public.enrollments (user_id, course_id, status)
    VALUES (NEW.user_id, NEW.course_id, 'enrolled')
    ON CONFLICT DO NOTHING;
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public.process_enrollment_approval() OWNER TO postgres;

--
-- TOC entry 409 (class 1255 OID 48552)
-- Name: record_xapi_statement(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.record_xapi_statement() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    SET search_path TO 'public'
    AS $$
DECLARE
  _assessment RECORD;
  _verb TEXT;
BEGIN
  -- Record assessment attempt as xAPI statement
  IF TG_TABLE_NAME = 'competency_assessment_attempts' THEN
    SELECT title INTO _assessment FROM public.competency_assessments WHERE id = NEW.assessment_id;

    IF NEW.completed_at IS NOT NULL THEN
      _verb := CASE WHEN NEW.passed THEN 'passed' ELSE 'failed' END;
    ELSE
      _verb := 'attempted';
    END IF;

    INSERT INTO public.xapi_statements (
      actor_id, verb, object_type, object_id, object_name,
      result_score, result_success, result_completion,
      result_duration
    ) VALUES (
      NEW.user_id,
      _verb,
      'assessment',
      NEW.assessment_id,
      _assessment.title,
      NEW.score,
      NEW.passed,
      NEW.completed_at IS NOT NULL,
      make_interval(secs => NEW.time_taken_seconds)
    );
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public.record_xapi_statement() OWNER TO postgres;

--
-- TOC entry 430 (class 1255 OID 48553)
-- Name: sync_all_enrollment_progress(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.sync_all_enrollment_progress() RETURNS void
    LANGUAGE plpgsql SECURITY DEFINER
    SET search_path TO 'public'
    AS $$
DECLARE
  _enrollment record;
  _total_content integer;
  _completed_content integer;
  _progress_percent integer;
  _course record;
  _existing_cert uuid;
BEGIN
  FOR _enrollment IN SELECT * FROM enrollments WHERE status != 'completed' LOOP
    -- Count total content in the course
    SELECT COUNT(*) INTO _total_content
    FROM module_content mc
    JOIN course_modules cm ON cm.id = mc.module_id
    WHERE cm.course_id = _enrollment.course_id;

    -- Count completed content
    SELECT COUNT(*) INTO _completed_content
    FROM content_progress cp
    JOIN module_content mc ON mc.id = cp.content_id
    JOIN course_modules cm ON cm.id = mc.module_id
    WHERE cm.course_id = _enrollment.course_id
    AND cp.user_id = _enrollment.user_id
    AND cp.status = 'completed';

    -- Calculate progress
    IF _total_content > 0 THEN
      _progress_percent := ROUND((_completed_content::numeric / _total_content::numeric) * 100);
    ELSE
      _progress_percent := 0;
    END IF;

    -- Update enrollment
    IF _progress_percent >= 100 THEN
      UPDATE enrollments
      SET progress_percentage = 100,
          status = 'completed',
          completed_at = COALESCE(completed_at, now())
      WHERE id = _enrollment.id;

      -- Issue certificate if needed
      SELECT * INTO _course FROM courses WHERE id = _enrollment.course_id;

      IF _course.enable_certificates = true THEN
        SELECT id INTO _existing_cert
        FROM issued_certificates
        WHERE user_id = _enrollment.user_id AND enrollment_id = _enrollment.id;

        IF _existing_cert IS NULL THEN
          INSERT INTO issued_certificates (
            user_id,
            enrollment_id,
            course_id,
            course_title,
            learner_name,
            learner_email,
            completion_date,
            certificate_number,
            verification_code,
            template_id
          )
          SELECT 
            _enrollment.user_id,
            _enrollment.id,
            _enrollment.course_id,
            _course.title,
            COALESCE(p.full_name, 'Learner'),
            COALESCE(p.email, 'unknown@email.com'),
            now(),
            'CERT-' || UPPER(SUBSTRING(md5(random()::text), 1, 8)),
            UPPER(SUBSTRING(md5(random()::text || now()::text), 1, 12)),
            _course.certificate_template_id
          FROM profiles p
          WHERE p.id = _enrollment.user_id;
        END IF;
      END IF;
    ELSE
      UPDATE enrollments
      SET progress_percentage = _progress_percent
      WHERE id = _enrollment.id;
    END IF;
  END LOOP;
END;
$$;


ALTER FUNCTION public.sync_all_enrollment_progress() OWNER TO postgres;

--
-- TOC entry 438 (class 1255 OID 48554)
-- Name: sync_enrollment_progress(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.sync_enrollment_progress() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    SET search_path TO 'public'
    AS $$
DECLARE
  _course_id uuid;
  _user_id uuid;
  _enrollment_id uuid;
  _total_content integer;
  _completed_content integer;
  _progress_percent integer;
  _course record;
  _existing_cert uuid;
BEGIN
  _user_id := NEW.user_id;

  -- Get the course_id from the content
  SELECT cm.course_id INTO _course_id
  FROM module_content mc
  JOIN course_modules cm ON cm.id = mc.module_id
  WHERE mc.id = NEW.content_id;

  IF _course_id IS NULL THEN
    RETURN NEW;
  END IF;

  -- Get the enrollment
  SELECT id INTO _enrollment_id
  FROM enrollments
  WHERE user_id = _user_id AND course_id = _course_id;

  IF _enrollment_id IS NULL THEN
    RETURN NEW;
  END IF;

  -- Count total content in the course
  SELECT COUNT(*) INTO _total_content
  FROM module_content mc
  JOIN course_modules cm ON cm.id = mc.module_id
  WHERE cm.course_id = _course_id;

  -- Count completed content
  SELECT COUNT(*) INTO _completed_content
  FROM content_progress cp
  JOIN module_content mc ON mc.id = cp.content_id
  JOIN course_modules cm ON cm.id = mc.module_id
  WHERE cm.course_id = _course_id
  AND cp.user_id = _user_id
  AND cp.status = 'completed';

  -- Calculate progress percentage
  IF _total_content > 0 THEN
    _progress_percent := ROUND((_completed_content::numeric / _total_content::numeric) * 100);
  ELSE
    _progress_percent := 0;
  END IF;

  -- Update enrollment progress
  IF _progress_percent >= 100 THEN
    -- Mark as completed
    UPDATE enrollments
    SET progress_percentage = 100,
        status = 'completed',
        completed_at = COALESCE(completed_at, now())
    WHERE id = _enrollment_id
    AND status != 'completed';

    -- Get course details for certificate
    SELECT * INTO _course FROM courses WHERE id = _course_id;

    -- Check if certificate should be issued
    IF _course.enable_certificates = true THEN
      -- Check if certificate already exists
      SELECT id INTO _existing_cert
      FROM issued_certificates
      WHERE user_id = _user_id AND enrollment_id = _enrollment_id;

      IF _existing_cert IS NULL THEN
        -- Get user details and issue certificate
        INSERT INTO issued_certificates (
          user_id,
          enrollment_id,
          course_id,
          course_title,
          learner_name,
          learner_email,
          completion_date,
          certificate_number,
          verification_code,
          template_id
        )
        SELECT 
          _user_id,
          _enrollment_id,
          _course_id,
          _course.title,
          COALESCE(p.full_name, 'Learner'),
          COALESCE(p.email, 'unknown@email.com'),
          now(),
          'CERT-' || UPPER(SUBSTRING(md5(random()::text), 1, 8)),
          UPPER(SUBSTRING(md5(random()::text || now()::text), 1, 12)),
          _course.certificate_template_id
        FROM profiles p
        WHERE p.id = _user_id;
      END IF;
    END IF;
  ELSE
    -- Just update progress
    UPDATE enrollments
    SET progress_percentage = _progress_percent
    WHERE id = _enrollment_id
    AND status != 'completed';
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public.sync_enrollment_progress() OWNER TO postgres;

--
-- TOC entry 473 (class 1255 OID 48555)
-- Name: sync_question_usage_on_content_change(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.sync_question_usage_on_content_change() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    SET search_path TO 'public'
    AS $$
DECLARE
  old_question_ids uuid[];
  new_question_ids uuid[];
  qid uuid;
BEGIN
  -- Only process quiz content type
  IF NEW.content_type != 'quiz' THEN
    RETURN NEW;
  END IF;

  -- Extract question IDs from old and new content_data
  IF TG_OP = 'UPDATE' AND OLD.content_data IS NOT NULL THEN
    SELECT ARRAY(
      SELECT (jsonb_array_elements_text(OLD.content_data->'question_ids'))::uuid
    ) INTO old_question_ids;
  ELSE
    old_question_ids := ARRAY[]::uuid[];
  END IF;

  IF NEW.content_data IS NOT NULL AND NEW.content_data ? 'question_ids' THEN
    SELECT ARRAY(
      SELECT (jsonb_array_elements_text(NEW.content_data->'question_ids'))::uuid
    ) INTO new_question_ids;
  ELSE
    new_question_ids := ARRAY[]::uuid[];
  END IF;

  -- Decrement count for removed questions
  IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN
    FOREACH qid IN ARRAY old_question_ids
    LOOP
      IF NOT (qid = ANY(new_question_ids)) THEN
        UPDATE question_bank SET usage_count = GREATEST(0, usage_count - 1) WHERE id = qid;
      END IF;
    END LOOP;
  END IF;

  -- Increment count for added questions
  IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
    FOREACH qid IN ARRAY new_question_ids
    LOOP
      IF NOT (qid = ANY(old_question_ids)) THEN
        UPDATE question_bank SET usage_count = usage_count + 1 WHERE id = qid;
      END IF;
    END LOOP;
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public.sync_question_usage_on_content_change() OWNER TO postgres;

--
-- TOC entry 395 (class 1255 OID 48556)
-- Name: track_competency_history(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.track_competency_history() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    SET search_path TO 'public'
    AS $$
BEGIN
  -- Only record if level actually changed
  IF TG_OP = 'UPDATE' AND OLD.current_level != NEW.current_level THEN
    INSERT INTO public.competency_history (user_id, competency_id, level_before, level_after, changed_by)
    VALUES (NEW.user_id, NEW.competency_id, OLD.current_level, NEW.current_level, auth.uid());
  ELSIF TG_OP = 'INSERT' AND NEW.current_level > 0 THEN
    INSERT INTO public.competency_history (user_id, competency_id, level_before, level_after, changed_by)
    VALUES (NEW.user_id, NEW.competency_id, 0, NEW.current_level, auth.uid());
  END IF;
  RETURN NEW;
END;
$$;


ALTER FUNCTION public.track_competency_history() OWNER TO postgres;

--
-- TOC entry 466 (class 1255 OID 48557)
-- Name: update_competency_on_assessment_pass(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.update_competency_on_assessment_pass() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    SET search_path TO 'public'
    AS $$
DECLARE
  _assessment RECORD;
BEGIN
  -- Only process if attempt was just marked as passed
  IF NEW.passed = true AND (OLD IS NULL OR OLD.passed = false) THEN
    SELECT * INTO _assessment FROM public.competency_assessments WHERE id = NEW.assessment_id;

    IF _assessment IS NOT NULL THEN
      -- Upsert competency progress
      INSERT INTO public.learner_competency_progress (user_id, competency_id, current_level, assessed_at, assessed_by, notes)
      VALUES (NEW.user_id, _assessment.competency_id, _assessment.level_awarded, now(), NULL, 'Passed assessment: ' || _assessment.title)
      ON CONFLICT (user_id, competency_id) DO UPDATE SET
        current_level = GREATEST(learner_competency_progress.current_level, _assessment.level_awarded),
        assessed_at = now(),
        notes = 'Passed assessment: ' || _assessment.title,
        updated_at = now();
    END IF;
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public.update_competency_on_assessment_pass() OWNER TO postgres;

--
-- TOC entry 474 (class 1255 OID 48558)
-- Name: update_competency_on_course_completion(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.update_competency_on_course_completion() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    SET search_path TO 'public'
    AS $$
DECLARE
  _competency RECORD;
BEGIN
  -- Only process if status changed to completed
  IF NEW.status = 'completed' AND (OLD IS NULL OR OLD.status != 'completed') THEN
    -- Get all competencies linked to this course
    FOR _competency IN 
      SELECT c.id, c.name, cc.competency_id
      FROM public.course_competencies cc
      JOIN public.competencies c ON c.id = cc.competency_id
      WHERE cc.course_id = NEW.course_id
    LOOP
      -- Increase competency level by 1 (max 5)
      INSERT INTO public.learner_competency_progress (user_id, competency_id, current_level, assessed_at, notes)
      VALUES (NEW.user_id, _competency.competency_id, 1, now(), 'Completed course with linked competency')
      ON CONFLICT (user_id, competency_id) DO UPDATE SET
        current_level = LEAST(learner_competency_progress.current_level + 1, 5),
        assessed_at = now(),
        notes = 'Completed course with linked competency',
        updated_at = now();
    END LOOP;
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public.update_competency_on_course_completion() OWNER TO postgres;

--
-- TOC entry 446 (class 1255 OID 48559)
-- Name: update_learner_competency_score(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.update_learner_competency_score() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    SET search_path TO 'public'
    AS $$
DECLARE
  _competency_id uuid;
  _score_result RECORD;
BEGIN
  -- Get competency for this assessment
  SELECT competency_id INTO _competency_id
  FROM public.competency_assessments
  WHERE id = NEW.assessment_id;

  IF _competency_id IS NOT NULL AND NEW.completed_at IS NOT NULL THEN
    -- Calculate new weighted score
    SELECT * INTO _score_result
    FROM public.calculate_competency_score(NEW.user_id, _competency_id);

    -- Upsert learner competency scores
    INSERT INTO public.learner_competency_scores (
      user_id, competency_id, weighted_score, total_assessments, 
      last_assessment_at, calculated_level
    ) VALUES (
      NEW.user_id, _competency_id, _score_result.weighted_score, 
      _score_result.total_assessments, NEW.completed_at, _score_result.calculated_level
    )
    ON CONFLICT (user_id, competency_id) DO UPDATE SET
      weighted_score = _score_result.weighted_score,
      total_assessments = _score_result.total_assessments,
      last_assessment_at = NEW.completed_at,
      calculated_level = _score_result.calculated_level,
      updated_at = now();

    -- Also update learner_competency_progress
    INSERT INTO public.learner_competency_progress (
      user_id, competency_id, current_level, assessed_at, notes
    ) VALUES (
      NEW.user_id, _competency_id, _score_result.calculated_level, now(),
      'Auto-calculated from assessment score: ' || ROUND(_score_result.weighted_score, 1) || '%'
    )
    ON CONFLICT (user_id, competency_id) DO UPDATE SET
      current_level = GREATEST(learner_competency_progress.current_level, _score_result.calculated_level),
      assessed_at = now(),
      notes = 'Auto-calculated from assessment score: ' || ROUND(_score_result.weighted_score, 1) || '%',
      updated_at = now();
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public.update_learner_competency_score() OWNER TO postgres;

--
-- TOC entry 424 (class 1255 OID 48560)
-- Name: update_poll_results_cache(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.update_poll_results_cache() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    SET search_path TO 'public'
    AS $$
DECLARE
  _option_counts JSONB;
  _total INTEGER;
BEGIN
  -- Calculate new counts
  SELECT 
    jsonb_object_agg(option_key, option_count),
    COUNT(*)
  INTO _option_counts, _total
  FROM (
    SELECT 
      jsonb_array_elements_text(selected_options) as option_key,
      COUNT(*) as option_count
    FROM public.poll_responses
    WHERE session_id = NEW.session_id AND question_id = NEW.question_id
    GROUP BY jsonb_array_elements_text(selected_options)
  ) counts;

  -- Upsert cache
  INSERT INTO public.poll_results_cache (session_id, question_id, option_counts, total_responses)
  VALUES (NEW.session_id, NEW.question_id, COALESCE(_option_counts, '{}'::jsonb), _total)
  ON CONFLICT (session_id, question_id) DO UPDATE SET
    option_counts = COALESCE(_option_counts, '{}'::jsonb),
    total_responses = _total,
    updated_at = now();

  RETURN NEW;
END;
$$;


ALTER FUNCTION public.update_poll_results_cache() OWNER TO postgres;

--
-- TOC entry 417 (class 1255 OID 48561)
-- Name: update_question_analytics(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.update_question_analytics() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    SET search_path TO 'public'
    AS $$
DECLARE
  _answers jsonb;
  _question RECORD;
  _is_correct boolean;
BEGIN
  _answers := NEW.answers;

  -- Process each answer
  FOR _question IN 
    SELECT q.id, q.correct_answer 
    FROM public.competency_assessment_questions q
    WHERE q.assessment_id = NEW.assessment_id
  LOOP
    _is_correct := (_answers->>(_question.id::text)) = _question.correct_answer;

    INSERT INTO public.question_analytics (question_id, question_source, assessment_id, total_attempts, correct_attempts)
    VALUES (_question.id, 'inline', NEW.assessment_id, 1, CASE WHEN _is_correct THEN 1 ELSE 0 END)
    ON CONFLICT (question_id, assessment_id) 
    DO UPDATE SET
      total_attempts = question_analytics.total_attempts + 1,
      correct_attempts = question_analytics.correct_attempts + CASE WHEN _is_correct THEN 1 ELSE 0 END,
      updated_at = now();
  END LOOP;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public.update_question_analytics() OWNER TO postgres;

--
-- TOC entry 463 (class 1255 OID 48562)
-- Name: update_question_bank_usage_count(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.update_question_bank_usage_count() RETURNS void
    LANGUAGE plpgsql SECURITY DEFINER
    SET search_path TO 'public'
    AS $$
DECLARE
  question_id uuid;
  question_count int;
BEGIN
  -- Reset all usage counts first
  UPDATE question_bank SET usage_count = 0;

  -- For each question, count how many times it's referenced in module_content quizzes
  FOR question_id IN (SELECT id FROM question_bank)
  LOOP
    SELECT COUNT(*) INTO question_count
    FROM module_content
    WHERE content_type = 'quiz'
      AND content_data IS NOT NULL
      AND content_data::text LIKE '%' || question_id::text || '%';

    UPDATE question_bank 
    SET usage_count = question_count 
    WHERE id = question_id;
  END LOOP;

  -- Also count references in assessment_question_links
  UPDATE question_bank qb
  SET usage_count = usage_count + (
    SELECT COUNT(*) 
    FROM assessment_question_links aql 
    WHERE aql.question_bank_id = qb.id
  );
END;
$$;


ALTER FUNCTION public.update_question_bank_usage_count() OWNER TO postgres;

--
-- TOC entry 433 (class 1255 OID 48563)
-- Name: update_trainer_rating(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.update_trainer_rating() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    SET search_path TO 'public'
    AS $$
BEGIN
    INSERT INTO public.trainer_ratings (trainer_id, average_rating, total_reviews, outstanding_count, good_count, average_count, bad_count, miserable_count, last_updated_at)
    SELECT 
        s.trainer_id,
        COALESCE(AVG(r.rating)::decimal(3,2), 0),
        COUNT(r.id),
        COUNT(CASE WHEN r.rating = 5 THEN 1 END),
        COUNT(CASE WHEN r.rating = 4 THEN 1 END),
        COUNT(CASE WHEN r.rating = 3 THEN 1 END),
        COUNT(CASE WHEN r.rating = 2 THEN 1 END),
        COUNT(CASE WHEN r.rating = 1 THEN 1 END),
        now()
    FROM public.trainer_feedback_surveys s
    LEFT JOIN public.trainer_feedback_responses r ON r.survey_id = s.id
    WHERE s.id = NEW.survey_id
    GROUP BY s.trainer_id
    ON CONFLICT (trainer_id)
    DO UPDATE SET
        average_rating = EXCLUDED.average_rating,
        total_reviews = EXCLUDED.total_reviews,
        outstanding_count = EXCLUDED.outstanding_count,
        good_count = EXCLUDED.good_count,
        average_count = EXCLUDED.average_count,
        bad_count = EXCLUDED.bad_count,
        miserable_count = EXCLUDED.miserable_count,
        last_updated_at = now();

    RETURN NEW;
END;
$$;


ALTER FUNCTION public.update_trainer_rating() OWNER TO postgres;

--
-- TOC entry 412 (class 1255 OID 48564)
-- Name: update_updated_at_column(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.update_updated_at_column() RETURNS trigger
    LANGUAGE plpgsql
    SET search_path TO 'public'
    AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$;


ALTER FUNCTION public.update_updated_at_column() OWNER TO postgres;

--
-- TOC entry 467 (class 1255 OID 48565)
-- Name: user_is_group_member(uuid, uuid); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.user_is_group_member(_user_id uuid, _group_id uuid) RETURNS boolean
    LANGUAGE sql STABLE SECURITY DEFINER
    SET search_path TO 'public'
    AS $$
  SELECT EXISTS (
    SELECT 1 FROM public.assignment_group_members
    WHERE learner_id = _user_id 
    AND group_id = _group_id
  )
$$;


ALTER FUNCTION public.user_is_group_member(_user_id uuid, _group_id uuid) OWNER TO postgres;

--
-- TOC entry 372 (class 1255 OID 48566)
-- Name: verify_certificate_public(text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.verify_certificate_public(_verification_code text) RETURNS TABLE(is_valid boolean, certificate_number text, learner_name text, course_title text, completion_date timestamp with time zone, issued_at timestamp with time zone, is_revoked boolean)
    LANGUAGE plpgsql SECURITY DEFINER
    SET search_path TO 'public'
    AS $$
BEGIN
  -- Validate input format (basic sanitization)
  IF _verification_code IS NULL OR length(_verification_code) < 8 THEN
    RETURN QUERY SELECT 
      false::boolean,
      NULL::text,
      NULL::text,
      NULL::text,
      NULL::timestamp with time zone,
      NULL::timestamp with time zone,
      NULL::boolean;
    RETURN;
  END IF;

  RETURN QUERY
  SELECT 
    true::boolean as is_valid,
    ic.certificate_number,
    ic.learner_name,
    ic.course_title,
    ic.completion_date,
    ic.issued_at,
    COALESCE(ic.is_revoked, false) as is_revoked
  FROM public.issued_certificates ic
  WHERE ic.verification_code = _verification_code
  LIMIT 1;

  -- If no rows returned, return invalid result
  IF NOT FOUND THEN
    RETURN QUERY SELECT 
      false::boolean,
      NULL::text,
      NULL::text,
      NULL::text,
      NULL::timestamp with time zone,
      NULL::timestamp with time zone,
      NULL::boolean;
  END IF;
END;
$$;


ALTER FUNCTION public.verify_certificate_public(_verification_code text) OWNER TO postgres;

--
-- TOC entry 450 (class 1255 OID 48567)
-- Name: version_competency_on_change(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.version_competency_on_change() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    SET search_path TO 'public'
    AS $$
DECLARE
  _next_version INTEGER;
BEGIN
  IF TG_OP = 'UPDATE' AND (OLD.name != NEW.name OR OLD.description != NEW.description OR OLD.level != NEW.level) THEN
    -- Get next version number
    SELECT COALESCE(MAX(version_number), 0) + 1 INTO _next_version
    FROM public.competency_versions
    WHERE competency_id = NEW.id;

    -- Close the current version
    UPDATE public.competency_versions
    SET effective_until = now(), is_active = false
    WHERE competency_id = NEW.id AND is_active = true;

    -- Create new version
    INSERT INTO public.competency_versions (
      competency_id, version_number, name, description, level,
      proficiency_scale_id, changed_by, change_reason
    ) VALUES (
      NEW.id, _next_version, NEW.name, NEW.description, NEW.level,
      NEW.proficiency_scale_id, auth.uid(), 'Updated via admin interface'
    );
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public.version_competency_on_change() OWNER TO postgres;



--
-- TOC entry 221 (class 1259 OID 48568)
-- Name: account_deletion_requests; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.account_deletion_requests (
    id character varying(255) DEFAULT gen_random_uuid() NOT NULL,
    user_id character varying(255) NOT NULL,
    status text DEFAULT 'pending'::text NOT NULL,
    reason text,
    requested_at timestamp with time zone DEFAULT now() NOT NULL,
    reviewed_by character varying(255),
    reviewed_at timestamp with time zone,
    completed_at timestamp with time zone,
    notes text,
    CONSTRAINT account_deletion_requests_status_check CHECK ((status = ANY (ARRAY['pending'::text, 'approved'::text, 'rejected'::text, 'completed'::text])))
);


ALTER TABLE public.account_deletion_requests OWNER TO postgres;

--
-- TOC entry 222 (class 1259 OID 48577)
-- Name: ai_content_tags; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.ai_content_tags (
    id character varying(255) DEFAULT gen_random_uuid() NOT NULL,
    content_type text NOT NULL,
    content_id character varying(255) NOT NULL,
    tag_id uuid,
    tag_name text NOT NULL,
    confidence_score numeric(38,2) DEFAULT 0,
    source text DEFAULT 'ai'::text,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    verified_by character varying(255),
    verified_at timestamp with time zone,
    updated_at timestamp(6) with time zone
);


ALTER TABLE public.ai_content_tags OWNER TO postgres;

--
-- TOC entry 223 (class 1259 OID 48586)
-- Name: ai_learning_recommendations; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.ai_learning_recommendations (
    id character varying(255) DEFAULT gen_random_uuid() NOT NULL,
    user_id character varying(255) NOT NULL,
    recommendation_type text NOT NULL,
    target_id character varying(255) NOT NULL,
    target_title text,
    reason text,
    priority_score numeric(38,2) DEFAULT 0,
    context character varying(255) DEFAULT '{}'::jsonb,
    is_dismissed boolean DEFAULT false,
    is_acted_upon boolean DEFAULT false,
    expires_at timestamp with time zone,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp(6) with time zone
);


ALTER TABLE public.ai_learning_recommendations OWNER TO postgres;

--
-- TOC entry 224 (class 1259 OID 48597)
-- Name: assessment_assignment_learners; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.assessment_assignment_learners (
    id character varying(255) DEFAULT gen_random_uuid() NOT NULL,
    assignment_id uuid NOT NULL,
    learner_id character varying(255) NOT NULL,
    status text DEFAULT 'pending'::text NOT NULL,
    attempts_used integer DEFAULT 0,
    best_score integer,
    completed_at timestamp with time zone,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp(6) with time zone
);


ALTER TABLE public.assessment_assignment_learners OWNER TO postgres;

--
-- TOC entry 225 (class 1259 OID 48606)
-- Name: assessment_assignments; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.assessment_assignments (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    assessment_id uuid NOT NULL,
    assigned_by uuid,
    assignment_type text DEFAULT 'individual'::text NOT NULL,
    target_ids character varying(255) DEFAULT '{}'::uuid[],
    due_date timestamp with time zone,
    start_date timestamp with time zone DEFAULT now(),
    max_attempts integer DEFAULT 3,
    is_mandatory boolean DEFAULT false,
    notification_sent boolean DEFAULT false,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL
);


ALTER TABLE public.assessment_assignments OWNER TO postgres;

--
-- TOC entry 226 (class 1259 OID 48620)
-- Name: assessment_calibration; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.assessment_calibration (
    id character varying(255) DEFAULT gen_random_uuid() NOT NULL,
    assessment_id uuid,
    examiner_id character varying(255),
    calibration_factor numeric(38,2) DEFAULT 1.000,
    difficulty_adjustment integer DEFAULT 0,
    sample_size integer DEFAULT 0,
    mean_score numeric(38,2),
    std_deviation numeric(38,2),
    last_calibrated_at timestamp with time zone,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL
);


ALTER TABLE public.assessment_calibration OWNER TO postgres;

--
-- TOC entry 227 (class 1259 OID 48629)
-- Name: assessment_question_links; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.assessment_question_links (
    id character varying(255) DEFAULT gen_random_uuid() NOT NULL,
    assessment_id uuid NOT NULL,
    question_bank_id uuid,
    inline_question_id uuid,
    order_index integer DEFAULT 0 NOT NULL,
    is_required boolean DEFAULT true,
    points_override integer,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp(6) with time zone,
    CONSTRAINT one_question_source CHECK ((((question_bank_id IS NOT NULL) AND (inline_question_id IS NULL)) OR ((question_bank_id IS NULL) AND (inline_question_id IS NOT NULL))))
);


ALTER TABLE public.assessment_question_links OWNER TO postgres;

--
-- TOC entry 228 (class 1259 OID 48637)
-- Name: assignment_group_members; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.assignment_group_members (
    id character varying(255) DEFAULT gen_random_uuid() NOT NULL,
    group_id uuid NOT NULL,
    learner_id character varying(255) NOT NULL,
    role text DEFAULT 'member'::text,
    joined_at timestamp with time zone DEFAULT now()
);


ALTER TABLE public.assignment_group_members OWNER TO postgres;

--
-- TOC entry 229 (class 1259 OID 48645)
-- Name: assignment_groups; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.assignment_groups (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    assignment_id uuid NOT NULL,
    group_name text NOT NULL,
    created_at timestamp with time zone DEFAULT now(),
    created_by character varying(255),
    updated_at timestamp(6) with time zone
);


ALTER TABLE public.assignment_groups OWNER TO postgres;

--
-- TOC entry 230 (class 1259 OID 48652)
-- Name: assignment_learners; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.assignment_learners (
    id character varying(255) DEFAULT gen_random_uuid() NOT NULL,
    assignment_id uuid NOT NULL,
    learner_id character varying(255) NOT NULL,
    assigned_at timestamp with time zone DEFAULT now() NOT NULL,
    assigned_by character varying(255)
);


ALTER TABLE public.assignment_learners OWNER TO postgres;

--
-- TOC entry 231 (class 1259 OID 48657)
-- Name: assignment_submission_history; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.assignment_submission_history (
    id character varying(255) DEFAULT gen_random_uuid() NOT NULL,
    submission_id uuid NOT NULL,
    action text NOT NULL,
    action_by character varying(255) NOT NULL,
    action_at timestamp with time zone DEFAULT now() NOT NULL,
    notes text,
    previous_status text,
    new_status text,
    submission_snapshot character varying(255)
);


ALTER TABLE public.assignment_submission_history OWNER TO postgres;

--
-- TOC entry 232 (class 1259 OID 48664)
-- Name: assignment_submissions; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.assignment_submissions (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    assignment_id uuid,
    student_id uuid NOT NULL,
    submission_url text,
    submission_text text,
    score integer,
    feedback text,
    submitted_at timestamp with time zone DEFAULT now(),
    graded_at timestamp with time zone,
    graded_by uuid,
    file_url text,
    file_name text,
    points_awarded integer DEFAULT 0,
    group_id uuid,
    is_group_submission boolean DEFAULT false,
    status text DEFAULT 'submitted'::text,
    rejection_reason text,
    submission_version integer DEFAULT 1,
    batch_assignment_id uuid
);

ALTER TABLE ONLY public.assignment_submissions FORCE ROW LEVEL SECURITY;


ALTER TABLE public.assignment_submissions OWNER TO postgres;

--
-- TOC entry 233 (class 1259 OID 48675)
-- Name: assignments; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.assignments (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    title text NOT NULL,
    description text,
    batch_id uuid,
    created_by uuid,
    due_date timestamp with time zone,
    max_score integer DEFAULT 100,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp(6) with time zone
);


ALTER TABLE public.assignments OWNER TO postgres;

--
-- TOC entry 234 (class 1259 OID 48683)
-- Name: attendance; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.attendance (
    id character varying(255) DEFAULT gen_random_uuid() NOT NULL,
    batch_id uuid NOT NULL,
    student_id uuid NOT NULL,
    session_date character varying(255) NOT NULL,
    status text DEFAULT 'present'::text,
    notes text,
    marked_by uuid,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    attentiveness_score integer,
    updated_at timestamp(6) with time zone,
    CONSTRAINT attendance_attentiveness_score_check CHECK (((attentiveness_score >= 1) AND (attentiveness_score <= 10)))
);


ALTER TABLE public.attendance OWNER TO postgres;

--
-- TOC entry 235 (class 1259 OID 48692)
-- Name: audit_logs; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.audit_logs (
    id character varying(255) DEFAULT gen_random_uuid() NOT NULL,
    user_id character varying(255),
    action_type text NOT NULL,
    entity_type text NOT NULL,
    entity_id character varying(255),
    old_values character varying(255),
    new_values character varying(255),
    ip_address text,
    user_agent text,
    session_id text,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp(6) with time zone
);

ALTER TABLE ONLY public.audit_logs FORCE ROW LEVEL SECURITY;


ALTER TABLE public.audit_logs OWNER TO postgres;

--
-- TOC entry 5840 (class 0 OID 0)
-- Dependencies: 235
-- Name: TABLE audit_logs; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON TABLE public.audit_logs IS 'Audit logs table. INSERT operations are restricted to the log_audit_event() SECURITY DEFINER function only. Direct inserts are blocked by RLS.';


--
-- TOC entry 236 (class 1259 OID 48699)
-- Name: auto_assignment_rules; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.auto_assignment_rules (
    id character varying(255) DEFAULT gen_random_uuid() NOT NULL,
    name text NOT NULL,
    description text,
    rule_type text NOT NULL,
    trigger_conditions character varying(255) DEFAULT '{}'::jsonb NOT NULL,
    action_type text NOT NULL,
    action_target_id character varying(255),
    priority integer DEFAULT 0,
    is_active boolean DEFAULT true,
    created_by character varying(255),
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL
);


ALTER TABLE public.auto_assignment_rules OWNER TO postgres;

--
-- TOC entry 237 (class 1259 OID 48710)
-- Name: badge_definitions; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.badge_definitions (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    name text NOT NULL,
    description text,
    icon text DEFAULT '🏆'::text,
    image_url text,
    rarity text DEFAULT 'common'::text,
    criteria_type text DEFAULT 'automatic'::text,
    criteria text DEFAULT '{}'::jsonb NOT NULL,
    points_reward integer DEFAULT 0,
    is_active boolean DEFAULT true,
    created_by uuid,
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp(6) with time zone,
    CONSTRAINT badge_definitions_criteria_type_check CHECK ((criteria_type = ANY (ARRAY['automatic'::text, 'admin_defined'::text]))),
    CONSTRAINT badge_definitions_rarity_check CHECK ((rarity = ANY (ARRAY['common'::text, 'rare'::text, 'epic'::text, 'legendary'::text])))
);


ALTER TABLE public.badge_definitions OWNER TO postgres;

--
-- TOC entry 238 (class 1259 OID 48725)
-- Name: batch_assignments; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.batch_assignments (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    batch_id uuid NOT NULL,
    module_content_id uuid,
    title text NOT NULL,
    description text,
    start_date character varying(255),
    due_date character varying(255),
    max_score integer DEFAULT 100,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    created_by character varying(255),
    points_on_time integer DEFAULT 0,
    points_early_submission integer DEFAULT 0,
    points_full_marks integer DEFAULT 0,
    is_gradeable boolean DEFAULT true,
    grading_scale text DEFAULT 'percentage'::text,
    passing_score integer DEFAULT 60,
    assignment_type text DEFAULT 'general'::text,
    media_url text,
    media_type text,
    instructions text,
    assign_to_all boolean DEFAULT true,
    is_group_assignment boolean DEFAULT false,
    min_group_size integer DEFAULT 2,
    max_group_size integer DEFAULT 5,
    updated_at timestamp(6) with time zone
);


ALTER TABLE public.batch_assignments OWNER TO postgres;

--
-- TOC entry 5841 (class 0 OID 0)
-- Dependencies: 238
-- Name: COLUMN batch_assignments.points_on_time; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.batch_assignments.points_on_time IS 'Points awarded for completing assignment before due date';


--
-- TOC entry 5842 (class 0 OID 0)
-- Dependencies: 238
-- Name: COLUMN batch_assignments.points_early_submission; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.batch_assignments.points_early_submission IS 'Points awarded for completing within 24 hours of start date';


--
-- TOC entry 5843 (class 0 OID 0)
-- Dependencies: 238
-- Name: COLUMN batch_assignments.points_full_marks; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.batch_assignments.points_full_marks IS 'Points awarded for achieving full marks';


--
-- TOC entry 239 (class 1259 OID 48744)
-- Name: batch_lesson_plans; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.batch_lesson_plans (
    id character varying(255) DEFAULT gen_random_uuid() NOT NULL,
    batch_id uuid NOT NULL,
    module_content_id uuid,
    sequence_order integer DEFAULT 0 NOT NULL,
    title text NOT NULL,
    notes text,
    status text DEFAULT 'pending'::text,
    completed_at timestamp with time zone,
    completed_by character varying(255),
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    planned_duration_minutes integer DEFAULT 0,
    updated_at timestamp(6) with time zone
);


ALTER TABLE public.batch_lesson_plans OWNER TO postgres;

--
-- TOC entry 240 (class 1259 OID 48754)
-- Name: batch_mentors; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.batch_mentors (
    id character varying(255) DEFAULT gen_random_uuid() NOT NULL,
    batch_id uuid NOT NULL,
    mentor_id character varying(255) NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    created_by character varying(255),
    updated_at timestamp(6) with time zone
);


ALTER TABLE public.batch_mentors OWNER TO postgres;

--
-- TOC entry 241 (class 1259 OID 48759)
-- Name: batch_schedule; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.batch_schedule (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    batch_id uuid NOT NULL,
    session_date character varying(255) NOT NULL,
    start_time time without time zone,
    end_time time without time zone,
    topic text,
    description text,
    recurrence_type text DEFAULT 'manual'::text,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    created_by character varying(255),
    meeting_link text,
    meeting_id text,
    meeting_provider text,
    meeting_integration_id uuid,
    updated_at timestamp(6) with time zone
);


ALTER TABLE public.batch_schedule OWNER TO postgres;

--
-- TOC entry 242 (class 1259 OID 48767)
-- Name: batch_students; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.batch_students (
    id character varying(255) DEFAULT gen_random_uuid() NOT NULL,
    batch_id uuid NOT NULL,
    student_id uuid NOT NULL,
    enrolled_at timestamp with time zone DEFAULT now() NOT NULL
);


ALTER TABLE public.batch_students OWNER TO postgres;

--
-- TOC entry 243 (class 1259 OID 48772)
-- Name: batch_trainers; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.batch_trainers (
    id character varying(255) DEFAULT gen_random_uuid() NOT NULL,
    batch_id uuid NOT NULL,
    trainer_id uuid NOT NULL,
    is_lead boolean DEFAULT false,
    role text DEFAULT 'primary'::text,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp(6) with time zone
);


ALTER TABLE public.batch_trainers OWNER TO postgres;

--
-- TOC entry 244 (class 1259 OID 48781)
-- Name: batches; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.batches (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    name text NOT NULL,
    description text,
    course_id uuid,
    trainer_id uuid,
    start_date character varying(255),
    end_date character varying(255),
    max_students integer,
    created_by uuid,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    batch_code text,
    updated_at timestamp(6) with time zone
);


ALTER TABLE public.batches OWNER TO postgres;

--
-- TOC entry 245 (class 1259 OID 48788)
-- Name: branding_settings; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.branding_settings (
    id character varying(255) DEFAULT gen_random_uuid() NOT NULL,
    setting_key text NOT NULL,
    setting_value character varying(255) DEFAULT '{}'::jsonb NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL
);


ALTER TABLE public.branding_settings OWNER TO postgres;

--
-- TOC entry 246 (class 1259 OID 48797)
-- Name: breakout_room_participants; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.breakout_room_participants (
    id character varying(255) DEFAULT gen_random_uuid() NOT NULL,
    room_id uuid,
    user_id character varying(255) NOT NULL,
    joined_at timestamp with time zone DEFAULT now(),
    left_at timestamp with time zone,
    role text DEFAULT 'participant'::text
);


ALTER TABLE public.breakout_room_participants OWNER TO postgres;

--
-- TOC entry 247 (class 1259 OID 48805)
-- Name: breakout_rooms; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.breakout_rooms (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    session_id uuid,
    room_name text NOT NULL,
    room_topic text,
    max_participants integer DEFAULT 10,
    duration_minutes integer DEFAULT 15,
    status text DEFAULT 'pending'::text,
    started_at timestamp with time zone,
    ended_at timestamp with time zone,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp(6) with time zone
);


ALTER TABLE public.breakout_rooms OWNER TO postgres;

--
-- TOC entry 248 (class 1259 OID 48815)
-- Name: bundle_courses; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.bundle_courses (
    id character varying(255) DEFAULT gen_random_uuid() NOT NULL,
    bundle_id uuid NOT NULL,
    course_id uuid NOT NULL,
    order_index integer DEFAULT 0
);


ALTER TABLE public.bundle_courses OWNER TO postgres;

--
-- TOC entry 249 (class 1259 OID 48820)
-- Name: bundle_enrollments; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.bundle_enrollments (
    id character varying(255) DEFAULT gen_random_uuid() NOT NULL,
    bundle_id uuid NOT NULL,
    user_id character varying(255) NOT NULL,
    enrolled_at timestamp with time zone DEFAULT now() NOT NULL,
    status text DEFAULT 'active'::text NOT NULL,
    completed_at timestamp with time zone
);


ALTER TABLE public.bundle_enrollments OWNER TO postgres;

--
-- TOC entry 250 (class 1259 OID 48828)
-- Name: certificate_templates; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.certificate_templates (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    name text NOT NULL,
    description text,
    template_data text DEFAULT '{}'::jsonb NOT NULL,
    background_url text,
    is_default boolean DEFAULT false,
    created_by uuid,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL
);


ALTER TABLE public.certificate_templates OWNER TO postgres;

--
-- TOC entry 251 (class 1259 OID 48838)
-- Name: competencies; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.competencies (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    name text NOT NULL,
    description text,
    level integer DEFAULT 1,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    proficiency_scale_id uuid,
    category text DEFAULT 'common'::text NOT NULL,
    department_id uuid,
    updated_at timestamp(6) with time zone,
    CONSTRAINT competencies_category_check CHECK ((category = ANY (ARRAY['common'::text, 'department_specific'::text])))
);


ALTER TABLE public.competencies OWNER TO postgres;

--
-- TOC entry 252 (class 1259 OID 48848)
-- Name: competency_assessment_attempts; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.competency_assessment_attempts (
    id character varying(255) DEFAULT gen_random_uuid() NOT NULL,
    assessment_id uuid NOT NULL,
    user_id character varying(255) NOT NULL,
    answers character varying(255) DEFAULT '{}'::jsonb NOT NULL,
    score integer,
    passed boolean DEFAULT false,
    started_at timestamp with time zone DEFAULT now() NOT NULL,
    completed_at timestamp with time zone,
    time_taken_seconds integer
);


ALTER TABLE public.competency_assessment_attempts OWNER TO postgres;

--
-- TOC entry 253 (class 1259 OID 48857)
-- Name: competency_assessment_questions; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.competency_assessment_questions (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    assessment_id uuid NOT NULL,
    question_text text NOT NULL,
    question_type text DEFAULT 'multiple_choice'::text NOT NULL,
    options character varying(255) DEFAULT '[]'::jsonb NOT NULL,
    correct_answer text NOT NULL,
    points integer DEFAULT 1 NOT NULL,
    order_index integer DEFAULT 0 NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    explanation text,
    difficulty text DEFAULT 'medium'::text,
    media_url text,
    media_type text,
    updated_at timestamp(6) with time zone
);


ALTER TABLE public.competency_assessment_questions OWNER TO postgres;

--
-- TOC entry 254 (class 1259 OID 48869)
-- Name: competency_assessments; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.competency_assessments (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    competency_id uuid NOT NULL,
    title text NOT NULL,
    description text,
    passing_score integer DEFAULT 70 NOT NULL,
    level_awarded integer DEFAULT 1 NOT NULL,
    time_limit_minutes integer,
    max_attempts integer DEFAULT 3,
    is_active boolean DEFAULT true,
    created_by character varying(255),
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    shuffle_questions boolean DEFAULT false,
    shuffle_options boolean DEFAULT false,
    questions_to_display integer,
    show_correct_answers boolean DEFAULT false,
    show_explanation boolean DEFAULT false,
    weight numeric(38,2) DEFAULT 1.0
);


ALTER TABLE public.competency_assessments OWNER TO postgres;

--
-- TOC entry 255 (class 1259 OID 48886)
-- Name: competency_history; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.competency_history (
    id character varying(255) DEFAULT gen_random_uuid() NOT NULL,
    user_id character varying(255) NOT NULL,
    competency_id uuid NOT NULL,
    level_before integer DEFAULT 0 NOT NULL,
    level_after integer NOT NULL,
    changed_at timestamp with time zone DEFAULT now() NOT NULL,
    changed_by character varying(255),
    notes text
);


ALTER TABLE public.competency_history OWNER TO postgres;

--
-- TOC entry 256 (class 1259 OID 48894)
-- Name: competency_role_mapping; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.competency_role_mapping (
    id character varying(255) DEFAULT gen_random_uuid() NOT NULL,
    competency_id uuid NOT NULL,
    organizational_role_id uuid NOT NULL,
    expected_level integer DEFAULT 0 NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL
);


ALTER TABLE public.competency_role_mapping OWNER TO postgres;

--
-- TOC entry 257 (class 1259 OID 48901)
-- Name: competency_validity_rules; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.competency_validity_rules (
    id character varying(255) DEFAULT gen_random_uuid() NOT NULL,
    competency_id uuid,
    job_family_id uuid,
    validity_period_months integer DEFAULT 12 NOT NULL,
    decay_rate_per_month numeric(38,2) DEFAULT 0,
    reassessment_required boolean DEFAULT true,
    auto_notify_days_before integer DEFAULT 30,
    is_active boolean DEFAULT true,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp(6) with time zone
);


ALTER TABLE public.competency_validity_rules OWNER TO postgres;

--
-- TOC entry 258 (class 1259 OID 48911)
-- Name: competency_verifications; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.competency_verifications (
    id character varying(255) DEFAULT gen_random_uuid() NOT NULL,
    learner_id character varying(255) NOT NULL,
    competency_id uuid NOT NULL,
    proposed_level integer NOT NULL,
    current_level integer DEFAULT 0,
    verification_type text DEFAULT 'assessment'::text NOT NULL,
    source_id character varying(255),
    verifier_id character varying(255),
    status text DEFAULT 'pending'::text NOT NULL,
    verifier_notes text,
    dispute_reason text,
    verified_at timestamp with time zone,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp(6) with time zone
);


ALTER TABLE public.competency_verifications OWNER TO postgres;

--
-- TOC entry 259 (class 1259 OID 48921)
-- Name: competency_versions; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.competency_versions (
    id character varying(255) DEFAULT gen_random_uuid() NOT NULL,
    competency_id uuid NOT NULL,
    version_number integer NOT NULL,
    name text NOT NULL,
    description text,
    level integer,
    proficiency_scale_id character varying(255),
    change_reason text,
    changed_by character varying(255),
    is_active boolean DEFAULT true,
    effective_from timestamp with time zone DEFAULT now(),
    effective_until timestamp with time zone,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp(6) with time zone
);


ALTER TABLE public.competency_versions OWNER TO postgres;

--
-- TOC entry 260 (class 1259 OID 48930)
-- Name: content_progress; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.content_progress (
    id character varying(255) DEFAULT gen_random_uuid() NOT NULL,
    user_id uuid NOT NULL,
    content_id uuid NOT NULL,
    status text DEFAULT 'not_started'::text,
    progress_percentage integer DEFAULT 0,
    score numeric(38,2),
    time_spent_seconds integer DEFAULT 0,
    scorm_data character varying(255),
    started_at timestamp with time zone,
    completed_at timestamp with time zone,
    last_accessed_at timestamp with time zone DEFAULT now()
);


ALTER TABLE public.content_progress OWNER TO postgres;

--
-- TOC entry 261 (class 1259 OID 48940)
-- Name: course_bundles; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.course_bundles (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    title text NOT NULL,
    description text,
    thumbnail_url text,
    created_by uuid,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    original_price numeric(38,2) DEFAULT 0,
    bundle_price numeric(38,2) DEFAULT 0,
    is_active boolean DEFAULT true NOT NULL,
    updated_at timestamp(6) with time zone
);


ALTER TABLE public.course_bundles OWNER TO postgres;

--
-- TOC entry 262 (class 1259 OID 48950)
-- Name: course_competencies; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.course_competencies (
    id character varying(255) DEFAULT gen_random_uuid() NOT NULL,
    course_id uuid NOT NULL,
    competency_id uuid NOT NULL,
    job_family_id uuid,
    target_level integer DEFAULT 1,
    CONSTRAINT course_competencies_target_level_check CHECK (((target_level >= 1) AND (target_level <= 5)))
);


ALTER TABLE public.course_competencies OWNER TO postgres;

--
-- TOC entry 263 (class 1259 OID 48956)
-- Name: course_modules; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.course_modules (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    course_id uuid NOT NULL,
    title text NOT NULL,
    description text,
    order_index integer DEFAULT 0 NOT NULL,
    is_preview boolean DEFAULT false,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp(6) with time zone
);


ALTER TABLE public.course_modules OWNER TO postgres;

--
-- TOC entry 264 (class 1259 OID 48965)
-- Name: course_trainers; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.course_trainers (
    id character varying(255) DEFAULT gen_random_uuid() NOT NULL,
    course_id uuid NOT NULL,
    trainer_id uuid NOT NULL,
    module_id uuid,
    is_lead boolean DEFAULT false,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp(6) with time zone
);


ALTER TABLE public.course_trainers OWNER TO postgres;

--
-- TOC entry 265 (class 1259 OID 48971)
-- Name: course_waitlist; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.course_waitlist (
    id character varying(255) DEFAULT gen_random_uuid() NOT NULL,
    course_id uuid NOT NULL,
    batch_id uuid,
    learner_id character varying(255) NOT NULL,
    nominated_by character varying(255),
    status text DEFAULT 'waiting'::text NOT NULL,
    priority integer DEFAULT 0,
    notes text,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    enrolled_at timestamp with time zone,
    updated_at timestamp(6) with time zone
);


ALTER TABLE public.course_waitlist OWNER TO postgres;

--
-- TOC entry 266 (class 1259 OID 48980)
-- Name: courses; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.courses (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    title text NOT NULL,
    description text,
    thumbnail_url text,
    duration_minutes integer DEFAULT 0,
    difficulty text DEFAULT 'beginner'::text,
    is_published boolean DEFAULT false,
    created_by uuid,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    preview_video_url text,
    image_position character varying(255) DEFAULT '{"x": 50, "y": 50}'::jsonb,
    certificate_template_id uuid,
    short_description text,
    long_description text,
    delivery_mode text DEFAULT 'online'::text,
    duration_value integer DEFAULT 0,
    duration_unit text DEFAULT 'hours'::text,
    tags text[] DEFAULT '{}'::text[],
    price numeric(38,2) DEFAULT 0,
    discount_percentage numeric(38,2) DEFAULT 0,
    is_mandatory boolean DEFAULT false,
    allow_self_enrollment boolean DEFAULT true,
    require_manager_approval boolean DEFAULT false,
    enable_certificates boolean DEFAULT true,
    category text,
    subcategory text,
    completion_min_attendance_percent integer DEFAULT 80,
    completion_require_all_assignments boolean DEFAULT true,
    completion_require_curriculum boolean DEFAULT true,
    completion_require_trainer_approval boolean DEFAULT true,
    compliance_deadline character varying(255),
    mandatory_department_ids uuid[] DEFAULT '{}'::uuid[]
);


ALTER TABLE public.courses OWNER TO postgres;

--
-- TOC entry 5844 (class 0 OID 0)
-- Dependencies: 266
-- Name: COLUMN courses.completion_min_attendance_percent; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.courses.completion_min_attendance_percent IS 'Minimum attendance percentage required for course completion (0-100)';


--
-- TOC entry 5845 (class 0 OID 0)
-- Dependencies: 266
-- Name: COLUMN courses.completion_require_all_assignments; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.courses.completion_require_all_assignments IS 'Whether all assignments must be completed/passed';


--
-- TOC entry 5846 (class 0 OID 0)
-- Dependencies: 266
-- Name: COLUMN courses.completion_require_curriculum; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.courses.completion_require_curriculum IS 'Whether all curriculum modules must be marked complete';


--
-- TOC entry 5847 (class 0 OID 0)
-- Dependencies: 266
-- Name: COLUMN courses.completion_require_trainer_approval; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.courses.completion_require_trainer_approval IS 'Whether trainer must approve completion for in-class courses';


--
-- TOC entry 267 (class 1259 OID 49007)
-- Name: customer_batch_access; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.customer_batch_access (
    id character varying(255) DEFAULT gen_random_uuid() NOT NULL,
    organization_id uuid NOT NULL,
    batch_id uuid NOT NULL,
    assigned_at timestamp with time zone DEFAULT now() NOT NULL,
    assigned_by character varying(255)
);


ALTER TABLE public.customer_batch_access OWNER TO postgres;

--
-- TOC entry 268 (class 1259 OID 49012)
-- Name: customer_batch_enrollments; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.customer_batch_enrollments (
    id character varying(255) DEFAULT gen_random_uuid() NOT NULL,
    organization_id uuid NOT NULL,
    user_id character varying(255) NOT NULL,
    batch_id uuid NOT NULL,
    enrolled_at timestamp with time zone DEFAULT now() NOT NULL,
    status text DEFAULT 'enrolled'::text NOT NULL,
    completed_at timestamp with time zone
);


ALTER TABLE public.customer_batch_enrollments OWNER TO postgres;

--
-- TOC entry 269 (class 1259 OID 49020)
-- Name: customer_course_access; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.customer_course_access (
    id character varying(255) DEFAULT gen_random_uuid() NOT NULL,
    organization_id uuid NOT NULL,
    course_id uuid NOT NULL,
    granted_at timestamp with time zone DEFAULT now(),
    granted_by uuid,
    expires_at timestamp with time zone,
    max_enrollments integer
);


ALTER TABLE public.customer_course_access OWNER TO postgres;

--
-- TOC entry 270 (class 1259 OID 49025)
-- Name: customer_enrollments; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.customer_enrollments (
    id character varying(255) DEFAULT gen_random_uuid() NOT NULL,
    organization_id uuid NOT NULL,
    user_id character varying(255) NOT NULL,
    course_id uuid NOT NULL,
    enrolled_at timestamp with time zone DEFAULT now() NOT NULL,
    completed_at timestamp with time zone,
    progress_percentage integer DEFAULT 0,
    last_accessed_at timestamp with time zone
);


ALTER TABLE public.customer_enrollments OWNER TO postgres;

--
-- TOC entry 271 (class 1259 OID 49031)
-- Name: customer_organizations; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.customer_organizations (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    name text NOT NULL,
    slug text NOT NULL,
    logo_url text,
    primary_color text DEFAULT '#3B82F6'::text,
    secondary_color text DEFAULT '#1E40AF'::text,
    contact_email text,
    contact_phone text,
    is_active boolean DEFAULT true,
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now(),
    created_by uuid
);


ALTER TABLE public.customer_organizations OWNER TO postgres;

--
-- TOC entry 272 (class 1259 OID 49042)
-- Name: customer_portal_admins; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.customer_portal_admins (
    id character varying(255) DEFAULT gen_random_uuid() NOT NULL,
    organization_id uuid NOT NULL,
    user_id character varying(255) NOT NULL,
    role text DEFAULT 'viewer'::text NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    created_by character varying(255),
    updated_at timestamp(6) with time zone
);


ALTER TABLE public.customer_portal_admins OWNER TO postgres;

--
-- TOC entry 273 (class 1259 OID 49050)
-- Name: customer_portal_invitations; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.customer_portal_invitations (
    id character varying(255) DEFAULT gen_random_uuid() NOT NULL,
    organization_id uuid NOT NULL,
    email text NOT NULL,
    full_name text,
    invited_by character varying(255) NOT NULL,
    invited_at timestamp with time zone DEFAULT now() NOT NULL,
    accepted_at timestamp with time zone,
    user_id character varying(255),
    status text DEFAULT 'pending'::text NOT NULL,
    expires_at timestamp with time zone DEFAULT (now() + '30 days'::interval)
);


ALTER TABLE public.customer_portal_invitations OWNER TO postgres;

--
-- TOC entry 274 (class 1259 OID 49059)
-- Name: dashboard_configurations; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.dashboard_configurations (
    id character varying(255) DEFAULT gen_random_uuid() NOT NULL,
    user_id uuid,
    role text,
    department_id uuid,
    kpi_id uuid NOT NULL,
    is_visible boolean DEFAULT true,
    display_order integer DEFAULT 0,
    custom_config character varying(255) DEFAULT '{}'::jsonb,
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now()
);


ALTER TABLE public.dashboard_configurations OWNER TO postgres;

--
-- TOC entry 275 (class 1259 OID 49070)
-- Name: dashboard_templates; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.dashboard_templates (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    name text NOT NULL,
    description text,
    layout_config character varying(255) DEFAULT '{}'::jsonb NOT NULL,
    thumbnail_url text,
    is_default boolean DEFAULT false,
    is_active boolean DEFAULT true,
    created_by uuid,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL
);


ALTER TABLE public.dashboard_templates OWNER TO postgres;

--
-- TOC entry 276 (class 1259 OID 49081)
-- Name: dashboard_widgets; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.dashboard_widgets (
    id character varying(255) DEFAULT gen_random_uuid() NOT NULL,
    name text NOT NULL,
    widget_type text NOT NULL,
    component_name text NOT NULL,
    default_config character varying(255) DEFAULT '{}'::jsonb,
    available_for_roles character varying(255) DEFAULT ARRAY['learner'::text, 'trainer'::text, 'admin'::text],
    category text DEFAULT 'general'::text,
    is_active boolean DEFAULT true,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp(6) with time zone
);


ALTER TABLE public.dashboard_widgets OWNER TO postgres;

--
-- TOC entry 277 (class 1259 OID 49092)
-- Name: data_export_requests; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.data_export_requests (
    id character varying(255) DEFAULT gen_random_uuid() NOT NULL,
    user_id character varying(255) NOT NULL,
    status text DEFAULT 'pending'::text NOT NULL,
    export_url text,
    requested_at timestamp with time zone DEFAULT now() NOT NULL,
    completed_at timestamp with time zone,
    expires_at timestamp with time zone,
    CONSTRAINT data_export_requests_status_check CHECK ((status = ANY (ARRAY['pending'::text, 'processing'::text, 'completed'::text, 'failed'::text])))
);


ALTER TABLE public.data_export_requests OWNER TO postgres;

--
-- TOC entry 278 (class 1259 OID 49101)
-- Name: department_competency_levels; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.department_competency_levels (
    id character varying(255) DEFAULT gen_random_uuid() NOT NULL,
    department_id uuid NOT NULL,
    competency_id uuid NOT NULL,
    expected_level integer DEFAULT 1 NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    created_by uuid,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    CONSTRAINT department_competency_levels_expected_level_check CHECK (((expected_level >= 1) AND (expected_level <= 5)))
);


ALTER TABLE public.department_competency_levels OWNER TO postgres;

--
-- TOC entry 279 (class 1259 OID 49109)
-- Name: departments; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.departments (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    name text NOT NULL,
    description text,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    parent_id uuid,
    level integer DEFAULT 1,
    head_user_id character varying(255),
    code text,
    is_active boolean DEFAULT true,
    updated_at timestamp with time zone DEFAULT now()
);


ALTER TABLE public.departments OWNER TO postgres;

--
-- TOC entry 280 (class 1259 OID 49119)
-- Name: designation_competencies; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.designation_competencies (
    id character varying(255) DEFAULT gen_random_uuid() NOT NULL,
    designation_id uuid NOT NULL,
    competency_id uuid NOT NULL,
    required_level integer DEFAULT 1,
    is_mandatory boolean DEFAULT true,
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp(6) with time zone
);


ALTER TABLE public.designation_competencies OWNER TO postgres;

--
-- TOC entry 281 (class 1259 OID 49126)
-- Name: designations; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.designations (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    name text NOT NULL,
    code text,
    description text,
    department_id uuid,
    band_level text,
    hierarchy_level integer DEFAULT 1,
    is_department_head boolean DEFAULT false,
    is_active boolean DEFAULT true,
    created_by character varying(255),
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now()
);


ALTER TABLE public.designations OWNER TO postgres;

--
-- TOC entry 282 (class 1259 OID 49137)
-- Name: employee_assignments; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.employee_assignments (
    id character varying(255) DEFAULT gen_random_uuid() NOT NULL,
    user_id character varying(255) NOT NULL,
    department_id uuid,
    designation_id uuid,
    reports_to_id character varying(255),
    is_primary boolean DEFAULT true,
    effective_from character varying(255) DEFAULT CURRENT_DATE,
    effective_until character varying(255),
    is_active boolean DEFAULT true,
    created_by character varying(255),
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now()
);


ALTER TABLE public.employee_assignments OWNER TO postgres;

--
-- TOC entry 283 (class 1259 OID 49146)
-- Name: enrollment_requests; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.enrollment_requests (
    id character varying(255) DEFAULT gen_random_uuid() NOT NULL,
    user_id character varying(255) NOT NULL,
    course_id uuid NOT NULL,
    manager_id character varying(255),
    status text DEFAULT 'pending'::text NOT NULL,
    request_notes text,
    review_notes text,
    requested_at timestamp with time zone DEFAULT now() NOT NULL,
    reviewed_at timestamp with time zone,
    reviewed_by character varying(255),
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp(6) with time zone,
    CONSTRAINT enrollment_requests_status_check CHECK ((status = ANY (ARRAY['pending'::text, 'approved'::text, 'rejected'::text])))
);


ALTER TABLE public.enrollment_requests OWNER TO postgres;

--
-- TOC entry 284 (class 1259 OID 49156)
-- Name: enrollments; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.enrollments (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    user_id uuid NOT NULL,
    course_id uuid NOT NULL,
    batch_id uuid,
    progress_percentage integer DEFAULT 0,
    status text DEFAULT 'enrolled'::text,
    enrolled_at timestamp with time zone DEFAULT now() NOT NULL,
    completed_at timestamp with time zone,
    completion_approved_by character varying(255),
    completion_approved_at timestamp with time zone,
    nominated_by uuid
);


ALTER TABLE public.enrollments OWNER TO postgres;

--
-- TOC entry 5848 (class 0 OID 0)
-- Dependencies: 284
-- Name: COLUMN enrollments.completion_approved_by; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.enrollments.completion_approved_by IS 'Trainer who approved the course completion';


--
-- TOC entry 5849 (class 0 OID 0)
-- Dependencies: 284
-- Name: COLUMN enrollments.completion_approved_at; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.enrollments.completion_approved_at IS 'When the completion was approved';


--
-- TOC entry 285 (class 1259 OID 49165)
-- Name: external_assessment_results; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.external_assessment_results (
    id character varying(255) DEFAULT gen_random_uuid() NOT NULL,
    external_assessment_id uuid NOT NULL,
    user_id character varying(255) NOT NULL,
    score integer,
    passed boolean DEFAULT false,
    raw_data character varying(255) DEFAULT '{}'::jsonb,
    completed_at timestamp with time zone,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp(6) with time zone
);


ALTER TABLE public.external_assessment_results OWNER TO postgres;

--
-- TOC entry 286 (class 1259 OID 49174)
-- Name: external_assessments; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.external_assessments (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    title text NOT NULL,
    description text,
    provider text NOT NULL,
    assessment_url text,
    embed_code text,
    integration_type text DEFAULT 'link'::text,
    competency_id uuid,
    passing_score integer DEFAULT 70,
    level_awarded integer DEFAULT 1,
    is_active boolean DEFAULT true,
    config_data character varying(255) DEFAULT '{}'::jsonb,
    created_by character varying(255),
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    CONSTRAINT valid_integration_type CHECK ((integration_type = ANY (ARRAY['link'::text, 'embed'::text, 'api'::text])))
);


ALTER TABLE public.external_assessments OWNER TO postgres;

--
-- TOC entry 287 (class 1259 OID 49188)
-- Name: external_user_profiles; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.external_user_profiles (
    id character varying(255) DEFAULT gen_random_uuid() NOT NULL,
    user_id uuid NOT NULL,
    organization_id uuid,
    external_user_type text NOT NULL,
    company_name text,
    phone_number text,
    external_employee_id text,
    metadata character varying(255) DEFAULT '{}'::jsonb,
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now(),
    CONSTRAINT external_user_profiles_external_user_type_check CHECK ((external_user_type = ANY (ARRAY['customer'::text, 'contractor'::text, 'delivery_staff'::text])))
);


ALTER TABLE public.external_user_profiles OWNER TO postgres;

--
-- TOC entry 288 (class 1259 OID 49198)
-- Name: feedback_analytics; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.feedback_analytics (
    id character varying(255) DEFAULT gen_random_uuid() NOT NULL,
    template_id uuid NOT NULL,
    question_id uuid,
    course_id uuid,
    batch_id uuid,
    period_start character varying(255),
    period_end character varying(255),
    total_responses integer DEFAULT 0,
    average_score numeric(38,2),
    score_distribution character varying(255) DEFAULT '{}'::jsonb,
    nps_score integer,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    created_at timestamp(6) with time zone
);


ALTER TABLE public.feedback_analytics OWNER TO postgres;

--
-- TOC entry 289 (class 1259 OID 49207)
-- Name: feedback_questions; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.feedback_questions (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    template_id uuid NOT NULL,
    question_text text NOT NULL,
    question_type text DEFAULT 'rating'::text NOT NULL,
    options character varying(255) DEFAULT '[]'::jsonb,
    is_required boolean DEFAULT true,
    order_index integer DEFAULT 0 NOT NULL,
    settings character varying(255) DEFAULT '{}'::jsonb,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp(6) with time zone,
    CONSTRAINT feedback_questions_question_type_check CHECK ((question_type = ANY (ARRAY['rating'::text, 'likert'::text, 'nps'::text, 'multiple_choice'::text, 'checkbox'::text, 'text'::text, 'yes_no'::text])))
);


ALTER TABLE public.feedback_questions OWNER TO postgres;

--
-- TOC entry 290 (class 1259 OID 49220)
-- Name: feedback_response_answers; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.feedback_response_answers (
    id character varying(255) DEFAULT gen_random_uuid() NOT NULL,
    response_id uuid NOT NULL,
    question_id uuid NOT NULL,
    answer_value text,
    answer_text text,
    answer_array character varying(255),
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp(6) with time zone
);


ALTER TABLE public.feedback_response_answers OWNER TO postgres;

--
-- TOC entry 291 (class 1259 OID 49227)
-- Name: feedback_responses; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.feedback_responses (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    template_id uuid NOT NULL,
    trigger_id uuid,
    user_id uuid NOT NULL,
    course_id uuid,
    batch_id uuid,
    module_id uuid,
    session_id uuid,
    is_anonymous boolean DEFAULT true,
    submitted_at timestamp with time zone DEFAULT now() NOT NULL
);


ALTER TABLE public.feedback_responses OWNER TO postgres;

--
-- TOC entry 292 (class 1259 OID 49233)
-- Name: feedback_templates; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.feedback_templates (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    title text NOT NULL,
    description text,
    template_type text DEFAULT 'course_feedback'::text NOT NULL,
    is_anonymous boolean DEFAULT true,
    is_active boolean DEFAULT true,
    created_by uuid,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    CONSTRAINT feedback_templates_template_type_check CHECK ((template_type = ANY (ARRAY['course_feedback'::text, 'module_feedback'::text, 'session_feedback'::text, 'trainer_feedback'::text, 'general'::text])))
);


ALTER TABLE public.feedback_templates OWNER TO postgres;

--
-- TOC entry 293 (class 1259 OID 49245)
-- Name: feedback_triggers; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.feedback_triggers (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    template_id uuid NOT NULL,
    trigger_type text NOT NULL,
    target_type text,
    target_id character varying(255),
    trigger_config character varying(255) DEFAULT '{}'::jsonb,
    is_active boolean DEFAULT true,
    created_by uuid,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp(6) with time zone,
    CONSTRAINT feedback_triggers_target_type_check CHECK ((target_type = ANY (ARRAY['course'::text, 'batch'::text, 'module'::text, 'all'::text]))),
    CONSTRAINT feedback_triggers_trigger_type_check CHECK ((trigger_type = ANY (ARRAY['course_completion'::text, 'module_completion'::text, 'batch_session'::text, 'manual'::text, 'scheduled'::text, 'progress_milestone'::text])))
);


ALTER TABLE public.feedback_triggers OWNER TO postgres;

--
-- TOC entry 294 (class 1259 OID 49256)
-- Name: gap_assignment_rules; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.gap_assignment_rules (
    id character varying(255) DEFAULT gen_random_uuid() NOT NULL,
    gap_min integer DEFAULT 1 NOT NULL,
    gap_max integer DEFAULT 1 NOT NULL,
    assignment_type text DEFAULT 'learning_path'::text NOT NULL,
    assignment_intensity text DEFAULT 'standard'::text NOT NULL,
    description text,
    auto_assign_enabled boolean DEFAULT true,
    notify_manager boolean DEFAULT false,
    notify_learner boolean DEFAULT true,
    include_ai_recommendations boolean DEFAULT true,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    CONSTRAINT valid_gap_range CHECK (((gap_min >= 0) AND (gap_max >= gap_min)))
);


ALTER TABLE public.gap_assignment_rules OWNER TO postgres;

--
-- TOC entry 295 (class 1259 OID 49273)
-- Name: help_article_media; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.help_article_media (
    id character varying(255) DEFAULT gen_random_uuid() NOT NULL,
    article_id uuid NOT NULL,
    media_type text NOT NULL,
    url text NOT NULL,
    caption text,
    order_index integer DEFAULT 0,
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp(6) with time zone,
    CONSTRAINT help_article_media_media_type_check CHECK ((media_type = ANY (ARRAY['image'::text, 'video'::text, 'youtube'::text])))
);


ALTER TABLE public.help_article_media OWNER TO postgres;

--
-- TOC entry 296 (class 1259 OID 49282)
-- Name: help_article_sections; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.help_article_sections (
    id character varying(255) DEFAULT gen_random_uuid() NOT NULL,
    article_id uuid NOT NULL,
    title text NOT NULL,
    content text,
    is_collapsible boolean DEFAULT true,
    is_expanded_by_default boolean DEFAULT false,
    order_index integer DEFAULT 0,
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp(6) with time zone
);


ALTER TABLE public.help_article_sections OWNER TO postgres;

--
-- TOC entry 297 (class 1259 OID 49292)
-- Name: help_article_steps; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.help_article_steps (
    id character varying(255) DEFAULT gen_random_uuid() NOT NULL,
    article_id uuid NOT NULL,
    step_number integer NOT NULL,
    title text NOT NULL,
    description text,
    image_url text,
    video_url text,
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp(6) with time zone
);


ALTER TABLE public.help_article_steps OWNER TO postgres;

--
-- TOC entry 298 (class 1259 OID 49299)
-- Name: help_articles; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.help_articles (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    category_id uuid,
    title text NOT NULL,
    description text,
    content text,
    keywords character varying(255) DEFAULT '{}'::text[],
    is_published boolean DEFAULT false,
    view_count integer DEFAULT 0,
    order_index integer DEFAULT 0,
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now(),
    created_by uuid,
    updated_by uuid
);


ALTER TABLE public.help_articles OWNER TO postgres;

--
-- TOC entry 299 (class 1259 OID 49311)
-- Name: help_categories; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.help_categories (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    name text NOT NULL,
    description text,
    icon text DEFAULT 'HelpCircle'::text,
    order_index integer DEFAULT 0,
    is_active boolean DEFAULT true,
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now(),
    created_by uuid
);


ALTER TABLE public.help_categories OWNER TO postgres;

--
-- TOC entry 300 (class 1259 OID 49322)
-- Name: help_related_articles; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.help_related_articles (
    id character varying(255) DEFAULT gen_random_uuid() NOT NULL,
    article_id uuid NOT NULL,
    related_article_id uuid NOT NULL,
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp(6) with time zone,
    CONSTRAINT help_related_articles_check CHECK ((article_id <> related_article_id))
);


ALTER TABLE public.help_related_articles OWNER TO postgres;

--
-- TOC entry 301 (class 1259 OID 49328)
-- Name: hrms_configurations; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.hrms_configurations (
    id character varying(255) DEFAULT gen_random_uuid() NOT NULL,
    hrms_type text NOT NULL,
    name text NOT NULL,
    is_active boolean DEFAULT false,
    config_data character varying(255) DEFAULT '{}'::jsonb NOT NULL,
    last_sync_at timestamp with time zone,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    created_by uuid,
    CONSTRAINT hrms_configurations_hrms_type_check CHECK ((hrms_type = ANY (ARRAY['workday'::text, 'sap_successfactors'::text, 'bamboohr'::text, 'generic_webhook'::text])))
);

ALTER TABLE ONLY public.hrms_configurations FORCE ROW LEVEL SECURITY;


ALTER TABLE public.hrms_configurations OWNER TO postgres;

--
-- TOC entry 302 (class 1259 OID 49339)
-- Name: hrms_sync_logs; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.hrms_sync_logs (
    id character varying(255) DEFAULT gen_random_uuid() NOT NULL,
    sync_type text DEFAULT 'webhook'::text NOT NULL,
    source_system text,
    status text DEFAULT 'pending'::text NOT NULL,
    total_records integer DEFAULT 0,
    successful_records integer DEFAULT 0,
    failed_records integer DEFAULT 0,
    error_details text DEFAULT '[]'::jsonb,
    sync_data text DEFAULT '{}'::jsonb,
    started_at timestamp with time zone DEFAULT now(),
    completed_at timestamp with time zone,
    triggered_by character varying(255),
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp(6) with time zone
);


ALTER TABLE public.hrms_sync_logs OWNER TO postgres;

--
-- TOC entry 303 (class 1259 OID 49354)
-- Name: inferred_skills; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.inferred_skills (
    id character varying(255) DEFAULT gen_random_uuid() NOT NULL,
    user_id character varying(255) NOT NULL,
    competency_id uuid,
    inferred_level numeric(38,2) DEFAULT 0,
    confidence_score numeric(38,2) DEFAULT 0,
    evidence character varying(255) DEFAULT '[]'::jsonb,
    last_updated timestamp with time zone DEFAULT now(),
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp(6) with time zone
);


ALTER TABLE public.inferred_skills OWNER TO postgres;

--
-- TOC entry 304 (class 1259 OID 49365)
-- Name: issued_certificates; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.issued_certificates (
    id character varying(255) DEFAULT gen_random_uuid() NOT NULL,
    user_id character varying(255) NOT NULL,
    course_id uuid NOT NULL,
    enrollment_id uuid NOT NULL,
    learner_name text NOT NULL,
    learner_email text NOT NULL,
    course_title text NOT NULL,
    completion_date timestamp with time zone NOT NULL,
    issued_at timestamp with time zone DEFAULT now() NOT NULL,
    template_id uuid,
    certificate_number text NOT NULL,
    verification_code text NOT NULL,
    is_revoked boolean DEFAULT false,
    revoked_at timestamp with time zone,
    revoked_by character varying(255),
    revoke_reason text
);


ALTER TABLE public.issued_certificates OWNER TO postgres;

--
-- TOC entry 305 (class 1259 OID 49373)
-- Name: job_families; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.job_families (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    name text NOT NULL,
    description text,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    created_by character varying(255),
    updated_at timestamp with time zone DEFAULT now() NOT NULL
);


ALTER TABLE public.job_families OWNER TO postgres;

--
-- TOC entry 306 (class 1259 OID 49381)
-- Name: job_family_competencies; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.job_family_competencies (
    id character varying(255) DEFAULT gen_random_uuid() NOT NULL,
    job_family_id uuid NOT NULL,
    competency_id uuid NOT NULL,
    expected_level integer DEFAULT 1 NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    created_by character varying(255),
    updated_at timestamp(6) with time zone
);


ALTER TABLE public.job_family_competencies OWNER TO postgres;

--
-- TOC entry 307 (class 1259 OID 49387)
-- Name: kpi_definitions; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.kpi_definitions (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    name text NOT NULL,
    description text,
    kpi_key text NOT NULL,
    visualization_type text NOT NULL,
    data_source text NOT NULL,
    default_config character varying(255) DEFAULT '{}'::jsonb,
    is_active boolean DEFAULT true,
    display_order integer DEFAULT 0,
    category text DEFAULT 'general'::text,
    required_role character varying(255) DEFAULT ARRAY['admin'::text, 'department_head'::text],
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now(),
    target_dashboards character varying(255) DEFAULT ARRAY['main_dashboard'::text],
    CONSTRAINT kpi_definitions_visualization_type_check CHECK ((visualization_type = ANY (ARRAY['gauge'::text, 'donut'::text, 'kpi_card'::text, 'heatmap'::text, 'line_bar'::text, 'stacked_bar'::text, 'line_chart'::text, 'funnel'::text, 'horizontal_bar'::text, 'radar'::text])))
);


ALTER TABLE public.kpi_definitions OWNER TO postgres;

--
-- TOC entry 5850 (class 0 OID 0)
-- Dependencies: 307
-- Name: COLUMN kpi_definitions.target_dashboards; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.kpi_definitions.target_dashboards IS 'Array of dashboard identifiers where this KPI should appear. Valid values: main_dashboard, learning_analytics, manager_dashboard, trainer_dashboard, learner_dashboard, competency_analytics';


--
-- TOC entry 308 (class 1259 OID 49402)
-- Name: landing_pages; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.landing_pages (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    title text NOT NULL,
    slug text NOT NULL,
    description text,
    is_published boolean DEFAULT false,
    is_homepage boolean DEFAULT false,
    seo_title text,
    seo_description text,
    seo_keywords character varying(255),
    og_image_url text,
    header_menu_id character varying(255),
    footer_menu_id character varying(255),
    created_by character varying(255),
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL
);


ALTER TABLE public.landing_pages OWNER TO postgres;

--
-- TOC entry 309 (class 1259 OID 49412)
-- Name: learner_badges; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.learner_badges (
    id character varying(255) DEFAULT gen_random_uuid() NOT NULL,
    user_id uuid NOT NULL,
    badge_id uuid NOT NULL,
    earned_at timestamp with time zone DEFAULT now(),
    awarded_by uuid
);


ALTER TABLE public.learner_badges OWNER TO postgres;

--
-- TOC entry 310 (class 1259 OID 49417)
-- Name: learner_competency_progress; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.learner_competency_progress (
    id character varying(255) DEFAULT gen_random_uuid() NOT NULL,
    user_id uuid NOT NULL,
    competency_id uuid NOT NULL,
    current_level integer DEFAULT 0 NOT NULL,
    target_level integer,
    assessed_at timestamp with time zone,
    assessed_by uuid,
    notes text,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    target_date character varying(255),
    target_source text
);


ALTER TABLE public.learner_competency_progress OWNER TO postgres;

--
-- TOC entry 311 (class 1259 OID 49426)
-- Name: learner_competency_scores; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.learner_competency_scores (
    id character varying(255) DEFAULT gen_random_uuid() NOT NULL,
    user_id uuid NOT NULL,
    competency_id uuid NOT NULL,
    weighted_score numeric(38,2) DEFAULT 0,
    total_assessments integer DEFAULT 0,
    last_assessment_at timestamp with time zone,
    calculated_level integer DEFAULT 0,
    ai_recommended_level integer,
    ai_confidence_score numeric(38,2),
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL
);


ALTER TABLE public.learner_competency_scores OWNER TO postgres;

--
-- TOC entry 312 (class 1259 OID 49437)
-- Name: learner_learning_paths; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.learner_learning_paths (
    id character varying(255) DEFAULT gen_random_uuid() NOT NULL,
    user_id character varying(255) NOT NULL,
    learning_path_id uuid NOT NULL,
    assigned_at timestamp with time zone DEFAULT now() NOT NULL,
    assigned_by character varying(255),
    started_at timestamp with time zone,
    completed_at timestamp with time zone,
    status text DEFAULT 'assigned'::text,
    progress_percentage integer DEFAULT 0
);


ALTER TABLE public.learner_learning_paths OWNER TO postgres;

--
-- TOC entry 313 (class 1259 OID 49446)
-- Name: learner_notes; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.learner_notes (
    id character varying(255) DEFAULT gen_random_uuid() NOT NULL,
    user_id character varying(255) NOT NULL,
    module_id uuid NOT NULL,
    content text,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL
);


ALTER TABLE public.learner_notes OWNER TO postgres;

--
-- TOC entry 314 (class 1259 OID 49454)
-- Name: learner_notifications; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.learner_notifications (
    id character varying(255) DEFAULT gen_random_uuid() NOT NULL,
    user_id character varying(255) NOT NULL,
    title text NOT NULL,
    message text NOT NULL,
    notification_type text DEFAULT 'info'::text,
    is_read boolean DEFAULT false,
    is_dismissed boolean DEFAULT false,
    related_course_id uuid,
    created_at timestamp with time zone DEFAULT now(),
    created_by character varying(255),
    updated_at timestamp(6) with time zone
);


ALTER TABLE public.learner_notifications OWNER TO postgres;

--
-- TOC entry 315 (class 1259 OID 49464)
-- Name: learner_points; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.learner_points (
    id character varying(255) DEFAULT gen_random_uuid() NOT NULL,
    user_id uuid NOT NULL,
    total_points integer DEFAULT 0,
    current_streak integer DEFAULT 0,
    longest_streak integer DEFAULT 0,
    last_activity_date character varying(255),
    updated_at timestamp with time zone DEFAULT now(),
    created_at timestamp(6) with time zone
);


ALTER TABLE public.learner_points OWNER TO postgres;

--
-- TOC entry 316 (class 1259 OID 49472)
-- Name: learning_activity_log; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.learning_activity_log (
    id character varying(255) DEFAULT gen_random_uuid() NOT NULL,
    user_id character varying(255) NOT NULL,
    activity_type text NOT NULL,
    course_id uuid,
    content_id uuid,
    session_duration_seconds integer DEFAULT 0,
    activity_date character varying(255) DEFAULT CURRENT_DATE,
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp(6) with time zone
);


ALTER TABLE public.learning_activity_log OWNER TO postgres;

--
-- TOC entry 317 (class 1259 OID 49481)
-- Name: learning_path_ai_metadata; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.learning_path_ai_metadata (
    id character varying(255) DEFAULT gen_random_uuid() NOT NULL,
    learning_path_id uuid,
    generation_prompt text,
    target_role_id uuid,
    target_competencies character varying(255) DEFAULT '[]'::jsonb,
    estimated_completion_weeks integer,
    difficulty_progression text,
    ai_model_used text,
    generated_at timestamp with time zone DEFAULT now(),
    last_optimized_at timestamp with time zone,
    optimization_notes text
);


ALTER TABLE public.learning_path_ai_metadata OWNER TO postgres;

--
-- TOC entry 318 (class 1259 OID 49489)
-- Name: learning_path_courses; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.learning_path_courses (
    id character varying(255) DEFAULT gen_random_uuid() NOT NULL,
    learning_path_id uuid NOT NULL,
    course_id uuid NOT NULL,
    order_index integer DEFAULT 0 NOT NULL,
    is_required boolean DEFAULT true
);


ALTER TABLE public.learning_path_courses OWNER TO postgres;

--
-- TOC entry 319 (class 1259 OID 49495)
-- Name: learning_paths; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.learning_paths (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    title text NOT NULL,
    description text,
    thumbnail_url text,
    target_competency_id uuid,
    created_by uuid,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp(6) with time zone,
    is_published boolean DEFAULT false
);


ALTER TABLE public.learning_paths OWNER TO postgres;

--
-- TOC entry 320 (class 1259 OID 49502)
-- Name: live_annotations; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.live_annotations (
    id character varying(255) DEFAULT gen_random_uuid() NOT NULL,
    session_id uuid,
    content_id uuid,
    annotator_id character varying(255) NOT NULL,
    annotation_type text NOT NULL,
    annotation_data character varying(255) NOT NULL,
    timestamp_seconds integer,
    is_visible boolean DEFAULT true,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp(6) with time zone
);


ALTER TABLE public.live_annotations OWNER TO postgres;

--
-- TOC entry 321 (class 1259 OID 49510)
-- Name: meeting_integrations; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.meeting_integrations (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    name text NOT NULL,
    provider text NOT NULL,
    client_id text,
    client_secret text,
    api_key text,
    api_secret text,
    access_token text,
    refresh_token text,
    token_expires_at timestamp with time zone,
    webhook_secret text,
    is_active boolean DEFAULT true,
    config_data character varying(255) DEFAULT '{}'::jsonb,
    created_by character varying(255),
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    CONSTRAINT meeting_integrations_provider_check CHECK ((provider = ANY (ARRAY['zoom'::text, 'webex'::text, 'teams'::text, 'google_meet'::text])))
);


ALTER TABLE public.meeting_integrations OWNER TO postgres;

--
-- TOC entry 322 (class 1259 OID 49521)
-- Name: mentor_learners; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.mentor_learners (
    id character varying(255) DEFAULT gen_random_uuid() NOT NULL,
    batch_id uuid NOT NULL,
    mentor_id character varying(255) NOT NULL,
    learner_id character varying(255) NOT NULL,
    assigned_at timestamp with time zone DEFAULT now() NOT NULL,
    assigned_by character varying(255)
);


ALTER TABLE public.mentor_learners OWNER TO postgres;

--
-- TOC entry 323 (class 1259 OID 49526)
-- Name: microlearning_assignments; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.microlearning_assignments (
    id character varying(255) DEFAULT gen_random_uuid() NOT NULL,
    microlearning_id uuid NOT NULL,
    user_id character varying(255) NOT NULL,
    assigned_by character varying(255),
    assigned_at timestamp with time zone DEFAULT now() NOT NULL
);


ALTER TABLE public.microlearning_assignments OWNER TO postgres;

--
-- TOC entry 324 (class 1259 OID 49531)
-- Name: microlearning_content; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.microlearning_content (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    title text NOT NULL,
    description text,
    content_type text DEFAULT 'video'::text NOT NULL,
    duration_minutes integer DEFAULT 5 NOT NULL,
    thumbnail_url text,
    content_url text,
    content_data character varying(255) DEFAULT '{}'::jsonb,
    xp_reward integer DEFAULT 10,
    is_universal boolean DEFAULT true,
    is_active boolean DEFAULT true,
    course_id uuid,
    created_by character varying(255),
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    department_id uuid,
    CONSTRAINT microlearning_content_content_type_check CHECK ((content_type = ANY (ARRAY['video'::text, 'quiz'::text, 'article'::text, 'flashcard'::text])))
);


ALTER TABLE public.microlearning_content OWNER TO postgres;

--
-- TOC entry 325 (class 1259 OID 49546)
-- Name: microlearning_progress; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.microlearning_progress (
    id character varying(255) DEFAULT gen_random_uuid() NOT NULL,
    microlearning_id uuid NOT NULL,
    user_id character varying(255) NOT NULL,
    started_at timestamp with time zone DEFAULT now(),
    completed_at timestamp with time zone,
    xp_awarded integer DEFAULT 0
);


ALTER TABLE public.microlearning_progress OWNER TO postgres;

--
-- TOC entry 326 (class 1259 OID 49552)
-- Name: module_content; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.module_content (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    module_id uuid NOT NULL,
    title text NOT NULL,
    content_type public.content_type NOT NULL,
    content_url text,
    content_data jsonb,
    duration_minutes integer DEFAULT 0,
    order_index integer DEFAULT 0 NOT NULL,
    is_preview boolean DEFAULT false,
    scorm_version text,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp(6) with time zone
);


ALTER TABLE public.module_content OWNER TO postgres;

--
-- TOC entry 327 (class 1259 OID 49562)
-- Name: navigation_items; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.navigation_items (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    menu_id uuid NOT NULL,
    parent_id uuid,
    label text NOT NULL,
    url text,
    page_id uuid,
    target text DEFAULT '_self'::text,
    icon text,
    order_index integer DEFAULT 0 NOT NULL,
    is_visible boolean DEFAULT true,
    created_at timestamp with time zone DEFAULT now() NOT NULL
);


ALTER TABLE public.navigation_items OWNER TO postgres;

--
-- TOC entry 328 (class 1259 OID 49572)
-- Name: navigation_menus; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.navigation_menus (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    name text NOT NULL,
    location text,
    is_active boolean DEFAULT true,
    created_by uuid,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL
);


ALTER TABLE public.navigation_menus OWNER TO postgres;

--
-- TOC entry 329 (class 1259 OID 49581)
-- Name: okr_competency_links; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.okr_competency_links (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    okr_id uuid NOT NULL,
    competency_id uuid NOT NULL,
    weight numeric(3,2) DEFAULT 1.00,
    target_level integer,
    created_at timestamp with time zone DEFAULT now() NOT NULL
);


ALTER TABLE public.okr_competency_links OWNER TO postgres;

--
-- TOC entry 330 (class 1259 OID 49587)
-- Name: okrs; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.okrs (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    title text NOT NULL,
    description text,
    okr_type text DEFAULT 'objective'::text NOT NULL,
    parent_id uuid,
    owner_id uuid,
    owner_type text DEFAULT 'individual'::text,
    target_value numeric,
    current_value numeric DEFAULT 0,
    unit text,
    start_date date,
    end_date date,
    status text DEFAULT 'on_track'::text,
    created_by uuid,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL
);


ALTER TABLE public.okrs OWNER TO postgres;

--
-- TOC entry 331 (class 1259 OID 49599)
-- Name: org_structure_audit; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.org_structure_audit (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    entity_type text NOT NULL,
    entity_id uuid NOT NULL,
    action text NOT NULL,
    old_values jsonb,
    new_values jsonb,
    changed_by uuid,
    changed_at timestamp with time zone DEFAULT now(),
    notes text
);


ALTER TABLE public.org_structure_audit OWNER TO postgres;

--
-- TOC entry 332 (class 1259 OID 49606)
-- Name: organizational_roles; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.organizational_roles (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    name text NOT NULL,
    description text,
    level_order integer DEFAULT 0 NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    created_by uuid,
    job_family_id uuid,
    department_id uuid,
    updated_at timestamp with time zone DEFAULT now() NOT NULL
);


ALTER TABLE public.organizational_roles OWNER TO postgres;

--
-- TOC entry 333 (class 1259 OID 49614)
-- Name: page_sections; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.page_sections (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    page_id uuid NOT NULL,
    template_id uuid,
    section_type text NOT NULL,
    title text,
    content jsonb DEFAULT '{}'::jsonb,
    styling jsonb DEFAULT '{}'::jsonb,
    order_index integer DEFAULT 0 NOT NULL,
    is_visible boolean DEFAULT true,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL
);


ALTER TABLE public.page_sections OWNER TO postgres;

--
-- TOC entry 334 (class 1259 OID 49626)
-- Name: path_adaptations; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.path_adaptations (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    personalized_path_id uuid,
    adaptation_type text NOT NULL,
    trigger_reason text,
    old_value jsonb,
    new_value jsonb,
    applied_at timestamp with time zone DEFAULT now(),
    reverted_at timestamp with time zone
);


ALTER TABLE public.path_adaptations OWNER TO postgres;

--
-- TOC entry 335 (class 1259 OID 49633)
-- Name: personalized_paths; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.personalized_paths (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    user_id uuid NOT NULL,
    base_learning_path_id uuid,
    title text NOT NULL,
    description text,
    target_competency_id uuid,
    current_phase text DEFAULT 'active'::text,
    customizations jsonb DEFAULT '{}'::jsonb,
    progress_percentage integer DEFAULT 0,
    started_at timestamp with time zone DEFAULT now(),
    completed_at timestamp with time zone,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now()
);


ALTER TABLE public.personalized_paths OWNER TO postgres;

--
-- TOC entry 336 (class 1259 OID 49645)
-- Name: point_rules; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.point_rules (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    name text NOT NULL,
    description text,
    trigger_event public.point_trigger_event NOT NULL,
    base_points integer DEFAULT 10 NOT NULL,
    difficulty_multiplier jsonb DEFAULT '{"advanced": 2, "beginner": 1, "intermediate": 1.5}'::jsonb,
    competency_multiplier numeric DEFAULT 1.0,
    priority_multiplier numeric DEFAULT 1.0,
    is_active boolean DEFAULT true,
    conditions jsonb DEFAULT '{}'::jsonb,
    created_by uuid,
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now()
);


ALTER TABLE public.point_rules OWNER TO postgres;

--
-- TOC entry 337 (class 1259 OID 49659)
-- Name: point_transactions; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.point_transactions (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    user_id uuid NOT NULL,
    rule_id uuid,
    trigger_event public.point_trigger_event NOT NULL,
    points_earned integer NOT NULL,
    multiplier_applied numeric DEFAULT 1.0,
    reference_type text,
    reference_id uuid,
    description text,
    created_at timestamp with time zone DEFAULT now()
);


ALTER TABLE public.point_transactions OWNER TO postgres;

--
-- TOC entry 338 (class 1259 OID 49667)
-- Name: poll_questions; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.poll_questions (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    poll_id uuid NOT NULL,
    question_text text NOT NULL,
    question_type text DEFAULT 'single_choice'::text NOT NULL,
    options jsonb DEFAULT '[]'::jsonb NOT NULL,
    order_index integer DEFAULT 0 NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    CONSTRAINT poll_questions_question_type_check CHECK ((question_type = ANY (ARRAY['single_choice'::text, 'multiple_choice'::text, 'rating'::text, 'word_cloud'::text])))
);


ALTER TABLE public.poll_questions OWNER TO postgres;

--
-- TOC entry 339 (class 1259 OID 49678)
-- Name: poll_responses; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.poll_responses (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    session_id uuid NOT NULL,
    question_id uuid NOT NULL,
    user_id uuid NOT NULL,
    selected_options jsonb DEFAULT '[]'::jsonb NOT NULL,
    text_response text,
    submitted_at timestamp with time zone DEFAULT now() NOT NULL
);


ALTER TABLE public.poll_responses OWNER TO postgres;

--
-- TOC entry 340 (class 1259 OID 49686)
-- Name: poll_results_cache; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.poll_results_cache (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    session_id uuid NOT NULL,
    question_id uuid NOT NULL,
    option_counts jsonb DEFAULT '{}'::jsonb NOT NULL,
    total_responses integer DEFAULT 0,
    updated_at timestamp with time zone DEFAULT now() NOT NULL
);


ALTER TABLE public.poll_results_cache OWNER TO postgres;

--
-- TOC entry 341 (class 1259 OID 49695)
-- Name: poll_sessions; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.poll_sessions (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    poll_id uuid NOT NULL,
    batch_id uuid,
    course_id uuid,
    started_by uuid,
    status text DEFAULT 'active'::text NOT NULL,
    time_limit_seconds integer,
    started_at timestamp with time zone DEFAULT now() NOT NULL,
    ended_at timestamp with time zone,
    current_question_index integer DEFAULT 0,
    CONSTRAINT poll_sessions_status_check CHECK ((status = ANY (ARRAY['active'::text, 'paused'::text, 'closed'::text])))
);


ALTER TABLE public.poll_sessions OWNER TO postgres;

--
-- TOC entry 342 (class 1259 OID 49705)
-- Name: poll_templates; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.poll_templates (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    title text NOT NULL,
    description text,
    is_anonymous boolean DEFAULT true,
    show_live_results boolean DEFAULT true,
    allow_multiple_votes boolean DEFAULT false,
    is_active boolean DEFAULT true,
    created_by uuid,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL
);


ALTER TABLE public.poll_templates OWNER TO postgres;

--
-- TOC entry 343 (class 1259 OID 49717)
-- Name: proficiency_scale_levels; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.proficiency_scale_levels (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    scale_id uuid NOT NULL,
    level integer NOT NULL,
    name text NOT NULL,
    description text,
    behavioral_indicators text[],
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL
);


ALTER TABLE public.proficiency_scale_levels OWNER TO postgres;

--
-- TOC entry 344 (class 1259 OID 49724)
-- Name: proficiency_scales; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.proficiency_scales (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    name text NOT NULL,
    description text,
    min_level integer DEFAULT 1 NOT NULL,
    max_level integer DEFAULT 5 NOT NULL,
    is_default boolean DEFAULT false,
    created_by uuid,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL
);


ALTER TABLE public.proficiency_scales OWNER TO postgres;

--
-- TOC entry 345 (class 1259 OID 49735)
-- Name: profiles; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.profiles (
    id uuid NOT NULL,
    email text NOT NULL,
    full_name text,
    avatar_url text,
    department_id uuid,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    employee_id text,
    designation text,
    is_manager boolean DEFAULT false,
    organizational_role_id uuid
);

ALTER TABLE ONLY public.profiles FORCE ROW LEVEL SECURITY;


ALTER TABLE public.profiles OWNER TO postgres;

--
-- TOC entry 346 (class 1259 OID 49743)
-- Name: question_analytics; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.question_analytics (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    question_id uuid NOT NULL,
    question_source text DEFAULT 'bank'::text NOT NULL,
    assessment_id uuid,
    total_attempts integer DEFAULT 0,
    correct_attempts integer DEFAULT 0,
    avg_time_seconds integer,
    discrimination_index numeric(5,4),
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    CONSTRAINT valid_question_source CHECK ((question_source = ANY (ARRAY['bank'::text, 'inline'::text])))
);


ALTER TABLE public.question_analytics OWNER TO postgres;

--
-- TOC entry 347 (class 1259 OID 49754)
-- Name: question_bank; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.question_bank (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    question_text text NOT NULL,
    question_type text DEFAULT 'multiple_choice'::text NOT NULL,
    options jsonb DEFAULT '[]'::jsonb NOT NULL,
    correct_answer text NOT NULL,
    explanation text,
    points integer DEFAULT 1 NOT NULL,
    difficulty text DEFAULT 'medium'::text,
    tags text[] DEFAULT '{}'::text[],
    competency_id uuid,
    media_url text,
    media_type text,
    is_active boolean DEFAULT true,
    usage_count integer DEFAULT 0,
    created_by uuid,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    CONSTRAINT valid_question_type CHECK ((question_type = ANY (ARRAY['multiple_choice'::text, 'true_false'::text, 'fill_blank'::text, 'matching'::text, 'short_answer'::text])))
);


ALTER TABLE public.question_bank OWNER TO postgres;

--
-- TOC entry 348 (class 1259 OID 49770)
-- Name: role_permissions; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.role_permissions (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    designation_id uuid NOT NULL,
    permission_key text NOT NULL,
    can_view boolean DEFAULT false,
    can_create boolean DEFAULT false,
    can_edit boolean DEFAULT false,
    can_delete boolean DEFAULT false,
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now()
);


ALTER TABLE public.role_permissions OWNER TO postgres;

--
-- TOC entry 349 (class 1259 OID 49781)
-- Name: scheduled_reassessments; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.scheduled_reassessments (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    learner_id uuid NOT NULL,
    competency_id uuid NOT NULL,
    assessment_id uuid,
    scheduled_date date NOT NULL,
    reminder_sent boolean DEFAULT false,
    status text DEFAULT 'scheduled'::text,
    created_at timestamp with time zone DEFAULT now() NOT NULL
);


ALTER TABLE public.scheduled_reassessments OWNER TO postgres;

--
-- TOC entry 350 (class 1259 OID 49790)
-- Name: score_level_mappings; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.score_level_mappings (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    proficiency_scale_id uuid,
    level_number integer NOT NULL,
    level_name text NOT NULL,
    min_score integer DEFAULT 0 NOT NULL,
    max_score integer DEFAULT 100 NOT NULL,
    description text,
    color text DEFAULT '#6B7280'::text,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    CONSTRAINT valid_score_range CHECK (((min_score >= 0) AND (max_score <= 100) AND (min_score < max_score)))
);


ALTER TABLE public.score_level_mappings OWNER TO postgres;

--
-- TOC entry 351 (class 1259 OID 49802)
-- Name: section_templates; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.section_templates (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    name text NOT NULL,
    type text NOT NULL,
    description text,
    icon text,
    default_config jsonb DEFAULT '{}'::jsonb,
    schema jsonb DEFAULT '{}'::jsonb,
    is_active boolean DEFAULT true,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP
);


ALTER TABLE public.section_templates OWNER TO postgres;

--
-- TOC entry 352 (class 1259 OID 49812)
-- Name: session_interactions; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.session_interactions (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    session_id uuid,
    user_id uuid NOT NULL,
    interaction_type text NOT NULL,
    content jsonb NOT NULL,
    parent_id uuid,
    is_anonymous boolean DEFAULT false,
    created_at timestamp with time zone DEFAULT now() NOT NULL
);


ALTER TABLE public.session_interactions OWNER TO postgres;

--
-- TOC entry 353 (class 1259 OID 49820)
-- Name: session_reminders; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.session_reminders (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    schedule_id uuid NOT NULL,
    user_id uuid NOT NULL,
    reminder_type text NOT NULL,
    remind_before_minutes integer DEFAULT 30 NOT NULL,
    sent_at timestamp with time zone,
    is_sent boolean DEFAULT false,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    CONSTRAINT session_reminders_reminder_type_check CHECK ((reminder_type = ANY (ARRAY['email'::text, 'in_app'::text, 'both'::text])))
);


ALTER TABLE public.session_reminders OWNER TO postgres;

--
-- TOC entry 354 (class 1259 OID 49830)
-- Name: skill_mapping_suggestions; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.skill_mapping_suggestions (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    course_id uuid,
    competency_id uuid,
    suggested_level integer DEFAULT 1,
    confidence_score numeric DEFAULT 0,
    reasoning text,
    status text DEFAULT 'pending'::text,
    reviewed_by uuid,
    reviewed_at timestamp with time zone,
    created_at timestamp with time zone DEFAULT now() NOT NULL
);


ALTER TABLE public.skill_mapping_suggestions OWNER TO postgres;

--
-- TOC entry 355 (class 1259 OID 49840)
-- Name: streak_tracker; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.streak_tracker (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    user_id uuid NOT NULL,
    current_streak integer DEFAULT 0,
    longest_streak integer DEFAULT 0,
    last_activity_date date,
    streak_start_date date,
    updated_at timestamp with time zone DEFAULT now()
);


ALTER TABLE public.streak_tracker OWNER TO postgres;

--
-- TOC entry 356 (class 1259 OID 49847)
-- Name: tag_taxonomy; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.tag_taxonomy (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    name text NOT NULL,
    category text NOT NULL,
    parent_id uuid,
    description text,
    is_active boolean DEFAULT true,
    created_at timestamp with time zone DEFAULT now() NOT NULL
);


ALTER TABLE public.tag_taxonomy OWNER TO postgres;

--
-- TOC entry 357 (class 1259 OID 49855)
-- Name: trainer_feedback_responses; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.trainer_feedback_responses (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    survey_id uuid NOT NULL,
    learner_id uuid,
    rating integer,
    feedback_text text,
    submitted_at timestamp with time zone DEFAULT now(),
    CONSTRAINT trainer_feedback_responses_rating_check CHECK (((rating >= 1) AND (rating <= 5)))
);


ALTER TABLE public.trainer_feedback_responses OWNER TO postgres;

--
-- TOC entry 358 (class 1259 OID 49863)
-- Name: trainer_feedback_surveys; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.trainer_feedback_surveys (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    trainer_id uuid NOT NULL,
    title text NOT NULL,
    description text,
    template_id uuid,
    initiated_by uuid,
    batch_id uuid,
    course_id uuid,
    status text DEFAULT 'active'::text,
    start_date timestamp with time zone DEFAULT now(),
    end_date timestamp with time zone,
    is_anonymous boolean DEFAULT true,
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now(),
    CONSTRAINT trainer_feedback_surveys_status_check CHECK ((status = ANY (ARRAY['draft'::text, 'active'::text, 'closed'::text])))
);


ALTER TABLE public.trainer_feedback_surveys OWNER TO postgres;

--
-- TOC entry 359 (class 1259 OID 49875)
-- Name: trainer_ratings; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.trainer_ratings (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    trainer_id uuid NOT NULL,
    average_rating numeric(3,2) DEFAULT 0,
    total_reviews integer DEFAULT 0,
    outstanding_count integer DEFAULT 0,
    good_count integer DEFAULT 0,
    average_count integer DEFAULT 0,
    bad_count integer DEFAULT 0,
    miserable_count integer DEFAULT 0,
    last_updated_at timestamp with time zone DEFAULT now()
);


ALTER TABLE public.trainer_ratings OWNER TO postgres;

--
-- TOC entry 360 (class 1259 OID 49887)
-- Name: trainers; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.trainers (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    user_id uuid,
    name text NOT NULL,
    title text,
    bio text,
    avatar_url text,
    is_external boolean DEFAULT false NOT NULL,
    company_name text,
    email text,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL
);


ALTER TABLE public.trainers OWNER TO postgres;

--
-- TOC entry 361 (class 1259 OID 49896)
-- Name: user_competencies; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.user_competencies (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    user_id uuid NOT NULL,
    competency_id uuid NOT NULL,
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now()
);


ALTER TABLE public.user_competencies OWNER TO postgres;

--
-- TOC entry 362 (class 1259 OID 49901)
-- Name: user_consent_records; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.user_consent_records (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    user_id uuid NOT NULL,
    consent_type text NOT NULL,
    consent_given boolean DEFAULT false NOT NULL,
    consent_version text,
    given_at timestamp with time zone,
    withdrawn_at timestamp with time zone,
    ip_address text,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL
);


ALTER TABLE public.user_consent_records OWNER TO postgres;

--
-- TOC entry 363 (class 1259 OID 49910)
-- Name: user_dashboard_layouts; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.user_dashboard_layouts (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    user_id uuid NOT NULL,
    layout jsonb DEFAULT '[]'::jsonb NOT NULL,
    theme_preferences jsonb DEFAULT '{}'::jsonb,
    updated_at timestamp with time zone DEFAULT now(),
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    template_id uuid
);


ALTER TABLE public.user_dashboard_layouts OWNER TO postgres;

--
-- TOC entry 364 (class 1259 OID 49920)
-- Name: user_departments; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.user_departments (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    user_id uuid NOT NULL,
    department_id uuid NOT NULL,
    created_at timestamp with time zone DEFAULT now()
);


ALTER TABLE public.user_departments OWNER TO postgres;

--
-- TOC entry 365 (class 1259 OID 49925)
-- Name: user_managers; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.user_managers (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    manager_id uuid NOT NULL,
    subordinate_id uuid NOT NULL,
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now(),
    CONSTRAINT user_managers_check CHECK ((manager_id <> subordinate_id))
);


ALTER TABLE public.user_managers OWNER TO postgres;

--
-- TOC entry 366 (class 1259 OID 49931)
-- Name: user_mfa_settings; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.user_mfa_settings (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    user_id uuid NOT NULL,
    mfa_enabled boolean DEFAULT false NOT NULL,
    mfa_method text DEFAULT 'totp'::text,
    enrolled_at timestamp with time zone,
    last_verified_at timestamp with time zone,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL
);


ALTER TABLE public.user_mfa_settings OWNER TO postgres;

--
-- TOC entry 367 (class 1259 OID 49941)
-- Name: user_roles; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.user_roles (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    user_id uuid NOT NULL,
    role public.app_role DEFAULT 'learner'::public.app_role NOT NULL,
    department_id uuid,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL
);


ALTER TABLE public.user_roles OWNER TO postgres;

--
-- TOC entry 368 (class 1259 OID 49947)
-- Name: user_type_ui_config; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.user_type_ui_config (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    user_type text NOT NULL,
    config jsonb DEFAULT '{}'::jsonb,
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now()
);


ALTER TABLE public.user_type_ui_config OWNER TO postgres;

--
-- TOC entry 220 (class 1259 OID 48447)
-- Name: users; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.users (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    email character varying(255),
    created_at timestamp with time zone DEFAULT now(),
    password text
);


ALTER TABLE public.users OWNER TO postgres;

--
-- TOC entry 369 (class 1259 OID 49956)
-- Name: xapi_statements; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.xapi_statements (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    statement_id uuid DEFAULT gen_random_uuid() NOT NULL,
    actor_id uuid NOT NULL,
    verb text NOT NULL,
    object_type text NOT NULL,
    object_id uuid NOT NULL,
    object_name text,
    result_score numeric(5,2),
    result_success boolean,
    result_completion boolean,
    result_duration interval,
    context_data jsonb DEFAULT '{}'::jsonb,
    "timestamp" timestamp with time zone DEFAULT now() NOT NULL,
    synced_to_lrs boolean DEFAULT false,
    synced_at timestamp with time zone
);


ALTER TABLE public.xapi_statements OWNER TO postgres;

--
-- TOC entry 5682 (class 0 OID 48568)
-- Dependencies: 221
-- Data for Name: account_deletion_requests; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY public.account_deletion_requests (id, user_id, status, reason, requested_at, reviewed_by, reviewed_at, completed_at, notes) FROM stdin;
\.


--
-- TOC entry 5683 (class 0 OID 48577)
-- Dependencies: 222
-- Data for Name: ai_content_tags; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY public.ai_content_tags (id, content_type, content_id, tag_id, tag_name, confidence_score, source, created_at, verified_by, verified_at, updated_at) FROM stdin;
\.


--
-- TOC entry 5684 (class 0 OID 48586)
-- Dependencies: 223
-- Data for Name: ai_learning_recommendations; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY public.ai_learning_recommendations (id, user_id, recommendation_type, target_id, target_title, reason, priority_score, context, is_dismissed, is_acted_upon, expires_at, created_at, updated_at) FROM stdin;
\.


--
-- TOC entry 5685 (class 0 OID 48597)
-- Dependencies: 224
-- Data for Name: assessment_assignment_learners; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY public.assessment_assignment_learners (id, assignment_id, learner_id, status, attempts_used, best_score, completed_at, created_at, updated_at) FROM stdin;
\.


--
-- TOC entry 5686 (class 0 OID 48606)
-- Dependencies: 225
-- Data for Name: assessment_assignments; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY public.assessment_assignments (id, assessment_id, assigned_by, assignment_type, target_ids, due_date, start_date, max_attempts, is_mandatory, notification_sent, created_at, updated_at) FROM stdin;
\.


--
-- TOC entry 5687 (class 0 OID 48620)
-- Dependencies: 226
-- Data for Name: assessment_calibration; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY public.assessment_calibration (id, assessment_id, examiner_id, calibration_factor, difficulty_adjustment, sample_size, mean_score, std_deviation, last_calibrated_at, created_at, updated_at) FROM stdin;
\.


--
-- TOC entry 5688 (class 0 OID 48629)
-- Dependencies: 227
-- Data for Name: assessment_question_links; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY public.assessment_question_links (id, assessment_id, question_bank_id, inline_question_id, order_index, is_required, points_override, created_at, updated_at) FROM stdin;
\.


--
-- TOC entry 5689 (class 0 OID 48637)
-- Dependencies: 228
-- Data for Name: assignment_group_members; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY public.assignment_group_members (id, group_id, learner_id, role, joined_at) FROM stdin;
\.


--
-- TOC entry 5690 (class 0 OID 48645)
-- Dependencies: 229
-- Data for Name: assignment_groups; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY public.assignment_groups (id, assignment_id, group_name, created_at, created_by, updated_at) FROM stdin;
\.


--
-- TOC entry 5691 (class 0 OID 48652)
-- Dependencies: 230
-- Data for Name: assignment_learners; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY public.assignment_learners (id, assignment_id, learner_id, assigned_at, assigned_by) FROM stdin;
\.


--
-- TOC entry 5692 (class 0 OID 48657)
-- Dependencies: 231
-- Data for Name: assignment_submission_history; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY public.assignment_submission_history (id, submission_id, action, action_by, action_at, notes, previous_status, new_status, submission_snapshot) FROM stdin;
\.


--
-- TOC entry 5693 (class 0 OID 48664)
-- Dependencies: 232
-- Data for Name: assignment_submissions; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY public.assignment_submissions (id, assignment_id, student_id, submission_url, submission_text, score, feedback, submitted_at, graded_at, graded_by, file_url, file_name, points_awarded, group_id, is_group_submission, status, rejection_reason, submission_version, batch_assignment_id) FROM stdin;
\.


--
-- TOC entry 5694 (class 0 OID 48675)
-- Dependencies: 233
-- Data for Name: assignments; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY public.assignments (id, title, description, batch_id, created_by, due_date, max_score, created_at, updated_at) FROM stdin;
\.


--
-- TOC entry 5695 (class 0 OID 48683)
-- Dependencies: 234
-- Data for Name: attendance; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY public.attendance (id, batch_id, student_id, session_date, status, notes, marked_by, created_at, attentiveness_score, updated_at) FROM stdin;
\.


--
-- TOC entry 5696 (class 0 OID 48692)
-- Dependencies: 235
-- Data for Name: audit_logs; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY public.audit_logs (id, user_id, action_type, entity_type, entity_id, old_values, new_values, ip_address, user_agent, session_id, created_at, updated_at) FROM stdin;
\.


--
-- TOC entry 5697 (class 0 OID 48699)
-- Dependencies: 236
-- Data for Name: auto_assignment_rules; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY public.auto_assignment_rules (id, name, description, rule_type, trigger_conditions, action_type, action_target_id, priority, is_active, created_by, created_at, updated_at) FROM stdin;
\.


--
-- TOC entry 5698 (class 0 OID 48710)
-- Dependencies: 237
-- Data for Name: badge_definitions; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY public.badge_definitions (id, name, description, icon, image_url, rarity, criteria_type, criteria, points_reward, is_active, created_by, created_at, updated_at) FROM stdin;
af50eee5-62b4-4079-a9db-5cbdca2ab9fe	Course Complete	To be awarded when the learner completes the course	🏆	\N	common	automatic	{"type":"courses_completed","count":1}	100	t	\N	2026-03-30 18:31:44.163396+05:30	2026-03-30 18:31:44.163396+05:30
4416d994-8799-42c6-8466-a9208ec99e79	Module Completed	To be awarded when the leanrer completes the Module!	🏅	\N	common	automatic	{"type":"modules_completed","count":1}	10	t	\N	2026-03-30 18:32:42.429995+05:30	2026-03-30 18:32:42.429995+05:30
\.


--
-- TOC entry 5699 (class 0 OID 48725)
-- Dependencies: 238
-- Data for Name: batch_assignments; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY public.batch_assignments (id, batch_id, module_content_id, title, description, start_date, due_date, max_score, created_at, created_by, points_on_time, points_early_submission, points_full_marks, is_gradeable, grading_scale, passing_score, assignment_type, media_url, media_type, instructions, assign_to_all, is_group_assignment, min_group_size, max_group_size, updated_at) FROM stdin;
975fa46a-1464-4ec9-a259-5c21477d2716	4ac72f4e-b51b-4c54-9868-8b202c568e50	\N	Assignment1	\N	2026-03-31	2026-04-03	100	2026-03-30 20:08:05.099229+05:30	82b57d97-ff21-4121-a794-352e627c95c4	10	15	25	t	percentage	60	classwork	\N	\N	mallik	t	f	2	5	2026-03-30 20:08:05.099229+05:30
\.


--
-- TOC entry 5700 (class 0 OID 48744)
-- Dependencies: 239
-- Data for Name: batch_lesson_plans; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY public.batch_lesson_plans (id, batch_id, module_content_id, sequence_order, title, notes, status, completed_at, completed_by, created_at, planned_duration_minutes, updated_at) FROM stdin;
\.


--
-- TOC entry 5701 (class 0 OID 48754)
-- Dependencies: 240
-- Data for Name: batch_mentors; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY public.batch_mentors (id, batch_id, mentor_id, created_at, created_by, updated_at) FROM stdin;
fcfd67cc-e5ef-445a-aa26-1c8b2204735f	4ac72f4e-b51b-4c54-9868-8b202c568e50	82b57d97-ff21-4121-a794-352e627c95c4	2026-03-31 14:00:00.700301+05:30	82b57d97-ff21-4121-a794-352e627c95c4	2026-03-31 14:00:00.700301+05:30
896ececc-f6bf-42f5-8a5d-5f421e275afd	6df7e3c2-8add-4545-aa2d-fbd3a5a9d897	50e5aa4e-e0bb-471d-9ce5-69a33aae63d2	2026-03-31 14:00:30.405789+05:30	82b57d97-ff21-4121-a794-352e627c95c4	2026-03-31 14:00:30.405789+05:30
\.


--
-- TOC entry 5702 (class 0 OID 48759)
-- Dependencies: 241
-- Data for Name: batch_schedule; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY public.batch_schedule (id, batch_id, session_date, start_time, end_time, topic, description, recurrence_type, created_at, created_by, meeting_link, meeting_id, meeting_provider, meeting_integration_id, updated_at) FROM stdin;
9c17da79-34ec-4742-8ff4-97857e0e10e1	4ac72f4e-b51b-4c54-9868-8b202c568e50	2026-04-03	10:00:00	11:00:00		\N	daily	2026-03-30 17:28:31.322707+05:30	82b57d97-ff21-4121-a794-352e627c95c4	\N	\N	\N	\N	2026-03-30 17:28:31.322707+05:30
8952a25f-079c-4695-8ed0-ea12c34d01d2	4ac72f4e-b51b-4c54-9868-8b202c568e50	2026-04-02	10:00:00	11:00:00		\N	daily	2026-03-30 17:28:31.322707+05:30	82b57d97-ff21-4121-a794-352e627c95c4	\N	\N	\N	\N	2026-03-30 17:28:31.322707+05:30
cf925ab0-cd1f-4d06-bda4-0cd426b64da1	4ac72f4e-b51b-4c54-9868-8b202c568e50	2026-04-06	10:00:00	11:00:00		\N	daily	2026-03-30 17:28:31.322707+05:30	82b57d97-ff21-4121-a794-352e627c95c4	\N	\N	\N	\N	2026-03-30 17:28:31.322707+05:30
13d5f396-205c-4857-bcc2-4d2becef1c52	4ac72f4e-b51b-4c54-9868-8b202c568e50	2026-04-05	10:00:00	11:00:00		\N	daily	2026-03-30 17:28:31.322707+05:30	82b57d97-ff21-4121-a794-352e627c95c4	\N	\N	\N	\N	2026-03-30 17:28:31.322707+05:30
3bfef8da-fec3-4a2e-933e-671a483eb5f6	4ac72f4e-b51b-4c54-9868-8b202c568e50	2026-04-04	10:00:00	11:00:00		\N	daily	2026-03-30 17:28:31.322707+05:30	82b57d97-ff21-4121-a794-352e627c95c4	\N	\N	\N	\N	2026-03-30 17:28:31.322707+05:30
0d1207ba-5aad-40ba-a26a-e8725bef9603	4ac72f4e-b51b-4c54-9868-8b202c568e50	2026-04-01	10:00:00	11:00:00		\N	daily	2026-03-30 17:28:31.322707+05:30	82b57d97-ff21-4121-a794-352e627c95c4	\N	\N	\N	\N	2026-03-30 17:28:31.322707+05:30
3f44e15d-c853-47db-aa1b-a49d4d633f1c	4ac72f4e-b51b-4c54-9868-8b202c568e50	2026-04-07	10:00:00	11:00:00		\N	daily	2026-03-30 17:28:31.337696+05:30	82b57d97-ff21-4121-a794-352e627c95c4	\N	\N	\N	\N	2026-03-30 17:28:31.337696+05:30
71407d7a-a99f-4163-a746-e4821cca1cd3	6df7e3c2-8add-4545-aa2d-fbd3a5a9d897	2026-04-06	10:00:00	11:00:00		\N	daily	2026-03-31 16:59:29.916345+05:30	82b57d97-ff21-4121-a794-352e627c95c4	\N	\N	\N	\N	2026-03-31 16:59:29.916345+05:30
6b8d0cd0-88b5-4aa8-84c3-5f0f468e2a66	6df7e3c2-8add-4545-aa2d-fbd3a5a9d897	2026-04-03	10:00:00	11:00:00		\N	daily	2026-03-31 16:59:29.916345+05:30	82b57d97-ff21-4121-a794-352e627c95c4	\N	\N	\N	\N	2026-03-31 16:59:29.916345+05:30
1440edd5-a193-4ee0-8f18-61f612af3e61	6df7e3c2-8add-4545-aa2d-fbd3a5a9d897	2026-04-02	10:00:00	11:00:00		\N	daily	2026-03-31 16:59:29.916346+05:30	82b57d97-ff21-4121-a794-352e627c95c4	\N	\N	\N	\N	2026-03-31 16:59:29.916346+05:30
af2bed7b-ccb1-4a29-b241-79fa8b20940d	6df7e3c2-8add-4545-aa2d-fbd3a5a9d897	2026-04-01	10:00:00	11:00:00		\N	daily	2026-03-31 16:59:29.916345+05:30	82b57d97-ff21-4121-a794-352e627c95c4	\N	\N	\N	\N	2026-03-31 16:59:29.916345+05:30
e4328bcc-1b0d-4953-8bca-e1d9582bbce1	6df7e3c2-8add-4545-aa2d-fbd3a5a9d897	2026-04-05	10:00:00	11:00:00		\N	daily	2026-03-31 16:59:29.916345+05:30	82b57d97-ff21-4121-a794-352e627c95c4	\N	\N	\N	\N	2026-03-31 16:59:29.916345+05:30
8d53059f-0e16-4304-9194-f42b39fd748a	6df7e3c2-8add-4545-aa2d-fbd3a5a9d897	2026-04-04	10:00:00	11:00:00		\N	daily	2026-03-31 16:59:29.916346+05:30	82b57d97-ff21-4121-a794-352e627c95c4	\N	\N	\N	\N	2026-03-31 16:59:29.916346+05:30
66c3eedd-bef9-4243-b303-792620b32600	6df7e3c2-8add-4545-aa2d-fbd3a5a9d897	2026-04-07	10:00:00	11:00:00		\N	daily	2026-03-31 16:59:29.931159+05:30	82b57d97-ff21-4121-a794-352e627c95c4	\N	\N	\N	\N	2026-03-31 16:59:29.931159+05:30
\.


--
-- TOC entry 5703 (class 0 OID 48767)
-- Dependencies: 242
-- Data for Name: batch_students; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY public.batch_students (id, batch_id, student_id, enrolled_at) FROM stdin;
4478f240-54d7-4664-a47d-c3ec6ae3e4aa	4ac72f4e-b51b-4c54-9868-8b202c568e50	360cb5f1-42a4-4a8c-b16c-988069b90fed	2026-03-31 13:41:38.731715+05:30
295d5458-d898-4f49-b400-d3d2e9d4fd77	6df7e3c2-8add-4545-aa2d-fbd3a5a9d897	6bd17ff7-6568-4789-bd7e-0b9b057eb5fe	2026-03-31 13:46:43.775038+05:30
2a429cca-4668-466c-8b5a-27492359005e	6df7e3c2-8add-4545-aa2d-fbd3a5a9d897	56821a90-157d-4626-acfc-eccf24c074b8	2026-03-31 13:46:43.775122+05:30
069866f2-7922-43f9-8623-ee0d1472d603	4ac72f4e-b51b-4c54-9868-8b202c568e50	56821a90-157d-4626-acfc-eccf24c074b8	2026-03-31 16:55:40.759044+05:30
6bdb8792-a50b-409a-8d77-3cef3d5c17e4	4ac72f4e-b51b-4c54-9868-8b202c568e50	6bd17ff7-6568-4789-bd7e-0b9b057eb5fe	2026-03-31 16:55:40.75904+05:30
1dbc9818-dcf7-4e57-bfd5-df8a23505c5d	4ac72f4e-b51b-4c54-9868-8b202c568e50	2dbf29ad-bb2d-4ff7-ba7a-6cb61d325b47	2026-03-31 16:55:40.75904+05:30
c98d610e-bcac-4cf5-8991-d412a25ebeda	6df7e3c2-8add-4545-aa2d-fbd3a5a9d897	d9e49dae-080b-41e7-9bed-e33634463f3e	2026-04-01 12:42:32.037923+05:30
acfaf397-d04c-4a31-97fd-7c76ced6f17f	9d23a3e1-bdda-42de-b65b-7b7ed1640174	d9e49dae-080b-41e7-9bed-e33634463f3e	2026-04-01 14:19:31.382615+05:30
\.


--
-- TOC entry 5704 (class 0 OID 48772)
-- Dependencies: 243
-- Data for Name: batch_trainers; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY public.batch_trainers (id, batch_id, trainer_id, is_lead, role, created_at, updated_at) FROM stdin;
\.


--
-- TOC entry 5705 (class 0 OID 48781)
-- Dependencies: 244
-- Data for Name: batches; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY public.batches (id, name, description, course_id, trainer_id, start_date, end_date, max_students, created_by, created_at, batch_code, updated_at) FROM stdin;
4ac72f4e-b51b-4c54-9868-8b202c568e50	PhyBatch1	Desc	92d67d86-5d58-4321-ba98-39986abb982a	50e5aa4e-e0bb-471d-9ce5-69a33aae63d2	2026-04-01	2026-04-07	10	82b57d97-ff21-4121-a794-352e627c95c4	2026-03-30 17:28:31.30072+05:30	92D-F3003EBE	2026-03-30 17:28:31.30072+05:30
6df7e3c2-8add-4545-aa2d-fbd3a5a9d897	BatChem	Desc	5b8a74d5-4d13-42c3-af9e-a25fe90055aa	a1cf1f9c-e141-41cc-84e2-c86c2ffba28b	2026-04-01	2026-04-07	3	82b57d97-ff21-4121-a794-352e627c95c4	2026-03-31 13:46:26.122732+05:30	5B8-11610303	2026-03-31 16:59:29.847869+05:30
9d23a3e1-bdda-42de-b65b-7b7ed1640174	BatchBio1	Desc	ee1a028f-3703-420a-b863-181cf2a1aec4	a1cf1f9c-e141-41cc-84e2-c86c2ffba28b	2026-04-01	2026-04-25	30	82b57d97-ff21-4121-a794-352e627c95c4	2026-04-01 14:17:51.513517+05:30	EE1-B1BF82CE	2026-04-01 14:17:51.513517+05:30
\.


--
-- TOC entry 5706 (class 0 OID 48788)
-- Dependencies: 245
-- Data for Name: branding_settings; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY public.branding_settings (id, setting_key, setting_value, created_at, updated_at) FROM stdin;
d707a8e5-d9d1-419a-abd8-042bfa1cdcb1	general_settings	{"siteName":"Jumpstart LMS","siteDescription":"Corporate Learning Management System","allowSelfEnrollment":true,"requireApproval":false,"emailNotifications":true,"certificatesEnabled":true}	2026-03-30 19:18:57.994722+05:30	2026-03-30 19:29:22.331002+05:30
\.


--
-- TOC entry 5707 (class 0 OID 48797)
-- Dependencies: 246
-- Data for Name: breakout_room_participants; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY public.breakout_room_participants (id, room_id, user_id, joined_at, left_at, role) FROM stdin;
\.


--
-- TOC entry 5708 (class 0 OID 48805)
-- Dependencies: 247
-- Data for Name: breakout_rooms; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY public.breakout_rooms (id, session_id, room_name, room_topic, max_participants, duration_minutes, status, started_at, ended_at, created_at, updated_at) FROM stdin;
\.


--
-- TOC entry 5709 (class 0 OID 48815)
-- Dependencies: 248
-- Data for Name: bundle_courses; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY public.bundle_courses (id, bundle_id, course_id, order_index) FROM stdin;
a1cc5221-8ee8-4319-8cfb-e3ce002c47de	eb72476f-9a11-4a86-b100-27fdd5527b05	5b8a74d5-4d13-42c3-af9e-a25fe90055aa	0
fb8cfe23-cab4-4d83-b520-1363ef1bbbdb	eb72476f-9a11-4a86-b100-27fdd5527b05	92d67d86-5d58-4321-ba98-39986abb982a	1
320be4eb-03b1-496f-a999-71608917ada3	05ec6f38-4871-4b77-8e92-8df8532174a6	5b8a74d5-4d13-42c3-af9e-a25fe90055aa	1
0442fe32-0fc9-4ecf-b60d-2685885d55d0	05ec6f38-4871-4b77-8e92-8df8532174a6	ee1a028f-3703-420a-b863-181cf2a1aec4	0
\.


--
-- TOC entry 5710 (class 0 OID 48820)
-- Dependencies: 249
-- Data for Name: bundle_enrollments; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY public.bundle_enrollments (id, bundle_id, user_id, enrolled_at, status, completed_at) FROM stdin;
\.


--
-- TOC entry 5711 (class 0 OID 48828)
-- Dependencies: 250
-- Data for Name: certificate_templates; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY public.certificate_templates (id, name, description, template_data, background_url, is_default, created_by, created_at, updated_at) FROM stdin;
c1c52400-79ea-4e0b-8e00-4a824edf57fc	course_completed	\N	[{"id":"el-1774875921234","type":"placeholder","content":"{{learner_name}}","x":316.0217654171705,"y":162.21281741233375,"width":200,"height":40,"fontSize":16,"fontWeight":"normal","textAlign":"center","color":"#000000"},{"id":"el-1774875926818","type":"placeholder","content":"{{course_title}}","x":50,"y":50,"width":200,"height":40,"fontSize":16,"fontWeight":"normal","textAlign":"center","color":"#000000"},{"id":"el-1774875927034","type":"placeholder","content":"{{course_title}}","x":309.25030229746073,"y":205.74365175332528,"width":200,"height":40,"fontSize":16,"fontWeight":"normal","textAlign":"center","color":"#000000"},{"id":"el-1774875932802","type":"placeholder","content":"{{completion_date}}","x":50,"y":50,"width":200,"height":40,"fontSize":16,"fontWeight":"normal","textAlign":"center","color":"#000000"},{"id":"el-1774875932994","type":"placeholder","content":"{{completion_date}}","x":319.89117291414755,"y":251.20918984280536,"width":200,"height":40,"fontSize":16,"fontWeight":"normal","textAlign":"center","color":"#000000"}]	/api/v1/files/download/certificate/f503b18a-8f61-4b31-8d62-c6fc54c2929b_RE2GuWL_1920x1080.jpg	\N	\N	2026-03-30 18:38:31.722228+05:30	2026-03-30 18:38:31.722228+05:30
8c615bac-7ce5-4767-a82c-e826fd386ff4	Module Completed	\N	[{"id":"el-1774876682689","type":"placeholder","content":"{{learner_name}}","x":267.65417170495766,"y":40.32648125755743,"width":200,"height":40,"fontSize":40,"fontWeight":"normal","textAlign":"center","color":"#000000"},{"id":"el-1774876692185","type":"placeholder","content":"{{completion_date}}","x":309.25030229746073,"y":204.77629987908102,"width":200,"height":40,"fontSize":25,"fontWeight":"normal","textAlign":"center","color":"#000000"}]	data:image/jpeg;base64,[encoded data, 333115 characters]