--- MySql
--- SQL command to create the wrreport table, that is necessary to use the wrreport extension.
CREATE TABLE wrreport (
  id integer auto_increment primary key,
  page_id integer,                       -- mediawiki page id the report is intended for
  page_title varchar(255),               -- mediawiki page title the report is intended for
  date_report date,                      -- date, the report is intended for (local time zone at sledrun position, probably Europe/Vienna)
  time_report time,                      -- optional time, the report is intended for (local time zone at sledrun position, probably Europe/Vienna)
  date_entry datetime,                   -- date and time where report was created (time zone Europe/Vienna)
  date_invalid datetime,                 -- date and time were report becomes invalid/expires (time zone Europe/Vienna)
  `condition` integer,                   -- condition from 1 (very good) to 5 (sledding not possible). NULL allowed.
  description text,                      -- report description (wikitext)
  author_name varchar(30),               -- name of the person who created the report
  author_ip varchar(15),                 -- IP address of the person who created the report
  author_userid integer,                 -- mediawiki user id of the person who created the report
  author_username varchar(30),           -- mediawiki user name of the person who created the report
  delete_date datetime,                  -- the report is considered deleted if this date is set (time zone Europe/Vienna)
  delete_person_name varchar(30),        -- name of the person who deleted the report
  delete_person_ip varchar(15),          -- IP address of the person who deleted the report
  delete_person_userid integer,          -- mediawiki user id of the person who deleted the report
  delete_person_username varchar(30),    -- mediawiki user name of the person who deleted the report
  delete_reason_public text,             -- reason for the deletion (wikitext)
  delete_invisible boolean,              -- do not mention that someone deleted this recored
  CONSTRAINT CHECK (`condition` >= 1 AND `condition` <= 5)
);

-- Indices
ALTER TABLE wrreport ADD INDEX idx_author (author_userid, `condition`, delete_date);
ALTER TABLE wrreport ADD INDEX idx_page (page_title, date_invalid);
ALTER TABLE wrreport ADD INDEX idx_date_invalid (date_invalid);
ALTER TABLE wrreport ADD INDEX idx_date_entry (date_entry);

-- Unicode
ALTER TABLE `wrreport` CHANGE `description` `description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `wrreport` CHANGE `author_name` `author_name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `wrreport` CHANGE `delete_person_name` `delete_person_name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `wrreport` CHANGE `delete_reason_public` `delete_reason_public` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CHECK constraint
ALTER TABLE wrreport ADD CONSTRAINT condition_range CHECK (`condition` >= 1 AND `condition` <= 5);
ALTER TABLE wrreport ADD CONSTRAINT author_userid_nonzero CHECK (`author_userid` > 0);
ALTER TABLE wrreport ADD CONSTRAINT delete_person_userid_nonzero CHECK (`delete_person_userid` > 0);

-- NULL constraints
ALTER TABLE wrreport MODIFY COLUMN `page_id` integer NOT NULL COMMENT 'mediawiki page id the report is intended for';
ALTER TABLE wrreport MODIFY COLUMN `page_title` varchar(255) NOT NULL COMMENT 'mediawiki page title the report is intended for';
ALTER TABLE wrreport MODIFY COLUMN `date_report` date NOT NULL COMMENT 'date, the report is intended for (local time zone at sledrun position, probably Europe/Vienna)';
ALTER TABLE wrreport MODIFY COLUMN `time_report` time NULL COMMENT 'optional time, the report is intended for (local time zone at sledrun position, probably Europe/Vienna)';
ALTER TABLE wrreport MODIFY COLUMN `date_entry` datetime NOT NULL COMMENT 'date and time where report was created (time zone Europe/Vienna)';
ALTER TABLE wrreport MODIFY COLUMN `date_invalid` datetime NOT NULL COMMENT 'date and time were report becomes invalid/expires (time zone Europe/Vienna)';
ALTER TABLE wrreport MODIFY COLUMN `condition` integer NULL COMMENT 'condition from 1 (very good) to 5 (sledding not possible). NULL allowed.';
ALTER TABLE wrreport MODIFY COLUMN `description` mediumtext NOT NULL COMMENT 'report description (wikitext)';
ALTER TABLE wrreport MODIFY COLUMN `author_name` varchar(30) NOT NULL COMMENT 'name/nickname of the person who created the report';
ALTER TABLE wrreport MODIFY COLUMN `author_ip` varchar(45) NULL COMMENT 'IP address of the person who created the report';
ALTER TABLE wrreport MODIFY COLUMN `author_userid` integer NULL COMMENT 'mediawiki user id of the person who created the report';
ALTER TABLE wrreport MODIFY COLUMN `author_username` varchar(255) NULL COMMENT 'mediawiki user name of the person who created the report';
ALTER TABLE wrreport MODIFY COLUMN `delete_date` datetime NULL COMMENT 'the report is considered deleted if this date is set (time zone Europe/Vienna)';
ALTER TABLE wrreport MODIFY COLUMN `delete_person_name` varchar(30) NULL COMMENT 'name/nickname of the person who deleted the report';
ALTER TABLE wrreport MODIFY COLUMN `delete_person_ip` varchar(45) NULL COMMENT 'IP address of the person who deleted the report';
ALTER TABLE wrreport MODIFY COLUMN `delete_person_userid` integer NULL COMMENT 'mediawiki user id of the person who deleted the report';
ALTER TABLE wrreport MODIFY COLUMN `delete_person_username` varchar(255) NULL COMMENT 'mediawiki user name of the person who deleted the report';
ALTER TABLE wrreport MODIFY COLUMN `delete_reason_public` mediumtext NULL COMMENT 'reason for the deletion (wikitext)';
ALTER TABLE wrreport MODIFY COLUMN `delete_invisible` boolean NULL COMMENT 'do not mention that someone deleted this recored';
