ANTOOZA-BACKEND/Infrastructure/Persistence/Migrations/20260707123951_AddSeibroDeriv.cs

using System;
using Microsoft.EntityFrameworkCore.Migrations;

#nullable disable

namespace Infrastructure.Migrations.AppDb
{
    /// <inheritdoc />
    public partial class AddSeibroDeriv : Migration
    {
        /// <inheritdoc />
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.CreateTable(
                name: "DerivAsset",
                columns: table => new
                {
                    ID = table.Column<int>(type: "int", nullable: false)
                        .Annotation("SqlServer:Identity", "1, 1"),
                    Isin = table.Column<string>(type: "nvarchar(12)", maxLength: 12, nullable: false, comment: "종목코드 (ISIN, 스탬핑)"),
                    AssetSeq = table.Column<int>(type: "int", nullable: false, comment: "자산일련번호 (ASSET_SEQ)"),
                    StndBassetCd = table.Column<string>(type: "nvarchar(12)", maxLength: 12, nullable: true, comment: "표준기초자산코드 (STND_BASSET_CD) — 원문(KSD31*·KR7* 등)"),
                    StndBassetNm = table.Column<string>(type: "nvarchar(200)", maxLength: 200, nullable: true, comment: "표준기초자산명 (STND_BASSET_NM)"),
                    XrcStdprc = table.Column<decimal>(type: "decimal(28,10)", nullable: true, comment: "행사기준가 (XRC_STDPRC)"),
                    XrcStopLolmtRatio = table.Column<decimal>(type: "decimal(8,3)", nullable: true, comment: "행사정지하한비율 (XRC_STOP_LOLMT_RATIO)"),
                    XrcStopLolmtPrice = table.Column<decimal>(type: "decimal(28,10)", nullable: true, comment: "행사정지하한가격 (XRC_STOP_LOLMT_PRICE)"),
                    XrcStopUplmtRatio = table.Column<decimal>(type: "decimal(8,3)", nullable: true, comment: "행사정지상한비율 (XRC_STOP_UPLMT_RATIO)"),
                    XrcStopUplmtPrice = table.Column<decimal>(type: "decimal(28,10)", nullable: true, comment: "행사정지상한가격 (XRC_STOP_UPLMT_PRICE)"),
                    BassetFirstValatTpcd = table.Column<string>(type: "nvarchar(1)", maxLength: 1, nullable: true, comment: "기초자산최초평가구분코드 (BASSET_FIRST_VALAT_TPCD)"),
                    BassetLastValatTpcd = table.Column<string>(type: "nvarchar(1)", maxLength: 1, nullable: true, comment: "기초자산최종평가구분코드 (BASSET_LAST_VALAT_TPCD)"),
                    FirstStdprcDecsnBeginDt = table.Column<DateOnly>(type: "date", nullable: true, comment: "최초기준가결정시작일자 (FIRST_STDPRC_DECSN_BEGIN_DT)"),
                    FirstStdprcDecsnExpryDt = table.Column<DateOnly>(type: "date", nullable: true, comment: "최초기준가결정종료일자 (FIRST_STDPRC_DECSN_EXPRY_DT)"),
                    LastStdprcDecsnBeginDt = table.Column<DateOnly>(type: "date", nullable: true, comment: "최종기준가결정시작일자 (LAST_STDPRC_DECSN_BEGIN_DT)"),
                    LastStdprcDecsnExpryDt = table.Column<DateOnly>(type: "date", nullable: true, comment: "최종기준가결정종료일자 (LAST_STDPRC_DECSN_EXPRY_DT)"),
                    RvltSeverTpcd = table.Column<string>(type: "nvarchar(1)", maxLength: 1, nullable: true, comment: "절상절사구분코드 (RVLT_SEVER_TPCD)"),
                    UpdatedAt = table.Column<DateTime>(type: "datetime2", nullable: false)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_DerivAsset", x => x.ID);
                },
                comment: "SEIBro 파생결합증권 기초자산 정보 (getAssetInfo) — 기초자산명·행사기준가·평가일. StndBassetCd 는 반환 원문(KSD31* 체계 가능)");

            migrationBuilder.CreateTable(
                name: "DerivAssetExercise",
                columns: table => new
                {
                    ID = table.Column<int>(type: "int", nullable: false)
                        .Annotation("SqlServer:Identity", "1, 1"),
                    Isin = table.Column<string>(type: "nvarchar(12)", maxLength: 12, nullable: false, comment: "종목코드 (ISIN, 스탬핑)"),
                    AssetSeq = table.Column<int>(type: "int", nullable: false, comment: "자산일련번호 (ASSET_SEQ)"),
                    XrcStdSeq = table.Column<int>(type: "int", nullable: false, comment: "행사기준일련번호 (XRC_STD_SEQ)"),
                    XrcStdRatio = table.Column<decimal>(type: "decimal(8,3)", nullable: true, comment: "행사기준비율 % (XRC_STD_RATIO)"),
                    XrcPrice = table.Column<decimal>(type: "decimal(28,10)", nullable: true, comment: "행사가격 (XRC_PRICE)"),
                    UpdatedAt = table.Column<DateTime>(type: "datetime2", nullable: false)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_DerivAssetExercise", x => x.ID);
                },
                comment: "SEIBro 파생결합증권 기초자산 행사정보 (getAssetXrcInfo) — 행사기준비율·행사가격. AssetSeq 로 DerivAsset 연계");

