CREATE TABLE staff_access_grants (
  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}$'),
  role VARCHAR(32) NOT NULL CHECK (role IN ('point_manager', 'accountant', 'director')),
  org_unit_ids JSONB NOT NULL DEFAULT '[]'::jsonb,
  display_name TEXT NOT NULL,
  token_hash CHAR(64) NOT NULL CHECK (token_hash ~ '^[0-9a-f]{64}$'),
  token_hint VARCHAR(16) NOT NULL,
  created_by VARCHAR(26) NOT NULL CHECK (created_by ~ '^[0-9A-HJKMNP-TV-Z]{26}$'),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  is_active BOOLEAN NOT NULL DEFAULT true,
  UNIQUE (tenant_id, id),
  UNIQUE (tenant_id, token_hash)
);

ALTER TABLE staff_access_grants ENABLE ROW LEVEL SECURITY;
ALTER TABLE staff_access_grants FORCE ROW LEVEL SECURITY;
CREATE POLICY staff_access_grants_tenant_isolation
  ON staff_access_grants
  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_access_grants TO krona_staff_app;

CREATE TABLE staff_payroll_batches (
  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}$'),
  org_unit_id VARCHAR(26) NOT NULL,
  period_start DATE NOT NULL,
  period_end DATE NOT NULL,
  status VARCHAR(32) NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'submitted', 'signed', 'returned')),
  return_comment TEXT,
  submitted_at TIMESTAMPTZ,
  submitted_by VARCHAR(26),
  signed_at TIMESTAMPTZ,
  signed_by VARCHAR(26),
  created_by VARCHAR(26) NOT NULL CHECK (created_by ~ '^[0-9A-HJKMNP-TV-Z]{26}$'),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (tenant_id, id),
  FOREIGN KEY (tenant_id, org_unit_id) REFERENCES staff_org_units (tenant_id, id) ON DELETE RESTRICT
);

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

GRANT SELECT, INSERT, UPDATE ON staff_payroll_batches TO krona_staff_app;

CREATE TABLE staff_payroll_batch_lines (
  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}$'),
  batch_id VARCHAR(26) NOT NULL,
  work_session_id VARCHAR(26) NOT NULL,
  employee_id VARCHAR(26) NOT NULL,
  org_unit_id VARCHAR(26) NOT NULL,
  amount_minor BIGINT NOT NULL,
  session_revision INTEGER NOT NULL DEFAULT 1,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (tenant_id, id),
  FOREIGN KEY (tenant_id, batch_id) REFERENCES staff_payroll_batches (tenant_id, id) ON DELETE CASCADE,
  FOREIGN KEY (tenant_id, work_session_id) REFERENCES staff_work_sessions (tenant_id, id) ON DELETE RESTRICT,
  FOREIGN KEY (tenant_id, employee_id) REFERENCES staff_employees (tenant_id, id) ON DELETE RESTRICT,
  FOREIGN KEY (tenant_id, org_unit_id) REFERENCES staff_org_units (tenant_id, id) ON DELETE RESTRICT
);

ALTER TABLE staff_payroll_batch_lines ENABLE ROW LEVEL SECURITY;
ALTER TABLE staff_payroll_batch_lines FORCE ROW LEVEL SECURITY;
CREATE POLICY staff_payroll_batch_lines_tenant_isolation
  ON staff_payroll_batch_lines
  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_payroll_batch_lines TO krona_staff_app;

CREATE TABLE staff_batch_notifications (
  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}$'),
  recipient_role VARCHAR(32) NOT NULL,
  recipient_actor_id VARCHAR(26),
  type VARCHAR(64) NOT NULL,
  subject_id VARCHAR(26) NOT NULL,
  payload JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (tenant_id, id)
);

ALTER TABLE staff_batch_notifications ENABLE ROW LEVEL SECURITY;
ALTER TABLE staff_batch_notifications FORCE ROW LEVEL SECURITY;
CREATE POLICY staff_batch_notifications_tenant_isolation
  ON staff_batch_notifications
  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_batch_notifications TO krona_staff_app;

CREATE TABLE staff_biometric_consents (
  tenant_id VARCHAR(26) NOT NULL CHECK (tenant_id ~ '^[0-9A-HJKMNP-TV-Z]{26}$'),
  employee_id VARCHAR(26) NOT NULL,
  status VARCHAR(32) NOT NULL CHECK (status IN ('granted', 'revoked')),
  document_ref TEXT,
  updated_by VARCHAR(26) NOT NULL CHECK (updated_by ~ '^[0-9A-HJKMNP-TV-Z]{26}$'),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (tenant_id, employee_id),
  FOREIGN KEY (tenant_id, employee_id) REFERENCES staff_employees (tenant_id, id) ON DELETE CASCADE
);

ALTER TABLE staff_biometric_consents ENABLE ROW LEVEL SECURITY;
ALTER TABLE staff_biometric_consents FORCE ROW LEVEL SECURITY;
CREATE POLICY staff_biometric_consents_tenant_isolation
  ON staff_biometric_consents
  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_biometric_consents TO krona_staff_app;

ALTER TABLE staff_sync_devices
  DROP CONSTRAINT IF EXISTS staff_sync_devices_tenant_id_org_unit_id_fkey;

ALTER TABLE staff_sync_devices
  ADD CONSTRAINT staff_sync_devices_tenant_id_org_unit_id_fkey
  FOREIGN KEY (tenant_id, org_unit_id) REFERENCES staff_org_units (tenant_id, id) ON DELETE SET NULL (org_unit_id);