codetics-ocpp-service 0.1.0

// SPDX-License-Identifier: Apache-2.0
// Copyright 2026 Kevin Vavelin, Codetics (https://codetics.fr)

//! Tariffs, tariff assignments, transaction samples and costs.

use serde::Serialize;
use serde_json::Value;

use super::{Db, Ts};

#[derive(Debug, Clone, Serialize, sqlx::FromRow)]
pub struct TariffRow {
    pub tariff_id: String,
    pub currency: String,
    pub tariff: Value,
    pub created_at: Ts,
}

pub enum Upsert {
    Created(TariffRow),
    Unchanged(TariffRow),
    /// The id exists with different content (I08.FR.08: a tariff id never changes content).
    Conflict,
}

pub async fn upsert(
    db: &Db,
    tariff_id: &str,
    currency: &str,
    tariff: &Value,
) -> sqlx::Result<Upsert> {
    let created: Option<TariffRow> = sqlx::query_as(
        "INSERT INTO tariffs (tariff_id, currency, tariff) VALUES ($1, $2, $3) ON CONFLICT (tariff_id) DO NOTHING RETURNING tariff_id, currency, tariff, created_at",
    )
    .bind(tariff_id)
    .bind(currency)
    .bind(sqlx::types::Json(tariff))
    .fetch_optional(db)
    .await?;
    if let Some(row) = created {
        return Ok(Upsert::Created(row));
    }
    let existing = get(db, tariff_id).await?.expect("conflicting row exists");
    Ok(if &existing.tariff == tariff {
        Upsert::Unchanged(existing)
    } else {
        Upsert::Conflict
    })
}

pub async fn get(db: &Db, tariff_id: &str) -> sqlx::Result<Option<TariffRow>> {
    sqlx::query_as(
        "SELECT tariff_id, currency, tariff, created_at FROM tariffs WHERE tariff_id = $1",
    )
    .bind(tariff_id)
    .fetch_optional(db)
    .await
}

pub async fn list(db: &Db) -> sqlx::Result<Vec<TariffRow>> {
    sqlx::query_as("SELECT tariff_id, currency, tariff, created_at FROM tariffs ORDER BY tariff_id")
        .fetch_all(db)
        .await
}

pub async fn delete(db: &Db, tariff_id: &str) -> sqlx::Result<bool> {
    Ok(sqlx::query("DELETE FROM tariffs WHERE tariff_id = $1")
        .bind(tariff_id)
        .execute(db)
        .await?
        .rows_affected()
        > 0)
}

#[derive(Debug, Clone, Serialize, sqlx::FromRow)]
pub struct Assignment {
    pub evse_number: i32,
    pub tariff_id: String,
    pub valid_from: Option<Ts>,
    pub install_status: Option<String>,
    pub updated_at: Ts,
}

/// Assigns a default tariff to an EVSE (0 = all EVSEs). A tariff that is valid
/// now replaces the currently valid ones; a future one is kept next to them
/// until it becomes valid (I07.FR.20–23).
pub async fn assign_default(
    db: &Db,
    station_id: i64,
    evse: i32,
    tariff_id: &str,
    valid_from: Option<Ts>,
    install_status: Option<&str>,
) -> sqlx::Result<()> {
    let mut tx = db.begin().await?;
    let now = Ts(ocpp_core::DateTime::now());
    if valid_from.is_none_or(|v| v <= now) {
        sqlx::query("DELETE FROM station_tariffs WHERE station_id = $1 AND evse_number = $2 AND tariff_id <> $3 AND (valid_from IS NULL OR valid_from <= $4)")
            .bind(station_id)
            .bind(evse)
            .bind(tariff_id)
            .bind(now)
            .execute(&mut *tx)
            .await?;
    }
    sqlx::query(
        "INSERT INTO station_tariffs (station_id, evse_number, tariff_id, valid_from, install_status) VALUES ($1, $2, $3, $4, $5) \
         ON CONFLICT (station_id, evse_number, tariff_id) DO UPDATE SET valid_from = EXCLUDED.valid_from, install_status = EXCLUDED.install_status, updated_at = now()",
    )
    .bind(station_id)
    .bind(evse)
    .bind(tariff_id)
    .bind(valid_from)
    .bind(install_status)
    .execute(&mut *tx)
    .await?;
    tx.commit().await
}

