 update interpro_prod.entry2method e2m_outer set e2m_outer.method_id=(select m_inner.method_id from interpro_prod.method m_inner where e2m_outer.method_ac=m_inner.method_ac);
 update interpro_prod.entry2method e2m_outer set e2m_outer.cv_evidence_id=(select cve_inner.cv_evidence_id from interpro_prod.cv_evidence cve_inner where e2m_outer.evidence=cve_inner.code);
 
-create table interpro_prod.entry2comp as select entry1_ac, entry2_ac, relation from interpro10.entry2comp limit 10000000;
+create table interpro_prod.entry2comp as select entry1_ac, entry2_ac, relation from interpro_src_data.entry2comp;
 alter table interpro_prod.entry2comp add column entry2comp_id INTEGER(32) AUTO_INCREMENT primary key first;
 alter table interpro_prod.entry2comp add column cv_relation_id INTEGER(32) after entry2comp_id;
 alter table interpro_prod.entry2comp add column entry1_id INTEGER(32) after cv_relation_id;

update interpro_prod.matches m_outer set m_outer.cv_evidence_id=(select cve_inner.cv_evidence_id from interpro_prod.cv_evidence cve_inner where m_outer.evidence=cve_inner.code);


create table interpro_prod.common_annotation as select ann_id, name, text, comments from interpro10.common_annotation limit 10000000;
alter table interpro_prod.common_annotation add column common_annotation_id INTEGER(32) AUTO_INCREMENT primary key first;


create table interpro_prod.entry2common_annotation as select entry_ac, ann_id, order_in from interpro10.entry2common limit 10000000;
alter table interpro_prod.entry2common_annotation add column entry2common_annotation_id INTEGER(32) AUTO_INCREMENT primary key first;
alter table interpro_prod.entry2common_annotation add column entry_id INTEGER(32) after entry2common_annotation_id;
alter table interpro_prod.entry2common_annotation add column common_annotation_id INTEGER(32) after entry_id;
update interpro_prod.entry2common_annotation e2ca_outer set e2ca_outer.entry_id=(select e_inner.entry_id from interpro_prod.entry e_inner where e2ca_outer.entry_ac=e_inner.entry_ac);
update interpro_prod.entry2common_annotation e2ca_outer set e2ca_outer.common_annotation_id=(select ca_inner.common_annotation_id from interpro_prod.common_annotation ca_inner where e2ca_outer.ann_id=ca_inner.ann_id);

create table interpro_prod.entry2cv_database as select entry_ac, dbcode, ac, name from interpro10.entry_xref limit 10000000;
alter table interpro_prod.entry2cv_database add column entry2cv_database_id INTEGER(32) AUTO_INCREMENT primary key first;
alter table interpro_prod.entry2cv_database add column entry_id INTEGER(32) after entry2cv_database_id;
alter table interpro_prod.entry2cv_database add column cv_database_id INTEGER(32) after entry_id;
update interpro_prod.entry2cv_database e2cvd_outer set e2cvd_outer.entry_id=(select e_inner.entry_id from interpro_prod.entry e_inner where e2cvd_outer.entry_ac=e_inner.entry_ac);
update interpro_prod.entry2cv_database e2cvd_outer set e2cvd_outer.cv_database_id=(select cvd_inner.cv_database_id from interpro_prod.cv_database cvd_inner where e2cvd_outer.dbcode=cvd_inner.dbcode);

create table interpro_prod.entry2method as select entry_ac, method_ac, evidence, ida from interpro10.entry2method limit 10000000;
alter table interpro_prod.entry2method add column entry2method_id INTEGER(32) AUTO_INCREMENT primary key first;
alter table interpro_prod.entry2method add column entry_id INTEGER(32) after entry2method_id;
alter table interpro_prod.entry2method add column method_id INTEGER(32) after entry_id;

drop schema interpro_prod cascade;
create schema interpro_prod;


create table interpro_prod.cv_entry_type as select code, abbrev, description from interpro_src_data.cv_entry_type;
alter table interpro_prod.cv_entry_type add column cv_entry_type_id INTEGER(32) AUTO_INCREMENT primary key first;

create table interpro_prod.cv_relation as select code, abbrev, description, forward, reverse from interpro_src_data.cv_relation;
alter table interpro_prod.cv_relation add column cv_relation_id INTEGER(32) AUTO_INCREMENT primary key first;

create table interpro_prod.cv_evidence as select code, abbrev, description from interpro_src_data.cv_evidence;
alter table interpro_prod.cv_evidence add column cv_evidence_id INTEGER(32) AUTO_INCREMENT primary key first;

create table interpro_prod.cv_database as select dbcode, dbname, dborder, dbshort from interpro_src_data.cv_database;
alter table interpro_prod.cv_database add column cv_database_id INTEGER(32) AUTO_INCREMENT primary key first;

create table interpro_prod.db_version as select dbcode, version, entry_count, file_date, load_date from interpro_src_data.db_version where dbcode in (select dbcode from interpro_prod.cv_database);
alter table interpro_prod.db_version add column db_version_id INTEGER(32) AUTO_INCREMENT primary key first;
alter table interpro_prod.db_version add column cv_database_id INTEGER(32) after db_version_id;
update interpro_prod.db_version dbv_outer set dbv_outer.cv_database_id=(select cvd_inner.cv_database_id from interpro_prod.cv_database cvd_inner where dbv_outer.dbcode=cvd_inner.dbcode);

create table interpro_prod.taxonomy as select DISTINCT tax_id as taxa_id from interpro_src_data.taxonomy2protein where tax_id in (6239,7165,7227,180454);
alter table interpro_prod.taxonomy add column taxonomy_id INTEGER(32) AUTO_INCREMENT primary key first;

create table interpro_prod.protein2taxonomy as select protein_ac, tax_id, hierarchy, tax_name_concat from interpro_src_data.taxonomy2protein where tax_id in (select taxa_id from interpro_prod.taxonomy);
alter table interpro_prod.protein2taxonomy add column protein2taxonomy_id INTEGER(32) AUTO_INCREMENT primary key first;
alter table interpro_prod.protein2taxonomy add column protein_id INTEGER(32) after protein2taxonomy_id;
alter table interpro_prod.protein2taxonomy add column taxonomy_id INTEGER(32) after protein_id;
create index idx_p2t_protein_ac on interpro_prod.protein2taxonomy(protein_ac);

create table interpro_prod.protein as select ip10.protein_ac, ip10.name, ip10.dbcode, ip10.crc64, ip10.len, ip10.fragment, ip10.struct_flag from interpro_src_data.protein ip10, interpro_prod.protein2taxonomy itp2t where ip10.protein_ac = itp2t.protein_ac;
alter table interpro_prod.protein add column protein_id INTEGER(32) AUTO_INCREMENT primary key first;
alter table interpro_prod.protein add column cv_database_id INTEGER(32) after protein_id;
create index idx_p_protein_ac on interpro_prod.protein(protein_ac);
