/*
 * Click nbfs://nbhost/SystemFileSystem/Templates/Licenses/license-default.txt to change this license
 * Click nbfs://nbhost/SystemFileSystem/Templates/Classes/Class.java to edit this template
 */
package com.megatim.fdxcommons.tools.database.queries;

import com.megatim.fdxcommons.tools.database.contrat.AppColumnDefinition;
import com.megatim.fdxcommons.tools.database.tables.appcolumns.FdxConsultationIndexColumnDefinition;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 *
 * @author ASUS
 */
public class TableDefinitionQueries {

    private final String tableName;
    private final Connection connection;

    public TableDefinitionQueries(String tableName, Connection connection) {
        this.tableName = tableName;
        this.connection = connection;
    }

    public boolean tableExists() throws SQLException {
        Long nb = 0L;

        try ( PreparedStatement statement = connection.prepareStatement(tableExistsQuery())) {
            ResultSet resultSet = statement.executeQuery();

            if (resultSet.next()) {
                nb = resultSet.getLong(1);
            }
        }
        return nb == 1;
    }
    
    public boolean constraintExists(String constraintName) throws SQLException {
        Long nb = 0L;

        try ( PreparedStatement statement = connection.prepareStatement(constraintExistsQuery(constraintName))) {
            ResultSet resultSet = statement.executeQuery();

            if (resultSet.next()) {
                nb = resultSet.getLong(1);
            }
        }
        return nb == 1;
    }

    public boolean columnExists(String columnName) throws SQLException {
        Long nb = 0L;

        try ( PreparedStatement statement = connection.prepareStatement(columnExistsQuery(columnName))) {
            ResultSet resultSet = statement.executeQuery();

            if (resultSet.next()) {
                nb = resultSet.getLong(1);
            }
        }
        return nb == 1;
    }

    private String columnExistsQuery(String columnName) {
        return "SELECT COUNT(column_name)"
                + " FROM information_schema.columns"
                + " WHERE table_name = '" + tableName + "' AND column_name = '" + columnName + "'";
    }

    private String constraintExistsQuery(String constraintName) {
        return "SELECT COUNT(*) FROM  pg_constraint WHERE conname =  '" + constraintName + "'";
    }

    private String tableExistsQuery() {
        return "SELECT  COUNT(table_name)"
                + " FROM  information_schema.tables"
                + " WHERE  table_schema LIKE 'public' AND  table_type LIKE 'BASE TABLE' AND table_name = '" + tableName + "'";
    }

    public void addAppColumn(AppColumnDefinition appColumn) throws SQLException {

        try ( Statement stmt = connection.createStatement()) {
            String query = addAppColumnQuery(appColumn).toString();
            stmt.execute(query);
        }
    }

    public void dropAppColumn(AppColumnDefinition appColumn) throws SQLException {
        String query = dropColumnQuery(appColumn);
        
        try ( Statement stmt = connection.createStatement()) {
            stmt.execute(query);
        }
    }

    public void dropConstraint(String constraintName) throws SQLException {
        String query = dropConstraintQuery(constraintName);
        
        try ( Statement stmt = connection.createStatement()) {
            stmt.execute(query);
        }
    }

    public void addPkConstraint(String constraintName) throws SQLException {
        String query = addPkConstraintQuery(constraintName);
        
        try ( Statement stmt = connection.createStatement()) {
            stmt.execute(query);
        }
    }

    private String dropColumnQuery(AppColumnDefinition column) {
        StringBuilder query = new StringBuilder("ALTER TABLE ");

        query
                .append(tableName)
                .append(" DROP COLUMN IF EXISTS ")
                .append(column.name())
                .append(";");

        return query.toString();
    }

    private String dropConstraintQuery(String constraintName) {
        StringBuilder query = new StringBuilder();

        query.append("ALTER TABLE ")
                .append(tableName)
                .append(" DROP CONSTRAINT IF EXISTS ")
                .append(constraintName)
                .append(";");

        return query.toString();
    }

    private String addPkConstraintQuery(String constraintName) {
        StringBuilder query = new StringBuilder();

        query.append("ALTER TABLE ")
                .append(tableName)
                .append(" ADD CONSTRAINT ")
                .append(constraintName)
                .append(" PRIMARY KEY(")
                .append(new FdxConsultationIndexColumnDefinition().name())
                .append(");");

        return query.toString();
    }

    private StringBuilder addAppColumnQuery(AppColumnDefinition column) {
        StringBuilder query = new StringBuilder("ALTER TABLE ");
        query
                .append(tableName)
                .append(" ADD COLUMN IF NOT EXISTS ")
                .append(column.name())
                .append(" ")
                .append(column.type())
                .append(column.isNull() ? " NULL " : " NOT NULL ");

        if (column.defaultValue() != null) {
            query.append("DEFAULT ").append(column.defaultValue());
        }
        return query;
    }
}