-- Legal Rules Engine (paramétrable, versionné) — MySQL additive
CREATE TABLE IF NOT EXISTS legal_rules (
  id VARCHAR(32) PRIMARY KEY,
  country VARCHAR(8) NOT NULL DEFAULT 'MA',
  category VARCHAR(64) NOT NULL,
  code VARCHAR(64) NOT NULL,
  name VARCHAR(255) NOT NULL,
  value DOUBLE NOT NULL DEFAULT 0,
  unit VARCHAR(32) DEFAULT 'PERCENT',
  min_value DOUBLE NULL,
  max_value DOUBLE NULL,
  effective_from VARCHAR(32) NOT NULL,
  effective_to VARCHAR(32) NULL,
  version VARCHAR(32) NOT NULL DEFAULT '1',
  source TEXT NULL,
  notes TEXT NULL,
  status VARCHAR(32) DEFAULT 'ACTIVE',
  active INT DEFAULT 1,
  created_at VARCHAR(64) NOT NULL,
  updated_at VARCHAR(64) NULL,
  UNIQUE KEY uq_legal_rule (country, category, code, version, effective_from)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS legal_rule_audit (
  id VARCHAR(32) PRIMARY KEY,
  rule_id VARCHAR(255) NOT NULL,
  user_id VARCHAR(255) NULL,
  action VARCHAR(64) NOT NULL,
  old_value TEXT NULL,
  new_value TEXT NULL,
  reason TEXT NULL,
  created_at VARCHAR(64) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE INDEX idx_legal_rules_lookup ON legal_rules(country, category, code, active);
CREATE INDEX idx_legal_rules_dates ON legal_rules(effective_from, effective_to);