magic the gathering cards

DROP TABLE IF EXISTS `my_card`;
DROP TABLE IF EXISTS `card_2_card`;
DROP TABLE IF EXISTS `print_2_artist`;
DROP TABLE IF EXISTS `print`;
DROP TABLE IF EXISTS `card_2_main_type`;
DROP TABLE IF EXISTS `card_2_sub_type`;
DROP TABLE IF EXISTS `card`;
DROP TABLE IF EXISTS `edition`;
DROP TABLE IF EXISTS `language`;
DROP TABLE IF EXISTS `main_type`;
DROP TABLE IF EXISTS `rarity`;
DROP TABLE IF EXISTS `sub_type`;
DROP TABLE IF EXISTS `artist`;


CREATE TABLE IF NOT EXISTS `edition` (
    `edition_id`  INT(10)     UNSIGNED NOT NULL AUTO_INCREMENT,
    `abbrevation` VARCHAR(6)           NOT NULL,
    `name`        VARCHAR(50)          NOT NULL,
    #
    PRIMARY KEY (`edition_id`),
    UNIQUE KEY `abbrevation_name` (`abbrevation`,`name`)
) ENGINE=InnoDB COLLATE='utf8_general_ci';


CREATE TABLE IF NOT EXISTS `language` (
    `language_id` VARCHAR(2)  NOT NULL,
    `name`        VARCHAR(20) NOT NULL,
    #
    PRIMARY KEY (`language_id`)
) ENGINE=InnoDB COLLATE='utf8_general_ci';


