-- ---------------------------------------------------------------------
-- 1. LOCATION LOOKUPS (Nigeria-specific)
-- ---------------------------------------------------------------------

CREATE TABLE states (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  geo_zone ENUM('North Central','North East','North West','South East','South South','South West') NOT NULL,
  UNIQUE KEY uq_state_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE lgas (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  state_id INT UNSIGNED NOT NULL,
  name VARCHAR(100) NOT NULL,
  UNIQUE KEY uq_state_lga (state_id, name),
  CONSTRAINT fk_lga_state FOREIGN KEY (state_id) REFERENCES states(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;