SSP OSCAL Alignment — Phase 1 Implementation Plan

cd ~/Projects/Jedicogy/module/jedi-python-package/jedi-oscal
git status -sb
git log --oneline -3

# 改成
jedi-oscal = { path = "/Users/chouraymond/Projects/Jedicogy/module/jedi-python-package/jedi-oscal", develop = true }

cd /Users/chouraymond/Projects/Billows/Audit-Manager/compliance-manager-be
poetry update jedi-oscal

poetry run python -c "from jedi_oscal.domain.entity.base.oscal_party_entity import PartyEntity; print(PartyEntity.__module__)"

git status
# Expected: pyproject.toml 顯示 modified，但不 stage / 不 commit
# (Task 14 收尾時改回 pin Nexus 版本才 commit)

cat ~/Projects/Jedicogy/module/jedi-python-package/jedi-oscal/jedi_oscal/domain/entity/base/oscal_party_entity.py
cat ~/Projects/Jedicogy/module/jedi-python-package/jedi-oscal/jedi_oscal/domain/entity/base/oscal_party_query_entity.py
cat ~/Projects/Jedicogy/module/jedi-python-package/jedi-oscal/jedi_oscal/infra/model/base/oscal_party.py
cat ~/Projects/Jedicogy/module/jedi-python-package/jedi-oscal/jedi_oscal/infra/mapper/base/party_mapper.py
cat ~/Projects/Jedicogy/module/jedi-python-package/jedi-oscal/jedi_oscal/infra/repository/base/party_repo_impl.py
cat ~/Projects/Jedicogy/module/jedi-python-package/jedi-oscal/jedi_oscal/domain/services/base/party_domain_service.py

import uuid
from jedi_oscal.domain.entity.base.oscal_leveraged_authorization_entity import (
    LeveragedAuthorizationEntity,
)


def test_leveraged_authorization_entity_defaults():
    entity = LeveragedAuthorizationEntity(
        uid="uid-1",
        ssp_id=1,
        title="Crowdstrike",
        tenant_id=102,
    )
    assert entity.uid == "uid-1"
    assert entity.ssp_id == 1
    assert entity.title == "Crowdstrike"
    assert entity.tenant_id == 102
    assert entity.party_uuid is None
    assert entity.date_authorized is None
    assert entity.props is None
    assert entity.remarks is None
    assert entity.is_active is True


def test_leveraged_authorization_entity_with_full_payload():
    entity = LeveragedAuthorizationEntity(
        uid="uid-2",
        ssp_id=2,
        title="AWS GovCloud",
        party_uuid="party-uid-3",
        date_authorized="2024-03-15",
        props={
            "fedramp_package_id": "FR18078583629",
            "impact_level": "moderate",
            "data_types": "FCI, System Logs",
        },
        remarks="initial signoff",
        tenant_id=102,
        org_unit_id=99,
    )
    assert entity.props["fedramp_package_id"] == "FR18078583629"
    assert entity.props["impact_level"] == "moderate"

cd ~/Projects/Jedicogy/module/jedi-python-package/jedi-oscal
poetry run pytest jedi_oscal/tests/test_oscal_leveraged_authorization_entity.py -v

from typing import Optional


class LeveragedAuthorizationEntity:
    """OSCAL <leveraged-authorization> domain entity.

    Represents a FedRAMP-style ATO (Authorization to Operate) that a downstream
    SSP inherits from. Pairs with a `ComponentEntity` via
    `Component.leveraged_authorization_uid` to express "this component is the
    one that was authorized".
    """

    def __init__(
        self,
        id: Optional[int] = None,
        uid: Optional[str] = None,
        ssp_id: Optional[int] = None,
        title: str = "",
        party_uuid: Optional[str] = None,
        date_authorized: Optional[str] = None,
        props: Optional[dict] = None,
        remarks: Optional[str] = None,
        tenant_id: Optional[int] = None,
        org_unit_id: Optional[int] = None,
        is_active: bool = True,
        created_at=None,
        created_user: Optional[str] = None,
        updated_at=None,
        updated_user: Optional[str] = None,
    ):
        self.id = id
        self.uid = uid
        self.ssp_id = ssp_id
        self.title = title
        self.party_uuid = party_uuid
        self.date_authorized = date_authorized
        self.props = props
        self.remarks = remarks
        self.tenant_id = tenant_id
        self.org_unit_id = org_unit_id
        self.is_active = is_active
        self.created_at = created_at
        self.created_user = created_user
        self.updated_at = updated_at
        self.updated_user = updated_user

