initdb/002-seed.sql

-- ============================================================
-- Firefly III Evaluation Seed Data
--
-- Task 1: Find Tim Hortons/Starbucks transactions from
--         March 1 through March 7, categorize as Coffee,
--         tag as Cafe, calculate total spent.
--         Expected answer: C$46.50
--
-- Task 2: Set February budgets and determine which are
--         exceeded and by how much.
--         Expected answer:
--           Exceeded: Gas & Transportation (by C$12.10),
--                     Dining Out (by C$1.50),
--                     Shopping (by C$9.99)
-- ============================================================

SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;

-- ============================================================
-- Clean up existing demo data
-- ============================================================
DELETE FROM `tag_transaction_journal`;
DELETE FROM `category_transaction_journal`;
DELETE FROM `category_transaction`;
DELETE FROM `budget_transaction_journal`;
DELETE FROM `budget_transaction`;
DELETE FROM `auto_budgets`;
DELETE FROM `budget_limits`;
DELETE FROM `available_budgets`;
DELETE FROM `budgets`;
DELETE FROM `transactions`;
DELETE FROM `transaction_journals`;
DELETE FROM `transaction_groups`;
DELETE FROM `account_balances`;
DELETE FROM `account_meta`;
DELETE FROM `accounts` WHERE `account_type_id` IN (1,7,14);
DELETE FROM `categories`;
DELETE FROM `tags`;
DELETE FROM `transaction_currency_user_group`;
DELETE FROM `transaction_currency_user`;

-- ============================================================
-- Enable CAD and USD currencies
-- ============================================================
UPDATE `transaction_currencies` SET `enabled`=0 WHERE `id`=1;  -- disable EUR
UPDATE `transaction_currencies` SET `enabled`=1 WHERE `id`=14; -- enable CAD
UPDATE `transaction_currencies` SET `enabled`=1 WHERE `id`=12; -- enable USD

-- ============================================================
-- Currency defaults: CAD is primary
-- ============================================================
INSERT INTO `transaction_currency_user_group` (`id`,`created_at`,`updated_at`,`user_group_id`,`transaction_currency_id`,`group_default`) VALUES
(1,'2026-02-01 00:00:00','2026-02-01 00:00:00',1,14,1);

INSERT INTO `transaction_currency_user` (`id`,`created_at`,`updated_at`,`user_id`,`transaction_currency_id`,`user_default`) VALUES
(1,'2026-02-01 00:00:00','2026-02-01 00:00:00',1,14,1);

-- ============================================================
-- Update preferences for CAD
-- ============================================================
UPDATE `preferences` SET `data`='[1,2,3,4]' WHERE `name`='frontpageAccounts' AND `user_id`=1;

-- ============================================================
-- Accounts
-- ============================================================

-- Asset accounts (account_type_id=1)
INSERT INTO `accounts` (`id`,`created_at`,`updated_at`,`deleted_at`,`user_id`,`user_group_id`,`account_type_id`,`name`,`virtual_balance`,`iban`,`active`,`encrypted`,`order`,`native_virtual_balance`) VALUES
(1,'2026-02-01 00:00:00','2026-02-01 00:00:00',NULL,1,1,1,'TD Chequing',0.000000000000,NULL,1,0,1,NULL),
(2,'2026-02-01 00:00:00','2026-02-01 00:00:00',NULL,1,1,1,'TD Savings',0.000000000000,NULL,1,0,2,NULL),
(3,'2026-02-01 00:00:00','2026-02-01 00:00:00',NULL,1,1,1,'USD Travel Account',0.000000000000,NULL,1,0,3,NULL),
(4,'2026-02-01 00:00:00','2026-02-01 00:00:00',NULL,1,1,1,'Cash Wallet',0.000000000000,NULL,1,0,4,NULL);

-- Expense accounts (account_type_id=7)
INSERT INTO `accounts` (`id`,`created_at`,`updated_at`,`deleted_at`,`user_id`,`user_group_id`,`account_type_id`,`name`,`virtual_balance`,`iban`,`active`,`encrypted`,`order`,`native_virtual_balance`) VALUES
(5, '2026-02-01 00:00:00','2026-02-01 00:00:00',NULL,1,1,7,'Tim Hortons',NULL,NULL,1,0,0,NULL),
(6, '2026-02-01 00:00:00','2026-02-01 00:00:00',NULL,1,1,7,'Starbucks',NULL,NULL,1,0,0,NULL),
(7, '2026-02-01 00:00:00','2026-02-01 00:00:00',NULL,1,1,7,'Loblaws',NULL,NULL,1,0,0,NULL),
(8, '2026-02-01 00:00:00','2026-02-01 00:00:00',NULL,1,1,7,'Amazon.ca',NULL,NULL,1,0,0,NULL),
(9, '2026-02-01 00:00:00','2026-02-01 00:00:00',NULL,1,1,7,'Shell Gas Station',NULL,NULL,1,0,0,NULL),
(10,'2026-02-01 00:00:00','2026-02-01 00:00:00',NULL,1,1,7,'Netflix',NULL,NULL,1,0,0,NULL),
(11,'2026-02-01 00:00:00','2026-02-01 00:00:00',NULL,1,1,7,'Uber Eats',NULL,NULL,1,0,0,NULL),
(12,'2026-02-01 00:00:00','2026-02-01 00:00:00',NULL,1,1,7,'Canadian Tire',NULL,NULL,1,0,0,NULL),
(13,'2026-02-01 00:00:00','2026-02-01 00:00:00',NULL,1,1,7,'Shoppers Drug Mart',NULL,NULL,1,0,0,NULL),
(14,'2026-02-01 00:00:00','2026-02-01 00:00:00',NULL,1,1,7,'McDonald''s',NULL,NULL,1,0,0,NULL),
(15,'2026-02-01 00:00:00','2026-02-01 00:00:00',NULL,1,1,7,'Costco',NULL,NULL,1,0,0,NULL),
(16,'2026-02-01 00:00:00','2026-02-01 00:00:00',NULL,1,1,7,'Rogers Wireless',NULL,NULL,1,0,0,NULL),
(17,'2026-02-01 00:00:00','2026-02-01 00:00:00',NULL,1,1,7,'Hydro Ottawa',NULL,NULL,1,0,0,NULL);

