-- Manufacturing v2: bulk production, separate packing, packaging materials
-- Run in phpMyAdmin AFTER selecting your Okhes database in the left sidebar.
--
-- IMPORTANT: Uses the NEW Okhes database (not bgmxghks_averycrm).
-- Change the name below if your cPanel database name differs.

USE bgmxghks_okhes;

-- ========== Feed types (bulk formulas) ==========
CREATE TABLE IF NOT EXISTS feed_types (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    sku VARCHAR(100) NULL,
    current_bulk_stock DECIMAL(15,3) NOT NULL DEFAULT 0.000 COMMENT 'Unpacked feed in kg',
    unit_cost_per_kg DECIMAL(15,6) NOT NULL DEFAULT 0.000000,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- ========== Packaging materials (bags) — separate from raw ingredients ==========
CREATE TABLE IF NOT EXISTS packaging_materials (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    sku VARCHAR(100) NULL,
    feed_type_id INT NULL COMMENT 'Optional: which feed this bag is for',
    bag_weight_kg DECIMAL(10,3) NOT NULL DEFAULT 0.000,
    cost_per_unit DECIMAL(15,6) NOT NULL DEFAULT 0.000000,
    supplier_id INT NULL,
    current_stock DECIMAL(15,3) NOT NULL DEFAULT 0.000,
    minimum_stock DECIMAL(15,3) NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_pkg_feed FOREIGN KEY (feed_type_id) REFERENCES feed_types(id),
    CONSTRAINT fk_pkg_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers(id)
);

-- ========== Recipes link to feed type (one recipe per feed) ==========
ALTER TABLE recipes ADD COLUMN IF NOT EXISTS feed_type_id INT NULL COMMENT 'Bulk feed formula owner';
ALTER TABLE recipes ADD CONSTRAINT fk_recipe_feed FOREIGN KEY (feed_type_id) REFERENCES feed_types(id);

-- ========== Finished products: packed bag-size SKUs ==========
ALTER TABLE finished_products ADD COLUMN IF NOT EXISTS feed_type_id INT NULL;
ALTER TABLE finished_products ADD COLUMN IF NOT EXISTS bag_weight_kg DECIMAL(10,3) NULL;
ALTER TABLE finished_products ADD COLUMN IF NOT EXISTS packaging_material_id INT NULL;
ALTER TABLE finished_products ADD COLUMN IF NOT EXISTS product_kind ENUM('PACKED','SERVICE','OTHER') NOT NULL DEFAULT 'PACKED';

-- ========== Production batches output bulk stock ==========
ALTER TABLE production_batches ADD COLUMN IF NOT EXISTS feed_type_id INT NULL;
ALTER TABLE production_batches ADD COLUMN IF NOT EXISTS reversed_at DATETIME NULL;
ALTER TABLE production_batches ADD COLUMN IF NOT EXISTS reversed_by INT NULL;
ALTER TABLE production_batches ADD COLUMN IF NOT EXISTS reversal_reason TEXT NULL;

-- ========== Packing runs (Stage 3) ==========
CREATE TABLE IF NOT EXISTS packing_runs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    feed_type_id INT NOT NULL,
    packing_date DATETIME NOT NULL,
    total_bulk_kg DECIMAL(15,3) NOT NULL DEFAULT 0.000,
    total_cost DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    status ENUM('COMPLETED','REVERSED') NOT NULL DEFAULT 'COMPLETED',
    notes TEXT NULL,
    created_by INT NULL,
    reversed_at DATETIME NULL,
    reversed_by INT NULL,
    reversal_reason TEXT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_pr_feed FOREIGN KEY (feed_type_id) REFERENCES feed_types(id),
    CONSTRAINT fk_pr_user FOREIGN KEY (created_by) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS packing_run_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    packing_run_id INT NOT NULL,
    product_id INT NOT NULL COMMENT 'Packed bag-size SKU',
    packaging_material_id INT NOT NULL,
    quantity_bags DECIMAL(15,3) NOT NULL,
    bulk_kg_used DECIMAL(15,3) NOT NULL,
    bag_cost DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    bulk_cost DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    unit_cost DECIMAL(15,2) NOT NULL DEFAULT 0.00 COMMENT 'Cost per bag (bulk + packaging)',
    CONSTRAINT fk_pri_run FOREIGN KEY (packing_run_id) REFERENCES packing_runs(id) ON DELETE CASCADE,
    CONSTRAINT fk_pri_product FOREIGN KEY (product_id) REFERENCES finished_products(id),
    CONSTRAINT fk_pri_pkg FOREIGN KEY (packaging_material_id) REFERENCES packaging_materials(id)
);

