-- ============================================================
-- 003_cashier_ratings.sql
-- Rating kasir dari landing page (3 level: 1/2/3)
-- ============================================================

CREATE TABLE IF NOT EXISTS cashier_ratings (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id INT UNSIGNED NOT NULL,
  location_id INT UNSIGNED NULL,
  cashier_user_id INT UNSIGNED NOT NULL,

  -- 1 = kurang puas, 2 = puas, 3 = sangat puas
  score TINYINT UNSIGNED NOT NULL,

  comment VARCHAR(500) NULL,

  -- optional: kalau rating dikaitkan ke transaksi
  invoice_no VARCHAR(64) NULL,

  -- anti-spam ringan (hash saja, bukan raw IP)
  ip_hash VARBINARY(32) NULL,
  ua_hash VARBINARY(32) NULL,

  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY (id),

  KEY idx_cr_tenant_date (tenant_id, created_at),
  KEY idx_cr_cashier_date (cashier_user_id, created_at),
  KEY idx_cr_location_date (location_id, created_at),

  -- prevent duplicate rating per invoice per kasir (kalau invoice_no dipakai)
  UNIQUE KEY uq_cr_invoice_cashier (tenant_id, invoice_no, cashier_user_id),

  CONSTRAINT chk_cr_score CHECK (score IN (1,2,3))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Foreign keys (optional tapi recommended)
-- Kalau kamu takut existing data belum rapi, boleh comment dulu bagian FK ini.
ALTER TABLE cashier_ratings
  ADD CONSTRAINT fk_cr_tenant
    FOREIGN KEY (tenant_id) REFERENCES tenants(id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT fk_cr_location
    FOREIGN KEY (location_id) REFERENCES locations(id)
    ON DELETE SET NULL ON UPDATE CASCADE,
  ADD CONSTRAINT fk_cr_cashier
    FOREIGN KEY (cashier_user_id) REFERENCES users(id)
    ON DELETE CASCADE ON UPDATE CASCADE;