-- Revenue accounts (account_type_id=14)
INSERT INTO `accounts` (`id`,`created_at`,`updated_at`,`deleted_at`,`user_id`,`user_group_id`,`account_type_id`,`name`,`virtual_balance`,`iban`,`active`,`encrypted`,`order`,`native_virtual_balance`) VALUES
(18,'2026-02-01 00:00:00','2026-02-01 00:00:00',NULL,1,1,14,'Employer - Payroll',NULL,NULL,1,0,0,NULL),
(19,'2026-02-01 00:00:00','2026-02-01 00:00:00',NULL,1,1,14,'CRA Tax Refund',NULL,NULL,1,0,0,NULL),
(20,'2026-02-01 00:00:00','2026-02-01 00:00:00',NULL,1,1,14,'Freelance USD Client',NULL,NULL,1,0,0,NULL);

-- ============================================================
-- Account metadata
-- ============================================================
INSERT INTO `account_meta` (`id`,`created_at`,`updated_at`,`account_id`,`name`,`data`) VALUES
-- TD Chequing
(1,'2026-02-01 00:00:00','2026-02-01 00:00:00',1,'account_role','"defaultAsset"'),
(2,'2026-02-01 00:00:00','2026-02-01 00:00:00',1,'currency_id','"14"'),
-- TD Savings
(3,'2026-02-01 00:00:00','2026-02-01 00:00:00',2,'account_role','"savingAsset"'),
(4,'2026-02-01 00:00:00','2026-02-01 00:00:00',2,'currency_id','"14"'),
-- USD Travel
(5,'2026-02-01 00:00:00','2026-02-01 00:00:00',3,'account_role','"defaultAsset"'),
(6,'2026-02-01 00:00:00','2026-02-01 00:00:00',3,'currency_id','"12"'),
-- Cash Wallet
(7,'2026-02-01 00:00:00','2026-02-01 00:00:00',4,'account_role','"cashWalletAsset"'),
(8,'2026-02-01 00:00:00','2026-02-01 00:00:00',4,'currency_id','"14"'),
-- Expense account metadata (BIC + account_number, empty)
(9, '2026-02-01 00:00:00','2026-02-01 00:00:00',5, 'BIC','""'),
(10,'2026-02-01 00:00:00','2026-02-01 00:00:00',5, 'account_number','""'),
(11,'2026-02-01 00:00:00','2026-02-01 00:00:00',6, 'BIC','""'),
(12,'2026-02-01 00:00:00','2026-02-01 00:00:00',6, 'account_number','""'),
(13,'2026-02-01 00:00:00','2026-02-01 00:00:00',7, 'BIC','""'),
(14,'2026-02-01 00:00:00','2026-02-01 00:00:00',7, 'account_number','""'),
(15,'2026-02-01 00:00:00','2026-02-01 00:00:00',8, 'BIC','""'),
(16,'2026-02-01 00:00:00','2026-02-01 00:00:00',8, 'account_number','""'),
(17,'2026-02-01 00:00:00','2026-02-01 00:00:00',9, 'BIC','""'),
(18,'2026-02-01 00:00:00','2026-02-01 00:00:00',9, 'account_number','""'),
(19,'2026-02-01 00:00:00','2026-02-01 00:00:00',10,'BIC','""'),
(20,'2026-02-01 00:00:00','2026-02-01 00:00:00',10,'account_number','""'),
(21,'2026-02-01 00:00:00','2026-02-01 00:00:00',11,'BIC','""'),
(22,'2026-02-01 00:00:00','2026-02-01 00:00:00',11,'account_number','""'),
(23,'2026-02-01 00:00:00','2026-02-01 00:00:00',12,'BIC','""'),
(24,'2026-02-01 00:00:00','2026-02-01 00:00:00',12,'account_number','""'),
(25,'2026-02-01 00:00:00','2026-02-01 00:00:00',13,'BIC','""'),
(26,'2026-02-01 00:00:00','2026-02-01 00:00:00',13,'account_number','""'),
(27,'2026-02-01 00:00:00','2026-02-01 00:00:00',14,'BIC','""'),
(28,'2026-02-01 00:00:00','2026-02-01 00:00:00',14,'account_number','""'),
(29,'2026-02-01 00:00:00','2026-02-01 00:00:00',15,'BIC','""'),
(30,'2026-02-01 00:00:00','2026-02-01 00:00:00',15,'account_number','""'),
(31,'2026-02-01 00:00:00','2026-02-01 00:00:00',16,'BIC','""'),
(32,'2026-02-01 00:00:00','2026-02-01 00:00:00',16,'account_number','""'),
(33,'2026-02-01 00:00:00','2026-02-01 00:00:00',17,'BIC','""'),
(34,'2026-02-01 00:00:00','2026-02-01 00:00:00',17,'account_number','""'),
-- Revenue account metadata
(35,'2026-02-01 00:00:00','2026-02-01 00:00:00',18,'BIC','""'),
(36,'2026-02-01 00:00:00','2026-02-01 00:00:00',18,'account_number','""'),
(37,'2026-02-01 00:00:00','2026-02-01 00:00:00',19,'BIC','""'),
(38,'2026-02-01 00:00:00','2026-02-01 00:00:00',19,'account_number','""'),
(39,'2026-02-01 00:00:00','2026-02-01 00:00:00',20,'BIC','""'),
(40,'2026-02-01 00:00:00','2026-02-01 00:00:00',20,'account_number','""');

