create database loot;
grant select,insert,update on loot.* to 'loot'@'localhost';

create table u_head (
	hid	int AUTO_INCREMENT PRIMARY KEY,

	head	varchar(950),

	unique key	ix_head (head)
);

create table url (
	uid	int AUTO_INCREMENT PRIMARY KEY,

	url	varchar(1000),

	hid	int,
	tail	varchar(950),

	unique key	ix_url (url),
	unique key	ix_ht (hid,tail)
);

-- set sql_mode = 'ANSI';
create table l2n (
	id	int AUTO_INCREMENT PRIMARY KEY,

	uid	int,

	code	smallint,
	lid	int,

	seen	smallint unsigned default 1,
	source	tinyint unsigned,

	minatime	timestamp null default null,
	maxatime	timestamp null default null,

	minmtime	timestamp null default null,
	maxmtime	timestamp null default null,

	tstamp	timestamp default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,

	unique key	ix_ulid (uid,lid),

	key	ix_uid (uid),
	key	ix_lid (lid),
	key	ix_ts (tstamp)
);


create table n2n (
	lid	int AUTO_INCREMENT PRIMARY KEY,

	`size`	int,
	tid	tinyint unsigned,

	sha1	binary(20),
	pref	binary(20),
--	sha256	binary(32),

	md4	binary(16),
	md5	binary(16),

	crc32	int unsigned,

	tstamp	timestamp,

--	unique key ix_set (size, sha1, sha256, md4, md5),
	key	ix_sha1 (sha1),
	key	ix_pref (pref),
	key	ix_md4 (md4),
	key	ix_md5 (md5),
	key	ix_crc32 (crc32),
	key	ix_ts (tstamp)
);


create table filetype (
	tid	tinyint unsigned AUTO_INCREMENT PRIMARY KEY,

	mime	char(50),

	unique key	ix_mime (mime)
);




create table triple (
	lid	int PRIMARY KEY,

	k	varchar(32),
	v	varchar(64),

	version	tinyint,

	tstamp	timestamp,

	unique key	ix_lidk (lid, k),
	key	ix_ts (tstamp)
);

create table image (
	lid	int PRIMARY KEY,

	magick	char(10),

	width	int unsigned,
	height	int unsigned ,

	colors	int unsigned,
	frames	smallint unsigned,

	version	tinyint,

	tstamp	timestamp,
	key	ix_ts (tstamp)
);


create table v_rgb4x4 (
	lid	int PRIMARY KEY,

	vec	binary(48),

	version	tinyint,

	tstamp	timestamp,
	key	ix_ts (tstamp)
);





create database audit;
create table audit.err (
	txt varchar(255) not null primary key
);
create user 'auditins'@'localhost';
update mysql.user set password=password('a2d44720309830995f7645c9e766452d') where user='auditins';
flush privileges;
grant insert,update,trigger on audit.* to 'auditins'@'localhost';
grant select,trigger on loot.* to 'auditins'@'localhost';
grant insert on loot.block to 'auditins'@'localhost';


create table block (
	bid	int AUTO_INCREMENT PRIMARY KEY,
	lid	int,
	status	char(3) not null,
	created	timestamp null default null,
	tstamp	timestamp default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
	key	ix_ts (tstamp),
	key	ix_st (status),
	unique key	ix_lid (lid)
);
create table audit.a_block like block;
alter table audit.a_block
	modify bid int, 
	modify tstamp timestamp null default null,
	add a_op char(3) not null,
	add a_user varchar(100) not null,
	add a_created timestamp null default null,
	add a_tstamp timestamp default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP;
alter table audit.a_block drop primary key;
alter table audit.a_block add index (lid);
alter table audit.a_block drop index ix_lid;
alter table audit.a_block add index (bid);

create table report (
	rid	int AUTO_INCREMENT PRIMARY KEY,

	lid	int,

	obj	varchar(255),
	src	varchar(255),
	txt	varchar(255),

	status	char(3) not null,

	created	timestamp null default null,
	tstamp	timestamp default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
	key	ix_ts (tstamp),
	key	ix_st (status),
	key	ix_lid (lid)
);
create table audit.a_report like report;
alter table audit.a_report 
	modify rid int, 
	modify tstamp timestamp null default null,
	add a_op char(3) not null,
	add a_user varchar(100) not null,
	add a_created timestamp null default null,
	add a_tstamp timestamp default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP;
alter table audit.a_report drop primary key;
alter table audit.a_report add index (rid);

delimiter //
drop trigger if exists audit.report_audit_insert;//
create definer='auditins'@'localhost' trigger 
		audit.report_audit_insert
	before insert on 
		audit.a_report
	for each row set 
		NEW.a_created = current_timestamp();//

drop trigger if exists audit.report_audit_noupdate;//
create definer='auditins'@'localhost' trigger 
		audit.report_audit_noupdate 
	before update on 
		audit.a_report
	for each row
	begin
		insert into audit.err values ('no update allowed');
		insert into audit.err values ('no update allowed');
	end;//

