Performance Evaluation Results

CREATE TABLE employee (
  id            UUID PRIMARY KEY,
  name          TEXT NOT NULL,
  role          TEXT NOT NULL,      -- 'UX Designer (O1)'
  team          TEXT NOT NULL,
  tenure_months INTEGER NOT NULL,
  avatar        TEXT,               -- emoji or asset ref
  accent_color  TEXT                -- hex, for compare theming
);

CREATE TABLE evaluation_cycle (
  id            UUID PRIMARY KEY,
  employee_id   UUID NOT NULL REFERENCES employee(id),
  year          SMALLINT NOT NULL,
  label         TEXT NOT NULL,      -- 'ประจำปี 2025'
  synthesis     TEXT NOT NULL,
  status        TEXT NOT NULL DEFAULT 'completed'
                CHECK (status IN ('completed')),
  percentile    SMALLINT NOT NULL CHECK (percentile BETWEEN 0 AND 100),
  peer_role_label TEXT NOT NULL,
  UNIQUE (employee_id, year)
);

CREATE TABLE dimension (
  id            UUID PRIMARY KEY,
  cycle_id      UUID NOT NULL REFERENCES evaluation_cycle(id) ON DELETE CASCADE,
  name          TEXT NOT NULL,      -- 'การส่งมอบงาน'
  weight        SMALLINT NOT NULL CHECK (weight BETWEEN 0 AND 100),
  final_score   NUMERIC(2,1) NOT NULL CHECK (final_score BETWEEN 0 AND 5),
  comment       TEXT,
  sort_order    SMALLINT NOT NULL
);

CREATE TABLE topic (
  id            UUID PRIMARY KEY,
  dimension_id  UUID NOT NULL REFERENCES dimension(id) ON DELETE CASCADE,
  name          TEXT NOT NULL,
  weight        SMALLINT NOT NULL,
  sort_order    SMALLINT NOT NULL
);

-- One row per source, for a dimension OR a topic (exactly one FK set).
CREATE TABLE source_score (
  id            UUID PRIMARY KEY,
  dimension_id  UUID REFERENCES dimension(id) ON DELETE CASCADE,
  topic_id      UUID REFERENCES topic(id) ON DELETE CASCADE,
  source        TEXT NOT NULL CHECK (source IN ('self','manager','peer','org')),
  score         NUMERIC(2,1) NOT NULL CHECK (score BETWEEN 0 AND 5),
  CHECK ((dimension_id IS NULL) <> (topic_id IS NULL)),
  UNIQUE (dimension_id, source),
  UNIQUE (topic_id, source)
);

CREATE TABLE peer_bucket (
  id            UUID PRIMARY KEY,
  cycle_id      UUID NOT NULL REFERENCES evaluation_cycle(id) ON DELETE CASCADE,
  range_label   TEXT NOT NULL,      -- '80–90'
  headcount     SMALLINT NOT NULL,
  contains_you  BOOLEAN NOT NULL DEFAULT FALSE,
  sort_order    SMALLINT NOT NULL
);

CREATE TABLE decision (
  id            UUID PRIMARY KEY,
  cycle_id      UUID NOT NULL REFERENCES evaluation_cycle(id) ON DELETE CASCADE,
  manager_id    UUID NOT NULL REFERENCES employee(id),
  merit_pct     NUMERIC(4,1),      -- nullable until recorded
  bonus_mult    NUMERIC(3,1),
  notes         TEXT DEFAULT '',
  updated_at    TIMESTAMPTZ NOT NULL,
  UNIQUE (cycle_id, manager_id)
);

weightedMean = Σ(dim.final × dim.weight) / Σ(dim.weight)   // 0..5
overallPct   = round(weightedMean × 20)                    // 0..100

merit = meritRange.max                          // default: top of band
if (trend != 'up' && percentile > 25)
    merit = meritRange.min                      // not rising & not top-quartile

blindSpot = any dimension has the amber 'อาจเป็นจุดบอด' flag

if   band == 'Excellence' && trend == 'up' && percentile <= 25
     → ready = 'ready'   'พร้อมเลื่อนตำแหน่ง'
elif band in ('Excellence','Exceeds') && trend == 'up'
     → ready = 'soon'    blindSpot ? 'เตรียมเลื่อน — ปิด gap ก่อน' : 'เตรียมเลื่อนภายใน 6 เดือน'
else
     → ready = 'hold'    'พัฒนาก่อนพิจารณาเลื่อนตำแหน่ง'

{
  "employee": { "id", "name", "role", "team", "tenure", "avatar", "accent" },
  "year": 2025, "label": "ประจำปี 2025", "synthesis": "…",
  "overallPct": 84,          // §5.1
  "grade": "Exceeds", "gradeTh": "เกินความคาดหวัง",   // §5.2
  "trendDir": "up",           // §5.3
  "prevYear": 2024,          // or null — §5.4
  "percentile": 22, "peerRoleLabel": "สาย UX/Design (28 คน)",
  "peerBuckets": [{ "range", "count", "you" }],
  "dimensions": [{
     "name", "weight", "final",
     "sources": { "self", "manager", "peer", "org" },
     "topics": [...], "comment",
     "gapFlag": { "label", "color", "tip" } | null   // §5.5
  }],
  "recommendation": { "merit", "bonus", "promo", "ready" },  // §5.7
  "decision": { "merit", "bonus", "notes", "updatedAt" }
}