-- AUTH SERVICE
CREATE DATABASE AnimoDish_auth;
GO
USE AnimoDish_auth;

-- USER SERVICE
CREATE DATABASE AnimoDish_user;
GO
USE AnimoDish_user;
CREATE TABLE profiles (
    user_id       INT PRIMARY KEY,
    username      VARCHAR(100) NOT NULL,
    phone         VARCHAR(20),
    created_at    DATETIME DEFAULT GETDATE()
);

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

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

-- ADD PRODUCTS
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);

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), -- Note the double single quote '' for the apostrophe
(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;
GO
USE AnimoDish_order;
CREATE TABLE orders (
    order_id        INT IDENTITY(1,1) PRIMARY KEY,
    user_id         INT NOT NULL,
    total_price     DECIMAL(10,2),
    pickup_time     VARCHAR(10),
    payment_method  VARCHAR(20) CHECK (payment_method IN ('GCash','COD')),
    status          VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending','ready','completed','cancelled')),
    created_at      DATETIME DEFAULT GETDATE()
);

CREATE TABLE order_items (
    item_id       INT IDENTITY(1,1) 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 === false) {
    header('Content-Type: application/json');

-- View User Data
USE AnimoDish_user;
SELECT 'PROFILES' as TableName, * FROM profiles;

-- View Product Data
USE AnimoDish_product;
SELECT 'STALLS' as TableName, * FROM stalls;
SELECT 'PRODUCTS' as TableName, * FROM products;

-- View Order Data
USE AnimoDish_order;
SELECT 'ORDERS' as TableName, * FROM orders;
SELECT 'ORDER_ITEMS' as TableName, * FROM order_items;



-- run para magkapermission

USE AnimoDish_product;
ALTER ROLE db_owner ADD MEMBER [NT AUTHORITY\SYSTEM];

USE AnimoDish_auth;
ALTER ROLE db_owner ADD MEMBER [NT AUTHORITY\SYSTEM];

USE AnimoDish_user;
ALTER ROLE db_owner ADD MEMBER [NT AUTHORITY\SYSTEM];

USE AnimoDish_order;
ALTER ROLE db_owner ADD MEMBER [NT AUTHORITY\SYSTEM];


-- run para mabago un table

ALTER TABLE products DROP CONSTRAINT DF__products__is_ava__3B75D760;
ALTER TABLE products ALTER COLUMN is_available varchar(20);
ALTER TABLE products ADD category varchar(50);
UPDATE products SET status = 'Available' WHERE status = '1';
UPDATE products SET category = 'Siomai' WHERE category IS NULL;

-- add gawa nung stall stall
ALTER TABLE orders ADD stall_id INT;
UPDATE orders SET stall_id= 1 WHERE stall_id IS NULL

-- add waffle time
INSERT INTO stalls (name, image_url, is_active) 
VALUES ('Waffle Time', 'https://encrypted-tbn0.gstatic.com/images?q=tbn:ANd9GcTGYxOWYlZ8eOrgyEVMKv0gPlVP8hSjqg3X0g&s', 1);

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 that offers a lighter, savory alternative to the meat-heavy options.', 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 encased in a warm, fluffy golden waffle.', 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 with a cheesy center, wrapped in a freshly baked waffle.', 50, 'Available', 'Savory'),
(2, 'TJ Chicken Hotdog', 32, 'https://waffletime-assets.s3.ap-southeast-1.amazonaws.com/party-package-page/waffles/04-tj-chickendog.png', 'A leaner but equally tasty option featuring a savory Purefoods Tender Juicy Chicken Hotdog wrapped in a warm, golden-brown waffle.', 50, 'Available', 'Savory'),
(2, 'Cheese Delight', 24, 'https://waffletime-assets.s3.ap-southeast-1.amazonaws.com/party-package-page/waffles/05-cheese-delight.png', 'A simple but crowd-pleasing choice filled with gooey, melted cheese for the ultimate comfort snack.', 50, 'Available', 'Savory'),
(2, 'Ham and Cheese', 32, 'https://waffletime-assets.s3.ap-southeast-1.amazonaws.com/party-package-page/waffles/06-ham-n-cheese.png', 'A timeless combination of savory ham slices and melted cheese—perfect for a quick meal or snack.', 50, 'Available', 'Savory'),
(2, 'Bacon and Cheese', 35, 'https://waffletime-assets.s3.ap-southeast-1.amazonaws.com/party-package-page/waffles/07-bacon-n-cheese.png', 'Savory bacon bits paired with creamy cheese for a smoky and satisfying flavor profile.', 50, 'Available', 'Savory'),
(2, 'German Cheese Franks', 34, 'https://waffletime-assets.s3.ap-southeast-1.amazonaws.com/party-package-page/waffles/08-german-franks.png', 'A step up for sausage lovers, featuring a savory frankfurter stuffed with cheese for a salty, melty bite.', 50, 'Available', 'Savory'),
(2, 'Tuna Cheesedog', 33, 'https://waffletime-assets.s3.ap-southeast-1.amazonaws.com/party-package-page/waffles/09-tuna-cheesedog.png', 'A unique "surf and turf" snack that combines a cheesy hotdog with savory tuna salad, offering a flavorful and creamy twist in every bite.', 50, 'Available', 'Savory'),
(2, 'Ultimate Ube', 26, 'https://waffletime-assets.s3.ap-southeast-1.amazonaws.com/party-package-page/waffles/10-ultimate-ube.png', 'A Filipino favorite filled with sweet and earthy purple yam spread.', 50, 'Available', 'Sweet'),
(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, featuring a rich, dark, and indulgent Belgian chocolate filling.', 50, 'Available', 'Sweet'),
(2, 'Bavarian Cream', 26, 'https://waffletime-assets.s3.ap-southeast-1.amazonaws.com/party-package-page/waffles/12-bavarian-cream.png', 'A smooth and velvety vanilla-flavored custard filling that tastes like a classic dessert.', 50, 'Available', 'Sweet');

-- delete wrong orders
DELETE FROM order_items WHERE order_id = 4;
DELETE FROM orders WHERE order_id = 4;

-- post service

CREATE DATABASE AnimoDish_review;
GO
USE AnimoDish_review;
CREATE TABLE REVIEWS (
    review_id INT PRIMARY KEY IDENTITY(1,1),
    user_id INT NOT NULL,
    stall_id INT,
    rating INT CHECK (rating >= 1 AND rating <= 5),
    comment_text NVARCHAR(MAX),
    image_url NVARCHAR(500), -- For photo reviews
    created_at DATETIME DEFAULT GETDATE()
);

CREATE DATABASE AnimoDish_alerts;
GO
USE AnimoDish_alerts;
CREATE TABLE ALERTS (
    alert_id INT PRIMARY KEY IDENTITY(1,1),
    stall_id INT NOT NULL,
    alert_type NVARCHAR(50), -- 'promo', 'soldout', 'announcement'
    content NVARCHAR(MAX),
    image_url NVARCHAR(500),
    created_at DATETIME DEFAULT GETDATE()
);

-- reaction service

CREATE DATABASE AnimoDish_reacts;
GO
USE AnimoDish_reacts;
CREATE TABLE REVIEW_LIKES (
    like_id INT PRIMARY KEY IDENTITY(1,1),
    post_id INT NOT NULL,
    post_type INT NOT NULL, -- 1 for Reviews, 2 for Alerts
    user_id INT NOT NULL,
    UNIQUE(post_id, post_type, user_id)  -- Prevents duplicate likes
);

-- add profile edits
USE AnimoDish_user;
ALTER TABLE profiles ADD profile_pic_url (NVARCHAR(500));

-- permissions
USE AnimoDish_review;
ALTER ROLE db_owner ADD MEMBER [NT AUTHORITY\SYSTEM];

USE AnimoDish_alerts;
ALTER ROLE db_owner ADD MEMBER [NT AUTHORITY\SYSTEM];

USE AnimoDish_reacts;
ALTER ROLE db_owner ADD MEMBER [NT AUTHORITY\SYSTEM];

-- Add emoji column to store which one was picked
ALTER TABLE AnimoDish_reacts.dbo.REVIEW_LIKES ADD reaction_emoji NVARCHAR(10);