/**
  TABLE SCHEMA CHANGES
 */

# Accounting Periods
ALTER TABLE `bu_accounting_periods` ADD COLUMN `notes` VARCHAR(1000) NULL DEFAULT NULL AFTER `period`;

# Accounts
ALTER TABLE `bu_accounts` CHANGE COLUMN `account_id` `uid` VARCHAR(5) NULL DEFAULT NULL AFTER `id`;
ALTER TABLE `bu_accounts` CHANGE COLUMN `name` `description` VARCHAR(128) NULL DEFAULT NULL AFTER `uid`;
ALTER TABLE `bu_accounts` CHANGE COLUMN `bank_id` `bank_uid` VARCHAR(5) NULL DEFAULT NULL AFTER `notes`;
ALTER TABLE `bu_accounts` CHANGE COLUMN `account_id_alpha` `account_uid_alpha` VARCHAR(1) NULL DEFAULT NULL AFTER `status`;
ALTER TABLE `bu_accounts` MODIFY `status` VARCHAR(10) DEFAULT 'Open' NOT NULL;

# Admin
ALTER TABLE `bu_admin` ADD COLUMN `notes` VARCHAR(1000) NULL DEFAULT NULL AFTER `password_hash`;

# Banks
ALTER TABLE `bu_banks` CHANGE COLUMN `bank_id` `uid` VARCHAR(5) NULL DEFAULT NULL AFTER `id`;
ALTER TABLE `bu_banks` CHANGE COLUMN `legal_name` `description` VARCHAR(128) NULL DEFAULT NULL AFTER `uid`;

# Entities
ALTER TABLE `bu_entities` CHANGE COLUMN `entity_id` `uid` VARCHAR(5) NULL DEFAULT NULL AFTER `id`;
ALTER TABLE `bu_entities` CHANGE COLUMN `entity_description` `description` VARCHAR(128) NULL DEFAULT NULL AFTER `uid`;
ALTER TABLE `bu_entities` ADD COLUMN `notes` VARCHAR(1000) NULL DEFAULT NULL AFTER `description`;
ALTER TABLE `bu_entities` COMMENT = 'category:lookup';

# Monthly Spend
ALTER TABLE `bu_monthly_spend` ADD COLUMN `notes` VARCHAR(1000) NULL DEFAULT NULL AFTER `end`;

# Prefills
ALTER TABLE `bu_prefills` CHANGE COLUMN `account_id_alpha` `account_uid_alpha` VARCHAR(1) NULL DEFAULT NULL AFTER `id`;
ALTER TABLE `bu_prefills` CHANGE COLUMN `account_id` `account_uid` VARCHAR(5) NULL DEFAULT NULL AFTER `account_uid_alpha`;
ALTER TABLE `bu_prefills` CHANGE COLUMN `entity_id` `entity_uid` VARCHAR(5) NULL DEFAULT NULL AFTER `account_uid`;
ALTER TABLE `bu_prefills` CHANGE COLUMN `type_id` `type_uid_old` VARCHAR(5) NULL DEFAULT NULL AFTER `entity_uid`;
ALTER TABLE `bu_prefills` ADD COLUMN `type_uid` VARCHAR(5) NULL DEFAULT NULL AFTER `type_uid_old`;
ALTER TABLE `bu_prefills` CHANGE COLUMN `sub_type_id` `sub_type_uid_old` VARCHAR(5) NULL DEFAULT NULL AFTER `type_uid`;
ALTER TABLE `bu_prefills` ADD COLUMN `sub_type_uid` VARCHAR(5) NULL DEFAULT NULL AFTER `sub_type_uid_old`;
ALTER TABLE `bu_prefills` CHANGE COLUMN `method_id` `method_uid` VARCHAR(5) NULL DEFAULT NULL AFTER `sub_type_uid`;

# Reconciliation
ALTER TABLE `bu_reconcilliation` ADD COLUMN `notes` VARCHAR(1000) NULL DEFAULT NULL AFTER `end`;

# Regular Debit Types
ALTER TABLE `bu_regular_debit_types` CHANGE COLUMN `type` `type_old` SMALLINT NULL DEFAULT NULL AFTER `id`;
ALTER TABLE `bu_regular_debit_types` ADD COLUMN `uid` VARCHAR(5) NULL DEFAULT NULL AFTER `type_old`;
ALTER TABLE `bu_regular_debit_types` CHANGE COLUMN `description` `description` VARCHAR(128) NULL DEFAULT NULL  AFTER `uid`;
ALTER TABLE `bu_regular_debit_types` ADD COLUMN `notes` VARCHAR(1000) NULL DEFAULT NULL AFTER `description`;
ALTER TABLE `bu_regular_debit_types` COMMENT = 'category:lookup';

