CREATE DATABASE IF NOT EXISTS cafe_corner_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE cafe_corner_db;

CREATE TABLE IF NOT EXISTS admins (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(120) NOT NULL,
    email VARCHAR(190) NOT NULL UNIQUE,
    password VARCHAR(190) NOT NULL
);

CREATE TABLE IF NOT EXISTS users (
    id VARCHAR(80) PRIMARY KEY,
    name VARCHAR(120) NOT NULL,
    email VARCHAR(190) NOT NULL,
    password VARCHAR(190) NOT NULL,
    item_add TINYINT(1) NOT NULL DEFAULT 1,
    item_edit TINYINT(1) NOT NULL DEFAULT 0,
    item_delete TINYINT(1) NOT NULL DEFAULT 0,
    bill_add TINYINT(1) NOT NULL DEFAULT 1,
    bill_edit TINYINT(1) NOT NULL DEFAULT 0,
    bill_delete TINYINT(1) NOT NULL DEFAULT 0,
    show_item_discount TINYINT(1) NOT NULL DEFAULT 0,
    show_bill_discount TINYINT(1) NOT NULL DEFAULT 0,
    show_bill_preview TINYINT(1) NOT NULL DEFAULT 1
);

CREATE TABLE IF NOT EXISTS items (
    id VARCHAR(80) PRIMARY KEY,
    name VARCHAR(190) NOT NULL,
    unit VARCHAR(120) NOT NULL,
    purchase_rate VARCHAR(40) NOT NULL,
    mrp VARCHAR(40) NOT NULL,
    packing VARCHAR(120) NOT NULL,
    special_rate VARCHAR(40) NOT NULL,
    date_from DATE NOT NULL,
    date_to DATE NOT NULL
);

CREATE TABLE IF NOT EXISTS app_stats (
    id INT PRIMARY KEY,
    total_orders INT NOT NULL DEFAULT 0
);

INSERT INTO admins (name, email, password)
SELECT 'Cafe Admin', 'admin@cafecorner.com', '123456'
WHERE NOT EXISTS (SELECT 1 FROM admins WHERE email = 'admin@cafecorner.com');

INSERT INTO users (
    id, name, email, password, item_add, item_edit, item_delete,
    bill_add, bill_edit, bill_delete, show_item_discount,
    show_bill_discount, show_bill_preview
)
SELECT
    'user_1', 'User1', 'user1@cafecorner.com', '123456',
    1, 1, 0, 1, 0, 0, 1, 0, 1
WHERE NOT EXISTS (SELECT 1 FROM users WHERE id = 'user_1');

INSERT INTO items (
    id, name, unit, purchase_rate, mrp, packing, special_rate, date_from, date_to
)
SELECT
    'item_1', 'Cold Coffee', 'Glass', '80', '120', '1 Glass', '105', '2026-05-01', '2026-05-31'
WHERE NOT EXISTS (SELECT 1 FROM items WHERE id = 'item_1');

INSERT INTO items (
    id, name, unit, purchase_rate, mrp, packing, special_rate, date_from, date_to
)
SELECT
    'item_2', 'Chicken Sandwich', 'Plate', '130', '180', '1 Plate', '165', '2026-05-05', '2026-05-28'
WHERE NOT EXISTS (SELECT 1 FROM items WHERE id = 'item_2');

INSERT INTO app_stats (id, total_orders)
SELECT 1, 128
WHERE NOT EXISTS (SELECT 1 FROM app_stats WHERE id = 1);