-- Packaging Module Tables Migration
-- Creates tables for packaging operations, templates, materials, batches, and performance tracking

-- Packaging Orders Table
CREATE TABLE IF NOT EXISTS packaging_orders (
    id SERIAL PRIMARY KEY,
    org_id INTEGER NOT NULL,
    order_number VARCHAR(50) NOT NULL UNIQUE,
    order_date TIMESTAMP NOT NULL,
    product_id INTEGER,
    product_name VARCHAR(255),
    template_id INTEGER,
    source_batch_number VARCHAR(50) NOT NULL,
    target_batch_number VARCHAR(50),
    quantity_to_pack NUMERIC(15,3) NOT NULL,
    unit VARCHAR(20),
    quantity_packed NUMERIC(15,3) DEFAULT 0,
    packages_required INTEGER NOT NULL,
    packages_completed INTEGER DEFAULT 0,
    assigned_to INTEGER,
    assigned_to_name VARCHAR(255),
    assigned_location_id INTEGER,
    priority VARCHAR(20) DEFAULT 'normal' CHECK (priority IN ('low', 'normal', 'high', 'urgent')),
    status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending', 'in-progress', 'completed', 'cancelled', 'on-hold')),
    scheduled_start TIMESTAMP,
    scheduled_end TIMESTAMP,
    actual_start TIMESTAMP,
    actual_end TIMESTAMP,
    customer_id INTEGER,
    notes TEXT,
    created_by INTEGER,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Packaging Templates Table
CREATE TABLE IF NOT EXISTS packaging_templates (
    id SERIAL PRIMARY KEY,
    org_id INTEGER NOT NULL,
    template_code VARCHAR(50) NOT NULL UNIQUE,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    package_type VARCHAR(50) NOT NULL,
    target_weight NUMERIC(10,3),
    target_volume NUMERIC(10,3),
    package_dimensions VARCHAR(100),
    materials_json TEXT,
    instructions TEXT,
    status VARCHAR(20) DEFAULT 'draft' CHECK (status IN ('draft', 'active', 'archived')),
    created_by INTEGER,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Packaging Materials Table
CREATE TABLE IF NOT EXISTS packaging_materials (
    id SERIAL PRIMARY KEY,
    org_id INTEGER NOT NULL,
    material_code VARCHAR(50) NOT NULL UNIQUE,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    material_type VARCHAR(50) NOT NULL,
    unit VARCHAR(20),
    unit_cost NUMERIC(10,2),
    current_stock NUMERIC(15,3) DEFAULT 0,
    minimum_stock NUMERIC(15,3),
    reorder_point NUMERIC(15,3),
    supplier_id INTEGER,
    supplier_name VARCHAR(255),
    status VARCHAR(20) DEFAULT 'active' CHECK (status IN ('active', 'inactive', 'discontinued')),
    created_by INTEGER,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Packaging Lines Table (materials used per order)
CREATE TABLE IF NOT EXISTS packaging_lines (
    id SERIAL PRIMARY KEY,
    order_id INTEGER NOT NULL REFERENCES packaging_orders(id) ON DELETE CASCADE,
    line_number INTEGER NOT NULL,
    material_id INTEGER REFERENCES packaging_materials(id),
    material_name VARCHAR(255),
    quantity_required NUMERIC(15,3),
    quantity_used NUMERIC(15,3) DEFAULT 0,
    unit VARCHAR(20),
    unit_cost NUMERIC(10,2),
    total_cost NUMERIC(15,2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Packaging Batches Table
CREATE TABLE IF NOT EXISTS packaging_batches (
    id SERIAL PRIMARY KEY,
    org_id INTEGER NOT NULL,
    batch_number VARCHAR(50) NOT NULL UNIQUE,
    order_id INTEGER REFERENCES packaging_orders(id),
    product_id INTEGER,
    product_name VARCHAR(255),
    packed_date TIMESTAMP NOT NULL,
    total_packages INTEGER NOT NULL,
    total_weight NUMERIC(15,3),
    total_units INTEGER,
    package_type VARCHAR(50),
    lot_number VARCHAR(50),
    expiry_date DATE,
    storage_location VARCHAR(100),
    packed_by INTEGER,
    packed_by_name VARCHAR(255),
    quality_checked BOOLEAN DEFAULT FALSE,
    quality_checked_by INTEGER,
    quality_checked_at TIMESTAMP,
    labels_printed BOOLEAN DEFAULT FALSE,
    status VARCHAR(20) DEFAULT 'packed' CHECK (status IN ('packed', 'verified', 'shipped', 'returned')),
    notes TEXT,
    created_by INTEGER,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Packaging Performance Table (for analytics)
CREATE TABLE IF NOT EXISTS packaging_performance (
    id SERIAL PRIMARY KEY,
    org_id INTEGER NOT NULL,
    date DATE NOT NULL,
    shift VARCHAR(20),
    location_id INTEGER,
    user_id INTEGER,
    orders_completed INTEGER DEFAULT 0,
    packages_produced INTEGER DEFAULT 0,
    total_quantity NUMERIC(15,3) DEFAULT 0,
    defect_count INTEGER DEFAULT 0,
    rework_count INTEGER DEFAULT 0,
    downtime_minutes INTEGER DEFAULT 0,
    efficiency_percentage NUMERIC(5,2),
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create indexes for better performance
CREATE INDEX IF NOT EXISTS idx_packaging_orders_org_id 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_orders_assigned ON packaging_orders(assigned_to);

CREATE INDEX IF NOT EXISTS idx_packaging_templates_org_id 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_materials_org_id ON packaging_materials(org_id);
CREATE INDEX IF NOT EXISTS idx_packaging_materials_status ON packaging_materials(status);

CREATE INDEX IF NOT EXISTS idx_packaging_lines_order_id ON packaging_lines(order_id);

CREATE INDEX IF NOT EXISTS idx_packaging_batches_org_id ON packaging_batches(org_id);
CREATE INDEX IF NOT EXISTS idx_packaging_batches_date ON packaging_batches(packed_date);
CREATE INDEX IF NOT EXISTS idx_packaging_batches_order_id ON packaging_batches(order_id);

CREATE INDEX IF NOT EXISTS idx_packaging_performance_org_id ON packaging_performance(org_id);
CREATE INDEX IF NOT EXISTS idx_packaging_performance_date ON packaging_performance(date);

-- Add comments
COMMENT ON TABLE packaging_orders IS 'Stores packaging orders for products';
COMMENT ON TABLE packaging_templates IS 'Reusable packaging templates with materials and instructions';
COMMENT ON TABLE packaging_materials IS 'Inventory of packaging materials (boxes, labels, tape, etc)';
COMMENT ON TABLE packaging_lines IS 'Materials used in each packaging order';
COMMENT ON TABLE packaging_batches IS 'Final packaged batches ready for shipment';
COMMENT ON TABLE packaging_performance IS 'Daily performance metrics for packaging operations';
