-- Create sample tables.
CREATE TABLE IF NOT EXISTS user_accounts
( account_id BIGINT UNSIGNED NOT NULL PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  currency_code VARCHAR(3) NOT NULL,
  UNIQUE KEY (user_id, currency_code)
) ENGINE=INNODB;

CREATE TABLE IF NOT EXISTS card_issuer_accounts
( account_id BIGINT UNSIGNED NOT NULL PRIMARY KEY,
  partner_id TINYINT UNSIGNED NOT NULL,
  currency_code VARCHAR(3) NOT NULL,
  UNIQUE KEY (partner_id, currency_code)
) ENGINE=INNODB;

CREATE TABLE IF NOT EXISTS card_transactions
( transaction_id BIGINT UNSIGNED NOT NULL PRIMARY KEY,
  partner_id TINYINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED,
  currency_code VARCHAR(3) NOT NULL,
  amount DECIMAL (8,0) NOT NULL DEFAULT 0,
  amount_partner_fee DECIMAL (6,0) DEFAULT 0,
  amount_surus_fee DECIMAL (6,0) DEFAULT 0,
  transaction_pending ENUM ('no', 'yes') NOT NULL DEFAULT 'yes',
  CHECK (amount_partner_fee >= 0),
  CHECK (amount_surus_fee >= 0)
) ENGINE=INNODB;

CREATE TABLE IF NOT EXISTS card_transactions_journal
( transaction_id BIGINT UNSIGNED NOT NULL,
  type ENUM ('partner_transaction', 'user_transaction', 'partner_fee', 'surus_fee', 'partner_settlement') NOT NULL,
  timestamp DATETIME NOT NULL,
  FOREIGN KEY (transaction_id) REFERENCES card_transactions (transaction_id),
  PRIMARY KEY (transaction_id, type)
) ENGINE=INNODB;

-- Insert sample data.
INSERT INTO user_accounts VALUES (10, 100, 'USD');
INSERT INTO user_accounts VALUES (11, 100, 'EUR');
INSERT INTO user_accounts VALUES (12, 101, 'USD');
INSERT INTO user_accounts VALUES (13, 101, 'GBP');

INSERT INTO card_issuer_accounts VALUES (20, 200, 'USD');
INSERT INTO card_issuer_accounts VALUES (21, 200, 'EUR');
INSERT INTO card_issuer_accounts VALUES (22, 201, 'USD');
INSERT INTO card_issuer_accounts VALUES (23, 201, 'GBP');

INSERT INTO card_transactions VALUES (1000, 200, 100, 'USD', 30000, 500, 1000, DEFAULT);
INSERT INTO card_transactions VALUES (1001, 200, 100, 'EUR', 40000, 500, 1000, DEFAULT);
INSERT INTO card_transactions VALUES (1002, 201, 101, 'USD', 50000, 500, 1000, DEFAULT);
INSERT INTO card_transactions VALUES (1003, 201, 101, 'GBP', 60000, 500, 1000, DEFAULT);
INSERT INTO card_transactions VALUES (1004, 201, NULL, 'GBP', 70000, NULL, NULL, 'no');

INSERT INTO card_transactions_journal VALUES (1000, 1, now());
INSERT INTO card_transactions_journal VALUES (1000, 2, now());
INSERT INTO card_transactions_journal VALUES (1000, 3, now());
INSERT INTO card_transactions_journal VALUES (1000, 4, now());

INSERT INTO card_transactions_journal VALUES (1001, 1, now());
INSERT INTO card_transactions_journal VALUES (1001, 2, now());
INSERT INTO card_transactions_journal VALUES (1001, 3, now());
INSERT INTO card_transactions_journal VALUES (1001, 4, now());

INSERT INTO card_transactions_journal VALUES (1002, 1, now());
INSERT INTO card_transactions_journal VALUES (1002, 2, now());
INSERT INTO card_transactions_journal VALUES (1002, 3, now());
INSERT INTO card_transactions_journal VALUES (1002, 4, now());

INSERT INTO card_transactions_journal VALUES (1003, 1, now());
INSERT INTO card_transactions_journal VALUES (1003, 2, now());
INSERT INTO card_transactions_journal VALUES (1003, 3, now());
INSERT INTO card_transactions_journal VALUES (1003, 4, now());

INSERT INTO card_transactions_journal VALUES (1004, 5, now());

-- Create view.
CREATE VIEW card_transactions_journal_view AS
SELECT transaction_id, type, timestamp,
  -- Calculate the card_issuer_account column:
  IF
  ( type = 1 OR type = 3 OR type = 5,
    ( SELECT account_id
      FROM card_issuer_accounts
      WHERE partner_id =
      ( SELECT partner_id
        FROM card_transactions
        WHERE transaction_id = T1.transaction_id
      )
      AND currency_code =
      ( SELECT currency_code
        FROM card_transactions
        WHERE transaction_id = T1.transaction_id
      )
    ),
    NULL
  ) AS card_issuer_account,
  -- Calculate the user_account column:
  IF
  ( type = 2 OR type = 4,
    ( SELECT account_id
      FROM user_accounts
      WHERE user_id =
      ( SELECT user_id
        FROM card_transactions
        WHERE transaction_id = T1.transaction_id
      )
      AND currency_code =
      ( SELECT currency_code
        FROM card_transactions
        WHERE transaction_id = T1.transaction_id
      )
    ),
    NULL
  ) AS user_account,
  -- Calculate the amount column:
  CASE type
    WHEN 1 THEN
    ( SELECT amount
      FROM card_transactions
      WHERE transaction_id = T1.transaction_id
    )
    WHEN 2 THEN
    ( SELECT (0 - amount)
      FROM card_transactions
      WHERE transaction_id = T1.transaction_id
    )
    WHEN 3 THEN
    ( SELECT amount_partner_fee
      FROM card_transactions
      WHERE transaction_id = T1.transaction_id
    )
    WHEN 4 THEN
    ( SELECT (0 - amount_surus_fee)
      FROM card_transactions
      WHERE transaction_id = T1.transaction_id
    )
    WHEN 5 THEN
    ( SELECT (0 - amount)
      FROM card_transactions
      WHERE transaction_id = T1.transaction_id
    )
  END AS amount
FROM card_transactions_journal AS T1;

-- Check results.
SELECT * FROM card_transactions_journal_view;