-- migrate:up

INSERT IGNORE INTO admin_roles (public_id, code, name, description, is_system) VALUES
  ('01J00000000000000000000001', 'super_admin', 'Super Admin', 'Full system authority with separation-of-duty checks.', 1),
  ('01J00000000000000000000002', 'operations_manager', 'Operations Manager', 'User, task, referral, reward-content, and operations management.', 1),
  ('01J00000000000000000000003', 'finance_manager', 'Finance Manager', 'Wallet, withdrawal, payout, and finance reporting.', 1),
  ('01J00000000000000000000004', 'content_manager', 'Content Manager', 'Content, message template, reminder, and theme management.', 1),
  ('01J00000000000000000000005', 'security_analyst', 'Security Analyst', 'Fraud, sessions, bans, and security-event review.', 1),
  ('01J00000000000000000000006', 'auditor', 'Auditor', 'Read-only access to financial, security, and audit evidence.', 1);

ALTER TABLE currencies
  ADD COLUMN version BIGINT UNSIGNED NOT NULL DEFAULT 1 AFTER is_active,
  ADD COLUMN updated_by_admin_id BIGINT UNSIGNED NULL AFTER version,
  ADD CONSTRAINT fk_currencies_updater
    FOREIGN KEY (updated_by_admin_id) REFERENCES admins (id) ON DELETE SET NULL,
  ADD CONSTRAINT chk_currencies_version CHECK (version > 0);

ALTER TABLE themes
  ADD COLUMN version BIGINT UNSIGNED NOT NULL DEFAULT 1 AFTER is_system,
  ADD COLUMN updated_by_admin_id BIGINT UNSIGNED NULL AFTER created_by_admin_id,
  ADD COLUMN active_slot TINYINT GENERATED ALWAYS AS (
    CASE WHEN status = 'active' THEN 1 ELSE NULL END
  ) STORED AFTER published_at,
  ADD UNIQUE KEY uq_themes_active_slot (active_slot),
  ADD CONSTRAINT fk_themes_updater
    FOREIGN KEY (updated_by_admin_id) REFERENCES admins (id) ON DELETE SET NULL,
  ADD CONSTRAINT chk_themes_version CHECK (version > 0),
  ADD CONSTRAINT chk_themes_lifecycle CHECK (
    (status = 'active' AND published_at IS NOT NULL) OR status <> 'active'
  );

ALTER TABLE managed_links
  ADD COLUMN version BIGINT UNSIGNED NOT NULL DEFAULT 1 AFTER status,
  ADD CONSTRAINT chk_managed_links_version CHECK (version > 0);

ALTER TABLE feature_flags
  ADD COLUMN version BIGINT UNSIGNED NOT NULL DEFAULT 1 AFTER rules,
  ADD CONSTRAINT chk_feature_flags_version CHECK (version > 0),
  ADD CONSTRAINT chk_feature_flags_rules CHECK (rules IS NULL OR JSON_TYPE(rules) = 'OBJECT');

ALTER TABLE banners
  ADD COLUMN version BIGINT UNSIGNED NOT NULL DEFAULT 1 AFTER status,
  ADD COLUMN published_at DATETIME(6) NULL AFTER version,
  ADD COLUMN published_by_admin_id BIGINT UNSIGNED NULL AFTER created_by_admin_id,
  ADD COLUMN updated_by_admin_id BIGINT UNSIGNED NULL AFTER published_by_admin_id,
  ADD CONSTRAINT fk_banners_publisher
    FOREIGN KEY (published_by_admin_id) REFERENCES admins (id) ON DELETE SET NULL,
  ADD CONSTRAINT fk_banners_updater
    FOREIGN KEY (updated_by_admin_id) REFERENCES admins (id) ON DELETE SET NULL,
  ADD CONSTRAINT chk_banners_version CHECK (version > 0),
  ADD CONSTRAINT chk_banners_lifecycle CHECK (
    (status IN ('active', 'paused') AND published_at IS NOT NULL) OR
    (status IN ('draft', 'archived'))
  );