poetry run pytest jedi_oscal/tests/test_oscal_leveraged_authorization_entity.py -v

from typing import Optional


class LeveragedAuthorizationQueryEntity:
    """Query DTO for LeveragedAuthorizationRepository — filterable fields."""

    def __init__(
        self,
        id: Optional[int] = None,
        uid: Optional[str] = None,
        ssp_id: Optional[int] = None,
        title: Optional[str] = None,
        party_uuid: Optional[str] = None,
        is_active: Optional[bool] = None,
        tenant_id: Optional[int] = None,
    ):
        self.id = id
        self.uid = uid
        self.ssp_id = ssp_id
        self.title = title
        self.party_uuid = party_uuid
        self.is_active = is_active
        self.tenant_id = tenant_id

git add jedi_oscal/domain/entity/base/oscal_leveraged_authorization_entity.py \
        jedi_oscal/domain/entity/base/oscal_leveraged_authorization_query_entity.py \
        jedi_oscal/tests/test_oscal_leveraged_authorization_entity.py
git commit -m "feat(oscal): add LeveragedAuthorizationEntity + QueryEntity (PR-1 part 1)"

from sqlalchemy import (
    Column, Integer, String, Text, Date, Boolean, DateTime, Index, ForeignKey,
)
from sqlalchemy.dialects.postgresql import JSONB
from jedi_oscal.infra.model.base.base_model import Base  # adjust import per package convention


class OscalLeveragedAuthorizationModel(Base):
    __tablename__ = "ssp_leveraged_authorizations"
    __table_args__ = (
        Index("idx_ssp_leveraged_ssp", "ssp_id", "is_active"),
        {"schema": "oscal"},
    )

    id = Column(Integer, primary_key=True, autoincrement=True)
    uid = Column(String(36), unique=True, nullable=False)
    ssp_id = Column(Integer, ForeignKey("oscal.system_security_plans.id", ondelete="CASCADE"), nullable=False)
    title = Column(String(255), nullable=False)
    party_uuid = Column(String(36), nullable=True)
    date_authorized = Column(Date, nullable=True)
    props = Column(JSONB, nullable=True)
    remarks = Column(Text, nullable=True)
    tenant_id = Column(Integer, nullable=False)
    org_unit_id = Column(Integer, nullable=True)
    is_active = Column(Boolean, nullable=False, default=True)
    created_at = Column(DateTime, nullable=True)
    created_user = Column(String(255), nullable=True)
    updated_at = Column(DateTime, nullable=True)
    updated_user = Column(String(255), nullable=True)

git add jedi_oscal/infra/model/base/oscal_leveraged_authorization.py
git commit -m "feat(oscal): add OscalLeveragedAuthorizationModel ORM (PR-1 part 2)"

cat ~/Projects/Jedicogy/module/jedi-python-package/jedi-oscal/jedi_oscal/infra/mapper/base/party_mapper.py

from jedi_oscal.domain.entity.base.oscal_leveraged_authorization_entity import (
    LeveragedAuthorizationEntity,
)
from jedi_oscal.infra.model.base.oscal_leveraged_authorization import (
    OscalLeveragedAuthorizationModel,
)