-- ============================================================
-- Categories (deliberately NO "Coffee" category)
-- ============================================================
INSERT INTO `categories` (`id`,`created_at`,`updated_at`,`deleted_at`,`user_id`,`user_group_id`,`name`,`encrypted`) VALUES
(1,'2026-02-01 00:00:00','2026-02-01 00:00:00',NULL,1,1,'Groceries',0),
(2,'2026-02-01 00:00:00','2026-02-01 00:00:00',NULL,1,1,'Gas & Transportation',0),
(3,'2026-02-01 00:00:00','2026-02-01 00:00:00',NULL,1,1,'Entertainment',0),
(4,'2026-02-01 00:00:00','2026-02-01 00:00:00',NULL,1,1,'Utilities & Bills',0),
(5,'2026-02-01 00:00:00','2026-02-01 00:00:00',NULL,1,1,'Dining Out',0),
(6,'2026-02-01 00:00:00','2026-02-01 00:00:00',NULL,1,1,'Shopping',0),
(7,'2026-02-01 00:00:00','2026-02-01 00:00:00',NULL,1,1,'Health & Personal Care',0);

-- ============================================================
-- Tags (deliberately NO "Cafe" tag)
-- ============================================================
INSERT INTO `tags` (`id`,`created_at`,`updated_at`,`deleted_at`,`user_id`,`user_group_id`,`tag`,`tag_mode`,`date`,`date_tz`,`description`,`latitude`,`longitude`,`zoomLevel`) VALUES
(1,'2026-02-01 00:00:00','2026-02-01 00:00:00',NULL,1,1,'essential','nothing',NULL,NULL,NULL,NULL,NULL,NULL),
(2,'2026-02-01 00:00:00','2026-02-01 00:00:00',NULL,1,1,'recurring','nothing',NULL,NULL,NULL,NULL,NULL,NULL),
(3,'2026-02-01 00:00:00','2026-02-01 00:00:00',NULL,1,1,'impulse-buy','nothing',NULL,NULL,NULL,NULL,NULL,NULL);

-- ============================================================
-- Transaction Groups (one per transaction, 31 total)
-- ============================================================
INSERT INTO `transaction_groups` (`id`,`created_at`,`updated_at`,`deleted_at`,`user_id`,`user_group_id`,`title`) VALUES
(1, '2026-02-15 00:00:00','2026-02-15 00:00:00',NULL,1,1,NULL),
(2, '2026-02-18 00:00:00','2026-02-18 00:00:00',NULL,1,1,NULL),
(3, '2026-02-20 00:00:00','2026-02-20 00:00:00',NULL,1,1,NULL),
(4, '2026-02-20 00:00:00','2026-02-20 00:00:00',NULL,1,1,NULL),
(5, '2026-02-22 00:00:00','2026-02-22 00:00:00',NULL,1,1,NULL),
(6, '2026-02-23 00:00:00','2026-02-23 00:00:00',NULL,1,1,NULL),
(7, '2026-02-25 00:00:00','2026-02-25 00:00:00',NULL,1,1,NULL),
(8, '2026-02-25 00:00:00','2026-02-25 00:00:00',NULL,1,1,NULL),
(9, '2026-02-26 00:00:00','2026-02-26 00:00:00',NULL,1,1,NULL),
(10,'2026-02-27 00:00:00','2026-02-27 00:00:00',NULL,1,1,NULL),
(11,'2026-02-28 00:00:00','2026-02-28 00:00:00',NULL,1,1,NULL),
(12,'2026-03-01 00:00:00','2026-03-01 00:00:00',NULL,1,1,NULL),
(13,'2026-03-01 00:00:00','2026-03-01 00:00:00',NULL,1,1,NULL),
(14,'2026-03-02 00:00:00','2026-03-02 00:00:00',NULL,1,1,NULL),
(15,'2026-03-02 00:00:00','2026-03-02 00:00:00',NULL,1,1,NULL),
(16,'2026-03-03 00:00:00','2026-03-03 00:00:00',NULL,1,1,NULL),
(17,'2026-03-03 00:00:00','2026-03-03 00:00:00',NULL,1,1,NULL),
(18,'2026-03-03 00:00:00','2026-03-03 00:00:00',NULL,1,1,NULL),
(19,'2026-03-04 00:00:00','2026-03-04 00:00:00',NULL,1,1,NULL),
(20,'2026-03-04 00:00:00','2026-03-04 00:00:00',NULL,1,1,NULL),
(21,'2026-03-05 00:00:00','2026-03-05 00:00:00',NULL,1,1,NULL),
(22,'2026-03-05 00:00:00','2026-03-05 00:00:00',NULL,1,1,NULL),
(23,'2026-03-06 00:00:00','2026-03-06 00:00:00',NULL,1,1,NULL),
(24,'2026-03-06 00:00:00','2026-03-06 00:00:00',NULL,1,1,NULL),
(25,'2026-03-07 00:00:00','2026-03-07 00:00:00',NULL,1,1,NULL),
(26,'2026-03-08 00:00:00','2026-03-08 00:00:00',NULL,1,1,NULL),
(27,'2026-03-09 00:00:00','2026-03-09 00:00:00',NULL,1,1,NULL),
(28,'2026-03-10 00:00:00','2026-03-10 00:00:00',NULL,1,1,NULL),
(29,'2026-03-10 00:00:00','2026-03-10 00:00:00',NULL,1,1,NULL),
(30,'2026-03-11 00:00:00','2026-03-11 00:00:00',NULL,1,1,NULL),
(31,'2026-03-12 00:00:00','2026-03-12 00:00:00',NULL,1,1,NULL);

