DROP TABLE IF EXISTS rlcos_data;

CREATE TABLE rlcos_data (
    id int(11),     
    name varchar(255), 
    category_id int(11), 
    category_id_name varchar(255), 
    department_id int(11), 
    department_id_name varchar(255), 
    latitude varchar(255),
    longitude varchar(255),
    main_info_video_path varchar(255),
    main_info_photo_path varchar(255),
    requirment_info_photo_path varchar(255),
    document_info_photo_path varchar(255),
    registration_num varchar(255),
    process_time varchar(255),
    process_time_unit varchar(255),
    contact_num varchar(255),
    fees varchar(255),
    renewal_period varchar(255),
    renewal_period_unit varchar(255),
    address varchar(255),
    doc_requirements text
);

LOAD DATA LOCAL INFILE 'docs/rlcos-data.csv' INTO TABLE rlcos_data FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;

TRUNCATE rlcos;
INSERT INTO rlcos (id, name, category_id, category_id_name, department_id, department_id_name, latitude, longitude, main_info_video_path, main_info_photo_path, requirment_info_photo_path, document_info_photo_path, registration_num, process_time, process_time_unit, contact_num, fees, renewal_period, renewal_period_unit, address, doc_requirements)
SELECT id, name, category_id, category_id_name, department_id, department_id_name, latitude, longitude, main_info_video_path, main_info_photo_path, requirment_info_photo_path, document_info_photo_path, registration_num, process_time, process_time_unit, contact_num, fees, renewal_period, renewal_period_unit, address, doc_requirements from rlcos_data;

update rlcos set main_info_video_path="rlcos/2/led-001.ffc8d9fc98d41c12ec46.mp4", requirment_info_photo_path="rlcos/2/labourAndHumanResource.945f6acd071cf1bf047e.PNG" where id%2==0
update rlcos set main_info_video_path="rlcos/8/LIcensetomanufacturedrug.596c413bca8f66153551.mp4", requirment_info_photo_path="rlcos/8/HealthDepartment.d8d6c2ef08b308b9b02e.PNG" where id=id%2==0
update rlcos set doc_requirements = REPLACE(doc_requirements,"â–ª", " • ");
# TRUNCATE lookup_categories;
#INSERT INTO lookup_categories (name, photo_path) SELECT DISTINCT category_id, category_id_name FROM rlcos;

UPDATE lookup_categories s, rlcos t SET t.category_id=s.id WHERE s.name=t.category_id_name;

# CHECK ALL CATEGORIES ASSIGNED
SELECT DISTINCT category_id_name FROM rlcos WHERE category_id NOT IN (SELECT id FROM lookup_categories); 


TRUNCATE lookup_departments;
INSERT INTO lookup_departments (name, latitude, longitude, is_active) SELECT DISTINCT department_id_name, latitude, longitude, 1 FROM rlcos;

UPDATE lookup_departments s, rlcos t SET t.department_id=s.id WHERE s.name=t.department_id_name;

# CHECK ALL DEPARTMENTS ASSIGNED
SELECT DISTINCT department_id_name FROM rlcos WHERE department_id NOT IN (SELECT id FROM lookup_departments); 


TRUNCATE department_category;
INSERT INTO department_category(category_id, department_id) SELECT DISTINCT category_id, department_id FROM rlcos;


DROP TABLE IF EXISTS rlcos_document_data;

CREATE TABLE rlcos_document_data (
    rlcos_id int(11),     
    name varchar(255), 
    document_path varchar(255)
);

LOAD DATA LOCAL INFILE 'docs/   rlcos-document-data.csv' INTO TABLE rlcos_document_data FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;

INSERT INTO rlcos_documents (rlcos_id, document_path) SELECT DISTINCT rlcos_id, document_path FROM rlcos_document_data;


mysqldump -uroot --no-create-info click_api rlcos > database\sql\rlcos.sql
mysqldump -uroot --no-create-info click_api rlcos_documents > database\sql\rlcos_documents.sql
mysqldump -uroot --no-create-info click_api lookup_departments > database\sql\lookup_departments.sql
mysqldump -uroot --no-create-info click_api department_category > database\sql\department_category.sql
mysqldump -uroot --no-create-info click_api lookup_rlcos_fee_types > database\sql\lookup_rlcos_fee_types.sql
mysqldump -uroot --no-create-info click_api rlcos_fees > database\sql\rlcos_fees.sql