class LeveragedAuthorizationMapper:
    @staticmethod
    def to_entity(model: OscalLeveragedAuthorizationModel) -> LeveragedAuthorizationEntity:
        if model is None:
            return None
        return LeveragedAuthorizationEntity(
            id=model.id,
            uid=model.uid,
            ssp_id=model.ssp_id,
            title=model.title,
            party_uuid=model.party_uuid,
            date_authorized=model.date_authorized.isoformat() if model.date_authorized else None,
            props=model.props,
            remarks=model.remarks,
            tenant_id=model.tenant_id,
            org_unit_id=model.org_unit_id,
            is_active=model.is_active,
            created_at=model.created_at,
            created_user=model.created_user,
            updated_at=model.updated_at,
            updated_user=model.updated_user,
        )

    @staticmethod
    def to_model(entity: LeveragedAuthorizationEntity) -> OscalLeveragedAuthorizationModel:
        return OscalLeveragedAuthorizationModel(
            id=entity.id,
            uid=entity.uid,
            ssp_id=entity.ssp_id,
            title=entity.title,
            party_uuid=entity.party_uuid,
            date_authorized=entity.date_authorized,
            props=entity.props,
            remarks=entity.remarks,
            tenant_id=entity.tenant_id,
            org_unit_id=entity.org_unit_id,
            is_active=entity.is_active,
        )

git add jedi_oscal/infra/mapper/base/leveraged_authorization_mapper.py
git commit -m "feat(oscal): add LeveragedAuthorizationMapper (PR-1 part 3)"

from abc import ABC, abstractmethod
from typing import List, Optional
from jedi_oscal.domain.entity.base.oscal_leveraged_authorization_entity import (
    LeveragedAuthorizationEntity,
)
from jedi_oscal.domain.entity.base.oscal_leveraged_authorization_query_entity import (
    LeveragedAuthorizationQueryEntity,
)


class ILeveragedAuthorizationRepository(ABC):
    @abstractmethod
    def get_one(self, query: LeveragedAuthorizationQueryEntity) -> Optional[LeveragedAuthorizationEntity]:
        ...

    @abstractmethod
    def get_list(self, query: LeveragedAuthorizationQueryEntity) -> List[LeveragedAuthorizationEntity]:
        ...

    @abstractmethod
    def add(self, entity: LeveragedAuthorizationEntity) -> LeveragedAuthorizationEntity:
        ...

    @abstractmethod
    def update(self, entity: LeveragedAuthorizationEntity, locale: Optional[str] = None) -> LeveragedAuthorizationEntity:
        ...

    @abstractmethod
    def deactivate(self, uid: str) -> None:
        ...

from jedi_common.session.database.repository.base_repository_impl import BaseRepositoryImpl
from jedi_oscal.domain.entity.base.oscal_leveraged_authorization_entity import (
    LeveragedAuthorizationEntity,
)
from jedi_oscal.domain.entity.base.oscal_leveraged_authorization_query_entity import (
    LeveragedAuthorizationQueryEntity,
)
from jedi_oscal.domain.repository.base.leveraged_authorization import (
    ILeveragedAuthorizationRepository,
)
from jedi_oscal.infra.model.base.oscal_leveraged_authorization import (
    OscalLeveragedAuthorizationModel,
)
from jedi_oscal.infra.mapper.base.leveraged_authorization_mapper import (
    LeveragedAuthorizationMapper,
)


class LeveragedAuthorizationRepoImpl(
    ILeveragedAuthorizationRepository,
    BaseRepositoryImpl[
        LeveragedAuthorizationEntity,
        LeveragedAuthorizationQueryEntity,
        OscalLeveragedAuthorizationModel,
        LeveragedAuthorizationMapper,
    ],
):
    def __init__(self):
        super().__init__(
            mapper=LeveragedAuthorizationMapper,
            model=OscalLeveragedAuthorizationModel,
        )

git add jedi_oscal/domain/repository/base/leveraged_authorization.py \
        jedi_oscal/infra/repository/base/leveraged_authorization_repo_impl.py
git commit -m "feat(oscal): add LeveragedAuthorization repo interface + impl (PR-1 part 4)"

from typing import List, Optional
from jedi_oscal.domain.entity.base.oscal_leveraged_authorization_entity import (
    LeveragedAuthorizationEntity,
)
from jedi_oscal.domain.entity.base.oscal_leveraged_authorization_query_entity import (
    LeveragedAuthorizationQueryEntity,
)
from jedi_oscal.domain.repository.base.leveraged_authorization import (
    ILeveragedAuthorizationRepository,
)