pub async fn remove_default(
    db: &Db,
    station_id: i64,
    tariff_id: &str,
    evse: Option<i32>,
) -> sqlx::Result<u64> {
    Ok(sqlx::query("DELETE FROM station_tariffs WHERE station_id = $1 AND tariff_id = $2 AND ($3::int IS NULL OR evse_number = $3)")
        .bind(station_id)
        .bind(tariff_id)
        .bind(evse)
        .execute(db)
        .await?
        .rows_affected())
}

pub async fn defaults(db: &Db, station_id: i64) -> sqlx::Result<Vec<Assignment>> {
    sqlx::query_as("SELECT evse_number, tariff_id, valid_from, install_status, updated_at FROM station_tariffs WHERE station_id = $1 ORDER BY evse_number, valid_from NULLS FIRST")
        .bind(station_id)
        .fetch_all(db)
        .await
}

/// Default tariff valid at `at` for an EVSE: EVSE-specific before EVSE 0, latest validFrom first.
pub async fn default_for(
    db: &Db,
    station_id: i64,
    evse: i32,
    at: Ts,
) -> sqlx::Result<Option<String>> {
    sqlx::query_scalar(
        "SELECT tariff_id FROM station_tariffs WHERE station_id = $1 AND evse_number IN ($2, 0) AND (valid_from IS NULL OR valid_from <= $3) \
         AND (install_status IS NULL OR install_status = 'Accepted') ORDER BY (evse_number = $2) DESC, valid_from DESC NULLS LAST LIMIT 1",
    )
    .bind(station_id)
    .bind(evse)
    .bind(at)
    .fetch_optional(db)
    .await
}

/// Driver-specific tariff of an id token (1.6-style case-insensitive match, any type).
pub async fn driver_tariff(
    db: &Db,
    id_token: &str,
    token_type: Option<&str>,
) -> sqlx::Result<Option<String>> {
    sqlx::query_scalar(
        "SELECT tariff_id FROM id_tokens WHERE tariff_id IS NOT NULL AND lower(id_token) = lower($1) AND ($2::text IS NULL OR token_type IN ($2, '*')) \
         ORDER BY (token_type = '*') ASC LIMIT 1",
    )
    .bind(id_token)
    .bind(token_type)
    .fetch_optional(db)
    .await
}

#[derive(Debug, Clone, sqlx::FromRow)]
pub struct TxCostInfo {
    pub id: i64,
    pub station_id: i64,
    pub transaction_uid: String,
    pub status: String,
    pub evse_number: Option<i32>,
    pub id_token: Option<String>,
    pub id_token_type: Option<String>,
    pub started_at: Option<Ts>,
    pub ended_at: Option<Ts>,
    pub last_event_at: Option<Ts>,
    pub tariff_id: Option<String>,
    pub payment_brand: Option<String>,
    pub payment_recognition: Option<String>,
    pub reserved_at: Option<Ts>,
    pub cost_details: Option<Value>,
    pub station_cost_details: Option<Value>,
    pub currency: Option<String>,
    pub cost_excl_tax: Option<f64>,
    pub cost_incl_tax: Option<f64>,
}

pub async fn tx_info(db: &Db, transaction_id: i64) -> sqlx::Result<Option<TxCostInfo>> {
    sqlx::query_as(
        "SELECT t.id, t.station_id, t.transaction_uid, t.status, t.evse_number, t.id_token, t.id_token_type, t.started_at, t.ended_at, t.last_event_at, \
           t.tariff_id, t.payment_brand, t.payment_recognition, r.created_at AS reserved_at, t.cost_details, t.station_cost_details, t.currency, \
           t.cost_excl_tax, t.cost_incl_tax \
         FROM transactions t LEFT JOIN reservations r ON r.id = t.reservation_db_id WHERE t.id = $1",
    )
    .bind(transaction_id)
    .fetch_optional(db)
    .await
}