# Regular Debits
ALTER TABLE `bu_regular_debits` CHANGE COLUMN `account_id_alpha` `account_uid_alpha` VARCHAR(1) NULL DEFAULT NULL AFTER `id`;
ALTER TABLE `bu_regular_debits` CHANGE COLUMN `account_id` `account_uid` VARCHAR(5) NULL DEFAULT NULL AFTER `account_uid_alpha`;
ALTER TABLE `bu_regular_debits` CHANGE COLUMN `entity_id` `entity_uid` VARCHAR(5) NULL DEFAULT NULL AFTER `account_uid`;
ALTER TABLE `bu_regular_debits` CHANGE COLUMN `type_id` `type_uid_old` VARCHAR(5) NULL DEFAULT NULL AFTER `entity_uid`;
ALTER TABLE `bu_regular_debits` ADD COLUMN `type_uid` VARCHAR(5) NULL DEFAULT NULL AFTER `type_uid_old`;
ALTER TABLE `bu_regular_debits` CHANGE COLUMN `sub_type_id` `sub_type_uid_old` VARCHAR(5) NULL DEFAULT NULL AFTER `type_uid`;
ALTER TABLE `bu_regular_debits` ADD COLUMN `sub_type_uid` VARCHAR(5) NULL DEFAULT NULL AFTER `sub_type_uid_old`;
ALTER TABLE `bu_regular_debits` CHANGE COLUMN `method_id` `method_uid` VARCHAR(5) NULL DEFAULT NULL AFTER `sub_type_uid`;
ALTER TABLE `bu_regular_debits` CHANGE COLUMN `regular_debit_type` `regular_debit_type_uid_old` SMALLINT NULL DEFAULT NULL AFTER `method_uid`;
ALTER TABLE `bu_regular_debits` ADD COLUMN `regular_debit_type_uid` VARCHAR(5) NULL DEFAULT NULL AFTER `regular_debit_type_uid_old`;
ALTER TABLE `bu_regular_debits` CHANGE COLUMN `day` `day` SMALLINT(2) NULL DEFAULT NULL AFTER `amount`;
ALTER TABLE `bu_regular_debits` ADD COLUMN `week` SMALLINT(2) NULL DEFAULT NULL AFTER `day`;
ALTER TABLE `bu_regular_debits` ADD COLUMN `last_processed_pre_adjusted` DATE NULL DEFAULT NULL AFTER `week`;
ALTER TABLE `bu_regular_debits` CHANGE COLUMN `last` `last_processed` DATE NULL DEFAULT NULL AFTER `last_processed_pre_adjusted`;
ALTER TABLE `bu_regular_debits` ADD COLUMN `last_processed_period` SMALLINT NULL DEFAULT NULL AFTER `last_processed`;
ALTER TABLE `bu_regular_debits` ADD COLUMN `next_to_process_pre_adjusted` DATE NULL DEFAULT NULL AFTER `last_processed_period`;
ALTER TABLE `bu_regular_debits` ADD COLUMN `next_to_process` DATE NULL DEFAULT NULL AFTER `next_to_process_pre_adjusted`;
ALTER TABLE `bu_regular_debits` ADD COLUMN `next_to_process_period` SMALLINT NULL DEFAULT NULL AFTER `next_to_process`;
ALTER TABLE `bu_regular_debits` ADD COLUMN `subsequent_period_to_process_pre_adjusted` DATE NULL DEFAULT NULL AFTER `next_to_process_period`;
ALTER TABLE `bu_regular_debits` CHANGE COLUMN `next` `subsequent_period_to_process` DATE NULL DEFAULT NULL AFTER `subsequent_period_to_process_pre_adjusted`;
ALTER TABLE `bu_regular_debits` ADD COLUMN `subsequent_period_to_process_period` SMALLINT NULL DEFAULT NULL AFTER `subsequent_period_to_process`;
ALTER TABLE `bu_regular_debits` ADD COLUMN `before_weekend` TINYINT(1) NULL DEFAULT 0 AFTER `period`;
ALTER TABLE `bu_regular_debits` ADD COLUMN `after_weekend` TINYINT(1) NULL DEFAULT 1 AFTER `before_weekend`;
ALTER TABLE `bu_regular_debits` CHANGE COLUMN `notes` `notes` VARCHAR(1000) NULL DEFAULT NULL AFTER `after_weekend`;

