CREATE TABLE newsletter (
    id INT PRIMARY KEY AUTO_INCREMENT UNIQUE NOT NULL, 
    contactlist int(11) DEFAULT 0,
    newslettername varchar(255) NOT NULL,
    fromname varchar(255) NOT NULL,
    subject varchar(255) NOT NULL,
    content LONGTEXT NOT NULL,
    drafted int(11) DEFAULT 0,
    createddate TIMESTAMP DEFAULT(CURRENT_TIMESTAMP),
    lastmodified TIMESTAMP NOT NULL DEFAULT(CURRENT_TIMESTAMP) ON UPDATE CURRENT_TIMESTAMP,
    scheduled int(11) DEFAULT 0,
    scheduleddate varchar(255),
    sent int(11) DEFAULT 0,
    amountsent int(11) DEFAULT 0,
    opens int(11) DEFAULT 0,
    unsubscribed int(11) DEFAULT 0,
    linkclicks int(11) DEFAULT 0,
    desktop int(11) DEFAULT 0,
    mobile int(11) DEFAULT 0
)


CREATE TABLE newsletterdetails (
    id INT PRIMARY KEY AUTO_INCREMENT UNIQUE NOT NULL,
    newsletterid int(11) NOT NULL,
    contactid int(11) NOT NULL,
    delivered int(11) DEFAULT 0,
    opened int(11) DEFAULT 0,
    openeddate TIMESTAMP NOT NULL DEFAULT(CURRENT_TIMESTAMP) ON UPDATE CURRENT_TIMESTAMP,
    linkclicked int(11) DEFAULT 0,
    unsubscribed int(11) DEFAULT 0,
    devicetype varchar(255)
)

CREATE TABLE newsletterimages (
    id INT PRIMARY KEY AUTO_INCREMENT UNIQUE NOT NULL,
    image varchar(255) NOT NULL,
    imagename varchar(255) NOT NULL,
    createddate TIMESTAMP DEFAULT(CURRENT_TIMESTAMP)
) 

CREATE TABLE communities ( 
    id INT PRIMARY KEY AUTO_INCREMENT UNIQUE NOT NULL, 
    communityname varchar(255) NOT NULL, 
    communityaddress varchar(255) NOT NULL, 
    contractstatus varchar(255) NOT NULL, 
    lastvisited varchar(255), 
    communityphoto varchar(255) DEFAULT 'https://allevohealth.com/admin/assets/images/blankprofilepicture.png',
    communitytype varchar(255) NOT NULL,
    communitymapmarker varchar(255) NOT NULL,
    lastvisitedreason varchar(255) 
    )


CREATE TABLE communityvisits ( 
id INT PRIMARY KEY AUTO_INCREMENT UNIQUE NOT NULL, 
communityid INT NOT NULL, 
visitdate varchar(255) NOT NULL, 
visitreason varchar(255) NOT NULL, 
submissiondate TIMESTAMP DEFAULT(CURRENT_TIMESTAMP)
)


SELECT * FROM communities 
LEFT JOIN 
(SELECT max(visitdate) AS visiteddate, visitreason, communityid FROM communityvisits GROUP BY communityid) 
AS communitvisits 
ON communities.id = communityid;

CREATE TABLE users ( 
id INT PRIMARY KEY AUTO_INCREMENT UNIQUE NOT NULL, 
fullname varchar(255) NOT NULL,  
password varchar(255) NOT NULL, 
email varchar(255) NOT NULL, 
profilepicture varchar(255) NOT NULL DEFAULT 'https://allevohealth.com/admin/assets/images/blankprofilepicture.png',
registered int(11) DEFAULT 0, 
register_date varchar(255) NOT NULL DEFAULT (CURRENT_TIMESTAMP),
rfidtag varchar(255),
scanstatus varchar(255) NOT NULL DEFAULT 'closed'
lastlogin varchar(255)
)


CREATE TABLE reviews (
id INT PRIMARY KEY AUTO_INCREMENT UNIQUE NOT NULL,
ipaddress varchar(255) UNIQUE NOT NULL,
devicetype varchar(255) NOT NULL,
referer varchar(255) NOT NULL,
google varchar(255) NOT NULL DEFAULT 0,
facebook varchar(255) NOT NULL DEFAULT 0,
yelp varchar(255) NOT NULL DEFAULT 0,
register_date varchar(255) NOT NULL DEFAULT (CURRENT_TIMESTAMP)
)

