-- migrate:up

ALTER TABLE daily_statistics
  ADD COLUMN reversed_rewards_minor BIGINT UNSIGNED NOT NULL DEFAULT 0
    AFTER mystery_rewards_minor,
  ADD CONSTRAINT chk_daily_statistics_watermark CHECK (
    source_watermark IS NULL OR source_watermark > 0
  );

ALTER TABLE task_statistics
  ADD COLUMN reversed_rewards_minor BIGINT UNSIGNED NOT NULL DEFAULT 0
    AFTER rewards_minor,
  ADD COLUMN source_watermark BIGINT UNSIGNED NULL AFTER calculated_at,
  ADD CONSTRAINT chk_task_statistics_watermark CHECK (
    source_watermark IS NULL OR source_watermark > 0
  );

ALTER TABLE user_statistics
  ADD COLUMN task_earned_minor BIGINT UNSIGNED NOT NULL DEFAULT 0
    AFTER earned_minor,
  ADD COLUMN referral_earned_minor BIGINT UNSIGNED NOT NULL DEFAULT 0
    AFTER task_earned_minor,
  ADD COLUMN mystery_earned_minor BIGINT UNSIGNED NOT NULL DEFAULT 0
    AFTER referral_earned_minor,
  ADD COLUMN reversed_earned_minor BIGINT UNSIGNED NOT NULL DEFAULT 0
    AFTER mystery_earned_minor,
  ADD COLUMN source_watermark BIGINT UNSIGNED NULL AFTER calculated_at,
  ADD CONSTRAINT chk_user_statistics_earnings CHECK (
    earned_minor = task_earned_minor + referral_earned_minor + mystery_earned_minor
  ),
  ADD CONSTRAINT chk_user_statistics_watermark CHECK (
    source_watermark IS NULL OR source_watermark > 0
  );

ALTER TABLE leaderboard_snapshots
  DROP INDEX uq_leaderboard_snapshots_period,
  ADD COLUMN generation_key VARCHAR(191) CHARACTER SET ascii COLLATE ascii_bin NULL
    AFTER public_id,
  ADD COLUMN generated_for DATE NULL AFTER period_end,
  ADD COLUMN currency_id BIGINT UNSIGNED NULL AFTER generated_for,
  ADD COLUMN source_watermark BIGINT UNSIGNED NULL AFTER currency_id,
  ADD COLUMN entry_count INT UNSIGNED NOT NULL DEFAULT 0 AFTER source_watermark,
  ADD UNIQUE KEY uq_leaderboard_snapshots_generation (generation_key),
  ADD KEY idx_leaderboard_snapshots_period (
    metric, period_type, period_start, period_end, generated_for
  ),
  ADD CONSTRAINT fk_leaderboard_snapshots_currency
    FOREIGN KEY (currency_id) REFERENCES currencies (id) ON DELETE RESTRICT,
  ADD CONSTRAINT chk_leaderboard_snapshots_currency CHECK (
    (metric = 'verified_earnings' AND currency_id IS NOT NULL) OR
    (metric <> 'verified_earnings' AND currency_id IS NULL)
  ),
  ADD CONSTRAINT chk_leaderboard_snapshots_status CHECK (
    (status = 'published' AND published_at IS NOT NULL) OR
    (status <> 'published')
  ),
  ADD CONSTRAINT chk_leaderboard_snapshots_entries CHECK (entry_count <= 1000);

ALTER TABLE leaderboard_entries
  ADD CONSTRAINT chk_leaderboard_entries_score CHECK (score_value > 0),
  ADD CONSTRAINT chk_leaderboard_entries_display CHECK (
    JSON_TYPE(display_snapshot) = 'OBJECT'
  );

CREATE TABLE analytics_rollup_runs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  public_id CHAR(26) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  statistic_date DATE NOT NULL,
  currency_id BIGINT UNSIGNED NOT NULL,
  source_watermarks JSON NOT NULL,
  rows_written JSON NOT NULL,
  started_at DATETIME(6) NOT NULL,
  completed_at DATETIME(6) NOT NULL,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (id),
  UNIQUE KEY uq_analytics_rollup_runs_public_id (public_id),
  UNIQUE KEY uq_analytics_rollup_runs_date_currency (statistic_date, currency_id),
  KEY idx_analytics_rollup_runs_completed (completed_at, id),
  CONSTRAINT fk_analytics_rollup_runs_currency
    FOREIGN KEY (currency_id) REFERENCES currencies (id) ON DELETE RESTRICT,
  CONSTRAINT chk_analytics_rollup_runs_times CHECK (completed_at >= started_at),
  CONSTRAINT chk_analytics_rollup_runs_watermarks CHECK (
    JSON_TYPE(source_watermarks) = 'OBJECT'
  ),
  CONSTRAINT chk_analytics_rollup_runs_rows CHECK (JSON_TYPE(rows_written) = 'OBJECT')
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE INDEX idx_user_statistics_date_earnings
  ON user_statistics (statistic_date, earned_minor, user_id);