# Settings
ALTER TABLE `bu_settings` CHANGE COLUMN `reconcilliation_first_period` `reconciliation_first_period` SMALLINT NULL DEFAULT NULL AFTER `current_period`;
ALTER TABLE `bu_settings` CHANGE COLUMN `reconcilliation_opening_balance` `reconciliation_opening_balance` DECIMAL(10,2) NULL DEFAULT NULL AFTER `reconciliation_first_period`;
ALTER TABLE `bu_settings` ADD COLUMN `notes` VARCHAR(1000) NULL DEFAULT NULL AFTER `number_of_tax_years`;
ALTER TABLE `bu_settings` DROP COLUMN `current_period`, DROP COLUMN `current_start`, DROP COLUMN `current_end`;

# Transaction Methods
ALTER TABLE `bu_transaction_methods` CHANGE COLUMN `method_id` `uid` VARCHAR(5) NULL DEFAULT NULL AFTER `id`;
ALTER TABLE `bu_transaction_methods` CHANGE COLUMN `method_description` `description` VARCHAR(128) NULL DEFAULT NULL  AFTER `uid`;
ALTER TABLE `bu_transaction_methods` ADD COLUMN `notes` VARCHAR(1000) NULL DEFAULT NULL AFTER `description`;
ALTER TABLE `bu_transaction_methods` COMMENT = 'category:lookup';


# Transaction Sub Types
ALTER TABLE `bu_transaction_sub_types` CHANGE COLUMN `sub_type_id` `sub_type_id_old` VARCHAR(5) NULL DEFAULT NULL AFTER `id`;
ALTER TABLE `bu_transaction_sub_types` ADD COLUMN `uid` VARCHAR(5) NULL DEFAULT NULL AFTER `sub_type_id_old`;
ALTER TABLE `bu_transaction_sub_types` CHANGE COLUMN `sub_type_description` `description` VARCHAR(128) NULL DEFAULT NULL  AFTER `uid`;
ALTER TABLE `bu_transaction_sub_types` ADD COLUMN `notes` VARCHAR(1000) NULL DEFAULT NULL AFTER `description`;
ALTER TABLE `bu_transaction_sub_types` COMMENT = 'category:lookup';

# Tax Years
ALTER TABLE `bu_tax_years` ADD COLUMN `notes` VARCHAR(1000) NULL DEFAULT NULL AFTER `tax_year`;

# Transaction Types
ALTER TABLE `bu_transaction_types` CHANGE COLUMN `type_id` `type_id_old` VARCHAR(5) NULL DEFAULT NULL AFTER `id`;
ALTER TABLE `bu_transaction_types` ADD COLUMN `uid` VARCHAR(5) NULL DEFAULT NULL AFTER `type_id_old`;
ALTER TABLE `bu_transaction_types` CHANGE COLUMN `type_description` `description` VARCHAR(128) NULL DEFAULT NULL  AFTER `uid`;
ALTER TABLE `bu_transaction_types` ADD COLUMN `notes` VARCHAR(1000) NULL DEFAULT NULL AFTER `description`;
ALTER TABLE `bu_transaction_types` COMMENT = 'category:lookup';

# Transactions
ALTER TABLE `bu_transactions` CHANGE COLUMN `account_id_alpha` `account_uid_alpha` VARCHAR(1) NULL DEFAULT NULL AFTER `id`;
ALTER TABLE `bu_transactions` CHANGE COLUMN `account_id` `account_uid` VARCHAR(5) NULL DEFAULT NULL AFTER `account_uid_alpha`;
ALTER TABLE `bu_transactions` CHANGE COLUMN `entity_id` `entity_uid` VARCHAR(5) NULL DEFAULT NULL AFTER `account_uid`;
ALTER TABLE `bu_transactions` CHANGE COLUMN `type_id` `type_uid_old` VARCHAR(5) NULL DEFAULT NULL AFTER `entity_uid`;
ALTER TABLE `bu_transactions` ADD COLUMN `type_uid` VARCHAR(5) NULL DEFAULT NULL AFTER `type_uid_old`;
ALTER TABLE `bu_transactions` CHANGE COLUMN `sub_type_id` `sub_type_uid_old` VARCHAR(5) NULL DEFAULT NULL AFTER `type_uid`;
ALTER TABLE `bu_transactions` ADD COLUMN `sub_type_uid` VARCHAR(5) NULL DEFAULT NULL AFTER `sub_type_uid_old`;
ALTER TABLE `bu_transactions` CHANGE COLUMN `method_id` `method_uid` VARCHAR(5) NULL DEFAULT NULL AFTER `sub_type_uid`;
ALTER TABLE `bu_transactions` CHANGE COLUMN `transaction_date` `effective_date` DATE NULL DEFAULT NULL AFTER `period`;

