-- IMK 요구(TO-BE 상품 업로드 양식 / #14 발주배수 / #17 협력사 분류카테고리 / #20 공급사) 기준 데모 데이터.
-- 기본필드는 items 컬럼, TO-BE 신규 항목(발주배수·하도급·납품대금연동)은 items.custom,
-- 협력사 분류카테고리·유통레벨은 suppliers.custom 에 넣는다 — 전부 회사 설정(item_fields/supplier_fields) 정의와 같은 키.
-- 코드 접두사 IMK- 만 지우고 다시 넣으므로 멱등하게 재실행 가능.

DO $$
DECLARE
    v_company uuid;
    v_user    uuid;
BEGIN
    SELECT company_id INTO v_company FROM company.companies WHERE name = '아이마켓코리아';
    SELECT user_id    INTO v_user    FROM company.users     WHERE company_id = v_company AND id = 'admin';

    DELETE FROM partner.supplier_items
     WHERE item_id IN (SELECT item_id FROM partner.items WHERE company_id = v_company AND code LIKE 'IMK-%');
    DELETE FROM partner.items     WHERE company_id = v_company AND code LIKE 'IMK-%';
    DELETE FROM partner.suppliers WHERE company_id = v_company AND code LIKE 'IMKS-%';

    -- 협력사: 분류카테고리(제조원 성격) + 유통레벨을 custom 으로. (#17)
    INSERT INTO partner.suppliers (company_id, user_id, name, code, manager_name, manager_email, manager_contact_number, custom)
    VALUES
      (v_company, v_user, '한솔제지',     'IMKS-001', '김제지', 'paper@example.com',  '02-1000-0001', '{"sourcing_category":"복사용지","distribution_level":"제조"}'),
      (v_company, v_user, '대한오피스',   'IMKS-002', '박사무', 'office@example.com', '02-1000-0002', '{"sourcing_category":"사무용품","distribution_level":"유통"}'),
      (v_company, v_user, '세이프코리아', 'IMKS-003', '이안전', 'safe@example.com',   '02-1000-0003', '{"sourcing_category":"안전보호구","distribution_level":"총판"}'),
      (v_company, v_user, '한국툴스',     'IMKS-004', '최공구', 'tools@example.com',  '02-1000-0004', '{"sourcing_category":"절삭공구","distribution_level":"제조"}'),
      (v_company, v_user, '광명전기자재', 'IMKS-005', '정전기', 'elec@example.com',   '02-1000-0005', '{"sourcing_category":"전기자재","distribution_level":"유통"}');

    -- 상품: category = SG명, lead_time = 표준납기, delivery_type 1=직납 2=IMK물류(배송) 3=IMK물류(집배송).
    -- custom: order_multiple(발주배수) / subcontract_yn(하도급) / price_linked_yn(납품대금연동). (#14, TO-BE)
    INSERT INTO partner.items (
        company_id, user_id, name, code, model_name, category, spec, manufacturer, made_in,
        price, purchase_price, selling_price, internet_lowest_price, internet_lowest_price_yn,
        moq, lead_time, quantity_unit, delivery_type, vat_yn, delivery_fee_yn, custom
    ) VALUES
      (v_company, v_user, 'A4 복사용지 80g',     'IMK-P-001', 'HS-A4-80',   '복사용지',   '210×297mm 2500매/박스', '한솔제지',   '대한민국',
       23000,  21000,  25000,  22500, true,  '10 BOX', 3,  'BOX', 1, true,  false, '{"order_multiple":10,"subcontract_yn":false,"price_linked_yn":true}'),
      (v_company, v_user, '레이저 토너 (검정)',  'IMK-P-002', 'CF280A',     '사무용품',   '2700매 표준용량',       'HP',         '중국',
       98000,  86000,  105000, 91000, true,  '5 EA',   5,  'EA',  1, true,  false, '{"order_multiple":5,"subcontract_yn":false,"price_linked_yn":false}'),
      (v_company, v_user, '안전화 (경작업용)',   'IMK-P-003', 'SK-450',     '안전보호구', '265mm KCS 인증',        '세이프코리아', '대한민국',
       78000,  69000,  85000,  74000, true,  '20 EA',  7,  'EA',  2, true,  true,  '{"order_multiple":20,"subcontract_yn":true,"price_linked_yn":true}'),
      (v_company, v_user, '니트릴 장갑 (100매)', 'IMK-P-004', 'NG-100',     '안전보호구', 'M 사이즈 100매/박스',   '세이프코리아', '말레이시아',
       12500,  10800,  14000,  11900, true,  '50 BOX', 4,  'BOX', 2, true,  true,  '{"order_multiple":50,"subcontract_yn":false,"price_linked_yn":false}'),
      (v_company, v_user, '초경 엔드밀 4날',     'IMK-P-005', 'EM-4F-10',   '절삭공구',   'Φ10 × 75mm',            '한국툴스',   '대한민국',
       34000,  29000,  38000,  32000, true,  '30 EA',  10, 'EA',  1, true,  false, '{"order_multiple":30,"subcontract_yn":true,"price_linked_yn":true}'),
      (v_company, v_user, 'LED 평판등 50W',      'IMK-P-006', 'LP-640-50',  '전기자재',   '640×640 주광색',        '광명전기자재', '대한민국',
       46000,  39500,  52000,  44000, true,  '20 EA',  14, 'EA',  3, true,  true,  '{"order_multiple":20,"subcontract_yn":false,"price_linked_yn":true}'),
      (v_company, v_user, '산업용 베어링 6204',  'IMK-P-007', '6204-2RS',   '기계부품',   '내경 20mm 밀폐형',      'NSK',        '일본',
       8900,   7200,   9800,   8300,  true,  '100 EA', 12, 'EA',  1, true,  false, '{"order_multiple":100,"subcontract_yn":false,"price_linked_yn":false}'),
      (v_company, v_user, '다목적 세정제 4L',    'IMK-P-008', 'CL-4000',    '청소용품',   '4L × 4통/박스',         '대한오피스', '대한민국',
       31000,  26500,  35000,  29800, true,  '15 BOX', 5,  'BOX', 2, true,  true,  '{"order_multiple":15,"subcontract_yn":false,"price_linked_yn":false}');

    -- 실적(계약) 공급사 매핑. supply_type 1=유통 2=제조 3=총판. (#20)
    INSERT INTO partner.supplier_items (supplier_id, item_id, supply_type)
    SELECT s.supplier_id, i.item_id, m.supply_type
    FROM (VALUES
        ('IMK-P-001', 'IMKS-001', 2::smallint),
        ('IMK-P-001', 'IMKS-002', 1::smallint),
        ('IMK-P-002', 'IMKS-002', 1::smallint),
        ('IMK-P-003', 'IMKS-003', 3::smallint),
        ('IMK-P-004', 'IMKS-003', 3::smallint),
        ('IMK-P-005', 'IMKS-004', 2::smallint),
        ('IMK-P-006', 'IMKS-005', 1::smallint),
        ('IMK-P-007', 'IMKS-004', 2::smallint),
        ('IMK-P-008', 'IMKS-002', 1::smallint)
    ) AS m(item_code, supplier_code, supply_type)
    JOIN partner.items     i ON i.code = m.item_code     AND i.company_id = v_company
    JOIN partner.suppliers s ON s.code = m.supplier_code AND s.company_id = v_company;

    -- 협력사 커스텀필드 정의(분류카테고리·유통레벨)를 회사 설정에 등록 — 값만 있고 정의가 없으면 화면에 안 뜬다.
    UPDATE company.companies
    SET settings = settings || jsonb_build_object('supplier_fields', '[
          {"key":"sourcing_category","type":"text","label":"분류카테고리"},
          {"key":"distribution_level","type":"text","label":"유통레벨"}]'::jsonb)
    WHERE company_id = v_company;
END $$;