BEGIN
    START TRANSACTION;

    -- Stage7
    DELETE FROM tbl_project_stg7 WHERE project_id = p_project_id;
    DELETE FROM tbl_project_stg7_closeout_summary WHERE project_id = p_project_id;
    DELETE FROM tbl_project_stg7_contractors WHERE project_id = p_project_id;
    DELETE FROM tbl_project_stg7_kpi_alignment WHERE project_id = p_project_id;

    -- Stage6
    DELETE FROM tbl_project_stg6_budget_items WHERE project_id = p_project_id;
    DELETE FROM tbl_project_stg6_construction_reports WHERE project_id = p_project_id;
    DELETE FROM tbl_project_stg6_kpis_master WHERE project_id = p_project_id;
    DELETE FROM tbl_project_stg6_production_reports WHERE project_id = p_project_id;
    DELETE FROM tbl_project_stg6_site_progress_report_data WHERE project_id = p_project_id;
    DELETE FROM tbl_project_stg6_workforce_and_equipement WHERE project_id = p_project_id;
    DELETE FROM tbl_stg6_kpis_master WHERE project_id = p_project_id;

    -- Risk Management
    DELETE FROM tbl_project_stg5_risk_management WHERE project_id = p_project_id;
    DELETE FROM tbl_project_risks WHERE project_id = p_project_id;
	
	-- project-planning
    DELETE FROM tbl_project_dept_phase_tasks WHERE project_id = p_project_id;
    DELETE FROM tbl_project_plan_data WHERE project_id = p_project_id;
	
	-- Stage5
    DELETE FROM tbl_project_stg5 WHERE project_id = p_project_id;
    DELETE FROM tbl_project_stg5_keys WHERE project_id = p_project_id;
    DELETE FROM tbl_project_stg5_locations WHERE project_id = p_project_id;
    DELETE FROM tbl_project_stg5_preferred_development_plan WHERE project_id = p_project_id;
    DELETE FROM tbl_project_stg5_quality_verified_by WHERE project_id = p_project_id;
    DELETE FROM tbl_project_stg5_risk_management WHERE project_id = p_project_id;
    DELETE FROM tbl_project_stg5_spatial_indicators_for_monitoring WHERE project_id = p_project_id;
    DELETE FROM tbl_project_stg5_stakeholders WHERE project_id = p_project_id;

    -- Stage4
    DELETE FROM tbl_project_stg4 WHERE project_id = p_project_id;
    DELETE FROM tbl_project_stg4_cost_benefit_analysis WHERE project_id = p_project_id;
    DELETE FROM tbl_project_stg4_cost_effectiveness_analysis WHERE project_id = p_project_id;
    DELETE FROM tbl_project_stg4_demand_forecasting WHERE project_id = p_project_id;
    DELETE FROM tbl_project_stg4_economic_multipliers WHERE project_id = p_project_id;
    DELETE FROM tbl_project_stg4_highlevel_cost WHERE project_id = p_project_id;
    DELETE FROM tbl_project_stg4_keys WHERE project_id = p_project_id;
    DELETE FROM tbl_project_stg4_legal_regulatory WHERE project_id = p_project_id;
    DELETE FROM tbl_project_stg4_macro_economic_benefits WHERE project_id = p_project_id;
    DELETE FROM tbl_project_stg4_option_analysis WHERE project_id = p_project_id;
    DELETE FROM tbl_project_stg4_revenue_model WHERE project_id = p_project_id;
    DELETE FROM tbl_project_stg4_risk_evaluation WHERE project_id = p_project_id;
    DELETE FROM tbl_project_stg4_departments WHERE project_id = p_project_id;
    
    -- Stage3
    DELETE FROM tbl_project_stg3 WHERE project_id = p_project_id;
    DELETE FROM tbl_project_stg3_geolocations WHERE project_id = p_project_id;
    DELETE FROM tbl_project_stg3_keys WHERE project_id = p_project_id;
    DELETE FROM tbl_project_stg3_gc_communities WHERE project_id = p_project_id;
    DELETE FROM tbl_project_stg3_gc_municipalities WHERE project_id = p_project_id;
    DELETE FROM tbl_project_stg3_gc_regions WHERE project_id = p_project_id;

    -- Stage2
    DELETE FROM tbl_project_stg2 WHERE project_id = p_project_id;
    DELETE FROM tbl_project_stg2_keys WHERE project_id = p_project_id;
    DELETE FROM tbl_project_stg2_locations WHERE project_id = p_project_id;

    -- Stage1
    DELETE FROM tbl_project_stg1 WHERE project_id = p_project_id;
    DELETE FROM tbl_project_stg1_keys WHERE project_id = p_project_id;
    DELETE FROM tbl_project_stg1_outcomes WHERE project_id = p_project_id;
    DELETE FROM tbl_project_departments WHERE project_id = p_project_id;

    -- Stage0
    DELETE FROM tbl_project_stg0_stakeholders WHERE project_id = p_project_id;
    DELETE FROM tbl_project_stg0 WHERE project_id = p_project_id;
    DELETE FROM tbl_project_stg0_objective WHERE project_id = p_project_id;

    -- documents
    DELETE d
    FROM tbl_documents d
    INNER JOIN tbl_project_docs pd ON d.doc_id = pd.doc_id
    WHERE pd.pr_id = p_project_id;

    DELETE FROM tbl_project_docs WHERE pr_id = p_project_id;

    -- Elevator pitch
    DELETE ept, ep FROM tbl_elevator_pitch ep
    LEFT JOIN tbl_elevator_pitch_timeline ept ON ept.row_id = ep.row_id
    WHERE ep.project_id = p_project_id;
    DELETE FROM tbl_elevator_pitch_key_parties WHERE project_id = p_project_id;
    DELETE FROM tbl_elevator_pitch_key_objective WHERE project_id = p_project_id;
    DELETE FROM tbl_elevator_pitch WHERE project_id = p_project_id;

    -- evaluators
    DELETE FROM tbl_project_evaluators WHERE project_id = p_project_id;
    DELETE FROM tbl_project_questions WHERE project_id = p_project_id;

    -- some project related tables
    DELETE FROM tbl_project_stages WHERE project_id = p_project_id;
    DELETE FROM tbl_project_stages_logs WHERE project_id = p_project_id;
    DELETE FROM tbl_project_users WHERE project_id = p_project_id;
    
    -- active stage step
    DELETE FROM tbl_frontend_active_stage WHERE project_id = p_project_id;
    
    -- Finally the project itself
    DELETE FROM tbl_projects WHERE project_id = p_project_id;

    COMMIT;
END