drop trigger if exists audit.report_audit_nodelete;//
create definer='auditins'@'localhost' trigger 
		audit.report_audit_nodelete
	before delete on 
		audit.a_report
	for each row
	begin
		insert into audit.err values ('no delete allowed');
		insert into audit.err values ('no delete allowed');
	end;//
	
grant trigger on loot.report to 'loot'@'localhost';//
drop trigger if exists report_preins;//
create definer='loot'@'localhost' trigger
		report_preins
	before insert on
		report
	for each row set 
		NEW.created = current_timestamp(),
		NEW.status = coalesce(NEW.status, 'new');//
	
drop trigger if exists report_audit_ins;//
create definer='auditins'@'localhost' trigger
		report_audit_ins
	after insert on
		report
	for each row
	begin
		if NEW.lid is not null then
			insert ignore into
				loot.block
			set
				lid = NEW.lid,
				status = 'new';
		end if;
		insert into
			audit.a_report
		set
			rid = NEW.rid,
			lid = NEW.lid,
			obj = NEW.obj,
			src = NEW.src,
			txt = NEW.txt,
			status = NEW.status,
			created	= NEW.created,
			tstamp = NEW.tstamp,
			a_user = user(),
			a_op = 'ins';
	end;//

drop trigger if exists report_audit_upd;//
create definer='auditins'@'localhost' trigger
		report_audit_upd
	after update on
		report
	for each row
	begin
		if NEW.lid is not null and ( OLD.lid is null or OLD.lid != NEW.lid ) then
			insert ignore into
				loot.block
			set
				lid = NEW.lid,
				status = 'new';
		end if;

		insert into
			audit.a_report
		set
			rid = NEW.rid,
			lid = NEW.lid,
			obj = NEW.obj,
			src = NEW.src,
			txt = NEW.txt,
			status = NEW.status,
			created	= NEW.created,
			tstamp = NEW.tstamp,
			a_user = user(),
			a_op = 'upd';
	end;//

drop trigger if exists report_audit_del;//
create definer='auditins'@'localhost' trigger
		report_audit_del
	after delete on
		report
	for each row
		insert into
			audit.a_report
		set
			rid = OLD.rid,
			lid = OLD.lid,
			obj = OLD.obj,
			src = OLD.src,
			txt = OLD.txt,
			status = OLD.status,
			created	= OLD.created,
			tstamp = OLD.tstamp,
			a_user = user(),
			a_op = 'del';//


drop trigger if exists audit.block_audit_insert;//
create definer='auditins'@'localhost' trigger 
		audit.block_audit_insert
	before insert on 
		audit.a_block
	for each row set 
		NEW.a_created = current_timestamp();//

drop trigger if exists audit.block_audit_noupdate;//
create definer='auditins'@'localhost' trigger 
		audit.block_audit_noupdate 
	before update on 
		audit.a_block
	for each row
	begin
		insert into audit.err values ('no update allowed');
		insert into audit.err values ('no update allowed');
	end;//

drop trigger if exists audit.block_audit_nodelete;//
create definer='auditins'@'localhost' trigger 
		audit.block_audit_nodelete
	before delete on 
		audit.a_block
	for each row
	begin
		insert into audit.err values ('no delete allowed');
		insert into audit.err values ('no delete allowed');
	end;//
	
grant trigger on loot.block to 'loot'@'localhost';//
drop trigger if exists block_preins;//
create definer='loot'@'localhost' trigger
		block_preins
	before insert on
		block
	for each row set 
		NEW.created = current_timestamp(),
		NEW.status = coalesce(NEW.status, 'new');//
	
drop trigger if exists block_audit_ins;//
create definer='auditins'@'localhost' trigger
		block_audit_ins
	after insert on
		block
	for each row
	begin
		insert into
			audit.a_block
		set
			bid = NEW.bid,
			lid = NEW.lid,
			status = NEW.status,
			created	= NEW.created,
			tstamp = NEW.tstamp,
			a_user = user(),
			a_op = 'ins';
	end;//

drop trigger if exists block_audit_upd;//
create definer='auditins'@'localhost' trigger
		block_audit_upd
	after update on
		block
	for each row
	begin
		insert into
			audit.a_block
		set
			bid = NEW.bid,
			lid = NEW.lid,
			status = NEW.status,
			created	= NEW.created,
			tstamp = NEW.tstamp,
			a_user = user(),
			a_op = 'upd';
	end;//

drop trigger if exists block_audit_del;//
create definer='auditins'@'localhost' trigger
		block_audit_del
	after delete on
		block
	for each row
		insert into
			audit.a_block
		set
			bid = OLD.bid,
			lid = OLD.lid,
			status = OLD.status,
			created	= OLD.created,
			tstamp = OLD.tstamp,
			a_user = user(),
			a_op = 'del';//

delimiter ;