/**
 * i-net software provides programming examples for illustration only, without warranty
 * either expressed or implied, including, but not limited to, the implied warranties
 * of merchantability and/or fitness for a particular purpose. This programming example
 * assumes that you are familiar with the programming language being demonstrated and
 * the tools used to create and debug procedures. i-net software support professionals
 * can help explain the functionality of a particular procedure, but they will not modify
 * these examples to provide added functionality or construct procedures to meet your
 * specific needs.
 *
 * Copyright © 1999-2026 i-net software GmbH, Berlin, Germany.
**/
package com.inet.taskplanner.databaseaction;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.ArrayList;
import java.util.List;

import com.inet.taskplanner.TaskPlannerServerPlugin;
import com.inet.taskplanner.server.api.action.ResultAction;
import com.inet.taskplanner.server.api.error.TaskExecutionException;
import com.inet.taskplanner.server.api.job.JobResultContainer;
import com.inet.taskplanner.server.api.result.FileResult;
import com.inet.taskplanner.server.api.result.Result;
import com.inet.taskplanner.server.api.result.ResultFlavor;

/**
 * Action that allows saving files into the database, using the JDBC URL for connection.
 */
public class JdbcDatabaseResultAction extends ResultAction {

    private String jdbcURL;
    private String username;
    private String password;
    private String table;
    private String fileColumn;
    private String identifierColumn;
    private String identifierValue;

    /** Creates instance.
     * @param jdbcURL JDBC URL property.
     * @param username user name property.
     * @param password password property (decoded).
     * @param table table property.
     * @param fileColumn file column property.
     */
    public JdbcDatabaseResultAction( String jdbcURL, String username, String password, String table, String fileColumn ) {
        this.jdbcURL = jdbcURL;
        this.username = username;
        this.password = password;
        this.table = table;
        this.fileColumn = fileColumn;
    }

    /** Creates instance.
     * @param jdbcURL JDBC URL property.
     * @param username user name property.
     * @param password password property (decoded).
     * @param table table property.
     * @param fileColumn file column property.
     * @param identifierColumn identifier column property.
     * @param identifierValue identifier value property.
     */
    public JdbcDatabaseResultAction( String jdbcURL, String username, String password, String table, String fileColumn, String identifierColumn, String identifierValue ) {
        this.jdbcURL = jdbcURL;
        this.username = username;
        this.password = password;
        this.table = table;
        this.fileColumn = fileColumn;
        this.identifierColumn = identifierColumn;
        this.identifierValue = identifierValue;
    }

    /**
     * {@inheritDoc}
     */
    @Override
    protected void handle( List<JobResultContainer> resultContainers ) throws TaskExecutionException {
        List<FileResult> fileResults = new ArrayList<>();
        // Get all results with the flavor "file" from the result containers
        for( JobResultContainer container : resultContainers ) {
            for( Result result : container.getResults( ResultFlavor.FILE ) ) {
                try {
                    FileResult fileResult = (FileResult)result;
                    if( fileResult.getFileSize() == 0 ) {
                        // Skip empty files
                        continue;
                    }
                    fileResults.add( (FileResult)result );
                } catch( Exception ex ) {
                    TaskPlannerServerPlugin.LOGGER.error( ex );
                }
            }
        }

        if( fileResults.isEmpty() ) {
            // No files to save
            return;
        }

        // Save all file results into database
        String sql;
        boolean numericIdentifier;
        if( identifierColumn != null ) {
            sql = String.format( "INSERT INTO %s (%s,%s) VALUES (?,?)", table, fileColumn, identifierColumn );
            try {
                Long.valueOf( identifierValue );
                numericIdentifier = true;
            } catch( Exception ex ) {
                numericIdentifier = false;
            }
        } else {
            sql = String.format( "INSERT INTO %s (%s) VALUES (?)", table, fileColumn );
            numericIdentifier = false;
        }
        try (Connection con = DriverManager.getConnection( jdbcURL, username, password ); PreparedStatement pstm = con.prepareStatement( sql )) {
            for( FileResult file : fileResults ) {
                pstm.setBinaryStream( 1, file.getFileContent() );
                if( identifierColumn != null ) {
                    if( numericIdentifier ) {
                        pstm.setLong( 2, Long.valueOf( identifierValue ).longValue() );
                    } else {
                        pstm.setString( 2, identifierValue );
                    }
                }
                pstm.executeUpdate();
            }
        } catch( Exception ex ) {
            throw new TaskExecutionException( ex );
        }
    }
}