/**
  DATA CHANGES
 */

# Accounts
UPDATE `bu_accounts` SET `sort_code` = REPLACE(`sort_code`, '-', '');
UPDATE `bu_accounts` SET `account_number` = REPLACE(`account_number`, 'X', '');

# Regular Debits
UPDATE `bu_regular_debits` SET `subsequent_period_to_process_pre_adjusted` = `subsequent_period_to_process`;
UPDATE `bu_regular_debits` SET `next_to_process` = `last_processed`;
UPDATE `bu_regular_debits` SET `next_to_process_pre_adjusted` = `last_processed`;
UPDATE `bu_regular_debits` SET `last_processed_pre_adjusted` = `last_processed`;
UPDATE `bu_regular_debits` SET `before_weekend` = 1 WHERE (`entity_uid` = 'E7590');
UPDATE `bu_regular_debits` SET `after_weekend` = 0 WHERE (`entity_uid` = 'E7590');
UPDATE `bu_regular_debits` SET `before_weekend` = 1 WHERE (`entity_uid` = 'E4005');
UPDATE `bu_regular_debits` SET `after_weekend` = 0 WHERE (`entity_uid` = 'E4005');
UPDATE `bu_regular_debits` SET `after_weekend` = 0 WHERE (`entity_uid` = 'E0028');
UPDATE `bu_regular_debits` SET `after_weekend` = 0 WHERE (`entity_uid` = 'E3085');
UPDATE `bu_regular_debits` SET `after_weekend` = 0 WHERE (`entity_uid` = 'E2899');
UPDATE `bu_regular_debits` SET `before_weekend` = 0 WHERE (`entity_uid` = 'E9419');
UPDATE `bu_regular_debits` SET `after_weekend` = 0 WHERE (`entity_uid` = 'E9419');
UPDATE `bu_regular_debits` SET `day` = NULL WHERE (`entity_uid` = 'E9419');
UPDATE `bu_regular_debits` SET `week` = 4 WHERE (`entity_uid` = 'E9419');


# Regular Debit Types
UPDATE `bu_regular_debit_types` SET `uid` = 'R4765' WHERE (`type_old` = '1');
UPDATE `bu_regular_debit_types` SET `uid` = 'R6209' WHERE (`type_old` = '2');
UPDATE `bu_regular_debit_types` SET `uid` = 'R3114' WHERE (`type_old` = '3');
UPDATE `bu_regular_debit_types` SET `uid` = 'R5374' WHERE (`type_old` = '4');
UPDATE `bu_regular_debit_types` SET `uid` = 'R4899' WHERE (`type_old` = '5');
UPDATE `bu_regular_debit_types` SET `uid` = 'R7740' WHERE (`type_old` = '6');
UPDATE `bu_regular_debit_types` SET `uid` = 'R0749' WHERE (`type_old` = '7');
UPDATE `bu_regular_debit_types` SET `uid` = 'R5384' WHERE (`type_old` = '8');

