Referral Migration

Version20150701100015.php

<?php

namespace Application\Migrations;

use Doctrine\DBAL\Migrations\AbstractMigration;
use Doctrine\DBAL\Schema\Schema;
use Doctrine\DBAL\Types\Type;

/**
 * Create tables for the referral system.
 *
 * Class Version20150701100015
 * @package Application\Migrations
 */
class Version20150701100015 extends AbstractMigration
{
    /**
     * @param Schema $schema
     */
    public function up(Schema $schema)
    {
        // Rewards table
        $this->createRewardTable($schema);

        // Rewards fulfilment table
        $this->createRewardFulfilmentTable($schema);

        // Referral table
        $this->createReferralTable($schema);

        // Shout box tables
        $this->createReferralTable($schema);
    }

    /**
     * @param Schema $schema
     */
    public function down(Schema $schema)
    {
        $schema->dropTable('reward');
        $schema->dropTable('reward_fulfilment');
        $schema->dropTable('referral');
        $schema->dropTable('shoutbox');
        $schema->dropTable('shoutbox_id');
    }

    /**
     * Create the `reward` table.
     *
     * @param Schema $schema
     */
    private function createRewardTable(Schema $schema)
    {
        $rewards = $schema->createTable('reward');
        $rewards->addColumn('id', Type::INTEGER, array(
            'unsigned' => true,
            'autoincrement' => true,
        ));
        $rewards->addColumn('product_id', Type::INTEGER, array(
            'length' => 10,
            'nullable' => true
        ));
        $rewards->addColumn('amount', Type::DECIMAL, array(
            'nullable' => true
        ));
        $rewards->setPrimaryKey(['id']);
        $rewards->addIndex(['product_id']);
        $rewards->addForeignKeyConstraint('products', ['product_id'], ['id']);
    }

    /**
     * Create the `reward_fulfilment` table.
     *
     * @param Schema $schema
     */
    private function createRewardFulfilmentTable(Schema $schema)
    {
        $rewardFulfilment = $schema->createTable('reward_fulfilment');
        $rewardFulfilment->addColumn('id', Type::INTEGER, array(
            'unsigned' => true,
            'autoincrement' => true,
        ));
        $rewardFulfilment->addColumn('reward_id', Type::INTEGER, array(
            'unsigned' => true
        ));
        $rewardFulfilment->addColumn('user_id', Type::INTEGER, array(
            'length' => 10,
            'unsigned' => true
        ));
        $rewardFulfilment->setPrimaryKey(['id']);
        $rewardFulfilment->addIndex(['reward_id']);
        $rewardFulfilment->addIndex(['user_id']);
        $rewardFulfilment->addForeignKeyConstraint('reward', ['reward_id'], ['id']);
    }

    /**
     * Create the `referral` table.
     *
     * @param Schema $schema
     */
    private function createReferralTable(Schema $schema)
    {
        $referral = $schema->createTable('referral');
        $referral->addColumn('id', Type::INTEGER, array(
            'unsigned' => true,
            'autoincrement' => true,
        ));
        $referral->addColumn('user_id', Type::INTEGER, array(
            'length' => 10,
            'unsigned' => true
        ));
        $referral->addColumn('reward_id', Type::INTEGER, array(
            'unsigned' => true
        ));
        $referral->addColumn('referee_reward_id', Type::INTEGER, array(
            'unsigned' => true
        ));
        $referral->addColumn('name', Type::STRING, array(
            'nullable' => true
        ));
        $referral->addColumn('email', Type::STRING, array(
            'nullable' => true
        ));
        $referral->addColumn('status', Type::STRING, array(
            'nullable' => true
        ));
        $referral->addColumn('deleted', Type::BOOLEAN, array(
            'nullable' => true
        ));
        $referral->addColumn('created', Type::DATETIME, array(
            'nullable' => false
        ));
        $referral->setPrimaryKey(['id']);
        $referral->addIndex(['user_id']);
        $referral->addIndex(['reward_id']);
        $referral->addIndex(['referee_reward_id']);
        $referral->addIndex(['email']);
        $referral->addForeignKeyConstraint('reward', ['reward_id'], ['id']);
        $referral->addForeignKeyConstraint('reward', ['referee_reward_id'], ['id']);
        $referral->addForeignKeyConstraint('acl_user', ['user_id'], ['id']);
    }

    /**
     * @param Schema $schema
     */
    public function createShoutBoxTables(Schema $schema)
    {
        $shoutBox = $schema->createTable('shoutbox');
        $shoutBox->addColumn('id', Type::INTEGER, array(
            'unsigned' => true,
            'autoincrement' => true,
        ));
        $shoutBox->addColumn('name', Type::string);
        $shoutBox->addColumn('icon', Type::INTEGER);
        $shoutBox->addColumn('title', Type::string);
        $shoutBox->addColumn('content', Type::TEXT);
        $shoutBox->addColumn('active', Type::STRING); // active/inactive
        $shoutBox->addColumn('deleted', Type::BOOLEAN, array(
            'nullable' => true
        ));
        $shoutBox->setPrimaryKey(['id']);
        $shoutBox->addIndex(['name']);

        $shoutBoxActions = $schema->createTable('shoutbox_actions');
        $shoutBoxActions->addColumn('id', Type::INTEGER, array(
            'unsigned' => true,
            'autoincrement' => true,
        ));
        $shoutBoxActions->addColumn('shoutbox_id', Type::INTEGER, array(
            'unsigned' => true
        ));
        $shoutBoxActions->addColumn('label', Type::INTEGER);
        $shoutBoxActions->addColumn('icon', Type::string);
        $shoutBoxActions->addColumn('url', Type::STRING);
        $shoutBoxActions->setPrimaryKey(['id']);
        $shoutBoxActions->addIndex(['shoutbox_id']);
        $shoutBoxActions->addForeignKeyConstraint('shoutbox', ['shoutbox_id'], ['id']);
    }
}