/**
 *  Copyright © 2025, Luis Andrés Lange <https://javacomm.net>
 *
 *  Previously released under Apache License 2.0; now licensed under MPL 2.0.
 *
 *  This Source Code Form is subject to the terms of the Mozilla Public
 *  License, v. 2.0. If a copy of the MPL was not distributed with this
 *  file, You can obtain one at http://mozilla.org/MPL/2.0/.
 *
 *  ----------------------------------------------------------------------------
 *
 *  Exhibit B - "Incompatible With Secondary Licenses" Notice
 *
 *  This Source Code Form is "Incompatible With Secondary Licenses",
 *  as defined by the Mozilla Public License, v. 2.0.
 *
 *  In short:
 *  - This file may be used, modified, and distributed under MPL 2.0 only.
 *  - It may NOT be relicensed under GPL, LGPL, AGPL, or any other Secondary License.
 *
 *  Rationale:
 *  - Ensures that the code remains MPL-2.0.
 *  - Avoids legal conflicts with GPL-licensed libraries (e.g., VideoLAN).
 *  - Maximizes usability for commercial and security-critical applications.
 *
 */
package net.javacomm.database;

import jakarta.xml.bind.JAXBException;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.apache.tomcat.jdbc.pool.DataSource;
import org.apache.tomcat.jdbc.pool.PoolProperties;
import org.xml.sax.SAXException;
import net.javacomm.config.Context;
import net.javacomm.config.Resource;
import net.javacomm.database.entity.Config;
import net.javacomm.environment.Environment;
import net.javacomm.facade.DatabaseService;
import net.javacomm.file.ContextFile;



/**
 * 
 * Der Server verbindet sich mit der Datenbank über einen Pool und eine
 * Datenquelle. Beides wird über die Datei <b><i>./etc/connection.xml</i></b>
 * konfiguriert. Bevor connect() aufgerufen wird, sollte zuerst
 * getDefaultPoolProperties() aufgerufen werden. Die Eigenschaften aus der
 * Konfigurationsdatei können überschrieben werden.
 * 
 * <pre>
 *   props = getDefaultPoolProperties()
 *   props.setUsername("123");
 *   props.setPassword("456");
 *   props.setUrl("jdbc:mysql://localhost:3306/datenbank");
 *   connect(props);
 * 
 * </pre>
 * 
 * @author llange
 *
 */
public class DatabaseServiceImpl extends DatabaseService {

  private final static Logger log = LogManager.getLogger(DatabaseServiceImpl.class);
  private static DatabaseService instance;
  private static Environment environment;
  static {
    environment = Environment.getInstance(); // muss vor DatabaseServiceImpl stehen
    try {
      instance = new DatabaseServiceImpl();
    }
    catch (SAXException | JAXBException | PoolException e) {
      log.fatal(e.getMessage(), e);
      log.fatal("Eine Datenbankverbindung kann nicht hergestellt werden.");
    }
  }

  private DataSource datasource;

  private DatabaseServiceImpl() throws SAXException, JAXBException, PoolException {
    datasource = new DataSource();
    log.info("datasource done");
    connect();
    pool = datasource.getPool();
  }



  /**
   * Der Server kennt genau eine Datenbankkomponente.
   * 
   * @return eine Referenz auf {@code Database}
   */
  public synchronized static DatabaseService getInstance() {
    return instance;
  }



  @Override
  public void clearGroupUser() {
    String sql = persistence.getSQL(Persistence.KEY_CLEAR_GRUPPE);
    if (log.isDebugEnabled()) log.debug(sql);
    Connection connection = null;
    Statement statement = null;
    try {
      connection = pool.getConnection();
      statement = connection.createStatement();
      statement.executeUpdate(sql);
    }
    catch (SQLException e) {
      log.fatal(e.getMessage(), e.getCause());
    }
    finally {
      try {
        if (statement != null) statement.close();
      }
      catch (SQLException e) {}
      try {
        if (connection != null) connection.close();
      }
      catch (SQLException e) {}
    }
  }