class LeveragedAuthorizationDomainService:
    def __init__(self, repository: ILeveragedAuthorizationRepository):
        self._repo = repository

    def get_one(self, query: LeveragedAuthorizationQueryEntity) -> Optional[LeveragedAuthorizationEntity]:
        return self._repo.get_one(query)

    def get_list(self, query: LeveragedAuthorizationQueryEntity) -> List[LeveragedAuthorizationEntity]:
        return self._repo.get_list(query)

    def add(self, entity: LeveragedAuthorizationEntity) -> LeveragedAuthorizationEntity:
        return self._repo.add(entity)

    def update(self, entity: LeveragedAuthorizationEntity, locale: Optional[str] = None) -> LeveragedAuthorizationEntity:
        return self._repo.update(entity, locale=locale)

    def deactivate(self, uid: str) -> None:
        self._repo.deactivate(uid)

git add jedi_oscal/domain/services/base/leveraged_authorization_domain_service.py
git commit -m "feat(oscal): add LeveragedAuthorizationDomainService (PR-1 part 5)"

cd ~/Projects/Jedicogy/module/jedi-python-package/jedi-oscal
poetry run pytest -v

cd /Users/chouraymond/Projects/Billows/Audit-Manager/compliance-manager-be
poetry run python -c "
from jedi_oscal.domain.entity.base.oscal_leveraged_authorization_entity import LeveragedAuthorizationEntity
from jedi_oscal.domain.services.base.leveraged_authorization_domain_service import LeveragedAuthorizationDomainService
print('OK')
"

from typing import Optional


COMPONENT_TYPE_ENUM = {
    "this-system", "system", "interconnection", "software", "hardware",
    "service", "policy", "physical", "process-procedure", "plan",
    "guidance", "standard", "validation", "network", "other",
}

COMPONENT_STATUS_ENUM = {
    "operational", "under-development", "under-major-modification",
    "disposition", "other",
}


class ComponentEntity:
    """OSCAL <component> domain entity.

    Component type follows OSCAL spec 14 enum + "other" (allow-other=yes).
    For framework-specific values that don't fit the 14 enum (e.g. CMMC 'api' /
    'cli'), normalize component_type='service' and store original in
    props.cmmc:category (see design.md §2.1).

    leveraged_authorization_uid is set when this component IS the resource that
    was authorized (FedRAMP-style ATO inheritance).
    """

    def __init__(
        self,
        id: Optional[int] = None,
        uid: Optional[str] = None,
        ssp_id: Optional[int] = None,
        component_type: str = "",
        title: str = "",
        description: Optional[str] = None,
        purpose: Optional[str] = None,
        status: Optional[str] = None,
        leveraged_authorization_uid: Optional[str] = None,
        props: Optional[dict] = None,
        tenant_id: Optional[int] = None,
        org_unit_id: Optional[int] = None,
        is_active: bool = True,
        created_at=None,
        created_user: Optional[str] = None,
        updated_at=None,
        updated_user: Optional[str] = None,
    ):
        self.id = id
        self.uid = uid
        self.ssp_id = ssp_id
        self.component_type = component_type
        self.title = title
        self.description = description
        self.purpose = purpose
        self.status = status
        self.leveraged_authorization_uid = leveraged_authorization_uid
        self.props = props
        self.tenant_id = tenant_id
        self.org_unit_id = org_unit_id
        self.is_active = is_active
        self.created_at = created_at
        self.created_user = created_user
        self.updated_at = updated_at
        self.updated_user = updated_user

from jedi_oscal.domain.entity.base.oscal_component_entity import (
    ComponentEntity, COMPONENT_TYPE_ENUM, COMPONENT_STATUS_ENUM,
)


def test_component_type_enum_includes_oscal_14_plus_other():
    expected_oscal_14 = {
        "this-system", "system", "interconnection", "software", "hardware",
        "service", "policy", "physical", "process-procedure", "plan",
        "guidance", "standard", "validation", "network",
    }
    assert expected_oscal_14.issubset(COMPONENT_TYPE_ENUM)
    assert "other" in COMPONENT_TYPE_ENUM


