-- Tyre Brand and Model Setup
CREATE TABLE IF NOT EXISTS tyre_brands (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    description VARCHAR(255) NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_tyre_brand_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS tyre_models (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    brand_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(100) NOT NULL,
    description VARCHAR(255) NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_tyre_model (brand_id, name),
    CONSTRAINT fk_model_brand FOREIGN KEY (brand_id) REFERENCES tyre_brands(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE tyres ADD COLUMN brand_id BIGINT UNSIGNED NULL AFTER vehicle_id;
ALTER TABLE tyres ADD CONSTRAINT fk_tyre_brand FOREIGN KEY (brand_id) REFERENCES tyre_brands(id) ON DELETE SET NULL;
ALTER TABLE tyres ADD COLUMN model_id BIGINT UNSIGNED NULL AFTER brand_id;
ALTER TABLE tyres ADD CONSTRAINT fk_tyre_model FOREIGN KEY (model_id) REFERENCES tyre_models(id) ON DELETE SET NULL;

-- (Seed data and permissions are in the inline PHP migration; see __setup_tyre_brands.php)
INSERT IGNORE INTO permissions (code, description) VALUES
('tyre_brand.view', 'View tyre brands'),
('tyre_brand.create', 'Create tyre brands'),
('tyre_brand.edit', 'Edit tyre brands'),
('tyre_brand.delete', 'Delete tyre brands'),
('tyre_model.view', 'View tyre models'),
('tyre_model.create', 'Create tyre models'),
('tyre_model.edit', 'Edit tyre models'),
('tyre_model.delete', 'Delete tyre models');

INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r CROSS JOIN permissions p
WHERE p.code IN ('tyre_brand.view','tyre_brand.create','tyre_brand.edit','tyre_brand.delete',
                 'tyre_model.view','tyre_model.create','tyre_model.edit','tyre_model.delete');