-- migrate:up

ALTER TABLE referrals
  ADD COLUMN version BIGINT UNSIGNED NOT NULL DEFAULT 1 AFTER risk_state,
  ADD CONSTRAINT chk_referrals_version CHECK (version > 0);

ALTER TABLE referral_activations
  ADD COLUMN source_task_completion_id BIGINT UNSIGNED NULL AFTER activation_event_key,
  ADD CONSTRAINT fk_referral_activations_completion
    FOREIGN KEY (source_task_completion_id) REFERENCES task_completions (id) ON DELETE RESTRICT;

CREATE TABLE referral_status_history (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  public_id CHAR(26) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  referral_id BIGINT UNSIGNED NOT NULL,
  from_status ENUM('pending', 'active', 'held', 'rejected', 'reversed') NULL,
  to_status ENUM('pending', 'active', 'held', 'rejected', 'reversed') NOT NULL,
  from_risk_state ENUM('normal', 'observed', 'review', 'blocked') NULL,
  to_risk_state ENUM('normal', 'observed', 'review', 'blocked') NOT NULL,
  action VARCHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  reason VARCHAR(1000) NULL,
  source_task_completion_id BIGINT UNSIGNED NULL,
  changed_by_admin_id BIGINT UNSIGNED NULL,
  metadata JSON NULL,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (id),
  UNIQUE KEY uq_referral_status_history_public_id (public_id),
  KEY idx_referral_status_history_referral (referral_id, created_at, id),
  KEY idx_referral_status_history_action (action, created_at, id),
  CONSTRAINT fk_referral_status_history_referral
    FOREIGN KEY (referral_id) REFERENCES referrals (id) ON DELETE RESTRICT,
  CONSTRAINT fk_referral_status_history_completion
    FOREIGN KEY (source_task_completion_id) REFERENCES task_completions (id) ON DELETE RESTRICT,
  CONSTRAINT fk_referral_status_history_admin
    FOREIGN KEY (changed_by_admin_id) REFERENCES admins (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE INDEX idx_referral_rewards_referral_status
  ON referral_rewards (referral_id, status, created_at, id);

INSERT INTO referral_status_history (
  public_id, referral_id, from_status, to_status, from_risk_state, to_risk_state,
  action, reason, source_task_completion_id, changed_by_admin_id, metadata, created_at
)
SELECT
  UPPER(SUBSTRING(SHA2(CONCAT('referral-attribution:', r.public_id), 256), 1, 26)),
  r.id, NULL, r.status, NULL, r.risk_state, 'attributed', NULL, NULL, NULL,
  JSON_OBJECT('attribution_source', r.attribution_source, 'phase_12_backfill', TRUE),
  r.attributed_at
FROM referrals r
WHERE NOT EXISTS (
  SELECT 1 FROM referral_status_history history
  WHERE history.referral_id = r.id AND history.action = 'attributed'
);

INSERT IGNORE INTO permissions (code, permission_group, description) VALUES
  ('referrals.settings.update', 'referrals', 'Update referral activation and reward settings.');

INSERT IGNORE INTO admin_role_permissions (role_id, permission_id)
SELECT role.id, permission.id
FROM admin_roles AS role
JOIN permissions AS permission ON permission.code = 'referrals.settings.update'
WHERE role.code IN ('super_admin', 'operations_manager');

-- migrate:down

DELETE FROM permissions WHERE code = 'referrals.settings.update';
DROP INDEX idx_referral_rewards_referral_status ON referral_rewards;
DROP TABLE IF EXISTS referral_status_history;
ALTER TABLE referral_activations
  DROP FOREIGN KEY fk_referral_activations_completion,
  DROP COLUMN source_task_completion_id;
ALTER TABLE referrals
  DROP CHECK chk_referrals_version,
  DROP COLUMN version;