            migrationBuilder.CreateTable(
                name: "DerivIssuance",
                columns: table => new
                {
                    ID = table.Column<int>(type: "int", nullable: false)
                        .Annotation("SqlServer:Identity", "1, 1"),
                    Isin = table.Column<string>(type: "nvarchar(12)", maxLength: 12, nullable: false, comment: "종목코드 (ISIN)"),
                    KorSecnNm = table.Column<string>(type: "nvarchar(100)", maxLength: 100, nullable: true, comment: "한글종목명 (KOR_SECN_NM)"),
                    IssucoCustno = table.Column<int>(type: "int", nullable: true, comment: "발행회사고객번호 (ISSUCO_CUSTNO)"),
                    SecnKacd = table.Column<string>(type: "nvarchar(4)", maxLength: 4, nullable: false, comment: "종목종류코드 (SECN_KACD, 스탬핑) — 4101ELS 4102ELB 4301DLS 4302DLB"),
                    IssuDt = table.Column<DateOnly>(type: "date", nullable: false, comment: "발행일자 (ISSU_DT, 스탬핑)"),
                    UpdatedAt = table.Column<DateTime>(type: "datetime2", nullable: false)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_DerivIssuance", x => x.ID);
                },
                comment: "SEIBro 파생결합증권 발행내역 (getDerivCombiIssuInfo) — ELS/ELB/DLS/DLB. DerivMaster rolling 발견 소스");

            migrationBuilder.CreateTable(
                name: "DerivMaster",
                columns: table => new
                {
                    Isin = table.Column<string>(type: "nvarchar(12)", maxLength: 12, nullable: false, comment: "종목코드 (ISIN)"),
                    KorSecnNm = table.Column<string>(type: "nvarchar(100)", maxLength: 100, nullable: true, comment: "한글종목명 (KOR_SECN_NM)"),
                    IssucoCustno = table.Column<int>(type: "int", nullable: true, comment: "발행회사고객번호 (ISSUCO_CUSTNO)"),
                    SecnKanm = table.Column<string>(type: "nvarchar(20)", maxLength: 20, nullable: true, comment: "종목구분명 (SECN_KANM) — ELS/ELB/ELW/DLS/DLB/ETN"),
                    IssuCurCd = table.Column<string>(type: "nvarchar(3)", maxLength: 3, nullable: true, comment: "발행통화코드 (ISSU_CUR_CD)"),
                    RecuWhcd = table.Column<string>(type: "nvarchar(2)", maxLength: 2, nullable: true, comment: "모집방법코드 (RECU_WHCD) — 11공모 21사모"),
                    FirstIssuQty = table.Column<decimal>(type: "decimal(20,2)", nullable: true, comment: "최초발행수량 (FIRST_ISSU_QTY)"),
                    IssuRemq = table.Column<decimal>(type: "decimal(20,2)", nullable: true, comment: "발행잔량 (ISSU_REMQ)"),
                    PayinAmtRatio = table.Column<decimal>(type: "decimal(15,10)", nullable: true, comment: "납입금액비율 (PAYIN_AMT_RATIO)"),
                    PayinAmt = table.Column<decimal>(type: "decimal(20,2)", nullable: true, comment: "납입금액 (PAYIN_AMT)"),
                    IssuDt = table.Column<DateOnly>(type: "date", nullable: true, comment: "발행일자 (ISSU_DT)"),
                    ContDt = table.Column<DateOnly>(type: "date", nullable: true, comment: "계약일자 (CONT_DT)"),
                    XpirDt = table.Column<DateOnly>(type: "date", nullable: true, comment: "만기일자 (XPIR_DT)"),
                    MidredPossYn = table.Column<bool>(type: "bit", nullable: true, comment: "중도상환가능여부 (MIDRED_POSS_YN)"),
                    RedDt = table.Column<DateOnly>(type: "date", nullable: true, comment: "상환일자 (RED_DT)"),
                    DerisecExerTpcd = table.Column<string>(type: "nvarchar(1)", maxLength: 1, nullable: true, comment: "파생증권권리행사구분코드 (DERISEC_EXER_TPCD) — 2만기 3조기"),
                    PrcpPrsvTpcd = table.Column<string>(type: "nvarchar(1)", maxLength: 1, nullable: true, comment: "원금보전구분코드 (PRCP_PRSV_TPCD) — 1보장 2부분 3비보장"),
                    PrcpPrsvRate = table.Column<decimal>(type: "decimal(8,3)", nullable: true, comment: "원금보전율 (PRCP_PRSV_RATE)"),
                    ColatSetupTpcd = table.Column<string>(type: "nvarchar(1)", maxLength: 1, nullable: true, comment: "담보설정구분코드 (COLAT_SETUP_TPCD) — 1설정 2미설정"),
                    BassetCnt = table.Column<int>(type: "int", nullable: true, comment: "기초자산 개수 (BASSET_CNT)"),
                    MmPayYn = table.Column<bool>(type: "bit", nullable: true, comment: "월지급여부 (MM_PAY_YN)"),
                    UpdatedAt = table.Column<DateTime>(type: "datetime2", nullable: false)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_DerivMaster", x => x.Isin);
                },
                comment: "SEIBro 파생결합증권 종목 정보 (getDerivCombiIsinInfo) — 원금보전·만기·발행잔량·기초자산수. ELS/DLS 상세 마스터");

