<commit_before>
USE oimnew;

INSERT INTO authorization_type (SELECT auth_type_id,description FROM oim.authorization_type);

INSERT INTO contact_type (id,name)
	(SELECT type_id,description FROM oim.contact_type);
UPDATE contact_type SET allow_secondary=1 WHERE 
	id IN (SELECT type_id FROM oim.contact_type WHERE max_no_contacts>1);
UPDATE contact_type SET allow_tertiary=1 WHERE 
	id IN (SELECT type_id FROM oim.contact_type WHERE max_no_contacts>2);

INSERT INTO contact_rank (SELECT rank_id, description FROM oim.contact_rank);

INSERT INTO downtime_action (SELECT downtime_action_id,downtime_action_name,downtime_action_desc FROM oim.downtime_action);

INSERT INTO downtime_class (SELECT downtime_class_id,downtime_class_name,downtime_class_desc FROM oim.downtime_class);

INSERT INTO downtime_severity 
	(SELECT downtime_severity_id,downtime_severity_name,downtime_severity_wlcg_name,downtime_severity_desc FROM oim.downtime_severity);

INSERT INTO metric (SELECT metric_id,name,common_name,abbrev,description,time_interval,fresh_for,help_url,wlcg_metric_type FROM oim.metric);

INSERT INTO metric_status (SELECT metric_status_id,metric_status_description FROM oim.metric_status);

INSERT INTO osg_grid_type (SELECT * FROM oim.osg_grid_type);

INSERT INTO service_group (SELECT service_group_id, name, description FROM oim.service_group);

INSERT INTO service (SELECT service_id,name,description,port, 1 FROM oim.service);
UPDATE service svc SET svc.service_group_id=(SELECT service_group_id FROM oim.service_service_group ssgrp WHERE ssgrp.service_id=svc.id);

INSERT INTO metric_service (SELECT metric_id,service_id,critical FROM oim.metric_service);

INSERT INTO field_of_science (SELECT id,name FROM oim.field_of_science);

INSERT INTO facility (SELECT facility_id,name,description,active,disable FROM oim.facility);
INSERT INTO facility_contact (SELECT person_id,facility_id,type_id,rank_id FROM oim.facility_contact);

INSERT INTO sc (SELECT sc_id,short_name,long_name,description,community,active,disable,footprints_id FROM oim.supportcenter);

INSERT INTO vo
	(SELECT vo_id,short_name,long_name,description,
	primary_url,aup_url,membership_services_url,purpose_url,
	support_url,app_description,community,sc_id,
	active,disable,footprints_id
	FROM oim.virtualorganization);

INSERT INTO vo_vo (child_vo_id, parent_vo_id)
	(SELECT vo_id, parent_vo_id
	FROM oim.virtualorganization
	WHERE parent_vo_id IS NOT NULL);

INSERT INTO vo_field_of_science (SELECT vo_id,science_id FROM oim.vo_field_of_science);

INSERT INTO vo_report_name (SELECT id,reporting_name,vo_id FROM oim.vo_report_name);

INSERT INTO vo_report_name_fqan (vo_report_name_id, group_name, role) (SELECT vo_report_name_id, fqan, fqan FROM oim.vo_fqan);

-- Insert contact records without submiter_dn for now - will update later
INSERT INTO contact
	(id, name, primary_email, secondary_email,
	primary_phone, primary_phone_ext, secondary_phone, secondary_phone_ext,
	address_line_1, address_line_2, city, state, zipcode, country,
	active, disable)
	(SELECT person_id, CONCAT(first_name,' ', middle_name, ' ', last_name),
	primary_email, secondary_email,
	primary_phone, primary_phone_ext, secondary_phone, secondary_phone_ext, 
	address_line_1, address_line_2, city, state, zipcode, cntry.name,
	active, disable 
	FROM oim.person pers 
	LEFT JOIN oim.country cntry ON (cntry.ccode=pers.ccode)
);

UPDATE contact SET person=TRUE WHERE id IN 
	(SELECT person_id FROM oim.person WHERE group_contact = 0);
UPDATE contact SET person=FALSE WHERE id IN 
	(SELECT person_id FROM oim.person WHERE group_contact = 1);

-- Insert group_contacts into mailing_list
-- INSERT INTO mailing_list
--	(id, name, email)
--	(SELECT person_id, CONCAT(first_name, " ", last_name), primary_email
--	FROM oim.person pers
--	WHERE pers.group_contact=1);

INSERT INTO dn (id,dn_string,contact_id) (SELECT dn_id, dn_string, person_id FROM oim.certificate_dn);
UPDATE contact SET submitter_dn_id=(SELECT optional_submitter_dn_id FROM oim.person oldPerson WHERE oldPerson.person_id=contact.id);

