/*
 *
 * Copyright (c) 2013 - 2020 Lijun Liao
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 * http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

package org.xipki.ca.mgmt.db.port;

import java.io.IOException;
import java.io.OutputStream;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;
import java.util.concurrent.atomic.AtomicBoolean;

import org.xipki.datasource.DataAccessException;
import org.xipki.datasource.DataSourceWrapper;
import org.xipki.util.InvalidConfException;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.serializer.SerializerFeature;

/**
 * Database exporter of CA configuration.
 *
 * @author Lijun Liao
 * @since 2.0.0
 */

class CaconfDbExporter extends DbPorter {

  CaconfDbExporter(DataSourceWrapper datasource, String destDir, AtomicBoolean stopMe)
      throws DataAccessException {
    super(datasource, destDir, stopMe);
  }

  public void export() throws Exception {
    CaCertstore.Caconf caconf = new CaCertstore.Caconf();
    caconf.setVersion(VERSION);

    System.out.println("exporting CA configuration from database");

    exportSigner(caconf);
    exportRequestor(caconf);
    exportUser(caconf);
    exportPublisher(caconf);
    exportCa(caconf);
    exportProfile(caconf);
    exportCaalias(caconf);
    exportCaHasRequestor(caconf);
    exportCaHasUser(caconf);
    exportCaHasPublisher(caconf);
    exportCaHasProfile(caconf);

    caconf.validate();
    try (OutputStream os = Files.newOutputStream(Paths.get(baseDir, FILENAME_CA_CONFIGURATION))) {
      JSON.writeJSONString(os, caconf, SerializerFeature.PrettyFormat);
    }
    System.out.println(" exported CA configuration from database");
  } // method export

  private void exportCaalias(CaCertstore.Caconf caconf)
      throws DataAccessException, InvalidConfException {
    System.out.println("exporting table CAALIAS");
    List<CaCertstore.Caalias> caaliases = new LinkedList<>();
    final String sql = "SELECT NAME,CA_ID FROM CAALIAS";

    Statement stmt = null;
    ResultSet rs = null;
    try {
      stmt = createStatement();
      rs = stmt.executeQuery(sql);

      while (rs.next()) {
        CaCertstore.Caalias caalias = new CaCertstore.Caalias();
        caalias.setName(rs.getString("NAME"));
        caalias.setCaId(rs.getInt("CA_ID"));

        caalias.validate();
        caaliases.add(caalias);
      }
    } catch (SQLException ex) {
      throw translate(sql, ex);
    } finally {
      releaseResources(stmt, rs);
    }

    caconf.setCaaliases(caaliases);
    System.out.println(" exported table CAALIAS");
  } // method exportCaalias

  private void exportRequestor(CaCertstore.Caconf caconf)
      throws DataAccessException, IOException, InvalidConfException {
    System.out.println("exporting table REQUESTOR");
    List<CaCertstore.IdNameTypeConf> requestors = new LinkedList<>();
    final String sql = "SELECT ID,NAME,TYPE,CONF FROM REQUESTOR";

    Statement stmt = null;
    ResultSet rs = null;
    try {
      stmt = createStatement();
      rs = stmt.executeQuery(sql);

      while (rs.next()) {
        String name = rs.getString("NAME");

        CaCertstore.IdNameTypeConf requestor = new CaCertstore.IdNameTypeConf();
        requestor.setId(rs.getInt("ID"));
        requestor.setName(name);
        requestor.setType(rs.getString("TYPE"));
        requestor.setConf(buildFileOrValue(
            rs.getString("CONF"), "ca-conf/cert-requestor-" + name + ".conf"));

        requestor.validate();
        requestors.add(requestor);
      }
    } catch (SQLException ex) {
      throw translate(sql, ex);
    } finally {
      releaseResources(stmt, rs);
    }

    caconf.setRequestors(requestors);
    System.out.println(" exported table REQUESTOR");
  } // method exportRequestor

  private void exportUser(CaCertstore.Caconf caconf)
      throws DataAccessException, IOException, InvalidConfException {
    System.out.println("exporting table TUSER");
    List<CaCertstore.User> users = new LinkedList<>();
    final String sql = "SELECT ID,NAME,ACTIVE,PASSWORD FROM TUSER";

    Statement stmt = null;
    ResultSet rs = null;
    try {
      stmt = createStatement();
      rs = stmt.executeQuery(sql);

      while (rs.next()) {
        CaCertstore.User user = new CaCertstore.User();
        user.setId(rs.getInt("ID"));
        user.setName(rs.getString("NAME"));
        user.setActive(rs.getInt("ACTIVE"));
        user.setPassword(rs.getString("PASSWORD"));

        user.validate();
        users.add(user);
      }
    } catch (SQLException ex) {
      throw translate(sql, ex);
    } finally {
      releaseResources(stmt, rs);
    }

    caconf.setUsers(users);
    System.out.println(" exported table TUSER");
  } // method exportUser

