-- Create items_cat table first (parent table)
CREATE TABLE `items_cat` (
  `items_cat_id` int(11) NOT NULL AUTO_INCREMENT,
  `items_cat_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`items_cat_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create items table with foreign key reference
CREATE TABLE `items` (
  `items_id` int(11) NOT NULL AUTO_INCREMENT,
  `items_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `items_cat_id` int(11) NOT NULL,
  `sell_type` enum('unit','fraction') COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'unit: sell as whole, fraction: sell by weight/portion',
  `selling_price` decimal(10,2) NOT NULL,
  `image_path` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `is_favorite` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`items_id`),
  KEY `items_cat_id` (`items_cat_id`),
  CONSTRAINT `items_ibfk_1` FOREIGN KEY (`items_cat_id`) REFERENCES `items_cat` (`items_cat_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert some sample categories
INSERT INTO `items_cat` (`items_cat_name`) VALUES 
('Food'),
('Beverages'),
('Snacks'),
('Desserts');

-- Insert some sample items
INSERT INTO `items` (`items_name`, `items_cat_id`, `sell_type`, `selling_price`, `is_favorite`) VALUES
('Fried Chicken', 1, 'unit', 120.00, 1),
('Rice', 1, 'unit', 20.00, 1),
('Coca Cola', 2, 'unit', 45.00, 0),
('Ice Cream', 4, 'fraction', 35.00, 0);

CREATE TABLE `cash_denominations` (
  `denomination_id` int(11) NOT NULL AUTO_INCREMENT,
  `country_code` varchar(3) NOT NULL DEFAULT 'PHP' COMMENT 'Currency code (PHP, USD, EUR, etc.)',
  `denomination_type` enum('bill','coin') NOT NULL,
  `denomination_value` decimal(10,2) NOT NULL,
  `denomination_name` varchar(50) NOT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `sort_order` int(11) DEFAULT 0,
  PRIMARY KEY (`denomination_id`),
  UNIQUE KEY `unique_denomination` (`country_code`, `denomination_value`, `denomination_type`),
  KEY `country_code` (`country_code`),
  KEY `is_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; 