/* 
 * @author Robert Strutts
 * @copyright (c) 2026, Robert Strutts
 * @license MIT
 */
/**
 * Author:  Robert Strutts
 * Created: May 27, 2026
 */

CREATE TABLE emails (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email CHAR(254) NOT NULL UNIQUE,
    uuid CHAR(26) NOT NULL UNIQUE,
    first_name VARCHAR(33) NOT NULL,
    last_initial CHAR(1) NOT NULL,
    last_name VARCHAR(63) NULL,
    is_adult TINYINT UNSIGNED DEFAULT 0,
    tos TINYINT UNSIGNED DEFAULT 0,
    be_anon TINYINT UNSIGNED DEFAULT 0,
    is_active TINYINT UNSIGNED DEFAULT 0,
    ip VARBINARY(16) NULL,
    is_banned TINYINT UNSIGNED DEFAULT 0,
    is_subscribed TINYINT UNSIGNED DEFAULT 0,
    send_newsletter TINYINT UNSIGNED DEFAULT 0,
    sent_email TINYINT UNSIGNED DEFAULT 0
);    

CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email_id INT UNSIGNED NOT NULL,
    pwd TEXT NOT NULL,
    access_level TINYINT UNSIGNED DEFAULT 1
);

CREATE TABLE goals (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    uuid CHAR(26) NOT NULL UNIQUE,
    email_id INT UNSIGNED NOT NULL,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    approved TINYINT UNSIGNED DEFAULT 0,
    youtube TINYINT UNSIGNED DEFAULT 0
);

CREATE TABLE tags (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) UNIQUE
);

CREATE TABLE goal_tags (
    goal_id BIGINT UNSIGNED,
    tag_id INT UNSIGNED,
    PRIMARY KEY(goal_id, tag_id)
);

CREATE TABLE advice (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    goal_id BIGINT UNSIGNED NOT NULL,
    email_id INT UNSIGNED NOT NULL,
    content TEXT NOT NULL,
    votes INT UNSIGNED DEFAULT 0,
    approved TINYINT UNSIGNED DEFAULT 0,
    youtube TINYINT UNSIGNED DEFAULT 0
);

CREATE TABLE advice_comments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    advice_id BIGINT UNSIGNED,
    email_id INT UNSIGNED NOT NULL,
    content TEXT,
    approved TINYINT UNSIGNED DEFAULT 0,
    youtube TINYINT UNSIGNED DEFAULT 0
);