Attribute VB_Name = "InvestPilot"
' InvestPilot — витрина MOEX + журнал + тренажёр
' CSV: GET {API}/api/atr/csv?user={User}&layout=vba
'
' При открытии: разнести колонки (если склеились) + форматирование.
' Кнопки на «Витрине»: «Обновить» · «Формат»

Option Explicit

Private Const DATA_START_ROW As Long = 4
Private Const VITRINA_SHEET As String = "Витрина"
Private Const JOURNAL_SHEET As String = "Журнал"
Private Const SANDBOX_SHEET As String = "Тренажёр"
Private Const VITRINA_COLS As Long = 20

' ======================== ТОЧКА ВХОДА ========================

Public Sub Auto_Open()
    On Error Resume Next
    SetupInvestPilot False
    BuildControlPanel GetVitrinaSheet()
    UpdateAll
    On Error GoTo 0
End Sub

Public Sub SetupInvestPilot(Optional ByVal force As Boolean = False)
    Dim ws As Worksheet

    EnsureSheet VITRINA_SHEET
    EnsureSheet JOURNAL_SHEET
    EnsureSheet SANDBOX_SHEET

    Set ws = GetVitrinaSheet()
    If force Or ws.Range("B4").Value = "" Then SetupVitrinaSheet ws

    Set ws = ThisWorkbook.Worksheets(JOURNAL_SHEET)
    If force Or ws.Range("B4").Value = "" Then SetupJournalSheet ws

    Set ws = ThisWorkbook.Worksheets(SANDBOX_SHEET)
    If force Or ws.Range("B4").Value = "" Then SetupSandboxSheet ws

    BuildControlPanel GetVitrinaSheet()
    BuildJournalPanel ThisWorkbook.Worksheets(JOURNAL_SHEET)
    BuildSandboxPanel ThisWorkbook.Worksheets(SANDBOX_SHEET)
End Sub

' ======================== ОБНОВЛЕНИЕ ========================

Public Sub UpdateAll()
    UpdateVitrina
    RefreshSandboxPrices
End Sub

Public Sub FormatVitrinaPublic()
    FixAndFormatVitrina
End Sub

Public Sub FixAndFormatVitrina()
    Dim ws As Worksheet
    Set ws = GetVitrinaSheet()
    SplitVitrinaIfSingleColumn ws
    FormatVitrinaTable ws
    AddVitrinaCheckboxes ws
    RefreshSandboxPrices
End Sub

Public Sub UpdateVitrina()
    Dim url As String
    Dim ws As Worksheet
    Dim qt As QueryTable

    Set ws = GetVitrinaSheet()
    url = BuildCsvUrl()

    Application.ScreenUpdating = False
    On Error GoTo CleanFail

    For Each qt In ws.QueryTables
        qt.Delete
    Next qt

    If ws.Cells(DATA_START_ROW, 2).Value <> "" Then
        ws.Range("A" & DATA_START_ROW & ":" & ws.Cells(ws.Rows.Count, ws.Columns.Count).Address).Clear
    End If

    With ws.QueryTables.Add(Connection:="TEXT;" & url, Destination:=ws.Range("B4"))
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFilePlatform = 65001
        .Refresh BackgroundQuery:=False
    End With

    For Each qt In ws.QueryTables
        qt.Delete
    Next qt

    ws.Range("A:A").ClearContents
    FixAndFormatVitrina

CleanExit:
    Application.ScreenUpdating = True
    Exit Sub
CleanFail:
    MsgBox "Ошибка загрузки витрины:" & vbCrLf & Err.Description, vbExclamation, "InvestPilot"
    Resume CleanExit
End Sub

