-- Import Raw Materials Production Data
-- This script will clean and import production data from rawmaterial.sql
-- WARNING: This will DELETE ALL existing data in m_raw_materials table!

-- INSTRUCTIONS:
-- 1. Backup your database first!
-- 2. Run this script using: psql -U username -d database_name -f import_raw_materials_production.sql
-- 3. Or execute via pgAdmin or your preferred PostgreSQL client

BEGIN;

-- Step 1: Backup existing data (optional - comment out if not needed)
-- CREATE TABLE m_raw_materials_backup_20260202 AS SELECT * FROM m_raw_materials;

-- Step 2: Truncate table
TRUNCATE TABLE m_raw_materials RESTART IDENTITY CASCADE;

-- Step 3: Insert production data
-- Note: Data with '-' values for uom_utuh or tipe_doi will be set to default values
-- uom_utuh '-' will be set to same as uom_pakai
-- tipe_doi '-' will be set to 'Kering'

INSERT INTO m_raw_materials(
    name, code, description, uom, uom_pakai, conversion_factor_utuh_pakai, 
    uom_utuh, conversion_factor_kirim_utuh, uom_kirim, tipe, tipe_doi,
    delete_flag, created_by, updated_by, created_at, updated_at
)
SELECT 
    name,
    code,
    description,
    uom,
    uom_pakai,
    CAST(conversion_factor_utuh_pakai AS NUMERIC),
    CASE WHEN uom_utuh = '-' THEN uom_pakai ELSE uom_utuh END as uom_utuh,
    CAST(conversion_factor_kirim_utuh AS NUMERIC),
    uom_kirim,
    REPLACE(REPLACE(tipe, '(', ''), ')', '') as tipe, -- Remove parentheses
    CASE WHEN tipe_doi = '-' THEN 'Kering' ELSE tipe_doi END as tipe_doi,
    0 as delete_flag,
    1 as created_by,
    1 as updated_by,
    NOW() as created_at,
    NOW() as updated_at
FROM (VALUES
