-- Banco de Dados: osiricascudo

CREATE TABLE categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    sort_order INT DEFAULT 0
);

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category_id INT,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) UNIQUE NOT NULL,
    description TEXT,
    base_price DECIMAL(10,2) NOT NULL,
    image_url VARCHAR(255),
    is_active TINYINT(1) DEFAULT 1,
    FOREIGN KEY (category_id) REFERENCES categories(id)
);

CREATE TABLE ingredients (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    stock_quantity DECIMAL(10,3) DEFAULT 0, -- Kg ou Unidade
    unit VARCHAR(10) DEFAULT 'un'
);

CREATE TABLE product_ingredients (
    product_id INT,
    ingredient_id INT,
    quantity DECIMAL(10,3) NOT NULL, -- Quanto gasta por produto
    FOREIGN KEY (product_id) REFERENCES products(id),
    FOREIGN KEY (ingredient_id) REFERENCES ingredients(id)
);

CREATE TABLE product_options (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT,
    name VARCHAR(100),
    additional_price DECIMAL(10,2) DEFAULT 0.00,
    FOREIGN KEY (product_id) REFERENCES products(id)
);

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(100),
    customer_phone VARCHAR(20),
    customer_address TEXT,
    total_amount DECIMAL(10,2),
    payment_method VARCHAR(50), -- Pix, Cartao, Dinheiro
    status VARCHAR(20) DEFAULT 'pending', -- pending, preparing, ready, delivery, completed
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE order_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT,
    product_name VARCHAR(100),
    quantity INT,
    unit_price DECIMAL(10,2),
    total_price DECIMAL(10,2),
    notes TEXT,
    FOREIGN KEY (order_id) REFERENCES orders(id)
);

-- Dados Iniciais (Seed)
INSERT INTO categories (name, sort_order) VALUES ('Hambúrgueres de Siri', 1), ('Acompanhamentos', 2), ('Bebidas', 3);

INSERT INTO products (category_id, name, slug, description, base_price, image_url) VALUES 
(1, 'Hambúrguer de Siri Clássico', 'hamburguer-siri-classico', 'Pão macio, hambúrguer de siri secreto, alface crocante e queijo.', 25.00, 'assets/img/burger.png'),
(1, 'Siri Duplo', 'siri-duplo', 'Dobro de carne, dobro de sabor.', 35.00, 'assets/img/burger_double.png'),
(2, 'Batata Coral', 'batata-coral', 'Batatas fritas crocantes com tempero especial.', 15.00, 'assets/img/fries.png'),
(3, 'Refrigerante Kelp', 'kelp-shake', 'O famoso shake de algas.', 12.00, 'assets/img/drink.png');

INSERT INTO ingredients (name, stock_quantity, unit) VALUES
('Carne de Siri', 100, 'un'),
('Pão de Hambúrguer', 100, 'un'),
('Alface', 50, 'maço'),
('Queijo', 200, 'fatia');

-- Relacionar ingredientes (Simplificado)
INSERT INTO product_ingredients (product_id, ingredient_id, quantity) VALUES
(1, 1, 1), (1, 2, 1), (1, 3, 0.1), (1, 4, 1),
(2, 1, 2), (2, 2, 1), (2, 3, 0.1), (2, 4, 2);