            migrationBuilder.CreateTable(
                name: "DerivRedemption",
                columns: table => new
                {
                    ID = table.Column<int>(type: "int", nullable: false)
                        .Annotation("SqlServer:Identity", "1, 1"),
                    Isin = table.Column<string>(type: "nvarchar(12)", maxLength: 12, nullable: false, comment: "종목코드 (ISIN)"),
                    DerisecExerTpcd = table.Column<string>(type: "nvarchar(1)", maxLength: 1, nullable: false, comment: "파생증권권리행사구분코드 (DERISEC_EXER_TPCD) — 2만기 3조기"),
                    RedQty = table.Column<decimal>(type: "decimal(20,2)", nullable: true, comment: "상환수량 (RED_QTY)"),
                    RedDt = table.Column<DateOnly>(type: "date", nullable: false, comment: "상환일자 (RED_DT, 스탬핑)"),
                    SecnKacd = table.Column<string>(type: "nvarchar(4)", maxLength: 4, nullable: false, comment: "종목종류코드 (SECN_KACD, 스탬핑) — 4101ELS 4102ELB 4301DLS 4302DLB"),
                    UpdatedAt = table.Column<DateTime>(type: "datetime2", nullable: false)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_DerivRedemption", x => x.ID);
                },
                comment: "SEIBro 파생결합증권 상환종목 (getRedIsinInfo) — 상환일별 상환종목·수량. RedDt×종류 날짜 스윕");

            migrationBuilder.CreateTable(
                name: "DerivRedemptionCondition",
                columns: table => new
                {
                    ID = table.Column<int>(type: "int", nullable: false)
                        .Annotation("SqlServer:Identity", "1, 1"),
                    Isin = table.Column<string>(type: "nvarchar(12)", maxLength: 12, nullable: false, comment: "종목코드 (ISIN, 스탬핑)"),
                    RedCondiTpcd = table.Column<string>(type: "nvarchar(1)", maxLength: 1, nullable: false, comment: "상환조건구분코드 (RED_CONDI_TPCD) — 1중간평가 2만기"),
                    ValatNtimesSeq = table.Column<int>(type: "int", nullable: false, comment: "평가횟수일련번호 (VALAT_NTIMES_SEQ)"),
                    MidValatPayDt = table.Column<DateOnly>(type: "date", nullable: true, comment: "중간평가지급일자 (MID_VALAT_PAY_DT)"),
                    MidValatBeginDt = table.Column<DateOnly>(type: "date", nullable: true, comment: "중간평가시작일자 (MID_VALAT_BEGIN_DT)"),
                    MidValatExpryDt = table.Column<DateOnly>(type: "date", nullable: true, comment: "중간평가종료일자 (MID_VALAT_EXPRY_DT)"),
                    RedCondiContent = table.Column<string>(type: "nvarchar(1000)", maxLength: 1000, nullable: true, comment: "상환조건내용 (RED_CONDI_CONTENT)"),
                    RedFormulaContent = table.Column<string>(type: "nvarchar(1000)", maxLength: 1000, nullable: true, comment: "상환계산식내용 (RED_FORMULA_CONTENT)"),
                    UpdatedAt = table.Column<DateTime>(type: "datetime2", nullable: false)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_DerivRedemptionCondition", x => x.ID);
                },
                comment: "SEIBro 파생결합증권 상환조건 (getRedCondiInfo) — 중간평가일·상환조건문·계산식(최대 1000자)");

