CREATE TABLE IF NOT EXISTS `hk_users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `password` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `hk_expense_categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `category_name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `hk_budgets` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`category_id` INT(11) NULL DEFAULT NULL,
	`max_budget` DECIMAL(10,2) NOT NULL,
	`time_period` VARCHAR(255) NOT NULL COLLATE 'utf32_unicode_ci',
	PRIMARY KEY (`id`) USING BTREE,
	INDEX `FK_hk_budgets_hk_expense_categories` (`category_id`) USING BTREE,
	CONSTRAINT `FK_hk_budgets_hk_expense_categories` FOREIGN KEY (`category_id`) REFERENCES `hk_expense_categories` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION
);



CREATE TABLE IF NOT EXISTS `hk_expenses` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `category_id` int(11) DEFAULT NULL,
  `product_name` varchar(255) NOT NULL,
  `purchase_date` date NOT NULL,
  `amount_spent` decimal(10,2) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `category_id` (`category_id`),
  CONSTRAINT `expenses_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `hk_expense_categories` (`id`)
);