DROP TRIGGER IF EXISTS trigger_inc_ticket_usage_count;
CREATE  TRIGGER trigger_inc_ticket_usage_count
AFTER INSERT ON `door_log` FOR EACH ROW
  begin

    DECLARE p_count_all Integer;
    DECLARE p_count_all_2 Integer;
    DECLARE p_from DATETIME;
    DECLARE p_usage_count Integer;
    DECLARE p_max_usage_count Integer;
    DECLARE p_mo_ticket_id Integer;
    DECLARE p_mo_ticket_max_usage_count Integer;
    DECLARE p_allow_multiple_enter boolean;
    DECLARE p_allow_enter boolean;

    delete from devlog;

    IF NEW.id_customer is  not null and NEW.id_card is  not null
    THEN

      IF  (NEW.direction  = 7 or New.direction = 3 )  and NEW.id_ticket_current is not null
      then
        INSERT INTO devlog ( msg) values('belepes feldoglozas indit');

        select count(*) into @p_count_all from door_log where  created_at >= CURDATE() and  id_ticket_current  = New.id_ticket_current  and ( direction = 7 or direction = 3);
        INSERT INTO devlog ( msg) values( concat( 'count all' ,@p_count_all ) );


        IF @p_count_all = 1
        THEN

          select usage_count, max_usage_count into @p_usage_count ,@p_max_usage_count from ticket where id_ticket = NEW.id_ticket_current;


          update ticket set usage_count = usage_count +1 where id_ticket = NEW.id_ticket_current;


          INSERT INTO log (type,message, app, id_ticket, id_door_log,created_at, updated_at)
          values(
            30, concat('Bérlet használat (elotte: ',@p_usage_count, ' > utana: ' , @p_usage_count +1 , ' max: ', @p_max_usage_count, ')' ), ' trigger_inc_ticket',New.id_ticket_current, New.id_door_log,now(),now());
        else

          select     min(created_at) + INTERVAL  (3 * FLOOR(  ( ( HOUR( TIMEDIFF( min(created_at) , now() ) ) /3 ) ) ) ) hour as last_date
          into @p_from
          from door_log
          where created_at > CURDATE() and id_customer is not null and id_ticket_current = NEW.id_ticket_current and ( direction = 7 or direction = 3);


          select count(*) into @p_count_all_2 from door_log where  created_at >=  @p_from  and  id_ticket_current  = New.id_ticket_current  and ( direction = 7 or direction = 3);

          INSERT INTO devlog ( msg) values(CONCAT( 'Belépések száma az aktuális 3 órás intervalumban: ', @p_count_all_2)  );


          IF @p_count_all_2 = 1
          THEN
            INSERT INTO devlog ( msg) values( 'Az aktuális intervallumban ez az első belépés, usage_count növelése'  );

            select usage_count, max_usage_count into @p_usage_count ,@p_max_usage_count from ticket where id_ticket = NEW.id_ticket_current;

            update ticket set usage_count = usage_count +1 where id_ticket = New.id_ticket_current;

            INSERT INTO log (type,message, app, id_ticket, id_door_log,created_at, updated_at)
            values(
              40, concat('Bérlet használat/egy nap tobbszori (elotte: ',@p_usage_count, ' > utana: ' , @p_usage_count +1 , ' max: ', @p_max_usage_count, ')' ), ' trigger_inc_ticket',New.id_ticket_current, New.id_door_log,now(),now());
          END IF;
        END IF;
      End IF;


      IF NEW.direction  = 5 or New.direction = 1
      then
        INSERT INTO devlog ( msg) values('Kilépés van folyamatban, kilépések számának beállítása');
        update ticket set count_move_out = usage_count	 where id_ticket = NEW.id_ticket_current;
      END IF;

      INSERT INTO devlog ( msg) values( 'Kártya validáció módosítása'   );

      UPDATE card as c1
        left JOIN ( select  ticket.id_card as id_card , max(ticket.id_ticket) as id_ticket
                    from ticket
                    where ticket.start <= CURDATE()
                          and ticket.end >= curdate()
                          and ticket.status = 10
                          and ticket.count_move_out < ticket.max_usage_count
                          and ticket.id_card = New.id_card
                    group by id_card
                    order by id_card desc  ) as t
          on t.id_card = c1.id_card
      SET c1.validity  = case when t.id_card is null then ( c1.validity | 1 << 0  )  else ( c1.validity & ~(1 << 0)  )    end
        , c1.flag  = case when t.id_card is null then ( c1.flag | 1 << 0  )  else ( c1.flag & ~(1 << 0)  )    end
        , c1.id_ticket_current = case when t.id_ticket is null then null else t.id_ticket end
      WHERE c1.type <> 50 and c1.id_card = New.id_card;

      IF NEW.direction  = 5 or New.direction = 1
      then
        select   max(ticket.id_ticket)  into @p_mo_ticket_id
        from ticket
        where ticket.start <= CURDATE()
              and ticket.end >= curdate()
              and ticket.status = 10
              and ticket.count_move_out < ticket.max_usage_count
              and ticket.id_card = New.id_card
        group by id_card
        order by id_card desc;
        set @p_allow_enter = true;
        update card set
          flag_out =  ( flag_out | 1 << 1 )  ,
          flag  = case when @p_allow_enter then ( flag & ~(1 << 1)  )  else  ( flag | 1 << 1 )  end
        WHERE type <> 50 and id_card = New.id_card;
      END IF;
      IF  (NEW.direction  = 7 or New.direction = 3 )  and NEW.id_ticket_current is not null
      THEN
        update card set
          flag_out = ( flag_out & ~(1 << 1 )  ) ,
          flag =   ( flag | 1 << 1 )
        WHERE type <> 50 and id_card = New.id_card;
      END IF;
    END IF;
  END