-- PKO @ 50 : RSVP database schema
-- Import this file from cPanel > phpMyAdmin after creating the database.

SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS rsvps (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  full_name     VARCHAR(120) NOT NULL,
  country_iso   CHAR(2)      NOT NULL,
  country_code  VARCHAR(8)   NOT NULL,
  phone         VARCHAR(24)  NOT NULL,
  phone_e164    VARCHAR(24)  NOT NULL,
  attending     TINYINT(1)   NOT NULL,
  lang          CHAR(2)      NOT NULL DEFAULT 'fr',
  ip_address    VARCHAR(45)  DEFAULT NULL,
  user_agent    VARCHAR(255) DEFAULT NULL,
  created_at    DATETIME     NOT NULL,
  updated_at    DATETIME     DEFAULT NULL,
  UNIQUE KEY uniq_phone (phone_e164),
  KEY idx_created (created_at),
  KEY idx_attending (attending)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Simple throttle table, one row per IP.
CREATE TABLE IF NOT EXISTS submit_log (
  ip_address    VARCHAR(45) NOT NULL PRIMARY KEY,
  last_submit   DATETIME    NOT NULL,
  hits          INT         NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Failed admin logins, for lockout.
CREATE TABLE IF NOT EXISTS login_attempts (
  ip_address    VARCHAR(45) NOT NULL PRIMARY KEY,
  attempts      INT         NOT NULL DEFAULT 0,
  locked_until  DATETIME    DEFAULT NULL,
  last_attempt  DATETIME    NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
