create or replace table document_category
(
	id bigint auto_increment
		primary key,
	subtype int default 0 not null,
	name varchar(255) not null,
	mime varchar(128) not null,
	constraint uq_doc_category_entry
		unique (mime, subtype)
);

create or replace table project
(
	id bigint auto_increment
		primary key,
	name varchar(512) not null,
	start_date date default current_timestamp() not null,
	end_date date default current_timestamp() not null,
	deleted_at datetime null,
	constraint chk_projdates
		check (`end_date` >= `start_date`)
);

create or replace table document
(
	id bigint auto_increment
		primary key,
	name varchar(512) default '' not null,
	original_name varchar(512) not null,
	upload_at date default current_timestamp() not null,
	deleted_at date null,
	type varchar(255) not null,
	category_id bigint not null,
	project_id bigint not null,
	viewpoint_id bigint null,
	navigation_radius double default 0 not null,
	content longblob null,
	hash varchar(512) null,
	direction double default 0 not null,
	constraint fk_doc_category
		foreign key (category_id) references document_category (id),
	constraint fk_doc_proj
		foreign key (project_id) references project (id)
);

create or replace trigger tr_insert_docu
	before insert
	on document
	for each row
	BEGIN
    IF ISNULL(new.type)
    THEN
        IF LOCATE('.SVG', UPPER(new.original_name)) > 0
        THEN
            SET new.type = 'image/svg+xml';
        ELSEIF LOCATE('.JPG', UPPER(new.original_name)) > 0
        THEN
            SET new.type = 'image/jpeg';
        ELSE
            SET new.type = 'text/plain';
        END IF;
    END IF;
    IF ISNULL(new.hash) THEN SET new.hash = SHA2(new.content, 256); END IF;
END;

create or replace trigger tr_update_docu
	before update
	on document
	for each row
	BEGIN
    SET NEW.hash = SHA2(NEW.content, 256);
END;

create or replace table role
(
	id bigint auto_increment
		primary key,
	name varchar(255) charset utf8 not null,
	administrator tinyint(1) default 0 not null,
	projectmanager tinyint(1) default 0 not null
);

create or replace table upload
(
	uuid varchar(64) not null,
	chunk_index int not null,
	chunk_count int not null,
	project_id bigint not null,
	chunk longblob not null,
	file_name varchar(512) not null,
	type varchar(255) not null,
	image_subtype int default 1 not null,
	file_size int not null,
	last_modified datetime not null,
	primary key (uuid, chunk_index),
	constraint fk_upload_project
		foreign key (project_id) references project (id)
);

create or replace table user
(
	id bigint auto_increment
		primary key,
	name varchar(255) charset utf8 not null,
	email varchar(255) charset utf8 not null,
	password text charset utf8 not null,
	disabled tinyint(1) default 0 not null,
	deleted_at datetime null,
	token_version bigint default 1 not null,
	constraint user_email_uindex
		unique (email)
);

create or replace table user_project_map
(
	user_id bigint not null,
	project_id bigint not null,
	role_id bigint not null,
	constraint unq_user_project_map
		unique (user_id, project_id, role_id),
	constraint fk_user_project_map_0
		foreign key (user_id) references user (id)
			on delete cascade,
	constraint fk_user_project_map_1
		foreign key (project_id) references project (id)
			on delete cascade,
	constraint fk_user_project_map_2
		foreign key (role_id) references role (id)
			on delete cascade
);

create or replace table user_role_map
(
	user_id bigint not null
		primary key,
	role_id bigint not null,
	constraint fk_user_role_role
		foreign key (role_id) references role (id)
			on delete cascade,
	constraint fk_user_role_user
		foreign key (user_id) references user (id)
			on delete cascade
);

create or replace table viewpoint
(
	id bigint auto_increment
		primary key,
	name varchar(128) not null,
	x double default 0 not null,
	y double default 0 not null,
	z double default 0 not null,
	document_id bigint not null,
	constraint fk_viewpoint_doc
		foreign key (document_id) references document (id)
);

