-- =============================================================================
-- 洗车记录系统 - Migration 0003: 车辆管理
-- =============================================================================

-- -----------------------------------------------------------------------------
-- 1. vehicles - 车辆字典
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS vehicles (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    name        TEXT    NOT NULL,
    plate       TEXT,
    type        TEXT    NOT NULL DEFAULT 'car' CHECK (type IN ('car','suv','mpv','truck','other')),
    color       TEXT,
    notes       TEXT,
    is_active   INTEGER NOT NULL DEFAULT 1 CHECK (is_active IN (0, 1)),
    sort_order  INTEGER NOT NULL DEFAULT 0,
    created_at  TEXT    NOT NULL DEFAULT (datetime('now')),
    updated_at  TEXT    NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX IF NOT EXISTS idx_vehicles_active ON vehicles(is_active);
CREATE INDEX IF NOT EXISTS idx_vehicles_sort   ON vehicles(sort_order);

-- -----------------------------------------------------------------------------
-- 2. wash_records 加 vehicle_id
-- -----------------------------------------------------------------------------
ALTER TABLE wash_records ADD COLUMN vehicle_id INTEGER REFERENCES vehicles(id) ON DELETE SET NULL;
CREATE INDEX IF NOT EXISTS idx_wash_records_vehicle ON wash_records(vehicle_id);

-- -----------------------------------------------------------------------------
-- 3. 视图：v_last_wash 加 vehicle_name
-- -----------------------------------------------------------------------------
DROP VIEW IF EXISTS v_last_wash;
CREATE VIEW v_last_wash AS
SELECT
    w.id              AS wash_id,
    w.wash_date,
    w.wash_type,
    w.vehicle_id,
    v.name            AS vehicle_name,
    CAST(julianday('now') - julianday(w.wash_date) AS INTEGER) AS days_since
FROM wash_records w
LEFT JOIN vehicles v ON v.id = w.vehicle_id
ORDER BY w.wash_date DESC, w.id DESC
LIMIT 1;