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,
    password_min_length integer NOT NULL DEFAULT 8,
    password_min_upper integer NOT NULL DEFAULT 1,
    password_min_lower integer NOT NULL DEFAULT 1,
    password_min_digit integer NOT NULL DEFAULT 1,
    password_min_special integer NOT NULL DEFAULT 1,
    dcre timestamp without time zone DEFAULT now()
);

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

CREATE TABLE IF NOT EXISTS users (
    user_id serial PRIMARY KEY,
    username text UNIQUE NOT NULL,
    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,
    user_id integer NOT NULL REFERENCES users(user_id),
    password_hash text NOT NULL,
    dcre timestamp without time zone DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_oldcreds_user_id
    ON oldcreds(user_id);

CREATE OR REPLACE FUNCTION public.f_check_password_complexity(
    p_password text
)
RETURNS void AS $$
DECLARE
    l_min_length integer;
    l_min_upper integer;
    l_min_lower integer;
    l_min_digit integer;
    l_min_special integer;
BEGIN
    SELECT password_min_length,
           password_min_upper,
           password_min_lower,
           password_min_digit,
           password_min_special
    INTO l_min_length, l_min_upper, l_min_lower, l_min_digit, l_min_special
    FROM params
    WHERE id = 1;

    IF p_password IS NULL OR length(p_password) < l_min_length THEN
        RAISE EXCEPTION 'Mot de passe trop court';
    END IF;

    IF l_min_upper > 0 AND p_password !~ '[A-Z]' THEN
        RAISE EXCEPTION 'Mot de passe doit contenir une majuscule';
    END IF;

    IF l_min_lower > 0 AND p_password !~ '[a-z]' THEN
        RAISE EXCEPTION 'Mot de passe doit contenir une minuscule';
    END IF;

    IF l_min_digit > 0 AND p_password !~ '[0-9]' THEN
        RAISE EXCEPTION 'Mot de passe doit contenir un chiffre';
    END IF;

    IF l_min_special > 0 AND p_password !~ '[^A-Za-z0-9]' THEN
        RAISE EXCEPTION 'Mot de passe doit contenir un caractère spécial';
    END IF;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION public.f_update_password_if_expired(
    p_username text,
    p_password_new text
)
RETURNS boolean AS $$
DECLARE
    l_password_max_days integer;
    l_password_changed_at timestamp without time zone;
    l_current_hash text;
    l_new_hash text;
    l_is_expired boolean;
    l_user_id integer;
BEGIN
    SELECT password_max_days
    INTO l_password_max_days
    FROM params
    WHERE id = 1;

    SELECT user_id, password_hash, password_changed_at
    INTO l_user_id, l_current_hash, l_password_changed_at
    FROM users
    WHERE username = p_username;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Utilisateur introuvable';
    END IF;

    l_is_expired := l_password_changed_at IS NULL
        OR l_password_changed_at + (l_password_max_days || ' days')::interval < now();

    IF NOT l_is_expired THEN
        RETURN false;
    END IF;

    PERFORM public.f_check_password_complexity(p_password_new);

    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 user_id = l_user_id
        AND crypt(p_password_new, password_hash) = password_hash
    ) THEN
        RAISE EXCEPTION 'Mot de passe déjà utilisé';
    END IF;

    INSERT INTO oldcreds (user_id, password_hash, dcre)
    VALUES (l_user_id, l_current_hash, now());

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

    UPDATE users
    SET password_hash = l_new_hash,
        password_changed_at = now()
    WHERE user_id = l_user_id;

    RETURN true;
END;
$$ LANGUAGE plpgsql;
