-- This retrieves all the pl.id for perl-* packages in non-EOL branches where the owner is cweyl

select pl.id from packagelisting as pl, package as p, collection as c where
  -- joins
  p.id = pl.packageid and c.id = pl.collectionid
  -- only active collections
  and c.statuscode in (1, 18)
  -- what we're interestd in 
  and pl.owner = 'cweyl'
  and p.name like 'perl-%';


-- retrieve all existing comaintainership requests

select ppla.id from personpackagelisting as ppl, personpackagelistingacl as ppla where
  -- joins
  ppl.id = ppla.personpackagelistingid
  -- packagelistings where cweyl is the owner
  and ppl.packagelistingid in (
    select pl.id from packagelisting as pl, package as p, collection as c where
    -- joins
    p.id = pl.packageid and c.id = pl.collectionid
    -- only active collections
    and c.statuscode in (1, 18)
    -- what we're interestd in.
    and pl.owner = 'cweyl'
    and p.name like 'perl-%'
  )
  -- comaint we're interested in
  and ppl.username in ('mmaslano', 'psabata', 'ppisar');

-- approve the existing comaintainership requests
update personpackagelistingacl set statuscode = 3 where id in (
  select ppla.id from personpackagelisting as ppl, personpackagelistingacl as ppla where
    -- joins
    ppl.id = ppla.personpackagelistingid
    -- packagelistings where cweyl is the owner
    and ppl.packagelistingid in (
      select pl.id from packagelisting as pl, package as p, collection as c where
      -- joins
      p.id = pl.packageid and c.id = pl.collectionid
      -- only active collections
      and c.statuscode in (1, 18)
      -- what we're interestd in.
      and pl.owner = 'cweyl'
      and p.name like 'perl-%'
    )
    -- comaint we're interested in
    and ppl.username in ('mmaslano', 'psabata', 'ppisar')
);


-- Insert a ppl where needed

-- username should cycle: mmaslano, psabata, ppisar
-- In two places

insert into personpackagelisting (username, packagelistingid) (select
'mmaslano', pl.id from packagelisting as pl where
  -- packagelisting in set of packagelistings owned by cweyl
  pl.id in (
    select pl.id from packagelisting as pl, package as p, collection as c where
      -- joins
      p.id = pl.packageid and c.id = pl.collectionid
      -- only active collections
      and c.statuscode in (1, 18)
      -- what we're interestd in 
      and pl.owner = 'cweyl'
      and p.name like 'perl-%'
  )
  -- packagelisting not in the packagelistings that already have a ppl for this person
  and pl.id not in (
    select ppl.packagelistingid from personpackagelisting as ppl where ppl.username = 'mmaslano'
  )
);


-- insert a ppla where needed

-- username cycles between mmaslano, psabata, ppisar
-- acl cycles between watchbugzilla, watchcommits, commit, approveacls

insert into personpackagelistingacl (personpackagelistingid, acl, statuscode) (select
  ppl.id, 'approveacls', 3 from personpackagelisting as ppl where
  -- all ppls for the comaint and cweyl's packages
  ppl.id in (
    select id from personpackagelisting as ppl where ppl.username = 'ppisar'
      and ppl.packagelistingid in (
        select pl.id from packagelisting as pl, package as p, collection as c where
          -- joins
          p.id = pl.packageid and c.id = pl.collectionid
          -- only active collections
          and c.statuscode in (1, 18)
          -- what we're interestd in
          and pl.owner = 'cweyl'
          and p.name like 'perl-%'
      )
  )
  -- all ppls where a ppla already exists
  and ppl.id not in (select ppla.personpackagelistingid from personpackagelistingacl as ppla where
    ppla.acl = 'approveacls'
    and ppla.personpackagelistingid in (select id from personpackagelisting as ppl where username = 'ppisar')
  )
);


-- select comaintainers where pl.owner = 'cweyl' and p.name like 'perl-%'
-- select comaintainers where pl.owner = 'mmaslano' and p.name like 'perl-%'

-- update owner of cweyl packages to someone who is around
update packagelisting set owner = 'mmaslano' where id in (
select pl.id from packagelisting as pl, package as p, collection as c where
  -- joins
  p.id = pl.packageid and c.id = pl.collectionid
  -- only active collections
  and c.statuscode in (1, 18)
  -- what we're interestd in.
  and pl.owner = 'cweyl'
  and p.name like 'perl-%');