-- AlterTable
ALTER TABLE `user` ADD COLUMN `gender` ENUM('male', 'female') NULL;

-- CreateTable
CREATE TABLE `saint` (
    `saint_id` INTEGER NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(150) NOT NULL,
    `honorific` VARCHAR(50) NULL,
    `gender` ENUM('male', 'female') NOT NULL,
    `samuday_id` INTEGER NULL,
    `display_order` INTEGER NOT NULL DEFAULT 0,
    `is_active` BOOLEAN NOT NULL DEFAULT true,
    `created_by` INTEGER NULL,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at` DATETIME(3) NOT NULL,

    INDEX `saint_gender_idx`(`gender`),
    INDEX `saint_samuday_id_idx`(`samuday_id`),
    UNIQUE INDEX `saint_name_honorific_key`(`name`, `honorific`),
    PRIMARY KEY (`saint_id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- AddForeignKey
ALTER TABLE `saint` ADD CONSTRAINT `saint_samuday_id_fkey` FOREIGN KEY (`samuday_id`) REFERENCES `samuday`(`samuday_id`) ON DELETE SET NULL ON UPDATE CASCADE;