Private Sub SplitVitrinaIfSingleColumn(ws As Worksheet)
    Dim lastRow As Long
    Dim r As Long
    Dim line As String
    Dim cols() As String
    Dim c As Long
    Dim needSplit As Boolean
    Dim shifted() As String

    needSplit = False
    If InStr(CStr(ws.Cells(DATA_START_ROW, 2).Value), vbTab) > 0 Then
        If ws.Cells(DATA_START_ROW, 3).Value = "" Then needSplit = True
    End If
    If InStr(CStr(ws.Cells(DATA_START_ROW, 2).Value), ";") > 0 Then
        If ws.Cells(DATA_START_ROW, 3).Value = "" Then needSplit = True
    End If
    If ws.Cells(DATA_START_ROW + 1, 4).Value = "" Then
        line = CStr(ws.Cells(DATA_START_ROW + 1, 2).Value)
        If InStr(line, vbTab) > 0 Or InStr(line, ";") > 0 Then needSplit = True
    End If
    If Not needSplit Then Exit Sub

    lastRow = ws.Cells(ws.Rows.Count, 2).End(xlUp).Row
    For r = DATA_START_ROW To lastRow
        line = CStr(ws.Cells(r, 2).Value)
        If InStr(line, vbTab) > 0 Then
            cols = Split(line, vbTab)
        ElseIf InStr(line, ";") > 0 Then
            cols = Split(line, ";")
            If LBound(cols) <= UBound(cols) Then
                If Trim$(cols(LBound(cols))) = "" Then
                    ReDim shifted(LBound(cols) To UBound(cols) - 1)
                    For c = LBound(cols) + 1 To UBound(cols)
                        shifted(c - 1) = cols(c)
                    Next c
                    cols = shifted
                End If
            End If
        Else
            GoTo NextRow
        End If
        ws.Range(ws.Cells(r, 2), ws.Cells(r, 2 + VITRINA_COLS)).ClearContents
        For c = LBound(cols) To UBound(cols)
            If c - LBound(cols) < VITRINA_COLS Then
                ws.Cells(r, 2 + c - LBound(cols)).Value = CleanCsvField(cols(c))
            End If
        Next c
NextRow:
    Next r
End Sub

Private Function StripUtf8Bom(ByVal s As String) As String
    If Len(s) = 0 Then
        StripUtf8Bom = s
        Exit Function
    End If
    If AscW(Left$(s, 1)) = &HFEFF Then
        StripUtf8Bom = Mid$(s, 2)
    ElseIf Len(s) >= 3 Then
        If Left$(s, 1) = Chr$(239) And Mid$(s, 2, 1) = Chr$(187) And Mid$(s, 3, 1) = Chr$(191) Then
            StripUtf8Bom = Mid$(s, 4)
        Else
            StripUtf8Bom = s
        End If
    Else
        StripUtf8Bom = s
    End If
End Function

Private Function CleanCsvField(ByVal v As String) As String
    v = Replace(v, Chr$(160), " ")
    CleanCsvField = Trim$(v)
End Function

Public Sub RefreshSandboxPrices()
    Dim wsS As Worksheet
    Dim lastRow As Long
    Dim r As Long

    On Error Resume Next
    Set wsS = ThisWorkbook.Worksheets(SANDBOX_SHEET)
    lastRow = wsS.Cells(wsS.Rows.Count, 2).End(xlUp).Row
    If lastRow < 5 Then Exit Sub

    For r = 5 To lastRow
        If wsS.Cells(r, 2).Value <> "" Then
            wsS.Cells(r, 6).Formula = "=IFERROR(VLOOKUP(B" & r & "," & VITRINA_SHEET & "!$D$5:$M$2000,10,FALSE),"""")"
        End If
    Next r
    On Error GoTo 0
End Sub

' ======================== ПАНЕЛИ ========================

Public Sub BuildControlPanel(ws As Worksheet)
    Dim btn As Button

    On Error Resume Next
    For Each btn In ws.Buttons
        btn.Delete
    Next btn
    On Error GoTo 0

    ws.Range("A1").Value = "InvestPilot"
    ws.Range("A1").Font.Size = 14
    ws.Range("A1").Font.Bold = True
    ws.Range("A2").Value = "investpilot74.ru"
    ws.Range("A2").Font.Size = 10
    ws.Range("A2").Font.Color = RGB(68, 114, 196)

    ws.Range("G1").Value = "User:"
    ws.Range("G1").Font.Bold = True
    ws.Range("H1").Name = "InvestPilot_User"
    If ws.Range("H1").Value = "" Then ws.Range("H1").Value = "pilot_demo"

    ws.Range("G2").Value = "API:"
    ws.Range("G2").Font.Bold = True
    ws.Range("H2").Name = "InvestPilot_API"
    If ws.Range("H2").Value = "" Then ws.Range("H2").Value = "http://localhost:8080"

    Set btn = ws.Buttons.Add(ws.Range("C1").Left, ws.Range("C1").Top, 78, 22)
    btn.Caption = "Обновить"
    btn.OnAction = "UpdateAll"

    Set btn = ws.Buttons.Add(ws.Range("D1").Left, ws.Range("D1").Top, 78, 22)
    btn.Caption = "Формат"
    btn.OnAction = "FormatVitrinaPublic"
End Sub

Private Sub BuildJournalPanel(ws As Worksheet)
    ws.Range("A1").Value = "Бортовой журнал"
    ws.Range("A1").Font.Size = 14
    ws.Range("A1").Font.Bold = True
    ws.Range("A2").Value = "Записи локально · цены с «Витрина» после «Обновить»"
    ws.Range("A2").Font.Size = 9
End Sub

Private Sub BuildSandboxPanel(ws As Worksheet)
    Dim btn As Button

    On Error Resume Next
    For Each btn In ws.Buttons
        btn.Delete
    Next btn
    On Error GoTo 0

    ws.Range("A1").Value = "Тренажёр (демо)"
    ws.Range("A1").Font.Size = 14
    ws.Range("A1").Font.Bold = True
    ws.Range("A2").Value = "Без реальных сделок · F5+ — формулы P/L"
    ws.Range("B2").Value = "Баланс:"
    ws.Range("C2").Value = 1000000
    ws.Range("C2").NumberFormat = "# ##0 ""₽"""

    Set btn = ws.Buttons.Add(ws.Range("E1").Left, ws.Range("E1").Top, 90, 22)
    btn.Caption = "Обновить цены"
    btn.OnAction = "RefreshSandboxPrices"
