{
  "survey-core": "^2.3.11",
  "survey-creator-core": "^2.3.11",
  "survey-creator-react": "^2.3.11",
  "survey-react-ui": "^2.3.11"
}

create table "public"."surveys" (
    "id" uuid not null default gen_random_uuid(),
    "survey_id" uuid not null default gen_random_uuid(),   -- logical id, see note below
    "class_id" bigint not null,
    "created_by" uuid not null,
    "title" text not null,
    "description" text,
    "json" jsonb not null default '[]'::jsonb,
    "status" survey_status not null default 'draft'::survey_status,
    "allow_response_editing" boolean not null default false,
    "due_date" timestamp with time zone,
    "validation_errors" text,
    "created_at" timestamp with time zone not null default now(),
    "updated_at" timestamp with time zone not null default now(),
    "deleted_at" timestamp with time zone,
    "version" integer not null default 1,
    "type" survey_type not null default 'assign_all'::survey_type,
    "assigned_to_all" boolean not null default true
);

-- Added by 20260222000000_survey_assignment_grading.sql
alter table "public"."surveys" add column "assignment_id" bigint;        -- :12
alter table "public"."surveys" add column "available_at" timestamptz;    -- :28

-- Added by 20260222000002_survey_analytics_enhanced.sql
alter table public.surveys add column if not exists series_id uuid       -- :85
  references public.survey_series(id) on delete set null;
alter table public.surveys add column if not exists series_ordinal integer;                -- :87
alter table public.surveys add column if not exists analytics_config jsonb default '{}'::jsonb;  -- :94

CREATE TABLE IF NOT EXISTS public.survey_series (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  class_id bigint NOT NULL REFERENCES public.classes(id) ON DELETE CASCADE,
  name text NOT NULL,
  description text,
  created_at timestamptz DEFAULT now() NOT NULL,
  created_by uuid REFERENCES public.profiles(id),
  CONSTRAINT survey_series_unique_name UNIQUE (class_id, name)
);

create table "public"."survey_responses" (
    "id" uuid not null default gen_random_uuid(),
    "survey_id" uuid not null,
    "profile_id" uuid not null,
    "response" jsonb not null default '{}'::jsonb,
    "submitted_at" timestamp with time zone,
    "is_submitted" boolean not null default false,
    "created_at" timestamp with time zone not null default now(),
    "updated_at" timestamp with time zone not null default now(),
    "deleted_at" timestamp with time zone
);

create table "public"."survey_assignments" (
    "id" uuid not null default gen_random_uuid(),
    "survey_id" uuid not null,
    "profile_id" uuid not null,
    "created_at" timestamp with time zone not null default now(),
    "class_id" bigint not null
);

create table "public"."survey_templates" (
    "id" uuid not null default gen_random_uuid(),
    "title" text not null,
    "description" text not null default ''::text,
    "template" jsonb not null default '{}'::jsonb,
    "created_at" timestamp with time zone not null default now(),
    "updated_at" timestamp with time zone not null default now(),
    "created_by" uuid not null,
    "version" integer not null default 1,
    "scope" template_scope not null default 'course'::template_scope,
    "class_id" bigint not null
);

create type "public"."survey_status" as enum ('draft', 'published', 'closed');
create type "public"."survey_type" as enum ('assign_all', 'specific', 'peer');
create type "public"."template_scope" as enum ('global', 'course');

SELECT soft_delete_survey(p_survey_id := '<surveys.id>', p_survey_logical_id := '<surveys.survey_id>');

SELECT create_survey_assignments('<surveys.id>', ARRAY['<profile-1>', '<profile-2>']::uuid[]);

-- Restore a soft-deleted survey and its responses. No UI does this.
UPDATE surveys SET deleted_at = NULL WHERE survey_id = '<logical-id>';
UPDATE survey_responses SET deleted_at = NULL
  WHERE survey_id IN (SELECT id FROM surveys WHERE survey_id = '<logical-id>');

