-- card name db
create table if not exists mtg (
  name text,
  cost text,
  card_type text
);
CREATE TABLE if not exists round (
    id integer primary key,
    fileid text not null,
    date text
);
CREATE TABLE if not exists deck (
    round integer not null,
    player varchar not null,
    card1 text,
    card2 text,
    card3 text
);
CREATE TABLE if not exists match (
    round integer,
    group_name text,
    player text, 
    opp_player text,
    score integer
);
CREATE VIEW if not exists card as 
    select round,player,card1 as card from deck
    union select round,player,card2 as card from deck
    union select round,player,card3 as card from deck
/* card(round,player,card) */;
CREATE INDEX if not exists matchidx on match(round);
CREATE INDEX if not exists deckidx on deck(round);
CREATE INDEX if not exists matchidx2 on match(player);
CREATE INDEX if not exists deckidx2 on deck(player);
CREATE VIEW if not exists rank as 
select round,player,rank() over (partition by round order by 
sum(case when match.group_name = 'final' then match.score else null end) desc nulls last,
sum(case when match.group_name != 'final' then match.score else null end) desc
) as rank 
from match group by 1,2 order by 3 desc
/* rank(round,player,rank) */;
CREATE TABLE if not exists ban (name text);
CREATE INDEX if not exists c1idx on deck(card1);
CREATE INDEX if not exists c2idx on deck(card2);
CREATE INDEX if not exists c3idx on deck(card3);