Bridge-and-Join-s/internal/lkgateway/depotables.go

package lkgateway

// Трансферные таблицы обмена с DEPO — FTV_M2M, FTV_M2M_LINES, FTV_M2M_EVENTS,
// FTV_M2M_HANDBOOK — в базе DEPO клиента (Fancy.DepoDB). Структура повторяет
// таблицы разработчика Fancy (сняты с SQL002 15.09.2026) и описана один раз:
// из неё строится DDL для MSSQL и PostgreSQL. При установке таблицы создаёт
// либо bj (логином с правами, пароль не сохраняется), либо администратор СУБД
// по скрипту. DDL идемпотентный: недостающие таблицы, колонки и индексы
// добавляются, существующие данные не трогаются.

import (
	"context"
	"database/sql"
	"fmt"
	"regexp"
	"strings"
)

// depoDefaultDatabase — база DEPO, если в настройках не задана (наша копия на SQL002).
const depoDefaultDatabase = "BJ_DEPO_DATA"

// DepoDB — имя базы депозитария DEPO на сервере Fancy.
func (s FancyMSSQLSettings) DepoDB() string {
	if d := strings.TrimSpace(s.DepoDatabase); d != "" {
		return d
	}
	return depoDefaultDatabase
}

// IISMarkers — с чего начинается название брокерского договора ИИС в DEPO (OD_CLIENTS_PORTS.PORT_NUM).
func (s FancyMSSQLSettings) IISMarkers() []string {
	var out []string
	for _, m := range strings.Split(s.IISContractMarker, ",") {
		if m = strings.TrimSpace(m); m != "" {
			out = append(out, m)
		}
	}
	if len(out) == 0 {
		out = []string{"БРИИС"}
	}
	return out
}

type depoCol struct {
	Name, MSSQL, PG string
	NotNull         bool
	DefMSSQL, DefPG string // выражение DEFAULT, "" — без значения по умолчанию
}

func depoNull(name, ms, pg string) depoCol { return depoCol{Name: name, MSSQL: ms, PG: pg} }
func depoNotNull(name, ms, pg string) depoCol {
	return depoCol{Name: name, MSSQL: ms, PG: pg, NotNull: true}
}

func (c depoCol) def(ms, pg string) depoCol {
	c.DefMSSQL, c.DefPG = ms, pg
	return c
}

type depoTableSpec struct {
	Name    string
	Cols    []depoCol // без ID: у всех таблиц это identity и первичный ключ
	Indexes [][]string
}

var (
	depoColState   = depoNotNull("STATE", "varchar(64)", "varchar(64)").def("'TODO'", "'TODO'")
	depoColTimeNew = depoNull("TimeNew", "datetime", "timestamp").def("GETDATE()", "LOCALTIMESTAMP")
	depoColTimeBeg = depoNull("TimeBeg", "datetime", "timestamp")
	depoColTimeEnd = depoNull("TimeEnd", "datetime", "timestamp")
	depoColComment = depoNull("COMMENT", "varchar(max)", "text")
	depoColGUID    = depoNotNull("GUID", "uniqueidentifier", "uuid")
)