CREATE INDEX idx_task_statistics_date_completions
  ON task_statistics (statistic_date, completions, task_id);
CREATE INDEX idx_leaderboard_entries_score
  ON leaderboard_entries (leaderboard_snapshot_id, score_value, rank_position);
CREATE INDEX idx_ledger_entries_analytics
  ON ledger_entries (account_id, transaction_id, entry_side, amount_minor);

INSERT IGNORE INTO settings (setting_key, value_type, value_json, is_public)
VALUES ('leaderboard.period', 'string', '"weekly"', 1);

INSERT IGNORE INTO permissions (code, permission_group, description) VALUES
  ('reports.financial.view', 'reports', 'View financial analytics and amount totals.');

INSERT IGNORE INTO admin_role_permissions (role_id, permission_id)
SELECT role.id, permission.id
FROM admin_roles AS role
CROSS JOIN permissions AS permission
WHERE role.code = 'super_admin';

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 = 'reports.financial.view'
WHERE role.code IN ('finance_manager', 'auditor');

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 = 'reports.view'
WHERE role.code = 'operations_manager';

CREATE TRIGGER trg_leaderboard_entries_no_update
BEFORE UPDATE ON leaderboard_entries
FOR EACH ROW
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Published leaderboard entries are immutable';

CREATE TRIGGER trg_leaderboard_entries_no_delete
BEFORE DELETE ON leaderboard_entries
FOR EACH ROW
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Leaderboard entry evidence cannot be deleted';

CREATE TRIGGER trg_analytics_rollup_runs_no_update
BEFORE UPDATE ON analytics_rollup_runs
FOR EACH ROW
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Analytics rollup evidence is immutable';

CREATE TRIGGER trg_analytics_rollup_runs_no_delete
BEFORE DELETE ON analytics_rollup_runs
FOR EACH ROW
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Analytics rollup evidence cannot be deleted';

-- migrate:down

DROP TRIGGER IF EXISTS trg_analytics_rollup_runs_no_delete;
DROP TRIGGER IF EXISTS trg_analytics_rollup_runs_no_update;
DROP TRIGGER IF EXISTS trg_leaderboard_entries_no_delete;
DROP TRIGGER IF EXISTS trg_leaderboard_entries_no_update;

DELETE FROM permissions WHERE code IN (
  'reports.financial.view'
);
DELETE FROM settings WHERE setting_key = 'leaderboard.period';

DROP INDEX idx_ledger_entries_analytics ON ledger_entries;
DROP INDEX idx_leaderboard_entries_score ON leaderboard_entries;
DROP INDEX idx_task_statistics_date_completions ON task_statistics;
DROP INDEX idx_user_statistics_date_earnings ON user_statistics;

DROP TABLE IF EXISTS analytics_rollup_runs;

ALTER TABLE leaderboard_entries
  DROP CHECK chk_leaderboard_entries_display,
  DROP CHECK chk_leaderboard_entries_score;

ALTER TABLE leaderboard_snapshots
  DROP CHECK chk_leaderboard_snapshots_entries,
  DROP CHECK chk_leaderboard_snapshots_status,
  DROP CHECK chk_leaderboard_snapshots_currency,
  DROP FOREIGN KEY fk_leaderboard_snapshots_currency,
  DROP INDEX idx_leaderboard_snapshots_period,
  DROP INDEX uq_leaderboard_snapshots_generation,
  DROP COLUMN entry_count,
  DROP COLUMN source_watermark,
  DROP COLUMN currency_id,
  DROP COLUMN generated_for,
  DROP COLUMN generation_key,
  ADD UNIQUE KEY uq_leaderboard_snapshots_period (
    metric, period_type, period_start, period_end
  );

ALTER TABLE user_statistics
  DROP CHECK chk_user_statistics_watermark,
  DROP CHECK chk_user_statistics_earnings,
  DROP COLUMN source_watermark,
  DROP COLUMN reversed_earned_minor,
  DROP COLUMN mystery_earned_minor,
  DROP COLUMN referral_earned_minor,
  DROP COLUMN task_earned_minor;

ALTER TABLE task_statistics
  DROP CHECK chk_task_statistics_watermark,
  DROP COLUMN source_watermark,
  DROP COLUMN reversed_rewards_minor;

ALTER TABLE daily_statistics
  DROP CHECK chk_daily_statistics_watermark,
  DROP COLUMN reversed_rewards_minor;