-- ============================================================
-- Transaction Journals
-- Types: 1=Deposit, 6=Transfer, 7=Withdrawal
-- Currency: 14=CAD, 12=USD
-- tag_count: number of tags on that journal
-- ============================================================
INSERT INTO `transaction_journals` (`id`,`created_at`,`updated_at`,`deleted_at`,`user_id`,`user_group_id`,`transaction_type_id`,`transaction_group_id`,`bill_id`,`transaction_currency_id`,`description`,`date`,`date_tz`,`interest_date`,`book_date`,`process_date`,`order`,`tag_count`,`encrypted`,`completed`) VALUES
-- February 2026
(1, '2026-02-15 00:00:00','2026-02-15 00:00:00',NULL,1,1,1, 1, NULL,14,'Salary - first half of February',       '2026-02-15 00:00:00','America/Toronto',NULL,NULL,NULL,0,0,0,1),
(2, '2026-02-18 00:00:00','2026-02-18 00:00:00',NULL,1,1,7, 2, NULL,14,'Weekly groceries',                       '2026-02-18 00:00:00','America/Toronto',NULL,NULL,NULL,0,1,0,1),
(3, '2026-02-20 00:00:00','2026-02-20 00:00:00',NULL,1,1,7, 3, NULL,14,'Gas fill-up',                            '2026-02-20 00:00:00','America/Toronto',NULL,NULL,NULL,0,0,0,1),
(4, '2026-02-20 00:00:00','2026-02-20 00:00:00',NULL,1,1,1, 4, NULL,12,'Freelance web design payment',           '2026-02-20 00:00:00','America/Toronto',NULL,NULL,NULL,0,0,0,1),
(5, '2026-02-22 00:00:00','2026-02-22 00:00:00',NULL,1,1,7, 5, NULL,14,'Netflix monthly subscription',           '2026-02-22 00:00:00','America/Toronto',NULL,NULL,NULL,0,1,0,1),
(6, '2026-02-23 00:00:00','2026-02-23 00:00:00',NULL,1,1,7, 6, NULL,14,'Rogers phone bill',                      '2026-02-23 00:00:00','America/Toronto',NULL,NULL,NULL,0,1,0,1),
(7, '2026-02-25 00:00:00','2026-02-25 00:00:00',NULL,1,1,7, 7, NULL,14,'Flat white and scone',                   '2026-02-25 00:00:00','America/Toronto',NULL,NULL,NULL,0,0,0,1),
(8, '2026-02-25 00:00:00','2026-02-25 00:00:00',NULL,1,1,7, 8, NULL,14,'Medium double double',                   '2026-02-25 00:00:00','America/Toronto',NULL,NULL,NULL,0,0,0,1),
(9, '2026-02-26 00:00:00','2026-02-26 00:00:00',NULL,1,1,7, 9, NULL,14,'Bluetooth headphones',                   '2026-02-26 00:00:00','America/Toronto',NULL,NULL,NULL,0,1,0,1),
(10,'2026-02-27 00:00:00','2026-02-27 00:00:00',NULL,1,1,7,10, NULL,14,'Costco bulk shopping',                   '2026-02-27 00:00:00','America/Toronto',NULL,NULL,NULL,0,1,0,1),
(11,'2026-02-28 00:00:00','2026-02-28 00:00:00',NULL,1,1,1,11, NULL,14,'Salary - second half of February',       '2026-02-28 00:00:00','America/Toronto',NULL,NULL,NULL,0,0,0,1),

