CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nation_id INT UNIQUE,
  nation_name VARCHAR(128),
  is_member TINYINT(1) DEFAULT 0,
  role ENUM('member','admin') DEFAULT 'member',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE api_keys (
  id INT AUTO_INCREMENT PRIMARY KEY,
  label VARCHAR(64) NOT NULL,
  key_hash CHAR(64) NOT NULL,
  created_by INT,
  active TINYINT(1) DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE build_rules (
  id INT AUTO_INCREMENT PRIMARY KEY,
  continent ENUM('Asia','Africa','Europe','North America','South America','Oceania','Antarctica') NOT NULL,
  city_min INT NOT NULL DEFAULT 1,
  city_max INT NOT NULL DEFAULT 100,
  rules_json JSON NOT NULL,
  active TINYINT(1) DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE project_rules (
  id INT AUTO_INCREMENT PRIMARY KEY,
  continent ENUM('Asia','Africa','Europe','North America','South America','Oceania','Antarctica') NOT NULL,
  city_min INT NOT NULL,
  city_max INT NOT NULL,
  required_projects JSON NOT NULL,
  active TINYINT(1) DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE audits (
  id INT AUTO_INCREMENT PRIMARY KEY,
  ran_by INT,
  filter_json JSON,
  started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE audit_targets (
  id INT AUTO_INCREMENT PRIMARY KEY,
  audit_id INT NOT NULL,
  nation_id INT NOT NULL,
  nation_name VARCHAR(128),
  issue_count INT DEFAULT 0,
  status ENUM('ok','issues','error') DEFAULT 'ok',
  FOREIGN KEY (audit_id) REFERENCES audits(id) ON DELETE CASCADE
);

CREATE TABLE audit_issues (
  id INT AUTO_INCREMENT PRIMARY KEY,
  audit_id INT NOT NULL,
  nation_id INT NOT NULL,
  code VARCHAR(64),
  message TEXT,
  severity ENUM('low','med','high') DEFAULT 'med',
  fix_hint TEXT,
  city_number INT NULL,
  FOREIGN KEY (audit_id) REFERENCES audits(id) ON DELETE CASCADE
);

CREATE TABLE login_codes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nation_id INT NOT NULL,
  code CHAR(6) NOT NULL,
  sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  expires_at TIMESTAMP NOT NULL,
  used TINYINT(1) DEFAULT 0
);

CREATE TABLE messages_outbox (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nation_id INT NOT NULL,
  subject VARCHAR(140),
  body TEXT,
  status ENUM('queued','sent','failed') DEFAULT 'queued',
  response TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  sent_at TIMESTAMP NULL
);
