CREATE TABLE IF NOT EXISTS flags (
  `key` VARCHAR(255) UNIQUE NOT NULL,
  name VARCHAR(255) NOT NULL,
  description TEXT NOT NULL,
  enabled BOOLEAN DEFAULT FALSE NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,
  PRIMARY KEY (`key`)
);

CREATE TABLE IF NOT EXISTS segments (
  `key` VARCHAR(255) UNIQUE NOT NULL,
  name VARCHAR(255) NOT NULL,
  description TEXT NOT NULL,
  match_type INTEGER DEFAULT 0 NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,
  PRIMARY KEY (`key`)
);

CREATE TABLE IF NOT EXISTS variants (
  id VARCHAR(255) UNIQUE NOT NULL,
  flag_key VARCHAR(255) NOT NULL,
  `key` VARCHAR(255) NOT NULL,
  name VARCHAR(255) NOT NULL,
  description TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (flag_key) REFERENCES flags (`key`) ON DELETE CASCADE,
  CONSTRAINT variants_flag_key_key UNIQUE (flag_key, `key`)
);

CREATE TABLE IF NOT EXISTS constraints (
  id VARCHAR(255) UNIQUE NOT NULL,
  segment_key VARCHAR(255) NOT NULL,
  type INTEGER DEFAULT 0 NOT NULL,
  property VARCHAR(255) NOT NULL,
  operator VARCHAR(255) NOT NULL,
  value TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (segment_key) REFERENCES segments (`key`) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS rules (
  id VARCHAR(255) UNIQUE NOT NULL,
  flag_key VARCHAR(255) NOT NULL,
  segment_key VARCHAR(255) NOT NULL,
  `rank` INTEGER DEFAULT 1 NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (flag_key) REFERENCES flags (`key`) ON DELETE CASCADE,
  FOREIGN KEY (segment_key) REFERENCES segments (`key`) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS distributions (
  id VARCHAR(255) UNIQUE NOT NULL,
  rule_id VARCHAR(255) NOT NULL,
  variant_id VARCHAR(255) NOT NULL,
  rollout float DEFAULT 0 NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (rule_id) REFERENCES rules (id) ON DELETE CASCADE,
  FOREIGN KEY (variant_id) REFERENCES variants (id) ON DELETE CASCADE
);