End Sub

' ======================== ЛИСТЫ ========================

Private Sub SetupVitrinaSheet(ws As Worksheet)
    ws.Cells.Clear
    ws.Range("B4").Value = "Баз.актив"
    ws.Range("C4").Value = "Название"
    ws.Range("D4").Value = "Тикер"
    ws.Range("E4").Value = "Экспирация"
    ws.Range("F4").Value = "Дней до эксп."
    ws.Range("G4").Value = "Статус"
    ws.Range("H4").Value = "Шаг"
    ws.Range("I4").Value = "Стоимость шага"
    ws.Range("J4").Value = "Лот"
    ws.Range("K4").Value = "ГО покупки"
    ws.Range("L4").Value = "ГО продажи"
    ws.Range("M4").Value = "Цена"
    ws.Range("N4").Value = "OPEN"
    ws.Range("O4").Value = "HIGH"
    ws.Range("P4").Value = "LOW"
    ws.Range("Q4").Value = "PrevClose"
    ws.Range("R4").Value = "Спред"
    ws.Range("S4").Value = "Объём за день"
    ws.Range("T4").Value = "Сделок"
    ws.Range("U4").Value = "ATR"
End Sub

Private Sub SetupJournalSheet(ws As Worksheet)
    ws.Cells.Clear
    ws.Range("B4").Value = "Дата"
    ws.Range("C4").Value = "Тикер"
    ws.Range("D4").Value = "Сторона"
    ws.Range("E4").Value = "Кол-во"
    ws.Range("F4").Value = "Цена"
    ws.Range("G4").Value = "Комиссия"
    ws.Range("H4").Value = "Заметка"
    ws.Range("I4").Value = "P/L ₽"
    ws.Range("J4").Value = "Текущая"
    ws.Range("K4").Value = "Нереал. P/L"

    With ws.Range("B5:K500")
        .Borders.LineStyle = xlContinuous
        .Borders.Color = RGB(220, 220, 220)
    End With

    ws.Range("J5").Formula = "=IF(C5="""","""",IFERROR(VLOOKUP(C5," & VITRINA_SHEET & "!$D$5:$M$2000,10,FALSE),""""))"
    ws.Range("K5").Formula = "=IF(OR(C5="""",F5=""""),"""",(J5-F5)*E5*(IF(D5=""Short"",-1,1)))"
    ws.Range("J5:K5").AutoFill Destination:=ws.Range("J5:K500")
End Sub

