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

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 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);