var depoTableSpecs = []depoTableSpec{
	{Name: "FTV_M2M", Cols: []depoCol{
		depoColGUID, depoColState, depoColTimeNew, depoColTimeBeg, depoColTimeEnd, depoColComment,
		depoNotNull("DIRECTION", "varchar(4)", "varchar(4)"),
		depoNotNull("CONTRAGENT_M2M_CODE", "varchar(255)", "varchar(255)"),
		depoNotNull("DEPONENT_NAME", "varchar(255)", "varchar(255)"),
		depoNotNull("DEPONENT_DOC", "varchar(255)", "varchar(255)"),
		depoNull("DOC", "int", "integer"),
		depoNull("CONTRAGENT", "int", "integer"),
		depoNull("DEPONENT", "int", "integer"),
	}, Indexes: [][]string{{"GUID"}}},
	{Name: "FTV_M2M_LINES", Cols: []depoCol{
		depoColGUID,
		depoNull("REFERENCE", "varchar(36)", "varchar(36)"),
		depoColState, depoColTimeNew, depoColTimeBeg, depoColTimeEnd,
		depoNull("ISIN", "varchar(12)", "varchar(12)"),
		depoNull("SEC_CODE", "varchar(255)", "varchar(255)"),
		depoNull("AMOUNT", "numeric(25,7)", "numeric(25,7)"),
		depoNull("PLACE_NUMBER", "varchar(128)", "varchar(128)"),
		depoNull("LINK_NUMBER", "varchar(64)", "varchar(64)"),
		depoNull("DEPO_NUMBER", "varchar(128)", "varchar(128)"),
		depoNull("DOC", "int", "integer"),
		depoNull("DOL", "int", "integer"),
		depoNull("SHARE", "int", "integer"),
		depoNull("PLACE", "int", "integer"),
		depoNull("DEPO", "int", "integer"),
		depoColComment,
	}, Indexes: [][]string{{"GUID"}}},
	{Name: "FTV_M2M_EVENTS", Cols: []depoCol{
		depoColGUID,
		depoNull("REFERENCE", "varchar(36)", "varchar(36)"),
		depoColState, depoColTimeNew, depoColTimeBeg, depoColTimeEnd, depoColComment,
		depoNotNull("EVENT_TYPE", "varchar(64)", "varchar(64)"),
		depoNotNull("EVENT_DATE", "datetime", "timestamp"),
		depoNull("M2M_REJECT_CODE", "varchar(max)", "text"),
	}, Indexes: [][]string{{"GUID"}, {"EVENT_TYPE", "STATE"}}},
	{Name: "FTV_M2M_HANDBOOK", Cols: []depoCol{
		depoNotNull("INN", "varchar(12)", "varchar(12)"),
		depoNull("PARTICIPANT_CODE", "varchar(12)", "varchar(12)"),
		depoNotNull("KIND", "varchar(8)", "varchar(8)"),
		depoNull("NAME_FULL", "nvarchar(255)", "varchar(255)"),
		depoNull("NAME_SHORT", "nvarchar(128)", "varchar(128)"),
		depoNull("NAME_DISPLAY", "nvarchar(255)", "varchar(255)"),
		depoNull("NAME_ENG", "nvarchar(255)", "varchar(255)"),
		depoNull("HANDBOOK_AT", "datetime", "timestamp"),
		depoNotNull("UPDATED_AT", "datetime", "timestamp").def("GETDATE()", "LOCALTIMESTAMP"),
		depoNotNull("ACTIVE", "bit", "smallint").def("1", "1"), // smallint: bj пишет литерал 1
	}, Indexes: [][]string{{"INN"}}},
}

func depoTableSpecByName(name string) depoTableSpec {
	for _, t := range depoTableSpecs {
		if t.Name == name {
			return t
		}
	}
	return depoTableSpec{}
}

func depoTableNames() []string {
	out := make([]string, 0, len(depoTableSpecs))
	for _, t := range depoTableSpecs {
		out = append(out, t.Name)
	}
	return out
}

// colSQL — определение колонки. forAdd — для ALTER TABLE ADD в существующую
// таблицу: NOT NULL без значения по умолчанию туда не добавить, колонка будет NULL.
func (c depoCol) colSQL(pg, forAdd bool, table string) string {
	typ, def := c.MSSQL, c.DefMSSQL
	if pg {
		typ, def = c.PG, c.DefPG
	}
	s := c.Name + " " + typ
	if c.NotNull && (!forAdd || def != "") {
		s += " NOT NULL"
	} else {
		s += " NULL"
	}
	if def != "" {
		if pg {
			s += " DEFAULT " + def
		} else {
			s += fmt.Sprintf(" CONSTRAINT DF_%s_%s DEFAULT (%s)", table, c.Name, def)
		}
	}
	return s
}

func (t depoTableSpec) createSQL(pg bool) string {
	var b strings.Builder
	if pg {
		fmt.Fprintf(&b, "CREATE TABLE IF NOT EXISTS %s (\n  ID integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY", t.Name)
	} else {
		fmt.Fprintf(&b, "IF OBJECT_ID(N'dbo.%[1]s', N'U') IS NULL\nCREATE TABLE dbo.%[1]s (\n  ID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_%[1]s PRIMARY KEY CLUSTERED", t.Name)
	}
	for _, c := range t.Cols {
		b.WriteString(",\n  " + c.colSQL(pg, false, t.Name))
	}
	b.WriteString("\n)")
	return b.String()
}

