-- =============================================================
-- Phase 6 — Public website
--
-- Adds the columns and content tables the public site depends on:
--   vehicles.video_url        (featured walkaround video embed URL)
--   vehicles.document_status  (LTO / title status shown on the detail page)
--   posts        (blog)
--   testimonials (homepage social proof)
--   faqs         (homepage + help)
--
-- Run through install_phase6.php (idempotent) — do NOT apply raw via
-- phpMyAdmin here, the installer checks information_schema first.
-- =============================================================

-- Columns added conditionally by the installer:
--   ALTER TABLE vehicles ADD video_url        VARCHAR(255) NULL AFTER description;
--   ALTER TABLE vehicles ADD document_status  VARCHAR(80)  NOT NULL DEFAULT 'Clean Title' AFTER registration_year;

CREATE TABLE IF NOT EXISTS `posts` (
    `id`           INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `title`        VARCHAR(190) NOT NULL,
    `slug`         VARCHAR(200) NOT NULL,
    `excerpt`      VARCHAR(500) DEFAULT NULL,
    `body`         TEXT,
    `image`        VARCHAR(255) DEFAULT NULL,
    `author`       VARCHAR(100) DEFAULT 'Admin',
    `published_at` DATE DEFAULT NULL,
    `is_published` 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_posts_slug` (`slug`),
    KEY `idx_posts_published` (`is_published`, `published_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `testimonials` (
    `id`            INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `customer_name` VARCHAR(190) NOT NULL,
    `message`       TEXT,
    `rating`        TINYINT NOT NULL DEFAULT 5,
    `is_active`     TINYINT(1) NOT NULL DEFAULT 1,
    `created_at`    DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_testimonials_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `faqs` (
    `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `question`    VARCHAR(255) NOT NULL,
    `answer`      TEXT,
    `sort_order`  INT NOT NULL DEFAULT 0,
    `is_active`   TINYINT(1) NOT NULL DEFAULT 1,
    `created_at`  DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_faqs_sort` (`is_active`, `sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Sample content feeds the public site only — every vehicle shown on the
-- website still comes from the `vehicles` table (nothing hard-coded).
INSERT INTO `testimonials` (`customer_name`, `message`, `rating`) VALUES
('Maria Santos', 'Smooth, honest transaction. The unit was exactly as described and the paperwork was done in one day.', 5),
('Jose Ramirez', 'Great selection and very patient staff. I traded in my old sedan and drove out the same afternoon.', 5),
('Andrea Lim', 'The 150-point inspection report they showed me sealed the deal. Highly recommended for first-time buyers.', 5),
('Paolo Garcia', 'Transparent pricing, no hidden charges. Got a fair deal on financing through their partner bank.', 5),
('Kaye Domingo', 'Booked a test drive online, they had the car ready the next day. Fantastic customer service.', 5),
('Miguel Torres', 'Sold my SUV to them at a fair price. Payment was quick and the process was straightforward.', 5)
ON DUPLICATE KEY UPDATE `customer_name` = VALUES(`customer_name`);

INSERT INTO `faqs` (`question`, `answer`, `sort_order`) VALUES
('Are the vehicles inspected before sale?', 'Yes. Every unit passes our multi-point mechanical and electrical inspection before it is listed on the website, and the inspection report is available on each car detail page.', 1),
('Can I finance a used car?', 'Absolutely. We partner with several banks and financing companies. We can pre-qualify you based on your down payment and income, then run the numbers on any vehicle you like.', 2),
('Can I trade in my current car?', 'Yes. Bring your vehicle for a free appraisal and we will deduct the trade-in offer from the price of the unit you choose.', 3),
('What documents come with the car?', 'Each unit includes the Certificate of Registration (CR), Official Receipt (OR), deed of sale and the latest LTO documents. Document status is listed on every car detail page.', 4),
('Is there a warranty or return period?', 'We back every sale with transparent terms. Eligible units include a 7-day return window and we walk you through the details before you pay anything.', 5),
('How do I book a test drive?', 'Use the Test Drive form, choose the vehicle and schedule a slot, or message us on WhatsApp. We confirm every booking personally by phone.', 6),
('Do you buy cars that need repairs?', 'We buy vehicles in all conditions. Send the details using the Sell Your Car form and we will send a fair cash valuation within 24 hours.', 7),
('Where is the showroom located?', 'You will find our address, map and phone numbers on the Contact page and in the footer. Walk-ins are welcome during business hours.', 8)
ON DUPLICATE KEY UPDATE `question` = VALUES(`question`);

INSERT INTO `posts` (`title`, `slug`, `excerpt`, `body`, `author`, `published_at`) VALUES
('Buying a Used Car in 2026: The Complete Checklist', 'buying-a-used-car-checklist', 'From budget to test drive, everything you need to check before you sign for a pre-owned vehicle.', 'Bringing home a used car should be exciting, not stressful. Before you commit, walk through this simple checklist.\n\n1. Set a realistic budget. Remember that the price of the car is only part of the picture. Insurance, registration, fuel and the first service all add up.\n\n2. Read the inspection report. Every unit here comes with a multi-point inspection. Check the overall condition and the notes on the engine, transmission and body.\n\n3. Check the documents. Verify the Certificate of Registration and Official Receipt match the vehicle plate and engine number.\n\n4. Take a test drive. Drive on city roads and the highway. Listen for unusual noises and check how the brakes, steering and transmission feel.\n\n5. Confirm the terms. Ask for a written quotation that includes all fees, and compare your financing options.\n\nStill have questions? Message us on WhatsApp or book a test drive below.', 'Mary Ann Cruz', '2026-09-10'),
('How to Get the Best Trade-In Value for Your Car', 'best-trade-in-value', 'Small habits that keep your car\'s value — and what to prepare before you get it appraised.', 'A clean, well-documented car always commands a better offer. Here is how to stack the odds in your favour.\n\nKeep service records. A folder of receipts proves the car was maintained and builds trust with the buyer.\n\nFix the small things. A dented bumper or a cracked tail light is a quick repair that instantly improves perceived value.\n\nDetail the interior. A deep clean goes a long way. Vacuum, wipe every surface and clean the windows.\n\nPrepare the papers. CR, OR, deed of sale, and proof of the latest LTO renewals speed the appraisal up.\n\nBring fair expectations. Research similar listings so you have a realistic price in mind. We buy cars in all conditions and pay quickly.', 'Mary Ann Cruz', '2026-08-24'),
('Financing a Pre-Owned Car: Rates, Terms and Down Payments', 'financing-pre-owned-car', 'How used-car financing works, what terms are available, and how to estimate your monthly payment.', 'Most buyers do not pay for a used car in cash, and knowing how financing works helps you budget like a pro.\n\nHow it works. You pay a down payment, usually 20% of the price, and finance the balance over 12 to 48 months.\n\nInterest. Rates vary by bank and your credit profile. Compare the total cost — not just the monthly payment.\n\nPre-qualification. Getting pre-qualified is free and tells you exactly how much you can borrow before you start shopping.\n\nUse the calculator. On every car page you will find an estimate based on the selling price, down payment and term. Try different combinations to find the payment that fits your life.', 'Arvin Reyes', '2026-08-02'),
('Sedan, SUV or Pickup? Choosing the Right Body Type', 'choosing-right-body-type', 'A practical guide to matching the right body type to the way you actually use your car.', 'The best car for you depends on how you use it. Ask yourself three questions.\n\nDo I carry people or cargo? SUVs and MPVs swallow families and luggage. Pickups handle hauling with ease.\n\nWhere do I drive? City commuters love compact sedans and hatchbacks for parking and fuel economy. Adventurers should look at SUVs with higher ground clearance.\n\nWhat is my running cost? Smaller engines and lighter bodies are cheaper to fuel, insure and maintain.\n\nFilter our inventory by body type and compare models side by side to find your match.', 'Arvin Reyes', '2026-07-18')
ON DUPLICATE KEY UPDATE `title` = VALUES(`title`);

-- Sample walkthrough videos for a handful of units (data only — never
-- rendered unless the unit has one).
UPDATE `vehicles` SET `video_url` = 'https://www.youtube.com/watch?v=t4L6MOuK5Dc'
WHERE `id` = 2 AND (`video_url` IS NULL OR `video_url` = '');
UPDATE `vehicles` SET `video_url` = 'https://www.youtube.com/watch?v=rNH-QgBB-pY'
WHERE `id` = 5 AND (`video_url` IS NULL OR `video_url` = '');

-- Give every vehicle a real document status if it does not have one yet.
UPDATE `vehicles` SET `document_status` =
    IF(MOD(`id`, 7) = 0, 'Documents Pending',
       IF(MOD(`id`, 5) = 0, 'For Renewal',
          IF(MOD(`id`, 3) = 0, 'LTO Registered',
             'Clean Title')))
WHERE `document_status` IS NULL OR `document_status` = '';