ALTER TABLE `user`
    ADD `payment_info` VARCHAR(255) NULL AFTER `allow_unlimited_free_adverts`;
ALTER TABLE `referred`
    ADD COLUMN `promotion_id` INT DEFAULT NULL,
    ADD CONSTRAINT `FK_PROMOTION_ID` FOREIGN KEY (`promotion_id`) REFERENCES `promotion_material` (`id`) ON
        DELETE
        SET NULL,
    ADD INDEX `IDX_PROMOTION_ID` (`promotion_id`);
CREATE TABLE IF NOT EXISTS `promotion_material`
(
    `id` INT NOT NULL AUTO_INCREMENT,
    `name`    VARCHAR(255)      DEFAULT NULL,
    `type`       INT      NOT NULL DEFAULT 1 COMMENT "1 for text, 2 for banner",
    `data`       VARCHAR(255)      DEFAULT NULL,
    `created_at` DATETIME NOT NULL,
    PRIMARY KEY (`id` )
) DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_0900_ai_ci;


-- after affiliate tracking code push
ALTER TABLE `user`
    DROP `payment_info`;
ALTER TABLE `user`
    ADD `company_name` VARCHAR(255) NULL
        AFTER `payment_info`,
    ADD `vat_number`   VARCHAR(255) NULL AFTER `company_name`,
    ADD `address`      TEXT         NULL AFTER `vat_number`,
    ADD `address2`     TEXT         NULL AFTER `address`,
    ADD `postcode`     VARCHAR(255) NULL AFTER `address2`,
    ADD `city`         VARCHAR(255) NULL AFTER `postocde`,
    ADD `country`      VARCHAR(255) NULL AFTER `city`;

CREATE TABLE IF NOT EXISTS `payment_information`
(
    `id`                  int NOT NULL AUTO_INCREMENT,
    `user_id`             int NOT NULL,
    `account_holder_name` varchar(255) DEFAULT NULL,
    `bank_name`           varchar(255) DEFAULT NULL,
    `bank_address`        text,
    `bank_city`           varchar(255) DEFAULT NULL,
    `swift_code`          varchar(255) DEFAULT NULL,
    `account_number`      text,
    PRIMARY KEY (`id`),
    KEY `User_ID_INDEX_FOR_PAYMENT` (`user_id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_0900_ai_ci;


ALTER TABLE `payment_information`
    ADD CONSTRAINT `payment_information_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT;

CREATE TABLE IF NOT EXISTS `promotion_performance`
(
    `id`           int NOT NULL AUTO_INCREMENT,
    `affiliate`    int          DEFAULT NULL,
    `user`         int          DEFAULT NULL,
    `promotion`    int          DEFAULT NULL,
    `http_referer` varchar(255) DEFAULT NULL,
    `ip_address`   varchar(255) DEFAULT NULL,
    `created_at`   date         DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `affiliate` (`affiliate`),
    KEY `user` (`user`),
    KEY `promotion` (`promotion`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_0900_ai_ci;


ALTER TABLE `promotion_performance`
    ADD CONSTRAINT `promotion_performance_ibfk_1` FOREIGN KEY (`affiliate`) REFERENCES `user` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
    ADD CONSTRAINT `promotion_performance_ibfk_2` FOREIGN KEY (`user`) REFERENCES `user` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
    ADD CONSTRAINT `promotion_performance_ibfk_3` FOREIGN KEY (`promotion`) REFERENCES `promotion_material` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT;

ALTER TABLE `eventadvert`
    ADD `paid_at` DATETIME NULL AFTER `premium`;



CREATE TABLE IF NOT EXISTS `affiliate_commission`
(
    `id`               int            NOT NULL AUTO_INCREMENT,
    `affiliate`        int            NOT NULL,
    `eventadvert_id`   int                     DEFAULT NULL,
    `eventadvert_type` int            NOT NULL DEFAULT '1' COMMENT '1 for normal adevt 2 for big premium advert',
    `total_commission` decimal(10, 2) NOT NULL,
    `paid_status`      int                     DEFAULT '0',
    `paid_at`          datetime                DEFAULT NULL,
    `payout_id`        int                     DEFAULT NULL,
    `created_at`       datetime       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `affiliate` (`affiliate`)
) ENGINE = InnoDB
  AUTO_INCREMENT = 16
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_0900_ai_ci;

ALTER TABLE `affiliate_commission`
    ADD CONSTRAINT `affiliate_commission_ibfk_1` FOREIGN KEY (`affiliate`) REFERENCES `user` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT;

CREATE TABLE IF NOT EXISTS `payouts`
(
    `id`           int NOT NULL AUTO_INCREMENT,
    `user_id`      int            DEFAULT NULL,
    `total_amount` decimal(10, 2) DEFAULT NULL,
    `payout_month` date           DEFAULT NULL,
    `status`       int            DEFAULT '0',
    `created_at`   datetime       DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `user` (`user_id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_0900_ai_ci;


ALTER TABLE `payouts`
    ADD CONSTRAINT `payouts_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT;

ALTER TABLE `notification`
    ADD `description` TEXT NULL AFTER `isRead`;
ALTER TABLE `notification`
    CHANGE `obj` `obj` BIGINT NULL;

ALTER TABLE `payouts`
    DROP `payout_month`;
ALTER TABLE `payouts`
    ADD `paid_at` DATETIME NULL AFTER `status`;
ALTER TABLE `affiliate_commission`
    CHANGE `total_commission` `total_commission` DECIMAL(10, 3) NULL DEFAULT NULL;
ALTER TABLE `referred`
    ADD `plan_type` VARCHAR(255) NULL AFTER `promotion_id`;
ALTER TABLE `referred`
    ADD `status` TINYINT(1) NULL DEFAULT '1' AFTER `plan_type`;
ALTER TABLE `affiliate_commission`
    ADD `plan_type` VARCHAR(255) NULL AFTER `payout_id`;
ALTER TABLE `payouts`
    CHANGE `total_amount` `total_amount` DECIMAL(10, 3) NULL DEFAULT NULL;
ALTER TABLE `affiliate_commission`
    ADD `user_id`         INT NULL
        AFTER `eventadvert_type`,
    ADD `commission_type` INT NOT NULL DEFAULT '1' COMMENT '1 for advert 2 for credits' AFTER `user_id`;
ALTER TABLE `affiliate_commission`
    CHANGE `commission_type` `commission_type` VARCHAR(255) NOT NULL DEFAULT 'advert';

ALTER TABLE `affiliate_commission`
    CHANGE `eventadvert_type` `eventadvert_type` INT NULL COMMENT '1 for normal advert 2 for big premium advert';
ALTER TABLE `affiliate_commission`
    ADD `transaction_id` INT NULL AFTER `commission_type`;


CREATE TABLE IF NOT EXISTS `signature`
(
    `id`         int      NOT NULL AUTO_INCREMENT,
    `user_id`    int      NOT NULL,
    `signature`  text,
    `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `user_id` (`user_id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_0900_ai_ci;

ALTER TABLE `signature`
    ADD CONSTRAINT `signature_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT;