alter table document
	add constraint fk_doc_vpoint
		foreign key (viewpoint_id) references viewpoint (id)
			on delete set null;

create or replace trigger tr_bi_viepoint
	before insert
	on viewpoint
	for each row
	BEGIN
    DECLARE t TEXT;
    SELECT d.type INTO t FROM document d WHERE d.id = NEW.document_id;
    IF LOWER(t) <> 'image/svg+xml' THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Improper document type! (Not a plan svg.)';
    END IF;
END;

create or replace trigger tr_bu_viepoint
	before update
	on viewpoint
	for each row
	BEGIN
    DECLARE t TEXT;
    SELECT d.type INTO t FROM document d WHERE d.id = NEW.document_id;
    IF LOWER(t) <> 'image/svg+xml' THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Improper document type! (Not a plan svg.)';
    END IF;
END;

create or replace table viewpoint_navigation
(
	viewpoint_id bigint not null,
	next_viewpoint_id bigint not null,
	primary key (viewpoint_id, next_viewpoint_id),
	constraint fk_navi_next_viewpoint
		foreign key (next_viewpoint_id) references viewpoint (id)
			on delete cascade,
	constraint fk_navi_viewpoint
		foreign key (viewpoint_id) references viewpoint (id)
			on delete cascade,
	constraint chk_navi_not_same_vpoint
		check (`viewpoint_id` <> `next_viewpoint_id`)
);

create or replace procedure auto_assign_doc_to_vpoint(IN docid int)
PROC:
BEGIN
    DECLARE vpcount INT DEFAULT 0;


    SELECT
        COUNT(vp.id)
    INTO
        vpcount
    FROM
        document d
        JOIN document pd ON pd.type = 'image/svg+xml' AND pd.project_id = d.project_id
        JOIN viewpoint vp ON vp.document_id = pd.id
    WHERE
        d.id = docid
        AND vp.name REGEXP get_vp_name_regex_from_docname(d.name);

    IF vpcount != 1 THEN
        LEAVE PROC;
    END IF;

    SELECT
        vp.id
    INTO
        vpcount
    FROM
        document d
        JOIN document pd ON pd.type = 'image/svg+xml' AND pd.project_id = d.project_id
        JOIN viewpoint vp ON vp.document_id = pd.id
    WHERE
        d.id = docid
        AND vp.name REGEXP get_vp_name_regex_from_docname(d.name);

    UPDATE document SET viewpoint_id = vpcount WHERE id = docid;
END;

create or replace procedure concat_upload(IN documentid bigint, IN uploaduuid varchar(64))
BEGIN
    DECLARE chunkCount, chunkCountTest0, chunkIdx INTEGER;
    DECLARE chunk LONGBLOB;
    DECLARE chunkitdone INT DEFAULT FALSE;
    DECLARE uid VARCHAR(64) DEFAULT uploaduuid;

    DECLARE chunkit CURSOR FOR SELECT `upload`.`chunk` FROM `upload` WHERE lower(uid) = lower(`upload`.`uuid`) ORDER BY `upload`.`chunk_index` ASC;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET chunkitdone = TRUE;

    SELECT MAX(chunk_count), COUNT(chunk_count)
        INTO chunkcount, chunkcounttest0
        FROM upload
        WHERE lower(uuid) = lower(uploaduuid)
        GROUP BY lower(uuid);
    IF (chunkcount <> chunkcounttest0) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid chunk count';
    END IF;

    IF (chunkcount > 0) THEN
        UPDATE document
            SET content = NULL
          WHERE id = documentid;

        OPEN chunkit;
chunkloop:
        LOOP
            FETCH chunkit INTO chunk;
            IF chunkitdone THEN
                LEAVE chunkloop;
            END IF;
            UPDATE document
                SET content = CONCAT(coalesce(content, ''), chunk)
                WHERE id = documentid;
        END LOOP ;
        CLOSE chunkit;

    ELSE
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Zero chunk count';
    END IF;
END;