# Transaction Sub Types
UPDATE `bu_transaction_sub_types` SET `uid` = 'S6152' WHERE (`sub_type_id_old` = '1');
UPDATE `bu_transaction_sub_types` SET `uid` = 'S1949' WHERE (`sub_type_id_old` = '2');
UPDATE `bu_transaction_sub_types` SET `uid` = 'S1333' WHERE (`sub_type_id_old` = '3');
UPDATE `bu_transaction_sub_types` SET `uid` = 'S5253' WHERE (`sub_type_id_old` = '4');
UPDATE `bu_transaction_sub_types` SET `uid` = 'S4303' WHERE (`sub_type_id_old` = '5');
UPDATE `bu_transaction_sub_types` SET `uid` = 'S0372' WHERE (`sub_type_id_old` = '6');
UPDATE `bu_transaction_sub_types` SET `uid` = 'S9586' WHERE (`sub_type_id_old` = '7');
UPDATE `bu_transaction_sub_types` SET `uid` = 'S7745' WHERE (`sub_type_id_old` = '8');
UPDATE `bu_transaction_sub_types` SET `uid` = 'S2261' WHERE (`sub_type_id_old` = '99');
UPDATE `bu_transaction_sub_types` SET `uid` = 'S3213' WHERE (`sub_type_id_old` = 'A');
UPDATE `bu_transaction_sub_types` SET `uid` = 'S8362' WHERE (`sub_type_id_old` = 'B');
UPDATE `bu_transaction_sub_types` SET `uid` = 'S5876' WHERE (`sub_type_id_old` = 'C');
UPDATE `bu_transaction_sub_types` SET `uid` = 'S6099' WHERE (`sub_type_id_old` = 'D');
UPDATE `bu_transaction_sub_types` SET `uid` = 'S4998' WHERE (`sub_type_id_old` = 'E');
UPDATE `bu_transaction_sub_types` SET `uid` = 'S0776' WHERE (`sub_type_id_old` = 'O');
UPDATE `bu_transaction_sub_types` SET `uid` = 'S3312' WHERE (`sub_type_id_old` = 'T');
UPDATE `bu_transaction_sub_types` SET `uid` = 'S9056' WHERE (`sub_type_id_old` = 'X');
UPDATE `bu_transaction_sub_types` SET `uid` = 'S5020' WHERE (`sub_type_id_old` = '25');
UPDATE `bu_transaction_sub_types` SET `uid` = 'S3128' WHERE (`sub_type_id_old` = '26');
UPDATE `bu_transaction_sub_types` SET `uid` = 'S9890' WHERE (`sub_type_id_old` = '27');
UPDATE `bu_transaction_sub_types` SET `uid` = 'S5593' WHERE (`sub_type_id_old` = '28');
UPDATE `bu_transaction_sub_types` SET `uid` = 'S4036' WHERE (`sub_type_id_old` = '29');
UPDATE `bu_transaction_sub_types` SET `uid` = 'S6975' WHERE (`sub_type_id_old` = '30');
UPDATE `bu_transaction_sub_types` SET `uid` = 'S9032' WHERE (`sub_type_id_old` = '31');
UPDATE `bu_transaction_sub_types` SET `uid` = 'S5892' WHERE (`sub_type_id_old` = '32');
UPDATE `bu_transaction_sub_types` SET `uid` = 'S5959' WHERE (`sub_type_id_old` = '33');
UPDATE `bu_transaction_sub_types` SET `uid` = 'S7648' WHERE (`sub_type_id_old` = '40');
UPDATE `bu_transaction_sub_types` SET `uid` = 'S4886' WHERE (`sub_type_id_old` = '50');

# Transaction Types
UPDATE `bu_transaction_types` SET `uid` = 'T6152' WHERE (`type_id_old` = '1');
UPDATE `bu_transaction_types` SET `uid` = 'T1949' WHERE (`type_id_old` = '2');
UPDATE `bu_transaction_types` SET `uid` = 'T1333' WHERE (`type_id_old` = '3');
UPDATE `bu_transaction_types` SET `uid` = 'T5253' WHERE (`type_id_old` = '4');
UPDATE `bu_transaction_types` SET `uid` = 'T4303' WHERE (`type_id_old` = '5');
UPDATE `bu_transaction_types` SET `uid` = 'T0372' WHERE (`type_id_old` = '6');
UPDATE `bu_transaction_types` SET `uid` = 'T9586' WHERE (`type_id_old` = '7');
UPDATE `bu_transaction_types` SET `uid` = 'T7745' WHERE (`type_id_old` = '8');
UPDATE `bu_transaction_types` SET `uid` = 'T2261' WHERE (`type_id_old` = '99');
UPDATE `bu_transaction_types` SET `uid` = 'T3213' WHERE (`type_id_old` = 'A');
UPDATE `bu_transaction_types` SET `uid` = 'T8362' WHERE (`type_id_old` = 'B');
UPDATE `bu_transaction_types` SET `uid` = 'T5876' WHERE (`type_id_old` = 'C');
UPDATE `bu_transaction_types` SET `uid` = 'T6099' WHERE (`type_id_old` = 'D');
UPDATE `bu_transaction_types` SET `uid` = 'T4998' WHERE (`type_id_old` = 'E');
UPDATE `bu_transaction_types` SET `uid` = 'T0776' WHERE (`type_id_old` = 'O');
UPDATE `bu_transaction_types` SET `uid` = 'T3312' WHERE (`type_id_old` = 'T');
UPDATE `bu_transaction_types` SET `uid` = 'T9056' WHERE (`type_id_old` = 'X');
UPDATE `bu_transaction_types` SET `uid` = 'T5422' WHERE (`type_id_old` = '20');
UPDATE `bu_transaction_types` SET `uid` = 'T5020' WHERE (`type_id_old` = '25');
UPDATE `bu_transaction_types` SET `uid` = 'T7648' WHERE (`type_id_old` = '40');
UPDATE `bu_transaction_types` SET `uid` = 'T4886' WHERE (`type_id_old` = '50');

