-- Phase 7: Enhanced Inventory Control System
-- Purchase Order line items, goods receipts, unit conversions, and dispatch management

-- ============================================================================
-- 1. PRODUCT UNIT CONVERSIONS
-- ============================================================================

-- Add unit conversion fields to products table
ALTER TABLE products ADD COLUMN IF NOT EXISTS base_unit VARCHAR(50) DEFAULT 'PIECE';
ALTER TABLE products ADD COLUMN IF NOT EXISTS master_unit VARCHAR(50) DEFAULT NULL;
ALTER TABLE products ADD COLUMN IF NOT EXISTS units_per_master INTEGER DEFAULT NULL;
ALTER TABLE products ADD COLUMN IF NOT EXISTS master_unit_2 VARCHAR(50) DEFAULT NULL;
ALTER TABLE products ADD COLUMN IF NOT EXISTS units_per_master_2 INTEGER DEFAULT NULL;

COMMENT ON COLUMN products.base_unit IS 'Smallest unit (e.g., PIECE, CAN, BOTTLE)';
COMMENT ON COLUMN products.master_unit IS 'First level bulk unit (e.g., CARTON, BOX, PACK)';
COMMENT ON COLUMN products.units_per_master IS 'How many base units in one master unit (e.g., 24 cans per carton)';
COMMENT ON COLUMN products.master_unit_2 IS 'Second level bulk unit (e.g., PALLET, CASE) - optional';
COMMENT ON COLUMN products.units_per_master_2 IS 'How many master units in one master_unit_2';

-- ============================================================================
-- 2. PURCHASE ORDER ENHANCEMENTS
-- ============================================================================

-- Update purchase_orders table with additional tracking fields
ALTER TABLE purchase_orders ADD COLUMN IF NOT EXISTS status VARCHAR(50) DEFAULT 'pending';
ALTER TABLE purchase_orders ADD COLUMN IF NOT EXISTS expected_delivery_date DATE DEFAULT NULL;
ALTER TABLE purchase_orders ADD COLUMN IF NOT EXISTS total_amount DECIMAL(12,2) DEFAULT 0.00;
ALTER TABLE purchase_orders ADD COLUMN IF NOT EXISTS notes TEXT DEFAULT NULL;
ALTER TABLE purchase_orders ADD COLUMN IF NOT EXISTS closed_at TIMESTAMPTZ DEFAULT NULL;
ALTER TABLE purchase_orders ADD COLUMN IF NOT EXISTS closed_by UUID REFERENCES users(user_id) DEFAULT NULL;
ALTER TABLE purchase_orders ADD COLUMN IF NOT EXISTS closure_notes TEXT DEFAULT NULL;

COMMENT ON COLUMN purchase_orders.status IS 'pending, approved, partially_received, fully_received, closed, cancelled';

-- Create index on status for filtering
CREATE INDEX IF NOT EXISTS idx_purchase_orders_status ON purchase_orders(status) WHERE isDeleted = false;
CREATE INDEX IF NOT EXISTS idx_purchase_orders_org_status ON purchase_orders(org_id, status) WHERE isDeleted = false;

-- ============================================================================
-- 3. PURCHASE ORDER LINE ITEMS
-- ============================================================================

CREATE TABLE IF NOT EXISTS purchase_order_lines (
  purchase_order_line_id UUID PRIMARY KEY DEFAULT uuid_generate_v7(),
  purchase_order_id UUID NOT NULL REFERENCES purchase_orders(purchase_order_id) ON DELETE CASCADE,
  product_id UUID NOT NULL REFERENCES products(product_id) ON DELETE RESTRICT,
  org_id UUID REFERENCES orgs(org_id) ON DELETE CASCADE,
  line_number INTEGER NOT NULL,
  description TEXT DEFAULT NULL,
  quantity_ordered DECIMAL(12,3) NOT NULL,
  unit_type VARCHAR(50) NOT NULL DEFAULT 'PIECE',
  unit_price DECIMAL(12,2) DEFAULT 0.00,
  line_total DECIMAL(12,2) GENERATED ALWAYS AS (quantity_ordered * unit_price) STORED,
  quantity_received DECIMAL(12,3) DEFAULT 0.00,
  quantity_remaining DECIMAL(12,3) GENERATED ALWAYS AS (quantity_ordered - quantity_received) STORED,
  notes TEXT DEFAULT NULL,
  isDeleted BOOLEAN NOT NULL DEFAULT false,
  updatedAt TIMESTAMPTZ NOT NULL DEFAULT now(),
  CONSTRAINT chk_po_line_quantities CHECK (quantity_ordered >= 0 AND quantity_received >= 0)
);

