-- Create users table
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    otp VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE restaurants (
    id INT AUTO_INCREMENT PRIMARY KEY,

    user_id INT NOT NULL,

    name VARCHAR(255) NOT NULL,
    slug VARCHAR(255) NOT NULL UNIQUE,

    whatsapp VARCHAR(20) NOT NULL,

    membership VARCHAR(50) DEFAULT 'trial',
    expiry_date DATETIME DEFAULT NULL,

    details JSON DEFAULT NULL,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE categories (
    id INT AUTO_INCREMENT PRIMARY KEY,

    restaurant_id INT NOT NULL,

    name VARCHAR(255) NOT NULL,

    sort_order INT DEFAULT 0,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (restaurant_id) REFERENCES restaurants(id) ON DELETE CASCADE
);

CREATE TABLE menu_items (
    id INT AUTO_INCREMENT PRIMARY KEY,

    restaurant_id INT NOT NULL,

    category_id INT DEFAULT NULL,

    name VARCHAR(255) NOT NULL,

    description TEXT,

    image_url VARCHAR(500),

    price DECIMAL(10,2) NOT NULL,

    sale_price DECIMAL(10,2) DEFAULT NULL,

    is_available BOOLEAN DEFAULT TRUE,

    sort_order INT DEFAULT 0,

    details JSON DEFAULT NULL,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (restaurant_id) REFERENCES restaurants(id) ON DELETE CASCADE,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
);


CREATE TABLE platform_settings (

    whatsapp VARCHAR(20) NOT NULL,

    details JSON DEFAULT NULL
);


CREATE TABLE plans (
    id INT AUTO_INCREMENT PRIMARY KEY,

    name VARCHAR(100) NOT NULL,

    description TEXT,

    price DECIMAL(10,2) NOT NULL,

    sale_price DECIMAL(10,2) DEFAULT NULL,

    features JSON,

    cta VARCHAR(255),

    duration INT DEFAULT 30,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);