-- March 1-7: FIRST WEEK OF MARCH (Tim Hortons/Starbucks have NO category, NO tag)
(12,'2026-03-01 09:15:00','2026-03-01 09:15:00',NULL,1,1,7,12, NULL,14,'Double double and muffin',               '2026-03-01 09:15:00','America/Toronto',NULL,NULL,NULL,0,0,0,1),
(13,'2026-03-01 00:00:00','2026-03-01 00:00:00',NULL,1,1,7,13, NULL,14,'Netflix monthly subscription',           '2026-03-01 00:00:00','America/Toronto',NULL,NULL,NULL,0,1,0,1),
(14,'2026-03-02 08:30:00','2026-03-02 08:30:00',NULL,1,1,7,14, NULL,14,'Grande latte',                           '2026-03-02 08:30:00','America/Toronto',NULL,NULL,NULL,0,0,0,1),
(15,'2026-03-02 00:00:00','2026-03-02 00:00:00',NULL,1,1,7,15, NULL,14,'Weekly groceries',                       '2026-03-02 00:00:00','America/Toronto',NULL,NULL,NULL,0,1,0,1),
(16,'2026-03-03 07:45:00','2026-03-03 07:45:00',NULL,1,1,7,16, NULL,14,'Large coffee and bagel BELT',            '2026-03-03 07:45:00','America/Toronto',NULL,NULL,NULL,0,0,0,1),
(17,'2026-03-03 00:00:00','2026-03-03 00:00:00',NULL,1,1,7,17, NULL,14,'Gas fill-up',                            '2026-03-03 00:00:00','America/Toronto',NULL,NULL,NULL,0,0,0,1),
(18,'2026-03-03 00:00:00','2026-03-03 00:00:00',NULL,1,1,6,18, NULL,14,'Transfer to savings',                    '2026-03-03 00:00:00','America/Toronto',NULL,NULL,NULL,0,0,0,1),
(19,'2026-03-04 08:00:00','2026-03-04 08:00:00',NULL,1,1,7,19, NULL,14,'Venti caramel macchiato',                '2026-03-04 08:00:00','America/Toronto',NULL,NULL,NULL,0,0,0,1),
(20,'2026-03-04 00:00:00','2026-03-04 00:00:00',NULL,1,1,7,20, NULL,14,'Dinner delivery',                        '2026-03-04 00:00:00','America/Toronto',NULL,NULL,NULL,0,0,0,1),
(21,'2026-03-05 09:00:00','2026-03-05 09:00:00',NULL,1,1,7,21, NULL,14,'Large dark roast',                       '2026-03-05 09:00:00','America/Toronto',NULL,NULL,NULL,0,0,0,1),
(22,'2026-03-05 00:00:00','2026-03-05 00:00:00',NULL,1,1,7,22, NULL,14,'Lunch combo',                            '2026-03-05 12:00:00','America/Toronto',NULL,NULL,NULL,0,0,0,1),
(23,'2026-03-06 08:15:00','2026-03-06 08:15:00',NULL,1,1,7,23, NULL,14,'Pike Place and cake pop',                '2026-03-06 08:15:00','America/Toronto',NULL,NULL,NULL,0,0,0,1),
(24,'2026-03-06 00:00:00','2026-03-06 00:00:00',NULL,1,1,7,24, NULL,14,'Vitamins and toiletries',                '2026-03-06 00:00:00','America/Toronto',NULL,NULL,NULL,0,0,0,1),
(25,'2026-03-07 07:30:00','2026-03-07 07:30:00',NULL,1,1,7,25, NULL,14,'Iced capp and donut',                    '2026-03-07 07:30:00','America/Toronto',NULL,NULL,NULL,0,0,0,1),

-- After first week of March
(26,'2026-03-08 00:00:00','2026-03-08 00:00:00',NULL,1,1,7,26, NULL,14,'Windshield wipers',                      '2026-03-08 00:00:00','America/Toronto',NULL,NULL,NULL,0,0,0,1),
(27,'2026-03-09 00:00:00','2026-03-09 00:00:00',NULL,1,1,7,27, NULL,14,'Weekly groceries',                       '2026-03-09 00:00:00','America/Toronto',NULL,NULL,NULL,0,1,0,1),
(28,'2026-03-10 09:00:00','2026-03-10 09:00:00',NULL,1,1,7,28, NULL,14,'Roll Up the Rim coffee',                 '2026-03-10 09:00:00','America/Toronto',NULL,NULL,NULL,0,0,0,1),
(29,'2026-03-10 08:45:00','2026-03-10 08:45:00',NULL,1,1,7,29, NULL,14,'Matcha latte',                           '2026-03-10 08:45:00','America/Toronto',NULL,NULL,NULL,0,0,0,1),
(30,'2026-03-11 00:00:00','2026-03-11 00:00:00',NULL,1,1,7,30, NULL,14,'Electricity bill',                       '2026-03-11 00:00:00','America/Toronto',NULL,NULL,NULL,0,1,0,1),
(31,'2026-03-12 00:00:00','2026-03-12 00:00:00',NULL,1,1,7,31, NULL,12,'Amazon US order - Programming books',    '2026-03-12 00:00:00','America/Toronto',NULL,NULL,NULL,0,0,0,1);

-- ============================================================
-- Transactions (2 rows per journal: source and destination)
-- balance_dirty=1 so Firefly recalculates balances
-- ============================================================
INSERT INTO `transactions` (`id`,`created_at`,`updated_at`,`deleted_at`,`reconciled`,`account_id`,`transaction_journal_id`,`description`,`transaction_currency_id`,`amount`,`balance_before`,`balance_after`,`balance_dirty`,`foreign_amount`,`foreign_currency_id`,`identifier`,`native_amount`,`native_foreign_amount`) VALUES
-- TJ1: Salary deposit Feb 15 (Employer->Chequing, CAD 3500)
(1, '2026-02-15 00:00:00','2026-02-15 00:00:00',NULL,0,18,1,NULL,14,-3500.000000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL),
(2, '2026-02-15 00:00:00','2026-02-15 00:00:00',NULL,0,1, 1,NULL,14, 3500.000000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL),

