-- Import in the database selected in cPanel/phpMyAdmin (no CREATE DATABASE).
-- Production requires MySQL 5.7+ or MariaDB 10.2+ and PHP 8.1+ with pdo_mysql.
-- Upload server/private outside public_html; copy config.example.php to config.php
-- and configure database credentials, a stable random app_key, and optional site_url.
-- All timestamps written by the application use UTC. Phone numbers store 10/11
-- Brazilian national digits (without the country code). Never expose these tables
-- or config.php in a public download. No public guest-list endpoint is provided.

CREATE TABLE IF NOT EXISTS rsvps (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    public_id CHAR(24) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
    payload_hash CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
    name VARCHAR(100) NOT NULL,
    phone VARCHAR(11) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
    guest_count TINYINT UNSIGNED NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY rsvps_public_id_unique (public_id),
    UNIQUE KEY rsvps_payload_hash_unique (payload_hash),
    KEY rsvps_created_at_index (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS companions (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    rsvp_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(100) NOT NULL,
    position TINYINT UNSIGNED NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY companions_position_unique (rsvp_id, position),
    CONSTRAINT companions_rsvp_fk FOREIGN KEY (rsvp_id) REFERENCES rsvps (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Records every accepted browser UUID, including retries with an identical payload.
-- This permits repeat submissions after a refresh without creating a second family.
CREATE TABLE IF NOT EXISTS rsvp_requests (
    request_id CHAR(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
    rsvp_id BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (request_id),
    KEY rsvp_requests_rsvp_index (rsvp_id),
    CONSTRAINT rsvp_requests_rsvp_fk FOREIGN KEY (rsvp_id) REFERENCES rsvps (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- HMAC only: raw IP addresses are never stored. Old buckets are removed on writes.
CREATE TABLE IF NOT EXISTS rsvp_rate_limits (
    key_hash CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
    window_started BIGINT UNSIGNED NOT NULL,
    attempts SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    PRIMARY KEY (key_hash),
    KEY rsvp_rate_limits_window_index (window_started)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;