CREATE TABLE IF NOT EXISTS `{{prefix}}invoice_items` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `invoice_id` BIGINT UNSIGNED NOT NULL,
  `service_id` BIGINT UNSIGNED NULL,
  `treatment_plan_item_id` BIGINT UNSIGNED NULL,
  `procedure_id` BIGINT UNSIGNED NULL,
  `item_code` VARCHAR(80) NULL,
  `description` VARCHAR(255) NOT NULL,
  `quantity` DECIMAL(10,2) NOT NULL DEFAULT 1.00,
  `unit_price` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `gross_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `discount_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `net_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `notes` VARCHAR(500) NULL,
  `created_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_invoice_item_invoice` (`invoice_id`,`id`),
  KEY `idx_invoice_item_service` (`service_id`),
  KEY `idx_invoice_item_plan` (`treatment_plan_item_id`),
  KEY `idx_invoice_item_procedure` (`procedure_id`),
  CONSTRAINT `{{prefix}}fk_invoice_item_invoice` FOREIGN KEY (`invoice_id`) REFERENCES `{{prefix}}invoices` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_invoice_item_service` FOREIGN KEY (`service_id`) REFERENCES `{{prefix}}service_catalog` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_invoice_item_plan` FOREIGN KEY (`treatment_plan_item_id`) REFERENCES `{{prefix}}treatment_plan_items` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_invoice_item_procedure` FOREIGN KEY (`procedure_id`) REFERENCES `{{prefix}}procedures` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_invoice_item_creator` FOREIGN KEY (`created_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
