CREATE DATABASE dynhdr_quote;

USE dynhdr_quote;

-- Table for dynamic headers per product category
CREATE TABLE headers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category VARCHAR(50),
    hdr1 VARCHAR(50),
    hdr2 VARCHAR(50),
    hdr3 VARCHAR(50),
    hdr4 VARCHAR(50),
    hdr5 VARCHAR(50)
);

-- Insert headers for Flat Steel and Round Bar categories
INSERT INTO headers (category, hdr1, hdr2, hdr3, hdr4, hdr5)
VALUES
('Flat Steel', 'Thickness', 'Width', 'Length', 'Grade', 'Finish'),
('Round Bar', 'Diameter', 'Length', 'Weight', 'Tolerance', 'Alloy');

-- Table for products
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category VARCHAR(50),
    product_name VARCHAR(100),
    hdr1_val VARCHAR(50),
    hdr2_val VARCHAR(50),
    hdr3_val VARCHAR(50),
    hdr4_val VARCHAR(50),
    hdr5_val VARCHAR(50),
    qty INT
);

-- Insert sample products for Flat Steel and Round Bar categories
INSERT INTO products (category, product_name, hdr1_val, hdr2_val, hdr3_val, hdr4_val, hdr5_val, qty)
VALUES
('Flat Steel', 'Flat Steel Product 1', '10mm', '200mm', '2m', 'A36', 'Polished', 0),
('Flat Steel', 'Flat Steel Product 2', '12mm', '150mm', '3m', 'A572', 'Rough', 0),
('Flat Steel', 'Flat Steel Product 3', '15mm', '300mm', '1.5m', 'A36', 'Polished', 0),
('Round Bar', 'Round Bar Product 1', '25mm', '6m', '10kg', 'H9', 'Alloy Steel', 0),
('Round Bar', 'Round Bar Product 2', '30mm', '3m', '15kg', 'H10', 'Carbon Steel', 0);

-- Table for quotes
CREATE TABLE quotes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT,
    qty INT,
    category VARCHAR(50)
);

-- Table for tracking categories and original pages for 'Go Back to Products' functionality
CREATE TABLE quote_origin (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category VARCHAR(50),
    page_url VARCHAR(255)
);