-- TJ2: Groceries Feb 18 (Chequing->Loblaws, CAD 87.43)
(3, '2026-02-18 00:00:00','2026-02-18 00:00:00',NULL,0,1, 2,NULL,14,-87.430000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL),
(4, '2026-02-18 00:00:00','2026-02-18 00:00:00',NULL,0,7, 2,NULL,14, 87.430000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL),

-- TJ3: Gas Feb 20 (Chequing->Shell, CAD 62.10)
(5, '2026-02-20 00:00:00','2026-02-20 00:00:00',NULL,0,1, 3,NULL,14,-62.100000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL),
(6, '2026-02-20 00:00:00','2026-02-20 00:00:00',NULL,0,9, 3,NULL,14, 62.100000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL),

-- TJ4: Freelance deposit Feb 20 (Freelance->USD Acct, USD 750)
(7, '2026-02-20 00:00:00','2026-02-20 00:00:00',NULL,0,20,4,NULL,12,-750.000000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL),
(8, '2026-02-20 00:00:00','2026-02-20 00:00:00',NULL,0,3, 4,NULL,12, 750.000000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL),

-- TJ5: Netflix Feb 22 (Chequing->Netflix, CAD 16.99)
(9, '2026-02-22 00:00:00','2026-02-22 00:00:00',NULL,0,1, 5,NULL,14,-16.990000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL),
(10,'2026-02-22 00:00:00','2026-02-22 00:00:00',NULL,0,10,5,NULL,14, 16.990000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL),

-- TJ6: Rogers Feb 23 (Chequing->Rogers, CAD 75.00)
(11,'2026-02-23 00:00:00','2026-02-23 00:00:00',NULL,0,1, 6,NULL,14,-75.000000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL),
(12,'2026-02-23 00:00:00','2026-02-23 00:00:00',NULL,0,16,6,NULL,14, 75.000000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL),

-- TJ7: Starbucks Feb 25 (Chequing->Starbucks, CAD 9.35) -- HAS category (Dining Out)
(13,'2026-02-25 00:00:00','2026-02-25 00:00:00',NULL,0,1, 7,NULL,14,-9.350000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL),
(14,'2026-02-25 00:00:00','2026-02-25 00:00:00',NULL,0,6, 7,NULL,14, 9.350000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL),

-- TJ8: Tim Hortons Feb 25 (Chequing->Tim Hortons, CAD 2.15) -- HAS category (Dining Out)
(15,'2026-02-25 00:00:00','2026-02-25 00:00:00',NULL,0,1, 8,NULL,14,-2.150000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL),
(16,'2026-02-25 00:00:00','2026-02-25 00:00:00',NULL,0,5, 8,NULL,14, 2.150000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL),

-- TJ9: Amazon headphones Feb 26 (Chequing->Amazon, CAD 49.99)
(17,'2026-02-26 00:00:00','2026-02-26 00:00:00',NULL,0,1, 9,NULL,14,-49.990000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL),
(18,'2026-02-26 00:00:00','2026-02-26 00:00:00',NULL,0,8, 9,NULL,14, 49.990000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL),

-- TJ10: Costco Feb 27 (Chequing->Costco, CAD 156.82)
(19,'2026-02-27 00:00:00','2026-02-27 00:00:00',NULL,0,1, 10,NULL,14,-156.820000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL),
(20,'2026-02-27 00:00:00','2026-02-27 00:00:00',NULL,0,15,10,NULL,14, 156.820000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL),

-- TJ11: Salary deposit Feb 28 (Employer->Chequing, CAD 3500)
(21,'2026-02-28 00:00:00','2026-02-28 00:00:00',NULL,0,18,11,NULL,14,-3500.000000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL),
(22,'2026-02-28 00:00:00','2026-02-28 00:00:00',NULL,0,1, 11,NULL,14, 3500.000000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL),

-- ====== FIRST WEEK OF MARCH: TARGET TRANSACTIONS (no category, no tag) ======

-- TJ12: Tim Hortons Mar 1 - "Double double and muffin" CAD 5.47
(23,'2026-03-01 09:15:00','2026-03-01 09:15:00',NULL,0,1, 12,NULL,14,-5.470000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL),
(24,'2026-03-01 09:15:00','2026-03-01 09:15:00',NULL,0,5, 12,NULL,14, 5.470000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL),

-- TJ13: Netflix Mar 1 (Chequing->Netflix, CAD 16.99)
(25,'2026-03-01 00:00:00','2026-03-01 00:00:00',NULL,0,1, 13,NULL,14,-16.990000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL),
(26,'2026-03-01 00:00:00','2026-03-01 00:00:00',NULL,0,10,13,NULL,14, 16.990000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL),

-- TJ14: Starbucks Mar 2 - "Grande latte" CAD 6.45
(27,'2026-03-02 08:30:00','2026-03-02 08:30:00',NULL,0,1, 14,NULL,14,-6.450000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL),
(28,'2026-03-02 08:30:00','2026-03-02 08:30:00',NULL,0,6, 14,NULL,14, 6.450000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL),

-- TJ15: Loblaws Mar 2 (Chequing->Loblaws, CAD 93.21)
(29,'2026-03-02 00:00:00','2026-03-02 00:00:00',NULL,0,1, 15,NULL,14,-93.210000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL),
(30,'2026-03-02 00:00:00','2026-03-02 00:00:00',NULL,0,7, 15,NULL,14, 93.210000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL),

