-- 2014-11-09	 To facilitate dynamic customization of input dialog for a feature
ALTER TABLE FEATUREDEFINITION ADD DIALOGJAVASCRIPT TEXT;

-- 2015-01-28	 To facilitate group ownership of a document
ALTER TABLE DOCUMENT ADD GROUPID VARCHAR(60);

-- 2015-05-01	 To facilitate applying diffent levels of copying protection to image display
ALTER TABLE SURROGATEIMAGE ADD PROTECTLEVEL INTEGER default 0;

-- 2016-06-14	 To facilitate options from Cat's Collation Editor 
ALTER TABLE REGULARIZATIONRULE ADD RULEOPTIONS INTEGER default 0;
ALTER TABLE REGULARIZATIONRULE ADD CREATEDATE timestamp default CURRENT_TIMESTAMP;

CREATE TABLE `REGULARIZATIONRULEEXCEPTION` (
  `REGULARIZATIONRULEID` int(11) NOT NULL,
  `VERSE` int(11) NOT NULL,
  `GROUPNAME` varchar(40) NOT NULL DEFAULT 'Unassigned',
  `USERID` varchar(200) DEFAULT NULL,
  `CREATEDATE` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`REGULARIZATIONRULEID`,`VERSE`,`GROUPNAME`,`USERID`),
  KEY `BIBLICALCONTENTVERSE` (`VERSE`,`GROUPNAME`,`USERID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2016-10-07	To facilitate per document versification
ALTER TABLE DOCUMENT ADD V11N VARCHAR(60) DEFAULT NULL;

-- 2016-10-30	To facilitate per project transcription attribution
ALTER TABLE PROJECT ADD TRANSCRIPTIONATTRIBUTION VARCHAR(254) DEFAULT NULL;

-- 2017-07-13	To have some semblance of designating which fragments
--		of a distributed page are at what institution
--		using image per fragment is not ideal but is a reasonable
--		compromise
ALTER TABLE SURROGATEIMAGE ADD SHELFID int(11);

-- 2018-05-28	Start of mapping Institution to geonames.org
ALTER TABLE INSTITUTION ADD GEONAME varchar(150);

-- 2018-05-31	Contains the official abbreviation as referred to by
--		the Coptic scholarly community or allows other
--		projects to specify their own custom names or limit institutes
--		to their project interests by filling in this field and choosing
--		the catalog option to include only institutes with custom names
ALTER TABLE INSTITUTION ADD `CUSTOMINSTNAME` varchar(120) DEFAULT NULL;

-- 2018-06-25	Added for quick computation of project stats
ALTER TABLE TRANSCRIPTIONPAGE ADD LINECOUNT int(11) DEFAULT NULL;
ALTER TABLE TRANSCRIPTIONPAGE ADD WORDCOUNT int(11) DEFAULT NULL;

-- 2018-08-05	Added Münster summer updates for retaining indexing order
ALTER TABLE PAGE ADD INDEXSTRINGORIG varchar(100) DEFAULT NULL;
-- 2018-08-05	... handling a secondary document group to differentiate transcriptions from editing
ALTER TABLE PROJECT ADD DOCUMENTGROUPIDSECONDARY int(11) DEFAULT NULL;
-- 2018-08-05	... letting students change stage of their assignments
ALTER TABLE TASK ADD STAGE INTEGER DEFAULT 0;
alter table TASK drop primary key;
alter table TASK add TASKID int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY;
create unique index task_project_assignments on TASK(`TASKTYPEID`,`OBJECTID`,`ASSIGNEDTO`,`PROJECTID`,`OBJECTPART`);
-- 2018-08-05	... letting projects define their own stages of each task
ALTER TABLE PROJECT ADD TASKSTAGEDEFINITIONS varchar(100) DEFAULT NULL;

-- 2018-08-16	Migrated final odd columns out of DOCUMENT into FEATURES
alter table DOCUMENT drop column DESTROYED;
alter table DOCUMENT drop column FORMERGANUMBER;
alter table DOCUMENT drop column PALEMPSESTOTHER;
alter table DOCUMENT drop column PALEMPSESTUNDER;
alter table DOCUMENT drop column SUPPLEMENTOF;
alter table DOCUMENT drop column KLFOOTNOTES;
alter table DOCUMENT drop column FORMATSCROLL;
alter table DOCUMENT drop column COMMENT;
-- 2018-08-16	Increased FEATURE STRINGVAL size to allow larger values
alter table FEATURE modify STRINGVAL1 VARCHAR(500);
alter table FEATURE modify STRINGVAL2 VARCHAR(500);


-- 2018-11-19	Shrink TASK columns down to allow new encoding to be within primary key max length
alter table TASK modify OBJECTPART VARCHAR(80) NOT NULL DEFAULT '';
alter table PROJECT modify OBJECTPART VARCHAR(80) DEFAULT NULL;
alter table TASK modify CREATEDBY VARCHAR(80) NOT NULL DEFAULT '';
alter table TASK modify ASSIGNEDTO VARCHAR(80) NOT NULL DEFAULT '';
alter table REGULARIZATIONRULEEXCEPTION modify USERID VARCHAR(80) NOT NULL DEFAULT '';

-- 2018-11-19	Assure entire database is proper utf8mb4 encoding
alter database vmr CHARACTER SET = utf8mb4
alter table BIBLICALCONTENT convert to CHARACTER SET utf8mb4  COLLATE utf8mb4_unicode_ci;
alter table DOCUMENT convert to CHARACTER SET utf8mb4  COLLATE utf8mb4_unicode_ci;
alter table DOCUMENTGROUP convert to CHARACTER SET utf8mb4  COLLATE utf8mb4_unicode_ci;
alter table DOCUMENTGROUPDOCUMENT convert to CHARACTER SET utf8mb4  COLLATE utf8mb4_unicode_ci;
alter table FEATURE convert to CHARACTER SET utf8mb4  COLLATE utf8mb4_unicode_ci;
alter table FEATUREDEFINITION convert to CHARACTER SET utf8mb4  COLLATE utf8mb4_unicode_ci;
alter table INSTITUTION convert to CHARACTER SET utf8mb4  COLLATE utf8mb4_unicode_ci;
alter table PAGE convert to CHARACTER SET utf8mb4  COLLATE utf8mb4_unicode_ci;
alter table PROJECT convert to CHARACTER SET utf8mb4  COLLATE utf8mb4_unicode_ci;
alter table REGULARIZATIONRULE convert to CHARACTER SET utf8mb4  COLLATE utf8mb4_unicode_ci;
alter table REGULARIZATIONRULEEXCEPTION convert to CHARACTER SET utf8mb4  COLLATE utf8mb4_unicode_ci;
alter table SEGMENT convert to CHARACTER SET utf8mb4  COLLATE utf8mb4_unicode_ci;
alter table SEGMENTGROUPSEGMENT convert to CHARACTER SET utf8mb4  COLLATE utf8mb4_unicode_ci;
alter table SEGMENTREADING convert to CHARACTER SET utf8mb4  COLLATE utf8mb4_unicode_ci;
alter table SEGMENTREADINGWITNESS convert to CHARACTER SET utf8mb4  COLLATE utf8mb4_unicode_ci;
alter table SHELFINSTANCE convert to CHARACTER SET utf8mb4  COLLATE utf8mb4_unicode_ci;
alter table SHELFINSTANCEPAGES convert to CHARACTER SET utf8mb4  COLLATE utf8mb4_unicode_ci;
alter table SURROGATEIMAGE convert to CHARACTER SET utf8mb4  COLLATE utf8mb4_unicode_ci;
alter table SURROGATETRANSCRIPTION convert to CHARACTER SET utf8mb4  COLLATE utf8mb4_unicode_ci;
alter table TASK convert to CHARACTER SET utf8mb4  COLLATE utf8mb4_unicode_ci;
alter table TASKTYPE convert to CHARACTER SET utf8mb4  COLLATE utf8mb4_unicode_ci;
alter table TRANSCRIPTIONPAGE convert to CHARACTER SET utf8mb4  COLLATE utf8mb4_unicode_ci;
alter table TRANSCRIPTIONVERSE convert to CHARACTER SET utf8mb4  COLLATE utf8mb4_unicode_ci;

-- 2018-11-19	Add VERSEINSTANCE to segment reading to allow for multiple verse instances in a witness
alter table SEGMENTREADINGWITNESS add VERSEINSTANCE varchar(20) NOT NULL DEFAULT '' after DOCUMENTID;
alter table SEGMENTREADINGWITNESS drop primary key, add primary key(DOCUMENTID, VERSEINSTANCE, HAND, SEGMENTID, PROJECTID);

-- 2020-02-17	Add sublabel column for readings with sublabels, e.g., ao, df, etc.
alter table SEGMENTREADINGWITNESS add SUBLABEL varchar(12) NOT NULL DEFAULT '';


alter table SEGMENT modify WORDSTART int(11) DEFAULT '0';
alter table SEGMENT modify WORDEND int(11) DEFAULT '0';

-- 2020-05-21	Increase index string length
alter table PAGE modify INDEXSTRINGORIG varchar(200) DEFAULT NULL;

-- 2020-08-13	Added Columns Description for wordy scholars
alter table DOCUMENT add COLUMNSDESCRIPTION VARCHAR(50) AFTER LANGUAGE;

-- 2020-08-19	Increased storage size for first stringval field
alter table FEATURE modify STRINGVAL1 varchar(700);

-- 2020-12-08	Increased storage size for prose to use mediumtext
alter table FEATURE modify PROSE mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL;

-- 2020-12-23	Add GROUPID to FEATUREDEFINITION for expanding FDs to have group scope.
alter table FEATUREDEFINITION add GROUPID varchar(80) COLLATE utf8mb4_unicode_ci DEFAULT NULL after USERID;

-- 2020-03-03	Increase max size of stored user input string to 300 characters
alter table PAGE modify INDEXSTRINGORIG varchar(300) COLLATE utf8mb4_unicode_ci DEFAULT NULL;

-- 2020-03-04	Add V11N column to BIBLICALCONTENT table to facilitate a page having content from multiple v11n systems
alter table BIBLICALCONTENT add V11N varchar(60) COLLATE utf8mb4_unicode_ci DEFAULT NULL after PAGEID;

-- 2020-06-19	Add SIGLUMSUFFIX column to SEGMENTREADINGWITNESS table to facilitate manual override of a witness siglum suffix at a specific reading
alter table SEGMENTREADINGWITNESS add column SIGLUMSUFFIX VARCHAR(10) NOT NULL DEFAULT '';

-- 2020-07-28	Add USERID and CREATEDATE column to SEGMENTREADING and SEGMENTREADINGWITNESS tables to track who adds and when data
alter table SEGMENTREADING add `USERID` varchar(60) COLLATE utf8mb4_unicode_ci DEFAULT NULL;
alter table SEGMENTREADING add `CREATEDATE` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP;
alter table SEGMENTREADINGWITNESS add `USERID` varchar(60) COLLATE utf8mb4_unicode_ci DEFAULT NULL;
alter table SEGMENTREADINGWITNESS add `CREATEDATE` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP;

-- 2020-07-28	Add SIGLUMGROUPQUAL column to SEGMENTREADINGWITNESS tables to capture qualifications for group sigla
alter table SEGMENTREADINGWITNESS add SIGLUMGROUPQUAL VARCHAR(254) NOT NULL DEFAULT '';

-- 2020-08-10	Add READINGSUBTEXT column to SEGMENTREADING table to capture text to be displayed after the reading
alter table SEGMENTREADING add `READINGSUBTEXT` varchar(250) DEFAULT NULL after READING;

-- 2020-08-13	Add flags to ultimately replace SIGLUMSUFFIX column on SEGMENTREADINGWITNESS table to capture meaning of siglum qualifiers
alter table SEGMENTREADINGWITNESS add `REGULARIZED` tinyint(1) NOT NULL DEFAULT 0 after `UNSURE`;
alter table SEGMENTREADINGWITNESS add `SUPPLEMENT` tinyint(1) NOT NULL DEFAULT 0 after `REGULARIZED`;
alter table SEGMENTREADINGWITNESS add `NONSENSE` tinyint(1) NOT NULL DEFAULT 0 AFTER `SUPPLEMENT`;

-- 2020-08-13	Add ALTSUBLABELTRANSCRIPTION to SEGMENTREADINGWITNESS table to capture witnesses to a sublabel reading which isn't our transcription
-- this is when our witness was regularized to another sublabel.  To keep these straight in our head, here is an example:
-- reading 'a': λογεις
-- reading 'ao': λογης
-- us: TRANSCRIPTION: λογηις, we were regularize the ao reading, so ALTSUBLABELTRANSCRIPTION: λογης, REGULARIZED: true
-- thus we will display with reading 'ao' (instead of our own 'ao2') with an 'r' suffix on our siglum
alter table SEGMENTREADINGWITNESS add `ALTSUBLABELTRANSCRIPTION` varchar(250) COLLATE utf8mb4_unicode_ci DEFAULT NULL after `SUBLABEL`;

-- 2020-08-13	Add flags to SEGMENTREADINGWITNESS table to capture special omission, h and a
alter table SEGMENTREADINGWITNESS add `OMHAP` tinyint(1) NOT NULL DEFAULT 0 AFTER `NONSENSE`;
alter table SEGMENTREADINGWITNESS add `OMCOMABBR` tinyint(1) NOT NULL DEFAULT 0 AFTER `OMHAP`;

-- 2020-09-03	Disambiguated CONTENTOVERVIEW from SHELFCONTENTOVERVIEW within joins of DOCUMENT AND SHELFINSTANCE
alter table SHELFINSTANCE CHANGE COLUMN `CONTENTOVERVIEW` `SHELFCONTENTOVERVIEW` varchar(500);
-- 2020-09-03	Added more capacity for GANUMBER in anticaption of upcoming: "Unassigned Document within Shelf Number: xxxx"
alter table DOCUMENT CHANGE COLUMN `GANUMBER` `GANUMBER` varchar(240) NOT NULL DEFAULT '';
-- 2020-09-03	Changed INSTITUTION column GEONAME to GEOLOCATOR to be more generic
alter table INSTITUTION change column `GEONAME` `GEOLOCATOR` varchar(150) COLLATE utf8mb4_unicode_ci DEFAULT NULL;        

-- 2020-11-25	Add CHECKSUM to SEGMENTREADINGWITNESS to facilitate re-attaching orphans
alter table SEGMENTREADINGWITNESS add CHECKSUM VARCHAR(250);

-- 2020-11-25	Add an index to SEGMENTREADINGWITNESS to speed up status report
create index SEGMENTREADINGWITNESSSEGMENTID on SEGMENTREADINGWITNESS(SEGMENTID,SEGMENTREADINGID);

-- 2020-11-26	Add flag to SRW to allow designation of a major unsure (=INTF: 1 whole word or more)
alter table SEGMENTREADINGWITNESS add `UNSUREMAJOR` tinyint(1) NOT NULL DEFAULT 0 after `UNSURE`;

-- 2022-03-23	Add additional comment fields for SEGMENTREADINGWITNESS
alter table SEGMENTREADINGWITNESS add COMMENTSEGMENT mediumtext;
alter table SEGMENTREADINGWITNESS add COMMENTPRIVATE mediumtext;

-- 2022-03-23	Add PROJECTID to SEGMENTREADING so we can record and manage security for who added a reading
alter table SEGMENTREADING add `PROJECTID` int(11) NOT NULL DEFAULT '0' AFTER SEGMENTREADINGID;

-- TODO	support self and transcription features
-- FEATURE
--   `SHELFID` int(11) NOT DEFAULT NULL,
--   `TRANSCRIPTIONSTART` int(11) NOT DEFAULT NULL,
--   `TRANSCRIPTIONEND` int(11) NOT DEFAULT NULL,


-- 2022-05-25	Update CONTENTOVERVIEW to allow unlimited text
alter table DOCUMENT modify column `CONTENTOVERVIEW` text COLLATE utf8mb4_unicode_ci DEFAULT NULL;

ALTER TABLE DOCUMENT modify column GROUPID VARCHAR(80);

-- 2022-05-25	Increase size for INSTITUTE NAME, PLACE, and COUNTRY
drop index INSTITUTIONCOPLNM on INSTITUTION;
drop index INSTITUTIONPLNM on INSTITUTION;
drop index INSTITUTIONNM on INSTITUTION;
ALTER TABLE INSTITUTION modify column NAME VARCHAR(200);
ALTER TABLE INSTITUTION modify column PLACE VARCHAR(200);
ALTER TABLE INSTITUTION modify column COUNTRY VARCHAR(200);
create index `INSTITUTIONCOPLNM` on INSTITUTION(`COUNTRY`(191),`PLACE`(191),`NAME`(191));
create index `INSTITUTIONPLNM` on INSTITUTION(`PLACE`(191),`NAME`(191));
create index `INSTITUTIONNM` on INSTITUTION(`NAME`(191));

-- 2022-07-15	Update PROJECT to add a third document group for indexing tasks
alter table PROJECT add column DOCUMENTGROUPIDTERTIARY INTEGER after DOCUMENTGROUPIDSECONDARY;

-- 2022-07-15	Update SHELFINSTANCE to include CREATEDATE
ALTER TABLE SHELFINSTANCE ADD CREATEDATE timestamp default CURRENT_TIMESTAMP;

-- 2022-08-03	Update PAGE.FOLIONUMBER to increase the width for basetext book.chapter names
alter table PAGE modify FOLIONUMBER varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL;

-- 2022-08-10	Update PROJECT table to allow storage of both transcription and edition default text selections
alter table PROJECT ADD TRANSCRIPTIONBASEDEFAULT varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL;
alter table PROJECT ADD EDITIONBASEDEFAULT varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL;

-- 2022-08-10	Update PROJECT table to allow setting a default document restriction query
alter table PROJECT add DOCUMENTRESTRICTQUERY varchar(254) COLLATE utf8mb4_unicode_ci DEFAULT NULL;

-- 2022-11-26	Increase available size for image URLs
alter table SURROGATEIMAGE modify WEBFRIENDLYURL varchar(300) COLLATE utf8mb4_unicode_ci DEFAULT NULL;
alter table SURROGATEIMAGE modify SURROGATEURI varchar(300) COLLATE utf8mb4_unicode_ci DEFAULT NULL;

-- 2022-12-06	Change shelf instance folio nums default to be '' instead of NULL
alter table SHELFINSTANCEPAGES modify `FOLIONUMBER` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT '' NOT NULL;

-- 2024-03-12	Add field for apparatus attribute to project configuration
alter table PROJECT add column APPARATUSATTRIBUTION varchar(254) COLLATE utf8mb4_unicode_ci DEFAULT NULL after TRANSCRIPTIONATTRIBUTION;

-- 2024-05-24	Increase hand size
alter table SEGMENTREADINGWITNESS modify HAND varchar(15) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '';

-- 2024-07-31	Update SHELFCONTENTOVERVIEW to allow unlimited text, matching DOCUMENT.CONTENTOVERVIEW
alter table SHELFINSTANCE modify column `SHELFCONTENTOVERVIEW` text COLLATE utf8mb4_unicode_ci DEFAULT NULL;

-- 2024-07-31	Updated REGULARIZATION rule to add USERDATA column and normalize USERID length
alter table REGULARIZATIONRULE modify column USERID varchar(80);
alter table REGULARIZATIONRULE ADD USERDATA varchar(40) DEFAULT NULL;

-- 2024-08-05	Updated SEGMENTREADINGWITNESS to add LACWORDS column
alter table SEGMENTREADINGWITNESS add column LACWORDS varchar(24) COLLATE utf8mb4_unicode_ci DEFAULT NULL after OMCOMABBR;

-- 2024-10-18	Updated SHELFINSTANCE to more easily disambiguate 2 more column names which are also in DOCUMENT
alter table SHELFINSTANCE CHANGE COLUMN `LEAVESDESCRIPTION` `SHELFLEAVESDESCRIPTION` varchar(80) DEFAULT NULL;
alter table SHELFINSTANCE CHANGE COLUMN `LEAVES` `SHELFLEAVES` int(11) DEFAULT NULL;

-- 2025-02-28	Updated SURROGATEIMAGE to increase ATTRIBUTION FIELD to 600 characters
alter table SURROGATEIMAGE modify `ATTRIBUTION` varchar(600) DEFAULT NULL;

-- 2025-06-09	Updated PROJECT to add a new RESTRICTAPPARATUS field to be used for more permission control of apparatus display
alter table PROJECT add column `RESTRICTAPPARATUSID` int(11) DEFAULT 0;

-- 2025-08-11	New CONFIGURATION table for internalizing OpenSocial gadget configuration into the VMR
alter table PROJECT add column `RESTRICTAPPARATUSID` int(11) DEFAULT 0;
CREATE TABLE `CONFIGURATION` (
  `CLASS` varchar(60) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `INSTANCE` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `PROPERTY` varchar(60) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `VALUE` mediumtext COLLATE utf8mb4_unicode_ci,
  `USERID` varchar(60) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `CREATEDATE` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`CLASS`,`INSTANCE`,`PROPERTY`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


