-- 1. AUTH SERVICE
CREATE DATABASE IF NOT EXISTS AnimoDish_auth;
USE AnimoDish_auth;

CREATE TABLE accounts (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    email         VARCHAR(255) UNIQUE NOT NULL,
    password      VARCHAR(255) NOT NULL,
    role          ENUM('customer','admin', 'superadmin') DEFAULT 'customer',
    stall_id      INT NULL,
    created_at    DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 2. USER SERVICE
CREATE DATABASE IF NOT EXISTS AnimoDish_user;
USE AnimoDish_user;

CREATE TABLE profiles (
    user_id          INT PRIMARY KEY,
    username         VARCHAR(100) NOT NULL,
    phone            VARCHAR(20),
    profile_pic_url  VARCHAR(500),
    created_at       DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 3. PRODUCT SERVICE
CREATE DATABASE IF NOT EXISTS 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,
    status        VARCHAR(20) DEFAULT 'Available', -- renamed from is_available!! check code.
    category      VARCHAR(50), 
    FOREIGN KEY (stall_id) REFERENCES stalls(stall_id)
);

-- 4. ORDER SERVICE
CREATE DATABASE IF NOT EXISTS AnimoDish_order;
USE AnimoDish_order;

CREATE TABLE orders (
    order_id        INT AUTO_INCREMENT PRIMARY KEY,
    user_id         INT NOT NULL,
    stall_id        INT, 
    total_price     DECIMAL(10,2),
    pickup_time     VARCHAR(10),
    payment_method  ENUM('GCash','COD'),
    status          ENUM('pending','ready','completed','cancelled') DEFAULT 'pending',
    created_at      DATETIME 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)
);

-- 5. REVIEW SERVICE
CREATE DATABASE IF NOT EXISTS AnimoDish_review;
USE AnimoDish_review;

CREATE TABLE REVIEWS (
    review_id    INT AUTO_INCREMENT PRIMARY KEY,
    user_id      INT NOT NULL,
    stall_id     INT,
    rating       INT CHECK (rating >= 1 AND rating <= 5),
    comment_text TEXT,
    image_url    VARCHAR(500),
    created_at   DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 6. ALERTS SERVICE
CREATE DATABASE IF NOT EXISTS AnimoDish_alerts;
USE AnimoDish_alerts;

CREATE TABLE ALERTS (
    alert_id     INT AUTO_INCREMENT PRIMARY KEY,
    stall_id     INT NOT NULL,
    alert_type   VARCHAR(50), -- 'promo', 'soldout', 'announcement'
    content      TEXT,
    image_url    VARCHAR(500),
    created_at   DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 7. REACTS SERVICE
CREATE DATABASE IF NOT EXISTS AnimoDish_reacts;
USE AnimoDish_reacts;

CREATE TABLE REVIEW_LIKES (
    like_id         INT AUTO_INCREMENT PRIMARY KEY,
    post_id         INT NOT NULL,
    post_type       INT NOT NULL, -- 1 for Reviews, 2 for Alerts
    user_id         INT NOT NULL,
    reaction_emoji  VARCHAR(10),
    UNIQUE(post_id, post_type, user_id)
);

-- ==========================================
-- DATA INSERTION (PRODUCT SERVICE)
-- ==========================================
USE AnimoDish_product;

-- Add Stalls
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),
('Waffle Time', 'https://encrypted-tbn0.gstatic.com/images?q=tbn:ANd9GcTGYxOWYlZ8eOrgyEVMKv0gPlVP8hSjqg3X0g&s', 1);

-- Add Products (Master Siomai)
INSERT INTO products (stall_id, name, price, image_url, description, stocks, status, category) 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, 'Available', 'Siomai'),
(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, 'Available', 'Siomai'),
(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, 'Available', 'Siomai'),
(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, 'Available', 'Siomai'),
(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, 'Available', 'Siomai'),
(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, 'Available', 'Siomai');

-- Add Products (Waffle Time)
INSERT INTO products (stall_id, name, price, image_url, description, stocks, status, category) VALUES
(2, 'Tuna Salad', 32, 'https://waffletime-assets.s3.ap-southeast-1.amazonaws.com/party-package-page/waffles/01-tuna-salad.png', 'A creamy and seasoned tuna filling...', 50, 'Available', 'Savory'),
(2, 'American Hotdog', 31, 'https://waffletime-assets.s3.ap-southeast-1.amazonaws.com/party-package-page/waffles/02-american-hotdog.png', 'A classic snack featuring a savory hotdog...', 50, 'Available', 'Savory'),
(2, 'TJ Cheesedog', 34, 'https://waffletime-assets.s3.ap-southeast-1.amazonaws.com/party-package-page/waffles/03-tj-cheesedog.png', 'The iconic Filipino "Tender Juicy" hotdog...', 50, 'Available', 'Savory'),
(2, 'Belgian Chocolate', 30, 'https://waffletime-assets.s3.ap-southeast-1.amazonaws.com/party-package-page/waffles/11-belgian-chocolate.png', 'Their best-selling sweet flavor...', 50, 'Available', 'Sweet');



-- COMMANDS??? AHAHHA DON'T RUN FOR SET UP BES AH!

-- Updating Information (UPDATE)
UPDATE table_name 
SET column1 = value1, column2 = value2 
WHERE condition;
--ex:
USE AnimoDish_product;
UPDATE products 
SET price = 45.00, stocks = 100 
WHERE name = 'Japanese Siomai';

-- Deleting Data (DELETE)
DELETE FROM table_name WHERE condition;
--ex:
USE AnimoDish_order;
DELETE FROM orders WHERE order_id = 10;

-- Adding a Column (ALTER TABLE ... ADD)
USE AnimoDish_auth;
ALTER TABLE accounts ADD phone_number VARCHAR(15);
ALTER TABLE accounts ADD middle_name VARCHAR(50) AFTER email;

-- Deleting a Column (ALTER TABLE ... DROP)
ALTER TABLE accounts DROP COLUMN phone_number;

-- Modifying a Column (MODIFY or CHANGE)
ALTER TABLE products MODIFY description TEXT; -- alter column sa ssms
ALTER TABLE products RENAME COLUMN status TO availability_status; -- sp_rename sa ssms

-- lowecase lahat ng database sa mysql for some reason :DDD pati column names