-- TJ16: Tim Hortons Mar 3 - "Large coffee and bagel BELT" CAD 8.23
(31,'2026-03-03 07:45:00','2026-03-03 07:45:00',NULL,0,1, 16,NULL,14,-8.230000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL),
(32,'2026-03-03 07:45:00','2026-03-03 07:45:00',NULL,0,5, 16,NULL,14, 8.230000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL),

-- TJ17: Shell gas Mar 3 (Chequing->Shell, CAD 58.40)
(33,'2026-03-03 00:00:00','2026-03-03 00:00:00',NULL,0,1, 17,NULL,14,-58.400000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL),
(34,'2026-03-03 00:00:00','2026-03-03 00:00:00',NULL,0,9, 17,NULL,14, 58.400000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL),

-- TJ18: Transfer to savings Mar 3 (Chequing->Savings, CAD 500)
(35,'2026-03-03 00:00:00','2026-03-03 00:00:00',NULL,0,1, 18,NULL,14,-500.000000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL),
(36,'2026-03-03 00:00:00','2026-03-03 00:00:00',NULL,0,2, 18,NULL,14, 500.000000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL),

-- TJ19: Starbucks Mar 4 - "Venti caramel macchiato" CAD 7.20
(37,'2026-03-04 08:00:00','2026-03-04 08:00:00',NULL,0,1, 19,NULL,14,-7.200000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL),
(38,'2026-03-04 08:00:00','2026-03-04 08:00:00',NULL,0,6, 19,NULL,14, 7.200000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL),

-- TJ20: Uber Eats Mar 4 (Chequing->Uber Eats, CAD 32.15)
(39,'2026-03-04 00:00:00','2026-03-04 00:00:00',NULL,0,1, 20,NULL,14,-32.150000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL),
(40,'2026-03-04 00:00:00','2026-03-04 00:00:00',NULL,0,11,20,NULL,14, 32.150000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL),

-- TJ21: Tim Hortons Mar 5 - "Large dark roast" CAD 2.35
(41,'2026-03-05 09:00:00','2026-03-05 09:00:00',NULL,0,1, 21,NULL,14,-2.350000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL),
(42,'2026-03-05 09:00:00','2026-03-05 09:00:00',NULL,0,5, 21,NULL,14, 2.350000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL),

-- TJ22: McDonald's Mar 5 (Chequing->McDonald's, CAD 12.49)
(43,'2026-03-05 12:00:00','2026-03-05 12:00:00',NULL,0,1, 22,NULL,14,-12.490000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL),
(44,'2026-03-05 12:00:00','2026-03-05 12:00:00',NULL,0,14,22,NULL,14, 12.490000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL),

-- TJ23: Starbucks Mar 6 - "Pike Place and cake pop" CAD 8.95
(45,'2026-03-06 08:15:00','2026-03-06 08:15:00',NULL,0,1, 23,NULL,14,-8.950000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL),
(46,'2026-03-06 08:15:00','2026-03-06 08:15:00',NULL,0,6, 23,NULL,14, 8.950000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL),

-- TJ24: Shoppers Drug Mart Mar 6 (Chequing->Shoppers, CAD 34.67)
(47,'2026-03-06 00:00:00','2026-03-06 00:00:00',NULL,0,1, 24,NULL,14,-34.670000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL),
(48,'2026-03-06 00:00:00','2026-03-06 00:00:00',NULL,0,13,24,NULL,14, 34.670000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL),

-- TJ25: Tim Hortons Mar 7 - "Iced capp and donut" CAD 7.85
(49,'2026-03-07 07:30:00','2026-03-07 07:30:00',NULL,0,1, 25,NULL,14,-7.850000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL),
(50,'2026-03-07 07:30:00','2026-03-07 07:30:00',NULL,0,5, 25,NULL,14, 7.850000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL),

-- ====== AFTER FIRST WEEK (distractors) ======

-- TJ26: Canadian Tire Mar 8 (Chequing->Can Tire, CAD 28.99)
(51,'2026-03-08 00:00:00','2026-03-08 00:00:00',NULL,0,1, 26,NULL,14,-28.990000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL),
(52,'2026-03-08 00:00:00','2026-03-08 00:00:00',NULL,0,12,26,NULL,14, 28.990000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL),

-- TJ27: Loblaws Mar 9 (Chequing->Loblaws, CAD 71.33)
(53,'2026-03-09 00:00:00','2026-03-09 00:00:00',NULL,0,1, 27,NULL,14,-71.330000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL),
(54,'2026-03-09 00:00:00','2026-03-09 00:00:00',NULL,0,7, 27,NULL,14, 71.330000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL),

-- TJ28: Tim Hortons Mar 10 - DISTRACTOR (outside first week, no category)
(55,'2026-03-10 09:00:00','2026-03-10 09:00:00',NULL,0,1, 28,NULL,14,-3.450000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL),
(56,'2026-03-10 09:00:00','2026-03-10 09:00:00',NULL,0,5, 28,NULL,14, 3.450000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL),

-- TJ29: Starbucks Mar 10 - DISTRACTOR (outside first week, no category)
(57,'2026-03-10 08:45:00','2026-03-10 08:45:00',NULL,0,1, 29,NULL,14,-6.750000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL),
(58,'2026-03-10 08:45:00','2026-03-10 08:45:00',NULL,0,6, 29,NULL,14, 6.750000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL),

