docsdao

package pt.agilefactor.trulydocs;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class DocsDAO {
    private String jdbcURL;
	private String jdbcUsername;
	private String jdbcPassword;
	private Connection jdbcConnection;

	public DocsDAO(String jdbcURL, String jdbcUsername, String jdbcPassword) {
		this.jdbcURL = jdbcURL;
		this.jdbcUsername = jdbcUsername;
		this.jdbcPassword = jdbcPassword;
	}

	protected void connect() throws SQLException {
		if (this.jdbcConnection == null || this.jdbcConnection.isClosed()) {
			try {
				Class.forName("com.mysql.cj.jdbc.Driver"); //$NON-NLS-1$
			} catch (ClassNotFoundException e) {
				throw new SQLException(e);
			}
			this.jdbcConnection = DriverManager.getConnection(this.jdbcURL, this.jdbcUsername, this.jdbcPassword);
		}
	}

	protected void disconnect() throws SQLException {
		if (this.jdbcConnection != null && !this.jdbcConnection.isClosed()) {
			this.jdbcConnection.close();
		}
	}

	public boolean insertDocs(Docs docs) throws SQLException {
		String sql = "INSERT INTO document (title_ds, document_tp, created_dt, update_dt, short_ds, created_user_id, update_user_id, long_ds) VALUES (?, ?, ?, ?, ?, ?, ?, ?)"; //$NON-NLS-1$
		boolean rowInserted = false;
		connect();
		try (PreparedStatement statement = this.jdbcConnection.prepareStatement(sql);) {
//			PreparedStatement statement = this.jdbcConnection.prepareStatement(sql);
			statement.setString(1, docs.getTitle_ds());
			statement.setString(2, docs.getDocument_tp());
			statement.setString(3, docs.getCreated_dt());
			statement.setString(4, docs.getUpdate_dt());
			statement.setString(5, docs.getShort_ds());
			statement.setString(6, docs.getCreated_user_id());
			statement.setString(7, docs.getUpdate_user_id());
			statement.setString(8, docs.getLong_ds());

			rowInserted = statement.executeUpdate() > 0;
			statement.close();
		}
		disconnect();
		return rowInserted;
	}

	public List<Docs> listAllDocs() throws SQLException {
		List<Docs> listDocs = new ArrayList<>();
		String sql = "SELECT * FROM document"; //$NON-NLS-1$
		connect();
		try (Statement statement = this.jdbcConnection.createStatement();) {
//			Statement statement = this.jdbcConnection.createStatement();
			try (ResultSet resultSet = statement.executeQuery(sql)) {
//				ResultSet resultSet = statement.executeQuery(sql);

				while (resultSet.next()) {
					String document_id = resultSet.getString("document_id"); //$NON-NLS-1$
					String title_ds = resultSet.getString("title_ds"); //$NON-NLS-1$
					String document_tp = resultSet.getString("document_tp"); //$NON-NLS-1$
					String created_dt = resultSet.getString("created_dt"); //$NON-NLS-1$
					String update_dt = resultSet.getString("update_dt"); //$NON-NLS-1$
					String short_ds = resultSet.getString("short_ds"); //$NON-NLS-1$
					String created_user_id = resultSet.getString("created_user_id"); //$NON-NLS-1$
					String update_user_id = resultSet.getString("update_user_id"); //$NON-NLS-1$
					String long_ds = resultSet.getString("long_ds"); //$NON-NLS-1$

					Docs docs = new Docs(document_id, title_ds, document_tp, created_dt, update_dt, short_ds, created_user_id,
							update_user_id, long_ds);
					listDocs.add(docs);
				}
				resultSet.close();
			}
			statement.close();
		}
		disconnect();
		return listDocs;
	}

	public boolean delete(int document_id) throws SQLException {
		String sql = "DELETE FROM document WHERE document_id = ?"; //$NON-NLS-1$
		boolean rowDeleted = false;
		connect();
		try (PreparedStatement statement = this.jdbcConnection.prepareStatement(sql);) {
//			PreparedStatement statement = this.jdbcConnection.prepareStatement(sql);
			statement.setInt(1, document_id);
			rowDeleted = statement.executeUpdate() > 0;
			statement.close();
		}
		disconnect();
		return rowDeleted;
	}

	public boolean download(int document_id) throws SQLException {
		String sql = "SELECT FROM document WHERE document_id = ?"; //$NON-NLS-1$
		boolean rowDownload = false;
		connect();
		try (PreparedStatement statement = this.jdbcConnection.prepareStatement(sql);) {
			statement.setInt(1, document_id);
			rowDownload = statement.executeUpdate() > 0;
			statement.close();
		}
		disconnect();
		return rowDownload;
	}
	/*
	 * public boolean updateDocs(Docs docs) throws SQLException { String sql =
	 * "UPDATE book SET title = ?, author = ?, price = ?"; sql +=
	 * " WHERE book_id = ?"; connect();
	 *
	 * PreparedStatement statement = jdbcConnection.prepareStatement(sql);
	 * statement.setString(1, docs.getTitle()); statement.setString(2,
	 * docs.getAuthor()); statement.setFloat(3, docs.getPrice());
	 * statement.setInt(4, docs.getId());
	 *
	 * boolean rowUpdated = statement.executeUpdate() > 0; statement.close();
	 * disconnect(); return rowUpdated; }
	 */

	/*
	 * public Files getFiles(int document_id) throws SQLException { Files files =
	 * null; String sql = "SELECT * FROM file WHERE document_id = ?"; connect();
	 * PreparedStatement statement = jdbcConnection.prepareStatement(sql);
	 * statement.setInt(1, document_id); ResultSet resultSet =
	 * statement.executeQuery(); if (resultSet.next()) { String file_ds =
	 * resultSet.getString("file_ds"); String first_upload_dt =
	 * resultSet.getString("first_upload_dt"); String last_upload_dt =
	 * resultSet.getString("last_upload_dt");
	 *
	 * files = new Files(document_id, file_ds, first_upload_dt, last_upload_dt); }
	 *
	 * resultSet.close(); statement.close();
	 *
	 * return files; }
	 */
	public List<Files> listAllFiles(String document_id) throws SQLException {
		List<Files> listFiles = new ArrayList<>();
		String sql = "SELECT * FROM file WHERE document_id = ?"; //$NON-NLS-1$
		connect();
		try (PreparedStatement statement = this.jdbcConnection.prepareStatement(sql);) {
//			PreparedStatement statement = this.jdbcConnection.prepareStatement(sql);
			statement.setString(1, document_id);
			try (ResultSet resultSet = statement.executeQuery();) {
//				ResultSet resultSet = statement.executeQuery();

				while (resultSet.next()) {
					String file_id = resultSet.getString("file_id"); //$NON-NLS-1$
					String first_upload_user_id = resultSet.getString("first_upload_user_id"); //$NON-NLS-1$
					String last_upload_user_id = resultSet.getString("last_upload_user_id"); //$NON-NLS-1$
					String file_ds = resultSet.getString("file_ds"); //$NON-NLS-1$
					String first_upload_dt = resultSet.getString("first_upload_dt"); //$NON-NLS-1$
					String last_upload_dt = resultSet.getString("last_upload_dt"); //$NON-NLS-1$
					String content_tp = resultSet.getString("content_tp"); //$NON-NLS-1$
					String uuid_fn = resultSet.getString("uuid_fn"); //$NON-NLS-1$
					String file_nm = resultSet.getString("file_nm"); //$NON-NLS-1$

					Files files = new Files(file_id, document_id, first_upload_user_id, last_upload_user_id, file_ds,
							first_upload_dt, last_upload_dt, content_tp, uuid_fn, file_nm);
					listFiles.add(files);
				}
				resultSet.close();
			}
			statement.close();
		}
		disconnect();
		return listFiles;
	}

	public List<Docs> listDoc(String document_id) throws SQLException {
		List<Docs> listDoc = new ArrayList<>();
		String sql = "SELECT * FROM document WHERE document_id = ?"; //$NON-NLS-1$
		connect();
		try (PreparedStatement statement = this.jdbcConnection.prepareStatement(sql);) {
//			PreparedStatement statement = this.jdbcConnection.prepareStatement(sql);
			statement.setString(1, document_id); // MYSQL BIGINT works as a String.
			try (ResultSet resultSet = statement.executeQuery();) {
//				ResultSet resultSet = statement.executeQuery();

				while (resultSet.next()) {
					String title_ds = resultSet.getString("title_ds"); //$NON-NLS-1$
					String document_tp = resultSet.getString("document_tp"); //$NON-NLS-1$
					String created_dt = resultSet.getString("created_dt"); //$NON-NLS-1$
					String update_dt = resultSet.getString("update_dt"); //$NON-NLS-1$
					String short_ds = resultSet.getString("short_ds"); //$NON-NLS-1$
					String created_user_id = resultSet.getString("created_user_id"); //$NON-NLS-1$
					String update_user_id = resultSet.getString("update_user_id"); //$NON-NLS-1$
					String long_ds = resultSet.getString("long_ds"); //$NON-NLS-1$

					Docs docs = new Docs(document_id, title_ds, document_tp, created_dt, update_dt, short_ds, created_user_id,
							update_user_id, long_ds);
					listDoc.add(docs);
				}
				resultSet.close();
			}
			statement.close();
		}
		disconnect();
		return listDoc;
	}

	public boolean insertFilesInDoc(Files files) throws SQLException {
		String sql = "INSERT INTO file (document_id, file_nm, file_ds, uuid_fn, first_upload_dt, last_upload_dt, first_upload_user_id, last_upload_user_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?)"; //$NON-NLS-1$
		boolean rowInserted = false;
		connect();
		try (PreparedStatement statement = this.jdbcConnection.prepareStatement(sql);) {
//			PreparedStatement statement = this.jdbcConnection.prepareStatement(sql);
			statement.setString(1, files.getDocument_id());
			statement.setString(2, files.getFile_nm());
			statement.setString(3, files.getFile_ds());
			statement.setString(4, files.getUuid_fn());
			statement.setString(5, files.getFirst_upload_dt());
			statement.setString(6, files.getLast_upload_dt());
			statement.setString(7, files.getFirst_upload_user_id());
			statement.setString(8, files.getLast_upload_user_id());

			rowInserted = statement.executeUpdate() > 0;
			statement.close();
		}
		disconnect();
		return rowInserted;
	}
}