create or replace function get_vp_name_regex_from_docname(name varchar(128)) returns varchar(128)
BEGIN
    DECLARE rx INT DEFAULT 0;

    IF (name IS NULL) OR (LENGTH(name) < 3) THEN
        RETURN NULL;
    END IF;


    -- GF1 -> ^GF[\x20_]?0?1$
    SELECT name RLIKE '(?i)^[^\\x20_]{2}[0-9][^0-9].*jpg$' INTO rx;
    IF rx != 0 THEN
        RETURN CONCAT(
            '^', SUBSTR(name, 1, 2), '[\\x20_]?0?', SUBSTR(name, 3, 1), '$');
    END IF;


    -- GF 1, GF_1 -> ^GF[\x20_]?0?1$
    SELECT name RLIKE '(?i)^[^\\x20_]{2}[\\x20_][0-9][^0-9].*jpg$' INTO rx;
    IF rx != 0 THEN
        RETURN CONCAT(
            '^', SUBSTR(name, 1, 2), '[\\x20_]?0?', SUBSTR(name, 4, 1), '$');
    END IF;


    -- GF01 -> ^GF[\x20_]?0?1$
    SELECT name RLIKE '(?i)^[^\\x20_]{2}0[0-9][^0-9].*jpg$' INTO rx;
    IF rx != 0 THEN
        RETURN CONCAT(
            '^', SUBSTR(name, 1, 2), '[\\x20_]?0?', SUBSTR(name, 4, 1), '$');
    END IF;


    -- GF10 -> ^GF[\x20_]?01$
    SELECT name RLIKE '(?i)^[^\\x20_]{2}[1-9][0-9][^0-9].*jpg$' INTO rx;
    IF rx != 0 THEN
        RETURN CONCAT(
            '^', SUBSTR(name, 1, 2), '[\\x20_]?', SUBSTR(name, 3, 2), '$');
    END IF;


    -- GF 01, GF_01 -> ^GF[\x20_]?0?1$
    SELECT name RLIKE '(?i)^[^\\x20_]{2}[\\x20_]0[0-9][^0-9].*jpg$' INTO rx;
    IF rx != 0 THEN
        RETURN CONCAT(
            '^', SUBSTR(name, 1, 2), '[\\x20_]?0?', SUBSTR(name, 5, 1), '$');
    END IF;


    -- GF 10, GF_10 -> ^GF[\x20_]?01$
    SELECT name RLIKE '(?i)^[^\\x20_]{2}[\\x20_][0-9]{2}[^0-9].*jpg$' INTO rx;
    IF rx != 0 THEN
        RETURN CONCAT('^', SUBSTR(name, 1, 2), '[\\x20_]?', SUBSTR(name, 4, 2), '$');
    END IF;

    RETURN 'WHAT';
END;


INSERT INTO role (id, name, administrator, projectmanager) VALUES (1, 'administrator', 1, 0);
INSERT INTO role (id, name, administrator, projectmanager) VALUES (2, 'projectmanager', 0, 1);
INSERT INTO role (id, name, administrator, projectmanager) VALUES (3, 'viewer', 0, 0);

INSERT INTO user (id, name, email, password, disabled, deleted_at, token_version) VALUES (1, 'Török Zoltán', 'torokze@gmail.com', 'ccdd4eb3081098aadc6a28b686b27e015b8327db9fc05190f7708cadaea631c8', 0, null, 1);

INSERT INTO user_role_map (user_id, role_id) VALUES (1, 1);

INSERT INTO document_category (id, subtype, name, mime) VALUES (1, 0, 'Plan', 'image/svg+xml');
INSERT INTO document_category (id, subtype, name, mime) VALUES (2, 1, 'Panoramic photo', 'image/jpeg');
INSERT INTO document_category (id, subtype, name, mime) VALUES (3, 2, 'Design render', 'image/jpeg');
INSERT INTO document_category (id, subtype, name, mime) VALUES (0, 0, 'Unknown', '*/*');
UPDATE document_category SET id = 0 WHERE id = 4;