-- ============================================================
-- Script SQL complet pour On Tour App
-- Généré par Doctrine ORM
-- ============================================================

CREATE TABLE users (
    id INT AUTO_INCREMENT NOT NULL,
    email VARCHAR(180) NOT NULL,
    password VARCHAR(255) NOT NULL,
    firstName VARCHAR(100) NOT NULL,
    lastName VARCHAR(100) NOT NULL,
    code VARCHAR(8) NOT NULL,
    isSuperAdmin TINYINT(1) DEFAULT 0 NOT NULL,
    isGod TINYINT(1) DEFAULT 0 NOT NULL,
    createdAt DATETIME NOT NULL,
    updatedAt DATETIME DEFAULT NULL,
    lastStructure_id INT DEFAULT NULL,
    UNIQUE INDEX UNIQ_1483A5E9E7927C74 (email),
    UNIQUE INDEX UNIQ_1483A5E977153098 (code),
    INDEX IDX_1483A5E9A0AEA9C6 (lastStructure_id),
    PRIMARY KEY(id)
) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;



CREATE TABLE structures (
    id INT AUTO_INCREMENT NOT NULL,
    name VARCHAR(255) NOT NULL,
    description LONGTEXT DEFAULT NULL,
    createdAt DATETIME NOT NULL,
    PRIMARY KEY(id)
) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;

CREATE TABLE structure_members (
    id INT AUTO_INCREMENT NOT NULL,
    structure_id INT NOT NULL,
    user_id INT NOT NULL,
    role VARCHAR(50) DEFAULT 'member' NOT NULL,
    createdAt DATETIME NOT NULL,
    INDEX IDX_9E02A2CE2534008B (structure_id),
    INDEX IDX_9E02A2CEA76ED395 (user_id),
    PRIMARY KEY(id)
) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;

CREATE TABLE teams (
    id INT AUTO_INCREMENT NOT NULL,
    structure_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    color VARCHAR(7) DEFAULT NULL,
    createdAt DATETIME NOT NULL,
    createdBy_id INT NOT NULL,
    INDEX IDX_96C222582534008B (structure_id),
    INDEX IDX_96C222583174800F (createdBy_id),
    PRIMARY KEY(id)
) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;

CREATE TABLE team_members (
    id INT AUTO_INCREMENT NOT NULL,
    team_id INT NOT NULL,
    user_id INT NOT NULL,
    role VARCHAR(100) DEFAULT NULL,
    createdAt DATETIME NOT NULL,
    INDEX IDX_BAD9A3C8296CD8AE (team_id),
    INDEX IDX_BAD9A3C8A76ED395 (user_id),
    PRIMARY KEY(id)
) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;

CREATE TABLE invitations (
    id INT AUTO_INCREMENT NOT NULL,
    structure_id INT NOT NULL,
    status VARCHAR(20) DEFAULT 'pending' NOT NULL,
    isInvitation TINYINT(1) DEFAULT 1 NOT NULL,
    createdAt DATETIME NOT NULL,
    fromUser_id INT NOT NULL,
    toUser_id INT NOT NULL,
    INDEX IDX_232710AE2534008B (structure_id),
    INDEX IDX_232710AE1CD1765A (fromUser_id),
    INDEX IDX_232710AEB7320117 (toUser_id),
    PRIMARY KEY(id)
) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;

CREATE TABLE tours (
    id INT AUTO_INCREMENT NOT NULL,
    name VARCHAR(255) NOT NULL,
    description LONGTEXT DEFAULT NULL,
    startDate DATE DEFAULT NULL,
    endDate DATE DEFAULT NULL,
    status VARCHAR(50) DEFAULT 'planned' NOT NULL,
    createdAt DATETIME NOT NULL,
    updatedAt DATETIME DEFAULT NULL,
    createdBy_id INT NOT NULL,
    INDEX IDX_2F0AC70E3174800F (createdBy_id),
    PRIMARY KEY(id)
) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;

CREATE TABLE shows (
    id INT AUTO_INCREMENT NOT NULL,
    tour_id INT DEFAULT NULL,
    name VARCHAR(255) NOT NULL,
    date DATE DEFAULT NULL,
    location VARCHAR(255) DEFAULT NULL,
    mapsLink VARCHAR(500) DEFAULT NULL,
    description LONGTEXT DEFAULT NULL,
    startTime TIME DEFAULT NULL,
    endTime TIME DEFAULT NULL,
    createdAt DATETIME NOT NULL,
    createdBy_id INT NOT NULL,
    INDEX IDX_6C3BF14415ED8D43 (tour_id),
    INDEX IDX_6C3BF1443174800F (createdBy_id),
    PRIMARY KEY(id)
) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;

