import { db } from "@/app/db/db";
import { sql } from "drizzle-orm";

export async function previewPropertyDeletion(propertyId: number) {
  const id = sql`${propertyId}`;

  const result = await db.execute(sql`
    -- ---------------------------------
    -- Recommendation chain
    -- ---------------------------------
    SELECT 'recommendation_materials' AS table, COUNT(*)::int AS count
    FROM recommendation_materials rm
    JOIN recommendation r ON rm.recommendation_id = r.id
    WHERE r.property_id = ${id}

    UNION ALL
    SELECT 'recommendation', COUNT(*)
    FROM recommendation
    WHERE property_id = ${id}

    -- ---------------------------------
    -- Funding / plan chain
    -- ---------------------------------
    UNION ALL
    SELECT 'funding_package_measures', COUNT(*)
    FROM funding_package_measures fpm
    JOIN funding_package fp ON fpm.funding_package_id = fp.id
    JOIN plan p ON fp.plan_id = p.id
    WHERE p.property_id = ${id}

    UNION ALL
    SELECT 'funding_package', COUNT(*)
    FROM funding_package fp
    JOIN plan p ON fp.plan_id = p.id
    WHERE p.property_id = ${id}

    UNION ALL
    SELECT 'plan_recommendations', COUNT(*)
    FROM plan_recommendations pr
    JOIN plan p ON pr.plan_id = p.id
    WHERE p.property_id = ${id}

    UNION ALL
    SELECT 'plan', COUNT(*)
    FROM plan
    WHERE property_id = ${id}

    -- ---------------------------------
    -- Property direct children
    -- ---------------------------------
    UNION ALL
    SELECT 'property_details_epc', COUNT(*)
    FROM property_details_epc
    WHERE property_id = ${id}

    UNION ALL
    SELECT 'property_targets', COUNT(*)
    FROM property_targets
    WHERE property_id = ${id}

    UNION ALL
    SELECT 'property_status_tracker', COUNT(*)
    FROM property_status_tracker
    WHERE property_id = ${id}

    UNION ALL
    SELECT 'inspections', COUNT(*)
    FROM inspections
    WHERE property_id = ${id}

    UNION ALL
    SELECT 'files_from_surveyor', COUNT(*)
    FROM files_from_surveyor
    WHERE property_id = ${id}

    -- ---------------------------------
    -- Root
    -- ---------------------------------
    UNION ALL
    SELECT 'property', COUNT(*)
    FROM property
    WHERE id = ${id};
  `);

  return result.rows;
}

