-- CHECKLABS BUTCHERY EDITION - supplier purchase extension
-- Import this after the original CHECKLABS/Laravel base database has been installed.
-- This is an application migration SQL, not a full Laravel base-schema dump.
SET NAMES utf8mb4;
SET @has_sell_price := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='butchery_batches' AND column_name='selling_price_per_kg');
SET @ddl := IF(@has_sell_price=0, 'ALTER TABLE `butchery_batches` ADD COLUMN `selling_price_per_kg` DECIMAL(12,2) NOT NULL DEFAULT 0 AFTER `cost_per_kg`', 'SELECT 1');
PREPARE s FROM @ddl; EXECUTE s; DEALLOCATE PREPARE s;
SET @has_supplier_id := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='butchery_batches' AND column_name='supplier_id');
SET @ddl := IF(@has_supplier_id=0, 'ALTER TABLE `butchery_batches` ADD COLUMN `supplier_id` BIGINT UNSIGNED NULL, ADD INDEX `butchery_batches_supplier_id_index` (`supplier_id`)', 'SELECT 1');
PREPARE s FROM @ddl; EXECUTE s; DEALLOCATE PREPARE s;
CREATE TABLE IF NOT EXISTS `butchery_purchases` (
 `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `branch_id` BIGINT UNSIGNED NOT NULL, `batch_id` BIGINT UNSIGNED NOT NULL,
 `supplier_id` BIGINT UNSIGNED NULL, `invoice_number` VARCHAR(100) NULL, `quantity_kg` DECIMAL(12,3) NOT NULL,
 `cost_per_kg` DECIMAL(12,2) NOT NULL, `total_amount` DECIMAL(12,2) NOT NULL, `amount_paid` DECIMAL(12,2) NOT NULL DEFAULT 0,
 `payment_status` VARCHAR(20) NOT NULL DEFAULT 'unpaid', `purchased_by` BIGINT UNSIGNED NULL,
 `created_at` TIMESTAMP NULL DEFAULT NULL, `updated_at` TIMESTAMP NULL DEFAULT NULL,
 PRIMARY KEY (`id`), KEY `butchery_purchases_branch_id_index` (`branch_id`), KEY `butchery_purchases_batch_id_index` (`batch_id`), KEY `butchery_purchases_supplier_id_index` (`supplier_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