def test_component_status_enum_oscal_5():
    expected = {
        "operational", "under-development", "under-major-modification",
        "disposition", "other",
    }
    assert COMPONENT_STATUS_ENUM == expected


def test_component_entity_minimal():
    entity = ComponentEntity(
        uid="comp-uid-1",
        ssp_id=1,
        component_type="service",
        title="AWS S3",
        tenant_id=102,
    )
    assert entity.component_type == "service"
    assert entity.leveraged_authorization_uid is None


def test_component_entity_with_leveraged_auth_link():
    entity = ComponentEntity(
        uid="comp-uid-2",
        ssp_id=1,
        component_type="service",
        title="Crowdstrike",
        leveraged_authorization_uid="la-uid-1",
        props={"cmmc:category": "api"},
        tenant_id=102,
    )
    assert entity.leveraged_authorization_uid == "la-uid-1"
    assert entity.props["cmmc:category"] == "api"

poetry run pytest jedi_oscal/tests/test_oscal_component_entity.py -v
# Expected: PASS

git add jedi_oscal/domain/entity/base/oscal_component_entity.py \
        jedi_oscal/domain/entity/base/oscal_component_query_entity.py \
        jedi_oscal/tests/test_oscal_component_entity.py
git commit -m "feat(oscal): add ComponentEntity + QueryEntity (PR-2 part 1)"

from typing import Optional, List


class InventoryItemEntity:
    """OSCAL <inventory-item> domain entity.

    Represents a specific instance (e.g. server 'web-01.acme.local',
    laptop 'asset-tag-12345'). Connects to one or more ComponentEntity via
    <implemented-component component-uuid=...> M2M (stored as
    implemented_component_uids list).
    """

    def __init__(
        self,
        id: Optional[int] = None,
        uid: Optional[str] = None,
        ssp_id: Optional[int] = None,
        description: str = "",
        props: Optional[dict] = None,
        implemented_component_uids: Optional[List[str]] = None,
        tenant_id: Optional[int] = None,
        org_unit_id: Optional[int] = None,
        is_active: bool = True,
        created_at=None,
        created_user: Optional[str] = None,
        updated_at=None,
        updated_user: Optional[str] = None,
    ):
        self.id = id
        self.uid = uid
        self.ssp_id = ssp_id
        self.description = description
        self.props = props
        self.implemented_component_uids = implemented_component_uids or []
        self.tenant_id = tenant_id
        self.org_unit_id = org_unit_id
        self.is_active = is_active
        self.created_at = created_at
        self.created_user = created_user
        self.updated_at = updated_at
        self.updated_user = updated_user

from sqlalchemy import Column, Integer, ForeignKey
from jedi_oscal.infra.model.base.base_model import Base


class OscalInventoryImplementedComponentModel(Base):
    __tablename__ = "ssp_inventory_implemented_components"
    __table_args__ = ({"schema": "oscal"},)

    inventory_item_id = Column(
        Integer,
        ForeignKey("oscal.ssp_inventory_items.id", ondelete="CASCADE"),
        primary_key=True,
    )
    component_id = Column(
        Integer,
        ForeignKey("oscal.ssp_components.id", ondelete="CASCADE"),
        primary_key=True,
    )

class InventoryItemMapper:
    @staticmethod
    def to_entity(model: OscalInventoryItemModel, implemented_component_uids: List[str] = None) -> InventoryItemEntity:
        if model is None:
            return None
        return InventoryItemEntity(
            id=model.id,
            uid=model.uid,
            ssp_id=model.ssp_id,
            description=model.description,
            props=model.props,
            implemented_component_uids=implemented_component_uids or [],
            tenant_id=model.tenant_id,
            org_unit_id=model.org_unit_id,
            is_active=model.is_active,
            # audit fields ...
        )

    @staticmethod
    def to_model(entity: InventoryItemEntity) -> OscalInventoryItemModel:
        return OscalInventoryItemModel(
            id=entity.id,
            uid=entity.uid,
            ssp_id=entity.ssp_id,
            description=entity.description,
            props=entity.props,
            tenant_id=entity.tenant_id,
            org_unit_id=entity.org_unit_id,
            is_active=entity.is_active,
        )

