CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE IF NOT EXISTS params (
    id integer PRIMARY KEY DEFAULT 1,
    password_max_days integer NOT NULL DEFAULT 90,
    dcre timestamp without time zone DEFAULT now()
);

INSERT INTO params (id)
VALUES (1)
ON CONFLICT (id) DO NOTHING;

CREATE TABLE IF NOT EXISTS app_user (
    lusername text PRIMARY KEY,
    password_hash text NOT NULL,
    password_changed_at timestamp without time zone DEFAULT now(),
    dcre timestamp without time zone DEFAULT now()
);

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

CREATE INDEX IF NOT EXISTS idx_oldcreds_lusername
    ON oldcreds(lusername);

CREATE OR REPLACE FUNCTION public.f_password_is_expired()
RETURNS boolean AS $$
DECLARE
    l_password_max_days integer;
    l_password_changed_at timestamp without time zone;
BEGIN
    SELECT password_max_days
    INTO l_password_max_days
    FROM params
    WHERE id = 1;

    SELECT password_changed_at
    INTO l_password_changed_at
    FROM app_user
    WHERE lusername = current_user;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Utilisateur introuvable pour current_user: %', current_user;
    END IF;

    IF l_password_changed_at IS NULL THEN
        RETURN true;
    END IF;

    RETURN l_password_changed_at + (l_password_max_days || ' days')::interval < 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 password_hash
    INTO l_current_hash
    FROM app_user
    WHERE lusername = current_user;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Utilisateur introuvable pour current_user: %', current_user;
    END IF;

    IF p_password_new IS NULL OR length(p_password_new) < 8 THEN
        RAISE EXCEPTION 'Mot de passe trop court';
    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 'Mot de passe doit contenir une majuscule';
    END IF;

    IF l_lower = 0 THEN
        RAISE EXCEPTION 'Mot de passe doit contenir une minuscule';
    END IF;

    IF l_digit = 0 THEN
        RAISE EXCEPTION 'Mot de passe doit contenir un chiffre';
    END IF;

    IF l_special = 0 THEN
        RAISE EXCEPTION 'Mot de passe doit contenir un caractère spécial';
    END IF;

    IF crypt(p_password_new, l_current_hash) = l_current_hash THEN
        RAISE EXCEPTION 'Mot de passe déjà utilisé';
    END IF;

    IF EXISTS (
        SELECT 1
        FROM oldcreds
        WHERE lusername = current_user
        AND crypt(p_password_new, password_hash) = password_hash
    ) THEN
        RAISE EXCEPTION 'Mot de passe déjà utilisé';
    END IF;

    INSERT INTO oldcreds (lusername, password_hash, dcre)
    VALUES (current_user, l_current_hash, now());

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

    UPDATE app_user
    SET password_hash = l_new_hash,
        password_changed_at = now()
    WHERE lusername = current_user;

    RETURN true;
END;
$$ LANGUAGE plpgsql;