  private void exportSigner(CaCertstore.Caconf caconf)
      throws DataAccessException, IOException, InvalidConfException {
    System.out.println("exporting table SIGNER");
    List<CaCertstore.Signer> signers = new LinkedList<>();
    final String sql = "SELECT NAME,TYPE,CONF,CERT FROM SIGNER";

    Statement stmt = null;
    ResultSet rs = null;
    try {
      stmt = createStatement();
      rs = stmt.executeQuery(sql);

      while (rs.next()) {
        String name = rs.getString("NAME");

        CaCertstore.Signer signer = new CaCertstore.Signer();
        signer.setName(name);
        signer.setType(rs.getString("TYPE"));
        signer.setConf(buildFileOrValue(rs.getString("CONF"), "ca-conf/conf-signer-" + name));
        signer.setCert(buildFileOrBase64Binary(
            rs.getString("CERT"), "ca-conf/cert-signer-" + name + ".der"));

        signer.validate();
        signers.add(signer);
      }
    } catch (SQLException ex) {
      throw translate(sql, ex);
    } finally {
      releaseResources(stmt, rs);
    }

    caconf.setSigners(signers);
    System.out.println(" exported table SIGNER");
  } // method exportSigner

  private void exportPublisher(CaCertstore.Caconf caconf)
      throws DataAccessException, IOException, InvalidConfException {
    System.out.println("exporting table PUBLISHER");
    List<CaCertstore.IdNameTypeConf> publishers = new LinkedList<>();
    final String sql = "SELECT ID,NAME,TYPE,CONF FROM PUBLISHER";

    Statement stmt = null;
    ResultSet rs = null;
    try {
      stmt = createStatement();
      rs = stmt.executeQuery(sql);

      while (rs.next()) {
        String name = rs.getString("NAME");

        CaCertstore.IdNameTypeConf publisher = new CaCertstore.IdNameTypeConf();
        publisher.setId(rs.getInt("ID"));
        publisher.setName(name);
        publisher.setType(rs.getString("TYPE"));
        publisher.setConf(buildFileOrValue(rs.getString("CONF"), "ca-conf/conf-publisher-" + name));

        publisher.validate();
        publishers.add(publisher);
      }
    } catch (SQLException ex) {
      throw translate(sql, ex);
    } finally {
      releaseResources(stmt, rs);
    }

    caconf.setPublishers(publishers);
    System.out.println(" exported table PUBLISHER");
  } // method exportPublisher

  private void exportProfile(CaCertstore.Caconf caconf)
      throws DataAccessException, IOException, InvalidConfException {
    System.out.println("exporting table PROFILE");
    List<CaCertstore.IdNameTypeConf> profiles = new LinkedList<>();
    final String sql = "SELECT ID,NAME,TYPE,CONF FROM PROFILE";

    Statement stmt = null;
    ResultSet rs = null;
    try {
      stmt = createStatement();
      rs = stmt.executeQuery(sql);

      while (rs.next()) {
        String name = rs.getString("NAME");

        CaCertstore.IdNameTypeConf profile = new CaCertstore.IdNameTypeConf();
        profile.setId(rs.getInt("ID"));
        profile.setName(name);
        profile.setType(rs.getString("TYPE"));
        profile.setConf(buildFileOrValue(rs.getString("CONF"), "ca-conf/certprofile-" + name));

        profile.validate();
        profiles.add(profile);
      }
    } catch (SQLException ex) {
      throw translate(sql, ex);
    } finally {
      releaseResources(stmt, rs);
    }

    caconf.setProfiles(profiles);
    System.out.println(" exported table PROFILE");
  } // method exportProfile