CREATE OR REPLACE FUNCTION public.can_respond_to_survey(p_survey_id uuid, p_profile_id uuid)
RETURNS boolean
LANGUAGE sql
STABLE
SECURITY DEFINER
SET search_path = public, pg_temp
AS $$
  SELECT EXISTS (
    SELECT 1
    FROM public.surveys s
    JOIN public.user_privileges up
      ON up.class_id = s.class_id
     AND up.user_id = auth.uid()
     AND (up.private_profile_id = p_profile_id OR up.public_profile_id = p_profile_id)
    WHERE s.id = p_survey_id
      AND s.deleted_at IS NULL
      AND s.status = 'published'::survey_status
      AND (s.available_at IS NULL OR s.available_at <= now())
      AND (
        s.assigned_to_all
        OR EXISTS (
          SELECT 1
          FROM public.survey_assignments sa
          WHERE sa.survey_id = s.id
            AND (sa.profile_id = up.private_profile_id OR sa.profile_id = up.public_profile_id)
        )
      )
  );
$$;

-- Let graders soft-delete responses in their own classes.
CREATE POLICY survey_responses_update_staff ON public.survey_responses
  FOR UPDATE TO authenticated
  USING (
    EXISTS (
      SELECT 1 FROM public.surveys s
      WHERE s.id = survey_responses.survey_id
        AND authorizeforclassgrader(s.class_id)
    )
  )
  WITH CHECK (
    EXISTS (
      SELECT 1 FROM public.surveys s
      WHERE s.id = survey_responses.survey_id
        AND authorizeforclassgrader(s.class_id)
    )
  );

// components/Survey.tsx:41-71
const surveyJsonKey = useMemo(() => JSON.stringify(surveyJson ?? null), [surveyJson]);

const survey = useMemo(() => {
  const model = new Model(JSON.parse(surveyJsonKey));
  model.questionTitleLocation = "top";
  model.focusFirstQuestionAutomatic = false;
  model.textUpdateMode = "onTyping";
  model.onGetTitleTagName.add((_sender, options) => {
    if ((options.element as { isQuestion?: boolean }).isQuestion) {
      options.tagName = "h2";
    }
  });
  return model;
}, [surveyJsonKey]);

{
  "pages": [
    {
      "name": "page1",
      "elements": [
        { "type": "text", "name": "question1", "title": "Name" },
        {
          "type": "rating",
          "name": "satisfaction-numeric",
          "title": "How satisfied are you with the course?",
          "description": "Numeric rating scale",
          "rateValues": [1, 2, 3, 4, 5, 6, 7, 8, 9, 10]
        }
      ]
    }
  ]
}

// components/survey/SurveyBuilderDataTypes.ts:19
export type ElementType = "text" | "comment" | "checkbox" | "radiogroup" | "boolean";

default: {
  const base = makeElement("text", elem?.name as string | undefined);
  return {
    ...base,
    name: isNonEmptyString(elem?.name) ? elem.name : base.name,
    title: (elem?.title as string | undefined) ?? base.title,
    // ... description, isRequired, validators
    inputType: "text",
    config: restWithoutKeys(elem, [
      "id", "type", "name", "title", "description", "isRequired", "validators", "inputType"
    ])
  };
}

// components/survey/SurveyAnalytics.tsx:82
allQuestions.filter((q) => ["rating", "nouislider", "text", "radiogroup", "checkbox"].includes(q.type))

// app/course/[course_id]/surveys/[survey_id]/page.tsx:143-179
const saveResponseToDb = useCallback(
  async (responseData: ResponseData, isSubmitted: boolean) => {
    if (!surveyId || !private_profile_id) return;
    // Never downgrade a submitted response back to a draft.
    if (!isSubmitted && hasSubmittedRef.current) return;

    const upsertData = {
      survey_id: surveyId,
      profile_id: private_profile_id,
      response: responseData,
      is_submitted: isSubmitted,
      ...(isSubmitted ? { submitted_at: new Date().toISOString() } : {})
    };

    const { data, error } = await controller.client
      .from("survey_responses")
      .upsert(upsertData, { onConflict: "survey_id,profile_id" })
      .select()
      .single();

    if (error) throw error;
    return data;
  },
  [surveyId, private_profile_id, controller.client]
);

// app/course/[course_id]/surveys/[survey_id]/page.tsx:236-260
const handleValueChanged = useCallback((surveyModel: Model) => {
  if (!private_profile_id || !surveyId || !allowResponseEditing) return;
  if (isSubmittingRef.current || hasSubmittedRef.current) return;
  // debounced by AUTOSAVE_DEBOUNCE_MS, trailing edge only
}, [private_profile_id, surveyId, allowResponseEditing, saveResponseToDb]);

