-- Create categories table
CREATE TABLE IF NOT EXISTS categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name_en VARCHAR(255) NOT NULL,
    name_ar VARCHAR(255) NOT NULL,
    image_url VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Add new columns to items table
ALTER TABLE items ADD COLUMN category_id INT AFTER id;
ALTER TABLE items ADD COLUMN image_url VARCHAR(255) AFTER name_ar;
ALTER TABLE items ADD COLUMN vat_percent DECIMAL(5, 2) DEFAULT 0.00 AFTER image_url;

-- Add foreign key for category
ALTER TABLE items ADD CONSTRAINT fk_item_category FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL;

-- Create item_variants table (interpreting "aspire table" as "a separate table for variants")
CREATE TABLE IF NOT EXISTS item_variants (
    id INT AUTO_INCREMENT PRIMARY KEY,
    item_id INT NOT NULL,
    name_en VARCHAR(255) NOT NULL,
    name_ar VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (item_id) REFERENCES items(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Update prices table to include variant_id (optional, making it more flexible)
ALTER TABLE prices ADD COLUMN variant_id INT AFTER item_id;
ALTER TABLE prices ADD CONSTRAINT fk_price_variant FOREIGN KEY (variant_id) REFERENCES item_variants(id) ON DELETE CASCADE;

-- Insert default categories
INSERT IGNORE INTO categories (name_en, name_ar) VALUES 
('Men', 'رجال'),
('Women', 'نساء'),
('Kids', 'أطفال'),
('Bed Sheets', 'ملاءات السرير');