-- Packaging Module Migration
-- Tables for managing packaging operations, templates, materials, and batches
-- Designed for agriculture wholesale and packing industry

-- Packaging Materials (tracking packaging supplies inventory)
CREATE TABLE IF NOT EXISTS packaging_materials (
    id SERIAL PRIMARY KEY,
    org_id INTEGER NOT NULL,
    material_code VARCHAR(50) NOT NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    material_type VARCHAR(50), -- 'box', 'bag', 'film', 'tray', 'label', 'tape', 'pallet'
    material_category VARCHAR(50), -- 'primary', 'secondary', 'tertiary'
    supplier_id INTEGER REFERENCES suppliers(id),
    unit_of_measure VARCHAR(20), -- 'each', 'roll', 'sheet', 'meter'
    cost_per_unit DECIMAL(10,4),
    current_stock DECIMAL(10,2) DEFAULT 0,
    minimum_stock DECIMAL(10,2),
    reorder_quantity DECIMAL(10,2),
    dimensions JSONB, -- {length, width, height, unit}
    weight_per_unit DECIMAL(10,4), -- kg
    specifications JSONB, -- Material specs, certifications, etc
    status VARCHAR(20) DEFAULT 'active', -- 'active', 'discontinued', 'out-of-stock'
    created_by INTEGER REFERENCES users(id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(org_id, material_code)
);

-- Packaging Templates (reusable packaging configurations)
CREATE TABLE IF NOT EXISTS packaging_templates (
    id SERIAL PRIMARY KEY,
    org_id INTEGER NOT NULL,
    template_code VARCHAR(50) NOT NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    product_category_id INTEGER REFERENCES product_categories(id),
    package_type VARCHAR(50), -- 'retail', 'bulk', 'export', 'sample'
    target_weight DECIMAL(10,3), -- kg
    target_quantity INTEGER, -- units per package
    materials_list JSONB, -- [{material_id, quantity, description}]
    labeling_requirements JSONB, -- Label templates, barcodes, etc
    instructions TEXT, -- Packing instructions
    estimated_time_minutes INTEGER, -- Time to pack
    cost_estimate DECIMAL(10,2), -- Estimated cost per package
    images JSONB, -- Reference images
    status VARCHAR(20) DEFAULT 'active', -- 'active', 'draft', 'archived'
    created_by INTEGER REFERENCES users(id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(org_id, template_code)
);

-- Packaging Orders (work orders for packaging operations)
CREATE TABLE IF NOT EXISTS packaging_orders (
    id SERIAL PRIMARY KEY,
    org_id INTEGER NOT NULL,
    order_number VARCHAR(50) NOT NULL,
    order_date TIMESTAMP NOT NULL,
    product_id INTEGER REFERENCES products(id),
    template_id INTEGER REFERENCES packaging_templates(id),
    source_batch_number VARCHAR(100), -- Source batch from inventory
    target_batch_number VARCHAR(100), -- New batch number after packaging
    quantity_to_pack DECIMAL(10,2),
    quantity_packed DECIMAL(10,2) DEFAULT 0,
    unit VARCHAR(20),
    packages_required INTEGER,
    packages_completed INTEGER DEFAULT 0,
    assigned_to INTEGER REFERENCES users(id),
    assigned_location_id INTEGER REFERENCES locations(id),
    priority VARCHAR(20) DEFAULT 'normal', -- 'low', 'normal', 'high', 'urgent'
    status VARCHAR(20) DEFAULT 'pending', -- 'pending', 'in-progress', 'completed', 'cancelled', 'on-hold'
    scheduled_start TIMESTAMP,
    scheduled_end TIMESTAMP,
    actual_start TIMESTAMP,
    actual_end TIMESTAMP,
    notes TEXT,
    customer_id INTEGER REFERENCES customers(id),
    sales_order_id INTEGER, -- Link to sales order if applicable
    metadata JSONB,
    created_by INTEGER REFERENCES users(id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(org_id, order_number)
);

-- Packaging Lines (tracking items in packaging orders)
CREATE TABLE IF NOT EXISTS packaging_lines (
    id SERIAL PRIMARY KEY,
    org_id INTEGER NOT NULL,
    order_id INTEGER REFERENCES packaging_orders(id) ON DELETE CASCADE,
    line_number INTEGER,
    material_id INTEGER REFERENCES packaging_materials(id),
    quantity_required DECIMAL(10,2),
    quantity_used DECIMAL(10,2) DEFAULT 0,
    unit VARCHAR(20),
    cost_per_unit DECIMAL(10,4),
    total_cost DECIMAL(10,2),
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Packaging Batches (completed packaging batches with tracking)
CREATE TABLE IF NOT EXISTS packaging_batches (
    id SERIAL PRIMARY KEY,
    org_id INTEGER NOT NULL,
    batch_number VARCHAR(100) NOT NULL,
    order_id INTEGER REFERENCES packaging_orders(id),
    product_id INTEGER REFERENCES products(id),
    template_id INTEGER REFERENCES packaging_templates(id),
    packed_date TIMESTAMP NOT NULL,
    packed_by INTEGER REFERENCES users(id),
    location_id INTEGER REFERENCES locations(id),
    total_packages INTEGER,
    total_weight DECIMAL(10,3),
    total_units INTEGER,
    package_type VARCHAR(50),
    expiry_date DATE,
    lot_number VARCHAR(100),
    quality_checked BOOLEAN DEFAULT false,
    quality_check_date TIMESTAMP,
    quality_check_by INTEGER REFERENCES users(id),
    quality_result VARCHAR(20), -- 'pass', 'fail', 'conditional'
    storage_location VARCHAR(100),
    labels_printed BOOLEAN DEFAULT false,
    label_print_date TIMESTAMP,
    status VARCHAR(20) DEFAULT 'packed', -- 'packed', 'quality-checked', 'stored', 'shipped', 'quarantine'
    metadata JSONB, -- Barcode info, pallet details, etc
    notes TEXT,
    created_by INTEGER REFERENCES users(id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(org_id, batch_number)
);

-- Packaging Performance (tracking efficiency metrics)
CREATE TABLE IF NOT EXISTS packaging_performance (
    id SERIAL PRIMARY KEY,
    org_id INTEGER NOT NULL,
    date DATE NOT NULL,
    shift VARCHAR(20), -- 'morning', 'afternoon', 'night'
    location_id INTEGER REFERENCES locations(id),
    operator_id INTEGER REFERENCES users(id),
    packages_produced INTEGER,
    hours_worked DECIMAL(5,2),
    packages_per_hour DECIMAL(10,2),
    downtime_minutes INTEGER,
    waste_percentage DECIMAL(5,2),
    quality_issues INTEGER,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Add indexes for performance
CREATE INDEX IF NOT EXISTS idx_packaging_materials_org ON packaging_materials(org_id);
CREATE INDEX IF NOT EXISTS idx_packaging_materials_type ON packaging_materials(material_type);
CREATE INDEX IF NOT EXISTS idx_packaging_materials_status ON packaging_materials(status);
CREATE INDEX IF NOT EXISTS idx_packaging_templates_org ON packaging_templates(org_id);
CREATE INDEX IF NOT EXISTS idx_packaging_templates_status ON packaging_templates(status);
CREATE INDEX IF NOT EXISTS idx_packaging_orders_org ON packaging_orders(org_id);
CREATE INDEX IF NOT EXISTS idx_packaging_orders_status ON packaging_orders(status);
CREATE INDEX IF NOT EXISTS idx_packaging_orders_date ON packaging_orders(order_date);
CREATE INDEX IF NOT EXISTS idx_packaging_lines_order ON packaging_lines(order_id);
CREATE INDEX IF NOT EXISTS idx_packaging_batches_org ON packaging_batches(org_id);
CREATE INDEX IF NOT EXISTS idx_packaging_batches_product ON packaging_batches(product_id);
CREATE INDEX IF NOT EXISTS idx_packaging_batches_date ON packaging_batches(packed_date);
CREATE INDEX IF NOT EXISTS idx_packaging_performance_org ON packaging_performance(org_id);
CREATE INDEX IF NOT EXISTS idx_packaging_performance_date ON packaging_performance(date);

-- Add to tenant_tables for IndexedDB sync
INSERT INTO tenant_tables (name, id_column, org_column, sync_enabled, last_sync) 
VALUES 
    ('packaging_materials', 'id', 'org_id', true, CURRENT_TIMESTAMP),
    ('packaging_templates', 'id', 'org_id', true, CURRENT_TIMESTAMP),
    ('packaging_orders', 'id', 'org_id', true, CURRENT_TIMESTAMP),
    ('packaging_lines', 'id', 'org_id', true, CURRENT_TIMESTAMP),
    ('packaging_batches', 'id', 'org_id', true, CURRENT_TIMESTAMP),
    ('packaging_performance', 'id', 'org_id', true, CURRENT_TIMESTAMP)
ON CONFLICT (name) DO UPDATE SET sync_enabled = true;

-- Add comments for documentation
COMMENT ON TABLE packaging_materials IS 'Packaging materials and supplies inventory';
COMMENT ON TABLE packaging_templates IS 'Reusable packaging configurations and instructions';
COMMENT ON TABLE packaging_orders IS 'Packaging work orders and job tracking';
COMMENT ON TABLE packaging_lines IS 'Materials used in packaging orders';
COMMENT ON TABLE packaging_batches IS 'Completed packaging batches with traceability';
COMMENT ON TABLE packaging_performance IS 'Packaging line performance and efficiency metrics';