// addColumnsSQL — недостающие колонки в таблице, созданной по более ранней структуре.
func (t depoTableSpec) addColumnsSQL(pg bool) string {
	parts := make([]string, 0, len(t.Cols))
	for _, c := range t.Cols {
		if pg {
			parts = append(parts, "ADD COLUMN IF NOT EXISTS "+c.colSQL(true, true, t.Name))
		} else {
			parts = append(parts, fmt.Sprintf("IF COL_LENGTH(N'dbo.%[1]s', N'%[2]s') IS NULL ALTER TABLE dbo.%[1]s ADD %[3]s;", t.Name, c.Name, c.colSQL(false, true, t.Name)))
		}
	}
	if pg {
		return "ALTER TABLE " + t.Name + "\n  " + strings.Join(parts, ",\n  ")
	}
	return strings.Join(parts, "\n")
}

func (t depoTableSpec) indexSQL(pg bool) []string {
	var out []string
	for _, cols := range t.Indexes {
		name := "IX_" + t.Name + "_" + strings.Join(cols, "_")
		if pg {
			out = append(out, fmt.Sprintf("CREATE INDEX IF NOT EXISTS %s ON %s (%s)", strings.ToLower(name), t.Name, strings.Join(cols, ", ")))
		} else {
			out = append(out, fmt.Sprintf("IF INDEXPROPERTY(OBJECT_ID(N'dbo.%[1]s'), N'%[2]s', 'IndexID') IS NULL CREATE INDEX %[2]s ON dbo.%[1]s (%[3]s)", t.Name, name, strings.Join(cols, ", ")))
		}
	}
	return out
}

// depoTablesDDL — все стейтменты создания/догонки структуры, по одному на вызов Exec.
func depoTablesDDL(pg bool) []string {
	var out []string
	for _, t := range depoTableSpecs {
		out = append(out, t.createSQL(pg), t.addColumnsSQL(pg))
		out = append(out, t.indexSQL(pg)...)
	}
	return out
}

var depoLoginRe = regexp.MustCompile(`^[A-Za-z0-9_.\-]+$`)

// depoTablesGrantSQL — права логину bj на чтение и запись трансферных таблиц.
func depoTablesGrantSQL(pg bool, user string) []string {
	if !depoLoginRe.MatchString(user) {
		return nil
	}
	if pg {
		return []string{
			fmt.Sprintf(`GRANT USAGE ON SCHEMA public TO "%s"`, user),
			fmt.Sprintf(`GRANT SELECT ON ALL TABLES IN SCHEMA public TO "%s"`, user), // учётные таблицы DEPO (сверка)
			fmt.Sprintf(`GRANT SELECT, INSERT, UPDATE, DELETE ON %s TO "%s"`, strings.Join(depoTableNames(), ", "), user),
		}
	}
	out := []string{
		fmt.Sprintf("IF USER_ID(N'%[1]s') IS NULL CREATE USER [%[1]s] FOR LOGIN [%[1]s]", user),
		// чтение учётных таблиц DEPO (лица, счета, остатки, опции) — для сверки заявок
		fmt.Sprintf("IF IS_ROLEMEMBER(N'db_datareader', N'%[1]s') = 0 ALTER ROLE db_datareader ADD MEMBER [%[1]s]", user),
	}
	for _, n := range depoTableNames() {
		out = append(out, fmt.Sprintf("GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.%s TO [%s]", n, user))
	}
	return out
}

func depoTablesFileName(f FancyMSSQLSettings) string {
	if f.IsPostgres() {
		return "bj-depo-tables-postgres.sql"
	}
	return "bj-depo-tables-mssql.sql"
}

