import { QueryInterface, QueryTypes } from "sequelize";

const date = new Date();

interface universalIdentifier {
  id: number;
}

const deckRanks = [
  { name: "Master", sort_order: 1 },
  { name: "Chief Officer", sort_order: 2 },
  { name: "Second Officer", sort_order: 3 },
  { name: "Third Officer", sort_order: 4 },
  { name: "Radio Officer", sort_order: 5 },
  { name: "Deck Cadet", sort_order: 6 },
  { name: "Trainee Cadet", sort_order: 7 },
  { name: "Bosun", sort_order: 8 },
  { name: "Deck Fitter", sort_order: 9 },
  { name: "AB", sort_order: 10 },
  { name: "OS", sort_order: 11 },
  { name: "GP", sort_order: 12 },
  { name: "Crane Operator", sort_order: 13 },
  { name: "Junior Officer", sort_order: 14 },
  { name: "Pumpman", sort_order: 15 },
  { name: "Fitters, Oilers", sort_order: 16 },
  { name: "Fitter", sort_order: 17 },
];

const engineRanks = [
  { name: "Chief Engineer", sort_order: 18 },
  { name: "Second Engineer", sort_order: 19 },
  { name: "Third Engineer", sort_order: 20 },
  { name: "Fourth Engineer", sort_order: 21 },
  { name: "Fifth Engineer", sort_order: 22 },
  { name: "Electrical Engineer", sort_order: 23 },
  { name: "Electrical Officer", sort_order: 24 },
  { name: "Electro Technical Officer", sort_order: 25 },
  { name: "Junior Engineer", sort_order: 26 },
  { name: "Asst. Electrical Officer", sort_order: 27 },
  { name: "Trainee Marine Engineer", sort_order: 28 },
  { name: "Reefer Engineer", sort_order: 29 },
  { name: "Reefer Mechanic", sort_order: 30 },
  { name: "Gas Engineer", sort_order: 31 },
  { name: "Engine Fitter", sort_order: 32 },
  { name: "Motorman", sort_order: 33 },
  { name: "Wiper", sort_order: 34 },
  { name: "Travel Fitter", sort_order: 35 },
  { name: "Travel Wiper", sort_order: 36 },
  { name: "Pielstik Engineer", sort_order: 37 },
  { name: "Cadet(Nautical Science)", sort_order: 38 },
  { name: "Other Senior Engineering Officers", sort_order: 39 },
  { name: "Deck Rating", sort_order: 40 },
  { name: "Seaman", sort_order: 41 },
  { name: "A B", sort_order: 42 },
  { name: "Ordinary Seaman", sort_order: 43 },
  { name: "Second Officer (NCV)", sort_order: 44 },
  { name: "Other Engineering Officers", sort_order: 45 },
  { name: "Other Deck/Nautical Officers", sort_order: 46 },
  { name: "Trainee Deck Rating", sort_order: 47 },
  {
    name: "Watchkeeping Engineer Officer (NCV) Watchkeeping Engineer Officer (NCV) OICEW",
    sort_order: 48,
  },
  { name: "Watch Keeping Deck Officer OINW or Third officer", sort_order: 49 },
  { name: "Watch Keeping Deck Officer (NCV) OINW", sort_order: 50 },
  { name: "Other Senior Deck/Nautical Officers", sort_order: 51 },
  { name: "Trainee Engine Rating", sort_order: 52 },
  { name: "Watch keeping Motor Man", sort_order: 53 },
  { name: "Electrical/Electronics Officer", sort_order: 54 },
  { name: "Assitant Electrical/Electronics Officer", sort_order: 55 },
  { name: "Deck Watchkeeping Rating", sort_order: 56 },
  { name: "Chief Engineer (NCV)", sort_order: 57 },
  { name: "Oiler", sort_order: 58 },
  { name: "Engine Rating", sort_order: 59 },
];

