-- Copyright [1999-2015] Wellcome Trust Sanger Institute and the EMBL-European Bioinformatics Institute
-- Copyright [2016-2019] EMBL-European Bioinformatics Institute
-- 
-- Licensed under the Apache License, Version 2.0 (the "License");
-- you may not use this file except in compliance with the License.
-- You may obtain a copy of the License at
-- 
--      http://www.apache.org/licenses/LICENSE-2.0
-- 
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.

--
-- Table structure for table `mage_xml`
--


CREATE TABLE `mage_xml` (
   `mage_xml_id` int(10) unsigned NOT NULL auto_increment,
   `xml` text,
   PRIMARY KEY  (`mage_xml_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

alter table experiment add `mage_xml_id` int(10) unsigned default NULL;
alter table feature_set add `name` varchar(40) default NULL;
alter table result_set add `name` varchar(40) default NULL;
alter table data_set add `name` varchar(40) default NULL;


alter table experimental_chip change replicate `biological_replicate` varchar(40) default NULL;
alter table experimental_chip add `technical_replicate` varchar(40) default NULL;

--now add names to import result sets manually, exp_name_IMPORT
--coalesce Stunneburg chip rsets, making sure displayable is only set for the correct replicate
--populate experimental_chip replicate fields appropriately

alter table experiment change date `date` date default '0000-00-00';
update meta set meta_value=45 where meta_key='schema_version';

-- add X and Y to result
alter table result add  `X` int(4) unsigned default NULL;
alter table result add  `Y` int(4) unsigned default NULL;


-- Need to update status for old ec and chans
-- select experimental_chip_id  from experimental_chip where experiment_id =12;

-- select e.name, rs.result_set_id from experiment e, result_set rs, experimental_chip ec, chip_channel cc where e.experiment_id=ec.experiment_id and ec.experiment_id>1 and ec.experiment_id <12 and ec.experimental_chip_id =cc.table_id and cc.table_name='experimental_chip' and cc.result_set_id=rs.result_set_id group by result_set_id;

--create table `tmp_chip_channel`(
 --  `chip_channel_id` int(10) unsigned NOT NULL auto_increment,
 --  `result_set_id` int(10) unsigned default '0',
 --  `table_id` int(10) unsigned NOT NULL,
 --  `table_name` varchar(20) NOT NULL,
 --  PRIMARY KEY  (`result_set_id`, `chip_channel_id`),
 --  UNIQUE KEY `rset_table_idname_idx` (`result_set_id`, `table_id`, `table_name`)
--) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--this primary key is not increenting the cc_id when we are using a different table_name
create table `tmp_chip_channel`(
   `chip_channel_id` int(10) unsigned NOT NULL auto_increment,
   `result_set_id` int(10) unsigned default '0',
   `table_id` int(10) unsigned NOT NULL,
   `table_name` varchar(20) NOT NULL,
   PRIMARY KEY  (`chip_channel_id`, `result_set_id`),
   UNIQUE KEY `rset_table_idname_idx` (`result_set_id`, `table_id`, `table_name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;



insert into tmp_chip_channel(select * from chip_channel);


---ctcf replicate chip_channel_id and name fix
---update chip_channel cc, tmp_chip_channel tcc set cc.chip_channel_id=tcc.chip_channel_id where tcc.table_name='experimental_chip' and cc.table_name='experimental_chip' and tcc.table_id=cc.table_id and tcc.result_set_id=17;
--update result_set set name=replace(name, 'SOM00H0', 'ctcf_ren');

drop table chip_channel;

rename table tmp_chip_channel to chip_channel;


-- rename biorep and techrep names as appropriate
--update result_set set name=replace(name, 'BIOREP', 'BR');
--update result_set set name=replace(name, 'techrep', 'TR');



alter table predicted_feature change display_label `display_label` varchar(60) default NULL;


---
-- Table structure for table `probe_design`
--

CREATE TABLE `probe_design` (
   `probe_id` int(10) unsigned NOT NULL default '0',
   `analysis_id` int(10) unsigned NOT NULL default '0',
   `score` double default NULL,	
   `coord_system_id` int(10) unsigned NOT NULL default '0',
    PRIMARY KEY  (`probe_id`, `analysis_id`, `coord_system_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;



-- make feature/set names unique key
-- can't do result set yet due to duplicate names of channel and chip IMPORT sets
create unique index `name_idx` on feature_set (name);
-- can't do this until we split the name of into a separate table

--create unique index `name_idx` on data_set (name);


--insert into cell_type values(NULL, 'CD4', NULL, 'Human CD4 T-Cells');
--update experimental_chip ec, cell_type ct set ec.cell_type_id=ct.cell_type_id where ct.name='CD4' and ec.unique_id='CD4_parzen_02';
--update experimental_chip ec, cell_type ct set ec.cell_type_id=ct.cell_type_id where ct.name='GM06996' and ec.unique_id='GM06990_parzen_0115';

--insert into feature_type values('', 'DNase', 'DNA', 'DNase Hypersensitive Site');
--update experimental_chip ec, feature_type ft set ec.feature_type_id=ft.feature_type_id where ft.name='DNase' and ec.unique_id='CD4_parzen_02';
--update experimental_chip ec, feature_type ft set ec.feature_type_id=ft.feature_type_id where ft.name='DNase' and ec.unique_id='GM06990_parzen_0115';



-- update  result_set rs, data_set ds set ds.name=rs.name where rs.result_set_id=ds.result_set_id;
-- update  feature_set fs, data_set ds set fs.name=ds.name where fs.feature_set_id=ds.feature_set_id;
-- insert into feature_type values ('', 'CTCF', 'INSULATOR', 'CCCCTC-binding factor');
-- update experimental_chip ec, experiment e, feature_type ft  set ec.feature_type_id=ft.feature_type_id where e.name='ctcf_ren' and e.experiment_id=ec.experiment_id and ft.name='CTCF';
-- insert into cell_type values ('', 'IMR90', '', 'Human Fetal Lung Fibroblast');
-- update cell_type set display_label =NULL where display_label='';
-- update experimental_chip ec, experiment e, cell_type ct  set ec.cell_type_id=ct.cell_type_id where e.name='ctcf_ren' and e.experiment_id=ec.experiment_id and ct.name='IMR90';



-- insert into cell_type values(NULL, 'HL-60', NULL, 'Hman promyelotic Leukemia Cells');
--  update feature_set set cell_type_id=6 where name like "Wiggle%";

drop index analysis_idx on result_set;
create unique index `name_analysis_idx` on result_set (name, analysis_id);


delete s from status s, status_name sn where sn.name='DISPLAYABLE' and sn.status_name_id=s.status_name_id;
insert into status(table_id, table_name, status_name_id) values(24, 'data_set', 2);
insert into status(table_id, table_name, status_name_id) values(27, 'feature_set', 2);

-- patch feature_set table to allow for longer names
ALTER TABLE feature_set CHANGE name `name` varchar(250) DEFAULT NULL;



--set on RegulatoryFeatures to displayble
delete s from status s, status_name sn where sn.name='DISPLAYABLE' and sn.status_name_id=s.status_name_id;
insert into status(table_id, table_name, status_name_id) values(24, 'data_set', 2);
insert into status(table_id, table_name, status_name_id) values(27, 'feature_set', 2);

--feature class update to facilitate get data set methods
update feature_type set class='REGULATORY FEATURE' where feature_type_id =19;

--feature type correction
update feature_type set name=replace(name, 'H3K20', 'H4K20');
update feature_set set name=replace(name, 'H3K20', 'H4K20');
update feature_type set class='HISTONE' where feature_type_id in(11,12,13,14); 
update feature_type set class='OVERLAP' where feature_type_id in(16, 17,18); 
update data_set set name ='Wiggle_H4K20me3' where name='H3K20me3_wiggle';



--CTCF displayable update
insert into status(table_id, table_name, status_name_id) select experimental_chip_id, 'experimental_chip', 2 from experimental_chip where experiment_id =12;
insert into status values(22, 'feature_set', 2);
insert into status values(22, 'data_set', 2);
insert into status values(18, 'result_set', 2);

--remove CTCF spurious data set and revert rset name back to original
delete from data_set where data_set_id=23;
update result_set set name = 'ctcf_ren_BR1' where name ='ctcf_ren_BR1_TR2';

-- correct ctcf analysis id
update feature_set set analysis_id =7 where analysis_id =14;



--- unique index on exp name
create unique index `name_idx` on experiment (name);


