USE friendbook;

CREATE TABLE login(
    username varchar(25) NOT NULL,
    pword varchar(25) NOT NULL,
    primary key(username));

CREATE TABLE contacts(
    username varchar(25) NOT NULL,
    fname varchar(25) NOT NULL,
    lname varchar(25) NOT NULL,
    primary key(username),
    foreign key(username) references login(username));

CREATE TABLE messages(
    messageID int NOT NULL AUTO_INCREMENT,
    sender varchar(25) NOT NULL,
    recipient varchar(25) NOT NULL,
    message text NOT NULL,
    date date NOT NULL,
    haveread varchar(1),
    primary key(messageID),
    foreign key(sender) references contacts(username),
    foreign key(recipient) references contacts(username));

CREATE TABLE friendList(
    username varchar(25) NOT NULL,
    friend varchar(25) NOT NULL,
    confirm boolean);

INSERT INTO login
    (username, pword)
VALUES 
    ('user1', 'password1'),
    ('user2', 'password2'),
    ('user3', 'password3');

INSERT INTO contacts
    (username, fname, lname)
VALUES
    ('user1', 'Larry', 'Dupree'),
    ('user2', 'John', 'Smith'),
    ('user3', 'Jane', 'Doe');

INSERT INTO messages
    (sender, recipient, message, date, haveread)
VALUES
    ('user1', 'user2', 'hello, how are you', now(), 'Y'),
    ('user2', 'user1', 'im doing good, thanks', now(), 'N');

INSERT INTO friendList
    (username, friend, confirm)
VALUES
    ('user2', 'user1', false),
    ('user1', 'user3', true),
    ('user3', 'user1', true);