-- AlterTable
ALTER TABLE `transactions` ALTER COLUMN `transaction_date` DROP DEFAULT;

-- CreateTable
CREATE TABLE `account_deletion_requests` (
    `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` INTEGER UNSIGNED NOT NULL,
    `society_id` INTEGER UNSIGNED NOT NULL,
    `reason` VARCHAR(1000) NOT NULL,
    `status` ENUM('pending', 'approved', 'rejected') NOT NULL DEFAULT 'pending',
    `reviewed_by` INTEGER UNSIGNED NULL,
    `reviewed_at` TIMESTAMP(0) NULL,
    `rejection_reason` VARCHAR(500) NULL,
    `created_at` TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0),
    `updated_at` TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0),

    INDEX `idx_account_deletion_user_id`(`user_id`),
    INDEX `idx_account_deletion_society_id`(`society_id`),
    INDEX `idx_account_deletion_status`(`status`),
    INDEX `idx_account_deletion_reviewed_by`(`reviewed_by`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- AddForeignKey
ALTER TABLE `account_deletion_requests` ADD CONSTRAINT `account_deletion_requests_user_id_fkey` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `account_deletion_requests` ADD CONSTRAINT `account_deletion_requests_society_id_fkey` FOREIGN KEY (`society_id`) REFERENCES `society`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `account_deletion_requests` ADD CONSTRAINT `account_deletion_requests_reviewed_by_fkey` FOREIGN KEY (`reviewed_by`) REFERENCES `users`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;