
CREATE EXTENSION IF NOT EXISTS pgcrypto;

ALTER TABLE employe ADD COLUMN IF NOT EXISTS lpassword_hash text;
ALTER TABLE employe ADD COLUMN IF NOT EXISTS dpassword_changed_at timestamp without time zone DEFAULT now();

CREATE TABLE IF NOT EXISTS oldcreds (
    oldcred_id serial PRIMARY KEY,
    llogon text NOT NULL REFERENCES employe(llogon),
    lpassword_hash text NOT NULL,
    dcre timestamp without time zone DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_oldcreds_llogon
    ON oldcreds(llogon);

CREATE OR REPLACE FUNCTION public.f_password_is_expired()
RETURNS boolean AS $$
DECLARE
    l_password_changed_at timestamp without time zone;
BEGIN
    SELECT dpassword_changed_at
    INTO l_password_changed_at
    FROM employe
    WHERE llogon = current_user;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'User not found for current_user: %', current_user;
    END IF;

    IF l_password_changed_at IS NULL THEN
        RETURN true;
    END IF;

    RETURN l_password_changed_at + interval '30 days' < now();
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION public.f_reset_password(
    p_password_new text
)
RETURNS boolean AS $$
DECLARE
    l_current_hash text;
    l_new_hash text;
    l_upper integer;
    l_lower integer;
    l_digit integer;
    l_special integer;
BEGIN
    SELECT lpassword_hash
    INTO l_current_hash
    FROM employe
    WHERE llogon = current_user;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'User not found for current_user: %', current_user;
    END IF;

    IF p_password_new IS NULL OR length(p_password_new) < 8 THEN
        RAISE EXCEPTION 'Password too short';
    END IF;

    l_upper := (p_password_new ~ '[A-Z]')::int;
    l_lower := (p_password_new ~ '[a-z]')::int;
    l_digit := (p_password_new ~ '[0-9]')::int;
    l_special := (p_password_new ~ '[^A-Za-z0-9]')::int;

    IF l_upper = 0 THEN
        RAISE EXCEPTION 'Password must contain an uppercase letter';
    END IF;

    IF l_lower = 0 THEN
        RAISE EXCEPTION 'Password must contain a lowercase letter';
    END IF;

    IF l_digit = 0 THEN
        RAISE EXCEPTION 'Password must contain a digit';
    END IF;

    IF l_special = 0 THEN
        RAISE EXCEPTION 'Password must contain a special character';
    END IF;

    IF l_current_hash IS NOT NULL AND crypt(p_password_new, l_current_hash) = l_current_hash THEN
        RAISE EXCEPTION 'Password already used';
    END IF;

    IF EXISTS (
        SELECT 1
        FROM oldcreds
        WHERE llogon = current_user
        AND crypt(p_password_new, lpassword_hash) = lpassword_hash
    ) THEN
        RAISE EXCEPTION 'Password already used';
    END IF;

    IF l_current_hash IS NOT NULL THEN
        INSERT INTO oldcreds (llogon, lpassword_hash, dcre)
        VALUES (current_user, l_current_hash, now());
    END IF;

    l_new_hash := crypt(p_password_new, gen_salt('bf', 10));

    UPDATE employe
    SET lpassword_hash = l_new_hash,
        dpassword_changed_at = now()
    WHERE llogon = current_user;

    -- Reset the PostgreSQL user password as well
    EXECUTE format('ALTER USER %I WITH PASSWORD %L', current_user, p_password_new);

    RETURN true;
END;
$$ LANGUAGE plpgsql;