-- migrate:up

CREATE TABLE currencies (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  code CHAR(3) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  name VARCHAR(120) NOT NULL,
  symbol VARCHAR(16) NOT NULL,
  symbol_position ENUM('before', 'after') NOT NULL DEFAULT 'before',
  decimal_places TINYINT UNSIGNED NOT NULL DEFAULT 2,
  rounding_mode ENUM('half_up', 'half_even', 'down') NOT NULL DEFAULT 'half_up',
  is_settlement TINYINT(1) NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 0,
  settlement_guard TINYINT GENERATED ALWAYS AS (CASE WHEN is_settlement = 1 THEN 1 ELSE NULL END) STORED,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (id),
  UNIQUE KEY uq_currencies_code (code),
  UNIQUE KEY uq_currencies_one_settlement (settlement_guard),
  CONSTRAINT chk_currencies_decimal_places CHECK (decimal_places <= 6)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE settings (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  setting_key VARCHAR(191) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  value_type ENUM('string', 'integer', 'boolean', 'array', 'object') NOT NULL,
  value_json JSON NOT NULL,
  is_public TINYINT(1) NOT NULL DEFAULT 0,
  version INT UNSIGNED NOT NULL DEFAULT 1,
  updated_by_admin_id BIGINT UNSIGNED NULL,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (id),
  UNIQUE KEY uq_settings_key (setting_key),
  CONSTRAINT fk_settings_updater FOREIGN KEY (updated_by_admin_id) REFERENCES admins (id) ON DELETE SET NULL,
  CONSTRAINT chk_settings_version CHECK (version > 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE setting_versions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  setting_id BIGINT UNSIGNED NOT NULL,
  version INT UNSIGNED NOT NULL,
  value_json JSON NOT NULL,
  changed_by_admin_id BIGINT UNSIGNED NULL,
  change_reason VARCHAR(500) NULL,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (id),
  UNIQUE KEY uq_setting_versions_setting_version (setting_id, version),
  CONSTRAINT fk_setting_versions_setting FOREIGN KEY (setting_id) REFERENCES settings (id) ON DELETE RESTRICT,
  CONSTRAINT fk_setting_versions_actor FOREIGN KEY (changed_by_admin_id) REFERENCES admins (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE themes (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  public_id CHAR(26) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  code VARCHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  name VARCHAR(120) NOT NULL,
  tokens JSON NOT NULL,
  status ENUM('draft', 'active', 'archived') NOT NULL DEFAULT 'draft',
  is_system TINYINT(1) NOT NULL DEFAULT 0,
  created_by_admin_id BIGINT UNSIGNED NULL,
  published_at DATETIME(6) NULL,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (id),
  UNIQUE KEY uq_themes_public_id (public_id),
  UNIQUE KEY uq_themes_code (code),
  KEY idx_themes_status (status, published_at),
  CONSTRAINT fk_themes_creator FOREIGN KEY (created_by_admin_id) REFERENCES admins (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE managed_links (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  link_key VARCHAR(120) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  label VARCHAR(120) NOT NULL,
  url VARCHAR(2048) NOT NULL,
  status ENUM('active', 'inactive') NOT NULL DEFAULT 'inactive',
  updated_by_admin_id BIGINT UNSIGNED NULL,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (id),
  UNIQUE KEY uq_managed_links_key (link_key),
  CONSTRAINT fk_managed_links_updater FOREIGN KEY (updated_by_admin_id) REFERENCES admins (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE feature_flags (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  flag_key VARCHAR(120) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  enabled TINYINT(1) NOT NULL DEFAULT 0,
  rollout_percentage TINYINT UNSIGNED NOT NULL DEFAULT 100,
  rules JSON NULL,
  updated_by_admin_id BIGINT UNSIGNED NULL,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (id),
  UNIQUE KEY uq_feature_flags_key (flag_key),
  CONSTRAINT fk_feature_flags_updater FOREIGN KEY (updated_by_admin_id) REFERENCES admins (id) ON DELETE SET NULL,
  CONSTRAINT chk_feature_flags_rollout CHECK (rollout_percentage <= 100)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- migrate:down

DROP TABLE IF EXISTS feature_flags;
DROP TABLE IF EXISTS managed_links;
DROP TABLE IF EXISTS themes;
DROP TABLE IF EXISTS setting_versions;
DROP TABLE IF EXISTS settings;
DROP TABLE IF EXISTS currencies;