-- Phase 5: Grading scales — custom grade definitions per store

CREATE TABLE "grading_scale" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  "name" varchar(255) NOT NULL,
  "description" text,
  "is_default" boolean NOT NULL DEFAULT false,
  "created_by" uuid REFERENCES "user"("id"),
  "is_active" boolean NOT NULL DEFAULT true,
  "created_at" timestamptz NOT NULL DEFAULT now(),
  "updated_at" timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE "grading_scale_level" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  "grading_scale_id" uuid NOT NULL REFERENCES "grading_scale"("id"),
  "value" varchar(50) NOT NULL,
  "label" varchar(255) NOT NULL,
  "numeric_value" integer NOT NULL,
  "color_hex" varchar(7),
  "sort_order" integer NOT NULL
);