  /**
   * 
   * Eine Datenbankverbindung wird mit den Defaultwerten aus der
   * Konfigurationsdatei <b><i>connection.xml</i></b> hergestellt.
   * 
   * @throws JAXBException
   * @throws SAXException
   * @throws PoolException
   * 
   * @throws DatabaseException
   */
  @Override
  public void connect() throws SAXException, JAXBException, PoolException {
    PoolProperties props = getDefaultPoolProperties();
    connect(props);
  }



  /**
   * 
   * Eine Danbankverbindung wird hergestellt.
   * 
   * @param props
   *              konfiguriert einen Datenbankpool und eine Datenquelle
   * 
   * @throws PoolException
   *                       DataSource hat keinen Connection Pool für die Datenbank
   *                       zurückgeleiefert
   */
  @Override
  public final void connect(PoolProperties props) throws PoolException {
    if (props == null) throw new IllegalArgumentException("props is null");
    datasource.close();
    datasource.setPoolProperties(props);
    pool = datasource.getPool();
    if (pool == null) {
      log.fatal("check the PoolProperties:");
      log.fatal(props);
      throw new PoolException("pool is closed, check the PoolProperties");
    }
    log.info("(C) 2015 www.nexuswob.org, Database connection established");
    log.info("url -----------------> " + props.getUrl());
    log.info("driverClassaNme -----> " + props.getDriverClassName());
    log.info("maxActive -----------> " + props.getMaxActive());
    deleteUserXChat();
    deleteOnline();
    new Thread() {
      @Override
      public void run() {
        while (true) {
          deleteConfirmationFlag();
          deleteRecord();
          try {
            Thread.sleep(3600000);
          }
          catch (InterruptedException e) {
            log.error(e.getMessage(), e.getCause());
          }
        }
      }
    }.start();
    deleteTelkos();
    deleteAllVideo();
    deleteAllVideoSender();
    deleteTemprooms();
    deleteAllPrivateChats();
    deleteTransferLib();
    deleteGroupUser();
    clearGroupUser();
    deleteInactiveUser();
    deleteOrphainedRoom();
    deleteToPhone();
    deleteNirvanaSuffixes();
    deleteBenutzerstatus();
    deleteAllPrivateChatfiles();
    deletePrivateChunks();
    updateChatfiles();
    deleteKonferenzraumSessions();
    updateUnmuteAll();
    insertConfigIf();
    switchOffProjectors();
    updateRSA();
    deleteAllToken();
  }



  @Override
  public void deleteAllPrivateChats() {
    Connection connection = null;
    PreparedStatement statement = null;
    try {
      connection = pool.getConnection();
      String sql = persistence.getSQL(Persistence.KEY_DELETE_ALL_PRIVATECHATS);
      if (log.isDebugEnabled()) log.debug(sql);
      statement = connection.prepareStatement(sql);
      statement.executeUpdate();
    }
    catch (SQLException e) {
      log.warn(e.getMessage(), e.getCause());
    }
    finally {
      try {
        if (statement != null) statement.close();
      }
      catch (SQLException e) {}
      try {
        if (connection != null) connection.close();
      }
      catch (SQLException e) {}
    }
  }



  @Override
  public void deleteForceRecord() {
    String sql = persistence.getSQL(Persistence.KEY_DELETE_FORCE_TB_RECORD);
    if (log.isDebugEnabled()) log.debug(sql);
    try(
      Connection connection = pool.getConnection();
      Statement statement = connection.createStatement()
    ) {
      statement.executeUpdate(sql);
    }
    catch (SQLException e) {
      log.error(e.getMessage(), e);
    }
  }



  @Override
  public int deleteGroupUser() {
    int result = 0;
    persistence = Persistence.getInstance();
    String sql = persistence.getSQL(Persistence.KEY_DELETE_GRUPPE_USER);
    if (log.isDebugEnabled()) log.debug(sql);
    Connection connection = null;
    Statement statement = null;
    try {
      connection = pool.getConnection();
      statement = connection.createStatement();
      result = statement.executeUpdate(sql);
    }
    catch (SQLException e) {
      log.fatal(e.getMessage(), e.getCause());
    }
    finally {
      try {
        if (statement != null) statement.close();
      }
      catch (SQLException e) {}
      try {
        if (connection != null) connection.close();
      }
      catch (SQLException e) {}
    }
    return result;
  }