CREATE INDEX idx_po_lines_po ON purchase_order_lines(purchase_order_id) WHERE isDeleted = false;
CREATE INDEX idx_po_lines_product ON purchase_order_lines(product_id) WHERE isDeleted = false;

COMMENT ON TABLE purchase_order_lines IS 'Line items for purchase orders with expected quantities';
COMMENT ON COLUMN purchase_order_lines.unit_type IS 'PIECE, CARTON, PALLET, etc. - matches product unit definitions';
COMMENT ON COLUMN purchase_order_lines.quantity_received IS 'Cumulative quantity received across all deliveries';

-- ============================================================================
-- 4. GOODS RECEIPTS ENHANCEMENT
-- ============================================================================

-- Add additional tracking to goods_receipts
ALTER TABLE goods_receipts ADD COLUMN IF NOT EXISTS delivery_note_number VARCHAR(100) DEFAULT NULL;
ALTER TABLE goods_receipts ADD COLUMN IF NOT EXISTS invoice_number VARCHAR(100) DEFAULT NULL;
ALTER TABLE goods_receipts ADD COLUMN IF NOT EXISTS received_by UUID REFERENCES users(user_id) DEFAULT NULL;
ALTER TABLE goods_receipts ADD COLUMN IF NOT EXISTS branch_id UUID REFERENCES branches(branch_id) DEFAULT NULL;
ALTER TABLE goods_receipts ADD COLUMN IF NOT EXISTS vehicle_number VARCHAR(100) DEFAULT NULL;

-- Create goods receipt lines table
CREATE TABLE IF NOT EXISTS goods_receipt_lines (
  goods_receipt_line_id UUID PRIMARY KEY DEFAULT uuid_generate_v7(),
  goods_receipt_id UUID NOT NULL REFERENCES goods_receipts(goods_receipt_id) ON DELETE CASCADE,
  purchase_order_line_id UUID NOT NULL REFERENCES purchase_order_lines(purchase_order_line_id) ON DELETE RESTRICT,
  product_id UUID NOT NULL REFERENCES products(product_id) ON DELETE RESTRICT,
  org_id UUID REFERENCES orgs(org_id) ON DELETE CASCADE,
  quantity_received DECIMAL(12,3) NOT NULL,
  unit_type VARCHAR(50) NOT NULL DEFAULT 'PIECE',
  batch_number VARCHAR(100) DEFAULT NULL,
  expiry_date DATE DEFAULT NULL,
  notes TEXT DEFAULT NULL,
  isDeleted BOOLEAN NOT NULL DEFAULT false,
  updatedAt TIMESTAMPTZ NOT NULL DEFAULT now(),
  CONSTRAINT chk_gr_line_qty CHECK (quantity_received > 0)
);

CREATE INDEX idx_gr_lines_receipt ON goods_receipt_lines(goods_receipt_id) WHERE isDeleted = false;
CREATE INDEX idx_gr_lines_po_line ON goods_receipt_lines(purchase_order_line_id) WHERE isDeleted = false;

COMMENT ON TABLE goods_receipt_lines IS 'Individual items received in each delivery against PO lines';

-- ============================================================================
-- 5. DISPATCH NOTES (TRANSFER DOCUMENTS)
-- ============================================================================

CREATE TABLE IF NOT EXISTS dispatch_notes (
  dispatch_note_id UUID PRIMARY KEY DEFAULT uuid_generate_v7(),
  dispatch_number VARCHAR(100) NOT NULL,
  org_id UUID REFERENCES orgs(org_id) ON DELETE CASCADE,
  from_branch_id UUID NOT NULL REFERENCES branches(branch_id) ON DELETE RESTRICT,
  to_branch_id UUID NOT NULL REFERENCES branches(branch_id) ON DELETE RESTRICT,
  dispatch_date TIMESTAMPTZ NOT NULL DEFAULT now(),
  dispatched_by UUID REFERENCES users(user_id) DEFAULT NULL,
  vehicle_number VARCHAR(100) DEFAULT NULL,
  driver_name VARCHAR(100) DEFAULT NULL,
  reference_id UUID DEFAULT NULL,
  reference_table VARCHAR(100) DEFAULT NULL,
  status VARCHAR(50) DEFAULT 'pending',
  received_date TIMESTAMPTZ DEFAULT NULL,
  received_by UUID REFERENCES users(user_id) DEFAULT NULL,
  notes TEXT DEFAULT NULL,
  isDeleted BOOLEAN NOT NULL DEFAULT false,
  updatedAt TIMESTAMPTZ NOT NULL DEFAULT now(),
  CONSTRAINT chk_dispatch_branches CHECK (from_branch_id != to_branch_id)
);

