CREATE TABLE IF NOT EXISTS `agency_transactions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `identifier` varchar(50) NOT NULL,
  `type` varchar(50) NOT NULL, -- 'transfer', 'deposit', 'withdraw', 'fixed_deposit', 'interest'
  `amount` int(11) NOT NULL,
  `target_identifier` varchar(50) DEFAULT NULL,
  `reason` varchar(255) DEFAULT NULL,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `agency_cards` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `identifier` varchar(50) NOT NULL,
  `card_number` varchar(16) NOT NULL,
  `card_pin` varchar(4) NOT NULL,
  `card_type` varchar(50) DEFAULT 'Agency 1',
  `card_color` varchar(50) DEFAULT 'Ultra Blue',
  `is_active` tinyint(1) DEFAULT 1,
  PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `agency_fixed_deposits` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `identifier` varchar(50) NOT NULL,
  `amount` int(11) NOT NULL,
  `interest_rate` float NOT NULL,
  `duration_days` int(11) NOT NULL,
  `start_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `end_date` timestamp NOT NULL,
  `is_claimed` tinyint(1) DEFAULT 0,
  PRIMARY KEY (`id`)
);