Private Sub SetupSandboxSheet(ws As Worksheet)
    ws.Cells.Clear
    ws.Range("B4").Value = "Тикер"
    ws.Range("C4").Value = "Long/Short"
    ws.Range("D4").Value = "Лоты"
    ws.Range("E4").Value = "Вход"
    ws.Range("F4").Value = "Текущая"
    ws.Range("G4").Value = "P/L пунктов"
    ws.Range("H4").Value = "P/L ₽ (оценка)"

    ws.Range("G5").Formula = "=IF(B5="""","""",(F5-E5)*(IF(C5=""Short"",-1,1)))"
    ws.Range("H5").Formula = "=IF(B5="""","""",G5*D5*IFERROR(VLOOKUP(B5," & VITRINA_SHEET & "!$D$5:$J$2000,7,FALSE),1))"
    ws.Range("G5:H5").AutoFill Destination:=ws.Range("G5:H500")

    With ws.Range("B4:H4")
        .Font.Bold = True
        .Interior.Color = RGB(68, 114, 196)
        .Font.Color = vbWhite
    End With
End Sub

' ======================== ФОРМАТИРОВАНИЕ ВИТРИНЫ ========================

Private Sub FormatVitrinaTable(ws As Worksheet)
    Dim lastRow As Long
    Dim lastCol As Long

    SplitVitrinaIfSingleColumn ws

    lastRow = ws.Cells(ws.Rows.Count, 4).End(xlUp).Row
    lastCol = ws.Cells(DATA_START_ROW, ws.Columns.Count).End(xlToLeft).Column
    If lastRow < DATA_START_ROW Then Exit Sub
    If lastCol < 4 Then lastCol = 2 + VITRINA_COLS

    ws.Rows(DATA_START_ROW + 1 & ":" & lastRow).RowHeight = 22.5

    With ws.Range(ws.Cells(DATA_START_ROW, 2), ws.Cells(DATA_START_ROW, lastCol))
        .Font.Bold = True
        .Font.Color = vbWhite
        .Interior.Color = RGB(68, 114, 196)
        .HorizontalAlignment = xlCenter
    End With

    If lastRow > DATA_START_ROW Then
        With ws.Range(ws.Cells(DATA_START_ROW + 1, 2), ws.Cells(lastRow, lastCol))
            .Borders(xlInsideHorizontal).LineStyle = xlContinuous
            .Borders(xlInsideVertical).LineStyle = xlContinuous
            .HorizontalAlignment = xlCenter
        End With
    End If

    ws.Range(ws.Cells(DATA_START_ROW, 2), ws.Cells(lastRow, lastCol)).EntireColumn.AutoFit
    ws.Columns("A").ColumnWidth = 3

    ws.Activate
    ws.Range("A5").Select
    ActiveWindow.FreezePanes = False
    ActiveWindow.FreezePanes = True
End Sub

Private Sub AddVitrinaCheckboxes(ws As Worksheet)
    Dim lastRow As Long
    Dim i As Long
    Dim cb As CheckBox

    lastRow = ws.Cells(ws.Rows.Count, 4).End(xlUp).Row

    On Error Resume Next
    For Each cb In ws.CheckBoxes
        cb.Delete
    Next cb
    On Error GoTo 0

    For i = DATA_START_ROW + 1 To lastRow
        If ws.Cells(i, 4).Value <> "" Then
            Set cb = ws.CheckBoxes.Add(ws.Cells(i, 1).Left + 3, ws.Cells(i, 1).Top + 3, 14, 14)
            cb.LinkedCell = ""
            cb.Caption = ""
            cb.Value = xlOff
        End If
    Next i
End Sub

' ======================== УТИЛИТЫ ========================

Private Function BuildCsvUrl() As String
    Dim api As String
    Dim userId As String
    api = Trim$(CStr(GetVitrinaSheet().Range("H2").Value))
    userId = Trim$(CStr(GetVitrinaSheet().Range("H1").Value))
    If api = "" Then api = "http://localhost:8080"
    If userId = "" Then userId = "pilot_demo"
    If Right$(api, 1) = "/" Then api = Left$(api, Len(api) - 1)
    BuildCsvUrl = api & "/api/atr/csv?user=" & userId & "&layout=vba&_=" & Timer
End Function

Private Function GetVitrinaSheet() As Worksheet
    EnsureSheet VITRINA_SHEET
    Set GetVitrinaSheet = ThisWorkbook.Worksheets(VITRINA_SHEET)
End Function

Private Sub EnsureSheet(ByVal sheetName As String)
    Dim ws As Worksheet
    On Error Resume Next
    Set ws = ThisWorkbook.Worksheets(sheetName)
    On Error GoTo 0
    If ws Is Nothing Then
        Set ws = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
        ws.Name = sheetName
    End If
End Sub

Public Sub UpdateATR()
    UpdateAll
End Sub