CREATE TABLE IF NOT EXISTS oil_brands (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(150) NOT NULL UNIQUE,
 country VARCHAR(100) NULL,
 website VARCHAR(255) NULL,
 active TINYINT(1) NOT NULL DEFAULT 1,
 created_at DATETIME NULL,
 updated_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS oil_standards (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 code VARCHAR(120) NOT NULL UNIQUE,
 issuer VARCHAR(80) NULL,
 description TEXT NULL,
 active TINYINT(1) NOT NULL DEFAULT 1,
 created_at DATETIME NULL,
 updated_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS oil_suppliers (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(180) NOT NULL UNIQUE,
 contact_name VARCHAR(180) NULL,
 email VARCHAR(180) NULL,
 phone VARCHAR(80) NULL,
 active TINYINT(1) NOT NULL DEFAULT 1,
 created_at DATETIME NULL,
 updated_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS oil_products (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 brand_id INT UNSIGNED NOT NULL,
 name VARCHAR(255) NOT NULL,
 viscosity VARCHAR(40) NULL,
 oil_type VARCHAR(80) NULL,
 base_type VARCHAR(80) NULL,
 package_liters DECIMAL(10,3) NOT NULL DEFAULT 0,
 product_code VARCHAR(100) NULL,
 ean VARCHAR(80) NULL,
 purchase_price DECIMAL(12,2) NOT NULL DEFAULT 0,
 sale_price_with_vat DECIMAL(12,2) NOT NULL DEFAULT 0,
 stock_quantity DECIMAL(12,3) NOT NULL DEFAULT 0,
 quality_category ENUM('Low-cost','Medium','High Quality','OE') NOT NULL DEFAULT 'Medium',
 notes TEXT NULL,
 active TINYINT(1) NOT NULL DEFAULT 1,
 created_at DATETIME NULL,
 updated_at DATETIME NULL,
 INDEX idx_oil_brand(brand_id), INDEX idx_oil_code(product_code), INDEX idx_oil_ean(ean), INDEX idx_oil_viscosity(viscosity),
 CONSTRAINT fk_oil_brand FOREIGN KEY(brand_id) REFERENCES oil_brands(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS oil_product_standards (
 oil_id INT UNSIGNED NOT NULL,
 standard_id INT UNSIGNED NOT NULL,
 PRIMARY KEY(oil_id,standard_id),
 CONSTRAINT fk_ops_oil FOREIGN KEY(oil_id) REFERENCES oil_products(id) ON DELETE CASCADE,
 CONSTRAINT fk_ops_standard FOREIGN KEY(standard_id) REFERENCES oil_standards(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS oil_product_suppliers (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 oil_id INT UNSIGNED NOT NULL,
 supplier_id INT UNSIGNED NOT NULL,
 supplier_code VARCHAR(120) NULL,
 purchase_price DECIMAL(12,2) NOT NULL DEFAULT 0,
 preferred TINYINT(1) NOT NULL DEFAULT 0,
 UNIQUE KEY uq_oil_supplier(oil_id,supplier_id),
 CONSTRAINT fk_opsp_oil FOREIGN KEY(oil_id) REFERENCES oil_products(id) ON DELETE CASCADE,
 CONSTRAINT fk_opsp_supplier FOREIGN KEY(supplier_id) REFERENCES oil_suppliers(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS oil_vehicle_recommendations (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 vin VARCHAR(32) NULL,
 make VARCHAR(120) NULL,
 model VARCHAR(160) NULL,
 year_from SMALLINT NULL,
 year_to SMALLINT NULL,
 engine_code VARCHAR(80) NULL,
 engine_name VARCHAR(180) NULL,
 oil_id INT UNSIGNED NULL,
 standard_id INT UNSIGNED NULL,
 quantity_liters DECIMAL(10,3) NOT NULL,
 min_quantity_liters DECIMAL(10,3) NULL,
 max_quantity_liters DECIMAL(10,3) NULL,
 service_km INT NULL,
 service_days INT NULL,
 notes TEXT NULL,
 created_at DATETIME NULL,
 updated_at DATETIME NULL,
 INDEX idx_ovr_vin(vin), INDEX idx_ovr_engine(engine_code), INDEX idx_ovr_vehicle(make,model,year_from,year_to),
 CONSTRAINT fk_ovr_oil FOREIGN KEY(oil_id) REFERENCES oil_products(id) ON DELETE SET NULL,
 CONSTRAINT fk_ovr_standard FOREIGN KEY(standard_id) REFERENCES oil_standards(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT IGNORE INTO oil_standards(code,issuer,description,active,created_at,updated_at) VALUES
('ACEA A3/B4','ACEA','Uleiuri benzină și diesel cu stabilitate ridicată',1,NOW(),NOW()),
('ACEA C2','ACEA','Low SAPS, economie combustibil',1,NOW(),NOW()),
('ACEA C3','ACEA','Mid SAPS pentru sisteme post-tratare',1,NOW(),NOW()),
('API SP','API','Specificație modernă motoare pe benzină',1,NOW(),NOW()),
('VW 504 00 / 507 00','Volkswagen','LongLife benzină/diesel',1,NOW(),NOW()),
('MB 229.51','Mercedes-Benz','Low SAPS diesel/benzină',1,NOW(),NOW()),
('BMW Longlife-04','BMW','Motoare BMW cu DPF',1,NOW(),NOW()),
('RN0720','Renault','Motoare diesel Renault cu DPF',1,NOW(),NOW()),
('PSA B71 2290','Stellantis/PSA','Motoare PSA cu cerință C2',1,NOW(),NOW()),
('Ford WSS-M2C913-D','Ford','Specificație Ford 5W-30',1,NOW(),NOW());