/*
  Add gig provider requests table.
*/

CREATE TABLE `cg_tbl_gig_provider_requests` (
    `id` BIGINT NOT NULL AUTO_INCREMENT,
    `gig_id` BIGINT NOT NULL,
    `provider_id` BIGINT NOT NULL,
    `provider_role_id` BIGINT NOT NULL,
    `status` ENUM('pending', 'accepted', 'rejected') NOT NULL DEFAULT 'pending',
    `platform` ENUM('web', 'app') NOT NULL DEFAULT 'app',
    `created_at` DATETIME NULL,
    `updated_at` DATETIME NULL,

    INDEX `cg_tbl_gig_provider_requests_gig_id_fkey`(`gig_id`),
    INDEX `cg_tbl_gig_provider_requests_provider_role_id_fkey`(`provider_role_id`),
    INDEX `cg_tbl_gig_provider_requests_provider_id_idx`(`provider_id`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ALTER TABLE `cg_tbl_gig_provider_requests`
    ADD CONSTRAINT `cg_tbl_gig_provider_requests_gig_id_fkey`
    FOREIGN KEY (`gig_id`) REFERENCES `cg_tbl_gigs`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    ADD CONSTRAINT `cg_tbl_gig_provider_requests_provider_role_id_fkey`
    FOREIGN KEY (`provider_role_id`) REFERENCES `cg_tbl_user_roles`(`id`) ON DELETE NO ACTION ON UPDATE CASCADE;