/*
 * Removing both the sequence and tables to remake the database
 */
DROP INDEX cards_artists_index;
DROP INDEX cards_capacities_index;
DROP INDEX cards_cardtexts_index;
DROP INDEX cards_cardtypes_index;
DROP INDEX cards_clans_index;
DROP INDEX cards_costs_index;
DROP INDEX cards_disciplines_index;
DROP INDEX cards_names_index;
DROP INDEX cards_rarities_index;
DROP INDEX cards_sects_index;
DROP INDEX cards_sets_index;
DROP INDEX cards_titles_index;
DROP INDEX decks_cards_index;
DROP INDEX logins_cards_index;
DROP INDEX tradelist_index;

DROP INDEX cards_artists2_index;
DROP INDEX cards_cardtypes2_index;
DROP INDEX cards_clans2_index;
DROP INDEX cards_disciplines2_index;
DROP INDEX cards_rarities2_index;
DROP INDEX cards_sects2_index;
DROP INDEX cards_sets2_index;
DROP INDEX cards_titles2_index;
DROP INDEX decks_cards2_index;
DROP INDEX logins_cards2_index;
DROP INDEX tradelist2_index;

DROP SEQUENCE titles_seq;
DROP SEQUENCE clans_seq;
DROP SEQUENCE artists_seq;
DROP SEQUENCE disciplines_seq;
DROP SEQUENCE cards_seq;
DROP SEQUENCE cardtypes_seq;
DROP SEQUENCE logins_seq;
DROP SEQUENCE decks_seq;
DROP SEQUENCE sets_seq;
DROP SEQUENCE rarities_seq;
DROP SEQUENCE sects_seq;

DROP TABLE tradelist;
DROP TABLE decks_cards;
DROP TABLE decks;
DROP TABLE logins_cards;
DROP TABLE logins;
DROP TABLE cards_cardtexts;
DROP TABLE cards_sects;
DROP TABLE cards_titles;
DROP TABLE cards_clans;
DROP TABLE cards_disciplines;
DROP TABLE cards_capacities;
DROP TABLE cards_cardtypes;
DROP TABLE cards_artists;
DROP TABLE cards_sets;
DROP TABLE cards_rarities;
DROP TABLE cards_names;
DROP TABLE cards_costs;
DROP TABLE cards;
DROP TABLE clans;
DROP TABLE titles;
DROP TABLE cardtypes;
DROP TABLE artists;
DROP TABLE disciplines;
DROP TABLE rarities;
DROP TABLE sets;
DROP TABLE sects;

/*
 * Sequences for the different PRIMARY KEYS in the tables 
 */
CREATE SEQUENCE titles_seq;
CREATE SEQUENCE clans_seq;
CREATE SEQUENCE artists_seq;
CREATE SEQUENCE disciplines_seq;
CREATE SEQUENCE cards_seq;
CREATE SEQUENCE cardtypes_seq;
CREATE SEQUENCE logins_seq;
CREATE SEQUENCE decks_seq;
CREATE SEQUENCE sets_seq;
CREATE SEQUENCE rarities_seq;
CREATE SEQUENCE sects_seq;

/*
 * Table depicting the names of the different sects that make up the
 * vampire world
 */
CREATE TABLE sects
(
  sect_id int2 DEFAULT NEXTVAL('sects_seq'),
  sect text NOT NULL,
  PRIMARY KEY(sect_id)
);

/*
 * Table depicting the names of the different sets that have been
 * released so far
 */
CREATE TABLE sets
(
  set_id int2 DEFAULT NEXTVAL('sets_seq'),
  set_abbr varchar(6) NOT NULL,
  set text NOT NULL,
  PRIMARY KEY(set_id)
);

/*
 * Table depicting the rarity of a card and/or if the card is 
 * included in a starter pack
 */
CREATE TABLE rarities
(
  rarity_id int2 DEFAULT NEXTVAL('rarities_seq'),
  rarity varchar(16) NOT NULL,
  PRIMARY KEY(rarity_id)
);

/*
 * Table depicting the names of the disciplines the vampires can have
 */
CREATE TABLE disciplines
(
  discipline_id int2 DEFAULT NEXTVAL('disciplines_seq'),
  discipline_abbr char(3) NOT NULL,
  discipline text NOT NULL,
  PRIMARY KEY(discipline_id)
);