  private void exportCa(CaCertstore.Caconf caconf)
      throws DataAccessException, IOException, InvalidConfException {
    System.out.println("exporting table CA");
    List<CaCertstore.Ca> cas = new LinkedList<>();

    final String sql = "SELECT ID,NAME,SN_SIZE,NEXT_CRLNO,STATUS,CA_URIS," // 6
        + "MAX_VALIDITY,CERT,CERTCHAIN,SIGNER_TYPE,CRL_SIGNER_NAME," // 5
        + "CMP_RESPONDER_NAME,SCEP_RESPONDER_NAME,CRL_CONTROL,CMP_CONTROL,SCEP_CONTROL," // 5
        + "CTLOG_CONTROL,PROTOCOL_SUPPORT,SAVE_REQ,PERMISSION," // 6
        + "NUM_CRLS,EXPIRATION_PERIOD,KEEP_EXPIRED_CERT_DAYS,VALIDITY_MODE,EXTRA_CONTROL," // 5
        + "SIGNER_CONF,REV_INFO,DHPOC_CONTROL,REVOKE_SUSPENDED_CONTROL FROM CA"; // 4

    Statement stmt = null;
    ResultSet rs = null;
    try {
      stmt = createStatement();
      rs = stmt.executeQuery(sql);

      while (rs.next()) {
        CaCertstore.Ca ca = new CaCertstore.Ca();
        ca.setId(rs.getInt("ID"));
        String name = rs.getString("NAME");
        ca.setName(name);
        ca.setSnSize(rs.getInt("SN_SIZE"));
        ca.setNextCrlNo(rs.getLong("NEXT_CRLNO"));
        ca.setStatus(rs.getString("STATUS"));
        ca.setCaUris(rs.getString("CA_URIS"));
        ca.setMaxValidity(rs.getString("MAX_VALIDITY"));
        ca.setCert(buildFileOrBase64Binary(
            rs.getString("CERT"), "ca-conf/cert-ca-" + name + ".der"));
        ca.setCertchain(buildFileOrValue(
            rs.getString("CERTCHAIN"), "ca-conf/certchain-ca-" + name + ".pem"));
        ca.setSignerType(rs.getString("SIGNER_TYPE"));
        ca.setSignerConf(buildFileOrValue(
            rs.getString("SIGNER_CONF"), "ca-conf/signerconf-ca-" + name));
        ca.setCmpResponderName(rs.getString("CMP_RESPONDER_NAME"));
        ca.setScepResponderName(rs.getString("SCEP_RESPONDER_NAME"));
        ca.setCrlSignerName(rs.getString("CRL_SIGNER_NAME"));
        ca.setCmpControl(rs.getString("CMP_CONTROL"));
        ca.setScepControl(rs.getString("SCEP_CONTROL"));
        ca.setCrlControl(rs.getString("CRL_CONTROL"));
        ca.setCtlogControl(rs.getString("CTLOG_CONTROL"));
        ca.setProtocolSupport(rs.getString("PROTOCOL_SUPPORT"));
        ca.setSaveReq(rs.getInt("SAVE_REQ"));
        ca.setPermission(rs.getInt("PERMISSION"));
        ca.setExpirationPeriod(rs.getInt("EXPIRATION_PERIOD"));
        ca.setKeepExpiredCertDays(rs.getInt("KEEP_EXPIRED_CERT_DAYS"));
        ca.setValidityMode(rs.getString("VALIDITY_MODE"));
        ca.setExtraControl(rs.getString("EXTRA_CONTROL"));
        ca.setNumCrls(rs.getInt("NUM_CRLS"));
        ca.setRevInfo(rs.getString("REV_INFO"));
        ca.setDhpocControl(rs.getString("DHPOC_CONTROL"));
        ca.setRevokeSuspendedControl(rs.getString("REVOKE_SUSPENDED_CONTROL"));

        ca.validate();
        cas.add(ca);
      }
    } catch (SQLException ex) {
      throw translate(sql, ex);
    } finally {
      releaseResources(stmt, rs);
    }

    caconf.setCas(cas);
    System.out.println(" exported table CA");
  } // method exportCa