CREATE TABLE Applications (
id INT PRIMARY KEY AUTO_INCREMENT UNIQUE NOT NULL,
position varchar(255) NOT NULL,
desiredshiftdays varchar(255) NOT NULL,
desiredshifttime varchar(255) NOT NULL,
fullname varchar(255) NOT NULL,
email varchar(255) NOT NULL,
phonenumber varchar(255) NOT NULL,
currentaddress varchar(255) NOT NULL,
previousexperience varchar(255) NOT NULL,
resume varchar(255) NOT NULL,
license varchar(255) NOT NULL,
submission_date varchar(255) NOT NULL DEFAULT (CURRENT_TIMESTAMP)
)

CREATE TABLE AIPhoneSession (
id INT PRIMARY KEY AUTO_INCREMENT UNIQUE NOT NULL,
ipaddress varchar(255) NOT NULL,
phonenumber varchar(255) NOT NULL,
reviewed INT NOT NULL DEFAULT 0,
session_date varchar(255) NOT NULL DEFAULT (CURRENT_TIMESTAMP)
)

CREATE TABLE AIPhoneMessage (
id INT PRIMARY KEY AUTO_INCREMENT UNIQUE NOT NULL,
phonesessionid int(11) NOT NULL,
message TEXT NOT NULL,
voicerecording varchar(255),
message_date varchar(255) NOT NULL DEFAULT (CURRENT_TIMESTAMP)
)

CREATE TABLE TelegramMessage (
id INT PRIMARY KEY AUTO_INCREMENT UNIQUE NOT NULL,
fullname varchar(255) NOT NULL,
chatid varchar(255) NOT NULL,
photo varchar(255) NOT NULL,
class varchar(255),
licenseplate varchar(255) DEFAULT "Unknown"
message_date varchar(255) NOT NULL DEFAULT (CURRENT_TIMESTAMP)
)

CREATE TABLE Announcements (
id INT PRIMARY KEY AUTO_INCREMENT UNIQUE NOT NULL,
post varchar(255) NOT NULL,
submitted_by varchar(255) NOT NULL,
submission_date varchar(255) NOT NULL DEFAULT (CURRENT_TIMESTAMP)
)

CREATE TABLE enquiry (
id INT PRIMARY KEY AUTO_INCREMENT UNIQUE NOT NULL,
Name varchar(255) NOT NULL,
Email varchar(255) NOT NULL,
Subject varchar(255) NOT NULL,
Message varchar(255) NOT NULL,
Replied INT NOT NULL DEFAULT 0,
submission_date varchar(255) NOT NULL DEFAULT (CURRENT_TIMESTAMP)
)

CREATE TABLE survey (
id INT PRIMARY KEY AUTO_INCREMENT UNIQUE NOT NULL,
question1 varchar(255) NOT NULL,
question2 varchar(255) NOT NULL,
question3 varchar(255) NOT NULL,
question4 varchar(255) NOT NULL,
question5 varchar(255) NOT NULL,
question6 varchar(255) NOT NULL,
name varchar(255) NOT NULL,
comment varchar(255) NOT NULL,
devicetype varchar(255) NOT NULL,
referer varchar(255) NOT NULL,
datesubmitted varchar(255) NOT NULL DEFAULT (CURRENT_TIMESTAMP)
)

ALTER TABLE survey
ADD question7 varchar(255) NOT NULL,
ADD question8 varchar(255) NOT NULL,
ADD question9 varchar(255) NOT NULL,
ADD question10 varchar(255) NOT NULL,
ADD question11 varchar(255) NOT NULL,
ADD question12 varchar(255) NOT NULL,
ADD question1comment varchar(255),
ADD question2comment varchar(255),
ADD question3comment varchar(255),
ADD question4comment varchar(255),
ADD question5comment varchar(255),
ADD question6comment varchar(255),
ADD question7comment varchar(255),
ADD question8comment varchar(255),
ADD question9comment varchar(255),
ADD question10comment varchar(255),
ADD question11comment varchar(255),
ADD question12comment varchar(255);


CREATE TABLE Uniform (
id INT PRIMARY KEY AUTO_INCREMENT UNIQUE NOT NULL,
fullname varchar(255) NOT NULL,
driverid varchar(255) NOT NULL
)

CREATE TABLE UniformScan (
id INT PRIMARY KEY AUTO_INCREMENT UNIQUE NOT NULL,
employeeid varchar(255) NOT NULL,
scannedphoto varchar(255) NOT NULL,
scan_date varchar(255) NOT NULL DEFAULT (CURRENT_TIMESTAMP)
)


CREATE TABLE ToDoList (
id INT PRIMARY KEY AUTO_INCREMENT UNIQUE NOT NULL,
category varchar(255) NOT NULL,
item varchar(255) NOT NULL,
deadline varchar(255) NOT NULL,
completed int(11) NOT NULL DEFAULT 0,
createddate varchar(255) NOT NULL DEFAULT (CURRENT_TIMESTAMP)
)