CREATE TABLE IF NOT EXISTS users(id INT AUTO_INCREMENT PRIMARY KEY,username VARCHAR(80) UNIQUE NOT NULL,password_hash VARCHAR(255) NOT NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE IF NOT EXISTS screens(id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(120) NOT NULL,slug VARCHAR(80) UNIQUE NOT NULL,title VARCHAR(150) NOT NULL,subtitle VARCHAR(255) DEFAULT '',active TINYINT(1) DEFAULT 1,refresh_seconds INT DEFAULT 15,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE IF NOT EXISTS categories(id INT AUTO_INCREMENT PRIMARY KEY,screen_id INT NOT NULL,name VARCHAR(120) NOT NULL,sort_order INT DEFAULT 0,active TINYINT(1) DEFAULT 1,FOREIGN KEY(screen_id) REFERENCES screens(id) ON DELETE CASCADE);
CREATE TABLE IF NOT EXISTS items(id INT AUTO_INCREMENT PRIMARY KEY,category_id INT NOT NULL,name VARCHAR(150) NOT NULL,description VARCHAR(255) DEFAULT '',price VARCHAR(40) DEFAULT '',price_large VARCHAR(40) DEFAULT '',badge VARCHAR(60) DEFAULT '',sold_out TINYINT(1) DEFAULT 0,active TINYINT(1) DEFAULT 1,sort_order INT DEFAULT 0,FOREIGN KEY(category_id) REFERENCES categories(id) ON DELETE CASCADE);
CREATE TABLE IF NOT EXISTS offers(id INT AUTO_INCREMENT PRIMARY KEY,screen_id INT NULL,title VARCHAR(150) NOT NULL,body VARCHAR(255) DEFAULT '',price VARCHAR(60) DEFAULT '',start_at DATETIME NULL,end_at DATETIME NULL,active TINYINT(1) DEFAULT 1,priority INT DEFAULT 0,FOREIGN KEY(screen_id) REFERENCES screens(id) ON DELETE CASCADE);
INSERT IGNORE INTO screens(id,name,slug,title,subtitle) VALUES (1,'Coffee','coffee','COFFEE','Speciality coffee, made properly'),(2,'Cold Drinks','cold','COLD DRINKS','Iced coffee, boba, smoothies & more'),(3,'Food & Specials','food','FOOD & SPECIALS','Cakes, hot food & current offers');
CREATE TABLE IF NOT EXISTS layouts(id INT AUTO_INCREMENT PRIMARY KEY,screen_id INT NOT NULL UNIQUE,draft_json LONGTEXT NULL,published_json LONGTEXT NULL,updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,published_at DATETIME NULL,FOREIGN KEY(screen_id) REFERENCES screens(id) ON DELETE CASCADE);