create table seats
( id       number(3)   not null primary key
, roww     number(3)  not null
, position varchar2(1) not null
)
/

create table boarding_passes
( seat_id number(3)
, more_details varchar2(100)
)
/

alter table boarding_passes
add constraint bps_seat_fk1 foreign key (seat_id)
references seats(id)
/

-- create all the seats for a 46 rows, 6 abreast plane (just an example)

insert into seats
(id, roww, position)
with positions as
( select chr(64+level) position
  from   dual
  connect
  by     level < 7
)
, rowws as
( select level roww
  from   dual
  connect
  by     level < 47
)
select rownum
,      roww
,      position
from   positions
       cross join
       rowws
/
	   
  
-- hand out boarding passes at random for approximately 35% of the seats

insert into boarding_passes
( seat_id, more_details)
with seat_allocations as
( SELECT     LEVEL                                   seat_id
  ,          TRUNC (DBMS_RANDOM.VALUE (1, 100), 2)   flag
  FROM       DUAL
  CONNECT
  BY         LEVEL < 277
)
select seat_id
,      'Seat was allocated'
from   seat_allocations
where  flag < 35
/


-- show the airplane with its seats using a pivot query
select *
from ( select  s.roww
       ,       s.position
       ,       case 
               when bp.rowid is null
	           then 0
	           else 1
	           end     seat_free_flag
       from    seats s
               left outer join
	           boarding_passes bp
	           on (bp.seat_id = s.id)
     ) pivot ( max(seat_free_flag) 
               FOR position in ('A' as A, 'B' as B,'C' as C,'D' as D,'E' as E,'F' as F))
order
by      roww
/
		
-- select the roww and its bitmap - an indication of free seats (0) and taken seats (1) in a per row bitmap (010000)

with seating as
( select  s.roww
  ,       s.position
  ,       case
          when bp.rowid is null
	      then 0
	      else 1
	      end     seat_free_flag
  from    seats s
          left outer join
	      boarding_passes bp
	      on (bp.seat_id = s.id)
)
select roww
,      listagg(seat_free_flag,'') within group (order by position) row_map
from   seating
group
by     roww
/


-- find all rows with two adjacent seats - considering that C and D are separated by the aisle and therefore not adjacent

with seating as
( select  s.roww
  ,       s.position
  ,       case
          when bp.rowid is null
	      then 0
	      else 1
	      end     seat_free_flag
  from    seats s
          left outer join
	      boarding_passes bp
	      on (bp.seat_id = s.id)
)
, row_maps as
( select roww
  ,      listagg(seat_free_flag||case position when 'C' then '|' end,'') within group (order by position) row_map
  from   seating
  group
  by     roww
)
select roww
,      row_map
from   row_maps 
where  instr(row_map ,'00') > 1
/




-- assign scores for adjacent, adjacent across the aisle and on aisle one behind the other

with seating as
( select  s.roww
  ,       s.position
  ,       case
          when bp.rowid is null
	      then 0
	      else 1
	      end     seat_free_flag
  from    seats s
          left outer join
	      boarding_passes bp
	      on (bp.seat_id = s.id)
)
, row_maps as
( select roww
  ,      listagg(seat_free_flag||case position when 'C' then '|' end,'') within group (order by position) row_map
  from   seating
  group
  by     roww
)
select roww
,      row_map
,      case
       when instr(row_map ,'00') > 0
       then 10
       when instr(row_map ,'0|0') > 0 -- across the aisle
       then 8  
       when ( instr(row_map ,'0|') > 0 and instr(lead(row_map) over (order by roww), '0|') > 0
              or
              instr(row_map ,'|0') > 0 and instr(lead(row_map) over (order by roww), '|0') > 0
            )
       then 4
       end row_score
from   row_maps 
order
by     roww
/

insert into boarding_passes
( seat_id)
values
(185)

insert into boarding_passes
( seat_id)
values
(48)
/

insert into boarding_passes
( seat_id)
values
(186)

/