// app/course/[course_id]/manage/surveys/[survey_id]/responses/SurveyResponsesView.tsx:97-118
function escapeCSVValue(value: unknown): string {
  if (value === null || value === undefined) {
    return "";
  }

  const stringValue = String(value);

  // Prevent CSV/Excel formula injection (OWASP/CWE-1236)
  const trimmed = stringValue.trimStart();
  if (["=", "+", "-", "@"].includes(trimmed[0] ?? "")) {
    return `"'${stringValue.replace(/"/g, '""')}"`;
  }

  if (stringValue.includes(",") || stringValue.includes('"') || stringValue.includes("\n")) {
    return `"${stringValue.replace(/"/g, '""')}"`;
  }

  return stringValue;
}

const surveys = useSurveys();                                  // :2664, staff, bare array
const survey = useSurvey(surveyId);                            // :2682
const { surveys, isLoading } = usePublishedSurveys();          // :2691, status published and not deleted
const { responses, isLoading } = useSurveyResponses(surveyId); // :2714
const series = useSurveySeries();                              // :556
const inSeries = useSurveysInSeries(seriesId);                 // :569
// hooks/useSurveyAnalytics.ts wraps get_survey_responses_with_full_context

app/course/[course_id]/
├── surveys/                                    # Student-facing
│   ├── page.tsx                                # Survey list
│   └── [survey_id]/page.tsx                    # Take survey; upsert and autosave live here
└── manage/surveys/                             # Staff-facing
    ├── page.tsx
    ├── ManageSurveysBody.tsx
    ├── SurveysTable.tsx                        # Calls soft_delete_survey
    ├── SurveysHeader.tsx
    ├── EmptySurveysState.tsx
    ├── new/
    │   ├── page.tsx                            # Create and update writes
    │   └── form.tsx                            # Shared by create and edit
    ├── preview/page.tsx
    ├── series/page.tsx
    └── [survey_id]/
        ├── edit/page.tsx                       # Reuses new/form.tsx
        └── responses/
            ├── page.tsx
            ├── SurveyResponsesView.tsx         # Table, filters, live CSV export
            ├── export.ts                       # Unused, unsafe duplicate
            └── [response_id]/page.tsx

components/survey/
├── SurveyBuilder.tsx                           # Visual builder
├── SurveyBuilderModal.tsx
├── SurveyBuilderDataTypes.ts                   # Five element types
├── factories.ts                                # makeElement, makePage, defaults
├── helpers.ts
├── serde.ts                                    # toJSON / fromJSON, the lossy branch
├── SurveyFilterButtons.tsx
├── SurveyTemplateLibraryModal.tsx
├── SurveyAnalytics.tsx
├── AnalyticsConfigEditor.tsx
├── SeriesManagementModal.tsx
├── SurveySeriesList.tsx
├── SurveyDataTypes.ts
└── analytics/                                  # Recharts components and utils

components/
├── Survey.tsx                                  # The SurveyJS wrapper; use this one
├── survey-preview-modal.tsx
└── ui/survey-status-banner.tsx

// types/survey.ts
type DbSurvey = Tables<"surveys">;
type DbSurveyResponse = Tables<"survey_responses">;
type DbSurveyAssignee = Tables<"survey_assignments">;

export type ResponseData = DbSurveyResponse["response"];
export type SurveyResponse = DbSurveyResponse;
export type Survey = DbSurvey;
export type SurveyAssignee = DbSurveyAssignee;

export type SurveyResponseWithProfile = SurveyResponse & {
  profiles: { id: string; name: string | null; sis_user_id?: string | null };
};

export type SurveyWithResponse = Survey & {
  response_status: "not_started" | "in_progress" | "completed";
  submitted_at?: string | null;
  is_submitted?: boolean;
};

export type SurveyWithCounts = Survey & {
  response_count: number;
  assigned_student_count: number;
};

export type SurveyWithAssignees = Survey & { assignees: SurveyAssignee[] };

# Playwright: tests/e2e
npx playwright test surveys                    # surveys.test.tsx
npx playwright test survey-assignment-grading  # survey-assignment-grading.test.tsx
npx playwright test survey-taking              # a11y-tasks/survey-taking__survey-complete.spec.ts

# Jest: tests/unit
npx jest survey-analytics-format
npx jest survey-component-a11y

npx supabase migration new your_migration_name
# edit supabase/migrations/<timestamp>_your_migration_name.sql
npx supabase db reset
npm run client-local          # regenerate utils/supabase/SupabaseTypes.d.ts