--
DROP TRIGGER users_trg ON users;
DROP TABLE user_passwords;
DROP TABLE users;
DROP FUNCTION IF EXISTS my_max CASCADE;
DROP AGGREGATE IF EXISTS max(my_timestamp);
DROP FUNCTION IF EXISTS my_from_local;
DROP TYPE IF EXISTS  my_timestamp CASCADE;
CREATE TYPE my_timestamp AS (
    tstamp TIMESTAMP WITHOUT TIME ZONE, 
    tzone INTERVAL, 
    short_name CHARACTER VARYING(4)
);

CREATE OR REPLACE FUNCTION 
    make_my_timestamp(tstamp timestamp without time zone, tzone interval, short_name character) 
    returns my_timestamp 
    language sql as 
    $_$ 
        SELECT ($1, $2, $3)::my_timestamp 
    $_$
;

CREATE TABLE users (
    name VARCHAR(30) UNIQUE NOT NULL,
    timezone VARCHAR,
    password VARCHAR,
    created my_timestamp,
    last_amended my_timestamp
);

CREATE TABLE user_passwords (name VARCHAR references users(name), timestamp my_timestamp, password VARCHAR);
CREATE OR REPLACE FUNCTION my_max(my_timestamp, my_timestamp) returns my_timestamp 
language plpgsql as $_$ 
    begin if $1 is null then return $2; 
    end if; 
    if $2.tstamp > $1.tstamp then return $2; 
    else return $1; 
    end if; 
end $_$;

create aggregate max(my_timestamp) (sfunc=my_max, stype=my_timestamp);

CREATE OR REPLACE FUNCTION my_from_local(timestamp with time zone) RETURNS my_timestamp  AS $$
    DECLARE
        v_row record;
        v_result record;
    BEGIN
        SELECT  ROW($1 AT TIME ZONE 'UTC',$1 - $1 AT TIME ZONE 'UTC', to_char($1, 'TZ')::VARCHAR(4)) AS result into v_row;
        v_result = v_row.result;
        RETURN v_result;
    END $$
LANGUAGE 'plpgsql' IMMUTABLE;



create or replace function user_passwords_add() returns trigger as $_$
declare
    lc_latest VARCHAR;
begin
    if new.password is not null and length(new.password) > 0 then
        select up.password into lc_latest from user_passwords up where up.name = new.name and up.timestamp = (select max(timestamp) from user_passwords where name=up.name);
        if lc_latest is null or (new.password <> lc_latest) then
            insert into user_passwords (name, timestamp, password) values (new.name,  my_from_local(now()), new.password);
        end if;
    end if;
    return new;
end
$_$ language plpgsql;


CREATE TRIGGER users_trg AFTER INSERT OR UPDATE ON users FOR EACH ROW EXECUTE PROCEDURE user_passwords_add();
