-- V86: 계약 승계 (contract_succession)
--   설계사 해촉/이관 시 보험계약의 "유지수수료" 귀속 설계사를 후임에게 전환.
--   - 모집수수료(1회차)는 원 모집설계사 귀속이므로 승계 대상 아님.
--   - 유지수수료는 승계월(succession_date의 YYYYMM) 이후 정산월부터 후임 설계사에게 귀속.
--   - 후임 설계사는 본인 등급의 payout 비율로 수령(CommissionCalculator 가 effectiveAgentId 등급 조회).
--   - 한 계약에 시차 승계(A→B→C) 가능: 정산월 기준 가장 최근 ACTIVE 승계가 유효.

CREATE TABLE contract_succession (
    succession_id    BIGSERIAL    PRIMARY KEY,
    contract_id      BIGINT       NOT NULL REFERENCES contract(contract_id),
    from_agent_id    BIGINT       NOT NULL REFERENCES agent(agent_id),
    to_agent_id      BIGINT       NOT NULL REFERENCES agent(agent_id),
    succession_date  DATE         NOT NULL,
    reason           VARCHAR(20)  NOT NULL DEFAULT 'TERMINATION',
    memo             VARCHAR(500),
    status           VARCHAR(20)  NOT NULL DEFAULT 'ACTIVE',
    created_at       TIMESTAMP    NOT NULL DEFAULT NOW(),
    created_by       BIGINT,
    updated_at       TIMESTAMP,
    updated_by       BIGINT,
    CHECK (reason IN ('TERMINATION','TRANSFER','REASSIGN')),
    CHECK (status IN ('ACTIVE','CANCELED')),
    CHECK (from_agent_id <> to_agent_id)
);

COMMENT ON TABLE  contract_succession                 IS '계약 승계 — 유지수수료 귀속 설계사 전환 이력';
COMMENT ON COLUMN contract_succession.contract_id     IS '대상 보험계약 FK';
COMMENT ON COLUMN contract_succession.from_agent_id   IS '전임 설계사 (관두거나 이관한 설계사)';
COMMENT ON COLUMN contract_succession.to_agent_id     IS '후임 설계사 (유지수수료를 이어받는 설계사)';
COMMENT ON COLUMN contract_succession.succession_date IS '승계 효력일 (이 날짜의 YYYYMM 이상 정산월부터 후임 귀속)';
COMMENT ON COLUMN contract_succession.reason          IS 'TERMINATION=해촉 / TRANSFER=이관 / REASSIGN=재배정';
COMMENT ON COLUMN contract_succession.status          IS 'ACTIVE=유효 / CANCELED=취소';

-- 배치 effective 조회용: contract_id + 효력일 역순
CREATE INDEX idx_cs_contract  ON contract_succession(contract_id, status, succession_date DESC);
CREATE INDEX idx_cs_to_agent  ON contract_succession(to_agent_id, status);
CREATE INDEX idx_cs_from_agent ON contract_succession(from_agent_id, status);

-- ============================================================
-- 메뉴 (GRP_PRD 하위, ENDORSE 옆)
-- ============================================================
INSERT INTO menu (parent_menu_id, menu_code, menu_name, menu_type, menu_path, component_path, menu_level, sort_order, is_visible, is_active)
SELECT p.menu_id, x.code, x.name, 'PAGE', x.path, x.cmpt, 2, x.sort, 'Y', 'Y'
FROM menu p
JOIN (VALUES
    ('CONTRACT_SUCCESSION', '계약 승계', '/contracts/successions', 'contracts/ContractSuccessions', 50)
) AS x(code, name, path, cmpt, sort) ON p.menu_code = 'GRP_PRD'
WHERE NOT EXISTS (SELECT 1 FROM menu WHERE menu_code = x.code);

-- 권한 항목 (READ/CREATE/UPDATE/DELETE)
INSERT INTO menu_permission (menu_id, perm_code, perm_name, sort_order)
SELECT m.menu_id, p.code, p.name, p.sort
FROM menu m
CROSS JOIN (VALUES
    ('READ',   '조회', 10),
    ('CREATE', '등록', 20),
    ('UPDATE', '취소', 30),
    ('DELETE', '삭제', 40)
) AS p(code, name, sort)
WHERE m.menu_code = 'CONTRACT_SUCCESSION'
ON CONFLICT (menu_id, perm_code) DO NOTHING;

-- 역할 부여: SUPER_ADMIN/ADMIN 전체
INSERT INTO role_menu_permission (role_id, menu_id, perm_code, is_granted)
SELECT r.role_id, mp.menu_id, mp.perm_code, 'Y'
FROM role r
CROSS JOIN menu_permission mp
JOIN menu m ON m.menu_id = mp.menu_id
WHERE r.role_code IN ('SUPER_ADMIN', 'ADMIN')
  AND m.menu_code = 'CONTRACT_SUCCESSION'
ON CONFLICT (role_id, menu_id, perm_code) DO NOTHING;

-- MANAGER: READ 만
INSERT INTO role_menu_permission (role_id, menu_id, perm_code, is_granted)
SELECT r.role_id, mp.menu_id, mp.perm_code, 'Y'
FROM role r
CROSS JOIN menu_permission mp
JOIN menu m ON m.menu_id = mp.menu_id
WHERE r.role_code = 'MANAGER'
  AND mp.perm_code = 'READ'
  AND m.menu_code = 'CONTRACT_SUCCESSION'
ON CONFLICT (role_id, menu_id, perm_code) DO NOTHING;

-- ============================================================
-- 공통코드
-- ============================================================
INSERT INTO common_code_group (group_code, group_name, description, is_system, sort_order) VALUES
    ('SUCCESSION_REASON', '승계사유', 'TERMINATION/TRANSFER/REASSIGN', 'Y', 470),
    ('SUCCESSION_STATUS', '승계상태', 'ACTIVE/CANCELED',               'Y', 480)
ON CONFLICT (group_code) DO NOTHING;

INSERT INTO common_code (group_code, code, code_name, sort_order, is_active) VALUES
    ('SUCCESSION_REASON', 'TERMINATION', '해촉',   10, 'Y'),
    ('SUCCESSION_REASON', 'TRANSFER',    '이관',   20, 'Y'),
    ('SUCCESSION_REASON', 'REASSIGN',    '재배정', 30, 'Y'),
    ('SUCCESSION_STATUS', 'ACTIVE',      '유효',   10, 'Y'),
    ('SUCCESSION_STATUS', 'CANCELED',    '취소',   20, 'Y')
ON CONFLICT (group_code, code) DO NOTHING;

-- ============================================================
-- 테스트 데이터 (소량) — 테이블이 비어있고 활성 계약/타 설계사가 존재할 때만 1건
-- ============================================================
INSERT INTO contract_succession (contract_id, from_agent_id, to_agent_id, succession_date, reason, memo, status, created_at)
SELECT c.contract_id, c.agent_id, b.agent_id, DATE '2025-01-01', 'TERMINATION', '테스트 승계 데이터', 'ACTIVE', NOW()
FROM contract c
CROSS JOIN LATERAL (
    SELECT agent_id FROM agent WHERE agent_id <> c.agent_id AND status = 'ACTIVE' ORDER BY agent_id LIMIT 1
) b
WHERE c.status = 'ACTIVE'
  AND NOT EXISTS (SELECT 1 FROM contract_succession)
ORDER BY c.contract_id
LIMIT 1;