CREATE TABLE IF NOT EXISTS `main_type` (
    `main_type_id` INT(10)     UNSIGNED NOT NULL AUTO_INCREMENT,
    `name`         VARCHAR(30)          NOT NULL,
    #
    PRIMARY KEY (`main_type_id`),
    UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB COLLATE='utf8_general_ci';


CREATE TABLE IF NOT EXISTS `rarity` (
    `rarity_id` CHAR(1)     NOT NULL,
    `name`      VARCHAR(15) NOT NULL,
    #
    PRIMARY KEY (`rarity_id`)
) ENGINE=InnoDB COLLATE='utf8_general_ci';


CREATE TABLE IF NOT EXISTS `sub_type` (
    `sub_type_id` INT(10)     UNSIGNED NOT NULL AUTO_INCREMENT,
    `name`        VARCHAR(70)          NOT NULL,
    #
    PRIMARY KEY (`sub_type_id`),
    UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB COLLATE='utf8_general_ci';


CREATE TABLE IF NOT EXISTS `artist` (
    `artist_id` INT(10)     UNSIGNED NOT NULL AUTO_INCREMENT,
    `name`      VARCHAR(50)          NOT NULL,
    #
    PRIMARY KEY (`artist_id`),
    UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB COLLATE='utf8_general_ci';


CREATE TABLE IF NOT EXISTS `card` (
    `card_id`     INT(10)         UNSIGNED NOT NULL AUTO_INCREMENT,
    `name`        VARCHAR(150)             NOT NULL,
    `cmc`         SMALLINT(5)     UNSIGNED NOT NULL,
    `color`       VARCHAR(5)               NOT NULL,
    `cost`        VARCHAR(30),
    `rule_text`   VARCHAR(600),
    `power`       VARCHAR(4),
    `toughness`   VARCHAR(4),
    `loyalty`     SMALLINT(6)     UNSIGNED,
    `flip`        tinyint(1)      UNSIGNED,
    `transform`   tinyint(1)      UNSIGNED,
    `split`       tinyint(1)      UNSIGNED,
    #
    PRIMARY KEY (`card_id`),
    UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB COLLATE='utf8_general_ci';


CREATE TABLE IF NOT EXISTS `card_2_card` (
    `card_id`         INT(10) UNSIGNED NOT NULL,
    `partner_card_id` INT(10) UNSIGNED NOT NULL,
    #
    PRIMARY KEY (`card_id`,`partner_card_id`),
    INDEX `fk_card_2_card_partner_card_id` (`partner_card_id`),
    CONSTRAINT `fk_card_2_card_card_id` FOREIGN KEY (`card_id`) REFERENCES `card` (`card_id`),
    CONSTRAINT `fk_card_2_card_partner_card_id` FOREIGN KEY (`partner_card_id`) REFERENCES `card` (`card_id`)
) ENGINE=InnoDB COLLATE='utf8_general_ci';


CREATE TABLE IF NOT EXISTS `print` (
    `print_id`     INT(10)    UNSIGNED NOT NULL,
    `card_id`      INT(10)    UNSIGNED NOT NULL,
    `edition_id`   INT(10)    UNSIGNED NOT NULL,
    `rarity_id`    CHAR(1)             NOT NULL,
    `multivers_id` INT(10)    UNSIGNED NOT NULL,
    `oversize`     tinyint(1) UNSIGNED,
    `language_id`  VARCHAR(2)          NOT NULL,
    #
    PRIMARY KEY (`print_id`),
    INDEX `fk_card_2_print_edition_id` (`edition_id`),
    INDEX `fk_card_2_print_rarity_id` (`rarity_id`),
    INDEX `fk_card_2_print_language_id` (`language_id`),
    UNIQUE KEY `card_id_edition_id_oversize_language_id`(`card_id`,`edition_id`, `oversize`, `language_id`),
    UNIQUE KEY `multivers_id` (`multivers_id`),
    CONSTRAINT `fk_card_2_print_edition_id` FOREIGN KEY (`edition_id`) REFERENCES `edition` (`edition_id`),
    CONSTRAINT `fk_card_2_print_card_id` FOREIGN KEY (`card_id`) REFERENCES `card` (`card_id`),
    CONSTRAINT `fk_card_2_print_rarity_id` FOREIGN KEY (`rarity_id`) REFERENCES `rarity` (`rarity_id`),
    CONSTRAINT `fk_card_2_print_language_id` FOREIGN KEY (`language_id`) REFERENCES `language` (`language_id`)
) ENGINE=InnoDB COLLATE='utf8_general_ci';


CREATE TABLE IF NOT EXISTS `print_2_artist` (
    `print_id`  INT(10) UNSIGNED NOT NULL,
    `artist_id` INT(10) UNSIGNED NOT NULL,
    #
    PRIMARY KEY (`print_id`,`artist_id`),
    INDEX `fk_print_2_artist_artist_id` (`artist_id`),
    CONSTRAINT `fk_print_2_artist_print_id` FOREIGN KEY (`print_id`) REFERENCES `print` (`print_id`),
    CONSTRAINT `fk_print_2_artist_artist_id` FOREIGN KEY (`artist_id`) REFERENCES `artist` (`artist_id`)
) ENGINE=InnoDB COLLATE='utf8_general_ci';


CREATE TABLE IF NOT EXISTS `card_2_main_type` (
    `card_id`      INT(10)     UNSIGNED NOT NULL,
    `main_type_id` INT(10)     UNSIGNED NOT NULL,
    `order`        SMALLINT(5) UNSIGNED NOT NULL,
    #
    PRIMARY KEY (`card_id`,`main_type_id`),
    INDEX `fk_card_2_main_type_main_type_id` (`main_type_id`),
    CONSTRAINT `fk_card_2_main_type_main_type_id` FOREIGN KEY (`main_type_id`) REFERENCES `main_type` (`main_type_id`),
    CONSTRAINT `fk_card_2_main_type_card_id` FOREIGN KEY (`card_id`) REFERENCES `card` (`card_id`)
) ENGINE=InnoDB COLLATE='utf8_general_ci';


CREATE TABLE IF NOT EXISTS `card_2_sub_type` (
    `card_id`     INT(10)     UNSIGNED NOT NULL,
    `sub_type_id` INT(10)     UNSIGNED NOT NULL,
    `order`       SMALLINT(5) UNSIGNED NOT NULL,
    #
    PRIMARY KEY (`card_id`,`sub_type_id`),
    INDEX `fk_card_2_sub_type_sub_type_id` (`sub_type_id`),
    CONSTRAINT `fk_card_2_sub_type_sub_type_id` FOREIGN KEY (`sub_type_id`) REFERENCES `sub_type` (`sub_type_id`),
    CONSTRAINT `fk_card_2_sub_type_card_id` FOREIGN KEY (`card_id`) REFERENCES `card` (`card_id`)
) ENGINE=InnoDB COLLATE='utf8_general_ci';


CREATE TABLE IF NOT EXISTS `my_card` (
    `my_card_id`   INT(10)    UNSIGNED NOT NULL AUTO_INCREMENT,
    #`user_id`      INT(10)    UNSIGNED NOT NULL,
    `print_id`     INT(10)    UNSIGNED NOT NULL,
    `count`        INT(10)    UNSIGNED NOT NULL,
    #
    PRIMARY KEY (`my_card_id`),
    UNIQUE KEY `print_id` (`print_id`),
    #UNIQUE KEY `user_id_print_id` (`user_id`, `print_id`),
    #CONSTRAINT `fk_my_card_user_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`),
    CONSTRAINT `fk_my_card_print_id` FOREIGN KEY (`print_id`) REFERENCES `print` (`print_id`)
) ENGINE=InnoDB COLLATE='utf8_general_ci';


INSERT INTO `rarity` (`rarity_id`, `name`) VALUES ('C', "Common"), ('U', "Uncommon"), ('R', "Rare"),
                                                    ('M', "Mythic Rare"), ('S', "Special"), ('L', "Land");
INSERT INTO `language` (`language_id`, `name`) VALUES ("en", "English"), ("de", "Deutsch"), ("fr", "Français"),
                                                      ("it", "Italiano"), ("es", "Español"), ("pt", "Português"),
                                                      ("jp", "日本語"), ("cn", "简体中文"), ("ru", "Русский"),
                                                      ("tw", "繁體中文"), ("ko", "한국어");