INSERT INTO site 
	(SELECT site_id,site.name,long_name,description,
	address_line_1,address_line_2,city,state,zipcode, 
	Ctry.name, longitude,latitude,sc_id,facility_id,
	submitter_dn_id,active,disable <commit_msg>removed middle name from the contact migration. Footprint was bomindg because "Soichi  Hayashi" doesn't exist.<commit_after>
USE oimnew;

INSERT INTO authorization_type (SELECT auth_type_id,description FROM oim.authorization_type);

INSERT INTO contact_type (id,name)
	(SELECT type_id,description FROM oim.contact_type);
UPDATE contact_type SET allow_secondary=1 WHERE 
	id IN (SELECT type_id FROM oim.contact_type WHERE max_no_contacts>1);
UPDATE contact_type SET allow_tertiary=1 WHERE 
	id IN (SELECT type_id FROM oim.contact_type WHERE max_no_contacts>2);

INSERT INTO contact_rank (SELECT rank_id, description FROM oim.contact_rank);

INSERT INTO downtime_action (SELECT downtime_action_id,downtime_action_name,downtime_action_desc FROM oim.downtime_action);

INSERT INTO downtime_class (SELECT downtime_class_id,downtime_class_name,downtime_class_desc FROM oim.downtime_class);

INSERT INTO downtime_severity 
	(SELECT downtime_severity_id,downtime_severity_name,downtime_severity_wlcg_name,downtime_severity_desc FROM oim.downtime_severity);

INSERT INTO metric (SELECT metric_id,name,common_name,abbrev,description,time_interval,fresh_for,help_url,wlcg_metric_type FROM oim.metric);

INSERT INTO metric_status (SELECT metric_status_id,metric_status_description FROM oim.metric_status);

INSERT INTO osg_grid_type (SELECT * FROM oim.osg_grid_type);

INSERT INTO service_group (SELECT service_group_id, name, description FROM oim.service_group);

INSERT INTO service (SELECT service_id,name,description,port, 1 FROM oim.service);
UPDATE service svc SET svc.service_group_id=(SELECT service_group_id FROM oim.service_service_group ssgrp WHERE ssgrp.service_id=svc.id);

INSERT INTO metric_service (SELECT metric_id,service_id,critical FROM oim.metric_service);

INSERT INTO field_of_science (SELECT id,name FROM oim.field_of_science);

INSERT INTO facility (SELECT facility_id,name,description,active,disable FROM oim.facility);
INSERT INTO facility_contact (SELECT person_id,facility_id,type_id,rank_id FROM oim.facility_contact);

INSERT INTO sc (SELECT sc_id,short_name,long_name,description,community,active,disable,footprints_id FROM oim.supportcenter);

INSERT INTO vo
	(SELECT vo_id,short_name,long_name,description,
	primary_url,aup_url,membership_services_url,purpose_url,
	support_url,app_description,community,sc_id,
	active,disable,footprints_id
	FROM oim.virtualorganization);

INSERT INTO vo_vo (child_vo_id, parent_vo_id)
	(SELECT vo_id, parent_vo_id
	FROM oim.virtualorganization
	WHERE parent_vo_id IS NOT NULL);

INSERT INTO vo_field_of_science (SELECT vo_id,science_id FROM oim.vo_field_of_science);

INSERT INTO vo_report_name (SELECT id,reporting_name,vo_id FROM oim.vo_report_name);

INSERT INTO vo_report_name_fqan (vo_report_name_id, group_name, role) (SELECT vo_report_name_id, fqan, fqan FROM oim.vo_fqan);

-- Insert contact records without submiter_dn for now - will update later
INSERT INTO contact
	(id, name, primary_email, secondary_email,
	primary_phone, primary_phone_ext, secondary_phone, secondary_phone_ext,
	address_line_1, address_line_2, city, state, zipcode, country,
	active, disable)
	(SELECT person_id, CONCAT(first_name,' ', last_name),
	primary_email, secondary_email,
	primary_phone, primary_phone_ext, secondary_phone, secondary_phone_ext, 
	address_line_1, address_line_2, city, state, zipcode, cntry.name,
	active, disable 
	FROM oim.person pers 
	LEFT JOIN oim.country cntry ON (cntry.ccode=pers.ccode)
);

UPDATE contact SET person=TRUE WHERE id IN 
	(SELECT person_id FROM oim.person WHERE group_contact = 0);
UPDATE contact SET person=FALSE WHERE id IN 
	(SELECT person_id FROM oim.person WHERE group_contact = 1);

-- Insert group_contacts into mailing_list
-- INSERT INTO mailing_list
--	(id, name, email)
--	(SELECT person_id, CONCAT(first_name, " ", last_name), primary_email
--	FROM oim.person pers
--	WHERE pers.group_contact=1);

INSERT INTO dn (id,dn_string,contact_id) (SELECT dn_id, dn_string, person_id FROM oim.certificate_dn);
UPDATE contact SET submitter_dn_id=(SELECT optional_submitter_dn_id FROM oim.person oldPerson WHERE oldPerson.person_id=contact.id);

INSERT INTO site 
	(SELECT site_id,site.name,long_name,description,
	address_line_1,address_line_2,city,state,zipcode, 
	Ctry.name, longitude,latitude,sc_id,facility_id,
	submitter_dn_id,active,disable 
{'fraction_non_alphanumeric': 0.07391456175066931, 'fraction_numerical': 0.003259224770108253, 'mean_word_length': 7.830421377183967, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 7, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '889160', 'n_tokens_mistral': 2977, 'n_tokens_neox': 2812, 'n_words': 695}