# リファクタ前後のDB移行

create database npartner_tmp;
exit

mysql -uroot -p npartner_tmp < ~/Downloads/リファクタ後用ダンプ1.sql

-- food_recommends
ALTER TABLE food_recommends
ADD `hearing_id` int DEFAULT NULL,
ADD UNIQUE KEY `food_recommends_hearing_id_key` (`hearing_id`),
ADD CONSTRAINT `food_recommends_hearing_id_fkey` FOREIGN KEY (`hearing_id`) REFERENCES hearings (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
drop column user_id,
DROP FOREIGN KEY food_recommends_user_id_fkey,
DROP INDEX food_recommends_user_id_fkey;

update food_recommends r set hearing_id = (select id from hearings h where h.food_recommend_id = r.id);


-- cooking_recommends
ALTER TABLE cooking_recommends
ADD `hearing_id` int DEFAULT NULL,
ADD UNIQUE KEY `cooking_recommends_hearing_id_key` (`hearing_id`),
ADD CONSTRAINT `cooking_recommends_hearing_id_fkey` FOREIGN KEY (`hearing_id`) REFERENCES hearings (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
drop column user_id,
DROP FOREIGN KEY cooking_recommends_user_id_fkey,
DROP INDEX cooking_recommends_user_id_fkey;

update cooking_recommends r set hearing_id = (select id from hearings h where h.food_recommend_id = r.id);

-- exercise_recommends
ALTER TABLE exercise_recommends
ADD `hearing_id` int DEFAULT NULL,
ADD UNIQUE KEY `exercise_recommends_hearing_id_key` (`hearing_id`),
ADD CONSTRAINT `exercise_recommends_hearing_id_fkey` FOREIGN KEY (`hearing_id`) REFERENCES hearings (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
drop column user_id,
DROP FOREIGN KEY exercise_recommends_user_id_fkey,
DROP INDEX exercise_recommends_user_id_fkey;

update exercise_recommends r set hearing_id = (select id from hearings h where h.exercise_recommend_id = r.id);

-- sleeping_recommends
ALTER TABLE sleeping_recommends
ADD `hearing_id` int DEFAULT NULL,
ADD UNIQUE KEY `sleeping_recommends_hearing_id_key` (`hearing_id`),
ADD CONSTRAINT `sleeping_recommends_hearing_id_fkey` FOREIGN KEY (`hearing_id`) REFERENCES hearings (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
drop column user_id,
DROP FOREIGN KEY sleeping_recommends_user_id_fkey,
DROP INDEX sleeping_recommends_user_id_fkey;

update sleeping_recommends r set hearing_id = (select id from hearings h where h.sleeping_recommend_id = r.id);

-- diagnoses
alter table diagnoses add (
  `q_breakfast_type_other` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `q_dinner_type_other` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `q_lunch_type_other` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `tmp_q_snack_time` json DEFAULT NULL
);

update diagnoses
set q_breakfast_type = case when q_breakfast_type in ("自炊", "中食", "外食") OR q_breakfast_type is NULL then q_breakfast_type
                            else "その他" end,
q_breakfast_type_other = case when q_breakfast_type in ("自炊", "中食", "外食") OR q_breakfast_type is NULL then NULL
                            else q_breakfast_type end,
q_lunch_type = case when q_lunch_type in ("自炊", "中食", "外食") OR q_lunch_type is NULL then q_lunch_type
                            else "その他" end,
q_lunch_type_other = case when q_lunch_type in ("自炊", "中食", "外食") OR q_lunch_type is NULL then NULL
                            else q_lunch_type end,
q_dinner_type = case when q_dinner_type in ("自炊", "中食", "外食") OR q_dinner_type is NULL then q_dinner_type
                            else "その他" end,
q_dinner_type_other = case when q_dinner_type in ("自炊", "中食", "外食") OR q_dinner_type is NULL then NULL
                            else q_dinner_type end,
tmp_q_snack_time = case when q_snack_time is not NULL then cast(q_snack_time as JSON)
                        else NULL end;

alter table diagnoses drop column q_snack_time;

alter table diagnoses rename column tmp_q_snack_time to q_snack_time;

-- hearing group
alter table hearing_groups drop column name;

-- hearing
alter table hearings add (
  `tmp_food_products_order` json DEFAULT NULL,
  `tmp_cookings_order` json DEFAULT NULL,
  `tmp_exercises_order` json DEFAULT NULL,
  `tmp_sleepings_order` json DEFAULT NULL,
  `closer_report` text COLLATE utf8mb4_unicode_ci
);
alter table hearings drop column next_consultation_date;
update hearings
set tmp_food_products_order = case when food_products_order is not null then cast(food_products_order as JSON) else NULL end,
tmp_cookings_order = case when cookings_order is not null then cast(cookings_order as JSON) else NULL end,
tmp_exercises_order = case when exercises_order is not null then cast(exercises_order as JSON) else NULL end,
tmp_sleepings_order = case when sleepings_order is not null then cast(sleepings_order as JSON) else NULL end;

alter table hearings
drop column food_products_order,
drop column cookings_order,
drop column exercises_order,
drop column sleepings_order;

alter table hearings
rename column tmp_food_products_order to food_products_order,
rename column tmp_cookings_order to cookings_order,
rename column tmp_exercises_order to exercises_order,
rename column tmp_sleepings_order to sleepings_order;


-- constraint drop
ALTER TABLE hearings
DROP COLUMN food_recommend_id,
DROP COLUMN exercise_recommend_id,
DROP COLUMN sleeping_recommend_id,
DROP COLUMN cooking_recommend_id,
DROP COLUMN consultation_type_id,
DROP FOREIGN KEY hearings_food_recommend_id_fkey,
DROP FOREIGN KEY hearings_exercise_recommend_id_fkey,
DROP FOREIGN KEY hearings_sleeping_recommend_id_fkey,
DROP FOREIGN KEY hearings_cooking_recommend_id_fkey,
DROP FOREIGN KEY hearings_consultation_type_id_fkey,
DROP INDEX hearings_food_recommend_id_fkey,
DROP INDEX hearings_exercise_recommend_id_fkey,
DROP INDEX hearings_sleeping_recommend_id_fkey,
DROP INDEX hearings_cooking_recommend_id_fkey,
DROP INDEX hearings_consultation_type_id_fkey;

-- assignments
alter table assignments drop column is_valid;
-- WARNING: 抜けてたので追加！反映済み
alter table assignments
add `value` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL;

-- reserved_times
ALTER TABLE reserved_times
DROP COLUMN last_hearing_id,
DROP FOREIGN KEY reserved_times_last_hearing_id_fkey,
DROP INDEX reserved_times_last_hearing_id_key;

-- patients
alter table patients add (
  `created_by` int DEFAULT NULL
);

ALTER TABLE patients
DROP FOREIGN KEY patients_user_id_fkey,
DROP INDEX patients_user_id_fkey;


update patients set created_by = user_id;

-- user
alter table users drop column role;
alter table users add (
  `tmp_gender` enum('MALE','FEMALE','OTHER') COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `hashed_access_token` text COLLATE utf8mb4_unicode_ci,
  `tmp_operated_months` json DEFAULT NULL
);

update users set tmp_gender = case when gender in ("MALE", "男性") then "MALE"
                                  when gender in ("FEMALE", "女性") then "FEMALE"
                                  when gender = "OTHER" then "OTHER"
                                  else NULL end,
tmp_operated_months = case when operated_months is not null then cast(operated_months as JSON)
                          else NULL end;

alter table users
drop column gender,
drop column operated_months;

alter table users
rename column tmp_gender to gender,
rename column tmp_operated_months to operated_months;

-- limited_urls
alter table limited_urls drop foreign key limited_urls_healtu_insurance_union_id_fkey;
ALTER TABLE limited_urls CHANGE healtu_insurance_union_id health_insurance_union_id INT;
ALTER TABLE limited_urls
ADD CONSTRAINT `limited_urls_health_insurance_union_id_fkey` FOREIGN KEY (`health_insurance_union_id`)
REFERENCES `health_insurance_unions` (`id`)
ON DELETE SET NULL
ON UPDATE CASCADE;

-- deleted_reservations
CREATE TABLE `deleted_reservations` (
  `id` int NOT NULL AUTO_INCREMENT,
  `created_at` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `start_time` datetime(6) NOT NULL,
  `end_time` datetime(6) NOT NULL,
  `reservation_number` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `is_online` tinyint(1) NOT NULL DEFAULT '0',
  `consultation_type_id` int NOT NULL,
  `cancellation_token` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `patient_id` int NOT NULL,
  `assigner_id` int DEFAULT NULL,
  `assignee_id` int NOT NULL,
  `delete_user_id` int DEFAULT NULL,
  `delete_patient_id` int DEFAULT NULL,
  `health_insurance_union_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `deleted_reservations_reservation_number_key` (`reservation_number`),
  UNIQUE KEY `deleted_reservations_cancellation_token_key` (`cancellation_token`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--hearings
ALTER TABLE hearings ADD UNIQUE KEY `hearings_consultation_date_hearing_group_id_key` (`consultation_date`, `hearing_group_id`);

// jsonにcastしたデータを整形
// import { PrismaClient } from '@prisma/client';
const { PrismaClient } = require("@prisma/client");
const prisma = new PrismaClient();

async function main() {
  // q_snack_timeの修正
  const diagnoses = await prisma.diagnosis.findMany({
    where: { q_snack_time: { not: null } },
  });

  for (let i = 0; i < diagnoses.length; i++) {
    console.log(i);
    const diagnosis = diagnoses[i];
    const fixedSnackTime = diagnosis.q_snack_time
      .filter((time) => time)
      .map((time) => time.match(/T(.+)\./)[1]);
    await prisma.diagnosis.update({
      where: { id: diagnosis.id },
      data: { q_snack_time: fixedSnackTime.length ? fixedSnackTime : null },
    });
  }

  // hearingsのjsonにcastしたやつ
  const hearings = await prisma.hearing.findMany();

  for (let i = 0; i < hearings.length; i++) {
    console.log(i);
    const hearing = hearings[i];
    const fixedFoodOrder = hearing.food_products_order?.map((order) =>
      Number(order)
    );
    const fixedCookingOrder = hearing.cookings_order?.map((order) =>
      Number(order)
    );
    const fixedExerciseOrder = hearing.exercises_order?.map((order) =>
      Number(order)
    );
    const fixedSleepingOrder = hearing.sleepings_order?.map((order) =>
      Number(order)
    );
    await prisma.hearing.update({
      where: { id: hearing.id },
      data: {
        food_products_order: fixedFoodOrder,
        cookings_order: fixedCookingOrder,
        exercises_order: fixedExerciseOrder,
        sleepings_order: fixedSleepingOrder,
      },
    });
  }
}

main()
  .catch((e) => {
    throw e;
  })
  .finally(async () => {
    await prisma.$disconnect();
  });

DELETE FROM favorite_food_products
WHERE food_product_id IN (
    SELECT id
    FROM food_products
    WHERE is_deleted = true
);
DELETE FROM favorite_cookings
WHERE cooking_id IN (
    SELECT id
    FROM cookings
    WHERE is_deleted = true
);
DELETE FROM favorite_exercises
WHERE exercise_id IN (
    SELECT id
    FROM exercises
    WHERE is_deleted = true
);
DELETE FROM favorite_sleepings
WHERE sleeping_id IN (
    SELECT id
    FROM sleepings
    WHERE is_deleted = true
);

delete from assignments where is_deleted=true;
alter table assignments drop column is_deleted;
delete from available_times where is_deleted=true;
alter table available_times drop column is_deleted;
delete from certifications where is_deleted=true;
alter table certifications drop column is_deleted;
delete from consultation_types where is_deleted=true;
alter table consultation_types drop column is_deleted;
delete from cooking_groups where is_deleted=true;
alter table cooking_groups drop column is_deleted;
delete from cooking_main_types where is_deleted=true;
alter table cooking_main_types drop column is_deleted;
delete from cooking_recommends where is_deleted=true;
alter table cooking_recommends drop column is_deleted;
delete from cooking_types where is_deleted=true;
alter table cooking_types drop column is_deleted;
delete from cookings where is_deleted=true;
alter table cookings drop column is_deleted;
delete from custom_roles where is_deleted=true;
alter table custom_roles drop column is_deleted;
delete from diagnoses where is_deleted=true;
alter table diagnoses drop column is_deleted;
delete from diets where is_deleted=true;
alter table diets drop column is_deleted;
delete from exercise_recommends where is_deleted=true;
alter table exercise_recommends drop column is_deleted;
delete from exercises where is_deleted=true;
alter table exercises drop column is_deleted;
delete from expenses where is_deleted=true;
alter table expenses drop column is_deleted;
delete from experienced_fields where is_deleted=true;
alter table experienced_fields drop column is_deleted;
delete from food_categories where is_deleted=true;
alter table food_categories drop column is_deleted;
delete from food_main_categories where is_deleted=true;
alter table food_main_categories drop column is_deleted;
delete from food_main_stores where is_deleted=true;
alter table food_main_stores drop column is_deleted;
delete from food_product_tags where is_deleted=true;
alter table food_product_tags drop column is_deleted;
delete from food_products where is_deleted=true;
alter table food_products drop column is_deleted;
delete from food_recommends where is_deleted=true;
alter table food_recommends drop column is_deleted;
delete from food_stores where is_deleted=true;
alter table food_stores drop column is_deleted;
delete from health_insurance_unions where is_deleted=true;
alter table health_insurance_unions drop column is_deleted;
delete from hearing_groups where is_deleted=true;
alter table hearing_groups drop column is_deleted;
delete from hearings where is_deleted=true;
alter table hearings drop column is_deleted;
delete from licenses where is_deleted=true;
alter table licenses drop column is_deleted;
delete from limited_urls where is_deleted=true;
alter table limited_urls drop column is_deleted;
delete from patient_tags where is_deleted=true;
alter table patient_tags drop column is_deleted;
delete from patients where is_deleted=true;
alter table patients drop column is_deleted;
delete from payments where is_deleted=true;
alter table payments drop column is_deleted;
delete from reservation_tokens where is_deleted=true;
alter table reservation_tokens drop column is_deleted;
delete from reserved_times where is_deleted=true;
alter table reserved_times drop column is_deleted;
delete from sleeping_recommends where is_deleted=true;
alter table sleeping_recommends drop column is_deleted;
delete from sleepings where is_deleted=true;
alter table sleepings drop column is_deleted;
delete from union_available_time_filters where is_deleted=true;
alter table union_available_time_filters drop column is_deleted;
delete from user_tags where is_deleted=true;
alter table user_tags drop column is_deleted;
delete from users where is_deleted=true;
alter table users drop column is_deleted;