DROP FUNCTION IF EXISTS STATISTICS;
CREATE FUNCTION STATISTICS(uid INT)
RETURNS TEXT
BEGIN

  DECLARE area_number INT DEFAULT 1;
  DECLARE total_areas INT DEFAULT 0;
  DECLARE completed_areas INT DEFAULT 0;
  DECLARE price_ft DECIMAL(5,3) DEFAULT 0.00;
  DECLARE price_fm DECIMAL(5,3) DEFAULT 0.00;
  DECLARE report_json TEXT DEFAULT '{}';


  SELECT COUNT(id) FROM associations INTO total_areas;

  SET price_ft = (SELECT price FROM price WHERE name='tel');
  SET price_fm = (SELECT price FROM price WHERE name='mail');

  SELECT JSON_INSERT(report_json, '$.currently_work_area', (SELECT association_id FROM dfb_league_clubs WHERE ticket IS NOT NULL ORDER BY id DESC LIMIT 1)) INTO report_json;
  SELECT JSON_INSERT(report_json, '$.currently_work_area_name', (SELECT gebiet FROM dfb_leagues WHERE id=(SELECT association_id FROM dfb_league_clubs WHERE ticket IS NOT NULL ORDER BY id DESC LIMIT 1))) INTO report_json;

  SELECT JSON_INSERT(report_json, '$.my_paid', (SELECT SUM(paid) FROM user_paid WHERE for_user=uid)) INTO report_json;

  WHILE area_number <= total_areas DO

  SET @current_area_name = (SELECT name FROM associations WHERE id=area_number );
  SET @total_league_in_area = (SELECT COUNT(id) FROM dfb_leagues WHERE association_id=area_number);
  SET @completed_league_in_area = (SELECT COUNT(id) FROM dfb_leagues WHERE association_id=area_number AND (completed=1 OR error=1));

  SET @area_start_date    = (SELECT begin_update FROM dfb_league_clubs WHERE association_id=area_number ORDER BY id ASC LIMIT 1);
  SET @area_end_date      = (SELECT end_update FROM dfb_league_clubs WHERE association_id=area_number AND completed=1 ORDER BY id DESC LIMIT 1);
  SET @area_missing_page  = (SELECT COUNT(id) FROM dfb_league_clubs WHERE association_id=area_number AND (to_facebook=1 OR rating_no_page=1) AND completed=1);
  SET @area_start_date_f  = '';
  SET @area_end_date_f    = '';
  SET @area_total_day     = '';
  SET @area_total_work_time = '';
  IF( @area_start_date IS NOT NULL AND @area_end_date IS NOT NULL ) THEN
    SET @area_start_date_f  = (SELECT DATE_FORMAT(@area_start_date, '%d.%m.%Y'));
    SET @area_end_date_f    = (SELECT DATE_FORMAT(@area_end_date, '%d.%m.%Y'));
    SET @area_total_day     = (SELECT DATEDIFF( @area_end_date, @area_start_date ));
    SET @area_total_work_time = (SELECT DATEDIFF( @area_end_date, @area_start_date ));
  END IF ;

  SET @total_club_in_area     = (SELECT COUNT(id) FROM dfb_league_clubs WHERE association_id=area_number);
  SET @completed_club_in_area = (SELECT COUNT(id) FROM dfb_league_clubs WHERE completed=1 AND association_id=area_number);
  SET @completed_club_in_area_with_email = (SELECT COUNT(id) FROM dfb_league_clubs WHERE association_id=area_number AND completed=1 AND email IS NOT NULL);

  SET @added_contact_in_area  = (SELECT COUNT(id) FROM club_contacts WHERE association_id=area_number);



  SET @added_contact_with_tel  = (SELECT COUNT(id) FROM club_contacts WHERE association_id=area_number AND mobil IS NOT NULL);
  SET @added_contact_with_mail = (SELECT COUNT(id) FROM club_contacts WHERE association_id=area_number AND email IS NOT NULL);
  SET @added_contact_with_combine = (SELECT COUNT(id) FROM club_contacts WHERE association_id=area_number AND email IS NOT NULL AND mobil IS NOT NULL);


  SET @my_added_contact           = (SELECT COUNT(id) FROM club_contacts WHERE association_id=area_number AND user_id=uid);
  SET @my_completed_clubs         = (SELECT COUNT(id) FROM dfb_league_clubs WHERE association_id=area_number AND user_id=uid AND completed=1);

  SET @my_added_clubs_with_mail   = (SELECT COUNT(id) FROM dfb_league_clubs WHERE association_id=area_number AND user_id=uid AND completed=1 AND email IS NOT NULL);
  SET @my_added_contact_with_tel  = (SELECT COUNT(id) FROM club_contacts WHERE association_id=area_number AND user_id=uid AND mobil IS NOT NULL);
  SET @my_added_contact_with_mail = (SELECT COUNT(id) FROM club_contacts WHERE association_id=area_number AND user_id=uid AND email IS NOT NULL);
  SET @my_added_contact_with_combine = (SELECT COUNT(id) FROM club_contacts WHERE association_id=area_number AND user_id=uid AND email IS NOT NULL AND mobil IS NOT NULL);

  SET @json_string = CONCAT(
      '{
      "name":"', @current_area_name,'",
      "total_league":"', @total_league_in_area,'",
      "completed_league":"', @completed_league_in_area,'",
      "total_club":"', @total_club_in_area,'",
      "completed_club":"', @completed_club_in_area,'",
      "completed_club_in_area_with_email":"', @completed_club_in_area_with_email,'",
      "area_missing_page":"', @area_missing_page,'",
      "area_start_date":"', @area_start_date_f,'",
      "area_end_date":"', @area_end_date_f,'",
      "area_work_day":"', @area_total_day,'",
      "total_contact":"', @added_contact_in_area,'",
      "added_contact_with_tel":"', @added_contact_with_tel,'",
      "added_contact_with_mail":"', @added_contact_with_mail,'",
      "added_contact_with_combine":"', @added_contact_with_combine,'",
      "my_completed_clubs":"', @my_completed_clubs,'",
      "my_added_clubs_with_mail":"', @my_added_clubs_with_mail,'",
      "my_added_contact":"', @my_added_contact,'",
      "my_added_contact_with_tel":"', @my_added_contact_with_tel,'",
      "my_added_contact_with_mail":"', @my_added_contact_with_mail,'",
      "my_added_contact_with_combine":"', @my_added_contact_with_combine,'"
      }'
    );


  SELECT JSON_MERGE(report_json, @json_string) INTO report_json;

  SET area_number = area_number + 1;


  END WHILE;

  RETURN report_json;

END;