  @Override
  public void deleteInactiveUser() {
    Connection connection = null;
    Statement statement = null;
    String sql = persistence.getSQL(Persistence.KEY_DELETE_INACTIVE_USER);
    if (log.isDebugEnabled()) log.debug(sql);
    try {
      connection = pool.getConnection();
      statement = connection.createStatement();
      statement.executeUpdate(sql);
    }
    catch (SQLException e) {
      log.warn(e.getMessage());
    }
    finally {
      try {
        if (statement != null) statement.close();
      }
      catch (SQLException e) {}
      try {
        if (connection != null) connection.close();
      }
      catch (SQLException e) {}
    }
  }



  /**
   * Diese Methode ist nur für Testzwecke. Alle Mailserverdaten werden gelöscht.
   * 
   */
  public void deleteMailserver() {
    String sql = persistence.getSQL(Persistence.KEY_DELETE_CONFIG);
    logSQLKey(Persistence.KEY_DELETE_CONFIG, sql);
    try(
      Connection connection = pool.getConnection();
      Statement statement = connection.createStatement();
    ) {
      statement.executeUpdate(sql);
    }
    catch (SQLException e) {
      log.fatal(e.getMessage(), e.getCause());
    }
  }



  @Override
  public void deleteOnline() {
    String sql = persistence.getSQL(Persistence.KEY_DELETE_ALL_ONLINE);
    if (log.isDebugEnabled()) log.debug(sql);

    try(
      Connection connection = pool.getConnection();
      PreparedStatement statement = connection.prepareStatement(sql)
    ) {
      statement.executeUpdate();
    }
    catch (SQLException e) {
      log.error(e.getMessage(), e);
    }
  }



  @Override
  public void deleteOrphainedRoom() {
    Connection connection = null;
    Statement statement = null;
    String sql = persistence.getSQL(Persistence.KEY_DELETE_ALL_ROOMS_WITHOUT_EXISTING_OWNER);
    if (log.isDebugEnabled()) log.debug(sql);
    try {
      connection = pool.getConnection();
      statement = connection.createStatement();
      statement.executeUpdate(sql);
    }
    catch (SQLException e) {
      log.warn(e.getMessage());
    }
    finally {
      try {
        if (statement != null) statement.close();
      }
      catch (SQLException e) {}
      try {
        if (connection != null) connection.close();
      }
      catch (SQLException e) {}
    }

  }



  /**
   * Lösche alle Nachrichten aus jedem Chatraum.
   */
  @Override
  public void deleteRecord() {
    String sql = persistence.getSQL(Persistence.KEY_DELETE_RECORDS);
    if (log.isDebugEnabled()) log.debug(sql);
    try(
      Connection connection = pool.getConnection();
      Statement statement = connection.createStatement()
    ) {
      statement.executeUpdate(sql);
    }
    catch (SQLException e) {
      log.error(e.getMessage(), e);
    }
  }



  @Override
  public void deleteScreencast() {
    String sql = persistence.getSQL(Persistence.KEY_DELETE_SCREENCAST);
    logSQLKey(Persistence.KEY_DELETE_SCREENCAST, sql);
    try(
      Connection connection = pool.getConnection();
      Statement statement = connection.createStatement()
    ) {
      statement.executeUpdate(sql);
    }
    catch (SQLException e) {
      log.error(e.getMessage(), e);
    }
  }



  @Override
  public void deleteTemprooms() {
    Connection connection = null;
    Statement statement = null;
    persistence = Persistence.getInstance();
    String sql = persistence.getSQL(Persistence.KEY_DELETE_TEMPROOMS);
    logSQLKey(Persistence.KEY_DELETE_TEMPROOMS, sql);
    try {
      connection = pool.getConnection();
      statement = connection.createStatement();
      statement.executeUpdate(sql);
    }
    catch (SQLException e) {
      log.warn(e.getMessage(), e.getCause());
    }
    finally {
      try {
        if (statement != null) statement.close();
      }
      catch (SQLException e) {}
      try {
        if (connection != null) connection.close();
      }
      catch (SQLException e) {}
    }
  }



