package db

import "database/sql"
import "strings"
import "time"

import "codit/internal/models"
import "codit/internal/util"

func (s *Store) ListPKICAs() ([]models.PKICA, error) {
	var rows *sql.Rows
	var err error
	var items []models.PKICA
	var item models.PKICA
	rows, err = s.DB.Query(`SELECT c.public_id, c.name, COALESCE(p.public_id, ''), c.is_root, c.cert_pem, c.key_pem, c.serial_counter, c.status, c.created_at, c.updated_at
		FROM pki_cas c
		LEFT JOIN pki_cas p ON p.id = c.parent_ca_id
		ORDER BY c.name`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	for rows.Next() {
		err = rows.Scan(&item.ID, &item.Name, &item.ParentCAID, &item.IsRoot, &item.CertPEM, &item.KeyPEM, &item.SerialCounter, &item.Status, &item.CreatedAt, &item.UpdatedAt)
		if err != nil {
			return nil, err
		}
		items = append(items, item)
	}
	err = rows.Err()
	if err != nil {
		return nil, err
	}
	return items, nil
}

func (s *Store) GetPKICA(id string) (models.PKICA, error) {
	var row *sql.Row
	var item models.PKICA
	var err error
	row = s.DB.QueryRow(`SELECT c.public_id, c.name, COALESCE(p.public_id, ''), c.is_root, c.cert_pem, c.key_pem, c.serial_counter, c.status, c.created_at, c.updated_at
		FROM pki_cas c
		LEFT JOIN pki_cas p ON p.id = c.parent_ca_id
		WHERE c.public_id = ?`, id)
	err = row.Scan(&item.ID, &item.Name, &item.ParentCAID, &item.IsRoot, &item.CertPEM, &item.KeyPEM, &item.SerialCounter, &item.Status, &item.CreatedAt, &item.UpdatedAt)
	if err != nil {
		return item, err
	}
	return item, nil
}

func (s *Store) UpdatePKICAName(id string, name string) error {
	var err error
	_, err = s.DB.Exec(`UPDATE pki_cas SET name = ?, updated_at = ? WHERE public_id = ?`, name, time.Now().UTC().Unix(), id)
	return err
}

func (s *Store) CreatePKICA(item models.PKICA) (models.PKICA, error) {
	var id string
	var now int64
	var err error
	if item.ID == "" {
		id, err = util.NewID()
		if err != nil {
			return item, err
		}
		item.ID = id
	}
	if item.SerialCounter <= 0 {
		item.SerialCounter = 1
	}
	if item.Status == "" {
		item.Status = "active"
	}
	now = time.Now().UTC().Unix()
	item.CreatedAt = now
	item.UpdatedAt = now
	_, err = s.DB.Exec(`INSERT INTO pki_cas (public_id, name, parent_ca_id, is_root, cert_pem, key_pem, serial_counter, status, created_at, updated_at)
		VALUES (?, ?, CASE WHEN ? = '' THEN NULL ELSE (SELECT id FROM pki_cas WHERE public_id = ?) END, ?, ?, ?, ?, ?, ?, ?)`,
		item.ID, item.Name, item.ParentCAID, item.ParentCAID, item.IsRoot, item.CertPEM, item.KeyPEM, item.SerialCounter, item.Status, item.CreatedAt, item.UpdatedAt)
	if err != nil {
		return item, err
	}
	return item, nil
}

func (s *Store) CountPKICAChildren(id string) (int, error) {
	var row *sql.Row
	var count int
	var err error
	row = s.DB.QueryRow(`SELECT COUNT(*)
		FROM pki_cas c
		JOIN pki_cas p ON p.id = c.parent_ca_id
		WHERE p.public_id = ?`, id)
	err = row.Scan(&count)
	if err != nil {
		return 0, err
	}
	return count, nil
}

func (s *Store) CountPKICertsByCA(id string) (int, error) {
	var row *sql.Row
	var count int
	var err error
	row = s.DB.QueryRow(`SELECT COUNT(*)
		FROM pki_certs c
		JOIN pki_cas ca ON ca.id = c.ca_id
		WHERE ca.public_id = ?`, id)
	err = row.Scan(&count)
	if err != nil {
		return 0, err
	}
	return count, nil
}

func (s *Store) DeletePKICA(id string) error {
	var err error
	_, err = s.DB.Exec(`DELETE FROM pki_cas WHERE public_id = ?`, id)
	return err
}

func (s *Store) DeletePKICASubtree(id string) error {
	var tx *sql.Tx
	var rows *sql.Rows
	var err error
	var itemID string
	var parentID string
	var parentByID map[string]string
	var pending []string
	var current string
	var i int
	var j int
	var target string
	var toDelete []string
	var contains bool
	parentByID = map[string]string{}
	tx, err = s.DB.Begin()
	if err != nil {
		return err
	}
	rows, err = tx.Query(`SELECT c.public_id, COALESCE(p.public_id, '')
		FROM pki_cas c
		LEFT JOIN pki_cas p ON p.id = c.parent_ca_id`)
	if err != nil {
		_ = tx.Rollback()
		return err
	}
	defer rows.Close()
	for rows.Next() {
		err = rows.Scan(&itemID, &parentID)
		if err != nil {
			_ = tx.Rollback()
			return err
		}
		parentByID[itemID] = parentID
	}
	err = rows.Err()
	if err != nil {
		_ = tx.Rollback()
		return err
	}
	pending = append(pending, id)
	for len(pending) > 0 {
		current = pending[0]
		pending = pending[1:]
		contains = false
		for i = 0; i < len(toDelete); i++ {
			if toDelete[i] == current {
				contains = true
				break
			}
		}
		if !contains {
			toDelete = append(toDelete, current)
		}
		for target = range parentByID {
			if parentByID[target] == current {
				pending = append(pending, target)
			}
		}
	}
	for i = len(toDelete) - 1; i >= 0; i-- {
		j = i
		_ = j
		_, err = tx.Exec(`DELETE FROM pki_cas WHERE public_id = ?`, toDelete[i])
		if err != nil {
			_ = tx.Rollback()
			return err
		}
	}
	err = tx.Commit()
	if err != nil {
		return err
	}
	return nil
}

func (s *Store) NextPKICASerial(caID string) (int64, error) {
	var tx *sql.Tx
	var err error
	var row *sql.Row
	var serial int64
	tx, err = s.DB.Begin()
	if err != nil {
		return 0, err
	}
	row = tx.QueryRow(`SELECT serial_counter FROM pki_cas WHERE public_id = ?`, caID)
	err = row.Scan(&serial)
	if err != nil {
		_ = tx.Rollback()
		return 0, err
	}
	_, err = tx.Exec(`UPDATE pki_cas SET serial_counter = ?, updated_at = ? WHERE public_id = ?`, serial+1, time.Now().UTC().Unix(), caID)
	if err != nil {
		_ = tx.Rollback()
		return 0, err
	}
	err = tx.Commit()
	if err != nil {
		return 0, err
	}
	return serial, nil
}

func (s *Store) ListPKICerts(caID string) ([]models.PKICert, error) {
	var rows *sql.Rows
	var err error
	var items []models.PKICert
	var item models.PKICert
	if caID == "" {
		rows, err = s.DB.Query(`SELECT c.public_id, COALESCE(ca.public_id, ''), c.serial_hex, c.common_name, c.san_dns, c.san_ips, c.is_ca, c.cert_pem, c.key_pem, c.not_before, c.not_after, c.status, c.revoked_at, c.revocation_reason, c.created_at
			FROM pki_certs c
			LEFT JOIN pki_cas ca ON ca.id = c.ca_id
			ORDER BY c.created_at DESC`)
	} else if caID == "standalone" {
		rows, err = s.DB.Query(`SELECT c.public_id, COALESCE(ca.public_id, ''), c.serial_hex, c.common_name, c.san_dns, c.san_ips, c.is_ca, c.cert_pem, c.key_pem, c.not_before, c.not_after, c.status, c.revoked_at, c.revocation_reason, c.created_at
			FROM pki_certs c
			LEFT JOIN pki_cas ca ON ca.id = c.ca_id
			WHERE c.ca_id IS NULL
			ORDER BY c.created_at DESC`)
	} else {
		rows, err = s.DB.Query(`SELECT c.public_id, COALESCE(ca.public_id, ''), c.serial_hex, c.common_name, c.san_dns, c.san_ips, c.is_ca, c.cert_pem, c.key_pem, c.not_before, c.not_after, c.status, c.revoked_at, c.revocation_reason, c.created_at
			FROM pki_certs c
			LEFT JOIN pki_cas ca ON ca.id = c.ca_id
			WHERE c.ca_id = (SELECT id FROM pki_cas WHERE public_id = ?)
			ORDER BY c.created_at DESC`, caID)
	}
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	for rows.Next() {
		err = rows.Scan(&item.ID, &item.CAID, &item.SerialHex, &item.CommonName, &item.SANDNS, &item.SANIPs, &item.IsCA, &item.CertPEM, &item.KeyPEM, &item.NotBefore, &item.NotAfter, &item.Status, &item.RevokedAt, &item.RevocationReason, &item.CreatedAt)
		if err != nil {
			return nil, err
		}
		items = append(items, item)
	}
	err = rows.Err()
	if err != nil {
		return nil, err
	}
	return items, nil
}

func (s *Store) GetPKICert(id string) (models.PKICert, error) {
	var row *sql.Row
	var item models.PKICert
	var err error
	row = s.DB.QueryRow(`SELECT c.public_id, COALESCE(ca.public_id, ''), c.serial_hex, c.common_name, c.san_dns, c.san_ips, c.is_ca, c.cert_pem, c.key_pem, c.not_before, c.not_after, c.status, c.revoked_at, c.revocation_reason, c.created_at
		FROM pki_certs c
		LEFT JOIN pki_cas ca ON ca.id = c.ca_id
		WHERE c.public_id = ?`, id)
	err = row.Scan(&item.ID, &item.CAID, &item.SerialHex, &item.CommonName, &item.SANDNS, &item.SANIPs, &item.IsCA, &item.CertPEM, &item.KeyPEM, &item.NotBefore, &item.NotAfter, &item.Status, &item.RevokedAt, &item.RevocationReason, &item.CreatedAt)
	if err != nil {
		return item, err
	}
	return item, nil
}

func (s *Store) CreatePKICert(item models.PKICert) (models.PKICert, error) {
	var id string
	var now int64
	var err error
	if item.ID == "" {
		id, err = util.NewID()
		if err != nil {
			return item, err
		}
		item.ID = id
	}
	if item.Status == "" {
		item.Status = "active"
	}
	now = time.Now().UTC().Unix()
	item.CreatedAt = now
	item.CAID = strings.TrimSpace(item.CAID)
	_, err = s.DB.Exec(`INSERT INTO pki_certs (public_id, ca_id, serial_hex, common_name, san_dns, san_ips, is_ca, cert_pem, key_pem, not_before, not_after, status, revoked_at, revocation_reason, created_at)
		VALUES (?, CASE WHEN ? = '' THEN NULL ELSE (SELECT id FROM pki_cas WHERE public_id = ?) END, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
		item.ID, item.CAID, item.CAID, item.SerialHex, item.CommonName, item.SANDNS, item.SANIPs, item.IsCA, item.CertPEM, item.KeyPEM, item.NotBefore, item.NotAfter, item.Status, item.RevokedAt, item.RevocationReason, item.CreatedAt)
	if err != nil {
		return item, err
	}
	return item, nil
}

func (s *Store) RevokePKICert(id string, reason string) error {
	var err error
	_, err = s.DB.Exec(`UPDATE pki_certs SET status = 'revoked', revoked_at = ?, revocation_reason = ? WHERE public_id = ?`, time.Now().UTC().Unix(), reason, id)
	return err
}

func (s *Store) DeletePKICert(id string) error {
	var err error
	_, err = s.DB.Exec(`DELETE FROM pki_certs WHERE public_id = ?`, id)
	return err
}

func (s *Store) CountTLSServerCertReferences(certID string) (int, int, error) {
	var row *sql.Row
	var appCount int
	var listenerCount int
	var err error
	row = s.DB.QueryRow(`SELECT COUNT(*) FROM app_settings WHERE key = 'tls.pki_server_cert_id' AND value = ?`, certID)
	err = row.Scan(&appCount)
	if err != nil {
		return 0, 0, err
	}
	row = s.DB.QueryRow(`SELECT COUNT(*) FROM tls_listeners WHERE tls_pki_server_cert_id = ?`, certID)
	err = row.Scan(&listenerCount)
	if err != nil {
		return 0, 0, err
	}
	return appCount, listenerCount, nil
}

func (s *Store) CountTLSClientCAReferences(caID string) (int, int, error) {
	var row *sql.Row
	var appCount int
	var listenerCount int
	var err error
	row = s.DB.QueryRow(`SELECT COUNT(*) FROM app_settings WHERE key = 'tls.pki_client_ca_id' AND value = ?`, caID)
	err = row.Scan(&appCount)
	if err != nil {
		return 0, 0, err
	}
	row = s.DB.QueryRow(`SELECT COUNT(*) FROM tls_listeners WHERE tls_pki_client_ca_id = ?`, caID)
	err = row.Scan(&listenerCount)
	if err != nil {
		return 0, 0, err
	}
	return appCount, listenerCount, nil
}