DROP DATABASE IF EXISTS poetree;

CREATE DATABASE poetree;

CREATE TABLE poetree.users (
  user_id INT NOT NULL AUTO_INCREMENT,
  first_name VARCHAR(50) NOT NULL, 
  last_name VARCHAR(50) NOT NULL, 
  email VARCHAR(100) NOT NULL UNIQUE, 
  username VARCHAR(30) NOT NULL UNIQUE,
  pass_phrase VARCHAR(500) NOT NULL,
  is_admin TINYINT NOT NULL DEFAULT 0,
  date_registered DATETIME NOT NULL DEFAULT now(),
  profile_pic VARCHAR(30) UNIQUE,
  registration_confirmed TINYINT NOT NULL DEFAULT 0,
  PRIMARY KEY(user_id)
);

CREATE TABLE poetree.categories (
  category_id INT NOT NULL AUTO_INCREMENT,
  category VARCHAR(100) NOT NULL, 
  PRIMARY KEY(category_id)
);

CREATE TABLE poetree.poems (
  poem_id INT NOT NULL AUTO_INCREMENT,
  title VARCHAR(200) NOT NULL, 
  poem TEXT NOT NULL, 
  date_submitted DATETIME NOT NULL DEFAULT now(),
  category_id INT,
  user_id INT,
  date_approved DATETIME,
  INDEX cat_ind (category_id),
  INDEX usr_ind (user_id),
  FOREIGN KEY (user_id) references users(user_id),
  FOREIGN KEY (category_id) references categories(category_id),
  PRIMARY KEY(poem_id)
);

CREATE TABLE poetree.tokens (
  token_id INT NOT NULL AUTO_INCREMENT,
  token CHAR(64) UNIQUE,
  user_id INT,
  token_expires DATETIME,
  INDEX usr_ind (user_id),
  FOREIGN KEY (user_id) references users(user_id),
  PRIMARY KEY(token_id)
);

USE poetree;

-- See http://php.net/password_hash
-- pass_phrase for all users is 'i love 2 eat BaseBalls!'
-- generated with password_hash('i love 2 eat BaseBalls!',PASSWORD_DEFAULT)
-- Another option: freaky Fr!days do so amaize
INSERT INTO poetree.users
(last_name, first_name, email, username, pass_phrase, date_registered, profile_pic, registration_confirmed, is_admin)
VALUES ('Rimer', 'Ralph', 'rrimer@phppoetry.com', 'LimerickMan', '$2y$10$GTRg3OMHAX5KFXdcVwDaS.oCHHrhiU6BqFF0h5JTpyycDhojjguqW',DATE_SUB(now(), INTERVAL 365 DAY), 'profile-1.png', 1, 1);

INSERT INTO poetree.users
(last_name, first_name, email, username, pass_phrase, date_registered, profile_pic, registration_confirmed)
VALUES ('Erheart', 'Hugh', 'herheart@phppoetry.com', 'HugHerHeart', '$2y$10$GTRg3OMHAX5KFXdcVwDaS.oCHHrhiU6BqFF0h5JTpyycDhojjguqW',DATE_SUB(now(), INTERVAL 105 DAY), 'profile-2.png', 1);

INSERT INTO poetree.users
(last_name, first_name, email, username, pass_phrase, date_registered, profile_pic, registration_confirmed)
VALUES ('O''Day', 'Dawn', 'dday@phppoetry.com', 'Dawnable', '$2y$10$GTRg3OMHAX5KFXdcVwDaS.oCHHrhiU6BqFF0h5JTpyycDhojjguqW',DATE_SUB(now(), INTERVAL 191 DAY), 'profile-3.png', 1);

INSERT INTO poetree.categories (category)  VALUES('Romantic');
INSERT INTO poetree.categories (category)  VALUES('Funny');
INSERT INTO poetree.categories (category)  VALUES('Scary');
INSERT INTO poetree.categories (category)  VALUES('Serious');
INSERT INTO poetree.categories (category)  VALUES('Nature');
INSERT INTO poetree.categories (category)  VALUES('Religious');
INSERT INTO poetree.categories (category)  VALUES('Celebratory');
INSERT INTO poetree.categories (category)  VALUES('Other');