/*
 * Table depicting the artists who draw the different pictures on the
 * cards
 */
CREATE TABLE artists
(
  artist_id int2 DEFAULT NEXTVAL('artists_seq'),
  artist text NOT NULL,
  PRIMARY KEY(artist_id)
);

/*
 * Table depicting which card types exists in V:TES
 */
CREATE TABLE cardtypes
(
  cardtype_id int2 DEFAULT NEXTVAL('cardtypes_seq'),
  cardtype text NOT NULL,
  PRIMARY KEY(cardtype_id)
);

/*
 * Table depicting the different titles a vampire can have
 */
CREATE TABLE titles
(
  title_id int2 DEFAULT NEXTVAL('titles_seq'),
  title text NOT NULL,
  PRIMARY KEY(title_id)
);

/*
 * Table depicting the names of the different clans
 */
CREATE TABLE clans
(
  clan_id int2 DEFAULT NEXTVAL('clans_seq'),
  clan text NOT NULL,
  PRIMARY KEY(clan_id)
);

/*
 * Table depicting the which cards exist and main information about
 * them
 */
CREATE TABLE cards
(
  card_id int2 DEFAULT NEXTVAL('cards_seq'),
  cardname text NOT NULL,
  PRIMARY KEY(card_id)
);

CREATE TABLE cards_costs
(
  card_id int2 NOT NULL,
  cost int2 NOT NULL,
  pool bool NOT NULL,
  FOREIGN KEY (card_id) REFERENCES cards
);

/*
 *  Table depicting the alternative name of a card
 */
CREATE TABLE cards_names
(
  card_id int2 NOT NULL,
  cardname text NOT NULL,
  FOREIGN KEY (card_id) REFERENCES cards
);

/*
 *  Table depicting the rarity value of a card
 */
CREATE TABLE cards_rarities
(
  card_id int2 NOT NULL,
  rarity_id int2 NULL,
  FOREIGN KEY (card_id) REFERENCES cards,
  FOREIGN KEY (rarity_id) REFERENCES rarities
);

/*
 *  Table depicting which sets a card is included in
 */
CREATE TABLE cards_sets
(
  card_id int2 NOT NULL,
  set_id int2 NOT NULL,
  FOREIGN KEY (card_id) REFERENCES cards,
  FOREIGN KEY (set_id) REFERENCES sets
);

/*
 *  Table depicting which artist/artists drew the picture on a card
 */
CREATE TABLE cards_artists
(
  card_id int2 NOT NULL,
  artist_id int2 NOT NULL,
  FOREIGN KEY (card_id) REFERENCES cards,
  FOREIGN KEY (artist_id) REFERENCES artists
);

/*
 *  Table depicting which type/types of cards exist
 */
CREATE TABLE cards_cardtypes
(
  card_id int2 NOT NULL,
  cardtype_id int2 NOT NULL,
  FOREIGN KEY (card_id) REFERENCES cards,
  FOREIGN KEY (cardtype_id) REFERENCES cardtypes
);

/*
 * Table depicting the capacity that a vampire has
 */
CREATE TABLE cards_capacities
(
  card_id int2 NOT NULL,
  capacity int2 NOT NULL,
  FOREIGN KEY (card_id) REFERENCES cards
);

/*
 * Table depicting which discipline a vampire has
 */
CREATE TABLE cards_disciplines
(
  card_id int2 NOT NULL,
  discipline_id int2 NOT NULL,
  superior boolean NOT NULL,
  FOREIGN KEY (card_id) REFERENCES cards
);

/*
 * Table depicting clan association for a vampire card
 */
CREATE TABLE cards_clans
(
  card_id int2 NOT NULL,
  clan_id int2 NOT NULL,
  FOREIGN KEY (card_id) REFERENCES cards,
  FOREIGN KEY (clan_id) REFERENCES clans
);

/*
 * Table depicting the title for a vampire card
 */
CREATE TABLE cards_titles
(
  card_id int2 NOT NULL,
  title_id int2 NOT NULL,
  FOREIGN KEY (card_id) REFERENCES cards,
  FOREIGN KEY (title_id) REFERENCES titles
);

/*
 * Table depicting the sect for a vampire card
 */