from jedi_common.session.database.repository.base_repository_impl import BaseRepositoryImpl
# ... imports

class InventoryItemRepoImpl(IInventoryItemRepository, BaseRepositoryImpl[...]):
    def __init__(self):
        super().__init__(
            mapper=InventoryItemMapper,
            model=OscalInventoryItemModel,
        )

    def add(self, entity: InventoryItemEntity) -> InventoryItemEntity:
        # 先寫主表
        saved = super().add(entity)
        # 寫 M2M join rows
        if entity.implemented_component_uids:
            self._write_join_rows(saved.id, entity.implemented_component_uids)
        return saved

    def _write_join_rows(self, inventory_item_id: int, component_uids: List[str]):
        # query components.id by uids，寫 join rows
        from jedi_oscal.infra.model.base.oscal_component import OscalComponentModel
        comp_ids = self.session.query(OscalComponentModel.id).filter(
            OscalComponentModel.uid.in_(component_uids)
        ).all()
        for (comp_id,) in comp_ids:
            join_row = OscalInventoryImplementedComponentModel(
                inventory_item_id=inventory_item_id,
                component_id=comp_id,
            )
            self.session.add(join_row)
        self.session.flush()

cat /Users/chouraymond/Projects/Billows/Audit-Manager/compliance-manager-be/domain/oscal/parser/ssp_intermediate.py | head -200

from datetime import date
from domain.oscal.parser.ssp_intermediate import (
    ParsedComponent, ParsedLeveragedAuthorization, ParsedInventoryItem,
    ParsedExcelEntityBundle, MatchMethod,
)


def test_parsed_component_defaults():
    pc = ParsedComponent(title="Crowdstrike", component_type="service")
    assert pc.title == "Crowdstrike"
    assert pc.component_type == "service"
    assert pc.description is None
    assert pc.leveraged_authorization_ref is None
    assert pc.match_method == MatchMethod.UNMATCHED


def test_parsed_leveraged_authorization_full_payload():
    pla = ParsedLeveragedAuthorization(
        title="Crowdstrike",
        provider="Crowdstrike Inc.",
        date_authorized=date(2024, 3, 15),
        fedramp_package_id="FR18078583629",
        impact_level="moderate",
    )
    assert pla.fedramp_package_id == "FR18078583629"
    assert pla.impact_level == "moderate"


def test_parsed_inventory_item_implemented_refs():
    pi = ParsedInventoryItem(
        description="web-01.acme.local",
        ipv4_address="10.0.1.5",
        implemented_component_refs=["nginx", "ubuntu"],
    )
    assert len(pi.implemented_component_refs) == 2


def test_bundle_has_3_new_lists_and_no_deprecated():
    bundle = ParsedExcelEntityBundle()
    assert bundle.parsed_components == []
    assert bundle.parsed_leveraged_authorizations == []
    assert bundle.parsed_inventory_items == []
    # 舊三類 dataclass 不在 bundle dataclass attributes
    assert not hasattr(bundle, "parsed_devices")
    assert not hasattr(bundle, "parsed_info_systems")
    assert not hasattr(bundle, "parsed_leveraged")

poetry run pytest tests/test_ssp_intermediate_v2_bundle.py -v

poetry run pytest tests/test_ssp_intermediate_v2_bundle.py -v

poetry run pytest tests/test_cmmc_ssp_adapter.py tests/test_docx_parser_core_metadata_block.py -v

git add domain/oscal/parser/ssp_intermediate.py tests/test_ssp_intermediate_v2_bundle.py
git commit -m "feat(ssp-oscal-alignment): add ParsedComponent/LeveragedAuthorization/InventoryItem dataclass + restructure Bundle"

leveraged_authorization_repo = providers.Singleton(LeveragedAuthorizationRepoImpl)
component_repo               = providers.Singleton(ComponentRepoImpl)
inventory_item_repo          = providers.Singleton(InventoryItemRepoImpl)

