-- Migration für Mapvoting-Feature

-- Map-Tabelle erweitern: BildUrl hinzufügen
ALTER TABLE Map 
    ADD COLUMN BildUrl VARCHAR(255) DEFAULT NULL;

-- Neue Tabelle für Map-Votings
CREATE TABLE IF NOT EXISTS map_votings (
    VotingID INT PRIMARY KEY AUTO_INCREMENT,
    Name VARCHAR(100) NOT NULL,
    Start DATETIME NOT NULL,
    Ende DATETIME NOT NULL,
    IstOeffentlich BOOLEAN DEFAULT FALSE
);

-- Zuordnungstabelle: Maps zu Voting
CREATE TABLE IF NOT EXISTS map_voting_maps (
    VotingID INT NOT NULL,
    MapID INT NOT NULL,
    PRIMARY KEY (VotingID, MapID),
    FOREIGN KEY (VotingID) REFERENCES map_votings(VotingID) ON DELETE CASCADE,
    FOREIGN KEY (MapID) REFERENCES Map(MapID) ON DELETE CASCADE
);

-- Stimmen pro Voting
CREATE TABLE IF NOT EXISTS map_voting_votes (
    VoteID INT PRIMARY KEY AUTO_INCREMENT,
    VotingID INT NOT NULL,
    MapID INT NOT NULL,
    Abstimmungskennung VARCHAR(255) NOT NULL, -- z.B. Cookie, IP oder Fingerprint
    Zeitpunkt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (VotingID, Abstimmungskennung), -- Nur eine Stimme pro Voting/Abstimmungskennung
    FOREIGN KEY (VotingID) REFERENCES map_votings(VotingID) ON DELETE CASCADE,
    FOREIGN KEY (MapID) REFERENCES Map(MapID) ON DELETE CASCADE
);
