-- +goose Up
CREATE TABLE IF NOT EXISTS club_memberships(
  id TEXT PRIMARY KEY,
  player_slug TEXT NOT NULL REFERENCES players(slug),
  club_slug TEXT NOT NULL REFERENCES teams(slug),
  start_date DATE NOT NULL,
  end_date DATE
);

CREATE INDEX IF NOT EXISTS club_memberships_player_slug_start_date_end_date_idx ON club_memberships(player_slug, start_date, end_date);

-- +goose Down
DROP INDEX IF EXISTS club_memberships_player_slug_start_date_end_date_idx;
DROP TABLE IF EXISTS club_memberships;