-- AUTH SERVICE
CREATE DATABASE AnimoDish_auth;
USE AnimoDish_auth;



-- USER SERVICE
CREATE DATABASE AnimoDish_user;
USE AnimoDish_user;
CREATE TABLE profiles (
    user_id       INT PRIMARY KEY,  -- same ID from auth_db
    username      VARCHAR(100) NOT NULL,
    phone         VARCHAR(20),
    created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);



-- PRODUCT SERVICE
CREATE DATABASE AnimoDish_product;
USE AnimoDish_product;
CREATE TABLE stalls (
    stall_id      INT AUTO_INCREMENT PRIMARY KEY,
    name          VARCHAR(100) NOT NULL,
    image_url     VARCHAR(500),
    is_active     TINYINT DEFAULT 1
);

CREATE TABLE products (
    product_id    INT AUTO_INCREMENT PRIMARY KEY,
    stall_id      INT,
    name          VARCHAR(100) NOT NULL,
    price         DECIMAL(10,2) NOT NULL,
    image_url     VARCHAR(500),
    description   TEXT,
    stocks        INT DEFAULT 0,
    is_available  TINYINT DEFAULT 1,
    FOREIGN KEY (stall_id) REFERENCES stalls(stall_id)
);


-- ADD PRODUCTS
USE animodish_product;

-- First add a stall
INSERT INTO stalls (name, image_url, is_active) 
VALUES ('Master Siomai', 'https://www.mastersiomai.com/wp-content/uploads/2025/07/ms-siomai-full-logo-updated-300x100.jpeg', 1);

-- Then add products (stall_id = 1)
INSERT INTO products (stall_id, name, price, image_url, description, stocks, is_available) VALUES
(1, 'Pork and Shrimp', 35, 'https://www.mastersiomai.com/wp-content/uploads/2020/06/pork_s.jpg', 'Your all time favorite siomai made with only real pork and shrimp!', 50, 1),
(1, 'Chicken Siomai', 35, 'https://www.mastersiomai.com/wp-content/uploads/2020/06/chick_s.jpg', 'Enjoy the savory taste of chicken in your Siomai.', 50, 1),
(1, 'Beef Siomai', 35, 'https://www.mastersiomai.com/wp-content/uploads/2020/06/beef_s.jpg', 'Juicy and flavorful beef siomai in every bite.', 50, 1),
(1, 'Tuna Siomai', 35, 'https://www.mastersiomai.com/wp-content/uploads/2020/06/tuna_s.jpg', 'Your guilt-free siomai made with healthy tuna.', 50, 1),
(1, "Shark's Fin Siomai", 40, 'https://www.mastersiomai.com/wp-content/uploads/2020/06/shark_s.jpg', "Premium shark\'s fin taste in your affordable siomai.", 50, 1),
(1, 'Japanese Siomai', 40, 'https://www.mastersiomai.com/wp-content/uploads/2020/06/jap_s.jpg', 'Wrapped with authentic nori and real crab sticks on top.', 50, 1);


-- ORDER SERVICE
CREATE DATABASE AnimoDish_order;
USE AnimoDish_order;
CREATE TABLE orders (
    order_id        INT AUTO_INCREMENT PRIMARY KEY,
    user_id         INT NOT NULL,
    total_price     DECIMAL(10,2),
    pickup_time     VARCHAR(10),
    payment_method  ENUM('GCash','COD'),
    status          ENUM('pending','ready','completed','cancelled') DEFAULT 'pending',
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE order_items (
    item_id       INT AUTO_INCREMENT PRIMARY KEY,
    order_id      INT,
    product_id    INT,
    quantity      INT,
    unit_price    DECIMAL(10,2),
    FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);