create table users (
    id          bigserial                   not null,
    ip_address  cidr                        not null,
    created     date                        default now()::date not null,
    primary key (id),
    unique (ip_address)
);

create table user_colors (
    user_id     bigint                      not null,
    color       integer                     not null,
    primary key (user_id, color),
    foreign key (user_id) references users
);

create table pixels (
    x           smallint                    not null,
    y           smallint                    not null,
    color       integer                     not null,
    user_id     bigint                      not null,
    last_update timestamp                   default now() not null,
    primary key (x, y),
    foreign key (user_id) references users
);

create table history (
    id          bigserial                   not null,
    user_id     bigint                      not null,
    time        timestamp                   default now() not null,
    primary key (id),
    foreign key (user_id) references users
);

create table requests (
    id          bigserial                   not null,
    ip_address  cidr                        not null,
    method      text                        not null,
    url         text                        not null,
    is_secure   bool                        not null,
    user_agent  character varying (4096),
    referer     character varying (2048),
    time        timestamp                   default now() not null,
    primary key (id)
);

create function pixels_history() returns trigger as $$
    begin
        insert into history (
            user_id
        ) values (
            new.user_id
        );
        return new;
    end;
$$ language plpgsql;

create trigger pixels_history after insert or update on pixels
    for each row execute procedure pixels_history();

create procedure put_pixel(
        _x smallint,
        _y smallint,
        _color integer,
        _ip_address cidr
) as $$
    declare
        _user_id bigint;
    begin
        insert into users (
            ip_address
        ) values (
            _ip_address
        ) on conflict (
            ip_address
        ) do nothing;
        _user_id = (
            select
                users.id
            from
                users
            where
                users.ip_address = _ip_address
        );
        insert into pixels (
            x,
            y,
            color,
            user_id,
            last_update
        ) values (
            _x,
            _y,
            _color,
            _user_id,
            default
        )
        on conflict (
            x,
            y
        ) do update set
            color = excluded.color,
            user_id = excluded.user_id,
            last_update = excluded.last_update;
    end;
$$ language plpgsql;

-- truncate pixels;