export default {
  up: async (queryInterface: QueryInterface) => {
    const categoryTypes = (await queryInterface.sequelize.query(
      `SELECT id FROM category_types WHERE code = 'rank' LIMIT 1`,
      { type: QueryTypes.SELECT },
    )) as universalIdentifier[];

    if (categoryTypes.length === 0) {
      console.log(
        'Category type "rank" not found. Run category-types seeder first.',
      );
      return;
    }

    const categoryTypeId = categoryTypes[0].id;

    const languages = (await queryInterface.sequelize.query(
      `SELECT id FROM languages WHERE code = 'en' LIMIT 1`,
      { type: QueryTypes.SELECT },
    )) as universalIdentifier[];

    if (languages.length === 0) {
      console.log('Language "en" not found.');
      return;
    }

    const languageId = languages[0].id;

    // Check if Department categories already exist
    let deckDept = (await queryInterface.sequelize.query(
      `SELECT id FROM categories WHERE code = 'deck' AND category_type_id = ${categoryTypeId} LIMIT 1`,
      { type: QueryTypes.SELECT },
    )) as universalIdentifier[];

    if (deckDept.length === 0) {
      await queryInterface.bulkInsert("categories", [
        {
          category_type_id: categoryTypeId,
          code: "deck",
          level: 1,
          status: 1,
          created_at: date,
          updated_at: date,
        },
      ]);
      deckDept = (await queryInterface.sequelize.query(
        `SELECT id FROM categories WHERE code = 'deck' LIMIT 1`,
        { type: QueryTypes.SELECT },
      )) as universalIdentifier[];

      await queryInterface.bulkInsert("category_content", [
        {
          category_id: deckDept[0].id,
          language_id: languageId,
          name: "Deck",
          created_at: date,
          updated_at: date,
        },
      ]);
    }

    let engineDept = (await queryInterface.sequelize.query(
      `SELECT id FROM categories WHERE code = 'engine' AND category_type_id = ${categoryTypeId} LIMIT 1`,
      { type: QueryTypes.SELECT },
    )) as universalIdentifier[];

    if (engineDept.length === 0) {
      await queryInterface.bulkInsert("categories", [
        {
          category_type_id: categoryTypeId,
          code: "engine",
          level: 1,
          status: 1,
          created_at: date,
          updated_at: date,
        },
      ]);
      engineDept = (await queryInterface.sequelize.query(
        `SELECT id FROM categories WHERE code = 'engine' LIMIT 1`,
        { type: QueryTypes.SELECT },
      )) as universalIdentifier[];

      await queryInterface.bulkInsert("category_content", [
        {
          category_id: engineDept[0].id,
          language_id: languageId,
          name: "Engine",
          created_at: date,
          updated_at: date,
        },
      ]);
    }

    const insertRanks = async (ranks: any[], parentId: number | null) => {
      for (const rank of ranks) {
        const parentQuery = parentId
          ? `AND c.parent_id = ${parentId}`
          : `AND c.parent_id IS NULL`;
        const existing = (await queryInterface.sequelize.query(
          `SELECT c.id FROM categories c 
                     JOIN category_content cc ON c.id = cc.category_id 
                     WHERE cc.name = '${rank.name.replace(/'/g, "''")}' ${parentQuery} LIMIT 1`,
          { type: QueryTypes.SELECT },
        )) as universalIdentifier[];

        if (existing.length === 0) {
          const code = rank.name
            .toLowerCase()
            .replace(/ /g, "_")
            .replace(/[^a-z0-9_]/g, "");
          const level = parentId ? 2 : 1;
          await queryInterface.bulkInsert("categories", [
            {
              category_type_id: categoryTypeId,
              parent_id: parentId,
              code: code,
              level: level,
              sort_order: rank.sort_order,
              status: 1,
              created_at: date,
              updated_at: date,
            },
          ]);

          const newCategory = (await queryInterface.sequelize.query(
            `SELECT id FROM categories WHERE code = '${code}' ${parentId ? `AND parent_id = ${parentId}` : `AND parent_id IS NULL`} LIMIT 1`,
            { type: QueryTypes.SELECT },
          )) as universalIdentifier[];

          await queryInterface.bulkInsert("category_content", [
            {
              category_id: newCategory[0].id,
              language_id: languageId,
              name: rank.name,
              created_at: date,
              updated_at: date,
            },
          ]);
        }
      }
    };

    await insertRanks(deckRanks, deckDept[0].id);
    await insertRanks(engineRanks, engineDept[0].id);

    const otherRanks = [
      { name: "Captain", sort_order: 100 },
      { name: "Ship Management", sort_order: 101 },
    ];
    await insertRanks(otherRanks, null);
  },

  down: async (queryInterface: QueryInterface) => {
    // This is complex for a seeder down, typically you'd delete by categoryTypeId
    const categoryTypes = (await queryInterface.sequelize.query(
      `SELECT id FROM category_types WHERE code = 'rank' LIMIT 1`,
      { type: QueryTypes.SELECT },
    )) as universalIdentifier[];

    if (categoryTypes.length > 0) {
      const categoryTypeId = categoryTypes[0].id;
      await queryInterface.sequelize.query(
        `DELETE FROM category_content WHERE category_id IN (SELECT id FROM categories WHERE category_type_id = ${categoryTypeId})`,
      );
      await queryInterface.sequelize.query(
        `DELETE FROM categories WHERE category_type_id = ${categoryTypeId}`,
      );
    }
  },
};