38 lines
1.6 KiB
SQL
38 lines
1.6 KiB
SQL
-- 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', 'ملاءات السرير');
|