CREATE TABLE cards_sects
(
  card_id int2 NOT NULL,
  sect_id int2 NOT NULL,
  FOREIGN KEY (card_id) REFERENCES cards,
  FOREIGN KEY (sect_id) REFERENCES sects
);

/*
 * Table depicting the text on a card
 */
CREATE TABLE cards_cardtexts
(
  card_id int2 NOT NULL,
  cardtext text NOT NULL,
  FOREIGN KEY (card_id) REFERENCES cards
);


/*
 * Table depicting the logins and passwords for the users of the
 * website
 */
CREATE TABLE logins
(
  login_id int4 DEFAULT NEXTVAL('logins_seq'),
  login text NOT NULL,
  password text NOT NULL,
  firstname text NOT NULL,
  lastname text NOT NULL,
  address text NULL,
  "zip code" text NULL,
  city text NULL,
  state text NULL,
  country text NULL,
  email text NULL,
  PRIMARY KEY(login_id)
);

/*
 * Table depicting the different cards and amount of cards that a 
 * user owns
 */
CREATE TABLE logins_cards
(
  login_id int4, 
  card_id int2,
  amount int2,
  FOREIGN KEY (login_id) REFERENCES logins,
  FOREIGN KEY (card_id) REFERENCES cards
);

/*
 * Table depicting meta information about decks and which users owns
 * which decks
 */
CREATE TABLE decks
(
  deck_id int4 DEFAULT NEXTVAL('decks_seq'),
  login_id int4,
  PRIMARY KEY(deck_id),
  FOREIGN KEY (login_id) REFERENCES logins
);

/*
 * Table depicting the different decks, the type and amount of cards 
 * that is included each deck
 */
CREATE TABLE decks_cards
(
  deck_id int4, 
  card_id int2,
  amount int2,
  FOREIGN KEY (deck_id) REFERENCES decks,
  FOREIGN KEY (card_id) REFERENCES cards
);

/*
 * Table depicting if a user is intrested in trading cards, both letting
 * go and aquiring
 */
CREATE TABLE tradelist
(
  login_id int2,
  card_id int2,
  want bool,
  amount int2,
  FOREIGN KEY (login_id) REFERENCES logins,
  FOREIGN KEY (card_id) REFERENCES cards  
);

CREATE INDEX cards_artists_index ON cards_artists (card_id);
CREATE INDEX cards_capacities_index ON cards_capacities (card_id);
CREATE INDEX cards_cardtexts_index ON cards_cardtexts (card_id);
CREATE INDEX cards_cardtypes_index ON cards_cardtypes (card_id);
CREATE INDEX cards_clans_index ON cards_clans (card_id);
CREATE INDEX cards_costs_index ON cards_costs (card_id);
CREATE INDEX cards_disciplines_index ON cards_disciplines (card_id);
CREATE INDEX cards_names_index ON cards_names (card_id);
CREATE INDEX cards_rarities_index ON cards_rarities (card_id);
CREATE INDEX cards_sects_index ON cards_sects (card_id);
CREATE INDEX cards_sets_index ON cards_sets (card_id);
CREATE INDEX cards_titles_index ON cards_titles (card_id);
CREATE INDEX decks_cards_index ON decks_cards (deck_id);
CREATE INDEX logins_cards_index ON logins_cards (login_id);
CREATE INDEX tradelist_index ON tradelist (login_id);
CREATE INDEX decks_index ON decks (login_id);

CREATE INDEX cards_artists2_index ON cards_artists (artist_id);
CREATE INDEX cards_cardtypes2_index ON cards_cardtypes (cardtype_id);
CREATE INDEX cards_clans2_index ON cards_clans (clan_id);
CREATE INDEX cards_disciplines2_index ON cards_disciplines (discipline_id);
CREATE INDEX cards_rarities2_index ON cards_rarities (rarity_id);
CREATE INDEX cards_sects2_index ON cards_sects (sect_id);
CREATE INDEX cards_sets2_index ON cards_sets (set_id);
CREATE INDEX cards_titles2_index ON cards_titles (title_id);
CREATE INDEX decks_cards2_index ON decks_cards (card_id);
CREATE INDEX logins_cards2_index ON logins_cards (card_id);
CREATE INDEX tradelist2_index ON tradelist (card_id);