#!/usr/bin/env bash
# Parameters: <db_host> <db_name> <db_username> <db_password> <pem_cert> <revocation_reason>

#
# TODOS:
#
# - Look for (or develop) something better integrated with EJBCA, e.g. an EJBCA CLI command. Related notes at the top of 'TODOS-EjbcaCt'.
# - If later this script is to be allowed to be executed remotely (not from EJBCA CA as required now), create a dedicated DB user per person with the minimum required DB privileges.
# - Check all commands for their exit status and only continue if the command executed succesfully.
#

MYSQL_BIN=/usr/bin/mysql
EJBCA_CLI=ejbca.sh

DEVMODE=0

# EJBCA CA DB parameters (not EJBCA OCSP).
caDbHost=$1
caDbName=$2
caDbUsername=$3
caDbPassword=$4
pemCertFile=$5
revocationReason=$6

fingerprint=$(openssl x509 -in $pemCertFile -outform DER | openssl dgst -sha1 | sed -e 's/(stdin)= //')
serialHex=$(openssl x509 -in $pemCertFile -serial -noout | sed -e 's/serial=//')
serialNumber=$(echo $serialHex | awk '{print "ibase=16; " $1}' | bc)
# All the following -nameopt options are a subset of the 'default'. See 'man x509'.
subjectDN=$(openssl x509 -in $pemCertFile -noout -subject -nameopt esc_2253,esc_ctrl,esc_msb,utf8,dump_nostr,dump_der,use_quote,sep_comma_plus,sname  | sed -e 's/subject=//')
issuerDN=$(openssl x509 -in $pemCertFile -noout -issuer -nameopt esc_2253,esc_ctrl,esc_msb,utf8,dump_nostr,dump_der,use_quote,sep_comma_plus,sname  | sed -e 's/issuer=//')
base64Cert=$(openssl x509 -in $pemCertFile -outform DER | base64 -w 64)
expireDateStr=$(openssl x509 -in $pemCertFile -enddate -noout | sed -e 's/notAfter=//')
expireDate=$(($(date --date "$expireDateStr" +"%s%N" )/1000000))

if [ $DEVMODE -eq 1 ]
then
   $MYSQL_BIN -h $caDbHost -u $caDbUsername --password=$caDbPassword -e "delete from CertificateData where fingerprint='$fingerprint';" $caDbName
fi

# TODO validate that the issuerDN corresponds to a CA being managed by this EJBCA and maybe get the 'cAFingerprint' in the process.

echo "Trying to insert (pre)certificate into DB..."

existsRealCert=$($MYSQL_BIN -h $caDbHost -u $caDbUsername --password=$caDbPassword -N -s -e "select count(*) from CertificateData where issuerDN='$issuerDN' and serialNumber = $serialNumber;" $caDbName)
if [ "$existsRealCert" = "0" ]
then
    # TODO expect only one result (use the username if the query returns only one record1) and maybe try to match in this step to the right issuing CA, that maybe the same DN could be used for different users with different CAs... is it possible in EJBCA?.
    matchingUsername=$($MYSQL_BIN -h $caDbHost -u $caDbUsername --password=$caDbPassword -N -s -e "select username from ejbca.UserData where subjectDN='$subjectDN';" $caDbName)
    if [ -n "$matchingUsername" ]
    then
	# TODO try to insert all the field inserted in 'org.ejbca.va.publisher.EnterpriseValidationAuthorityPublisher.insertCertificateSQL': base64Cert,subjectDN,issuerDN,cAFingerprint,serialNumber,status,type,username,expireDate,revocationDate,revocationReason,tag,certificateProfileId,updateTime,subjectKeyId,fingerprint,rowVersion. The specially important are the ones that could affect CRL generation or OCSP processing, but consider usages for other fields from EJBCA Admin GUI or other places. Maybe look the source code for usages for the remaining fields.
	if $MYSQL_BIN -h $caDbHost -u $caDbUsername --password=$caDbPassword -e "insert into CertificateData \
(base64Cert,subjectDN,issuerDN,serialNumber,username,expireDate,revocationReason,fingerprint) values \
('$base64Cert', '$subjectDN', '$issuerDN', '$serialNumber', '$matchingUsername', $expireDate, -1, '$fingerprint');" $caDbName ; then
	    echo "(Pre)certificate succesfully inserted."

	    echo "Revoking (pre)certificate in EJBCA..."
	    # NOTE that this will trigger publishing.
	    # TODO determine if EJBCA CLI can be executed remotely.
	    sudo $EJBCA_CLI ra revokecert --dn \"$issuerDN\" -s $serialHex -r $revocationReason
	else
	    # TODO manage all errors connecting to MySQL (there are some previous statements) in an uniform way.
	    echo "Error!."
	fi

    else
	# The prefix for every certificate which has been MANually REVoked and for which a matching username couldn't be found: USERNAME_PREFIX=manually_revoked_precert_*
	# TODO default to use $USERNAME_PREFIX for creating a new username... but what about republishing certs to external OCSP?, it seems that 'ejbca.sh ra revokecert' wouldn't trigger publishing for these certificates by default. Debug EJBCA publishing code to determine what to do here.
	echo "No matching username in EJBCA DB. Please ask for support to analyze EJBCA database manually."
    fi

else
    echo "(Pre)certificate exists in EJBCA DB. Revoke it from Admin GUI."
fi