/// Sets the tariff of a transaction once (the tariff does not change during a transaction, I07.FR.24).
pub async fn set_tx_tariff_once(
    db: &Db,
    transaction_id: i64,
    tariff_id: &str,
    currency: &str,
) -> sqlx::Result<()> {
    sqlx::query(
        "UPDATE transactions SET tariff_id = $2, currency = $3 WHERE id = $1 AND tariff_id IS NULL",
    )
    .bind(transaction_id)
    .bind(tariff_id)
    .bind(currency)
    .execute(db)
    .await?;
    Ok(())
}

pub async fn set_payment(
    db: &Db,
    transaction_id: i64,
    brand: Option<&str>,
    recognition: Option<&str>,
) -> sqlx::Result<()> {
    sqlx::query("UPDATE transactions SET payment_brand = COALESCE($2, payment_brand), payment_recognition = COALESCE($3, payment_recognition) WHERE id = $1")
        .bind(transaction_id)
        .bind(brand)
        .bind(recognition)
        .execute(db)
        .await?;
    Ok(())
}

pub async fn set_cost(
    db: &Db,
    transaction_id: i64,
    details: &Value,
    currency: &str,
    excl: Option<f64>,
    incl: Option<f64>,
) -> sqlx::Result<()> {
    sqlx::query("UPDATE transactions SET cost_details = $2, currency = $3, cost_excl_tax = $4, cost_incl_tax = $5 WHERE id = $1")
        .bind(transaction_id)
        .bind(sqlx::types::Json(details))
        .bind(currency)
        .bind(excl)
        .bind(incl)
        .execute(db)
        .await?;
    Ok(())
}

/// Cost details reported by a station doing local cost calculation (I12).
pub async fn set_station_cost(db: &Db, transaction_id: i64, details: &Value) -> sqlx::Result<()> {
    sqlx::query("UPDATE transactions SET station_cost_details = $2 WHERE id = $1")
        .bind(transaction_id)
        .bind(sqlx::types::Json(details))
        .execute(db)
        .await?;
    Ok(())
}

#[derive(Debug, Clone, Default)]
pub struct NewSample<'a> {
    pub at: Option<Ts>,
    pub seq_no: Option<i32>,
    pub event_type: Option<&'a str>,
    pub charging_state: Option<&'a str>,
    pub energy_wh: Option<f64>,
    pub power_w: Option<f64>,
    pub current_a: Option<f64>,
    pub tariff_id: Option<&'a str>,
}

pub async fn insert_sample(db: &Db, transaction_id: i64, s: &NewSample<'_>) -> sqlx::Result<()> {
    sqlx::query(
        "INSERT INTO transaction_samples (transaction_id, at, seq_no, event_type, charging_state, energy_wh, power_w, current_a, tariff_id) \
         VALUES ($1, COALESCE($2, now()), $3, $4, $5, $6, $7, $8, $9)",
    )
    .bind(transaction_id)
    .bind(s.at)
    .bind(s.seq_no)
    .bind(s.event_type)
    .bind(s.charging_state)
    .bind(s.energy_wh)
    .bind(s.power_w)
    .bind(s.current_a)
    .bind(s.tariff_id)
    .execute(db)
    .await?;
    Ok(())
}

#[derive(Debug, Clone, sqlx::FromRow)]
pub struct SampleRow {
    pub at: Ts,
    pub charging_state: Option<String>,
    pub energy_wh: Option<f64>,
    pub power_w: Option<f64>,
    pub current_a: Option<f64>,
    pub tariff_id: Option<String>,
}

pub async fn samples(db: &Db, transaction_id: i64) -> sqlx::Result<Vec<SampleRow>> {
    sqlx::query_as("SELECT at, charging_state, energy_wh, power_w, current_a, tariff_id FROM transaction_samples WHERE transaction_id = $1 ORDER BY at, seq_no NULLS FIRST")
        .bind(transaction_id)
        .fetch_all(db)
        .await
}