
--
-- Table structure for table `types`
--

CREATE TABLE `types` (
  `id` int NOT NULL AUTO_INCREMENT,
  `uid` varchar(5) DEFAULT NULL,
  `description` varchar(128) DEFAULT NULL,
  `notes` varchar(1000) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8mb3 COMMENT='category:lookup';

--
-- Dumping data for table `types`
--

INSERT INTO `types` VALUES (1,'T6152','ATM',NULL,'2026-04-18 05:28:28','2026-04-18 05:28:28'),(2,'T1949','Utility',NULL,'2026-04-18 05:28:28','2026-04-18 05:28:28'),(3,'T1333','Commute',NULL,'2026-04-18 05:28:28','2026-04-18 05:28:28'),(4,'T5253','Card',NULL,'2026-04-18 05:28:28','2026-04-18 05:28:28'),(5,'T4303','Supermarket',NULL,'2026-04-18 05:28:28','2026-04-18 05:28:28'),(6,'T0372','Other',NULL,'2026-04-18 05:28:28','2026-04-18 05:28:28'),(7,'T9586','Rent',NULL,'2026-04-18 05:28:28','2026-04-18 05:28:28'),(8,'T7745','Charity',NULL,'2026-04-18 05:28:28','2026-04-18 05:28:28'),(9,'T2261','Balance Adjustment',NULL,'2026-04-18 05:28:28','2026-04-18 05:28:28'),(10,'T3213','Salary',NULL,'2026-04-18 05:28:28','2026-04-18 05:28:28'),(11,'T8362','Pension',NULL,'2026-04-18 05:28:28','2026-04-18 05:28:28'),(12,'T5876','Taxable Interest',NULL,'2026-04-18 05:28:28','2026-04-18 05:28:28'),(13,'T6099','Non-taxable Interest',NULL,'2026-04-18 05:28:28','2026-04-18 05:28:28'),(14,'T4998','Cashback',NULL,'2026-04-18 05:28:28','2026-04-18 05:28:28'),(15,'T0776','Overseas Bank',NULL,'2026-04-18 05:28:28','2026-04-18 05:28:28'),(16,'T3312','Transfer',NULL,'2026-04-18 05:28:28','2026-04-18 05:28:28'),(17,'T9056','Excluded',NULL,'2026-04-18 05:28:28','2026-04-18 05:28:28'),(27,'T5020','Additional Payment',NULL,'2026-04-18 05:28:28','2026-04-18 05:28:28'),(28,'T7648','Interest-free Finance',NULL,'2026-04-18 05:28:28','2026-04-18 05:28:28'),(29,'T5422','Income',NULL,'2026-04-18 05:28:28','2026-04-18 05:28:28'),(30,'T4886','Mazda CX-3',NULL,'2026-04-18 05:28:28','2026-04-18 05:28:28');

--
-- Dumping routines for database 'bankuk_laravel'
--
/*!50003 DROP PROCEDURE IF EXISTS `bu_accounting_periods_current` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `bu_accounting_periods_current`(
                IN _date DATE
            )
BEGIN 
            SELECT 
                * 
            FROM 
                bu_accounting_periods
            WHERE 
                bu_accounting_periods.`period_start` <= _date AND bu_accounting_periods.`period_end` >= _date;
        END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `bu_accounts_dropdown` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `bu_accounts_dropdown`(
                IN _status VARCHAR(6)
            )
BEGIN 
            SELECT 
                bu_accounts.`account_id_alpha`,
                CONCAT(bu_banks.`trading_name`,' ',bu_accounts.`name`) AS _name, 
                bu_accounts.`account_number`, 
                bu_accounts.`status` 
            FROM 
                bu_accounts
            LEFT JOIN 
                bu_banks ON bu_accounts.`bank_id` = bu_banks.`bank_id`
            WHERE
                LOWER(bu_accounts.`status`) LIKE LOWER(_status)
            ORDER BY 
                _name; 
        END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `bu_accounts_get_data` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `bu_accounts_get_data`(
                IN _account_id_alpha VARCHAR(1)
            )
BEGIN 
            SELECT 
                bu_accounts.`account_id`
            FROM 
                bu_accounts
            WHERE
                LOWER(bu_accounts.`account_id_alpha`) LIKE LOWER(_account_id_alpha); 
        END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `bu_banks_dropdown` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `bu_banks_dropdown`()
BEGIN 
            SELECT 
                bu_banks.`bank_id`, 
                bu_banks.`legal_name`,
                bu_banks.`trading_name` 
            FROM 
                bu_banks
            ORDER BY bu_banks.`legal_name` ASC;
        END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `bu_entities_dropdown` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `bu_entities_dropdown`()
BEGIN 
            SELECT 
                bu_entities.`entity_id`, 
                bu_entities.`entity_description`
            FROM 
                bu_entities
            ORDER BY 
                bu_entities.`entity_description` ASC;
        END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `bu_password` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `bu_password`(
                IN _email VARCHAR(200)
            )
BEGIN 
            SELECT 
                bu_admin.`email`,
                bu_admin.`md5_password`, 
                bu_admin.`password_hash`, 
                bu_admin.`admin_id`,
                bu_admin.`locked`,
                bu_admin.`login_attempts`  
            FROM 
                bu_admin
            WHERE 
                bu_admin.`email` = _email;
        END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `bu_regular_debit_types_dropdown` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `bu_regular_debit_types_dropdown`()
BEGIN 
            SELECT 
                bu_regular_debit_types.`type`, 
                bu_regular_debit_types.`description` 
            FROM 
                bu_regular_debit_types
            ORDER BY 
                bu_regular_debit_types.`description` ASC;
        END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `bu_settings_get_settings` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `bu_settings_get_settings`()
BEGIN 
            SELECT 
                *
            FROM 
                bu_settings;
        END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `bu_tax_years_current` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `bu_tax_years_current`(
                IN _date DATE
            )
BEGIN 
            SELECT 
                * 
            FROM 
                bu_tax_years
            WHERE 
                bu_tax_years.`tax_year_start` <= _date AND bu_tax_years.`tax_year_end` >= _date;
        END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `bu_transactions_get_transactions` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `bu_transactions_get_transactions`(
                IN _record TEXT
            )
BEGIN
            SELECT 
                bu_transactions.`id`,
                bu_transactions.`account_id_alpha`,
                bu_banks.`trading_name`,
                bu_accounts.`name`,
                bu_accounts.`sort_code`,
                bu_accounts.`account_number`,
                bu_accounts.`status`,
                bu_transactions.`amount`,
                bu_transaction_types.`type_description`,
                bu_transaction_sub_types.`sub_type_description`,
                bu_transaction_methods.`method_description`,
                bu_entities.`entity_description`,
                bu_transactions.`transaction_date`,
                bu_transactions.`period`,
                bu_transactions.`tax_year`,
                bu_transactions.`notes`
            FROM
                bu_transactions
            LEFT JOIN
                bu_accounts ON bu_transactions.`account_id_alpha` = bu_accounts.`account_id_alpha`
            LEFT JOIN
                bu_entities ON bu_transactions.`entity_id` = bu_entities.`entity_id`
            LEFT JOIN
                bu_transaction_types ON bu_transactions.`type_id` = bu_transaction_types.`type_id`
            LEFT JOIN
                bu_transaction_sub_types ON bu_transactions.`sub_type_id` = bu_transaction_sub_types.`sub_type_id`
            LEFT JOIN
                bu_transaction_methods ON bu_transactions.`method_id` = bu_transaction_methods.`method_id`
            LEFT JOIN
                bu_banks ON bu_accounts.`bank_id` = bu_banks.`bank_id`
            WHERE 
                bu_transactions.`id` LIKE _record       
            ORDER BY 
                bu_transactions.`transaction_date` DESC, 
                bu_transactions.`id` DESC;
        END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `bu_transaction_methods_dropdown` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `bu_transaction_methods_dropdown`()
BEGIN 
            SELECT 
                bu_transaction_methods.`method_id`, 
                bu_transaction_methods.`method_description` 
            FROM 
                bu_transaction_methods
            ORDER BY 
                bu_transaction_methods.`method_description` ASC;
        END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `bu_transaction_sub_types_dropdown` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `bu_transaction_sub_types_dropdown`()
BEGIN 
            SELECT 
                bu_transaction_sub_types.`sub_type_id`, 
                bu_transaction_sub_types.`sub_type_description` 
            FROM 
                bu_transaction_sub_types
            ORDER BY 
                bu_transaction_sub_types.`sub_type_description` ASC;
        END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `bu_transaction_types_dropdown` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `bu_transaction_types_dropdown`()
BEGIN 
            SELECT 
                bu_transaction_types.`type_id`, 
                bu_transaction_types.`type_description` 
            FROM 
                bu_transaction_types
            ORDER BY 
                bu_transaction_types.`type_description` ASC;
        END ;;
DELIMITER ;