CREATE INDEX idx_dispatch_notes_org ON dispatch_notes(org_id) WHERE isDeleted = false;
CREATE INDEX idx_dispatch_notes_from ON dispatch_notes(from_branch_id) WHERE isDeleted = false;
CREATE INDEX idx_dispatch_notes_to ON dispatch_notes(to_branch_id) WHERE isDeleted = false;
CREATE INDEX idx_dispatch_notes_status ON dispatch_notes(status) WHERE isDeleted = false;

COMMENT ON TABLE dispatch_notes IS 'Transfer documents for moving inventory between branches';
COMMENT ON COLUMN dispatch_notes.status IS 'pending, in_transit, received, cancelled';
COMMENT ON COLUMN dispatch_notes.reference_table IS 'Optional: sales, purchase_orders, etc.';

-- Create unique dispatch number per org
CREATE UNIQUE INDEX idx_dispatch_number_org ON dispatch_notes(org_id, dispatch_number) WHERE isDeleted = false;

-- ============================================================================
-- 6. DISPATCH LINES
-- ============================================================================

CREATE TABLE IF NOT EXISTS dispatch_lines (
  dispatch_line_id UUID PRIMARY KEY DEFAULT uuid_generate_v7(),
  dispatch_note_id UUID NOT NULL REFERENCES dispatch_notes(dispatch_note_id) ON DELETE CASCADE,
  product_id UUID NOT NULL REFERENCES products(product_id) ON DELETE RESTRICT,
  org_id UUID REFERENCES orgs(org_id) ON DELETE CASCADE,
  line_number INTEGER NOT NULL,
  quantity_dispatched DECIMAL(12,3) NOT NULL,
  dispatch_unit VARCHAR(50) NOT NULL DEFAULT 'PIECE',
  quantity_received DECIMAL(12,3) DEFAULT NULL,
  receive_unit VARCHAR(50) DEFAULT NULL,
  repack_on_dispatch BOOLEAN DEFAULT false,
  batch_number VARCHAR(100) DEFAULT NULL,
  notes TEXT DEFAULT NULL,
  isDeleted BOOLEAN NOT NULL DEFAULT false,
  updatedAt TIMESTAMPTZ NOT NULL DEFAULT now(),
  CONSTRAINT chk_dispatch_line_qty CHECK (quantity_dispatched > 0)
);

CREATE INDEX idx_dispatch_lines_note ON dispatch_lines(dispatch_note_id) WHERE isDeleted = false;
CREATE INDEX idx_dispatch_lines_product ON dispatch_lines(product_id) WHERE isDeleted = false;

COMMENT ON TABLE dispatch_lines IS 'Individual items on dispatch note with optional unit conversion';
COMMENT ON COLUMN dispatch_lines.repack_on_dispatch IS 'If true, break bulk units into base units at destination';
COMMENT ON COLUMN dispatch_lines.dispatch_unit IS 'Unit sent from source (CARTON, PALLET, etc.)';
COMMENT ON COLUMN dispatch_lines.receive_unit IS 'Unit received at destination (may differ if repacked)';

-- ============================================================================
-- 7. INVENTORY TABLE UPDATES
-- ============================================================================

-- Add unit type tracking to inventory
ALTER TABLE inventory ADD COLUMN IF NOT EXISTS unit_type VARCHAR(50) DEFAULT 'PIECE';

-- Add unit type tracking to inventory_txn
ALTER TABLE inventory_txn ADD COLUMN IF NOT EXISTS unit_type VARCHAR(50) DEFAULT 'PIECE';
ALTER TABLE inventory_txn ADD COLUMN IF NOT EXISTS quantity_in_base_units INTEGER DEFAULT NULL;

COMMENT ON COLUMN inventory.unit_type IS 'The unit type this inventory record represents (PIECE, CARTON, etc.)';
COMMENT ON COLUMN inventory_txn.unit_type IS 'The unit type of the transaction';
COMMENT ON COLUMN inventory_txn.quantity_in_base_units IS 'Calculated: change * conversion_factor for reporting';

-- ============================================================================
-- 8. INVENTORY BREAKING TRANSACTIONS
-- ============================================================================

