module Rodauth

def self.create_database_authentication_functions(db, opts={})
  table_name = opts[:table_name] || :account_password_hashes
  get_salt_name = opts[:get_salt_name] || :rodauth_get_salt
  valid_hash_name = opts[:valid_hash_name] || :rodauth_valid_password_hash 
  case db.database_type
  when :postgres
    db.run <<END
EATE OR REPLACE FUNCTION #{get_salt_name}(acct_id int8) RETURNS text AS $$
CLARE salt text;
GIN
LECT substr(password_hash, 0, 30) INTO salt 
OM #{table_name}
ERE acct_id = id;
TURN salt;
D;
 LANGUAGE plpgsql
CURITY DEFINER
T search_path = public, pg_temp;
D
    db.run <<END
EATE OR REPLACE FUNCTION #{valid_hash_name}(acct_id int8, hash text) RETURNS boolean AS $$
CLARE valid boolean;
GIN
LECT password_hash = hash INTO valid 
OM #{table_name}
ERE acct_id = id;
TURN valid;
D;
 LANGUAGE plpgsql
CURITY DEFINER
T search_path = public, pg_temp;
D
  when :mysql
    db.run <<END
EATE FUNCTION #{get_salt_name}(acct_id int8) RETURNS varchar(255)
L SECURITY DEFINER
ADS SQL DATA
GIN
CLARE salt varchar(255);
CLARE csr CURSOR FOR
LECT substr(password_hash, 1, 30)
OM #{table_name}
ERE acct_id = id;
EN csr;
TCH csr INTO salt;
OSE csr;
TURN salt;
D;
D
    db.run <<END
EATE FUNCTION #{valid_hash_name}(acct_id int8, hash varchar(255)) RETURNS tinyint(1)
L SECURITY DEFINER
ADS SQL DATA
GIN
CLARE valid tinyint(1);
CLARE csr CURSOR FOR 
LECT password_hash = hash
OM #{table_name}
ERE acct_id = id;
EN csr;
TCH csr INTO valid;
OSE csr;
TURN valid;
D;
D
  when :mssql
    db.run <<END
EATE FUNCTION #{get_salt_name}(@account_id bigint) RETURNS nvarchar(255)
TH EXECUTE AS OWNER

GIN
CLARE @salt nvarchar(255);
LECT @salt = substring(password_hash, 0, 30)
OM #{table_name}
ERE id = @account_id;
TURN @salt;
D;
D
    db.run <<END
EATE FUNCTION #{valid_hash_name}(@account_id bigint, @hash nvarchar(255)) RETURNS bit
TH EXECUTE AS OWNER

GIN
CLARE @valid bit;
CLARE @ph nvarchar(255);
LECT @ph = password_hash
OM #{table_name}
ERE id = @account_id;
(@hash = @ph)
SET @valid = 1;
SE
SET @valid = 0
TURN @valid;
D;
D
  end
end