// depoTablesScript — скрипт для администратора СУБД (вариант «по скрипту»).
func depoTablesScript(f FancyMSSQLSettings) string {
	pg := f.IsPostgres()
	db, user := f.DepoDB(), strings.TrimSpace(f.User)
	sep := "\nGO\n\n"
	var b strings.Builder
	if pg {
		sep = ";\n\n"
		fmt.Fprintf(&b, "-- Bridge&Join: трансферные таблицы обмена с DEPO. PostgreSQL 10 и новее.\n-- Выполнить в базе %[1]s владельцем базы или суперпользователем, например:\n--   psql -h <сервер> -U postgres -d %[1]s -f %[2]s\n", db, depoTablesFileName(f))
	} else {
		fmt.Fprintf(&b, "-- Bridge&Join: трансферные таблицы обмена с DEPO. Microsoft SQL Server.\n-- Выполнить в SSMS или sqlcmd под логином с ролью db_owner в базе %s\n-- (роль нужна, чтобы добавить логин bj в db_datareader).\n", db)
	}
	b.WriteString("-- Скрипт можно запускать повторно: добавляются только недостающие таблицы, колонки и индексы, данные не меняются.\n\n")
	if !pg {
		fmt.Fprintf(&b, "USE [%s];%s", db, sep)
	}
	for _, s := range depoTablesDDL(pg) {
		b.WriteString(s + sep)
	}
	if grants := depoTablesGrantSQL(pg, user); len(grants) > 0 {
		fmt.Fprintf(&b, "-- Права логину bj (%s): чтение и запись трансферных таблиц.\n", user)
		for _, s := range grants {
			b.WriteString(s + sep)
		}
	} else {
		b.WriteString("-- Права логину bj: выдайте SELECT, INSERT, UPDATE, DELETE на таблицы выше (логин bj в настройках не указан).\n\n")
	}
	b.WriteString("-- Кроме того, логину bj нужно право SELECT на учётные таблицы DEPO (OD_CLIENTS, OD_CLIENTS_PORTS, OD_RESTS, OD_VALUES и др.) для сверки переводов.\n")
	return b.String()
}

func depoTableVisible(ctx context.Context, db *sql.DB, pg bool, name string) bool {
	q, arg := "SELECT CASE WHEN OBJECT_ID(@p1, 'U') IS NULL THEN 0 ELSE 1 END", "dbo."+name
	if pg {
		q, arg = "SELECT to_regclass($1) IS NOT NULL", name
	}
	var ok bool
	return db.QueryRowContext(ctx, q, arg).Scan(&ok) == nil && ok
}

func depoMissingColumns(ctx context.Context, db *sql.DB, pg bool, t depoTableSpec) []string {
	q, arg := "SELECT name FROM sys.columns WHERE object_id = OBJECT_ID(@p1)", "dbo."+t.Name
	if pg {
		q, arg = "SELECT column_name FROM information_schema.columns WHERE table_schema = current_schema() AND table_name = lower($1)", t.Name
	}
	rows, err := db.QueryContext(ctx, q, arg)
	if err != nil {
		return nil
	}
	defer rows.Close()
	have := map[string]bool{}
	for rows.Next() {
		var n string
		if rows.Scan(&n) == nil {
			have[strings.ToUpper(n)] = true
		}
	}
	var miss []string
	for _, c := range append([]depoCol{{Name: "ID"}}, t.Cols...) {
		if !have[strings.ToUpper(c.Name)] {
			miss = append(miss, c.Name)
		}
	}
	return miss
}

func depoStmtHead(s string) string {
	s, _, _ = strings.Cut(strings.TrimSpace(s), "\n")
	if len(s) > 70 {
		s = s[:70] + "…"
	}
	return s
}

func depoTablesRightsHint(f FancyMSSQLSettings, login string, err error) string {
	m := strings.ToLower(err.Error())
	if !strings.Contains(m, "permission") && !strings.Contains(m, "denied") && !strings.Contains(m, "разрешени") && !strings.Contains(m, "запрещ") {
		return ""
	}
	if f.IsPostgres() {
		return fmt.Sprintf(". У роли %s нет права создавать таблицы в базе %s: укажите владельца базы или суперпользователя либо выберите вариант «по скрипту»", login, f.DepoDB())
	}
	return fmt.Sprintf(". У логина %s нет права создавать таблицы в базе %s: нужна роль db_owner (или db_ddladmin, а для выдачи прав bj ещё db_securityadmin) либо вариант «по скрипту»", login, f.DepoDB())
}