CREATE TABLE IF NOT EXISTS inventory_breaks (
  inventory_break_id UUID PRIMARY KEY DEFAULT uuid_generate_v7(),
  org_id UUID REFERENCES orgs(org_id) ON DELETE CASCADE,
  product_id UUID NOT NULL REFERENCES products(product_id) ON DELETE RESTRICT,
  branch_id UUID REFERENCES branches(branch_id) DEFAULT NULL,
  break_date TIMESTAMPTZ NOT NULL DEFAULT now(),
  from_unit VARCHAR(50) NOT NULL,
  to_unit VARCHAR(50) NOT NULL,
  quantity_broken DECIMAL(12,3) NOT NULL,
  quantity_produced DECIMAL(12,3) NOT NULL,
  batch_number VARCHAR(100) DEFAULT NULL,
  broken_by UUID REFERENCES users(user_id) DEFAULT NULL,
  txn_remove_id UUID DEFAULT NULL,
  txn_add_id UUID DEFAULT NULL,
  notes TEXT DEFAULT NULL,
  isDeleted BOOLEAN NOT NULL DEFAULT false,
  updatedAt TIMESTAMPTZ NOT NULL DEFAULT now(),
  CONSTRAINT chk_break_quantities CHECK (quantity_broken > 0 AND quantity_produced > 0)
);

CREATE INDEX idx_inventory_breaks_product ON inventory_breaks(product_id) WHERE isDeleted = false;
CREATE INDEX idx_inventory_breaks_branch ON inventory_breaks(branch_id) WHERE isDeleted = false;
CREATE INDEX idx_inventory_breaks_date ON inventory_breaks(break_date) WHERE isDeleted = false;

COMMENT ON TABLE inventory_breaks IS 'Log of bulk item breaking operations (CARTON → PIECES)';
COMMENT ON COLUMN inventory_breaks.txn_remove_id IS 'inventory_txn_id for removing bulk units';
COMMENT ON COLUMN inventory_breaks.txn_add_id IS 'inventory_txn_id for adding base units';

-- ============================================================================
-- 9. MATERIALIZED VIEW: INVENTORY MOVEMENT HISTORY
-- ============================================================================

CREATE MATERIALIZED VIEW IF NOT EXISTS inventory_movement_history AS
SELECT 
    it.inventory_txn_id,
    it.org_id,
    it.updatedAt as movement_date,
    p.product_id,
    p.name as product_name,
    p.barcode,
    it.change as quantity,
    it.unit_type,
    it.quantity_in_base_units,
    b_from.name as from_location,
    b_to.name as to_location,
    it.branch_id as location_id,
    it.reason as movement_type,
    it.reference_table,
    it.reference_id,
    CASE 
        WHEN it.reference_table = 'purchase_orders' THEN po.order_number
        ELSE NULL
    END as purchase_order_number,
    CASE 
        WHEN it.reference_table = 'goods_receipts' THEN gr.delivery_note_number
        ELSE NULL
    END as delivery_note_number,
    CASE 
        WHEN it.reference_table = 'goods_receipts' THEN gr.invoice_number
        WHEN it.reference_table = 'inventory_receive' THEN ir.invoice_number
        ELSE NULL
    END as invoice_number,
    CASE 
        WHEN it.reference_table = 'dispatch_notes' THEN dn.dispatch_number
        ELSE NULL
    END as dispatch_number,
    u.username as performed_by,
    it.data as additional_data
FROM inventory_txn it
LEFT JOIN products p ON it.product_id = p.product_id
LEFT JOIN branches b_from ON it.branch_id = b_from.branch_id
LEFT JOIN branches b_to ON (it.data::jsonb->>'to_branch_id')::uuid = b_to.branch_id
LEFT JOIN users u ON it.user_id = u.user_id
LEFT JOIN purchase_orders po ON (it.reference_table = 'purchase_orders' AND it.reference_id = po.purchase_order_id)
LEFT JOIN goods_receipts gr ON (it.reference_table = 'goods_receipts' AND it.reference_id = gr.goods_receipt_id)
LEFT JOIN inventory_receive ir ON (it.reference_table = 'inventory_receive' AND it.reference_id = ir.inventory_receive_id)
LEFT JOIN dispatch_notes dn ON (it.reference_table = 'dispatch_notes' AND it.reference_id = dn.dispatch_note_id)
WHERE it.isDeleted = false
ORDER BY it.updatedAt DESC;

-- Create indexes on the materialized view
CREATE INDEX IF NOT EXISTS idx_mvmt_history_org ON inventory_movement_history(org_id);
CREATE INDEX IF NOT EXISTS idx_mvmt_history_product ON inventory_movement_history(product_id);
CREATE INDEX IF NOT EXISTS idx_mvmt_history_date ON inventory_movement_history(movement_date);
CREATE INDEX IF NOT EXISTS idx_mvmt_history_location ON inventory_movement_history(location_id);