  private void exportCaHasRequestor(CaCertstore.Caconf caconf)
      throws DataAccessException, InvalidConfException {
    System.out.println("exporting table CA_HAS_REQUESTOR");
    List<CaCertstore.CaHasRequestor> caHasRequestors = new LinkedList<>();
    final String sql = "SELECT CA_ID,REQUESTOR_ID,RA,PERMISSION,PROFILES FROM CA_HAS_REQUESTOR";

    Statement stmt = null;
    ResultSet rs = null;
    try {
      stmt = createStatement();
      rs = stmt.executeQuery(sql);

      while (rs.next()) {
        CaCertstore.CaHasRequestor caHasRequestor = new CaCertstore.CaHasRequestor();
        caHasRequestor.setCaId(rs.getInt("CA_ID"));
        caHasRequestor.setRequestorId(rs.getInt("REQUESTOR_ID"));
        caHasRequestor.setRa(rs.getInt("RA"));
        caHasRequestor.setPermission(rs.getInt("PERMISSION"));
        caHasRequestor.setProfiles(rs.getString("PROFILES"));

        caHasRequestor.validate();
        caHasRequestors.add(caHasRequestor);
      }
    } catch (SQLException ex) {
      throw translate(sql, ex);
    } finally {
      releaseResources(stmt, rs);
    }

    caconf.setCaHasRequestors(caHasRequestors);
    System.out.println(" exported table CA_HAS_REQUESTOR");
  } // method exportCaHasRequestor

  private void exportCaHasUser(CaCertstore.Caconf caconf)
      throws DataAccessException, InvalidConfException {
    System.out.println("exporting table CA_HAS_USER");
    List<CaCertstore.CaHasUser> caHasUsers = new LinkedList<>();
    final String sql = "SELECT ID,CA_ID,USER_ID,PERMISSION,PROFILES FROM CA_HAS_USER";

    Statement stmt = null;
    ResultSet rs = null;
    try {
      stmt = createStatement();
      rs = stmt.executeQuery(sql);

      while (rs.next()) {
        CaCertstore.CaHasUser caHasUser = new CaCertstore.CaHasUser();
        caHasUser.setId(rs.getInt("ID"));
        caHasUser.setCaId(rs.getInt("CA_ID"));
        caHasUser.setUserId(rs.getInt("USER_ID"));
        caHasUser.setPermission(rs.getInt("PERMISSION"));
        caHasUser.setProfiles(rs.getString("PROFILES"));

        caHasUser.validate();
        caHasUsers.add(caHasUser);
      }
    } catch (SQLException ex) {
      throw translate(sql, ex);
    } finally {
      releaseResources(stmt, rs);
    }

    caconf.setCaHasUsers(caHasUsers);
    System.out.println(" exported table CA_HAS_USER");
  } // method exportCaHasRequestor

  private void exportCaHasPublisher(CaCertstore.Caconf caconf)
      throws DataAccessException, InvalidConfException {
    System.out.println("exporting table CA_HAS_PUBLISHER");
    List<CaCertstore.CaHasPublisher> caHasPublishers = new LinkedList<>();
    final String sql = "SELECT CA_ID,PUBLISHER_ID FROM CA_HAS_PUBLISHER";

    Statement stmt = null;
    ResultSet rs = null;
    try {
      stmt = createStatement();
      rs = stmt.executeQuery(sql);

      while (rs.next()) {
        CaCertstore.CaHasPublisher caHasPublisher = new CaCertstore.CaHasPublisher();
        caHasPublisher.setCaId(rs.getInt("CA_ID"));
        caHasPublisher.setPublisherId(rs.getInt("PUBLISHER_ID"));

        caHasPublisher.validate();
        caHasPublishers.add(caHasPublisher);
      }
    } catch (SQLException ex) {
      throw translate(sql, ex);
    } finally {
      releaseResources(stmt, rs);
    }

    caconf.setCaHasPublishers(caHasPublishers);
    System.out.println(" exported table CA_HAS_PUBLISHER");
  } // method exportCaHasPublisher

  private void exportCaHasProfile(CaCertstore.Caconf caconf)
      throws DataAccessException, InvalidConfException {
    System.out.println("exporting table CA_HAS_PROFILE");
    List<CaCertstore.CaHasProfile> caHasProfiles = new LinkedList<>();
    final String sql = "SELECT CA_ID,PROFILE_ID FROM CA_HAS_PROFILE";

    Statement stmt = null;
    ResultSet rs = null;
    try {
      stmt = createStatement();
      rs = stmt.executeQuery(sql);

      while (rs.next()) {
        CaCertstore.CaHasProfile caHasProfile = new CaCertstore.CaHasProfile();
        caHasProfile.setCaId(rs.getInt("CA_ID"));
        caHasProfile.setProfileId(rs.getInt("PROFILE_ID"));

        caHasProfile.validate();
        caHasProfiles.add(caHasProfile);
      }
    } catch (SQLException ex) {
      throw translate(sql, ex);
    } finally {
      releaseResources(stmt, rs);
    }

    caconf.setCaHasProfiles(caHasProfiles);
    System.out.println(" exported table CA_HAS_PROFILE");
  } // method exportCaHasProfile

}