-- ============================================================= -- Phase 8 — Reports, Dashboard Analytics, Staff & Website CMS -- -- Tables added: -- website_settings (key/value application settings) -- banners (homepage hero slider) -- pages (CMS content pages served at /page/{slug}) -- contact_messages (messages stored from the contact form) -- -- Also: -- users.role ENUM expanded to the Phase 8 staff roles. -- -- Posts (blogs), testimonials and faqs already exist (Phase 6) and -- receive their admin CRUD in this phase — no new tables needed. -- ============================================================= -- ------------------------------------------------------------- -- Expand the users role ENUM for the Phase 8 staff module -- ------------------------------------------------------------- ALTER TABLE `users` MODIFY `role` ENUM( 'super-admin', 'manager', 'sales-executive', 'inventory-manager', 'accountant', 'admin', 'staff', 'customer' ) NOT NULL DEFAULT 'customer'; -- ------------------------------------------------------------- -- website_settings -- ------------------------------------------------------------- CREATE TABLE IF NOT EXISTS `website_settings` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `setting_key` VARCHAR(100) NOT NULL, `setting_value` TEXT, `label` VARCHAR(190) DEFAULT NULL, `setting_group` VARCHAR(50) NOT NULL DEFAULT 'general', `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP, `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `uq_website_settings_key` (`setting_key`), KEY `idx_website_settings_group` (`setting_group`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ------------------------------------------------------------- -- banners -- ------------------------------------------------------------- CREATE TABLE IF NOT EXISTS `banners` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `title` VARCHAR(190) NOT NULL DEFAULT '', `subtitle` VARCHAR(500) DEFAULT NULL, `image` VARCHAR(255) DEFAULT NULL, `link` VARCHAR(255) DEFAULT NULL, `button_label` VARCHAR(80) DEFAULT NULL, `sort_order` INT NOT NULL DEFAULT 0, `is_active` TINYINT(1) NOT NULL DEFAULT 1, `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP, `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `idx_banners_active` (`is_active`, `sort_order`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ------------------------------------------------------------- -- pages -- ------------------------------------------------------------- CREATE TABLE IF NOT EXISTS `pages` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `title` VARCHAR(190) NOT NULL, `slug` VARCHAR(200) NOT NULL, `content` LONGTEXT, `meta_title` VARCHAR(190) DEFAULT NULL, `meta_description` VARCHAR(255) DEFAULT NULL, `is_active` TINYINT(1) NOT NULL DEFAULT 1, `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP, `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `uq_pages_slug` (`slug`), KEY `idx_pages_active` (`is_active`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ------------------------------------------------------------- -- contact_messages -- ------------------------------------------------------------- CREATE TABLE IF NOT EXISTS `contact_messages` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `name` VARCHAR(190) NOT NULL, `email` VARCHAR(190) DEFAULT NULL, `mobile` VARCHAR(20) DEFAULT NULL, `subject` VARCHAR(200) DEFAULT NULL, `message` TEXT, `is_read` TINYINT(1) NOT NULL DEFAULT 0, `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `idx_contact_messages_read` (`is_read`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ------------------------------------------------------------- -- Default settings seed (company profile mirrors config/app.php so -- a fresh import already has a populated settings screen) -- ------------------------------------------------------------- INSERT INTO `website_settings` (`setting_key`, `setting_value`, `label`, `setting_group`) VALUES ('company_name', 'Used Car Dealer Pro', 'Company / Website name', 'general'), ('tagline', 'Quality used cars, great prices, trusted service.', 'Tagline', 'general'), ('logo', '', 'Logo (image)', 'general'), ('favicon', '', 'Favicon (image)', 'general'), ('gstin', '', 'GSTIN / VAT number', 'general'), ('footer_text', 'Quality inspected used cars at honest prices. Visit our showroom or call us today.', 'Footer blurb', 'general'), ('about_since', '2010', 'About page — since year', 'general'), ('about_customers', '5,000+', 'About page — happy customers', 'general'), ('about_cars_sold', '850+', 'About page — cars sold', 'general'), ('phone', '(02) 8123 4567', 'Landline', 'contact'), ('mobile', '0917 123 4567', 'Mobile', 'contact'), ('whatsapp', '09171234567', 'WhatsApp number', 'contact'), ('email', 'info@usedcardealerpro.test','Email', 'contact'), ('address', '123 Sucat Road, Parañaque City, Metro Manila', 'Address', 'contact'), ('city', 'Parañaque City', 'City', 'contact'), ('state', 'Metro Manila', 'State / Province', 'contact'), ('pincode', '', 'Pin / Zip code', 'contact'), ('hours', 'Mon - Sat, 9:00 AM - 7:00 PM', 'Business hours', 'contact'), ('map_query', 'Sucat Road, Parañaque City, Metro Manila', 'Google Maps location query', 'contact'), ('facebook', 'https://facebook.com/', 'Facebook URL', 'social'), ('instagram', 'https://instagram.com/', 'Instagram URL', 'social'), ('youtube', 'https://youtube.com/', 'YouTube URL', 'social'), ('meta_title', 'Used Car Dealer Pro — Quality Pre-Owned Cars', 'Meta title', 'seo'), ('meta_description','Quality inspected used cars at honest prices. Browse our inventory, book a test drive or sell your car today.', 'Meta description', 'seo'), ('meta_keywords', 'used cars, pre-owned cars, buy used car, sell car, car dealership', 'Meta keywords', 'seo'), ('og_image', '', 'Social share image', 'seo') ON DUPLICATE KEY UPDATE `setting_value` = VALUES(`setting_value`); -- ------------------------------------------------------------- -- Sample banners (image left empty → the carousel uses the CSS -- gradient hero). Edit these in the admin after banner uploads. -- Guarded with NOT EXISTS so re-running never duplicates banners. -- ------------------------------------------------------------- INSERT INTO `banners` (`title`, `subtitle`, `link`, `button_label`, `sort_order`, `is_active`) SELECT * FROM (SELECT 'Find Your Next Used Car' AS title, 'Every unit passes our 150-point inspection and comes with full paperwork.' AS subtitle, '/cars' AS link, 'Browse Inventory' AS button_label, 1 AS sort_order, 1 AS is_active) AS s WHERE NOT EXISTS (SELECT 1 FROM `banners` WHERE `title` = 'Find Your Next Used Car'); INSERT INTO `banners` (`title`, `subtitle`, `link`, `button_label`, `sort_order`, `is_active`) SELECT * FROM (SELECT 'Sell Your Car In 24 Hours' AS title, 'Fair cash valuation, no hidden fees.' AS subtitle, '/sell-your-car' AS link, 'Get An Offer' AS button_label, 2 AS sort_order, 1 AS is_active) AS s WHERE NOT EXISTS (SELECT 1 FROM `banners` WHERE `title` = 'Sell Your Car In 24 Hours'); INSERT INTO `banners` (`title`, `subtitle`, `link`, `button_label`, `sort_order`, `is_active`) SELECT * FROM (SELECT 'Flexible Financing' AS title, 'Low monthly payments with partner banks and financing companies.' AS subtitle, '/finance' AS link, 'Check Rates' AS button_label, 3 AS sort_order, 1 AS is_active) AS s WHERE NOT EXISTS (SELECT 1 FROM `banners` WHERE `title` = 'Flexible Financing'); -- ------------------------------------------------------------- -- Sample CMS page (more can be added in the admin) -- ------------------------------------------------------------- INSERT INTO `pages` (`title`, `slug`, `content`, `meta_title`, `is_active`) VALUES ('Terms of Service', 'terms-of-service', '

1. Vehicle listings

All vehicles listed on this website are subject to availability. Prices may change without prior notice. Photographs are illustrative and the actual unit may vary in detail.

2. Reservations & bookings

A booking holds a vehicle for an agreed period. The booking amount is applied towards the purchase price and is refundable according to the terms agreed at the time of booking.

3. Financing

Financing is arranged through third-party banks and financing companies. Approval and interest rates depend on the lender and your credit profile.

4. Warranty & returns

Eligible units include a 7-day return or exchange window. Always review the inspection report and the sales agreement before completing a purchase.

5. Contact

Questions about this policy? Reach us through the contact page and we will gladly assist.

', 'Terms of Service', 1) ON DUPLICATE KEY UPDATE `title` = VALUES(`title`);