# Update prefills table with new transaction types and sub types
UPDATE
    `bu_prefills` AS t
LEFT JOIN
    `bu_transaction_types` AS x ON t.type_uid_old = x.type_id_old
LEFT JOIN
    `bu_transaction_sub_types` AS y ON t.sub_type_uid_old = y.sub_type_id_old
SET
    t.type_uid = x.uid,
    t.sub_type_uid = y.uid
WHERE
    TRUE;

# Update regular debits table with new transaction types, sub types and regular debit types
UPDATE
    `bu_regular_debits` AS t
LEFT JOIN
    `bu_transaction_types` AS x ON t.type_uid_old = x.type_id_old
LEFT JOIN
    `bu_transaction_sub_types` AS y ON t.sub_type_uid_old = y.sub_type_id_old
LEFT JOIN
    `bu_regular_debit_types` AS z ON t.regular_debit_type_uid_old = z.type_old
SET
    t.type_uid = x.uid,
    t.sub_type_uid = y.uid,
    t.regular_debit_type_uid = z.uid
WHERE
    TRUE;

# Update transactions table with new transaction types and sub types
UPDATE
    `bu_transactions` AS t
LEFT JOIN
    `bu_transaction_types` AS x ON t.type_uid_old = x.type_id_old
LEFT JOIN
    `bu_transaction_sub_types` AS y ON t.sub_type_uid_old = y.sub_type_id_old
SET
    t.type_uid = x.uid,
    t.sub_type_uid = y.uid
WHERE
    TRUE;


# Add created_at to all tables
ALTER TABLE `bu_accounting_periods` ADD COLUMN `created_at` TIMESTAMP NULL;
ALTER TABLE `bu_accounts` ADD COLUMN `created_at` TIMESTAMP NULL;
ALTER TABLE `bu_admin` ADD COLUMN `created_at` TIMESTAMP NULL;
ALTER TABLE `bu_banks` ADD COLUMN `created_at` TIMESTAMP NULL;
ALTER TABLE `bu_entities` ADD COLUMN `created_at` TIMESTAMP NULL;
ALTER TABLE `bu_entities_unused` ADD COLUMN `created_at` TIMESTAMP NULL;
ALTER TABLE `bu_monthly_spend` ADD COLUMN `created_at` TIMESTAMP NULL;
ALTER TABLE `bu_prefills` ADD COLUMN `created_at` TIMESTAMP NULL;
ALTER TABLE `bu_reconcilliation` ADD COLUMN `created_at` TIMESTAMP NULL;
ALTER TABLE `bu_regular_debit_types` ADD COLUMN `created_at` TIMESTAMP NULL;
ALTER TABLE `bu_regular_debits` ADD COLUMN `created_at` TIMESTAMP NULL;
ALTER TABLE `bu_settings` ADD COLUMN `created_at` TIMESTAMP NULL;
ALTER TABLE `bu_tax_years` ADD COLUMN `created_at` TIMESTAMP NULL;
ALTER TABLE `bu_transaction_methods` ADD COLUMN `created_at` TIMESTAMP NULL;
ALTER TABLE `bu_transaction_sub_types` ADD COLUMN `created_at` TIMESTAMP NULL;
ALTER TABLE `bu_transaction_types` ADD COLUMN `created_at` TIMESTAMP NULL;
ALTER TABLE `bu_transactions` ADD COLUMN `created_at` TIMESTAMP NULL;

# Add updated_at to all tables
ALTER TABLE `bu_accounting_periods` ADD COLUMN `updated_at` TIMESTAMP NULL;
ALTER TABLE `bu_accounts` ADD COLUMN `updated_at` TIMESTAMP NULL;
ALTER TABLE `bu_admin` ADD COLUMN `updated_at` TIMESTAMP NULL;
ALTER TABLE `bu_banks` ADD COLUMN `updated_at` TIMESTAMP NULL;
ALTER TABLE `bu_entities` ADD COLUMN `updated_at` TIMESTAMP NULL;
ALTER TABLE `bu_entities_unused` ADD COLUMN `updated_at` TIMESTAMP NULL;
ALTER TABLE `bu_monthly_spend` ADD COLUMN `updated_at` TIMESTAMP NULL;
ALTER TABLE `bu_prefills` ADD COLUMN `updated_at` TIMESTAMP NULL;
ALTER TABLE `bu_reconcilliation` ADD COLUMN `updated_at` TIMESTAMP NULL;
ALTER TABLE `bu_regular_debit_types` ADD COLUMN `updated_at` TIMESTAMP NULL;
ALTER TABLE `bu_regular_debits` ADD COLUMN `updated_at` TIMESTAMP NULL;
ALTER TABLE `bu_settings` ADD COLUMN `updated_at` TIMESTAMP NULL;
ALTER TABLE `bu_tax_years` ADD COLUMN `updated_at` TIMESTAMP NULL;
ALTER TABLE `bu_transaction_methods` ADD COLUMN `updated_at` TIMESTAMP NULL;
ALTER TABLE `bu_transaction_sub_types` ADD COLUMN `updated_at` TIMESTAMP NULL;
ALTER TABLE `bu_transaction_types` ADD COLUMN `updated_at` TIMESTAMP NULL;
ALTER TABLE `bu_transactions` ADD COLUMN `updated_at` TIMESTAMP NULL;

