/*PGR-GNU*****************************************************************
File: optimize.sql

Copyright (c) 2021 pgRouting developers
Mail: project@pgrouting.org

Function's developer:
Copyright (c) 2021 Celia Virginia Vergara Castillo
Copyright (c) 2021 Joseph Emile Honour Percival

------

This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation; either version 2 of the License, or
(at your option) any later version.

This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU General Public License for more details.

You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software
Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.

 ********************************************************************PGR-GNU*/

CREATE OR REPLACE PROCEDURE vrp_optimize(
  TEXT, -- orders sql (required)
  TEXT, -- vehicles sql (required)
  TEXT, -- time matrix sql (required)
  TEXT, -- time dependencies SQL
  vehicles_tbl REGCLASS, -- vehicles table to update

  execution_date TIMESTAMP,
  factor         FLOAT,
  max_cycles     INTEGER
)
AS
$BODY$
DECLARE
  curr_time RECORD;
  curr_vehicles BIGINT[];
  prev_vehicles BIGINT[];
  ok_orders BIGINT [];

  orders_sql      TEXT;
  vehicles_sql    TEXT;

BEGIN
  DROP TABLE IF EXISTS __results;


  /** cycle to extract the times where the vehicle opens or ends **/
  FOR curr_time IN EXECUTE format($$
    SELECT s_tw_open AS start_time
      FROM (%1$s) a
      WHERE date_trunc('day', %2$L::TIMESTAMP) <= s_tw_open

      UNION

      SELECT e_tw_close
      FROM  (%1$s) b
      WHERE date_trunc('day', %2$L::TIMESTAMP) <= s_tw_open

      ORDER BY start_time $$,
      $2, execution_date)
  LOOP

    IF (curr_time.start_time < date_trunc('day', execution_date)) THEN
      RAISE EXCEPTION 'NOT OPTIMIZING % Belongs to the past, should never appear because the cycle does not allow to  optimize on the past', curr_time;
      CONTINUE;
    END IF;

    SELECT array_agg(id) FROM _vrp_vehiclesAtTime($2, curr_time.start_time, dryrun => false) INTO curr_vehicles;

    IF prev_vehicles = curr_vehicles THEN
      RAISE DEBUG 'NOT OPTIMIZING  %   vehicles % set of vehicles did not change', curr_time, curr_vehicles;
      CONTINUE;
    END IF;


    SELECT cmd FROM _vrp_vehiclesAtTime($2, curr_time.start_time, dryrun => true) LIMIT 1 INTO vehicles_sql;


    EXECUTE format($$
      WITH
      v AS (%1$s),
      orders_q AS (
          SELECT unnest(stops) AS orders_id FROM v
      )
      SELECT array_agg(DISTINCT orders_id)
      FROM orders_q WHERE orders_id IS NOT NULL
      $$,
      vehicles_sql)
    INTO ok_orders;


    orders_sql = format($$
      SELECT *
      FROM (%1$s) a
      WHERE id ::BIGINT IN (SELECT unnest('%2$s'::BIGINT[]))
      $$,
      $1, ok_orders);

    IF ok_orders IS NULL THEN
      RAISE WARNING 'NOT OPTIMIZING % No orders found %', curr_time, curr_vehicles;
      CONTINUE;
    END IF;


    RAISE NOTICE 'OPTIMIZING time:%   vehicles %', curr_time, curr_vehicles;

    BEGIN
      CREATE TEMP TABLE __results ON COMMIT DROP AS
      SELECT *
      FROM vrp_pickDeliver(
        orders_sql,
        vehicles_sql,
        $3,
        $4,
        execution_date,
        optimize => true,
        factor => factor,
        max_cycles => max_cycles,
        stop_on_all_served => false);

      EXCEPTION WHEN OTHERS THEN
          RAISE WARNING 'COULD NOT OPTIMIZE %', curr_time;
          RAISE WARNING 'SQLERRM %', SQLERRM;
          CONTINUE;
    END;

    EXECUTE format($$
      WITH
      new_stops AS (
          SELECT vehicle_id AS id, array_remove(array_agg(shipment_id ORDER BY seq)::BIGINT[],-1::BIGINT) AS stops
          FROM __results
          GROUP BY vehicle_id
      )
      UPDATE %1$I AS v
        SET stops = n.stops
        FROM new_stops AS n WHERE v.id = n.id
        $$, vehicles_tbl);

    COMMIT;

    prev_vehicles := curr_vehicles;
  END LOOP;

END;
$BODY$
LANGUAGE plpgsql;

COMMENT ON PROCEDURE vrp_optimize(TEXT, TEXT, TEXT, TEXT, REGCLASS, TIMESTAMP, FLOAT, INTEGER)
IS 'vrp_optimize
- Documentation:
  - ${PROJECT_DOC_LINK}/vrp_optimize.html
';