.separator ' '

create table if not exists work_limits(

rows integer, cols integer, cars integer,

rides integer, bonus integer, steps integer

);

create table if not exists booked_rides_tmp(

x1 integer, y1 integer,

x2 integer, y2 integer,

ride_start integer, ride_end integer

);

.import 'a_example.in' booked_rides_tmp

.h on

insert into work_limits select * from booked_rides_tmp where rowid=1;

delete from booked_rides_tmp where rowid = 1;

select * from work_limits;

create table if not exists booked_rides(

id integer primary key

,x1 integer

,y1 integer

,x2 integer

,y2 integer

,ride_start integer

,ride_end integer

,assigned_car integer default 0

,assigned_at_step integer

,waiting_steps integer -- if positive the car need wait, negative the passenger need wait

,distance_to_start integer -- car distance between last position to the start of the assigned ride

);

insert into booked_rides(x1, y1, x2, y2, ride_start, ride_end)

select x1, y1, x2, y2, ride_start, ride_end

from booked_rides_tmp;

drop table booked_rides_tmp;

create table if not exists cars(

id integer primary key

,x2 integer not null default 0

,y2 integer not null default 0

,ride_end integer not null default 0

,ride_id integer

);

WITH RECURSIVE

cnt(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM cnt WHERE x<(select cars from work_limits))

insert into cars(id) SELECT x FROM cnt;

select ('Cars table has ' || count(*) || ' cars') cars_count from cars;

create table if not exists car_rides(

id integer primary key,

car_id integer not null,

ride_id integer not null,

unique(car_id, ride_id)

);

create view if not exists booked_rides_list_view AS

select tbl.*,

(case when

(tbl.ride_start >= start_distance)

or ((tbl.ride_end-tbl.ride_size) >= start_distance)

then 1 else 0 end) possible

from (

SELECT

a.id,

a.x1,

a.y1,

a.x2,

a.y2,

a.ride_start,

a.ride_end,

(abs(x1-x2) + abs(y1-y2)) ride_size,

(ride_end - ride_start) max_time,

a.assigned_car,

a.assigned_at_step,

a.distance_to_start,

a.waiting_steps,

(x1 + y1) start_distance

FROM booked_rides AS a

Order by ride_start, ride_size desc, start_distance

) tbl;

create view if not exists booked_rides_extended_list_view as

select *,

(case when (waiting_steps > 0) then waiting_steps else 0 end) waiting_steps,

(case when (waiting_steps < 0) then (-waiting_steps) else 0 end) delay_steps

from booked_rides_list_view;

create view if not exists booked_rides_result_view AS

select 'all', count(*) count, sum(ride_size) total_size,

sum(distance_to_start) total_between_rides,

sum(waiting_steps) total_waiting_steps,

sum(delay_steps) total_delay_steps

from booked_rides_extended_list_view

select 'possible', count(*) count, sum(ride_size) total_size,

sum(distance_to_start) total_between_rides,

sum(waiting_steps) total_waiting_steps,

sum(delay_steps) total_delay_steps

from booked_rides_extended_list_view where possible=1

select 'assigned', count(*) count, sum(ride_size) total_size,

sum(distance_to_start) total_between_rides,

sum(waiting_steps) total_waiting_steps,

sum(delay_steps) total_delay_steps

from booked_rides_extended_list_view where assigned_car > 0

select 'ontime', count(*) count, sum(ride_size) total_size,

sum(distance_to_start) total_between_rides,

sum(waiting_steps) total_waiting_steps,

sum(delay_steps) total_delay_steps

from booked_rides_extended_list_view where assigned_car > 0 and ride_start=assigned_at_step;

create view if not exists car_stats_view as

select assigned_car, count(*) total_rides,

max(assigned_at_step) max_assigned_at_step,

sum(distance_to_start) total_distance_to_start,

sum(waiting_steps) total_waiting_steps,

sum(delay_steps) total_delay_steps

from booked_rides_extended_list_view

group by assigned_car;

create view if not exists calculate_score_view as

select (select sum(ride_size) total_size

from booked_rides_extended_list_view

where assigned_car > 0)

