-- Universal Vehicle Identification & Origin Engine (UVI-ODE)
-- Standalone MySQL / MariaDB Dump for cPanel phpMyAdmin
-- Generated: 2026-10-09 19:54:51

SET FOREIGN_KEY_CHECKS = 0;
SET SQL_MODE = 'NO_AUTO_VALUE_ON_ZERO';

DROP TABLE IF EXISTS `geo_countries`;
CREATE TABLE `geo_countries` (
  `iso_alpha2` varchar(2) NOT NULL,
  `iso_alpha3` varchar(3) NOT NULL,
  `country_name` varchar(100) NOT NULL,
  `continent_code` varchar(10) NOT NULL,
  `region_name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`iso_alpha2`),
  UNIQUE KEY `geo_countries_iso_alpha3_unique` (`iso_alpha3`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `geo_countries` (`iso_alpha2`, `iso_alpha3`, `country_name`, `continent_code`, `region_name`) VALUES
('US', 'USA', 'United States', 'NA', 'North America'),
('CA', 'CAN', 'Canada', 'NA', 'North America'),
('MX', 'MEX', 'Mexico', 'NA', 'North America'),
('CR', 'CRI', 'Costa Rica', 'NA', 'North America'),
('KY', 'CYM', 'Cayman Islands', 'NA', 'North America'),
('JP', 'JPN', 'Japan', 'AS', 'Asia'),
('KR', 'KOR', 'South Korea', 'AS', 'Asia'),
('CN', 'CHN', 'China', 'AS', 'Asia'),
('IN', 'IND', 'India', 'AS', 'Asia'),
('ID', 'IDN', 'Indonesia', 'AS', 'Asia'),
('TH', 'THA', 'Thailand', 'AS', 'Asia'),
('MY', 'MYS', 'Malaysia', 'AS', 'Asia'),
('PH', 'PHL', 'Philippines', 'AS', 'Asia'),
('SG', 'SGP', 'Singapore', 'AS', 'Asia'),
('TW', 'TWN', 'Taiwan', 'AS', 'Asia'),
('VN', 'VNM', 'Vietnam', 'AS', 'Asia'),
('TR', 'TUR', 'Turkey', 'AS', 'Asia'),
('IR', 'IRN', 'Iran', 'AS', 'Asia'),
('PK', 'PAK', 'Pakistan', 'AS', 'Asia'),
('AE', 'ARE', 'United Arab Emirates', 'AS', 'Asia'),
('SA', 'SAU', 'Saudi Arabia', 'AS', 'Asia'),
('MM', 'MMR', 'Myanmar', 'AS', 'Asia'),
('DE', 'DEU', 'Germany', 'EU', 'Europe'),
('GB', 'GBR', 'United Kingdom', 'EU', 'Europe'),
('FR', 'FRA', 'France', 'EU', 'Europe'),
('IT', 'ITA', 'Italy', 'EU', 'Europe'),
('ES', 'ESP', 'Spain', 'EU', 'Europe'),
('SE', 'SWE', 'Sweden', 'EU', 'Europe'),
('PL', 'POL', 'Poland', 'EU', 'Europe'),
('CZ', 'CZE', 'Czech Republic', 'EU', 'Europe'),
('HU', 'HUN', 'Hungary', 'EU', 'Europe'),
('AT', 'AUT', 'Austria', 'EU', 'Europe'),
('BE', 'BEL', 'Belgium', 'EU', 'Europe'),
('NL', 'NLD', 'Netherlands', 'EU', 'Europe'),
('PT', 'PRT', 'Portugal', 'EU', 'Europe'),
('CH', 'CHE', 'Switzerland', 'EU', 'Europe'),
('RO', 'ROU', 'Romania', 'EU', 'Europe'),
('SK', 'SVK', 'Slovakia', 'EU', 'Europe'),
('FI', 'FIN', 'Finland', 'EU', 'Europe'),
('RU', 'RUS', 'Russia', 'EU', 'Europe'),
('RS', 'SRB', 'Serbia', 'EU', 'Europe'),
('HR', 'HRV', 'Croatia', 'EU', 'Europe'),
('SI', 'SVN', 'Slovenia', 'EU', 'Europe'),
('LT', 'LTU', 'Lithuania', 'EU', 'Europe'),
('LV', 'LVA', 'Latvia', 'EU', 'Europe'),
('DK', 'DNK', 'Denmark', 'EU', 'Europe'),
('IE', 'IRL', 'Ireland', 'EU', 'Europe'),
('BG', 'BGR', 'Bulgaria', 'EU', 'Europe'),
('GR', 'GRC', 'Greece', 'EU', 'Europe'),
('AU', 'AUS', 'Australia', 'OC', 'Oceania'),
('NZ', 'NZL', 'New Zealand', 'OC', 'Oceania'),
('BR', 'BRA', 'Brazil', 'SA', 'South America'),
('AR', 'ARG', 'Argentina', 'SA', 'South America'),
('CL', 'CHL', 'Chile', 'SA', 'South America'),
('CO', 'COL', 'Colombia', 'SA', 'South America'),
('EC', 'ECU', 'Ecuador', 'SA', 'South America'),
('PE', 'PER', 'Peru', 'SA', 'South America'),
('UY', 'URY', 'Uruguay', 'SA', 'South America'),
('VE', 'VEN', 'Venezuela', 'SA', 'South America'),
('PY', 'PRY', 'Paraguay', 'SA', 'South America'),
('ZA', 'ZAF', 'South Africa', 'AF', 'Africa'),
('CI', 'CIV', 'Ivory Coast', 'AF', 'Africa'),
('AO', 'AGO', 'Angola', 'AF', 'Africa'),
('KE', 'KEN', 'Kenya', 'AF', 'Africa'),
('TZ', 'TZA', 'Tanzania', 'AF', 'Africa'),
('BJ', 'BEN', 'Benin', 'AF', 'Africa'),
('MG', 'MDG', 'Madagascar', 'AF', 'Africa'),
('TN', 'TUN', 'Tunisia', 'AF', 'Africa'),
('EG', 'EGY', 'Egypt', 'AF', 'Africa'),
('MA', 'MAR', 'Morocco', 'AF', 'Africa'),
('ZM', 'ZMB', 'Zambia', 'AF', 'Africa'),
('ET', 'ETH', 'Ethiopia', 'AF', 'Africa'),
('MZ', 'MOZ', 'Mozambique', 'AF', 'Africa'),
('GH', 'GHA', 'Ghana', 'AF', 'Africa'),
('NG', 'NGA', 'Nigeria', 'AF', 'Africa');

DROP TABLE IF EXISTS `wmi_registry`;
CREATE TABLE `wmi_registry` (
  `wmi_code` varchar(3) NOT NULL,
  `is_low_volume` tinyint(1) NOT NULL DEFAULT 0,
  `wmi_extended` varchar(3) NOT NULL DEFAULT '000',
  `manufacturer_name` varchar(150) NOT NULL,
  `country_code` varchar(2) NOT NULL,
  `vehicle_type` varchar(50) NOT NULL DEFAULT 'Passenger Car',
  `brand_common_name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`wmi_code`,`wmi_extended`),
  KEY `wmi_registry_wmi_code_index` (`wmi_code`),
  KEY `wmi_registry_country_code_foreign` (`country_code`),
  CONSTRAINT `wmi_registry_country_code_foreign` FOREIGN KEY (`country_code`) REFERENCES `geo_countries` (`iso_alpha2`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `wmi_registry` (`wmi_code`, `is_low_volume`, `wmi_extended`, `manufacturer_name`, `country_code`, `vehicle_type`, `brand_common_name`) VALUES
('1FA', 0, '000', 'Ford Motor Company', 'US', 'Passenger Car', 'Ford'),
('1FB', 0, '000', 'Ford Motor Company', 'US', 'Commercial / Truck', 'Ford'),
('1FM', 0, '000', 'Ford Motor Company', 'US', 'Multipurpose Passenger Vehicle', 'Ford'),
('1FT', 0, '000', 'Ford Motor Company', 'US', 'Truck', 'Ford'),
('1G1', 0, '000', 'General Motors LLC', 'US', 'Passenger Car', 'Chevrolet'),
('1GC', 0, '000', 'General Motors LLC', 'US', 'Truck', 'Chevrolet'),
('1HG', 0, '000', 'American Honda Motor Co., Inc.', 'US', 'Passenger Car', 'Honda'),
('4T1', 0, '000', 'Toyota Motor Manufacturing, Kentucky, Inc.', 'US', 'Passenger Car', 'Toyota'),
('5TD', 0, '000', 'Toyota Motor Manufacturing, Indiana, Inc.', 'US', 'Multipurpose Passenger Vehicle', 'Toyota'),
('5YJ', 0, '000', 'Tesla, Inc.', 'US', 'Passenger Car', 'Tesla'),
('7SA', 0, '000', 'Tesla, Inc.', 'US', 'Multipurpose Passenger Vehicle', 'Tesla'),
('2T3', 0, '000', 'Toyota Motor Manufacturing Canada Inc.', 'CA', 'Multipurpose Passenger Vehicle', 'Toyota'),
('2HG', 0, '000', 'Honda of Canada Mfg.', 'CA', 'Passenger Car', 'Honda'),
('3VW', 0, '000', 'Volkswagen de Mexico', 'MX', 'Passenger Car', 'Volkswagen'),
('3TM', 0, '000', 'Toyota Motor Manufacturing de Baja California, S. de R.L. de C.V.', 'MX', 'Truck', 'Toyota'),
('JHM', 0, '000', 'Honda Motor Co., Ltd.', 'JP', 'Passenger Car', 'Honda'),
('JT3', 0, '000', 'Toyota Motor Corporation', 'JP', 'Multipurpose Passenger Vehicle', 'Toyota'),
('JTD', 0, '000', 'Toyota Motor Corporation', 'JP', 'Passenger Car', 'Toyota'),
('JTE', 0, '000', 'Toyota Motor Corporation', 'JP', 'Multipurpose Passenger Vehicle', 'Toyota'),
('JM1', 0, '000', 'Mazda Motor Corporation', 'JP', 'Passenger Car', 'Mazda'),
('JN1', 0, '000', 'Nissan Motor Co., Ltd.', 'JP', 'Passenger Car', 'Nissan'),
('WBA', 0, '000', 'Bayerische Motoren Werke AG (BMW)', 'DE', 'Passenger Car', 'BMW'),
('WBS', 0, '000', 'BMW M GmbH', 'DE', 'Passenger Car', 'BMW M'),
('WAU', 0, '000', 'Audi AG', 'DE', 'Passenger Car', 'Audi'),
('WDB', 0, '000', 'Mercedes-Benz AG', 'DE', 'Passenger Car', 'Mercedes-Benz'),
('WDD', 0, '000', 'Mercedes-Benz AG', 'DE', 'Passenger Car', 'Mercedes-Benz'),
('WVW', 0, '000', 'Volkswagen AG', 'DE', 'Passenger Car', 'Volkswagen'),
('WP0', 0, '000', 'Dr. Ing. h.c. F. Porsche AG', 'DE', 'Passenger Car', 'Porsche'),
('WP0', 1, 'XXX', 'Dr. Ing. h.c. F. Porsche AG (Special Series)', 'DE', 'Passenger Car', 'Porsche'),
('WP9', 1, '000', 'Dr. Ing. h.c. F. Porsche AG (Low Volume)', 'DE', 'Passenger Car', 'Porsche'),
('WP9', 1, 'XXX', 'Dr. Ing. h.c. F. Porsche AG (Low Volume Composite)', 'DE', 'Passenger Car', 'Porsche'),
('SB1', 0, '000', 'Toyota Motor Manufacturing (UK) Ltd.', 'GB', 'Passenger Car', 'Toyota'),
('SAL', 0, '000', 'Jaguar Land Rover Limited', 'GB', 'Multipurpose Passenger Vehicle', 'Land Rover'),
('SAJ', 0, '000', 'Jaguar Land Rover Limited', 'GB', 'Passenger Car', 'Jaguar'),
('MR0', 0, '000', 'Toyota Motor Thailand Co., Ltd.', 'TH', 'Multipurpose Passenger Vehicle', 'Toyota'),
('KM8', 0, '000', 'Hyundai Motor Company', 'KR', 'Multipurpose Passenger Vehicle', 'Hyundai'),
('KNA', 0, '000', 'Kia Corporation', 'KR', 'Passenger Car', 'Kia'),
('LFV', 0, '000', 'FAW-Volkswagen Automotive Co., Ltd.', 'CN', 'Passenger Car', 'Volkswagen'),
('LSV', 0, '000', 'SAIC Volkswagen Automotive Co., Ltd.', 'CN', 'Passenger Car', 'Volkswagen');

DROP TABLE IF EXISTS `assembly_plants`;
CREATE TABLE `assembly_plants` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `wmi_code` varchar(3) NOT NULL,
  `plant_code` varchar(1) NOT NULL,
  `plant_name` varchar(150) NOT NULL,
  `city` varchar(100) NOT NULL,
  `state_province` varchar(100) DEFAULT NULL,
  `country_code` varchar(2) NOT NULL,
  `active_from_year` int DEFAULT NULL,
  `active_to_year` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `assembly_plants_wmi_code_plant_code_unique` (`wmi_code`,`plant_code`),
  KEY `assembly_plants_country_code_foreign` (`country_code`),
  CONSTRAINT `assembly_plants_country_code_foreign` FOREIGN KEY (`country_code`) REFERENCES `geo_countries` (`iso_alpha2`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `assembly_plants` (`id`, `wmi_code`, `plant_code`, `plant_name`, `city`, `state_province`, `country_code`, `active_from_year`, `active_to_year`) VALUES
(1, '1FA', '5', 'Flat Rock Assembly Plant', 'Flat Rock', 'Michigan', 'US', 1987, NULL),
(2, '1FA', 'F', 'Dearborn Truck Plant', 'Dearborn', 'Michigan', 'US', 1925, NULL),
(3, '4T1', 'U', 'Toyota Motor Manufacturing Kentucky (TMMK)', 'Georgetown', 'Kentucky', 'US', 1988, NULL),
(4, '4T1', 'X', 'Toyota Motor Manufacturing Texas (TMMTX)', 'San Antonio', 'Texas', 'US', 2006, NULL),
(5, '5TD', 'S', 'Toyota Motor Manufacturing Indiana (TMMI)', 'Princeton', 'Indiana', 'US', 1998, NULL),
(6, '2T3', 'C', 'Toyota Motor Manufacturing Canada (TMMC)', 'Cambridge', 'Ontario', 'CA', 1988, NULL),
(7, '2T3', 'M', 'Toyota Motor Manufacturing Canada (TMMC Cambridge/Woodstock)', 'Cambridge', 'Ontario', 'CA', 1988, NULL),
(8, '3TM', 'M', 'Toyota Motor Manufacturing de Baja California (TMMBC)', 'Tijuana', 'Baja California', 'MX', 2004, NULL),
(9, '3VW', 'M', 'Volkswagen Puebla Plant', 'Puebla', 'Puebla', 'MX', 1964, NULL),
(10, '3VW', 'H', 'Volkswagen Puebla Plant (Line 2)', 'Puebla', 'Puebla', 'MX', 1964, NULL),
(11, '1HG', 'A', 'Marysville Auto Plant', 'Marysville', 'Ohio', 'US', 1982, NULL),
(12, 'JHM', 'C', 'Sayama Plant', 'Sayama', 'Saitama', 'JP', 1964, NULL),
(13, 'WBA', 'A', 'BMW Group Plant Munich', 'Munich', 'Bavaria', 'DE', 1922, NULL),
(14, 'WBA', 'D', 'BMW Group Plant Dingolfing', 'Dingolfing', 'Bavaria', 'DE', 1973, NULL),
(15, 'WBA', 'F', 'BMW Group Plant Munich (Line F)', 'Munich', 'Bavaria', 'DE', 1922, NULL),
(16, 'WP0', 'S', 'Porsche Stuttgart-Zuffenhausen Plant', 'Stuttgart', 'Baden-Württemberg', 'DE', 1938, NULL),
(17, 'WP9', 'S', 'Porsche Stuttgart-Zuffenhausen Plant', 'Stuttgart', 'Baden-Württemberg', 'DE', 1938, NULL);

DROP TABLE IF EXISTS `vin_cache`;
CREATE TABLE `vin_cache` (
  `vin_normalized` varchar(20) NOT NULL,
  `input_format` varchar(30) NOT NULL,
  `wmi_country_code` varchar(2) DEFAULT NULL,
  `actual_plant_country_code` varchar(2) DEFAULT NULL,
  `plant_city` varchar(100) DEFAULT NULL,
  `confidence_level` varchar(40) NOT NULL,
  `raw_api_payload` json DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `expires_at` timestamp NOT NULL,
  PRIMARY KEY (`vin_normalized`),
  KEY `vin_cache_expires_at_index` (`expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `api_keys`;
CREATE TABLE `api_keys` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `key_prefix` varchar(16) NOT NULL,
  `key_hash` varchar(64) NOT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `rate_limit_per_minute` int NOT NULL DEFAULT 60,
  `last_used_at` timestamp NULL DEFAULT NULL,
  `expires_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `api_keys_key_hash_unique` (`key_hash`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `api_keys` (`id`, `name`, `key_prefix`, `key_hash`, `is_active`, `rate_limit_per_minute`, `last_used_at`, `expires_at`, `created_at`, `updated_at`) VALUES
(1, 'Initial Production Project', 'uvi_live_pOF9eH', '81f3dd370eb0aa48b850c9617edb6ce65ac9c3343b51853111b81f58f68eed76', 1, 60, NULL, NULL, '2026-10-09 19:15:45', '2026-10-09 19:15:45');

SET FOREIGN_KEY_CHECKS = 1;