MokoSuiteAuto/source/packages/plg_system_mokosuiteauto/sql/install.mysql.sql

--
-- MokoSuite Auto Tables
--

CREATE TABLE IF NOT EXISTS `#__mokosuiteauto_vehicles` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `vin` CHAR(17) NOT NULL,
  `stock_number` VARCHAR(20) NOT NULL DEFAULT '',
  `year` SMALLINT UNSIGNED NOT NULL,
  `make` VARCHAR(100) NOT NULL,
  `model` VARCHAR(100) NOT NULL,
  `trim_level` VARCHAR(100) NOT NULL DEFAULT '',
  `body_style` VARCHAR(50) NOT NULL DEFAULT '',
  `exterior_color` VARCHAR(50) NOT NULL DEFAULT '',
  `interior_color` VARCHAR(50) NOT NULL DEFAULT '',
  `engine` VARCHAR(100) NOT NULL DEFAULT '',
  `transmission` ENUM('automatic','manual','cvt') NOT NULL DEFAULT 'automatic',
  `drivetrain` ENUM('fwd','rwd','awd','4wd') NOT NULL DEFAULT 'fwd',
  `mileage` INT UNSIGNED NOT NULL DEFAULT 0,
  `condition_type` ENUM('new','used','certified') NOT NULL DEFAULT 'used',
  `status` ENUM('in_transit','on_lot','sold','wholesale','trade_pending') NOT NULL DEFAULT 'on_lot',
  `invoice_price` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `list_price` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `internet_price` DECIMAL(12,2) DEFAULT NULL,
  `reconditioning_cost` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `lot_date` DATE DEFAULT NULL,
  `sold_date` DATE DEFAULT NULL,
  `notes` TEXT,
  `created` DATETIME NOT NULL,
  `modified` DATETIME DEFAULT NULL,
  `created_by` INT NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_vin` (`vin`),
  KEY `idx_stock` (`stock_number`),
  KEY `idx_status` (`status`),
  KEY `idx_condition` (`condition_type`),
  KEY `idx_make_model` (`make`, `model`),
  KEY `idx_lot_date` (`lot_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `#__mokosuiteauto_deals` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `vehicle_id` INT UNSIGNED NOT NULL,
  `buyer_contact_id` INT NOT NULL,
  `salesperson_id` INT DEFAULT NULL,
  `fi_manager_id` INT DEFAULT NULL,
  `sale_price` DECIMAL(12,2) NOT NULL,
  `trade_allowance` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `trade_payoff` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `down_payment` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `financed_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `apr` DECIMAL(5,2) DEFAULT NULL,
  `term_months` INT UNSIGNED DEFAULT NULL,
  `monthly_payment` DECIMAL(10,2) DEFAULT NULL,
  `front_gross` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `back_gross` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `total_gross` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `status` ENUM('prospect','negotiating','pending_finance','approved','delivered','unwound') NOT NULL DEFAULT 'prospect',
  `deal_number` VARCHAR(20) NOT NULL DEFAULT '',
  `created` DATETIME NOT NULL,
  `modified` DATETIME DEFAULT NULL,
  `delivered_at` DATETIME DEFAULT NULL,
  `created_by` INT NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_vehicle` (`vehicle_id`),
  KEY `idx_buyer` (`buyer_contact_id`),
  KEY `idx_salesperson` (`salesperson_id`),
  KEY `idx_status` (`status`),
  KEY `idx_deal_number` (`deal_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `#__mokosuiteauto_fi_products` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `deal_id` INT UNSIGNED NOT NULL,
  `product_name` VARCHAR(255) NOT NULL,
  `cost` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `price` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `profit` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `term_months` INT UNSIGNED DEFAULT NULL,
  `created` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_deal` (`deal_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `#__mokosuiteauto_tradeins` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `deal_id` INT UNSIGNED DEFAULT NULL,
  `vin` CHAR(17) NOT NULL DEFAULT '',
  `year` SMALLINT UNSIGNED DEFAULT NULL,
  `make` VARCHAR(100) NOT NULL DEFAULT '',
  `model` VARCHAR(100) NOT NULL DEFAULT '',
  `mileage` INT UNSIGNED NOT NULL DEFAULT 0,
  `condition_grade` ENUM('excellent','good','fair','poor') NOT NULL DEFAULT 'good',
  `appraisal_value` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `payoff_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `equity` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `status` ENUM('pending_review','approved','applied','rejected') NOT NULL DEFAULT 'pending_review',
  `owner_contact_id` INT DEFAULT NULL,
  `appraised_by` INT DEFAULT NULL,
  `notes` TEXT,
  `created` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_deal` (`deal_id`),
  KEY `idx_vin` (`vin`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `#__mokosuiteauto_test_drives` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `vehicle_id` INT UNSIGNED NOT NULL,
  `contact_id` INT NOT NULL,
  `salesperson_id` INT DEFAULT NULL,
  `scheduled_at` DATETIME NOT NULL,
  `started_at` DATETIME DEFAULT NULL,
  `ended_at` DATETIME DEFAULT NULL,
  `mileage_out` INT UNSIGNED DEFAULT NULL,
  `mileage_in` INT UNSIGNED DEFAULT NULL,
  `status` ENUM('scheduled','in_progress','completed','cancelled','no_show') NOT NULL DEFAULT 'scheduled',
  `notes` TEXT,
  `created` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_vehicle` (`vehicle_id`),
  KEY `idx_contact` (`contact_id`),
  KEY `idx_date` (`scheduled_at`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `#__mokosuiteauto_service_orders` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `ro_number` VARCHAR(20) NOT NULL DEFAULT '',
  `vin` CHAR(17) NOT NULL DEFAULT '',
  `contact_id` INT DEFAULT NULL,
  `advisor_id` INT DEFAULT NULL,
  `technician_id` INT DEFAULT NULL,
  `type` ENUM('repair','maintenance','warranty','recall','inspection') NOT NULL DEFAULT 'repair',
  `concern` TEXT,
  `diagnosis` TEXT,
  `resolution` TEXT,
  `parts_total` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `labor_total` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `total` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `status` ENUM('open','in_progress','waiting_parts','completed','invoiced') NOT NULL DEFAULT 'open',
  `opened_at` DATETIME NOT NULL,
  `completed_at` DATETIME DEFAULT NULL,
  `created` DATETIME NOT NULL,
  `created_by` INT NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_ro` (`ro_number`),
  KEY `idx_vin` (`vin`),
  KEY `idx_contact` (`contact_id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;