export async function deleteProperty(propertyId: number) {
  await db.transaction(async (tx) => {
    // -----------------------------
    // LEAF TABLES FIRST
    // -----------------------------

    await tx.execute(sql`
      DELETE FROM funding_package_measures fpm
      USING funding_package fp, plan p
      WHERE fpm.funding_package_id = fp.id
        AND fp.plan_id = p.id
        AND p.property_id = ${propertyId};
    `);

    await tx.execute(sql`
      DELETE FROM recommendation_materials rm
      USING recommendation r
      WHERE rm.recommendation_id = r.id
        AND r.property_id = ${propertyId};
    `);

    // -----------------------------
    // PLAN CHILDREN
    // -----------------------------

    await tx.execute(sql`
      DELETE FROM funding_package fp
      USING plan p
      WHERE fp.plan_id = p.id
        AND p.property_id = ${propertyId};
    `);

    await tx.execute(sql`
      DELETE FROM plan_recommendations pr
      USING plan p
      WHERE pr.plan_id = p.id
        AND p.property_id = ${propertyId};
    `);

    // -----------------------------
    // PROPERTY DIRECT CHILDREN
    // -----------------------------

    await tx.execute(sql`
      DELETE FROM property_details_epc
      WHERE property_id = ${propertyId};
    `);

    await tx.execute(sql`
      DELETE FROM property_targets
      WHERE property_id = ${propertyId};
    `);

    await tx.execute(sql`
      DELETE FROM property_status_tracker
      WHERE property_id = ${propertyId};
    `);

    await tx.execute(sql`
      DELETE FROM inspections
      WHERE property_id = ${propertyId};
    `);

    await tx.execute(sql`
      DELETE FROM files_from_surveyor
      WHERE property_id = ${propertyId};
    `);

    await tx.execute(sql`
      DELETE FROM recommendation
      WHERE property_id = ${propertyId};
    `);

    await tx.execute(sql`
      DELETE FROM plan
      WHERE property_id = ${propertyId};
    `);

    // -----------------------------
    // PROPERTY LAST (ALWAYS)
    // -----------------------------

    await tx.execute(sql`
      DELETE FROM property
      WHERE id = ${propertyId};
    `);
  });
}
export async function previewPlanDeletion(planId: number) {
  const result = await db.execute(sql`
    -- ---------------------------------
    -- Recommendation materials
    -- ---------------------------------
    SELECT 'recommendation_materials' AS table, COUNT(*)::int AS count
    FROM recommendation_materials rm
    JOIN recommendation r ON rm.recommendation_id = r.id
    JOIN plan_recommendations pr ON pr.recommendation_id = r.id
    WHERE pr.plan_id = ${planId}

    UNION ALL
    -- ---------------------------------
    -- Recommendations
    -- ---------------------------------
    SELECT 'recommendation', COUNT(*)::int
    FROM recommendation r
    JOIN plan_recommendations pr ON pr.recommendation_id = r.id
    WHERE pr.plan_id = ${planId}

    UNION ALL
    -- ---------------------------------
    -- Plan recommendations
    -- ---------------------------------
    SELECT 'plan_recommendations', COUNT(*)::int
    FROM plan_recommendations
    WHERE plan_id = ${planId}

    UNION ALL
    -- ---------------------------------
    -- Funding chain
    -- ---------------------------------
    SELECT 'funding_package_measures', COUNT(*)::int
    FROM funding_package_measures fpm
    JOIN funding_package fp ON fpm.funding_package_id = fp.id
    WHERE fp.plan_id = ${planId}

    UNION ALL
    SELECT 'funding_package', COUNT(*)::int
    FROM funding_package
    WHERE plan_id = ${planId}

    UNION ALL
    -- ---------------------------------
    -- Root
    -- ---------------------------------
    SELECT 'plan', COUNT(*)::int
    FROM plan
    WHERE id = ${planId};
  `);

  return result.rows;
}
export async function deletePlan(planId: number) {
  await db.transaction(async (tx) => {
    // ---------------------------------
    // Recommendation materials (LEAF)
    // ---------------------------------
    await tx.execute(sql`
      DELETE FROM recommendation_materials rm
      USING recommendation r, plan_recommendations pr
      WHERE rm.recommendation_id = r.id
        AND r.id = pr.recommendation_id
        AND pr.plan_id = ${planId};
    `);

    // ---------------------------------
    // Plan recommendations (FK OWNER)
    // ---------------------------------
    await tx.execute(sql`
      DELETE FROM plan_recommendations
      WHERE plan_id = ${planId};
    `);

    // ---------------------------------
    // Recommendations (NOW SAFE)
    // ---------------------------------
    await tx.execute(sql`
      DELETE FROM recommendation r
      WHERE r.id NOT IN (
        SELECT recommendation_id FROM plan_recommendations
      )
      AND r.id IN (
        SELECT recommendation_id
        FROM plan_recommendations
        WHERE plan_id = ${planId}
      );
    `);

    // ---------------------------------
    // Funding chain
    // ---------------------------------
    await tx.execute(sql`
      DELETE FROM funding_package_measures fpm
      USING funding_package fp
      WHERE fpm.funding_package_id = fp.id
        AND fp.plan_id = ${planId};
    `);

    await tx.execute(sql`
      DELETE FROM funding_package
      WHERE plan_id = ${planId};
    `);

    // ---------------------------------
    // Root (LAST)
    // ---------------------------------
    await tx.execute(sql`
      DELETE FROM plan
      WHERE id = ${planId};
    `);
  });
}

// Find All foregin keys used for 'property' table with id via this command
// SELECT
//   tc.table_name,
//   kcu.column_name,
//   ccu.table_name AS foreign_table_name,
//   ccu.column_name AS foreign_column_name
// FROM information_schema.table_constraints AS tc
// JOIN information_schema.key_column_usage AS kcu
//   ON tc.constraint_name = kcu.constraint_name
// JOIN information_schema.constraint_column_usage AS ccu
//   ON ccu.constraint_name = tc.constraint_name
// WHERE tc.constraint_type = 'FOREIGN KEY'
//   AND ccu.table_name = 'property';