CREATE TABLE show_members (
    id INT AUTO_INCREMENT NOT NULL,
    show_id INT NOT NULL,
    user_id INT NOT NULL,
    role VARCHAR(255) DEFAULT NULL,
    addedAt DATETIME NOT NULL,
    INDEX IDX_22F2FF1CD0C1FC64 (show_id),
    INDEX IDX_22F2FF1CA76ED395 (user_id),
    PRIMARY KEY(id)
) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;

CREATE TABLE show_teams (
    id INT AUTO_INCREMENT NOT NULL,
    show_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    color VARCHAR(7) DEFAULT NULL,
    createdAt DATETIME NOT NULL,
    INDEX IDX_94734684D0C1FC64 (show_id),
    PRIMARY KEY(id)
) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;

CREATE TABLE show_team_members (
    id INT AUTO_INCREMENT NOT NULL,
    user_id INT NOT NULL,
    role VARCHAR(255) DEFAULT NULL,
    addedAt DATETIME NOT NULL,
    showTeam_id INT NOT NULL,
    INDEX IDX_C2716E9A9085292A (showTeam_id),
    INDEX IDX_C2716E9AA76ED395 (user_id),
    PRIMARY KEY(id)
) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;

-- ============================================================
-- Contraintes étrangères
-- ============================================================

ALTER TABLE users
    ADD CONSTRAINT FK_1483A5E9A0AEA9C6 FOREIGN KEY (lastStructure_id) REFERENCES structures (id) ON DELETE SET NULL;

ALTER TABLE structure_members

    ADD CONSTRAINT FK_9E02A2CE2534008B FOREIGN KEY (structure_id) REFERENCES structures (id) ON DELETE CASCADE;
ALTER TABLE structure_members
    ADD CONSTRAINT FK_9E02A2CEA76ED395 FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE;

ALTER TABLE teams
    ADD CONSTRAINT FK_96C222582534008B FOREIGN KEY (structure_id) REFERENCES structures (id) ON DELETE CASCADE;
ALTER TABLE teams
    ADD CONSTRAINT FK_96C222583174800F FOREIGN KEY (createdBy_id) REFERENCES users (id) ON DELETE CASCADE;

ALTER TABLE team_members
    ADD CONSTRAINT FK_BAD9A3C8296CD8AE FOREIGN KEY (team_id) REFERENCES teams (id) ON DELETE CASCADE;
ALTER TABLE team_members
    ADD CONSTRAINT FK_BAD9A3C8A76ED395 FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE;

ALTER TABLE invitations
    ADD CONSTRAINT FK_232710AE2534008B FOREIGN KEY (structure_id) REFERENCES structures (id) ON DELETE CASCADE;
ALTER TABLE invitations
    ADD CONSTRAINT FK_232710AE1CD1765A FOREIGN KEY (fromUser_id) REFERENCES users (id) ON DELETE CASCADE;
ALTER TABLE invitations
    ADD CONSTRAINT FK_232710AEB7320117 FOREIGN KEY (toUser_id) REFERENCES users (id) ON DELETE CASCADE;

ALTER TABLE tours
    ADD CONSTRAINT FK_2F0AC70E3174800F FOREIGN KEY (createdBy_id) REFERENCES users (id) ON DELETE CASCADE;

ALTER TABLE shows
    ADD CONSTRAINT FK_6C3BF14415ED8D43 FOREIGN KEY (tour_id) REFERENCES tours (id) ON DELETE SET NULL;
ALTER TABLE shows
    ADD CONSTRAINT FK_6C3BF1443174800F FOREIGN KEY (createdBy_id) REFERENCES users (id) ON DELETE CASCADE;

ALTER TABLE show_members
    ADD CONSTRAINT FK_22F2FF1CD0C1FC64 FOREIGN KEY (show_id) REFERENCES shows (id) ON DELETE CASCADE;
ALTER TABLE show_members
    ADD CONSTRAINT FK_22F2FF1CA76ED395 FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE;

ALTER TABLE show_teams
    ADD CONSTRAINT FK_94734684D0C1FC64 FOREIGN KEY (show_id) REFERENCES shows (id) ON DELETE CASCADE;

ALTER TABLE show_team_members
    ADD CONSTRAINT FK_C2716E9A9085292A FOREIGN KEY (showTeam_id) REFERENCES show_teams (id) ON DELETE CASCADE;
ALTER TABLE show_team_members
    ADD CONSTRAINT FK_C2716E9AA76ED395 FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE;
