badging

CREATE TABLE my_keyspace.slots (
    day int,
    skuid text,
    priority int,
    badge text,
    PRIMARY KEY ((day, skuid), priority)
);

CREATE TABLE my_keyspace.skus_by_badge (
    badge text,
    skuid text,
    is_present boolean,
    priorities list<int>,
    priority int,
    PRIMARY KEY (badge, skuid)
);

// we duplicate sku badge info to every badge id, with is_present = true only if actually assigned to that badge
CREATE INDEX is_present_idx ON my_keyspace.skus_by_badge (is_present);

// insert some badge assignment to a sku
insert into slots (day, skuid, priority, badge) values (1, 'sku1', 10, 'badge1');
// for each badge, now insert the sku appropriately. In this example there are only 3 total badges
update skus_by_badge set is_present = true, priority = 10, priorities = priorities + [10] where badge = 'badge1' and skuid = 'sku1';
update skus_by_badge set priorities = priorities + [10] where badge = 'badge2' and skuid = 'sku1';
update skus_by_badge set priorities = priorities + [10] where badge = 'badge3' and skuid = 'sku1';

insert into slots (day, skuid, priority, badge) values (1, 'sku2', 10, 'badge1');
update skus_by_badge set is_present = true, priority = 10, priorities = priorities + [10] where badge = 'badge1' and skuid = 'sku2';
update skus_by_badge set priorities = priorities + [10] where badge = 'badge2' and skuid = 'sku2';
update skus_by_badge set priorities = priorities + [10] where badge = 'badge3' and skuid = 'sku2';

insert into slots (day, skuid, priority, badge) values (1, 'sku2', 20, 'badge2');
update skus_by_badge set is_present = true, priority = 20, priorities = priorities + [20] where badge = 'badge2' and skuid = 'sku2';
update skus_by_badge set priorities = priorities + [20] where badge = 'badge1' and skuid = 'sku2';
update skus_by_badge set priorities = priorities + [20] where badge = 'badge3' and skuid = 'sku2';

insert into slots (day, skuid, priority, badge) values (1, 'sku3', 20, 'badge2');
update skus_by_badge set is_present = true, priority = 20, priorities = priorities + [20] where badge = 'badge2' and skuid = 'sku3';
update skus_by_badge set priorities = priorities + [20] where badge = 'badge1' and skuid = 'sku3';
update skus_by_badge set priorities = priorities + [20] where badge = 'badge3' and skuid = 'sku3';


 select * from slots;

 day | skuid | priority | badge
-----+-------+----------+--------
   1 |  sku2 |       10 | badge1
   1 |  sku2 |       20 | badge2
   1 |  sku1 |       10 | badge1
   1 |  sku3 |       20 | badge2
   
  select * from skus_by_badge;
 
 badge  | skuid | is_present | priorities | priority
--------+-------+------------+------------+----------
 badge1 |  sku1 |       True |       [10] |       10
 badge1 |  sku2 |       True |   [10, 20] |       10
 badge1 |  sku3 |       null |       [20] |     null
 badge3 |  sku1 |       null |       [10] |     null
 badge3 |  sku2 |       null |   [10, 20] |     null
 badge3 |  sku3 |       null |       [20] |     null
 badge2 |  sku1 |       null |       [10] |     null
 badge2 |  sku2 |       True |   [10, 20] |       20
 badge2 |  sku3 |       True |       [20] |       20

 
 // actual query for skus_by_badge for badge1
select skuid, priority, priorities from skus_by_badge where badge = 'badge1' and is_present = true;
  
 skuid | priority | priorities
-------+----------+------------
  sku1 |       10 |       [10]
  sku2 |       10 |   [10, 20]
  
  // now the client app needs to just filter this list for where the priority is in the top 2 of priorities (or however many spots allowed)

// For example in badge 2, if we only wnat to pass the top 1 highest priority, the client will filter out sku2 here
select skuid, priority, priorities from skus_by_badge where badge = 'badge2' and is_present = true;

 skuid | priority | priorities
-------+----------+------------
  sku2 |       20 |   [10, 20]
  sku3 |       20 |       [20]

// and for badge 3:
select skuid, priority, priorities from skus_by_badge where badge = 'badge3' and is_present = true;
 badge | skuid | is_present | priorities | priority
-------+-------+------------+------------+----------