-- LimerickMan's poems written by Nat :-)
-- Other poems generated by https://www.poem-generator.org.uk and edited by Nat
INSERT INTO poetree.poems (title, poem, user_id, category_id, date_submitted, date_approved)
VALUES ('Banana Duet', 'Four boisterous bananas from Tibet
Began a barbershop quartet
One slipped on his peel
Another split to Mobile
Leaving just two - a dancing duet', 1, 2, DATE_SUB(now(), INTERVAL 360*24*63*60 SECOND), DATE_SUB(now(), INTERVAL 360*24*63*60 SECOND)); -- Making the minutes slightly off of 60 randomizes time

INSERT INTO poetree.poems (title, poem, user_id, category_id, date_submitted, date_approved)
VALUES ('The Aristocratic Apple', 'Even a bee wouldn''t bother with me
Says an aristocratic Apple from East Albany
Though he isn''t to blame
He feels so ashamed
For having fallen, last fall, from his family tree', 1, 2, DATE_SUB(now(), INTERVAL 58*24*57*60 SECOND), NULL); -- Making the minutes slightly off of 60 randomizes time

INSERT INTO poetree.poems (title, poem, user_id, category_id, date_submitted, date_approved)
VALUES ('Carrots and Camels', 'A carrot named Carla and Daniel the camel
Disputed the virtues of veggies and mammals
Carla debated
Until the camel was sated
The virtues of veggies are plenty grinned Daniel', 1, 2, DATE_SUB(now(), INTERVAL 3*24*59*60 SECOND), DATE_SUB(now(), INTERVAL 3*24*59*60 SECOND)); -- Making the minutes slightly off of 60 randomizes time

INSERT INTO poetree.poems (title, poem, user_id, category_id, date_submitted, date_approved)
VALUES ('The Geriatric General', 'The geriatric general could not make up his mind
To follow from the front or to lead from behind
He strolled side to side
Until he just up and died
His soldiers now lay beside him in a perfect straight line', 1, 2, DATE_SUB(now(), INTERVAL 3*24*64*60 SECOND), DATE_SUB(now(), INTERVAL 3*24*63*60 SECOND)); -- Making the minutes slightly off of 60 randomizes time

INSERT INTO poetree.poems (title, poem, user_id, category_id, date_submitted, date_approved)
VALUES ('Dancing Dogs in Dungarees', 'A dozen dancing dogs in dungarees
All of the most distinguished pedigrees
Held their heads high by day
By night, they''d bow them and pray
For freedom from filthy frolicking fleas', 1, 2, DATE_SUB(now(), INTERVAL 3*24*63*60 SECOND), DATE_SUB(now(), INTERVAL 3*24*62*60 SECOND)); -- Making the minutes slightly off of 60 randomizes time

INSERT INTO poetree.poems (title, poem, user_id, category_id, date_submitted, date_approved)
VALUES ('My Innocent Tulip', 'Roses are red,
Violets are blue,
A heart is innocent,
And so are you.

Orchids are white,
Ghost ones are rare,
Life is short,
And so is your hair.

Magnolia grows,
With buds like eggs,
War and Peace is long,
And so are your legs.

Sunflowers reach,
Up to the skies,
Public speakers are bold,
And so are your eyes.

Foxgloves in hedges,
Surround the farms,
Seatbelts are safe,
And so are your arms.

Daisies are pretty,
Daffies have style,
Serena Williams is winning,
And so is your smile.

A Tulip is beautiful,
Just like you.', 2, 1, DATE_SUB(now(), INTERVAL 73*24*62*60 SECOND), DATE_SUB(now(), INTERVAL 73*24*59*60 SECOND)); -- Making the minutes slightly off of 60 randomizes time

INSERT INTO poetree.poems (title, poem, user_id, category_id, date_submitted, date_approved)
VALUES ('Harry''s Torment', 'Harry couldn''t stop thinking about the fur mitten
Its beauty was of a sort most delicious
And he worried about his newborn kitten

That morning, Harry spent the morn just watching and sittin''
He found himself feeling lightheaded and suspicious
Harry couldn''t stop thinking about the fur mitten

Later, Harry saw a coyote recently bitten
He thought the situation had become rather pernicious
And he worried about his newborn kitten

Ron said he thought Harry was embarrassingly smitten
Said he had lately become just a bit too capricious
Harry couldn''t stop thinking about the fur mitten

Harry was worried his hand wouldn''t fit in
The mitten was becoming too vicious
And he worried about his newborn kitten

Harry nosedived like a sharp metal piton
His mind became dangerously malicious
Harry couldn''t stop thinking about the fur mitten
And he worried about his newborn kitten', 3, 4, DATE_SUB(now(), INTERVAL 97*24*61*60 SECOND), DATE_SUB(now(), INTERVAL 97*24*59*60 SECOND)); -- Making the minutes slightly off of 60 randomizes time



INSERT INTO poetree.poems (title, poem, user_id, category_id, date_submitted, date_approved)
VALUES ('A Bloody Good Goodbye', 'If I could muddle in a puddle with a chocolate kangaroo
Or whittle just a little with a carpenter named Drew
If I could hide my whole body in a ballerina''s shoe
Oh, these things would make me happier than an overdue adieu

If I could split a piece of gum with a grinning crocodile
Or stack a prickle of porcupines in one big pointy pile
If could find out what it was that made Mona Lisa smile
Oh these things would make me happier than an I-won''t-see-you-for-awhile

If I could ring a ding the doorbell of a tiny tortoise shell
Or meet a merry mammoth and call her mademoiselle
If I could ride a galloping gazelle down a steep and windy stairwell
Oh, these things would make me happier than a finally farewell

Oh, when a goodbye isn''t good and lasts much longer than it should
I think of silly verses that will not be understood
And as the rhymes keep rhyming
In this crazy mind of mine
When this goodbye finally goes, it will be oh so bloody good', 2, 1, DATE_SUB(now(), INTERVAL 73*24*69*60 SECOND), DATE_SUB(now(), INTERVAL 73*24*65*60 SECOND)); -- Making the minutes slightly off of 60 randomizes time

-- ANOTER POEM
-- The dragon watches as you sleep
-- In corners she does noiselessly creep
-- When you wake, will she be there?
-- Ready to carry you off to her lair?
-- Or perhaps she'll snag you as you slumber
-- Threatening to tear your limbs asunder
-- And so the decision you're forced to make
-- Do you dare sleep on? Do you dare to wake?