leveraged_authorization_domain_service = providers.Factory(
    LeveragedAuthorizationDomainService,
    leveraged_authorization_repo=leveraged_authorization_repo,
)
component_domain_service = providers.Factory(
    ComponentDomainService,
    component_repo=component_repo,
)
inventory_item_domain_service = providers.Factory(
    InventoryItemDomainService,
    inventory_item_repo=inventory_item_repo,
)

"""E2E test for Phase 1 migration cutover.

Prerequisites:
- Task 7-9 已跑完（新表存在 + 資料 migrated）
- DI 已 wire 3 個新 domain service (Task 6)

Goal: 確認 Excel import → GET SSP detail 能完整經過新表 path 取得資料。
"""

import pytest
from unittest.mock import MagicMock, patch


@pytest.fixture(autouse=True)
def patch_logger():
    # Per CLAUDE.md feedback「寫 app service test 必加 logger patch」
    with patch("app.oscal.service.ssp_excel_import_app_service.logger", MagicMock()):
        yield


def test_excel_import_then_get_ssp_writes_to_new_tables(client, headers):
    # 1. Upload Excel template containing leveraged + components + inventory data
    # 2. POST /ssp-excel-imports/{parse_uid}/confirm
    # 3. GET /ssp/{ssp_uid} — verify response 含 leveraged/components/inventory data
    # 4. Direct DB query: confirm rows in new tables (ssp_components / ssp_leveraged / inventory)
    pass  # Full impl 視 Phase 2 confirm path / GET SSP detail API 進度


def test_docx_import_with_customer_fixture_writes_new_tables(client, headers):
    # Use tests/data/oscal/customer_sample_scrubbed.docx
    # POST /ssp-docx-imports → confirm
    # Direct DB verify new tables populated
    pass


def test_migrated_ssp_get_returns_full_data(client, headers):
    # 已存在的 SSP（migration 前就有的）— GET 後 leveraged/components 從新表讀
    pass

PGPASSWORD='jedi@123!' psql -h 192.168.50.188 -p 25432 -U cmmgr -d guidant_ai_dev -c "
SET app.is_super_admin='t';

-- 全表 spot check
SELECT 'leveraged_authorizations' AS tbl, count(*) FROM oscal.ssp_leveraged_authorizations
UNION ALL
SELECT 'components', count(*) FROM oscal.ssp_components
UNION ALL
SELECT 'inventory_items', count(*) FROM oscal.ssp_inventory_items
UNION ALL
SELECT 'inventory_implemented_components', count(*) FROM oscal.ssp_inventory_implemented_components
UNION ALL
SELECT 'old_items_active', count(*) FROM oscal.ssp_system_implementation_items WHERE is_active;

-- Leveraged FK link 驗證
SELECT c.title, c.component_type, la.title AS leveraged_title
FROM oscal.ssp_components c
LEFT JOIN oscal.ssp_leveraged_authorizations la ON la.uid = c.leveraged_authorization_uid
WHERE c.leveraged_authorization_uid IS NOT NULL LIMIT 5;
"

poetry run pytest tests/test_docx_section_extractors.py \
                  tests/test_docx_section_extractors_customer.py \
                  tests/test_cmmc_ssp_adapter.py \
                  tests/test_ssp_docx_diff_service.py \
                  tests/test_ssp_docx_import_app_service.py -v

cd ~/Projects/Jedicogy/module/jedi-python-package/jedi-oscal
grep -rn "SspSystemImplementationItemEntity\|ssp_system_implementation_item" jedi_oscal/ --include="*.py"

cd /Users/chouraymond/Projects/Billows/Audit-Manager/compliance-manager-be
grep -rn "SspSystemImplementationItemEntity\|ssp_system_implementation_item" \
  --include="*.py" --include="*.sql" \
  --exclude-dir=__pycache__ --exclude-dir=.venv

grep -rn "SspSystemImplementationItemEntity\|ssp_system_implementation_item" \
  ~/Projects/Jedicogy/module/jedi-python-package/ --include="*.py"