-- TJ30: Hydro Ottawa Mar 11 (Chequing->Hydro, CAD 142.50)
(59,'2026-03-11 00:00:00','2026-03-11 00:00:00',NULL,0,1, 30,NULL,14,-142.500000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL),
(60,'2026-03-11 00:00:00','2026-03-11 00:00:00',NULL,0,17,30,NULL,14, 142.500000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL),

-- TJ31: Amazon US order Mar 12 (USD Acct->Amazon, USD 45.99)
(61,'2026-03-12 00:00:00','2026-03-12 00:00:00',NULL,0,3, 31,NULL,12,-45.990000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL),
(62,'2026-03-12 00:00:00','2026-03-12 00:00:00',NULL,0,8, 31,NULL,12, 45.990000000000,NULL,NULL,1,NULL,NULL,0,NULL,NULL);

-- ============================================================
-- Category <-> Transaction Journal links
-- Only for non-target transactions
-- ============================================================
INSERT INTO `category_transaction_journal` (`id`,`category_id`,`transaction_journal_id`) VALUES
-- Feb transactions with categories
(1, 1, 2),   -- TJ2:  Groceries -> Weekly groceries
(2, 2, 3),   -- TJ3:  Gas & Transportation -> Gas fill-up
(3, 3, 5),   -- TJ5:  Entertainment -> Netflix
(4, 4, 6),   -- TJ6:  Utilities & Bills -> Rogers
(5, 5, 7),   -- TJ7:  Dining Out -> Starbucks Feb 25
(6, 5, 8),   -- TJ8:  Dining Out -> Tim Hortons Feb 25
(7, 6, 9),   -- TJ9:  Shopping -> Bluetooth headphones
(8, 1, 10),  -- TJ10: Groceries -> Costco
-- March transactions with categories (NOT the Tim Hortons/Starbucks first-week ones)
(9,  3, 13), -- TJ13: Entertainment -> Netflix Mar 1
(10, 1, 15), -- TJ15: Groceries -> Loblaws Mar 2
(11, 2, 17), -- TJ17: Gas & Transportation -> Shell Mar 3
(12, 5, 20), -- TJ20: Dining Out -> Uber Eats Mar 4
(13, 5, 22), -- TJ22: Dining Out -> McDonald's Mar 5
(14, 7, 24), -- TJ24: Health & Personal Care -> Shoppers Mar 6
(15, 6, 26), -- TJ26: Shopping -> Canadian Tire Mar 8
(16, 1, 27), -- TJ27: Groceries -> Loblaws Mar 9
(17, 4, 30), -- TJ30: Utilities & Bills -> Hydro Ottawa Mar 11
(18, 6, 31); -- TJ31: Shopping -> Amazon US Mar 12

-- ============================================================
-- Tag <-> Transaction Journal links
-- Only for non-target transactions
-- ============================================================
INSERT INTO `tag_transaction_journal` (`id`,`tag_id`,`transaction_journal_id`) VALUES
(1, 1, 2),   -- essential -> Weekly groceries Feb
(2, 2, 5),   -- recurring -> Netflix Feb
(3, 2, 6),   -- recurring -> Rogers Feb
(4, 3, 9),   -- impulse-buy -> Bluetooth headphones
(5, 1, 10),  -- essential -> Costco Feb
(6, 2, 13),  -- recurring -> Netflix Mar
(7, 1, 15),  -- essential -> Loblaws Mar
(8, 1, 27),  -- essential -> Loblaws Mar 9
(9, 2, 30);  -- recurring -> Hydro Ottawa Mar

SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;

-- ============================================================
-- TASK 1 VERIFICATION:
-- Target transactions (Tim Hortons/Starbucks, March 1-7):
--   TJ12: Tim Hortons  Mar 1  C$5.47
--   TJ14: Starbucks    Mar 2  C$6.45
--   TJ16: Tim Hortons  Mar 3  C$8.23
--   TJ19: Starbucks    Mar 4  C$7.20
--   TJ21: Tim Hortons  Mar 5  C$2.35
--   TJ23: Starbucks    Mar 6  C$8.95
--   TJ25: Tim Hortons  Mar 7  C$7.85
-- Total: C$46.50
--
-- Distractors (should NOT be included):
--   TJ7:  Starbucks    Feb 25 C$9.35  (before March)
--   TJ8:  Tim Hortons  Feb 25 C$2.15  (before March)
--   TJ28: Tim Hortons  Mar 10 C$3.45  (after first week)
--   TJ29: Starbucks    Mar 10 C$6.75  (after first week)
-- ============================================================
--
-- ============================================================
-- TASK 2 VERIFICATION:
-- February spending by category:
--   Groceries:           C$87.43 + C$156.82 = C$244.25
--   Gas & Transportation: C$62.10
--   Entertainment:        C$16.99
--   Utilities & Bills:    C$75.00
--   Dining Out:           C$9.35 + C$2.15 = C$11.50
--   Shopping:             C$49.99
--
-- Budgets vs Actual:
--   Groceries:            Budget C$300   | Spent C$244.25 | UNDER by C$55.75
--   Gas & Transportation: Budget C$50    | Spent C$62.10  | EXCEEDED by C$12.10
--   Entertainment:        Budget C$25    | Spent C$16.99  | UNDER by C$8.01
--   Utilities & Bills:    Budget C$100   | Spent C$75.00  | UNDER by C$25.00
--   Dining Out:           Budget C$10    | Spent C$11.50  | EXCEEDED by C$1.50
--   Shopping:             Budget C$40    | Spent C$49.99  | EXCEEDED by C$9.99
-- ============================================================