            migrationBuilder.CreateTable(
                name: "ElsUnredeemedScale",
                columns: table => new
                {
                    ID = table.Column<int>(type: "int", nullable: false)
                        .Annotation("SqlServer:Identity", "1, 1"),
                    StdDt = table.Column<DateOnly>(type: "date", nullable: false, comment: "기준일자 (STD_DT, 스탬핑)"),
                    StndBassetCd = table.Column<string>(type: "nvarchar(12)", maxLength: 12, nullable: false, comment: "표준기초자산코드 (STND_BASSET_CD, 스탬핑) — 입력 13종 KSD02* 원문"),
                    SecnTpcd = table.Column<string>(type: "nvarchar(2)", maxLength: 2, nullable: false, comment: "종목구분코드 (SECN_TPCD, 스탬핑) — 41ELS 43DLS"),
                    BassetContent = table.Column<string>(type: "nvarchar(500)", maxLength: 500, nullable: true, comment: "기초자산내용 (BASSET_CONTENT)"),
                    UnredSecncnt = table.Column<int>(type: "int", nullable: true, comment: "미상환종목수 (UNRED_SECNCNT)"),
                    UnredRema = table.Column<decimal>(type: "decimal(20,2)", nullable: true, comment: "미상환잔액 (UNRED_REMA) — 원화 환산 합계"),
                    UpdatedAt = table.Column<DateTime>(type: "datetime2", nullable: false)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_ElsUnredeemedScale", x => x.ID);
                },
                comment: "SEIBro 기초자산별 미상환규모 (getBassetUnredScale) — ELS 지수 하락 리스크 지표. 일별 13기초자산×{41,43}");

            migrationBuilder.CreateIndex(
                name: "IX_DerivAsset_Isin_AssetSeq",
                table: "DerivAsset",
                columns: new[] { "Isin", "AssetSeq" },
                unique: true);

            migrationBuilder.CreateIndex(
                name: "IX_DerivAsset_StndBassetCd",
                table: "DerivAsset",
                column: "StndBassetCd");

            migrationBuilder.CreateIndex(
                name: "IX_DerivAssetExercise_Isin_AssetSeq_XrcStdSeq",
                table: "DerivAssetExercise",
                columns: new[] { "Isin", "AssetSeq", "XrcStdSeq" },
                unique: true);

            migrationBuilder.CreateIndex(
                name: "IX_DerivIssuance_Isin",
                table: "DerivIssuance",
                column: "Isin",
                unique: true);

            migrationBuilder.CreateIndex(
                name: "IX_DerivIssuance_IssuDt",
                table: "DerivIssuance",
                column: "IssuDt");

            migrationBuilder.CreateIndex(
                name: "IX_DerivIssuance_SecnKacd",
                table: "DerivIssuance",
                column: "SecnKacd");

            migrationBuilder.CreateIndex(
                name: "IX_DerivMaster_IssucoCustno",
                table: "DerivMaster",
                column: "IssucoCustno");

            migrationBuilder.CreateIndex(
                name: "IX_DerivMaster_XpirDt",
                table: "DerivMaster",
                column: "XpirDt");

            migrationBuilder.CreateIndex(
                name: "IX_DerivRedemption_Isin_RedDt_DerisecExerTpcd",
                table: "DerivRedemption",
                columns: new[] { "Isin", "RedDt", "DerisecExerTpcd" },
                unique: true);

            migrationBuilder.CreateIndex(
                name: "IX_DerivRedemption_RedDt",
                table: "DerivRedemption",
                column: "RedDt");

            migrationBuilder.CreateIndex(
                name: "IX_DerivRedemptionCondition_Isin_RedCondiTpcd_ValatNtimesSeq",
                table: "DerivRedemptionCondition",
                columns: new[] { "Isin", "RedCondiTpcd", "ValatNtimesSeq" },
                unique: true);

            migrationBuilder.CreateIndex(
                name: "IX_ElsUnredeemedScale_StdDt_StndBassetCd_SecnTpcd",
                table: "ElsUnredeemedScale",
                columns: new[] { "StdDt", "StndBassetCd", "SecnTpcd" },
                unique: true);

            migrationBuilder.CreateIndex(
                name: "IX_ElsUnredeemedScale_StndBassetCd_StdDt",
                table: "ElsUnredeemedScale",
                columns: new[] { "StndBassetCd", "StdDt" });
        }

        /// <inheritdoc />
        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropTable(
                name: "DerivAsset");

            migrationBuilder.DropTable(
                name: "DerivAssetExercise");

            migrationBuilder.DropTable(
                name: "DerivIssuance");

            migrationBuilder.DropTable(
                name: "DerivMaster");

            migrationBuilder.DropTable(
                name: "DerivRedemption");

            migrationBuilder.DropTable(
                name: "DerivRedemptionCondition");

            migrationBuilder.DropTable(
                name: "ElsUnredeemedScale");
        }
    }
}