/*
  Add user earnings table.
*/

CREATE TABLE `cg_tbl_user_earnings` (
    `id` BIGINT NOT NULL AUTO_INCREMENT,
    `user_id` BIGINT NOT NULL,
    `user_role_id` BIGINT NOT NULL,
    `total_earned` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `total_settled` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `available_balance` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `hold_balance` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `platform` ENUM('app', 'web') NOT NULL DEFAULT 'app',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX `cg_tbl_user_earnings_user_id_idx`(`user_id`),
    INDEX `cg_tbl_user_earnings_user_role_id_fkey`(`user_role_id`),
    INDEX `cg_tbl_user_earnings_platform_idx`(`platform`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ALTER TABLE `cg_tbl_user_earnings`
    ADD CONSTRAINT `cg_tbl_user_earnings_user_role_id_fkey`
    FOREIGN KEY (`user_role_id`) REFERENCES `cg_tbl_user_roles`(`id`) ON DELETE NO ACTION ON UPDATE CASCADE;