https://unitedstatealerts.com/

CREATE TABLE companies (
id INT AUTO_INCREMENT PRIMARY KEY,
company_name VARCHAR(255) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE employees ADD company_id INT NOT NULL DEFAULT 1;
ALTER TABLE products ADD company_id INT NOT NULL DEFAULT 1;
ALTER TABLE sales ADD company_id INT NOT NULL DEFAULT 1;
ALTER TABLE sale_items ADD company_id INT NOT NULL DEFAULT 1;
ALTER TABLE customers ADD company_id INT NOT NULL DEFAULT 1;
ALTER TABLE company_settings ADD company_id INT NOT NULL DEFAULT 1;

CREATE TABLE companies (
id INT AUTO_INCREMENT PRIMARY KEY,
company_name VARCHAR(255) NOT NULL,
owner_name VARCHAR(255),
email VARCHAR(255),
phone VARCHAR(50),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS vehicle_years (
id INT AUTO_INCREMENT PRIMARY KEY,
year INT NOT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY uniq_year (year)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS vehicle_makes (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(80) NOT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY uniq_make (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS vehicle_models (
id INT AUTO_INCREMENT PRIMARY KEY,
make_id INT NOT NULL,
name VARCHAR(120) NOT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY uniq_make_model (make_id, name),
KEY idx_make (make_id),
CONSTRAINT fk_models_make FOREIGN KEY (make_id)
REFERENCES vehicle_makes(id)
ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS engine_sizes (
id INT AUTO_INCREMENT PRIMARY KEY,
size VARCHAR(50) NOT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY uniq_size (size)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS vehicle_makes (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(80) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS vehicle_models (
id INT AUTO_INCREMENT PRIMARY KEY,
make_id INT NOT NULL,
name VARCHAR(120) NOT NULL,
UNIQUE KEY uniq_make_model (make_id, name),
CONSTRAINT fk_models_make
FOREIGN KEY (make_id) REFERENCES vehicle_makes(id)
ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Seed common makes
INSERT IGNORE INTO vehicle_makes (name) VALUES
('Ford'),('Jeep'),('Honda'),('Toyota'),('Chevrolet'),('Nissan'),('Hyundai'),('Kia'),
('Dodge'),('RAM'),('GMC'),('BMW'),('Mercedes-Benz'),('Audi'),('Subaru'),('Volkswagen');

-- Seed a few models (add more anytime)
INSERT IGNORE INTO vehicle_models (make_id, name)
SELECT m.id, x.model
FROM vehicle_makes m
JOIN (
SELECT 'Ford' make, 'F-150' model UNION ALL
SELECT 'Ford', 'Mustang' UNION ALL
SELECT 'Ford', 'Explorer' UNION ALL
SELECT 'Jeep', 'Wrangler' UNION ALL
SELECT 'Jeep', 'Grand Cherokee' UNION ALL
SELECT 'Honda', 'Civic' UNION ALL
SELECT 'Honda', 'Accord' UNION ALL
SELECT 'Honda', 'CR-V' UNION ALL
SELECT 'Toyota', 'Camry' UNION ALL
SELECT 'Toyota', 'Corolla' UNION ALL
SELECT 'Chevrolet', 'Silverado' UNION ALL
SELECT 'Nissan', 'Altima'
) x ON x.make = m.name;

7641de9c897eddcd8c12709df240366c