ANTOOZA-BACKEND/Infrastructure/Persistence/Migrations/20260706052225_AddEsgData.cs

using System;
using Microsoft.EntityFrameworkCore.Migrations;

#nullable disable

namespace Infrastructure.Migrations.AppDb
{
    /// <inheritdoc />
    public partial class AddEsgData : Migration
    {
        /// <inheritdoc />
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.CreateTable(
                name: "EsgIndexDailyPrice",
                columns: table => new
                {
                    ID = table.Column<long>(type: "bigint", nullable: false)
                        .Annotation("SqlServer:Identity", "1, 1"),
                    IndexName = table.Column<string>(type: "nvarchar(100)", maxLength: 100, nullable: false, comment: "지수명 (IDX_NM)"),
                    TradeDate = table.Column<DateOnly>(type: "date", nullable: false),
                    Close = table.Column<decimal>(type: "decimal(18,2)", precision: 18, scale: 2, nullable: false, comment: "현재가/종가 (CLSPRC_IDX)"),
                    ChangeVal = table.Column<decimal>(type: "decimal(18,2)", precision: 18, scale: 2, nullable: false, comment: "전일비 (PRV_DD_CMPR)"),
                    FlucRateBp = table.Column<int>(type: "int", nullable: false, comment: "등락률 basis point (%×100, UPDN_RATE)"),
                    ConstituentCount = table.Column<int>(type: "int", nullable: false, comment: "구성종목수 (TRD_ISU_CNT)"),
                    Volume = table.Column<long>(type: "bigint", nullable: false, comment: "거래량 (ACC_TRDVOL, 천주)"),
                    Value = table.Column<long>(type: "bigint", nullable: false, comment: "거래대금 (ACC_TRDVAL, 백만원)"),
                    CreatedAt = table.Column<DateTime>(type: "datetime2", nullable: false)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_EsgIndexDailyPrice", x => x.ID);
                },
                comment: "ESG 지수 일별시세 (KRX OpenAPI esg/esg_index_info)");

            migrationBuilder.CreateTable(
                name: "EsgSecurity",
                columns: table => new
                {
                    ID = table.Column<long>(type: "bigint", nullable: false)
                        .Annotation("SqlServer:Identity", "1, 1"),
                    Name = table.Column<string>(type: "nvarchar(100)", maxLength: 100, nullable: false, comment: "종목명 (ISU_ABBRV)"),
                    TradeDate = table.Column<DateOnly>(type: "date", nullable: false),
                    Close = table.Column<decimal>(type: "decimal(18,2)", precision: 18, scale: 2, nullable: false, comment: "현재가/종가 (TDD_CLSPRC)"),
                    ChangeAmount = table.Column<decimal>(type: "decimal(18,2)", precision: 18, scale: 2, nullable: false, comment: "전일비 (CMPPREVDD_PRC)"),
                    ChangeRate = table.Column<decimal>(type: "decimal(9,2)", precision: 9, scale: 2, nullable: false, comment: "등락률 % (FLUC_RT)"),
                    ListedShares = table.Column<long>(type: "bigint", nullable: false, comment: "상장좌수 (LIST_SHRS)"),
                    Volume = table.Column<long>(type: "bigint", nullable: false, comment: "거래량(좌) (ACC_TRDVOL)"),
                    TradeValue = table.Column<long>(type: "bigint", nullable: false, comment: "거래대금(원) (ACC_TRDVAL)"),
                    CreatedAt = table.Column<DateTime>(type: "datetime2", nullable: false)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_EsgSecurity", x => x.ID);
                },
                comment: "ESG 증권상품 일별시세 (KRX OpenAPI esg/esg_etp_info)");

            migrationBuilder.CreateTable(
                name: "SriBond",
                columns: table => new
                {
                    ID = table.Column<long>(type: "bigint", nullable: false)
                        .Annotation("SqlServer:Identity", "1, 1"),
                    Code = table.Column<string>(type: "nvarchar(12)", maxLength: 12, nullable: false, comment: "표준코드 (ISU_CD) — 12자리 ISIN"),
                    TradeDate = table.Column<DateOnly>(type: "date", nullable: false),
                    IssuerName = table.Column<string>(type: "nvarchar(100)", maxLength: 100, nullable: false, comment: "발행기관 (ISUR_NM)"),
                    SriBondType = table.Column<string>(type: "nvarchar(30)", maxLength: 30, nullable: false, comment: "채권종류 (SRI_BND_TP_NM)"),
                    Name = table.Column<string>(type: "nvarchar(100)", maxLength: 100, nullable: false, comment: "종목명 (ISU_NM)"),
                    ListDate = table.Column<DateOnly>(type: "date", nullable: true, comment: "상장일 (LIST_DD)"),
                    IssueDate = table.Column<DateOnly>(type: "date", nullable: true, comment: "발행일 (ISU_DD)"),
                    RedemptionDate = table.Column<DateOnly>(type: "date", nullable: true, comment: "상환일 (REDMPT_DD)"),
                    CouponRate = table.Column<decimal>(type: "decimal(9,5)", precision: 9, scale: 5, nullable: true, comment: "표면이자율 % (ISU_RT)"),
                    IssueAmount = table.Column<long>(type: "bigint", nullable: false, comment: "발행금액 (ISU_AMT, 원)"),
                    ListAmount = table.Column<long>(type: "bigint", nullable: false, comment: "상장금액 (LIST_AMT, 원)"),
                    BondType = table.Column<string>(type: "nvarchar(30)", maxLength: 30, nullable: true, comment: "채권유형 (BND_TP_NM)"),
                    CreatedAt = table.Column<DateTime>(type: "datetime2", nullable: false)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_SriBond", x => x.ID);
                },
                comment: "사회책임투자채권(SRI) 정보 스냅샷 (KRX OpenAPI esg/sri_bond_info)");

            migrationBuilder.CreateIndex(
                name: "IX_EsgIndexDailyPrice_IndexName_TradeDate",
                table: "EsgIndexDailyPrice",
                columns: new[] { "IndexName", "TradeDate" },
                unique: true);

            migrationBuilder.CreateIndex(
                name: "IX_EsgIndexDailyPrice_TradeDate",
                table: "EsgIndexDailyPrice",
                column: "TradeDate");

            migrationBuilder.CreateIndex(
                name: "IX_EsgSecurity_Name_TradeDate",
                table: "EsgSecurity",
                columns: new[] { "Name", "TradeDate" },
                unique: true);

            migrationBuilder.CreateIndex(
                name: "IX_EsgSecurity_TradeDate_TradeValue",
                table: "EsgSecurity",
                columns: new[] { "TradeDate", "TradeValue" },
                descending: new[] { false, true });

            migrationBuilder.CreateIndex(
                name: "IX_SriBond_Code_TradeDate",
                table: "SriBond",
                columns: new[] { "Code", "TradeDate" },
                unique: true);

            migrationBuilder.CreateIndex(
                name: "IX_SriBond_TradeDate_SriBondType",
                table: "SriBond",
                columns: new[] { "TradeDate", "SriBondType" });
        }

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

            migrationBuilder.DropTable(
                name: "EsgSecurity");

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