ALTER TABLE announcements
  ADD COLUMN version BIGINT UNSIGNED NOT NULL DEFAULT 1 AFTER status,
  ADD COLUMN published_by_admin_id BIGINT UNSIGNED NULL AFTER created_by_admin_id,
  ADD COLUMN updated_by_admin_id BIGINT UNSIGNED NULL AFTER published_by_admin_id,
  ADD CONSTRAINT fk_announcements_publisher
    FOREIGN KEY (published_by_admin_id) REFERENCES admins (id) ON DELETE SET NULL,
  ADD CONSTRAINT fk_announcements_updater
    FOREIGN KEY (updated_by_admin_id) REFERENCES admins (id) ON DELETE SET NULL,
  ADD CONSTRAINT chk_announcements_version CHECK (version > 0),
  ADD CONSTRAINT chk_announcements_target_rules CHECK (
    target_rules IS NULL OR JSON_TYPE(target_rules) = 'OBJECT'
  ),
  ADD CONSTRAINT chk_announcements_lifecycle CHECK (
    (status IN ('scheduled', 'active', 'expired') AND published_at IS NOT NULL) OR
    (status IN ('draft', 'archived'))
  );

ALTER TABLE tutorials
  ADD COLUMN version BIGINT UNSIGNED NOT NULL DEFAULT 1 AFTER status,
  ADD COLUMN published_by_admin_id BIGINT UNSIGNED NULL AFTER created_by_admin_id,
  ADD COLUMN updated_by_admin_id BIGINT UNSIGNED NULL AFTER published_by_admin_id,
  ADD CONSTRAINT fk_tutorials_publisher
    FOREIGN KEY (published_by_admin_id) REFERENCES admins (id) ON DELETE SET NULL,
  ADD CONSTRAINT fk_tutorials_updater
    FOREIGN KEY (updated_by_admin_id) REFERENCES admins (id) ON DELETE SET NULL,
  ADD CONSTRAINT chk_tutorials_version CHECK (version > 0),
  ADD CONSTRAINT chk_tutorials_lifecycle CHECK (
    (status = 'published' AND published_at IS NOT NULL) OR status <> 'published'
  );

ALTER TABLE maintenance_windows
  ADD COLUMN version BIGINT UNSIGNED NOT NULL DEFAULT 1 AFTER allow_admin_access,
  ADD COLUMN updated_by_admin_id BIGINT UNSIGNED NULL AFTER created_by_admin_id,
  ADD CONSTRAINT fk_maintenance_windows_updater
    FOREIGN KEY (updated_by_admin_id) REFERENCES admins (id) ON DELETE SET NULL,
  ADD CONSTRAINT chk_maintenance_windows_version CHECK (version > 0);

INSERT IGNORE INTO feature_flags (
  flag_key, enabled, rollout_percentage, rules, version
) VALUES ('banners', 1, 100, NULL, 1);

INSERT IGNORE INTO permissions (code, permission_group, description) VALUES
  ('banners.view', 'content', 'View banner drafts and lifecycle evidence.'),
  ('banners.publish', 'content', 'Publish, pause, or archive banners.'),
  ('announcements.view', 'content', 'View announcement drafts and read statistics.'),
  ('announcements.publish', 'content', 'Schedule, publish, expire, or archive announcements.'),
  ('tutorials.view', 'content', 'View tutorial drafts and lifecycle evidence.'),
  ('tutorials.publish', 'content', 'Publish or archive tutorials.'),
  ('managed_links.manage', 'settings', 'Manage allowlisted public links.'),
  ('feature_flags.manage', 'settings', 'Manage client feature availability and rollout.'),
  ('themes.publish', 'settings', 'Activate or archive validated themes.'),
  ('maintenance.view', 'settings', 'View maintenance schedules and status.'),
  ('settings.view', 'settings', 'View settings.'),
  ('maintenance.manage', 'settings', 'Manage maintenance windows.');

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 IN (
  'banners.view', 'banners.publish', 'announcements.view', 'announcements.publish',
  'tutorials.view', 'tutorials.publish', 'managed_links.manage', 'themes.publish'
)
WHERE role.code = 'content_manager';

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 IN (
  'banners.view', 'announcements.view', 'tutorials.view',
  'feature_flags.manage', 'maintenance.view', 'maintenance.manage', 'settings.view',
  'mystery_box.publish', 'mystery_box.claims.view'
)
WHERE role.code = 'operations_manager';

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 IN (
  'withdrawals.account_details.view',
  'withdrawals.payout.confirm',
  'withdrawals.settings.manage'
)
WHERE role.code = 'finance_manager';

