-- MsSQL
CREATE TABLE dbo.Categories (
 category_id INT IDENTITY(1,1) PRIMARY KEY,
 category_name VARCHAR(100) NOT NULL
);
IF OBJECT_ID('dbo.Categories', 'U') IS NOT NULL DROP
TABLE dbo.Categories;
CREATE TABLE dbo.Categories (
 category_id INT IDENTITY(1,1) PRIMARY KEY,
 category_name VARCHAR(100) NOT NULL
);


-- MsSQL
CREATE TABLE dbo.Suppliers (
 supplier_id INT IDENTITY(1,1) PRIMARY KEY,
 supplier_name VARCHAR(100) NOT NULL,
 contact_name VARCHAR(100),
 phone VARCHAR(50),
 email VARCHAR(100)
);

--MSSQL
CREATE TABLE dbo.Products (
 product_id INT IDENTITY(1,1) PRIMARY KEY,
 product_name VARCHAR(150) NOT NULL,
 description TEXT,
 category_id INT,
 supplier_id INT,
 price DECIMAL(10,2),
 image_url VARCHAR(255),
 CONSTRAINT FK_Products_Categories FOREIGN KEY (category_id) REFERENCES dbo.Categories(category_id),
 CONSTRAINT FK_Products_Suppliers FOREIGN KEY (supplier_id) REFERENCES dbo.Suppliers(supplier_id)
);


--MSSQL
CREATE TABLE dbo.Customers (
 customer_id INT IDENTITY(1,1) PRIMARY KEY,
 customer_name VARCHAR(100) NOT NULL,
 address VARCHAR(255),
 city VARCHAR(100),
 country VARCHAR(100),
 email VARCHAR(100)
);


CREATE TABLE dbo.Shipping (
 shipping_id INT IDENTITY(1,1) PRIMARY KEY,
 shipping_method VARCHAR(100) NOT NULL,
 shipping_cost DECIMAL(10,2)
 );


 --MSSQL
CREATE TABLE dbo.Orders (
 order_id INT IDENTITY(1,1) PRIMARY KEY,
 customer_id INT,
 order_date DATETIME,
 status VARCHAR(50),
 shipping_id INT,
 CONSTRAINT FK_Orders_Customers FOREIGN KEY (customer_id) REFERENCES dbo.Customers(customer_id),
 CONSTRAINT FK_Orders_Shipping FOREIGN KEY (shipping_id) REFERENCES dbo.Shipping(shipping_id)
);


--MSSQL
CREATE TABLE dbo.Order_Items (
 order_item_id INT IDENTITY(1,1) PRIMARY KEY,
 order_id INT,
 product_id INT,
 quantity INT,
 CONSTRAINT FK_OrderItems_Orders FOREIGN KEY (order_id) REFERENCES dbo.Orders(order_id),
 CONSTRAINT FK_OrderItems_Products FOREIGN KEY (product_id) REFERENCES dbo.Products(product_id)
);


--MSQL
CREATE TABLE dbo.Payments (
 payment_id INT IDENTITY(1,1) PRIMARY KEY,
 order_id INT,
 payment_method VARCHAR(50),
 payment_date DATETIME,
 amount DECIMAL(10,2),
 CONSTRAINT FK_Payments_Orders FOREIGN KEY (order_id) REFERENCES dbo.Orders(order_id)
);


--MSSQL
CREATE TABLE dbo.Reviews (
 review_id INT IDENTITY(1,1) PRIMARY KEY,
 product_id INT,
 customer_id INT,
 rating INT,
 comment TEXT,
 CONSTRAINT FK_Reviews_Products FOREIGN KEY (product_id) REFERENCES dbo.Products(product_id),
 CONSTRAINT FK_Reviews_Customers FOREIGN KEY (customer_id) REFERENCES dbo.Customers(customer_id)
);


--MSSQL Veri Ekleme
-- Insert into Categories
INSERT INTO dbo.Categories (category_name) VALUES
('Electronics'),
('Books'),
('Clothing');
-- Insert into Suppliers
INSERT INTO dbo.Suppliers (supplier_name, contact_name, phone, email) VALUES
('TechSupplier Inc.', 'John Doe', '+1-202-555-0147', 'john@techsupplier.com'),
('BookWorld', 'Jane Smith', '+1-202-555-0199', 'jane@bookworld.com');
-- Insert into Products
INSERT INTO dbo.Products (product_name, description, category_id, supplier_id, price, image_url) VALUES
('Smartphone XYZ', 'A high-end smartphone.', 1, 1, 699.00,'http://example.com/images/smartphone_xyz.jpg'),
('Laptop ABC', 'Lightweight laptop with 16GB RAM.', 1, 1, 999.99, 'http://example.com/images/laptop_abc.jpg'),
('Novel: The Great Adventure', 'A thrilling adventure novel.', 2, 2, 19.99, 'http://example.com/images/great_adventure.jpg');
-- Insert into Customers
INSERT INTO dbo.Customers (customer_name, address, city, country, email) VALUES
('Alice Johnson', '123 Main St', 'New York', 'USA', 'alice@example.com'),
('Bob Williams', '456 Elm Ave', 'Chicago', 'USA', 'bob@example.com');
-- Insert into Shipping
INSERT INTO dbo.Shipping (shipping_method, shipping_cost) VALUES
('Standard', 5.00),
('Express', 15.00);
-- Insert into Orders
INSERT INTO dbo.Orders (customer_id, order_date, status, shipping_id) VALUES
(1, '2024-12-19 10:30:00', 'Processing', 1),
(2, '2024-12-19 11:00:00', 'Shipped', 2);
-- Insert into Order_Items
INSERT INTO dbo.Order_Items (order_id, product_id, quantity) VALUES
(1, 1, 2), -- 2 x Smartphone XYZ
(1, 3, 1), -- 1 x Novel
(2, 2, 1); -- 1 x Laptop ABC
-- Insert into Payments
-- For amount calculation, you must do it manually in MSSQL
INSERT:
-- Order 1 total = (2*699.00) + 19.99 + 5.00 = 699*2 + 19.99 + 5.00 = 1399 + 19.99 + 5 = 1423.99
-- Order 2 total = 999.99 + 15.00 = 1014.99
INSERT INTO dbo.Payments (order_id, payment_method, payment_date, amount) VALUES
(1, 'Credit Card', '2024-12-19 10:40:00', 1423.99),
(2, 'PayPal', '2024-12-19 11:10:00', 1014.99);
-- Insert into Reviews
INSERT INTO dbo.Reviews (product_id, customer_id, rating, comment) VALUES
(1, 1, 5, 'Excellent smartphone!'),
(3, 1, 4, 'Good read, enjoyed it.'),
(2, 2, 3, 'Laptop is decent, but battery life could be better.');