  @Override
  public void deleteTransferLib() {
    String sql = persistence.getSQL(Persistence.KEY_CLEAR_TRANSFERLIB);
    logSQLKey(Persistence.KEY_CLEAR_TRANSFERLIB, sql);
    try(
      Connection connection = pool.getConnection();
      Statement statement = connection.createStatement()
    ) {
      statement.executeUpdate(sql);
    }
    catch (SQLException e) {
      log.error(e.getMessage(), e.getCause());
    }
  }



  @Override
  public void deleteUserXChat() {
    Connection connection = null;
    PreparedStatement statement = null;

    try {
      connection = pool.getConnection();
      String sql = persistence.getSQL(Persistence.KEY_DELETE_USER_CHATS);
      if (log.isDebugEnabled()) log.debug(sql);
      statement = connection.prepareStatement(sql);
      statement.executeUpdate();
    }
    catch (SQLException e) {
      log.warn(e.getMessage(), e.getCause());
    }
    finally {
      try {
        if (statement != null) statement.close();
      }
      catch (SQLException e) {}
      try {
        if (connection != null) connection.close();
      }
      catch (SQLException e) {}
    }
  }



  /**
   * Die Datenbankeigenschaften werden aus der Konfigurationsdatei gelesen und in
   * {@literal org.apache.tomcat.jdbc.pool.PoolProperties} gespeichert.
   * 
   * @return die Pool-Eigenschaften die
   * @throws SAXException
   * @throws JAXBException
   */
  @Override
  public PoolProperties getDefaultPoolProperties() throws SAXException, JAXBException {
    PoolProperties poolprops = null;
//    try {
    Path etcdir = environment.getEtcdir();
    Path configfile = Paths.get(etcdir.toString(), Environment.CONFIG_FILE);
    ContextFile contextfile = new ContextFile(configfile);
    Context root = contextfile.getContext();
    Resource resource = root.getResource();
    poolprops = new PoolProperties();
    // poolprops.setDefaultAutoCommit(true);
    if (resource.getDriverClassName() != null) poolprops.setDriverClassName(resource.getDriverClassName());
    if (resource.getUrl() != null) poolprops.setUrl(resource.getUrl());
    if (resource.isJmxEnabled() != null) poolprops.setJmxEnabled(resource.isJmxEnabled());
    if (resource.getUsername() != null) poolprops.setUsername(resource.getUsername());
    if (resource.getPassword() != null) poolprops.setPassword(resource.getPassword());
    if (resource.getValidationQuery() != null) poolprops.setValidationQuery(resource.getValidationQuery());
    return poolprops;
  }



  @Override
  public final String getSmtpHost() throws NamingException {
    InitialContext initContext = new InitialContext();
    log.info("DatabaseServciceImpl hat kein java:comp/env/mail.smtp.host");
//    return (String) initContext.lookup("java:comp/env/mail.smtp.host");
    return "";
  }



  @Override
  public final String getSmtpUser() throws NamingException {
    InitialContext initContext = new InitialContext();
//    return (String) initContext.lookup("java:comp/env/mail.smtp.user");
    log.info("DatabaseServciceImpl hat kein java:comp/env/mail.smtp.user");
    return "";
  }



  @Override
  public void init() {

  }



  /**
   * Diese Methode ist nur für Testzwecke. Die Mailserverdaten werden
   * initialisiert.
   * 
   * @param config
   *               die Mailserverdaten
   * @throws SQLException
   */
  public void insertMailserver(Config config) throws SQLException {
    String sql = persistence.getSQL(Persistence.KEY_INSERT_CONFIG);
    logSQLKey(Persistence.KEY_INSERT_CONFIG, sql);
    try(
      Connection connection = pool.getConnection();
      PreparedStatement statement = connection.prepareStatement(sql)
    ) {

      statement.setString(1, config.getDomain());
      statement.setBoolean(2, config.getIsPublic());
      statement.setString(3, config.getMailSmtpUser());
      statement.setString(4, config.getMailSmtpHost());
      statement.setInt(5, config.getMailSmtpPort());
      statement.setInt(6, config.getFlipToForum());
      statement.setInt(7, config.getHours());
      statement.executeUpdate();
    }
  }



  @Override
  public void shutdown() {
    // TODO Auto-generated method stub

  }



  @Override
  public Integer getSmtpPort() throws NamingException {
    return 587;
  }

}