// depoTablesCreate — создать таблицы в базе DEPO. adminUser/adminPass — логин с правами
// (используется один раз, не сохраняется); пусто — логин bj из настроек. Если логин
// другой, bj выдаёт своему логину права на таблицы.
func depoTablesCreate(ctx context.Context, f FancyMSSQLSettings, adminUser, adminPass string) (string, error) {
	pg := f.IsPostgres()
	conn, grantTo := f, ""
	if u := strings.TrimSpace(adminUser); u != "" {
		conn.User, conn.Password = u, adminPass
		if !strings.EqualFold(u, strings.TrimSpace(f.User)) {
			grantTo = strings.TrimSpace(f.User)
		}
	}
	db, err := fancyOpen(conn, f.DepoDB())
	if err != nil {
		return "", err
	}
	defer db.Close()
	if err := db.PingContext(ctx); err != nil {
		return "", fmt.Errorf("подключение к базе %s логином %s: %s", f.DepoDB(), conn.User, quikErrorDetail(err))
	}
	existed := 0
	for _, t := range depoTableSpecs {
		if depoTableVisible(ctx, db, pg, t.Name) {
			existed++
		}
	}
	for _, s := range depoTablesDDL(pg) {
		if _, err := db.ExecContext(ctx, s); err != nil {
			return "", fmt.Errorf("%s → %s%s", depoStmtHead(s), quikErrorDetail(err), depoTablesRightsHint(f, conn.User, err))
		}
	}
	msg := fmt.Sprintf("в базе %s создано таблиц: %d, уже были: %d", f.DepoDB(), len(depoTableSpecs)-existed, existed)
	if grantTo != "" {
		grants := depoTablesGrantSQL(pg, grantTo)
		if grants == nil {
			return msg + "; права логину " + grantTo + " не выданы: недопустимые символы в имени", nil
		}
		for _, s := range grants {
			if _, err := db.ExecContext(ctx, s); err != nil {
				return "", fmt.Errorf("%s, но выдать права логину %s не удалось: %s → %s", msg, grantTo, depoStmtHead(s), quikErrorDetail(err))
			}
		}
		msg += ", права логину " + grantTo + " выданы"
	}
	return msg, nil
}

// depoTablesCheck — проверка логином bj: таблицы видны, колонки на месте, есть права на запись.
func depoTablesCheck(ctx context.Context, f FancyMSSQLSettings) (string, bool) {
	pg := f.IsPostgres()
	db, err := fancyOpen(f, f.DepoDB())
	if err != nil {
		return "подключение: " + err.Error(), false
	}
	defer db.Close()
	if err := db.PingContext(ctx); err != nil {
		return fmt.Sprintf("нет подключения к базе %s: %s", f.DepoDB(), quikErrorDetail(err)), false
	}
	var problems []string
	for _, t := range depoTableSpecs {
		if !depoTableVisible(ctx, db, pg, t.Name) {
			problems = append(problems, "нет таблицы "+t.Name+" (или у логина "+f.User+" нет к ней доступа)")
			continue
		}
		var lacks []string
		for _, p := range []string{"SELECT", "INSERT", "UPDATE", "DELETE"} {
			q, arg := "SELECT ISNULL(HAS_PERMS_BY_NAME(@p1, 'OBJECT', @p2), 0)", "dbo."+t.Name
			if pg {
				q, arg = "SELECT has_table_privilege($1, $2)", t.Name
			}
			var has bool
			if db.QueryRowContext(ctx, q, arg, p).Scan(&has) != nil || !has {
				lacks = append(lacks, p)
			}
		}
		if len(lacks) > 0 {
			problems = append(problems, fmt.Sprintf("у логина %s нет прав %s на %s", f.User, strings.Join(lacks, "/"), t.Name))
		}
		if miss := depoMissingColumns(ctx, db, pg, t); len(miss) > 0 {
			problems = append(problems, t.Name+": нет колонок "+strings.Join(miss, ", "))
		}
	}
	if len(problems) > 0 {
		return strings.Join(problems, "; "), false
	}
	return fmt.Sprintf("в базе %s все %d таблицы на месте, колонки совпадают, у логина %s есть SELECT/INSERT/UPDATE/DELETE", f.DepoDB(), len(depoTableSpecs), f.User), true
}

// DepoTablesView — данные карточки «Трансферные таблицы DEPO».
type DepoTablesView struct {
	DB, User, Script, ScriptFile string
	Postgres                     bool
}

func depoTablesView(f FancyMSSQLSettings) DepoTablesView {
	return DepoTablesView{DB: f.DepoDB(), User: f.User, Script: depoTablesScript(f), ScriptFile: depoTablesFileName(f), Postgres: f.IsPostgres()}
}