+ ((select count(*) count

from booked_rides_extended_list_view

where assigned_car > 0 and ride_start=assigned_at_step)

* (select bonus from work_limits)) score;

create view if not exists car_rides_result_list_view as

(select count(*) || ' ' || group_concat(the_ride_id, ' ')

from (select ride_id-1 the_ride_id -- the result want a zero based id

from car_rides where car_id=a.id

order by id

) tbl

) result_rides

from cars a

order by id;

create table ride_assign_tmp_calculations(

id integer primary key,

--car_id integer, ride_id integer, --to debug

distance_to_start integer not null default 0,

new_car_end integer not null default 0,

waiting_steps integer not null default 0,

assigned_at_step integer not null default 0

);

insert into ride_assign_tmp_calculations(id) values(1);

create view if not exists find_nearest_available_car_view as

select id, ride_end, x2, y2,

1 as target_x1, 1 as target_y1,

1 as target_x2, 1 as target_y2,

1 as target_ride_id, 1 as target_ride_start,

1 as target_ride_end , 1 as target_ride_size

from cars;

create trigger if not exists find_nearest_available_car_trigger instead of update

on find_nearest_available_car_view

-- old.* references cars record

-- new.* references booked_ride record passed in dummy fields

--hack simulation of variables using a dummy table

--how far we are to the begining of the new ride ?

update ride_assign_tmp_calculations set

distance_to_start = (abs(new.target_x1-old.x2) + abs(new.target_y1-old.y2))

--,car_id=old.id, ride_id=new.target_ride_id --to debug

where id=1;

--incrementally we'll be calculating the new_car_end

update ride_assign_tmp_calculations set

new_car_end = old.ride_end + distance_to_start

where id=1;

--will we need to wait to start the ride ontime ?

--if result is negative we'll be late, passenger will need to wait

update ride_assign_tmp_calculations set

waiting_steps = new.target_ride_start - new_car_end

where id=1;

--if we need to wait let's add it to the new_car_end

--time unit is equal to steps unit

update ride_assign_tmp_calculations set

new_car_end = new_car_end + waiting_steps

where id=1 and waiting_steps > 0;

--need be after check for waiting_steps

update ride_assign_tmp_calculations set

assigned_at_step = new_car_end

where id=1;

--now that we saved the assigned_at_step we upadate to the end of the ride

update ride_assign_tmp_calculations set

new_car_end = new_car_end + new.target_ride_size

where id=1;

--now let's apply our calculations and assign the ride

update booked_rides set

assigned_car=old.id,

assigned_at_step=(select assigned_at_step from ride_assign_tmp_calculations where id=1),

waiting_steps=(select waiting_steps from ride_assign_tmp_calculations where id=1),

distance_to_start=(select distance_to_start from ride_assign_tmp_calculations where id=1)

where id=new.target_ride_id;

update cars set

ride_id=new.target_ride_id, x2=new.target_x2, y2=new.target_y2,

ride_end=(select new_car_end from ride_assign_tmp_calculations where id=1)

where id=old.id;

insert into car_rides(car_id, ride_id) values(old.id, new.target_ride_id);

end;

create view if not exists assign_rides_to_cars_view as

select id, x1, y1, x2, y2, ride_start, ride_end , ride_size, 0 as dummy

from booked_rides_list_view

where possible=1;

create trigger if not exists assign_rides_to_cars_trigger instead of update

on assign_rides_to_cars_view

-- old.* references booked_rides record

--find the nearest car to this ride and tranfer ride information to do the work

update find_nearest_available_car_view set

target_ride_id=old.id,

target_x1=old.x1, target_y1=old.y1,

target_x2=old.x2, target_y2=old.y2,

target_ride_start=old.ride_start,

target_ride_size=old.ride_size

where id=(

select id

from cars

where ride_end <= (old.ride_end - old.ride_size)

order by (ride_end + (abs(old.x1-x2) + abs(old.y1-y2)))

limit 1

);

end;

update assign_rides_to_cars_view set dummy=1;

.separator ' | '

select 'Result Status' '----'; select * from booked_rides_result_view;select '' '----';

select * from calculate_score_view;

.h off

.output 'results.txt'

select * from car_rides_result_list_view;