-- V17: 테스트 데이터 (보험사 5 / 상품 20 / 조직 7 / 설계사 50 / 계약 200 / 수수료규정)
-- 운영 데이터 적재 전 시연/검증용

-- ============ 보험사 5 ============
INSERT INTO insurance_company (company_code, company_name, company_type, biz_no, contact_name, contact_phone, contact_email)
VALUES
    ('SAMSUNG',  '삼성생명',     'LIFE',    '101-81-22000', '김삼성', '02-1588-1000', 'contact@samsung.com'),
    ('HANWHA',   '한화생명',     'LIFE',    '104-81-23456', '이한화', '02-789-5000',  'contact@hanwha.com'),
    ('KYOBO',    '교보생명',     'LIFE',    '110-81-12345', '박교보', '02-2151-2000', 'contact@kyobo.com'),
    ('DBLIFE',   '디비생명',     'NONLIFE', '108-81-50101', '최디비', '02-3011-3000', 'contact@dblife.com'),
    ('KB',       'KB손해보험',   'NONLIFE', '105-81-99999', '정케이비','02-2080-1114','contact@kb.com');

-- ============ 상품 20 (보험사별 4개) ============
INSERT INTO product (company_id, product_code, product_name, insurance_type, product_group, pay_period_type, launch_date)
SELECT c.company_id, p.product_code, p.product_name, p.insurance_type, p.product_group, p.pay_period_type, '2023-01-01'
FROM insurance_company c
CROSS JOIN (VALUES
    ('TERM10',     '정기보험 10년',   'TERM',       'TERM',  'MONTHLY'),
    ('WHOLE100',   '종신보험 100세',  'WHOLE_LIFE', 'WHOLE', 'MONTHLY'),
    ('SAVINGS5',   '저축보험 5년',    'SAVINGS',    'SAVE',  'MONTHLY'),
    ('HEALTHCARE', '건강보험',        'HEALTH',     'HEALTH','MONTHLY')
) AS p(product_code, product_name, insurance_type, product_group, pay_period_type);

-- ============ 조직 7 (본부 1 + 지점 3 + 팀 3) ============
INSERT INTO organization (org_id, parent_org_id, org_name, org_type, org_level, region, effective_from, is_active)
VALUES
    (1001, NULL, '본사',         'HQ', 1, '서울',   '2020-01-01', 'Y'),
    (1002, 1001, '서울지점',     'BR', 2, '서울',   '2020-01-01', 'Y'),
    (1003, 1001, '경기지점',     'BR', 2, '경기',   '2020-01-01', 'Y'),
    (1004, 1001, '부산지점',     'BR', 2, '부산',   '2020-01-01', 'Y'),
    (1005, 1002, '서울1팀',      'TM', 3, '서울',   '2020-01-01', 'Y'),
    (1006, 1002, '서울2팀',      'TM', 3, '서울',   '2020-01-01', 'Y'),
    (1007, 1003, '경기1팀',      'TM', 3, '경기',   '2020-01-01', 'Y');

-- agent SERIAL 시퀀스 보존을 위해 organization sequence 보정
SELECT setval('organization_org_id_seq', (SELECT MAX(org_id) FROM organization));

-- ============ 설계사 50 (팀당 ~16명) ============
-- 본부장 3 / 지사장 3 / 매니저 6 / 시니어FC 12 / FC 26
INSERT INTO agent (org_id, grade_id, agent_name, license_no, phone, email, status, join_date)
SELECT
    CASE
        WHEN g.grade_level >= 5 THEN 1001
        WHEN g.grade_level = 4 THEN (ARRAY[1002,1003,1004])[((s-1) % 3) + 1]
        ELSE (ARRAY[1005,1006,1007])[((s-1) % 3) + 1]
    END AS org_id,
    g.grade_id,
    '설계사' || LPAD(s::text, 3, '0') AS agent_name,
    'L' || LPAD(s::text, 6, '0') AS license_no,
    '010-' || LPAD((1000 + s)::text, 4, '0') || '-' || LPAD(s::text, 4, '0'),
    'agent' || s || '@ga.com',
    'ACTIVE',
    DATE '2023-01-01' + (s % 365)
FROM generate_series(1, 50) AS s
CROSS JOIN LATERAL (
    SELECT grade_id, grade_level FROM grade
    WHERE grade_level = CASE
        WHEN s <= 3 THEN 6     -- GM 본부장
        WHEN s <= 6 THEN 5     -- DM 지사장
        WHEN s <= 12 THEN 4    -- SMGR 시니어매니저
        WHEN s <= 24 THEN 2    -- SFC 시니어FC
        ELSE 1                  -- FC
    END
    LIMIT 1
) g;

-- ============ 수수료 규정 ============
-- commission_rate: 상품별 1년차 (모집)
INSERT INTO commission_rate (product_id, commission_year, rate_pct, effective_from, version, created_at)
SELECT product_id, 1,
       CASE insurance_type
           WHEN 'TERM'       THEN 60.0000
           WHEN 'WHOLE_LIFE' THEN 80.0000
           WHEN 'SAVINGS'    THEN 8.0000
           WHEN 'HEALTH'     THEN 70.0000
       END,
       '2023-01-01', 1, NOW()
FROM product;

-- 2~5년차 유지수수료 (점감)
INSERT INTO commission_rate (product_id, commission_year, rate_pct, effective_from, version, created_at)
SELECT p.product_id, y.year,
       CASE p.insurance_type
           WHEN 'TERM'       THEN 60.0000 * POWER(0.5, y.year - 1)
           WHEN 'WHOLE_LIFE' THEN 80.0000 * POWER(0.4, y.year - 1)
           WHEN 'SAVINGS'    THEN 8.0000 * POWER(0.5, y.year - 1)
           WHEN 'HEALTH'     THEN 70.0000 * POWER(0.4, y.year - 1)
       END,
       '2023-01-01', 1, NOW()
