-- 创建数据库
CREATE DATABASE IF NOT EXISTS car_service_db DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE car_service_db;

-- 客户表会由 JPA 自动创建,但如果需要手动创建,可以使用以下SQL:
-- CREATE TABLE customers (
-- license_plate VARCHAR(20) PRIMARY KEY,
-- owner_name VARCHAR(50) NOT NULL,
-- phone VARCHAR(20) NOT NULL,
-- birthday DATE,
-- total_points INT NOT NULL DEFAULT 0,
-- created_at DATETIME NOT NULL,
-- updated_at DATETIME
-- );

-- CREATE TABLE maintenance_records (
-- id BIGINT AUTO_INCREMENT PRIMARY KEY,
-- license_plate VARCHAR(20) NOT NULL,
-- service_date DATETIME NOT NULL,
-- amount DECIMAL(10,2) NOT NULL,
-- points_earned INT NOT NULL,
-- engine_oil_type VARCHAR(50),
-- transmission_oil_type VARCHAR(50),
-- coolant VARCHAR(50),
-- brake_fluid VARCHAR(50),
-- power_steering_fluid VARCHAR(50),
-- battery_info VARCHAR(100),
-- notes VARCHAR(500),
-- created_at DATETIME NOT NULL
-- );

-- CREATE TABLE gift_redemptions (
-- id BIGINT AUTO_INCREMENT PRIMARY KEY,
-- license_plate VARCHAR(20) NOT NULL,
-- gift_name VARCHAR(100) NOT NULL,
-- points_used INT NOT NULL,
-- redemption_date DATETIME NOT NULL,
-- notes VARCHAR(200)
-- );