cd ~/Projects/Jedicogy/module/jedi-python-package/jedi-oscal && poetry run pytest -v
cd /Users/chouraymond/Projects/Billows/Audit-Manager/compliance-manager-be && poetry run pytest tests/ -v

cd ~/Projects/Jedicogy/module/jedi-python-package/jedi-oscal
git rm jedi_oscal/domain/entity/base/ssp_system_implementation_item_entity.py \
       jedi_oscal/domain/entity/base/ssp_system_implementation_item_query_entity.py \
       jedi_oscal/domain/repository/base/ssp_system_implementation_item.py \
       jedi_oscal/domain/services/base/ssp_system_implementation_item_domain_service.py \
       jedi_oscal/infra/model/base/ssp_system_implementation_item.py \
       jedi_oscal/infra/mapper/base/ssp_system_implementation_item_mapper.py \
       jedi_oscal/infra/repository/base/ssp_system_implementation_item_repo_impl.py
# (確切路徑用 Step 11.1.1 結果調整)

cd ~/Projects/Jedicogy/module/jedi-python-package/jedi-oscal && poetry run pytest -v
cd /Users/chouraymond/Projects/Billows/Audit-Manager/compliance-manager-be && poetry run pytest tests/ -v

# 套件側
cd ~/Projects/Jedicogy/module/jedi-python-package/jedi-oscal
git commit -m "feat(oscal): remove SspSystemImplementationItemEntity + repo/service/model/mapper (PR-4 cleanup)"

# 主專案
cd /Users/chouraymond/Projects/Billows/Audit-Manager/compliance-manager-be
git add <changed files>
git commit -m "refactor(ssp-oscal-alignment): replace SspSystemImplementationItemEntity callers with new entities"

-- Date: 2026-05-XX
-- 4. DROP oscal.ssp_system_implementation_items + dependent objects (2026-05-XX)
-- Prerequisite: Task 11 caller cleanup 完成、e2e 驗證通過
-- Run as: cmmgr
DROP TABLE IF EXISTS oscal.ssp_system_implementation_items CASCADE;

PGPASSWORD='jedi@123!' psql -h 192.168.50.188 -p 25432 -U cmmgr -d guidant_ai_dev \
  -f scripts/sql/ssp_oscal_alignment_drop_old.sql

PGPASSWORD='jedi@123!' psql -h 192.168.50.188 -p 25432 -U cmmgr -d guidant_ai_dev -c "
SET app.is_super_admin='t';
SELECT tablename FROM pg_tables WHERE schemaname='oscal'
  AND tablename = 'ssp_system_implementation_items';"

git add scripts/sql/ssp_oscal_alignment_drop_old.sql
git commit -m "feat(ssp-oscal-alignment): SQL migration Step 4 — DROP old ssp_system_implementation_items"

cd ~/Projects/Jedicogy/module/jedi-python-package/jedi-oscal
# 改 pyproject.toml version: 0.0.18 → 0.1.0 (minor bump — 加 3 個 entity 算 functional addition)

git add pyproject.toml
git commit -m "chore(jedi-oscal): bump 0.0.18 → 0.1.0 — add Component / LeveragedAuth / InventoryItem"
git push origin feature/ssp-oscal-alignment
# user 自行在 GitLab/GitHub 開 PR 4 個 (PR-1 ~ PR-4) merge 到 main

cd /Users/chouraymond/Projects/Billows/Audit-Manager/compliance-manager-be
# pyproject.toml jedi-oscal 改回 Nexus pin
# jedi-oscal = "0.1.0"

poetry update jedi-oscal

poetry run pytest tests/ -v

git add pyproject.toml poetry.lock
git commit -m "chore(ssp-oscal-alignment): pin jedi-oscal 0.1.0 from Nexus (cutover from path dependency)"

---
type: feat
breaking: true
modules: [oscal, jedi-oscal]
commit: <Task 1-13 主 commit hashes>
---

# SSP OSCAL Alignment — Phase 1 Foundation

[詳細內容: 改動範圍 / API 變更 / migration 路徑 / 測試結果]

poetry run python scripts/extract_claude_sessions.py \
    --date 2026-05-XX --topic ssp-oscal-alignment-phase1 --auto