-- ============================================================
-- UECF.NET Guest Book — Database Schema (simplified)
-- Fields: First Name, Last Name, Comments only.
-- MySQL 5.7+/8.x, InnoDB, utf8mb4
-- ============================================================

CREATE DATABASE IF NOT EXISTS uecf_guestbook
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE uecf_guestbook;

-- ------------------------------------------------------------
-- Main guest book entries table
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS guestbook (
    id                 INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    first_name         VARCHAR(80)  NOT NULL,
    last_name          VARCHAR(80)  NOT NULL,
    comments           TEXT NULL,
    ip_address         VARCHAR(45) NOT NULL,
    created_at         DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_created_at (created_at),
    INDEX idx_ip_created (ip_address, created_at)
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- Rate limiting log (separate from guestbook so deleting a
-- guestbook entry never affects abuse tracking)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS guestbook_submission_log (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ip_address  VARCHAR(45) NOT NULL,
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_ip_time (ip_address, created_at)
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- Sample seed data (optional — remove in production)
-- ------------------------------------------------------------
-- INSERT INTO guestbook (first_name, last_name, comments, ip_address)
-- VALUES ('John', 'Doe', 'Great service today, God bless!', '127.0.0.1');