-- Temporary standalone identity mapping. Core remains the owner of users and SSO in platform mode.
-- Raw access tokens are never stored; only a SHA-256 digest and a short non-secret hint are kept.

CREATE TABLE staff_standalone_employee_access (
  id VARCHAR(26) PRIMARY KEY CHECK (id ~ '^[0-9A-HJKMNP-TV-Z]{26}$'),
  tenant_id VARCHAR(26) NOT NULL CHECK (tenant_id ~ '^[0-9A-HJKMNP-TV-Z]{26}$'),
  employee_id VARCHAR(26) NOT NULL,
  subject_id VARCHAR(26) NOT NULL CHECK (subject_id ~ '^[0-9A-HJKMNP-TV-Z]{26}$'),
  token_hash CHAR(64) NOT NULL CHECK (token_hash ~ '^[0-9a-f]{64}$'),
  token_hint VARCHAR(8) NOT NULL,
  created_by VARCHAR(26) NOT NULL CHECK (created_by ~ '^[0-9A-HJKMNP-TV-Z]{26}$'),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  rotated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (tenant_id, id),
  UNIQUE (tenant_id, employee_id),
  UNIQUE (tenant_id, subject_id),
  UNIQUE (tenant_id, token_hash),
  FOREIGN KEY (tenant_id, employee_id) REFERENCES staff_employees (tenant_id, id) ON DELETE CASCADE
);

ALTER TABLE staff_standalone_employee_access ENABLE ROW LEVEL SECURITY;
ALTER TABLE staff_standalone_employee_access FORCE ROW LEVEL SECURITY;
CREATE POLICY staff_standalone_employee_access_tenant_isolation
  ON staff_standalone_employee_access
  USING (tenant_id = current_setting('app.tenant_id', true))
  WITH CHECK (tenant_id = current_setting('app.tenant_id', true));

GRANT SELECT, INSERT, UPDATE, DELETE ON staff_standalone_employee_access TO krona_staff_app;

ALTER TABLE staff_payroll_ledger ADD COLUMN external_key TEXT;
CREATE UNIQUE INDEX staff_payroll_ledger_external_key_idx
  ON staff_payroll_ledger (tenant_id, external_key) WHERE external_key IS NOT NULL;