Бонусы (структура таблиц)

CREATE SCHEMA IF NOT EXISTS bonus
CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE IF NOT EXISTS bonus.bonus_segment
(
    id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY
    , name VARCHAR(150) NOT NULL UNIQUE COMMENT 'название сегмента'
    , segment_amount INT UNSIGNED NOT NULL COMMENT 'сумма бонусов за переход в этот сегмент'
    , segment_expiration_days INT UNSIGNED NOT NULL COMMENT 'число дней действия бонусов'
)
CHARACTER SET utf8 COLLATE utf8_general_ci
COMMENT 'Перечень сегментов с данными по бонусам';

CREATE TABLE IF NOT EXISTS bonus.bonus_customer_segment
(
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
    , customer_id INT(11) NOT NULL
    , bonus_segment_id SMALLINT UNSIGNED NOT NULL
    , created DATETIME NOT NULL
    , UNIQUE (customer_id, bonus_segment_id)
    , FOREIGN KEY (customer_id) REFERENCES yii_doc.customers(id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
    , FOREIGN KEY (bonus_segment_id) REFERENCES bonus.bonus_segment(id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
)
CHARACTER SET utf8 COLLATE utf8_general_ci
COMMENT 'История перехода клиентов по сегментам';

CREATE TABLE IF NOT EXISTS bonus.bonus_settings
(
    id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY
    , name VARCHAR(50) NOT NULL UNIQUE
    , value VARCHAR(50) NOT NULL
    , comment VARCHAR(150) NOT NULL COMMENT 'Описание назначения константы, ее единицы измерения'
)
CHARACTER SET utf8 COLLATE utf8_general_ci
COMMENT 'Таблица внутренних настроек для бонусов';

CREATE TABLE IF NOT EXISTS bonus.customer_bonus
(
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
    , customer_id INT(11) NOT NULL UNIQUE COMMENT 'ссылка на таблицу клиентов yii_doc.customers'
    , balance INT UNSIGNED DEFAULT 0 COMMENT 'текущий доступный баланс бонусов'
    , expiration_date DATETIME COMMENT 'дата окончания срока действия бонусов'
    , FOREIGN KEY (customer_id) REFERENCES yii_doc.customers(id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
    , INDEX(expiration_date DESC, customer_id DESC)
)
CHARACTER SET utf8 COLLATE utf8_general_ci
COMMENT 'Бонусы клиентов (актуальная информация)';

CREATE TABLE IF NOT EXISTS bonus.bonus_docs_credit_segment
(
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
    , bonus_customer_segment_id INT UNSIGNED NOT NULL UNIQUE COMMENT 'ссылка на таблицу bonus.bonus_customer_segment'
    , completed BIT(1) NOT NULL DEFAULT 0 COMMENT 'статус операции: 0 - новая, 1 - завершенная'
    , amount INT UNSIGNED NOT NULL
    , created DATETIME NOT NULL
    , updated DATETIME NOT NULL
    , FOREIGN KEY (bonus_customer_segment_id) REFERENCES bonus.bonus_customer_segment(id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
    , INDEX(completed DESC, bonus_customer_segment_id DESC)
)
    CHARACTER SET utf8 COLLATE utf8_general_ci
    COMMENT 'Документы: начисление бонусов за попадание клиентов в сегмент';

CREATE TABLE IF NOT EXISTS bonus.bonus_docs_debit
(
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
    , order_product_id INT(11) NOT NULL UNIQUE COMMENT 'ссылка на таблицу yii_doc.order_product'
    , completed BIT(1) NOT NULL DEFAULT 0 COMMENT 'статус операции: 0 - новая, 1 - завершенная'
    , amount INT UNSIGNED NOT NULL COMMENT 'количество бонусов за товар в заказе'
    , created DATETIME NOT NULL
    , updated DATETIME NOT NULL
    , FOREIGN KEY (order_product_id) REFERENCES yii_doc.order_product(id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
    , INDEX(completed DESC, order_product_id DESC)
)
CHARACTER SET utf8 COLLATE utf8_general_ci
COMMENT 'Документы: списания бонусов';

CREATE TABLE IF NOT EXISTS bonus.bonus_docs_credit_refund
(
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
    , refund_id INT(11) NOT NULL UNIQUE COMMENT 'ссылка на таблицу yii_doc.refund'
    , completed BIT(1) NOT NULL DEFAULT 0 COMMENT 'статус операции: 0 - новая, 1 - завершенная'
    , amount INT UNSIGNED NOT NULL
    , created DATETIME NOT NULL
    , updated DATETIME NOT NULL
    , FOREIGN KEY (refund_id) REFERENCES yii_doc.refund(id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
    , INDEX(completed DESC, refund_id DESC)
)
CHARACTER SET utf8 COLLATE utf8_general_ci
COMMENT 'Документы: возврат бонусов при возврате товара';

CREATE TABLE IF NOT EXISTS bonus.bonus_docs_nullify
(
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
    , customer_id INT(11) NOT NULL COMMENT 'ссылка на таблицу клиентов yii_doc.customers'
    , completed BIT(1) NOT NULL DEFAULT 0 COMMENT 'статус операции: 0 - новая, 1 - завершенная'
    , amount INT UNSIGNED NOT NULL
    , created DATETIME NOT NULL
    , updated DATETIME NOT NULL
    , FOREIGN KEY (customer_id) REFERENCES yii_doc.customers(id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
    , INDEX(completed, customer_id)
)
CHARACTER SET utf8 COLLATE utf8_general_ci
COMMENT 'Документы: списывание бонусов по сроку жизни';

CREATE TABLE IF NOT EXISTS bonus.bonus_operations_history
(
    id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY
    , customer_id INT(11) NOT NULL COMMENT 'ссылка на таблицу клиентов yii_doc.customers'
    , operation_type ENUM('debit', 'credit_segment', 'credit_reverse', 'credit_refund', 'nullify') NOT NULL
        COMMENT 'указатель на связанную таблицу: "debit" - bonus.bonus_docs_debit; "credit_segment" - bonus.bonus_docs_credit_segment; "credit_reverse" - bonus.bonus_docs_debit; "credit_refund" - bonus.bonus_docs_credit_refund; "nullify" - bonus.bonus_docs_nullify'
    , operation_id INT UNSIGNED NOT NULL COMMENT 'id записи из таблицы указанной в operation_type'
    , amount INT NOT NULL COMMENT 'Сумма операции'
    , balance_before INT UNSIGNED NOT NULL COMMENT 'Общий баланс до операции'
    , balance_after INT UNSIGNED NOT NULL COMMENT 'Общий баланс после операции'
    , created DATETIME NOT NULL DEFAULT NOW() COMMENT 'Время создания записи'
    , FOREIGN KEY (customer_id) REFERENCES yii_doc.customers(id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
    , INDEX(customer_id, operation_type, operation_id)
)
CHARACTER SET utf8 COLLATE utf8_general_ci
COMMENT 'История операций по движению бонусов клиента';


# !!!!! простой ОТКАТ миграции !!!!!
DROP SCHEMA IF EXISTS bonus;