-- Add HoldStatus enum and UserEarningHold table

-- CreateTable
CREATE TABLE `cg_tbl_user_earning_holds` (
    `id` BIGINT NOT NULL AUTO_INCREMENT,
    `user_earning_id` BIGINT NOT NULL,
    `gig_id` BIGINT NOT NULL,
    `gig_service_id` BIGINT NULL,
    `hold_amount` DECIMAL(12, 2) NOT NULL DEFAULT 0.00,
    `released_amount` DECIMAL(12, 2) NOT NULL DEFAULT 0.00,
    `release_at` TIMESTAMP NULL,
    `released_at` TIMESTAMP NULL,
    `grievance_id` BIGINT NULL,
    `status` ENUM('ACTIVE', 'UNDER_GRIEVANCE', 'RELEASED', 'DEDUCTED', 'CANCELLED') NOT NULL DEFAULT 'ACTIVE',
    `released_by` BIGINT NULL,
    `remarks` TEXT NULL,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX `cg_tbl_user_earning_holds_user_earning_id_idx`(`user_earning_id`),
    INDEX `cg_tbl_user_earning_holds_gig_id_idx`(`gig_id`),
    INDEX `cg_tbl_user_earning_holds_gig_service_id_idx`(`gig_service_id`),
    INDEX `cg_tbl_user_earning_holds_status_idx`(`status`),
    INDEX `cg_tbl_user_earning_holds_release_at_idx`(`release_at`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- AddForeignKey
ALTER TABLE `cg_tbl_user_earning_holds`
    ADD CONSTRAINT `cg_tbl_user_earning_holds_user_earning_id_fkey`
    FOREIGN KEY (`user_earning_id`) REFERENCES `cg_tbl_user_earnings`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `cg_tbl_user_earning_holds`
    ADD CONSTRAINT `cg_tbl_user_earning_holds_gig_id_fkey`
    FOREIGN KEY (`gig_id`) REFERENCES `cg_tbl_gigs`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `cg_tbl_user_earning_holds`
    ADD CONSTRAINT `cg_tbl_user_earning_holds_gig_service_id_fkey`
    FOREIGN KEY (`gig_service_id`) REFERENCES `cg_tbl_gig_services`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;