-- ============================================================ -- 洗车管理系统 MySQL 一键初始化（v2.1） -- 适用 MySQL 8.0+ / utf8mb4 -- -- 用法： -- 1. 宝塔：选 carlog 库 →『导入』→ 选本文件 -- 2. 命令行：mysql -uroot -p carlog < carlog-init.sql -- -- 完全幂等，反复重跑不会破坏数据 -- ============================================================ DROP PROCEDURE IF EXISTS _add_index_if_missing; CREATE PROCEDURE _add_index_if_missing(IN p_table VARCHAR(64), IN p_index VARCHAR(64), IN p_def TEXT, IN p_unique INT) BEGIN IF NOT EXISTS (SELECT 1 FROM information_schema.statistics WHERE table_schema = DATABASE() AND table_name = p_table AND index_name = p_index) THEN IF p_unique = 1 THEN SET @s = CONCAT('CREATE UNIQUE INDEX ', p_index, ' ON ', p_table, ' ', p_def); ELSE SET @s = CONCAT('CREATE INDEX ', p_index, ' ON ', p_table, ' ', p_def); END IF; PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st; END IF; END; DROP PROCEDURE IF EXISTS _try_sql; CREATE PROCEDURE _try_sql(IN p_sql TEXT) BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN END; SET @s = p_sql; PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st; END; -- >>> 0001_init.sql -- ========================================================== -- ============================================================================= -- 洗车记录系统 - Migration 0001: 基础表 (MySQL 8.x) -- ============================================================================= -- ----------------------------------------------------------------------------- -- 1. chemicals - 药剂字典（Grocy 缓存层） -- ----------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS chemicals ( grocy_product_id VARCHAR(255) NOT NULL, name VARCHAR(255) NOT NULL, category VARCHAR(255) DEFAULT NULL, unit VARCHAR(50) NOT NULL DEFAULT 'ml', standard_dose DOUBLE DEFAULT NULL, notes TEXT DEFAULT NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, fetched_at DATETIME DEFAULT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (grocy_product_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CALL _add_index_if_missing('chemicals', 'idx_chemicals_category', '(category)', 0); CALL _add_index_if_missing('chemicals', 'idx_chemicals_active', '(is_active)', 0); CALL _add_index_if_missing('chemicals', 'idx_chemicals_fetched', '(fetched_at)', 0); -- ----------------------------------------------------------------------------- -- 2. weather_snapshots - 天气快照 -- ----------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS weather_snapshots ( id INT AUTO_INCREMENT PRIMARY KEY, snapshot_date VARCHAR(10) NOT NULL, city VARCHAR(100) NOT NULL, provider VARCHAR(50) NOT NULL, temp_c DOUBLE DEFAULT NULL, humidity INT DEFAULT NULL, weather_desc VARCHAR(255) DEFAULT NULL, weather_code VARCHAR(20) DEFAULT NULL, wind_kph DOUBLE DEFAULT NULL, precip_mm DOUBLE DEFAULT NULL, raw_json TEXT DEFAULT NULL, fetched_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CALL _add_index_if_missing('weather_snapshots', 'uk_weather_city_date', '(city, snapshot_date)', 1); CALL _add_index_if_missing('weather_snapshots', 'idx_weather_date', '(snapshot_date)', 0); -- ----------------------------------------------------------------------------- -- 3. wash_records - 洗车记录 -- ----------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS wash_records ( id INT AUTO_INCREMENT PRIMARY KEY, wash_date VARCHAR(10) NOT NULL, wash_type VARCHAR(20) NOT NULL, weather_snapshot_id INT DEFAULT NULL, location VARCHAR(255) DEFAULT NULL, cost DOUBLE NOT NULL DEFAULT 0, duration_min INT DEFAULT NULL, notes TEXT DEFAULT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT chk_wash_type CHECK (wash_type IN ('quick','full','detail','other')), CONSTRAINT fk_wash_weather FOREIGN KEY (weather_snapshot_id) REFERENCES weather_snapshots(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CALL _add_index_if_missing('wash_records', 'idx_wash_records_date', '(wash_date)', 0); CALL _add_index_if_missing('wash_records', 'idx_wash_records_type', '(wash_type)', 0); CALL _add_index_if_missing('wash_records', 'idx_wash_records_weather', '(weather_snapshot_id)', 0); -- ----------------------------------------------------------------------------- -- 4. chemical_usage - 药剂消耗 -- ----------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS chemical_usage ( id INT AUTO_INCREMENT PRIMARY KEY, usage_date VARCHAR(10) NOT NULL, chemical_id VARCHAR(255) NOT NULL, amount DOUBLE NOT NULL, wash_record_id INT DEFAULT NULL, notes TEXT DEFAULT NULL, sync_status VARCHAR(20) NOT NULL DEFAULT 'pending', sync_at DATETIME DEFAULT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT chk_sync_status CHECK (sync_status IN ('pending','synced','failed')), CONSTRAINT fk_usage_chemical FOREIGN KEY (chemical_id) REFERENCES chemicals(grocy_product_id) ON DELETE RESTRICT, CONSTRAINT fk_usage_wash FOREIGN KEY (wash_record_id) REFERENCES wash_records(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CALL _add_index_if_missing('chemical_usage', 'idx_usage_date', '(usage_date)', 0); CALL _add_index_if_missing('chemical_usage', 'idx_usage_chemical', '(chemical_id)', 0); CALL _add_index_if_missing('chemical_usage', 'idx_usage_wash', '(wash_record_id)', 0); CALL _add_index_if_missing('chemical_usage', 'idx_usage_sync', '(sync_status)', 0); -- ----------------------------------------------------------------------------- -- 5. settings - 运行时配置 KV -- ----------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS settings ( `key` VARCHAR(100) NOT NULL PRIMARY KEY, value TEXT DEFAULT NULL, is_secret TINYINT(1) NOT NULL DEFAULT 0, description TEXT DEFAULT NULL, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT IGNORE INTO settings (`key`, value, is_secret, description) VALUES ('app_city', NULL, 0, '所在城市（用于天气查询）'), ('app_timezone', 'Asia/Shanghai', 0, '时区'), ('grocy_url', NULL, 0, 'Grocy 实例 URL'), ('grocy_api_token', NULL, 1, 'Grocy REST API token'), ('backup_keep_count', '10', 0, '本地备份保留份数'), ('backup_dir', 'storage/backups', 0, '备份输出目录'); -- ----------------------------------------------------------------------------- -- 6. views -- ----------------------------------------------------------------------------- DROP VIEW IF EXISTS v_wash_monthly_count; CALL _try_sql('CREATE VIEW v_wash_monthly_count AS SELECT SUBSTRING(wash_date, 1, 7) AS month, COUNT(*) AS wash_count, SUM(COALESCE(cost, 0)) AS total_cost FROM wash_records GROUP BY SUBSTRING(wash_date, 1, 7) ORDER BY month DESC'); DROP VIEW IF EXISTS v_chemical_monthly_usage; CALL _try_sql('CREATE VIEW v_chemical_monthly_usage AS SELECT SUBSTRING(cu.usage_date, 1, 7) AS month, c.grocy_product_id AS grocy_product_id, c.name AS chemical_name, c.unit AS unit, SUM(cu.amount) AS total_amount, COUNT(*) AS usage_count FROM chemical_usage cu JOIN chemicals c ON c.grocy_product_id = cu.chemical_id GROUP BY SUBSTRING(cu.usage_date, 1, 7), c.grocy_product_id ORDER BY month DESC, total_amount DESC'); DROP VIEW IF EXISTS v_last_wash; CALL _try_sql('CREATE VIEW v_last_wash AS SELECT id AS wash_id, wash_date, wash_type, DATEDIFF(NOW(), STR_TO_DATE(wash_date, ''%Y-%m-%d'')) AS days_since FROM wash_records ORDER BY wash_date DESC, id DESC LIMIT 1'); -- >>> 0002_auth.sql -- ========================================================== -- 0002_auth.sql - 用户认证 + 防撞库 (MySQL) CREATE TABLE IF NOT EXISTS users ( id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) NOT NULL UNIQUE, password_hash VARCHAR(255) NOT NULL, role VARCHAR(20) NOT NULL DEFAULT 'user', is_active TINYINT(1) NOT NULL DEFAULT 1, last_login_at DATETIME DEFAULT NULL, last_login_ip VARCHAR(45) DEFAULT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT chk_role CHECK (role IN ('user','admin')) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CALL _add_index_if_missing('users', 'idx_users_active', '(is_active)', 0); CREATE TABLE IF NOT EXISTS login_attempts ( id INT AUTO_INCREMENT PRIMARY KEY, attempted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, ip_address VARCHAR(45) NOT NULL, username VARCHAR(50) NOT NULL, success TINYINT(1) NOT NULL, user_agent VARCHAR(500) DEFAULT NULL, failure_reason VARCHAR(100) DEFAULT NULL, CONSTRAINT chk_success CHECK (success IN (0, 1)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CALL _add_index_if_missing('login_attempts', 'idx_attempts_ip_time', '(ip_address, attempted_at)', 0); CALL _add_index_if_missing('login_attempts', 'idx_attempts_user_time', '(username, attempted_at)', 0); CALL _add_index_if_missing('login_attempts', 'idx_attempts_time', '(attempted_at)', 0); CREATE TABLE IF NOT EXISTS auth_locks ( lock_key VARCHAR(100) PRIMARY KEY, lock_type VARCHAR(10) NOT NULL, target VARCHAR(50) NOT NULL, locked_until DATETIME NOT NULL, reason VARCHAR(255) DEFAULT NULL, attempts INT NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT chk_lock_type CHECK (lock_type IN ('ip','user')) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CALL _add_index_if_missing('auth_locks', 'idx_locks_until', '(locked_until)', 0); INSERT IGNORE INTO settings (`key`, value, is_secret, description) VALUES ('session_lifetime_days', '30', 0, '登录 session 有效期（天）'), ('session_cookie_secure', 'auto', 0, 'Cookie secure 标志：true/false/auto'), ('login_max_failures_ip', '5', 0, '每 IP 允许的最大连续失败次数'), ('login_max_failures_user', '5', 0, '每用户名允许的最大连续失败次数'), ('login_lock_minutes_ip', '15', 0, 'IP 级别锁定时长（分钟）'), ('login_lock_minutes_user', '30', 0, '用户名级别锁定时长（分钟）'), ('login_global_max_failures', '10', 0, '触发全局 IP 封锁的失败次数'), ('login_global_lock_hours', '1', 0, '全局 IP 封锁时长（小时）'), ('login_attempts_retention_days', '30', 0, 'login_attempts 保留天数'), ('csrf_token_lifetime_hours', '12', 0, 'CSRF token 有效期（小时）'), ('bcrypt_cost', '12', 0, 'bcrypt cost factor'); -- >>> 0003_vehicles.sql -- ========================================================== -- 0003_vehicles.sql - 车辆管理 (MySQL) CREATE TABLE IF NOT EXISTS vehicles ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, plate VARCHAR(20) DEFAULT NULL, type VARCHAR(20) NOT NULL DEFAULT 'car', color VARCHAR(30) DEFAULT NULL, notes TEXT DEFAULT NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, sort_order INT NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT chk_vehicle_type CHECK (type IN ('car','suv','mpv','truck','other')) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CALL _add_index_if_missing('vehicles', 'idx_vehicles_active', '(is_active)', 0); CALL _add_index_if_missing('vehicles', 'idx_vehicles_sort', '(sort_order)', 0); CALL _try_sql('ALTER TABLE wash_records ADD COLUMN vehicle_id INT DEFAULT NULL'); CALL _add_index_if_missing('wash_records', 'idx_wash_records_vehicle', '(vehicle_id)', 0); DROP VIEW IF EXISTS v_last_wash; CALL _try_sql('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, DATEDIFF(NOW(), STR_TO_DATE(w.wash_date, ''%Y-%m-%d'')) 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'); -- >>> 0004_grocy_full.sql -- ========================================================== -- 0004_grocy_full.sql - Grocy 主数据同步字段 (MySQL) CALL _try_sql('ALTER TABLE chemicals ADD COLUMN description TEXT DEFAULT NULL, ADD COLUMN current_amount DOUBLE NOT NULL DEFAULT 0, ADD COLUMN current_value DOUBLE NOT NULL DEFAULT 0, ADD COLUMN min_stock_amount DOUBLE NOT NULL DEFAULT 0, ADD COLUMN best_before_date VARCHAR(20) DEFAULT NULL, ADD COLUMN location VARCHAR(255) DEFAULT NULL, ADD COLUMN product_group_id INT DEFAULT NULL, ADD COLUMN qu_id INT DEFAULT NULL, ADD COLUMN location_id INT DEFAULT NULL, ADD COLUMN picture_file_name VARCHAR(255) DEFAULT NULL, ADD COLUMN last_synced_at DATETIME DEFAULT NULL'); CALL _add_index_if_missing('chemicals', 'idx_chem_amount', '(current_amount)', 0); CALL _add_index_if_missing('chemicals', 'idx_chem_pg', '(product_group_id)', 0); CALL _add_index_if_missing('chemicals', 'idx_chem_synced', '(last_synced_at)', 0); INSERT IGNORE INTO settings (`key`, value, is_secret, description) VALUES ('grocy_sync_batch', '50', 0, 'Grocy 扣减同步每批条数'), ('grocy_low_stock_pct', '20', 0, '低库存阈值（百分比）'), ('grocy_pull_auto', '0', 0, 'Grocy 拉取模式：0=手动，1=启动时自动拉'); -- >>> 0005_inventory_detail.sql -- ========================================================== -- 0005_inventory_detail.sql (MySQL) CALL _try_sql('ALTER TABLE chemicals ADD COLUMN source VARCHAR(20) NOT NULL DEFAULT ''manual'', ADD COLUMN grocy_last_pulled_at DATETIME DEFAULT NULL'); CREATE TABLE IF NOT EXISTS category_mappings ( grocy_group_id INT PRIMARY KEY, display_name VARCHAR(100) NOT NULL, sort_order INT NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS chemical_inventory_log ( id INT AUTO_INCREMENT PRIMARY KEY, chemical_id VARCHAR(255) NOT NULL, change_type VARCHAR(20) NOT NULL, amount_delta DOUBLE NOT NULL, amount_after DOUBLE DEFAULT NULL, source VARCHAR(20) NOT NULL DEFAULT 'local', source_ref VARCHAR(255) DEFAULT NULL, note TEXT DEFAULT NULL, occurred_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT chk_change_type CHECK (change_type IN ('purchase','consume','inventory','transfer','adjust')) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CALL _add_index_if_missing('chemical_inventory_log', 'idx_invlog_chem', '(chemical_id, occurred_at DESC)', 0); CALL _add_index_if_missing('chemical_inventory_log', 'idx_invlog_type', '(change_type)', 0); INSERT IGNORE INTO settings (`key`, value, is_secret, description) VALUES ('grocy_categories_json', '[]', 0, 'Grocy 分类映射 JSON'); -- >>> 0006_unit_conversion.sql -- ========================================================== -- 0006_unit_conversion.sql (MySQL) CALL _try_sql('ALTER TABLE chemicals ADD COLUMN qu_factor DOUBLE NOT NULL DEFAULT 1.0, ADD COLUMN consume_unit_id INT DEFAULT NULL, ADD COLUMN consume_unit_name VARCHAR(100) DEFAULT NULL'); CALL _try_sql('ALTER TABLE chemical_usage ADD COLUMN unit VARCHAR(50) DEFAULT NULL, ADD COLUMN stock_amount DOUBLE DEFAULT NULL, ADD COLUMN consume_unit_id INT DEFAULT NULL'); -- >>> 0007_vehicle_logs.sql -- ========================================================== -- 0007_vehicle_logs.sql (MySQL) CREATE TABLE IF NOT EXISTS maintenance_records ( id INT AUTO_INCREMENT PRIMARY KEY, vehicle_id INT NOT NULL, maint_date VARCHAR(10) NOT NULL, odometer_km INT DEFAULT NULL, total_cost DOUBLE NOT NULL DEFAULT 0, shop VARCHAR(255) DEFAULT NULL, items_json JSON NOT NULL DEFAULT ('[]'), next_due_date VARCHAR(10) DEFAULT NULL, next_due_km INT DEFAULT NULL, notes TEXT DEFAULT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CALL _add_index_if_missing('maintenance_records', 'idx_maint_vehicle_date', '(vehicle_id, maint_date DESC)', 0); CALL _add_index_if_missing('maintenance_records', 'idx_maint_date', '(maint_date DESC)', 0); CREATE TABLE IF NOT EXISTS refuel_records ( id INT AUTO_INCREMENT PRIMARY KEY, vehicle_id INT NOT NULL, refuel_date VARCHAR(10) NOT NULL, odometer_km INT DEFAULT NULL, liters DOUBLE NOT NULL, price_per_liter DOUBLE DEFAULT NULL, total_cost DOUBLE NOT NULL, fuel_type VARCHAR(20) DEFAULT NULL, is_full TINYINT(1) NOT NULL DEFAULT 0, station VARCHAR(255) DEFAULT NULL, notes TEXT DEFAULT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CALL _add_index_if_missing('refuel_records', 'idx_refuel_vehicle_date', '(vehicle_id, refuel_date DESC)', 0); CALL _add_index_if_missing('refuel_records', 'idx_refuel_date', '(refuel_date DESC)', 0); CREATE TABLE IF NOT EXISTS charging_records ( id INT AUTO_INCREMENT PRIMARY KEY, vehicle_id INT NOT NULL, charge_date VARCHAR(10) NOT NULL, odometer_km INT DEFAULT NULL, kwh DOUBLE NOT NULL, price_per_kwh DOUBLE DEFAULT NULL, total_cost DOUBLE NOT NULL, charge_type VARCHAR(20) DEFAULT NULL, start_soc INT DEFAULT NULL, end_soc INT DEFAULT NULL, station VARCHAR(255) DEFAULT NULL, notes TEXT DEFAULT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CALL _add_index_if_missing('charging_records', 'idx_charging_vehicle_date', '(vehicle_id, charge_date DESC)', 0); CALL _add_index_if_missing('charging_records', 'idx_charging_date', '(charge_date DESC)', 0); DROP VIEW IF EXISTS v_recent_logs; CALL _try_sql('CREATE VIEW v_recent_logs AS SELECT ''maintenance'' AS log_type, id, vehicle_id, maint_date AS log_date, total_cost, odometer_km, shop AS location FROM maintenance_records UNION ALL SELECT ''refuel'' AS log_type, id, vehicle_id, refuel_date AS log_date, total_cost, odometer_km, station AS location FROM refuel_records UNION ALL SELECT ''charging'' AS log_type, id, vehicle_id, charge_date AS log_date, total_cost, odometer_km, station AS location FROM charging_records'); -- >>> 0008_mileage_and_insurance.sql -- ========================================================== -- 0008_mileage_and_insurance.sql (MySQL) CALL _try_sql('ALTER TABLE maintenance_records ADD COLUMN ev_km INT DEFAULT NULL, ADD COLUMN hev_km INT DEFAULT NULL'); CREATE TABLE IF NOT EXISTS insurance_records ( id INT AUTO_INCREMENT PRIMARY KEY, vehicle_id INT NOT NULL, insurance_type VARCHAR(50) NOT NULL, company VARCHAR(100) DEFAULT NULL, policy_no VARCHAR(100) DEFAULT NULL, start_date VARCHAR(10) NOT NULL, end_date VARCHAR(10) NOT NULL, premium DOUBLE DEFAULT NULL, coverage_amount DOUBLE DEFAULT NULL, notes TEXT DEFAULT NULL, attachment_path VARCHAR(500) DEFAULT NULL, attachment_name VARCHAR(255) DEFAULT NULL, attachment_mime VARCHAR(100) DEFAULT NULL, attachment_size INT DEFAULT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CALL _add_index_if_missing('insurance_records', 'idx_insurance_vehicle', '(vehicle_id)', 0); CALL _add_index_if_missing('insurance_records', 'idx_insurance_end_date', '(end_date)', 0); -- >>> 0009_vehicle_powertrain.sql -- ========================================================== -- 0009_vehicle_powertrain.sql (MySQL) CALL _try_sql('ALTER TABLE vehicles ADD COLUMN powertrain VARCHAR(10) NOT NULL DEFAULT ''ice'''); -- >>> 0010_operation_logs.sql -- ========================================================== -- 0010_operation_logs.sql (MySQL) CREATE TABLE IF NOT EXISTS operation_logs ( id INT AUTO_INCREMENT PRIMARY KEY, user_id INT DEFAULT NULL, username VARCHAR(50) DEFAULT NULL, action VARCHAR(50) NOT NULL, target_type VARCHAR(50) NOT NULL, target_ids TEXT NOT NULL, target_summary TEXT DEFAULT NULL, detail_json TEXT DEFAULT NULL, ip VARCHAR(45) DEFAULT NULL, user_agent VARCHAR(500) DEFAULT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CALL _add_index_if_missing('operation_logs', 'idx_oplog_created', '(created_at DESC)', 0); CALL _add_index_if_missing('operation_logs', 'idx_oplog_user_time', '(username, created_at DESC)', 0); CALL _add_index_if_missing('operation_logs', 'idx_oplog_action', '(action, target_type, created_at DESC)', 0); -- >>> 0011_soft_delete.sql -- ========================================================== -- 0011_soft_delete.sql (MySQL) CALL _try_sql('ALTER TABLE vehicles ADD COLUMN is_deleted TINYINT(1) NOT NULL DEFAULT 0'); CALL _try_sql('ALTER TABLE wash_records ADD COLUMN is_deleted TINYINT(1) NOT NULL DEFAULT 0'); CALL _try_sql('ALTER TABLE chemical_usage ADD COLUMN is_deleted TINYINT(1) NOT NULL DEFAULT 0'); CALL _try_sql('ALTER TABLE maintenance_records ADD COLUMN is_deleted TINYINT(1) NOT NULL DEFAULT 0'); CALL _try_sql('ALTER TABLE refuel_records ADD COLUMN is_deleted TINYINT(1) NOT NULL DEFAULT 0'); CALL _try_sql('ALTER TABLE charging_records ADD COLUMN is_deleted TINYINT(1) NOT NULL DEFAULT 0'); CALL _try_sql('ALTER TABLE insurance_records ADD COLUMN is_deleted TINYINT(1) NOT NULL DEFAULT 0'); CALL _add_index_if_missing('vehicles', 'ix_vehicles_is_deleted', '(is_deleted)', 0); CALL _add_index_if_missing('wash_records', 'ix_wash_records_is_deleted', '(is_deleted)', 0); CALL _add_index_if_missing('maintenance_records', 'ix_maintenance_is_deleted', '(is_deleted)', 0); CALL _add_index_if_missing('refuel_records', 'ix_refuel_is_deleted', '(is_deleted)', 0); CALL _add_index_if_missing('charging_records', 'ix_charging_is_deleted', '(is_deleted)', 0); CALL _add_index_if_missing('insurance_records', 'ix_insurance_is_deleted', '(is_deleted)', 0); -- >>> 0012_operation_logs_recovery.sql -- ========================================================== -- 0012_operation_logs_recovery.sql (MySQL) CALL _try_sql('ALTER TABLE operation_logs ADD COLUMN recovered_at DATETIME DEFAULT NULL'); -- >>> 0013_weather_wttr.sql -- ========================================================== -- 0013_weather_wttr.sql (MySQL) -- 删除旧 CHECK 并重建（MySQL 允许 ALTER TABLE 改 CHECK，但为保险用 ALTER COLUMN） CALL _try_sql('ALTER TABLE weather_snapshots MODIFY COLUMN provider VARCHAR(50) NOT NULL'); -- >>> 0014_grocy_auth.sql -- ========================================================== -- 0014_grocy_auth.sql (MySQL) INSERT IGNORE INTO settings (`key`, value, is_secret, description) VALUES ('grocy_username', '', 1, 'Grocy 用户名（session cookie 鉴权）'), ('grocy_password', '', 1, 'Grocy 密码（session cookie 鉴权）'), ('app_city_default', '', 0, '天气默认城市（永久生效）'); CREATE TABLE IF NOT EXISTS grocy_sync_logs ( id INT AUTO_INCREMENT PRIMARY KEY, action VARCHAR(50) NOT NULL, status VARCHAR(20) NOT NULL, ok_count INT NOT NULL DEFAULT 0, fail_count INT NOT NULL DEFAULT 0, detail TEXT DEFAULT NULL, started_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, finished_at DATETIME DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CALL _add_index_if_missing('grocy_sync_logs', 'idx_grocy_sync_logs_action', '(action)', 0); CALL _add_index_if_missing('grocy_sync_logs', 'idx_grocy_sync_logs_started', '(started_at DESC)', 0); -- >>> 0015_wash_photos.sql -- ========================================================== -- 0015_wash_photos.sql (MySQL) — 洗车对比照（before / after / detail） CREATE TABLE IF NOT EXISTS wash_photos ( id INT AUTO_INCREMENT PRIMARY KEY, wash_id INT NOT NULL, photo_type VARCHAR(20) NOT NULL DEFAULT 'detail', -- before / after / detail / scene file_path VARCHAR(500) NOT NULL, file_name VARCHAR(255) NOT NULL, mime_type VARCHAR(50) DEFAULT NULL, file_size INT DEFAULT NULL, width INT DEFAULT NULL, height INT DEFAULT NULL, caption VARCHAR(255) DEFAULT NULL, sort_order INT NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, is_deleted TINYINT(1) NOT NULL DEFAULT 0, INDEX idx_wash_photos_wash (wash_id, is_deleted), INDEX idx_wash_photos_type (photo_type), INDEX idx_wash_photos_created (created_at DESC) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; DROP PROCEDURE IF EXISTS _add_index_if_missing; DROP PROCEDURE IF EXISTS _try_sql; CREATE TABLE IF NOT EXISTS schema_migrations (filename VARCHAR(255) PRIMARY KEY, applied_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT IGNORE INTO schema_migrations (filename) VALUES ('0001_init.sql'); INSERT IGNORE INTO schema_migrations (filename) VALUES ('0002_auth.sql'); INSERT IGNORE INTO schema_migrations (filename) VALUES ('0003_vehicles.sql'); INSERT IGNORE INTO schema_migrations (filename) VALUES ('0004_grocy_full.sql'); INSERT IGNORE INTO schema_migrations (filename) VALUES ('0005_inventory_detail.sql'); INSERT IGNORE INTO schema_migrations (filename) VALUES ('0006_unit_conversion.sql'); INSERT IGNORE INTO schema_migrations (filename) VALUES ('0007_vehicle_logs.sql'); INSERT IGNORE INTO schema_migrations (filename) VALUES ('0008_mileage_and_insurance.sql'); INSERT IGNORE INTO schema_migrations (filename) VALUES ('0009_vehicle_powertrain.sql'); INSERT IGNORE INTO schema_migrations (filename) VALUES ('0010_operation_logs.sql'); INSERT IGNORE INTO schema_migrations (filename) VALUES ('0011_soft_delete.sql'); INSERT IGNORE INTO schema_migrations (filename) VALUES ('0012_operation_logs_recovery.sql'); INSERT IGNORE INTO schema_migrations (filename) VALUES ('0013_weather_wttr.sql'); INSERT IGNORE INTO schema_migrations (filename) VALUES ('0014_grocy_auth.sql'); INSERT IGNORE INTO schema_migrations (filename) VALUES ('0015_wash_photos.sql');