-- Legal Rules Engine (paramétrable, versionné) — SQLite additive
CREATE TABLE IF NOT EXISTS legal_rules (
  id TEXT PRIMARY KEY,
  country TEXT NOT NULL DEFAULT 'MA',
  category TEXT NOT NULL,
  code TEXT NOT NULL,
  name TEXT NOT NULL,
  value REAL NOT NULL DEFAULT 0,
  unit TEXT DEFAULT 'PERCENT',
  min_value REAL,
  max_value REAL,
  effective_from TEXT NOT NULL,
  effective_to TEXT,
  version TEXT NOT NULL DEFAULT '1',
  source TEXT,
  notes TEXT,
  status TEXT DEFAULT 'ACTIVE',
  active INTEGER DEFAULT 1,
  created_at TEXT NOT NULL,
  updated_at TEXT,
  UNIQUE (country, category, code, version, effective_from)
);

CREATE TABLE IF NOT EXISTS legal_rule_audit (
  id TEXT PRIMARY KEY,
  rule_id TEXT NOT NULL,
  user_id TEXT,
  action TEXT NOT NULL,
  old_value TEXT,
  new_value TEXT,
  reason TEXT,
  created_at TEXT NOT NULL
);

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