CREATE TABLE `cancellations` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `userpackage_id` INT NOT NULL,
    `reason` TEXT NOT NULL,
    `status` TINYINT NOT NULL,
    `requested_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `effective_type` TINYINT NOT NULL,
    `effective_at` DATETIME NULL,
    `processed_at` DATETIME NULL,
    PRIMARY KEY (`id`),
    INDEX (`userpackage_id`),
    INDEX (`status`),
    INDEX (`effective_at`)
) ENGINE=MyISAM DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE `orders` (
    `id` int NOT NULL AUTO_INCREMENT,
    `client_id` int NOT NULL,
    `invoice_id` int DEFAULT NULL,
    `affiliate_id` int DEFAULT NULL,
    `order_date` datetime NOT NULL,
    `status` tinyint NOT NULL,
    `ip_address` varchar(45) NOT NULL,
    `user_agent` varchar(255) NOT NULL,
    `fraud_plugin` varchar(50) DEFAULT NULL,
    `risk_score` float DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `client_id` (`client_id`),
    KEY `invoice_id` (`invoice_id`),
    KEY `status` (`status`)
) ENGINE=MyISAM DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE `order_items` (
    `id` int NOT NULL AUTO_INCREMENT,
    `order_id` int NOT NULL,
    `package_id` int NOT NULL,
    PRIMARY KEY (`id`),
    KEY `order_id` (`order_id`),
    KEY `package_id` (`package_id`)
) ENGINE=MyISAM DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE `order_risk_factors` (
    `id` int NOT NULL AUTO_INCREMENT,
    `order_id` int NOT NULL,
    `factor_key` varchar(50) NOT NULL,
    `label` varchar(100) NOT NULL,
    `value` varchar(100) NOT NULL,
    `status` tinyint(1) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`),
    KEY `order_id` (`order_id`),
    KEY `factor_key` (`factor_key`)
) ENGINE=MyISAM DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

INSERT INTO `customuserfields` (`id`, `company_id`, `name`, `type`, `isrequired`, `isChangable`, `isAdminOnly`, `width`, `myOrder`, `showcustomer`, `showadmin`, `InSignup`, `showingridadmin`, `inSettings`, `dropdownoptions`, `desc`) VALUES
(NULL, 0, 'TwoFactor Secret', 0, 0, 0, 0, 20, 0, 0, 0, 0, 0, 0, '', ''),
(NULL, 0, 'TwoFactor Enabled', 0, 0, 0, 0, 20, 0, 0, 0, 0, 0, 0, '', ''),
(NULL, 0, 'TwoFactor Plugin', 0, 0, 0, 0, 20, 0, 0, 0, 0, 0, 0, '', '');
