CREATE TABLE IF NOT EXISTS vehicle_open_app (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(96) NOT NULL,
    app_key_hash BINARY(32) NOT NULL,
    app_key_prefix CHAR(8) NOT NULL,
    status VARCHAR(16) NOT NULL DEFAULT 'enabled',
    valid_from DATETIME(3) NOT NULL,
    valid_to DATETIME(3) NULL,
    created_by VARCHAR(96) NOT NULL,
    updated_by VARCHAR(96) NOT NULL,
    created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
    UNIQUE KEY uk_vehicle_open_app_key_hash (app_key_hash),
    INDEX idx_vehicle_open_app_validity (status, valid_from, valid_to),
    CONSTRAINT chk_vehicle_open_app_status CHECK (status IN ('enabled', 'disabled')),
    CONSTRAINT chk_vehicle_open_app_validity CHECK (valid_to IS NULL OR valid_to > valid_from)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS vehicle_open_app_vehicle (
    app_id BIGINT UNSIGNED NOT NULL,
    vin VARCHAR(64) NOT NULL,
    valid_from DATETIME(3) NOT NULL,
    valid_to DATETIME(3) NULL,
    granted_by VARCHAR(96) NOT NULL,
    created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
    PRIMARY KEY (app_id, vin),
    INDEX idx_vehicle_open_grant_vin_time (vin, valid_from, valid_to),
    INDEX idx_vehicle_open_grant_app_time (app_id, valid_from, valid_to, vin),
    CONSTRAINT fk_vehicle_open_grant_app FOREIGN KEY (app_id) REFERENCES vehicle_open_app(id) ON DELETE CASCADE,
    CONSTRAINT chk_vehicle_open_grant_validity CHECK (valid_to IS NULL OR valid_to > valid_from)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS vehicle_open_daily_energy (
    vin VARCHAR(64) NOT NULL,
    stat_date DATE NOT NULL,
    energy_type VARCHAR(24) NOT NULL,
    source_endpoint VARCHAR(128) NOT NULL DEFAULT '',
    consumption_kg DECIMAL(18,3) NOT NULL DEFAULT 0,
    unit VARCHAR(16) NOT NULL,
    first_mass_kg DECIMAL(18,3) NULL,
    last_mass_kg DECIMAL(18,3) NULL,
    sample_count BIGINT UNSIGNED NOT NULL DEFAULT 0,
    refuel_count INT UNSIGNED NOT NULL DEFAULT 0,
    quality_status VARCHAR(24) NOT NULL,
    quality_reason VARCHAR(255) NOT NULL DEFAULT '',
    calculated_at DATETIME(3) NOT NULL,
    created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
    PRIMARY KEY (vin, stat_date, energy_type),
    INDEX idx_vehicle_open_energy_date_type (stat_date, energy_type, quality_status, vin),
    CONSTRAINT chk_vehicle_open_energy_type CHECK (energy_type IN ('HYDROGEN')),
    CONSTRAINT chk_vehicle_open_energy_quality CHECK (quality_status IN ('OK', 'NO_DATA', 'SUSPECT'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS vehicle_open_api_audit (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    trace_id VARCHAR(64) NOT NULL,
    app_id BIGINT UNSIGNED NULL,
    endpoint VARCHAR(64) NOT NULL,
    result VARCHAR(24) NOT NULL,
    requested_vehicle_count INT UNSIGNED NOT NULL DEFAULT 0,
    detail VARCHAR(512) NOT NULL DEFAULT '',
    created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    INDEX idx_vehicle_open_api_audit_app_time (app_id, created_at),
    INDEX idx_vehicle_open_api_audit_trace (trace_id),
    INDEX idx_vehicle_open_api_audit_result_time (result, created_at),
    CONSTRAINT fk_vehicle_open_api_audit_app FOREIGN KEY (app_id) REFERENCES vehicle_open_app(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS vehicle_open_admin_audit (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    app_id BIGINT UNSIGNED NULL,
    actor VARCHAR(96) NOT NULL,
    action VARCHAR(64) NOT NULL,
    detail_json JSON NULL,
    created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    INDEX idx_vehicle_open_admin_audit_app_time (app_id, created_at),
    INDEX idx_vehicle_open_admin_audit_actor_time (actor, created_at),
    CONSTRAINT fk_vehicle_open_admin_audit_app FOREIGN KEY (app_id) REFERENCES vehicle_open_app(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;