COMMENT ON MATERIALIZED VIEW inventory_movement_history IS 'Complete audit trail of all inventory movements with document references';

-- Function to refresh the materialized view
CREATE OR REPLACE FUNCTION refresh_inventory_movement_history()
RETURNS void AS $$
BEGIN
    REFRESH MATERIALIZED VIEW CONCURRENTLY inventory_movement_history;
END;
$$ LANGUAGE plpgsql;

-- ============================================================================
-- 10. HELPER FUNCTIONS
-- ============================================================================

-- Function to calculate PO completion percentage
CREATE OR REPLACE FUNCTION get_po_completion_percentage(po_id UUID)
RETURNS DECIMAL AS $$
DECLARE
    total_ordered DECIMAL;
    total_received DECIMAL;
BEGIN
    SELECT 
        COALESCE(SUM(quantity_ordered), 0),
        COALESCE(SUM(quantity_received), 0)
    INTO total_ordered, total_received
    FROM purchase_order_lines
    WHERE purchase_order_id = po_id AND isDeleted = false;
    
    IF total_ordered = 0 THEN
        RETURN 0;
    END IF;
    
    RETURN ROUND((total_received / total_ordered * 100)::NUMERIC, 2);
END;
$$ LANGUAGE plpgsql;

-- Function to auto-update PO status based on received quantities
CREATE OR REPLACE FUNCTION update_po_status_on_receipt()
RETURNS TRIGGER AS $$
DECLARE
    po_id UUID;
    completion_pct DECIMAL;
BEGIN
    -- Get the purchase order ID from the line
    SELECT purchase_order_id INTO po_id
    FROM purchase_order_lines
    WHERE purchase_order_line_id = NEW.purchase_order_line_id;
    
    -- Calculate completion percentage
    completion_pct := get_po_completion_percentage(po_id);
    
    -- Update PO status
    IF completion_pct >= 100 THEN
        UPDATE purchase_orders 
        SET status = 'fully_received'
        WHERE purchase_order_id = po_id AND status != 'closed';
    ELSIF completion_pct > 0 THEN
        UPDATE purchase_orders 
        SET status = 'partially_received'
        WHERE purchase_order_id = po_id AND status = 'pending';
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Trigger to update PO status when goods are received
DROP TRIGGER IF EXISTS trg_update_po_status ON goods_receipt_lines;
CREATE TRIGGER trg_update_po_status
AFTER INSERT OR UPDATE ON goods_receipt_lines
FOR EACH ROW
EXECUTE FUNCTION update_po_status_on_receipt();

-- Function to update purchase_order_lines.quantity_received
CREATE OR REPLACE FUNCTION update_po_line_received_qty()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE purchase_order_lines
    SET quantity_received = quantity_received + NEW.quantity_received
    WHERE purchase_order_line_id = NEW.purchase_order_line_id;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Trigger to update line received quantity
DROP TRIGGER IF EXISTS trg_update_po_line_qty ON goods_receipt_lines;
CREATE TRIGGER trg_update_po_line_qty
AFTER INSERT ON goods_receipt_lines
FOR EACH ROW
EXECUTE FUNCTION update_po_line_received_qty();

-- ============================================================================
-- 11. SAMPLE DATA / DEMO
-- ============================================================================

-- Update existing products with unit conversion examples (only if they exist)
UPDATE products 
SET 
    base_unit = 'CAN',
    master_unit = 'CARTON',
    units_per_master = 24
WHERE name ILIKE '%coca%cola%' AND isDeleted = false;

UPDATE products 
SET 
    base_unit = 'BOTTLE',
    master_unit = 'CASE',
    units_per_master = 12
WHERE name ILIKE '%water%' AND isDeleted = false;

-- ============================================================================
-- 12. PERMISSIONS & GRANTS
-- ============================================================================

-- Grant permissions to application role (adjust as needed)
-- GRANT ALL ON purchase_order_lines TO app_role;
-- GRANT ALL ON goods_receipt_lines TO app_role;
-- GRANT ALL ON dispatch_notes TO app_role;
-- GRANT ALL ON dispatch_lines TO app_role;
-- GRANT ALL ON inventory_breaks TO app_role;
-- GRANT SELECT ON inventory_movement_history TO app_role;

-- ============================================================================
-- MIGRATION COMPLETE
-- ============================================================================

-- Add migration tracking
INSERT INTO schema_migrations (version, description, applied_at)
VALUES ('phase7_inventory_control', 'Enhanced inventory control with PO tracking, unit conversions, and dispatch management', now())
ON CONFLICT (version) DO UPDATE SET applied_at = now();