FROM product p
CROSS JOIN (VALUES (2),(3),(4),(5)) AS y(year);

-- payout_rule: 직급별 × 보험종류별 × 1년차 지급율
INSERT INTO payout_rule (grade_id, insurance_type, commission_year, payout_pct, effective_from, version, created_at)
SELECT g.grade_id, t.insurance_type, 1,
       -- 직급이 높을수록 본인 지급율은 낮아지고 오버라이드로 받음 (단순 모델)
       CASE g.grade_level
           WHEN 1 THEN 70.0000   -- FC
           WHEN 2 THEN 75.0000   -- SFC
           WHEN 3 THEN 60.0000   -- MGR
           WHEN 4 THEN 50.0000   -- SMGR
           WHEN 5 THEN 30.0000   -- DM
           WHEN 6 THEN 20.0000   -- GM
       END,
       '2023-01-01', 1, NOW()
FROM grade g
CROSS JOIN (VALUES ('TERM'),('WHOLE_LIFE'),('SAVINGS'),('HEALTH')) AS t(insurance_type);

-- 2~5년차 유지지급율
INSERT INTO payout_rule (grade_id, insurance_type, commission_year, payout_pct, effective_from, version, created_at)
SELECT g.grade_id, t.insurance_type, y.year,
       CASE g.grade_level
           WHEN 1 THEN 60.0000
           WHEN 2 THEN 65.0000
           WHEN 3 THEN 50.0000
           WHEN 4 THEN 40.0000
           WHEN 5 THEN 25.0000
           WHEN 6 THEN 15.0000
       END,
       '2023-01-01', 1, NOW()
FROM grade g
CROSS JOIN (VALUES ('TERM'),('WHOLE_LIFE'),('SAVINGS'),('HEALTH')) AS t(insurance_type)
CROSS JOIN (VALUES (2),(3),(4),(5)) AS y(year);

-- override_rule: 상위 직급 → 하위 직급 오버라이드
INSERT INTO override_rule (from_grade, to_grade, override_pct, calc_type, effective_from)
SELECT lower.grade_id, upper.grade_id, pct, 'DIRECT', '2023-01-01'
FROM (VALUES
    -- (하위 직급명, 상위 직급명, 오버라이드율 %)
    ('FC',   'MGR',  10.0000),
    ('FC',   'SMGR', 5.0000),
    ('FC',   'DM',   3.0000),
    ('SFC',  'MGR',  10.0000),
    ('SFC',  'SMGR', 5.0000),
    ('SFC',  'DM',   3.0000),
    ('MGR',  'SMGR', 10.0000),
    ('MGR',  'DM',   5.0000),
    ('SMGR', 'DM',   8.0000),
    ('DM',   'GM',   5.0000)
) AS r(low_name, up_name, pct)
JOIN grade lower ON lower.grade_name = r.low_name
JOIN grade upper ON upper.grade_name = r.up_name;

-- chargeback_rule: 실효월수별 환수율 (전 보험사 공통)
INSERT INTO chargeback_rule (company_code, insurance_type, lapse_month_from, lapse_month_to, cb_rate, effective_from)
VALUES
    (NULL, NULL, 0,  3,  100.0000, '2023-01-01'),  -- 3개월 이내 실효: 전액 환수
    (NULL, NULL, 4,  6,  80.0000,  '2023-01-01'),
    (NULL, NULL, 7,  12, 50.0000,  '2023-01-01'),
    (NULL, NULL, 13, 24, 20.0000,  '2023-01-01');

-- ============ 계약 200 ============
INSERT INTO contract (agent_id, product_id, policy_no, contractor_name, insured_name,
                      premium, pay_cycle, pay_period, coverage_period, status, contract_date, effective_date, created_at)
SELECT
    a.agent_id,
    p.product_id,
    'POL-' || LPAD(s::text, 8, '0'),
    '고객' || LPAD(s::text, 4, '0'),
    '피보험' || LPAD(s::text, 4, '0'),
    -- 보험료: 5만 ~ 50만 (5천원 단위)
    (50 + (s % 91) * 5) * 1000,
    'MONTHLY',
    20, 30,
    -- 95% 활성 / 5% 실효
    CASE WHEN s % 20 = 0 THEN 'LAPSE' ELSE 'ACTIVE' END,
    -- 계약일: 최근 6개월 내 분포
    CURRENT_DATE - ((s * 3) % 180),
    CURRENT_DATE - ((s * 3) % 180),
    NOW()
FROM generate_series(1, 200) AS s
CROSS JOIN LATERAL (
    SELECT agent_id FROM agent
    ORDER BY (agent_id * s) % 100
    LIMIT 1
) a
CROSS JOIN LATERAL (
    SELECT product_id FROM product
    ORDER BY (product_id * s) % 80
    LIMIT 1
) p;

-- 실효 계약에는 lapse_date 채움
UPDATE contract SET lapse_date = contract_date + INTERVAL '4 months'
WHERE status = 'LAPSE';

-- ============ company_profile (수동 업로드 모드) ============
INSERT INTO company_profile (company_code, data_format, receive_method, file_encoding, header_row, data_start_row, is_active)
SELECT company_code, 'EXCEL', 'MANUAL', 'UTF-8', 1, 2, 'Y'
FROM insurance_company
ON CONFLICT (company_code) DO NOTHING;