SET @now = NOW();

UPDATE `bu_accounting_periods` SET `created_at` = @now WHERE TRUE;
UPDATE `bu_accounts` SET `created_at` = @now WHERE TRUE;
UPDATE `bu_admin` SET `created_at` = @now WHERE TRUE;
UPDATE `bu_banks` SET `created_at` = @now WHERE TRUE;
UPDATE `bu_entities` SET `created_at` = @now WHERE TRUE;
UPDATE `bu_entities_unused` SET `created_at` = @now WHERE TRUE;
UPDATE `bu_monthly_spend` SET `created_at` = @now WHERE TRUE;
UPDATE `bu_prefills` SET `created_at` = @now WHERE TRUE;
UPDATE `bu_reconcilliation` SET `created_at` = @now WHERE TRUE;
UPDATE `bu_regular_debit_types` SET `created_at` = @now WHERE TRUE;
UPDATE `bu_regular_debits` SET `created_at` = @now WHERE TRUE;
UPDATE `bu_settings` SET `created_at` = @now WHERE TRUE;
UPDATE `bu_tax_years` SET `created_at` = @now WHERE TRUE;
UPDATE `bu_transaction_methods` SET `created_at` = @now WHERE TRUE;
UPDATE `bu_transaction_sub_types` SET `created_at` = @now WHERE TRUE;
UPDATE `bu_transaction_types` SET `created_at` = @now WHERE TRUE;
UPDATE `bu_transactions` SET `created_at` = @now WHERE TRUE;

UPDATE `bu_accounting_periods` SET `updated_at` = @now WHERE TRUE;
UPDATE `bu_accounts` SET `updated_at` = @now WHERE TRUE;
UPDATE `bu_admin` SET `updated_at` = @now WHERE TRUE;
UPDATE `bu_banks` SET `updated_at` = @now WHERE TRUE;
UPDATE `bu_entities` SET `updated_at` = @now WHERE TRUE;
UPDATE `bu_entities_unused` SET `updated_at` = @now WHERE TRUE;
UPDATE `bu_monthly_spend` SET `updated_at` = @now WHERE TRUE;
UPDATE `bu_prefills` SET `updated_at` = @now WHERE TRUE;
UPDATE `bu_reconcilliation` SET `updated_at` = @now WHERE TRUE;
UPDATE `bu_regular_debit_types` SET `updated_at` = @now WHERE TRUE;
UPDATE `bu_regular_debits` SET `updated_at` = @now WHERE TRUE;
UPDATE `bu_settings` SET `updated_at` = @now WHERE TRUE;
UPDATE `bu_tax_years` SET `updated_at` = @now WHERE TRUE;
UPDATE `bu_transaction_methods` SET `updated_at` = @now WHERE TRUE;
UPDATE `bu_transaction_sub_types` SET `updated_at` = @now WHERE TRUE;
UPDATE `bu_transaction_types` SET `updated_at` = @now WHERE TRUE;
UPDATE `bu_transactions` SET `updated_at` = @now WHERE TRUE;

# Rename tables
RENAME TABLE `bu_accounting_periods` TO `accounting_periods`;
RENAME TABLE `bu_accounts` TO `accounts`;
RENAME TABLE `bu_admin` TO `admin`;
RENAME TABLE `bu_banks` TO `banks`;
RENAME TABLE `bu_entities` TO `entities`;
RENAME TABLE `bu_entities_unused` TO `entities_unused`;
RENAME TABLE `bu_monthly_spend` TO `monthly_spend`;
RENAME TABLE `bu_prefills` TO `pre_fills`;
RENAME TABLE `bu_reconcilliation` TO `reconciliation`;
#RENAME TABLE `bu_regular_debit_types` TO `recurring_transaction_types`;
RENAME TABLE `bu_regular_debit_types` TO `recurring_transaction_types`;
#RENAME TABLE `bu_regular_debits` TO `recurring_transactions`;
RENAME TABLE `bu_regular_debits` TO `recurring_transactions`;
RENAME TABLE `bu_settings` TO `settings`;
RENAME TABLE `bu_tax_years` TO `tax_years`;
RENAME TABLE `bu_transaction_methods` TO `methods`;
RENAME TABLE `bu_transaction_sub_types` TO `sub_types`;
RENAME TABLE `bu_transaction_types` TO `types`;
RENAME TABLE `bu_transactions` TO `transactions`;

