create table mail_links (
       link_id	integer constraint mail_links_pk primary key,
       url	text constraint mail_links_url_nn not null
);

create sequence mail_links_seq;

create table mail_link_mail_map (
       mail_id		   integer 
       			   constraint mail_link_mail_map_m_id_fk
			   references acs_objects on delete cascade,
       link_id		   integer 
       			   constraint mail_link_mail_map_link_id_fk
       			   references mail_links on delete cascade,
       constraint mail_link_mail_map_pk
       primary key (mail_id, link_id)
);

create table mail_link_clicks (
       mail_id		   integer 
       			   constraint mail_link_clicks_mail_id_fk
       			   references acs_objects on delete cascade,
       link_id		   integer
       			   constraint mail_link_clicks_link_id_fk
			   references mail_links on delete cascade,
       user_id		   integer
       			   constraint mail_link_clicks_user_id_fk
			   references users on delete cascade,
       click_time	   timestamptz default current_timestamp
);

-- to speed up statistic queries

create index mail_link_clicks_mail_ix on mail_link_clicks(mail_id);
create index mail_link_clicks_link_ix on mail_link_clicks(link_id);
create index mail_link_clicks_user_ix on mail_link_clicks(user_id);
create index mail_link_clicks_date_ix on mail_link_clicks(click_time);


\i mail-links-package.sql