-- ========== Packaging purchases ==========
CREATE TABLE IF NOT EXISTS packaging_purchases (
    id INT AUTO_INCREMENT PRIMARY KEY,
    supplier_id INT NOT NULL,
    purchase_date DATE NOT NULL,
    reference_number VARCHAR(100) NULL,
    status ENUM('ORDERED','PENDING','RECEIVED','CANCELLED') NOT NULL DEFAULT 'ORDERED',
    payment_status ENUM('PAID','PARTIAL','DUE') NOT NULL DEFAULT 'DUE',
    total_amount DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    notes TEXT NULL,
    created_by INT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_pp_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers(id)
);

CREATE TABLE IF NOT EXISTS packaging_purchase_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    purchase_id INT NOT NULL,
    packaging_material_id INT NOT NULL,
    quantity DECIMAL(15,3) NOT NULL,
    unit_cost DECIMAL(15,6) NOT NULL,
    line_total DECIMAL(15,2) NOT NULL,
    tax_rate_id INT NULL,
    tax_amount DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    tax_recoverable TINYINT(1) NOT NULL DEFAULT 1,
    CONSTRAINT fk_ppi_purchase FOREIGN KEY (purchase_id) REFERENCES packaging_purchases(id),
    CONSTRAINT fk_ppi_pkg FOREIGN KEY (packaging_material_id) REFERENCES packaging_materials(id)
);

-- ========== Extend stock_movements item types ==========
-- MySQL 8+: alter enum; for compatibility use VARCHAR migration in PHP bootstrap
ALTER TABLE stock_movements MODIFY item_type VARCHAR(20) NOT NULL;

-- ========== Transaction reversals log ==========
CREATE TABLE IF NOT EXISTS transaction_reversals (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    transaction_type ENUM('SALE','PRODUCTION','PACKING') NOT NULL,
    original_id INT NOT NULL,
    reversal_movement_type VARCHAR(50) NOT NULL,
    reason TEXT NULL,
    reversed_by INT NOT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_tr_user FOREIGN KEY (reversed_by) REFERENCES users(id)
);

-- ========== Sales backdating ==========
ALTER TABLE sales ADD COLUMN IF NOT EXISTS entered_at DATETIME NULL COMMENT 'When record was created in system';
ALTER TABLE sales ADD COLUMN IF NOT EXISTS is_backdated TINYINT(1) NOT NULL DEFAULT 0;
ALTER TABLE sales ADD COLUMN IF NOT EXISTS reversed_at DATETIME NULL;
ALTER TABLE sales ADD COLUMN IF NOT EXISTS reversed_by INT NULL;
ALTER TABLE sales ADD COLUMN IF NOT EXISTS reversal_reason TEXT NULL;
ALTER TABLE sales ADD COLUMN IF NOT EXISTS status_reason TEXT NULL;

-- Update status enum to include REVERSED
-- ALTER TABLE sales MODIFY status ENUM('DRAFT','COMPLETED','CANCELLED','REVERSED') NOT NULL DEFAULT 'COMPLETED';

-- ========== Purchase tax per line ==========
ALTER TABLE purchase_items ADD COLUMN IF NOT EXISTS tax_rate_id INT NULL;
ALTER TABLE purchase_items ADD COLUMN IF NOT EXISTS tax_amount DECIMAL(15,2) NOT NULL DEFAULT 0.00;
ALTER TABLE purchase_items ADD COLUMN IF NOT EXISTS tax_recoverable TINYINT(1) NOT NULL DEFAULT 1;

-- ========== Tax rates: recoverable flag ==========
ALTER TABLE tax_rates ADD COLUMN IF NOT EXISTS is_recoverable TINYINT(1) NOT NULL DEFAULT 1;

-- ========== New permissions ==========
INSERT IGNORE INTO permissions (name, description) VALUES
('packaging', 'Packaging materials management'),
('packing', 'Packing runs'),
('feed_types', 'Feed type / bulk formula management'),
('transactions.reverse', 'Reverse sales, production, or packing'),
('sales.backdate', 'Enter sales with a past date'),
('reports.tax', 'Tax in/out report'),
('reports.profit', 'Gross and net profit reports');

INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r JOIN permissions p ON p.name IN ('packaging','packing','feed_types','transactions.reverse')
WHERE r.name IN ('Admin','Production Manager');

INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r JOIN permissions p ON p.name IN ('sales.backdate','reports.tax','reports.profit')
WHERE r.name IN ('Admin','Finance');