-- Sun Rubber Database Schema for cPanel (MySQL/MariaDB)

-- 1. Pattern Groups (For Products)
CREATE TABLE IF NOT EXISTS pattern_groups (
    id VARCHAR(50) PRIMARY KEY,
    pattern_family VARCHAR(100),
    description JSON,
    tyre_position JSON,
    application JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 2. Products
CREATE TABLE IF NOT EXISTS products (
    id VARCHAR(100) PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    pattern_group_id VARCHAR(50),
    width DOUBLE,
    thickness DOUBLE,
    rim_diameter DOUBLE,
    weight DOUBLE,
    image VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (pattern_group_id) REFERENCES pattern_groups(id) ON DELETE SET NULL
);

-- 3. R&D Equipment
CREATE TABLE IF NOT EXISTS rd_equipment (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    standard VARCHAR(100),
    icon VARCHAR(50),
    image VARCHAR(255),
    description TEXT,
    category VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 4. News
CREATE TABLE IF NOT EXISTS news (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    category VARCHAR(100),
    date_label VARCHAR(50), -- e.g. "FEB 2026"
    excerpt TEXT,
    content JSON, -- Array of paragraphs
    image VARCHAR(255),
    author VARCHAR(100) DEFAULT 'Sun Rubber Team',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 5. Enquiries (Existing structure)
CREATE TABLE IF NOT EXISTS enquiries (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    email VARCHAR(255),
    phone VARCHAR(50),
    company VARCHAR(255),
    country VARCHAR(100),
    subject VARCHAR(255),
    message TEXT,
    product_context JSON,
    status VARCHAR(50) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);