CREATE TABLE stores (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    owner_name VARCHAR(100),
    email VARCHAR(100),
    password VARCHAR(255),
    status TINYINT DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);


CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    store_id INT NULL,
    name VARCHAR(100),
    email VARCHAR(100),
    password VARCHAR(255),
    role ENUM('superadmin','admin') DEFAULT 'admin',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);


CREATE TABLE categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    store_id INT,
    name VARCHAR(100),
    type ENUM('dining','takeaway','online'),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);


CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    store_id INT,
    category_id INT,
    name VARCHAR(150),
    image VARCHAR(255),
    status TINYINT DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);


CREATE TABLE product_variations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT,
    name VARCHAR(50), -- Small / Medium / Large
    price DECIMAL(10,3)
);


CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    store_id INT,
    order_type ENUM('dinein','takeaway','online'),
    source VARCHAR(50), -- zomato/swiggy/null
    table_no VARCHAR(50),
    total DECIMAL(10,3),
    status VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);


CREATE TABLE order_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT,
    product_id INT,
    variation_id INT,
    qty INT,
    price DECIMAL(10,3),
    total DECIMAL(10,3)
);