-- Create the `products` table if it doesn't already exist
CREATE TABLE IF NOT EXISTS products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL,
    stock INT DEFAULT 0,
    image_url VARCHAR(255)
);

-- Create the `customers` table if it doesn't already exist
CREATE TABLE IF NOT EXISTS customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    phone VARCHAR(20),
    address TEXT
);













CREATE TABLE IF NOT EXISTS orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT DEFAULT 1,
    total DECIMAL(10, 2),
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);


DELIMITER //

CREATE TRIGGER calculate_order_total
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
    DECLARE product_price DECIMAL(10, 2);
    
    -- Fetch the product price
    SELECT price INTO product_price
    FROM products
    WHERE id = NEW.product_id;

    -- Calculate the total
    SET NEW.total = NEW.quantity * product_price;
END //

DELIMITER ;


CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100) NOT NULL UNIQUE,   -- Username should be unique
    password VARCHAR(255) NOT NULL,          -- To store hashed password
    email VARCHAR(150) NOT NULL UNIQUE,      -- Email should be unique
    first_name VARCHAR(100) NOT NULL,        -- First name of the user
    last_name VARCHAR(100) NOT NULL,         -- Last name of the user
    is_admin BOOLEAN DEFAULT 0,              -- 1 if admin, 0 if regular user
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP  -- Timestamp when user was created
);






INSERT INTO users (username, password, email, first_name, last_name, is_admin) 
VALUES 
('clifton_mcfarlane', 'Capstone', 'cmcfarlane@uwm.edu', 'Clifton', 'McFarlane', 1);

INSERT INTO users (username, password, email, first_name, last_name, is_admin) 
VALUES 
('karl_campbell', 'Campbell', 'kcampbell@golf.us', 'Karl', 'Campbell', 0),
('zachary_francis', 'Francis', 'zfrancis@uwm.edu', 'Zachary', 'Francis', 0),

hashed
-- Insert the admin user
INSERT INTO users (username, password, email, first_name, last_name, is_admin) 
VALUES 
('c_mcfarlane', 'Capstone', 'cmcfarlane@matc.edu', 'Clifton', 'McFarlane', 1);

-- Insert regular users
INSERT INTO users (username, password, email, first_name, last_name, is_admin) 
VALUES 
('karl_campbell', 'Campbell', 'kcampbell@golf.us', 'Karl', 'Campbell', 0),
('zachary_francis', 'Francis', 'zfrancis@uwm.edu', 'Zachary', 'Francis', 0);