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

USE kruger_united;

CREATE TABLE IF NOT EXISTS members (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(30) NOT NULL,
    surname VARCHAR(30) NOT NULL,
    id_number CHAR(13) NOT NULL UNIQUE,
    cellphone CHAR(10) NOT NULL,
    address VARCHAR(30) NOT NULL,
    gender ENUM('Male','Female','Prefer not to say') NOT NULL,
    age TINYINT UNSIGNED NOT NULL,
    birthdate DATE NOT NULL,
    email VARCHAR(255) NOT NULL,
    heard_about VARCHAR(100) NOT NULL,
    tshirt_size ENUM('Small','Medium','Large','Extra Large') NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_name (name),
    INDEX idx_surname (surname),
    INDEX idx_email (email)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS admin_users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(100) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Create your first admin user AFTER uploading the package.
-- Generate a password hash using:
-- php -r "echo password_hash('CHANGE_THIS_PASSWORD', PASSWORD_DEFAULT), PHP_EOL;"
-- Then insert the resulting hash:
-- INSERT INTO admin_users (username, password_hash, full_name)
-- VALUES ('admin', 'PASTE_HASH_HERE', 'Kruger United Staff');