package org.mte.numecoeval.calculs.infrastructure.controller.export;

import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Profile;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Création d'API utilisées uniquement pour les tests e2e
 */
@RestController
@Slf4j
@Profile("!prod & !test")
@RequestMapping("indicateur")
public class IndicateurController {

    private static final int MAX_ROWS = 100_000;

    @Autowired
    private DataSource dataSource;

    @GetMapping("/equipementPhysiqueCsv")
    public String getEquipementPhysiqueList(
            @RequestParam String nomLot,
            @RequestParam String nomOrganisation,
            @RequestParam String fields
    ) {

        try (Connection conn = dataSource.getConnection()) {
            try (var ps = conn.prepareStatement(String.format("""
                    SELECT %s
                    FROM ind_indicateur_impact_equipement_physique
                    WHERE nom_lot = ? AND nom_organisation = ?
                    ORDER BY nom_equipement, critere, etapeacv;
                    """, fields))) {

                ps.setString(1, nomLot);
                ps.setString(2, nomOrganisation);
                return resultSetToCsv(ps.executeQuery());
            }
        } catch (SQLException e) {
            log.error("SQl exception", e);
            throw new RuntimeException(e);
        }
    }

    @GetMapping("/equipementVirtuelCsv")
    public String getEquipementVirtuelList(
            @RequestParam String nomLot,
            @RequestParam String nomOrganisation,
            @RequestParam String fields
    ) {

        try (Connection conn = dataSource.getConnection()) {
            try (var ps = conn.prepareStatement(String.format("""
                    SELECT %s
                    FROM ind_indicateur_impact_equipement_virtuel
                    WHERE nom_lot = ? AND nom_organisation = ?
                    ORDER BY nom_equipement, nom_equipement_virtuel, critere, etapeacv;
                    """, fields))) {

                ps.setString(1, nomLot);
                ps.setString(2, nomOrganisation);
                return resultSetToCsv(ps.executeQuery());
            }
        } catch (SQLException e) {
            log.error("SQl exception", e);
            throw new RuntimeException(e);
        }
    }

    @GetMapping("/applicationCsv")
    public String getApplicationList(
            @RequestParam String nomLot,
            @RequestParam String nomOrganisation,
            @RequestParam String fields
    ) {

        try (Connection conn = dataSource.getConnection()) {
            try (var ps = conn.prepareStatement(String.format("""
                    SELECT %s
                    FROM ind_indicateur_impact_application
                    WHERE nom_lot = ? AND nom_organisation = ?
                    ORDER BY nom_equipement_physique, nom_equipement_virtuel, nom_application, critere, etapeacv;
                    """, fields))) {

                ps.setString(1, nomLot);
                ps.setString(2, nomOrganisation);
                return resultSetToCsv(ps.executeQuery());
            }
        } catch (SQLException e) {
            log.error("SQl exception", e);
            throw new RuntimeException(e);
        }
    }

    @GetMapping("/reseauCsv")
    public String getReseauList(
            @RequestParam String nomLot,
            @RequestParam String nomOrganisation,
            @RequestParam String fields
    ) {

        try (Connection conn = dataSource.getConnection()) {
            try (var ps = conn.prepareStatement(String.format("""
                    SELECT %s
                    FROM ind_indicateur_impact_reseau
                    WHERE nom_lot = ? AND nom_organisation = ?
                    ORDER BY nom_equipement, critere, etapeacv;
                    """, fields))) {

                ps.setString(1, nomLot);
                ps.setString(2, nomOrganisation);
                return resultSetToCsv(ps.executeQuery());
            }
        } catch (SQLException e) {
            log.error("SQl exception", e);
            throw new RuntimeException(e);
        }
    }

    @GetMapping("/operationNonITCsv")
    public String getOperationNonITList(
            @RequestParam String nomLot,
            @RequestParam String nomOrganisation,
            @RequestParam String fields
    ) {

        try (Connection conn = dataSource.getConnection()) {
            try (var ps = conn.prepareStatement(String.format("""
                    SELECT %s
                    FROM ind_indicateur_impact_operation_non_it
                    WHERE nom_lot = ? AND nom_organisation = ?
                    ORDER BY nom_item_non_it, critere, etapeacv;
                    """, fields))) {

                ps.setString(1, nomLot);
                ps.setString(2, nomOrganisation);
                return resultSetToCsv(ps.executeQuery());
            }
        } catch (SQLException e) {
            log.error("SQl exception", e);
            throw new RuntimeException(e);
        }
    }

    private String resultSetToCsv(ResultSet rs) throws SQLException {
        List<String> lines = new ArrayList<>();

        ResultSetMetaData rsmd = rs.getMetaData();
        int numCols = rsmd.getColumnCount();

        List<String> header = new ArrayList<>();
        for (int i = 1; i <= numCols; i++) header.add(rsmd.getColumnLabel(i));
        lines.add(String.join(",", header));

        int rows = 0;
        while (rs.next()) {
            rows++;
            List<String> values = new ArrayList<>();
            for (int i = 1; i <= numCols; i++) {
                var value = rs.getString(i);
                if (value == null) value = "";
                values.add(value);
            }
            lines.add(String.join(",", values));
            if (rows >= MAX_ROWS) break;
        }
        return String.join("\n", lines);
    }
}
