package database

import (
	"fmt"

	"git.maurice.fr/thomas/mailout/pkg/config"
	"git.maurice.fr/thomas/mailout/pkg/models"
	"gorm.io/driver/postgres"
	"gorm.io/gorm"
)

const (
	createViewRawSQL = `
/*
    This view basically returns to you the most recent key for
    each known domain, so that when opendkim performs a where
    filter on the result only one result will be returned.
    Hence avoiding to make it confused and upset
*/
CREATE OR REPLACE VIEW signing_table AS
    SELECT
        k1.id,
        k1.domain_name,
        k1.created_at
    FROM
        dkimkeys AS k1
    INNER JOIN
        dkimkeys AS k2
            ON
                k1.created_at = (
                    SELECT
                        max(created_at)
                    FROM
                        dkimkeys
                    WHERE
                        domain_name = k1.domain_name
                        AND k1.active   = true
                )
            WHERE k1.domain_name = k2.domain_name
            AND k1.created_at    = k2.created_at;
`
)

func NewDB(cfg *config.Config) (*gorm.DB, error) {
	db, err := gorm.Open(
		postgres.Open(
			fmt.Sprintf(
				"host=%s port=%d user=%s password=%s dbname=%s sslmode=%s",
				cfg.Postgres.Hostname,
				cfg.Postgres.Port,
				cfg.Postgres.User,
				cfg.Postgres.Password,
				cfg.Postgres.Database,
				cfg.Postgres.SSLMode,
			),
		),
		&gorm.Config{})

	return db, err
}

func InitMigrate(db *gorm.DB) error {
	err := db.AutoMigrate(&models.User{}, &models.DKIMKey{})
	if err != nil {
		return fmt.Errorf("could not auto migrate database: %w", err)
	}

	err = db.Exec(createViewRawSQL).Error
	if err != nil {
		return fmt.Errorf("could not create the view: %w", err)
	}

	return nil
}