-- Crear la base de datos
CREATE DATABASE IF NOT EXISTS online_store;
USE online_store;

-- Tabla de Categorías
CREATE TABLE Categories (
    category_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) UNIQUE NOT NULL,
    description TEXT
);

-- Tabla de Productos
CREATE TABLE Products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL,
    stock_quantity INT NOT NULL,
    category_id INT,
    image_url VARCHAR(255),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES Categories(category_id)
);

-- Tabla de Órdenes
CREATE TABLE Orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    total_amount DECIMAL(10,2) NOT NULL,
    status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES Users(user_id)
);

-- Tabla de Ítems de Órdenes
CREATE TABLE OrderItems (
    order_item_id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT,
    product_id INT,
    quantity INT NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES Orders(order_id),
    FOREIGN KEY (product_id) REFERENCES Products(product_id)
);

-- Tabla de Pagos
CREATE TABLE Payments (
    payment_id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT,
    payment_method ENUM('credit_card', 'paypal', 'bank_transfer') NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    status ENUM('pending', 'completed', 'failed') DEFAULT 'pending',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES Orders(order_id)
);

-- Tabla de Envíos
CREATE TABLE Shipping (
    shipping_id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT,
    shipping_address TEXT NOT NULL,
    status ENUM('pending', 'shipped', 'delivered', 'returned') DEFAULT 'pending',
    tracking_number VARCHAR(100),
    shipping_date DATETIME,
    delivery_date DATETIME,
    FOREIGN KEY (order_id) REFERENCES Orders(order_id)
);

-- Tabla de Reseñas de Productos
CREATE TABLE ProductReviews (
    review_id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT,
    user_id INT,
    rating INT CHECK(rating BETWEEN 1 AND 5) NOT NULL,
    comment TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES Products(product_id),
    FOREIGN KEY (user_id) REFERENCES Users(user_id)
);

-- Tabla de Logs de Inventario
CREATE TABLE InventoryLogs (
    log_id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT,
    change_amount INT NOT NULL,
    reason ENUM('purchase', 'restock', 'return') NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES Products(product_id)
);

-- Insertar Datos en Categorías
INSERT INTO Categories (name, description)
VALUES 
('Electronics', 'Devices, gadgets, and electronics'),
('Books', 'Books, magazines, and e-books'),
('Clothing', 'Apparel and clothing items');

-- Insertar Datos en Productos
INSERT INTO Products (name, description, price, stock_quantity, category_id, image_url)
VALUES 
('Smartphone X', 'Latest model of Smartphone X with amazing features.', 999.99, 50, 1, 'smartphone_x.jpg'),
('Laptop Pro', 'High-performance laptop for professionals.', 1499.99, 30, 1, 'laptop_pro.jpg'),
('Fiction Novel', 'Bestselling fiction novel of the year.', 19.99, 100, 2, 'fiction_novel.jpg'),
('Jeans', 'Comfortable and stylish jeans.', 39.99, 200, 3, 'jeans.jpg');

-- Insertar Datos en Órdenes
INSERT INTO Orders (user_id, total_amount, status)
VALUES 
(1, 1019.98, 'processing'),
(2, 1499.99, 'pending');

-- Insertar Datos en Ítems de Órdenes
INSERT INTO OrderItems (order_id, product_id, quantity, price)
VALUES 
(1, 1, 1, 999.99),
(1, 3, 1, 19.99),
(2, 2, 1, 1499.99);

-- Insertar Datos en Pagos
INSERT INTO Payments (order_id, payment_method, amount, status)
VALUES 
(1, 'credit_card', 1019.98, 'completed'),
(2, 'paypal', 1499.99, 'pending');

-- Insertar Datos en Envíos
INSERT INTO Shipping (order_id, shipping_address, status, tracking_number, shipping_date)
VALUES 
(1, '123 Main St, Cityville', 'shipped', 'TRACK12345', NOW());

-- Insertar Datos en Reseñas de Productos
INSERT INTO ProductReviews (product_id, user_id, rating, comment)
VALUES 
(1, 1, 5, 'Great product! Highly recommend.'),
(3, 2, 4, 'Enjoyed the book, but the ending was a bit predictable.');

-- Insertar Datos en Logs de Inventario
INSERT INTO InventoryLogs (product_id, change_amount, reason)
VALUES 
(1, -1, 'purchase'),
(2, -1, 'purchase'),
(3, -1, 'purchase'),
(1, 50, 'restock');

