-- Database schema for Helmet Sales Application

-- Create database
CREATE DATABASE IF NOT EXISTS helmet_sales;
USE helmet_sales;

-- Table for helmets
CREATE TABLE IF NOT EXISTS helmets (
    id INT AUTO_INCREMENT PRIMARY KEY,
    brand VARCHAR(100) NOT NULL,
    stock INT NOT NULL DEFAULT 0,
    price DECIMAL(10,2) NOT NULL,
    image VARCHAR(255) DEFAULT NULL
);

-- Table for payment methods
CREATE TABLE IF NOT EXISTS payment_methods (
    id INT AUTO_INCREMENT PRIMARY KEY,
    method_name VARCHAR(50) NOT NULL UNIQUE
);

-- Table for sales
CREATE TABLE IF NOT EXISTS sales (
    id INT AUTO_INCREMENT PRIMARY KEY,
    helmet_id INT NOT NULL,
    quantity INT NOT NULL,
    total_price DECIMAL(10,2) NOT NULL,
    payment_method_id INT NOT NULL,
    sale_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (helmet_id) REFERENCES helmets(id),
    FOREIGN KEY (payment_method_id) REFERENCES payment_methods(id)
);

-- Insert default payment methods
INSERT INTO payment_methods (method_name) VALUES
('Cash'),
('Credit Card'),
('Debit Card'),
('Transfer Bank')
ON DUPLICATE KEY UPDATE method_name = method_name;