ALTER TABLE `recurring_transactions` CHANGE COLUMN `regular_debit_type_uid_old` `recurring_transaction_type_uid_old` SMALLINT NULL DEFAULT NULL AFTER `method_uid`;
ALTER TABLE `recurring_transactions` CHANGE COLUMN `regular_debit_type_uid` `recurring_transaction_type_uid` VARCHAR(5) NULL DEFAULT NULL AFTER `recurring_transaction_type_uid_old`;

# Drop tables

/*
USE bankuk_laravel;
-- PREFILLS
SELECT
    x.id,
-- Types
    x.type_uid_old AS 'prefills.TYPE_UID_OLD',
    y.type_id_old AS 'types.TYPE_ID_OLD',
    x.type_uid AS 'prefills.TYPE_UID',
    y.uid AS 'types.UID',
-- Sub Types
    x.sub_type_uid_old AS 'prefills.SUB_TYPE_UID_OLD',
    z.sub_type_id_old AS 'sub_types.SUB_TYPE_ID_OLD',
    x.sub_type_uid AS 'prefills.SUB_TYPE_UID',
    z.uid AS 'sub_types.UID'
FROM
    `prefills` AS x
LEFT JOIN
    `types` AS y ON x.type_uid_old = y.type_id_old
LEFT JOIN
    `sub_types` AS z ON x.sub_type_uid_old = z.sub_type_id_old;
*/

/*
USE bankuk_laravel;
-- TRANSACTIONS
SELECT
    x.id,
-- Types
    x.type_uid_old AS 'transactions.TYPE_UID_OLD',
    y.type_id_old AS 'types.TYPE_ID_OLD',
    x.type_uid AS 'transactions.TYPE_UID',
    y.uid AS 'types.UID',
-- Sub Types
    x.sub_type_uid_old AS 'transactions.SUB_TYPE_UID_OLD',
    z.sub_type_id_old AS 'sub_types.SUB_TYPE_ID_OLD',
    x.sub_type_uid AS 'transactions.SUB_TYPE_UID',
    z.uid AS 'sub_types.UID'
FROM
    `transactions` AS x
LEFT JOIN
     `types` AS y ON x.type_uid_old = y.type_id_old
LEFT JOIN
     `sub_types` AS z ON x.sub_type_uid_old = z.sub_type_id_old;
*/

/*
USE bankuk_laravel;
-- SCHEDULED TRANSACTIONS
SELECT
    x.id,
    t.id,
-- Types
    t.type_uid_old AS 'recurring_transactions.TYPE_UID_OLD',
    x.type_id_old AS 'types.TYPE_ID_OLD',
    t.type_uid AS 'recurring_transactions.TYPE_UID',
    x.uid AS 'types.UID',
-- Sub Types
    t.sub_type_uid_old AS 'recurring_transactions.SUB_TYPE_UID_OLD',
    y.sub_type_id_old AS 'sub_types.SUB_TYPE_ID_OLD',
    t.sub_type_uid AS 'recurring_transactions.SUB_TYPE_UID',
    y.uid AS 'sub_types.UID',
-- Recurring Transaction Types
    t.recurring_transaction_type_uid_old AS 'recurring_transactions.RECURRING_TRANSACTION_TYPE_UID_OLD',
    z.type_old AS 'recurring_transaction_types.TYPE_OLD',
    t.recurring_transaction_type_uid AS 'recurring_transactions.RECURRING_TRANSACTION_TYPE_UID',
    z.uid AS 'recurring_transaction_types.UID'
FROM
    `recurring_transactions` AS t
LEFT JOIN
    `types` AS x ON t.type_uid_old = x.type_id_old
LEFT JOIN
    `sub_types` AS y ON t.sub_type_uid_old = y.sub_type_id_old
LEFT JOIN
    `recurring_transaction_types` AS z ON t.recurring_transaction_type_uid_old = z.type_old
*/
