# This is a fix for InnoDB in MySQL >= 4.1.x
# It "suspends judgement" for fkey relationships until are tables are set.
SET FOREIGN_KEY_CHECKS = 0;

-- ---------------------------------------------------------------------
-- user
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `user`;

CREATE TABLE `user`
(
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `username` VARCHAR(128) NOT NULL,
    `passwd` VARCHAR(128) NOT NULL,
    `slug` VARCHAR(255),
    PRIMARY KEY (`id`),
    UNIQUE INDEX `user_slug` (`slug`(255))
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- gallery
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `gallery`;

CREATE TABLE `gallery`
(
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `title` VARCHAR(128) NOT NULL,
    `description` TEXT,
    `is_disabled` TINYINT(1) DEFAULT 0,
    `slug` VARCHAR(255),
    `sortable_rank` INTEGER,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `gallery_slug` (`slug`(255))
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- painting
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `painting`;

CREATE TABLE `painting`
(
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `title` VARCHAR(255) NOT NULL,
    `description` TEXT,
    `image` TEXT NOT NULL,
    `thumb` TEXT NOT NULL,
    `width` VARCHAR(20) NOT NULL,
    `height` VARCHAR(20) NOT NULL,
    `year` INTEGER NOT NULL,
    `gallery_id` INTEGER NOT NULL,
    `medium_id` INTEGER,
    `status` VARCHAR(80),
    `is_disabled` TINYINT(1) DEFAULT 0,
    `slug` VARCHAR(255),
    `sortable_rank` INTEGER,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `painting_slug` (`slug`(255)),
    INDEX `painting_FI_1` (`gallery_id`),
    INDEX `painting_FI_2` (`medium_id`),
    CONSTRAINT `painting_FK_1`
        FOREIGN KEY (`gallery_id`)
        REFERENCES `gallery` (`id`),
    CONSTRAINT `painting_FK_2`
        FOREIGN KEY (`medium_id`)
        REFERENCES `medium` (`id`)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- medium
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `medium`;

CREATE TABLE `medium`
(
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(64) NOT NULL,
    `slug` VARCHAR(255),
    PRIMARY KEY (`id`),
    UNIQUE INDEX `medium_slug` (`slug`(255))
) ENGINE=InnoDB;

# This restores the fkey checks, after having unset them earlier
SET FOREIGN_KEY_CHECKS = 1;