-- CreateTable
CREATE TABLE `Track` (
    `id` CHAR(25) NOT NULL,
    `spotifyTrackId` VARCHAR(191) NULL,
    `spotifyUri` VARCHAR(191) NULL,
    `trackSlug` VARCHAR(191) NOT NULL,
    `trackName` VARCHAR(191) NOT NULL,
    `artistNames` VARCHAR(191) NOT NULL,
    `albumName` VARCHAR(191) NOT NULL,
    `durationMs` INTEGER NOT NULL,
    `lastListenedAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    UNIQUE INDEX `Track_spotifyTrackId_key`(`spotifyTrackId`),
    UNIQUE INDEX `Track_spotifyUri_key`(`spotifyUri`),
    UNIQUE INDEX `Track_trackSlug_key`(`trackSlug`),
    INDEX `Track_lastListenedAt_idx`(`lastListenedAt`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `ListeningSession` (
    `id` CHAR(25) NOT NULL,
    `idempotencyKey` VARCHAR(191) NOT NULL,
    `trackId` CHAR(25) NOT NULL,
    `playbackStartMs` BIGINT NOT NULL,
    `startedAt` DATETIME(3) NOT NULL,
    `lastObservedAt` DATETIME(3) NOT NULL,
    `listenedMsAccumulated` INTEGER NOT NULL,
    `maxProgressMsObserved` INTEGER NOT NULL DEFAULT 0,
    `isActive` BOOLEAN NOT NULL DEFAULT true,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    UNIQUE INDEX `ListeningSession_idempotencyKey_key`(`idempotencyKey`),
    INDEX `ListeningSession_trackId_idx`(`trackId`),
    INDEX `ListeningSession_isActive_lastObservedAt_idx`(`isActive`, `lastObservedAt`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `Label` (
    `id` CHAR(25) NOT NULL,
    `name` VARCHAR(191) NOT NULL,
    `color` VARCHAR(191) NULL,
    `iconUrl` VARCHAR(191) NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `Note` (
    `id` CHAR(25) NOT NULL,
    `text` VARCHAR(191) NOT NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `Marker` (
    `id` CHAR(25) NOT NULL,
    `trackId` CHAR(25) NOT NULL,
    `timestampMs` INTEGER NOT NULL,
    `kind` ENUM('NOTE', 'LABEL', 'SIMPLE') NOT NULL,
    `noteId` CHAR(25) NULL,
    `labelId` CHAR(25) NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    INDEX `Marker_trackId_createdAt_idx`(`trackId`, `createdAt`),
    INDEX `Marker_noteId_idx`(`noteId`),
    INDEX `Marker_labelId_idx`(`labelId`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `TrackProfile` (
    `trackId` CHAR(25) NOT NULL,
    `stars` INTEGER NULL,
    `freshness` INTEGER NULL,
    `rank` VARCHAR(191) NULL,
    `titleScore` INTEGER NULL,
    `otherScore` INTEGER NULL,
    `energy` INTEGER NULL,
    `mood` INTEGER NULL,
    `rhythm` INTEGER NULL,
    `lyrics` INTEGER NULL,
    `tone` VARCHAR(191) NULL,
    `bpm` INTEGER NULL,
    `popularity` INTEGER NULL,
    `relatedArtists` JSON NULL,
    `connectionTrackIds` JSON NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    INDEX `TrackProfile_updatedAt_idx`(`updatedAt`),
    PRIMARY KEY (`trackId`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `Genre` (
    `id` CHAR(25) NOT NULL,
    `name` VARCHAR(191) NOT NULL,
    `isSystem` BOOLEAN NOT NULL DEFAULT false,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    UNIQUE INDEX `Genre_name_key`(`name`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `Category` (
    `id` CHAR(25) NOT NULL,
    `name` VARCHAR(191) NOT NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    UNIQUE INDEX `Category_name_key`(`name`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `TrackGenre` (
    `trackId` CHAR(25) NOT NULL,
    `genreId` CHAR(25) NOT NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    INDEX `TrackGenre_genreId_idx`(`genreId`),
    PRIMARY KEY (`trackId`, `genreId`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `TrackCategory` (
    `trackId` CHAR(25) NOT NULL,
    `categoryId` CHAR(25) NOT NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    INDEX `TrackCategory_categoryId_idx`(`categoryId`),
    PRIMARY KEY (`trackId`, `categoryId`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `TrackMetricEvent` (
    `id` CHAR(25) NOT NULL,
    `trackId` CHAR(25) NOT NULL,
    `metricKey` ENUM('FRESHNESS') NOT NULL,
    `source` ENUM('MANUAL') NOT NULL DEFAULT 'MANUAL',
    `intValue` INTEGER NOT NULL,
    `note` VARCHAR(191) NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    INDEX `TrackMetricEvent_trackId_metricKey_createdAt_idx`(`trackId`, `metricKey`, `createdAt`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `ProfileOption` (
    `id` CHAR(25) NOT NULL,
    `kind` ENUM('RANK', 'TONE', 'RELATED_ARTIST', 'CONNECTION') NOT NULL,
    `value` VARCHAR(191) NOT NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    INDEX `ProfileOption_kind_createdAt_idx`(`kind`, `createdAt`),
    UNIQUE INDEX `ProfileOption_kind_value_key`(`kind`, `value`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- AddForeignKey
ALTER TABLE `ListeningSession` ADD CONSTRAINT `ListeningSession_trackId_fkey` FOREIGN KEY (`trackId`) REFERENCES `Track`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `Marker` ADD CONSTRAINT `Marker_trackId_fkey` FOREIGN KEY (`trackId`) REFERENCES `Track`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `Marker` ADD CONSTRAINT `Marker_noteId_fkey` FOREIGN KEY (`noteId`) REFERENCES `Note`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `Marker` ADD CONSTRAINT `Marker_labelId_fkey` FOREIGN KEY (`labelId`) REFERENCES `Label`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `TrackProfile` ADD CONSTRAINT `TrackProfile_trackId_fkey` FOREIGN KEY (`trackId`) REFERENCES `Track`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `TrackGenre` ADD CONSTRAINT `TrackGenre_trackId_fkey` FOREIGN KEY (`trackId`) REFERENCES `Track`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `TrackGenre` ADD CONSTRAINT `TrackGenre_genreId_fkey` FOREIGN KEY (`genreId`) REFERENCES `Genre`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `TrackCategory` ADD CONSTRAINT `TrackCategory_trackId_fkey` FOREIGN KEY (`trackId`) REFERENCES `Track`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `TrackCategory` ADD CONSTRAINT `TrackCategory_categoryId_fkey` FOREIGN KEY (`categoryId`) REFERENCES `Category`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `TrackMetricEvent` ADD CONSTRAINT `TrackMetricEvent_trackId_fkey` FOREIGN KEY (`trackId`) REFERENCES `Track`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;