DROP DATABASE teclearn_we_products;
CREATE DATABASE teclearn_we_products;
USE teclearn_we_products;

-- =========================
-- CATEGORY
-- =========================
CREATE TABLE category (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL UNIQUE,
    description TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

select * from category;

-- =========================
-- PRODUCT
-- =========================
CREATE TABLE product (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    category_id INT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_product_category
        FOREIGN KEY (category_id)
        REFERENCES category(id)
        ON DELETE SET NULL
) ENGINE=InnoDB;

-- =========================
-- PRESENTATION
-- Example: Label, Letter Sheet (20 labels per sheet)
-- =========================
CREATE TABLE presentation (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL UNIQUE,
    units_per_presentation INT NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CHECK (units_per_presentation > 0)
) ENGINE=InnoDB;

-- =========================
-- PRODUCT_PRESENTATION
-- One product can have multiple presentations
-- =========================
CREATE TABLE product_presentation (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT NOT NULL,
    presentation_id INT NOT NULL,
    UNIQUE KEY uk_product_presentation (product_id, presentation_id),
    CONSTRAINT fk_pp_product
        FOREIGN KEY (product_id)
        REFERENCES product(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_pp_presentation
        FOREIGN KEY (presentation_id)
        REFERENCES presentation(id)
        ON DELETE CASCADE
) ENGINE=InnoDB;

-- =========================
-- PACKAGING
-- Example: Box containing 500 Letter Sheets
-- =========================
CREATE TABLE packaging (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    presentation_id INT NOT NULL,
    quantity INT NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CHECK (quantity > 0),
    CONSTRAINT fk_packaging_presentation
        FOREIGN KEY (presentation_id)
        REFERENCES presentation(id)
) ENGINE=InnoDB;

-- =========================
-- ATTRIBUTE 
-- =========================
CREATE TABLE attribute (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL UNIQUE,
    unit VARCHAR(50),
    data_type ENUM('text', 'numeric') DEFAULT 'text',
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- =========================
-- PRODUCT ATTRIBUTE VALUES
-- =========================
CREATE TABLE product_attribute_value (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT NOT NULL,
    attribute_id INT NOT NULL,
    value_text VARCHAR(255),
    value_numeric DECIMAL(10,2),
    UNIQUE KEY uk_product_attribute (product_id, attribute_id),
    CONSTRAINT fk_pav_product
        FOREIGN KEY (product_id)
        REFERENCES product(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_pav_attribute
        FOREIGN KEY (attribute_id)
        REFERENCES attribute(id)
) ENGINE=InnoDB;

-- =========================
-- PACKAGING ATTRIBUTE VALUES
-- (Box dimensions, weight, volume, etc.)
-- =========================
CREATE TABLE packaging_attribute_value (
    id INT AUTO_INCREMENT PRIMARY KEY,
    packaging_id INT NOT NULL,
    attribute_id INT NOT NULL,
    value_text VARCHAR(255),
    value_numeric DECIMAL(10,2),
    UNIQUE KEY uk_packaging_attribute (packaging_id, attribute_id),
    CONSTRAINT fk_pckav_packaging
        FOREIGN KEY (packaging_id)
        REFERENCES packaging(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_pckav_attribute
        FOREIGN KEY (attribute_id)
        REFERENCES attribute(id)
) ENGINE=InnoDB;

-- =========================
-- PRODUCT IMAGES
-- =========================
CREATE TABLE product_image (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT NOT NULL,
    image LONGBLOB,
    is_default BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_product_image
        FOREIGN KEY (product_id)
        REFERENCES product(id)
        ON DELETE CASCADE
) ENGINE=InnoDB;

-- =========================
-- SAMPLE ATTRIBUTES
-- =========================
INSERT INTO attribute (name, unit, data_type) VALUES
('length', 'cm', 'numeric'),
('width', 'cm', 'numeric'),
('height', 'cm', 'numeric'),
('weight', 'kg', 'numeric'),
('volume', 'ltr', 'numeric');


-- ============================================
-- SAMPLE DATA FOR TESTING
-- ============================================

USE teclearn_we_products;

-- =========================
-- CATEGORIES
-- =========================
INSERT INTO category (name, description) VALUES
('Labels', 'Self-adhesive labels for packaging and identification'),
('Letter Sheets', 'Pre-formatted letter sheets for printing'),
('Box Packaging', 'Cardboard boxes for shipping and storage'),
('Specialty Papers', 'Premium papers for special applications');

-- =========================
-- PRODUCTS
-- =========================
INSERT INTO product (name, category_id) VALUES
('Standard Address Label', 1),
('Shipping Label Heavy Duty', 1),
('A4 Letter Sheet Premium', 2),
('A4 Letter Sheet Economy', 2),
('Small Corrugated Box', 3),
('Medium Shipping Box', 3),
('Large Moving Box', 3),
('Glossy Photo Paper', 4),
('Waterproof Label Stock', 1);

-- =========================
-- PRESENTATIONS
-- =========================
INSERT INTO presentation (name, units_per_presentation) VALUES
('Single Label', 1),
('Label Sheet (20 labels)', 20),
('Label Sheet (30 labels)', 30),
('Letter Sheet Pack (100)', 100),
('Box Unit', 1),
('Roll (500 labels)', 500);

-- =========================
-- PRODUCT-PRESENTATION MAPPINGS
-- =========================
INSERT INTO product_presentation (product_id, presentation_id) VALUES
(1, 2), -- Standard Address Label - Label Sheet (20 labels)
(1, 6), -- Standard Address Label - Roll (500 labels)
(2, 3), -- Shipping Label Heavy Duty - Label Sheet (30 labels)
(2, 6), -- Shipping Label Heavy Duty - Roll (500 labels)
(3, 4), -- A4 Letter Sheet Premium - Letter Sheet Pack (100)
(4, 4), -- A4 Letter Sheet Economy - Letter Sheet Pack (100)
(5, 5), -- Small Corrugated Box - Box Unit
(6, 5), -- Medium Shipping Box - Box Unit
(7, 5), -- Large Moving Box - Box Unit
(8, 4), -- Glossy Photo Paper - Letter Sheet Pack (100)
(9, 2), -- Waterproof Label Stock - Label Sheet (20 labels)
(9, 6); -- Waterproof Label Stock - Roll (500 labels)

-- =========================
-- PACKAGING
-- =========================
INSERT INTO packaging (name, presentation_id, quantity) VALUES
('Box of 100 Label Sheets', 2, 100),
('Carton of 10 Rolls', 6, 10),
('Case of 500 Letter Sheets', 4, 5),
('Pallet of 50 Boxes', 5, 50),
('Mini Pack (25 sheets)', 2, 25);

-- =========================
-- ADDITIONAL ATTRIBUTES
-- =========================
INSERT INTO attribute (name, unit, data_type) VALUES
('color', NULL, 'text'),
('material', NULL, 'text'),
('adhesive_type', NULL, 'text'),
('thickness', 'mm', 'numeric'),
('gsm', 'g/m²', 'numeric');

-- =========================
-- PRODUCT ATTRIBUTE VALUES
-- =========================
-- Standard Address Label
INSERT INTO product_attribute_value (product_id, attribute_id, value_text, value_numeric) VALUES
(1, 1, NULL, 5.5),    -- length: 5.5 cm
(1, 2, NULL, 2.5),    -- width: 2.5 cm
(1, 6, 'White', NULL), -- color: White
(1, 7, 'Paper', NULL); -- material: Paper

-- Shipping Label Heavy Duty
INSERT INTO product_attribute_value (product_id, attribute_id, value_text, value_numeric) VALUES
(2, 1, NULL, 10.0),   -- length: 10 cm
(2, 2, NULL, 7.5),    -- width: 7.5 cm
(2, 4, NULL, 0.05),   -- weight: 0.05 kg
(2, 7, 'Vinyl', NULL); -- material: Vinyl

-- Small Corrugated Box
INSERT INTO product_attribute_value (product_id, attribute_id, value_text, value_numeric) VALUES
(5, 1, NULL, 20.0),   -- length: 20 cm
(5, 2, NULL, 15.0),   -- width: 15 cm
(5, 3, NULL, 10.0),   -- height: 10 cm
(5, 4, NULL, 0.25);   -- weight: 0.25 kg

-- Medium Shipping Box
INSERT INTO product_attribute_value (product_id, attribute_id, value_text, value_numeric) VALUES
(6, 1, NULL, 35.0),   -- length: 35 cm
(6, 2, NULL, 25.0),   -- width: 25 cm
(6, 3, NULL, 20.0),   -- height: 20 cm
(6, 4, NULL, 0.50);   -- weight: 0.50 kg

-- A4 Letter Sheet Premium
INSERT INTO product_attribute_value (product_id, attribute_id, value_text, value_numeric) VALUES
(3, 10, NULL, 120),   -- gsm: 120 g/m²
(3, 6, 'Bright White', NULL); -- color: Bright White

-- =========================
-- PACKAGING ATTRIBUTE VALUES
-- =========================
-- Box of 100 Label Sheets
INSERT INTO packaging_attribute_value (packaging_id, attribute_id, value_text, value_numeric) VALUES
(1, 1, NULL, 30.0),   -- length: 30 cm
(1, 2, NULL, 22.0),   -- width: 22 cm
(1, 3, NULL, 10.0),   -- height: 10 cm
(1, 4, NULL, 2.5);    -- weight: 2.5 kg

-- Carton of 10 Rolls
INSERT INTO packaging_attribute_value (packaging_id, attribute_id, value_text, value_numeric) VALUES
(2, 1, NULL, 40.0),   -- length: 40 cm
(2, 2, NULL, 40.0),   -- width: 40 cm
(2, 3, NULL, 15.0),   -- height: 15 cm
(2, 4, NULL, 8.0);    -- weight: 8 kg

-- Pallet of 50 Boxes
INSERT INTO packaging_attribute_value (packaging_id, attribute_id, value_text, value_numeric) VALUES
(4, 1, NULL, 120.0),  -- length: 120 cm
(4, 2, NULL, 100.0),  -- width: 100 cm
(4, 3, NULL, 150.0),  -- height: 150 cm
(4, 4, NULL, 250.0),  -- weight: 250 kg
(4, 5, NULL, 1.8);    -- volume: 1.8 ltr (cubic meters approximation)

-- ============================================
-- END OF SAMPLE DATA
-- ============================================