CREATE TRIGGER trg_setting_versions_no_update
BEFORE UPDATE ON setting_versions
FOR EACH ROW
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Setting version history is immutable';

CREATE TRIGGER trg_setting_versions_no_delete
BEFORE DELETE ON setting_versions
FOR EACH ROW
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Setting version history cannot be deleted';

CREATE TRIGGER trg_announcement_reads_no_update
BEFORE UPDATE ON announcement_reads
FOR EACH ROW
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Announcement read evidence is immutable';

CREATE TRIGGER trg_announcement_reads_no_delete
BEFORE DELETE ON announcement_reads
FOR EACH ROW
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Announcement read evidence cannot be deleted';

CREATE TRIGGER trg_banners_no_delete
BEFORE DELETE ON banners
FOR EACH ROW
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Banners must be archived, not deleted';

CREATE TRIGGER trg_announcements_no_delete
BEFORE DELETE ON announcements
FOR EACH ROW
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Announcements must be archived, not deleted';

CREATE TRIGGER trg_tutorials_no_delete
BEFORE DELETE ON tutorials
FOR EACH ROW
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Tutorials must be archived, not deleted';

-- migrate:down

DROP TRIGGER IF EXISTS trg_tutorials_no_delete;
DROP TRIGGER IF EXISTS trg_announcements_no_delete;
DROP TRIGGER IF EXISTS trg_banners_no_delete;
DROP TRIGGER IF EXISTS trg_announcement_reads_no_delete;
DROP TRIGGER IF EXISTS trg_announcement_reads_no_update;
DROP TRIGGER IF EXISTS trg_setting_versions_no_delete;
DROP TRIGGER IF EXISTS trg_setting_versions_no_update;

DELETE FROM permissions
WHERE code IN (
  'banners.view', 'banners.publish', 'announcements.view', 'announcements.publish',
  'tutorials.view', 'tutorials.publish', 'managed_links.manage',
  'feature_flags.manage', 'themes.publish', 'maintenance.view'
);

DELETE FROM feature_flags WHERE flag_key = 'banners';

ALTER TABLE maintenance_windows
  DROP CHECK chk_maintenance_windows_version,
  DROP FOREIGN KEY fk_maintenance_windows_updater,
  DROP COLUMN updated_by_admin_id,
  DROP COLUMN version;

ALTER TABLE tutorials
  DROP CHECK chk_tutorials_lifecycle,
  DROP CHECK chk_tutorials_version,
  DROP FOREIGN KEY fk_tutorials_updater,
  DROP FOREIGN KEY fk_tutorials_publisher,
  DROP COLUMN updated_by_admin_id,
  DROP COLUMN published_by_admin_id,
  DROP COLUMN version;

ALTER TABLE announcements
  DROP CHECK chk_announcements_lifecycle,
  DROP CHECK chk_announcements_target_rules,
  DROP CHECK chk_announcements_version,
  DROP FOREIGN KEY fk_announcements_updater,
  DROP FOREIGN KEY fk_announcements_publisher,
  DROP COLUMN updated_by_admin_id,
  DROP COLUMN published_by_admin_id,
  DROP COLUMN version;

ALTER TABLE banners
  DROP CHECK chk_banners_lifecycle,
  DROP CHECK chk_banners_version,
  DROP FOREIGN KEY fk_banners_updater,
  DROP FOREIGN KEY fk_banners_publisher,
  DROP COLUMN updated_by_admin_id,
  DROP COLUMN published_by_admin_id,
  DROP COLUMN published_at,
  DROP COLUMN version;

ALTER TABLE feature_flags
  DROP CHECK chk_feature_flags_rules,
  DROP CHECK chk_feature_flags_version,
  DROP COLUMN version;

ALTER TABLE managed_links
  DROP CHECK chk_managed_links_version,
  DROP COLUMN version;

ALTER TABLE themes
  DROP CHECK chk_themes_lifecycle,
  DROP CHECK chk_themes_version,
  DROP FOREIGN KEY fk_themes_updater,
  DROP INDEX uq_themes_active_slot,
  DROP COLUMN active_slot,
  DROP COLUMN updated_by_admin_id,
  DROP COLUMN version;

ALTER TABLE currencies
  DROP CHECK chk_currencies_version,
  DROP FOREIGN KEY fk_currencies_updater,
  DROP COLUMN updated_by_admin_id,
  DROP COLUMN version;