package service

import (
	"context"
	"encoding/json"
	"fmt"
	"os"
	"path/filepath"
	"strings"
	"sync/atomic"
	"time"

	"git.5iot.com/iotlcm-public/iotlcm-onelink-mock/internal/dao"
	"git.5iot.com/iotlcm-public/iotlcm-onelink-mock/internal/model/do"
	"git.5iot.com/iotlcm-public/iotlcm-onelink-mock/internal/model/entity"
	"git.5iot.com/iotlcm-public/iotlcm-onelink-mock/manifest"
	_ "github.com/gogf/gf/contrib/drivers/sqlite/v2"
	"github.com/gogf/gf/v2/database/gdb"
	"github.com/gogf/gf/v2/errors/gerror"
)

var sqliteStoreSequence atomic.Uint64

type sqliteStoreBackend struct {
	db       gdb.DB
	ctx      context.Context
	keepOpen bool
}

func newSQLiteStoreBackend(databaseLink string) (storeBackend, error) {
	if !strings.HasPrefix(databaseLink, "sqlite:") {
		if strings.TrimSpace(databaseLink) == "" {
			databaseLink = filepath.Join("data", "onelink_mock.db")
		}
		if databaseLink != ":memory:" {
			if err := os.MkdirAll(filepath.Dir(databaseLink), 0o755); err != nil {
				return nil, gerror.Wrap(err, "create sqlite directory")
			}
		}
		databaseLink = fmt.Sprintf("sqlite::@file(%s)", filepath.ToSlash(databaseLink))
	}
	group := fmt.Sprintf("onelink_sqlite_%d", sqliteStoreSequence.Add(1))
	if err := gdb.AddConfigNode(group, gdb.ConfigNode{
		Type: "sqlite", Link: databaseLink,
		MaxIdleConnCount: 1, MaxOpenConnCount: 1, MaxConnLifeTime: 24 * time.Hour,
	}); err != nil {
		return nil, gerror.Wrap(err, "configure sqlite")
	}
	db, err := gdb.NewByGroup(group)
	if err != nil {
		return nil, gerror.Wrap(err, "open sqlite")
	}
	backend := &sqliteStoreBackend{db: db, ctx: context.Background(), keepOpen: strings.Contains(databaseLink, ":memory:")}
	if err = backend.ensureSchema(); err != nil {
		return nil, err
	}
	if !backend.keepOpen {
		if err = db.Close(backend.ctx); err != nil {
			return nil, gerror.Wrap(err, "close sqlite after initialization")
		}
	}
	return backend, nil
}

func (b *sqliteStoreBackend) close() error {
	if b.keepOpen {
		return nil
	}
	if err := b.db.Close(b.ctx); err != nil {
		return gerror.Wrap(err, "close sqlite connection")
	}
	return nil
}

func (b *sqliteStoreBackend) ensureSchema() error {
	if _, err := b.db.Exec(b.ctx, manifest.SchemaSQL); err != nil {
		return gerror.Wrap(err, "initialize sqlite schema")
	}
	rows, err := b.db.GetAll(b.ctx, "PRAGMA table_info(mock_t_sim_card)")
	if err != nil {
		return gerror.Wrap(err, "inspect sqlite schema")
	}
	columns := make(map[string]struct{}, len(rows))
	for _, row := range rows {
		columns[row["name"].String()] = struct{}{}
	}
	migrations := map[string]string{
		"group_id":          "ALTER TABLE mock_t_sim_card ADD COLUMN group_id INTEGER",
		"stop_reason":       "ALTER TABLE mock_t_sim_card ADD COLUMN stop_reason TEXT",
		"frozen_status":     "ALTER TABLE mock_t_sim_card ADD COLUMN frozen_status TEXT",
		"frozen_reason":     "ALTER TABLE mock_t_sim_card ADD COLUMN frozen_reason TEXT",
		"frozen_oper_time":  "ALTER TABLE mock_t_sim_card ADD COLUMN frozen_oper_time TEXT",
		"exported":          "ALTER TABLE mock_t_sim_card ADD COLUMN exported INTEGER NOT NULL DEFAULT 0",
		"export_checked_at": "ALTER TABLE mock_t_sim_card ADD COLUMN export_checked_at TEXT",
	}
	for column, statement := range migrations {
		if _, ok := columns[column]; ok {
			continue
		}
		if _, err = b.db.Exec(b.ctx, statement); err != nil {
			return gerror.Wrapf(err, "add sqlite column %s", column)
		}
	}
	return nil
}

func (b *sqliteStoreBackend) Load() (Store, error) {
	defer b.close()
	data := defaultStore()
	var groups []entity.MockCardGroup
	if err := b.db.Model(dao.MockCardGroup.Table()).Ctx(b.ctx).OrderAsc(dao.MockCardGroup.Columns().Id).Scan(&groups); err != nil {
		return Store{}, gerror.Wrap(err, "load card groups")
	}
	data.Groups = make([]CardGroup, 0, len(groups))
	groupNames := make(map[int64]string, len(groups))
	for _, row := range groups {
		group := CardGroup{ID: int64(row.Id), Name: row.Name, CreatedAt: row.CreatedAt, UpdatedAt: row.UpdatedAt}
		data.Groups = append(data.Groups, group)
		groupNames[group.ID] = group.Name
	}
	var rows []entity.MockTSimCard
	if err := b.db.Model(dao.MockTSimCard.Table()).Ctx(b.ctx).OrderAsc(dao.MockTSimCard.Columns().Id).Scan(&rows); err != nil {
		return Store{}, gerror.Wrap(err, "load sim cards")
	}
	data.Cards = make([]SIMCard, 0, len(rows))
	for _, row := range rows {
		card := SIMCard{
			GroupID: int64(row.GroupId), GroupName: groupNames[int64(row.GroupId)],
			MSISDN: row.Msisdn, ICCID: row.Iccid, IMSI: row.Imsi, IMEI: row.Imei, Remark: row.Remark,
			CardStatus: row.CardStatus, LastChangeDate: row.LastChangeDate, StopReason: row.StopReason,
			FrozenStatus: row.FrozenStatus, FrozenReason: row.FrozenReason, FrozenOperTime: row.FrozenOperTime,
			OpenDate: row.OpenDate, ActiveDate: row.ActiveDate, OnOffStatus: row.OnOffStatus,
			SessionStatus: row.SessionStatus, SessionCreateDate: row.SessionCreateDate,
			IP: row.SessionIpv4, IPv6Prefix: row.SessionIpv6Prefix, IPv6: row.SessionIpv6, RAT: row.Rat,
			CardBindStatus: row.CardBindStatus, RegionLimitStatus: row.RegionLimitStatus,
			AreaRestrictType: row.AreaRestrictType, RestrictedArea: row.RestrictedArea,
			MonthUsageKB: float64(row.CurrentMonthUsageKb), PoolUsageBytes: float64(row.PoolUsageBytes),
			DailyBaseKB: float64(row.DailyUsageKb), MonthlyBaseKB: float64(row.MonthlyUsageKb),
			GrowthPerMinuteKB: float64(row.GrowthPerMinuteKb), LastUsageUpdateTime: dbTimeToRFC3339(row.LastUsageUpdateTime),
			Exported: row.Exported != 0, ExportCheckedAt: row.ExportCheckedAt,
		}
		card.APNs = parseAPNs(row.ApnListJson, row.PrimaryApnName, row.PrimaryApnStatus, float64(row.PrimaryApnUsageKb), int64(row.PrimaryApnUsageBytes))
		data.Cards = append(data.Cards, card)
	}
	var orders []entity.MockOrder
	if err := b.db.Model(dao.MockOrder.Table()).Ctx(b.ctx).OrderAsc(dao.MockOrder.Columns().Id).Scan(&orders); err != nil {
		return Store{}, gerror.Wrap(err, "load orders")
	}
	data.Orders = make([]Order, 0, len(orders))
	for _, row := range orders {
		data.Orders = append(data.Orders, Order{OrderNum: row.OrderNum, MSISDN: row.Msisdn, ICCID: row.Iccid, IMSI: row.Imsi, Type: row.Type, OperType: row.OperType, APNName: row.ApnName, Status: row.Status, CreatedAt: row.CreatedAt})
	}
	return data, nil
}

func (b *sqliteStoreBackend) Save(data Store) error {
	defer b.close()
	return b.db.Transaction(b.ctx, func(ctx context.Context, tx gdb.TX) error {
		if _, err := tx.Exec(manifest.SchemaSQL); err != nil {
			return gerror.Wrap(err, "initialize sqlite schema")
		}
		for _, table := range []string{dao.MockOrder.Table(), dao.MockTSimCard.Table(), dao.MockCardGroup.Table()} {
			if _, err := tx.Model(table).Ctx(ctx).Where("1=1").Delete(); err != nil {
				return gerror.Wrap(err, "clear sqlite table")
			}
		}
		if err := insertGroups(ctx, tx, data.Groups); err != nil {
			return err
		}
		if err := insertCards(ctx, tx, data.Cards); err != nil {
			return err
		}
		return insertOrders(ctx, tx, data.Orders)
	})
}

func (b *sqliteStoreBackend) AppendCards(cards []SIMCard) error {
	defer b.close()
	return b.db.Transaction(b.ctx, func(ctx context.Context, tx gdb.TX) error {
		if _, err := tx.Exec(manifest.SchemaSQL); err != nil {
			return gerror.Wrap(err, "initialize sqlite schema")
		}
		return insertCards(ctx, tx, cards)
	})
}

func insertGroups(ctx context.Context, tx gdb.TX, groups []CardGroup) error {
	for _, item := range groups {
		if _, err := tx.Model(dao.MockCardGroup.Table()).Ctx(ctx).Data(do.MockCardGroup{Id: item.ID, Name: item.Name, CreatedAt: item.CreatedAt, UpdatedAt: item.UpdatedAt}).Insert(); err != nil {
			return gerror.Wrap(err, "insert card group")
		}
	}
	return nil
}

func insertCards(ctx context.Context, tx gdb.TX, cards []SIMCard) error {
	for _, item := range cards {
		normalizeCard(&item)
		primary := primaryAPN(item)
		apnList, err := json.Marshal(apnsForDB(item.APNs))
		if err != nil {
			return gerror.Wrap(err, "encode apn list")
		}
		carrier, packageCode, packageName := mockMetadataFromRemark(item.Remark)
		data := do.MockTSimCard{
			GroupId: item.GroupID, Msisdn: item.MSISDN, Iccid: item.ICCID, Imsi: item.IMSI, Imei: item.IMEI,
			Remark: item.Remark, Carrier: carrier, PackageCode: packageCode, PackageName: packageName,
			PackageStatus: 1, PackageTotalFlowMb: item.MonthlyBaseKB / 1024, IsFlowPool: 1,
			CardStatus: item.CardStatus, LastChangeDate: item.LastChangeDate, StopReason: item.StopReason,
			FrozenStatus: item.FrozenStatus, FrozenReason: item.FrozenReason, FrozenOperTime: item.FrozenOperTime,
			OpenDate: item.OpenDate, ActiveDate: item.ActiveDate, OnOffStatus: item.OnOffStatus, SessionStatus: item.SessionStatus,
			ApnId: primary.Name, SessionIpv4: item.IP, SessionIpv6Prefix: item.IPv6Prefix, SessionIpv6: item.IPv6,
			SessionCreateDate: item.SessionCreateDate, Rat: item.RAT, ApnListJson: string(apnList), ApnUsageJson: string(apnList),
			PrimaryApnName: primary.Name, PrimaryApnStatus: primary.Status, PrimaryApnUsageKb: primary.UsageBytes / 1024,
			PrimaryApnUsageBytes: int64(primary.UsageBytes), PccCode: "default", PccUsageKb: primary.UsageBytes / 1024,
			CurrentMonthUsageKb: item.MonthUsageKB, PoolUsageBytes: int64(item.PoolUsageBytes), DailyUsageKb: item.DailyBaseKB,
			MonthlyUsageKb: item.MonthlyBaseKB, DailyQueryDate: parseCardTime(item.ActiveDate).Format("20060102"),
			MonthlyQueryMonth: parseCardTime(item.ActiveDate).Format("200601"), MonthlyDeadline: parseCardTime(item.ActiveDate).AddDate(0, 0, -1).Format("20060102"),
			GrowthPerMinuteKb: item.GrowthPerMinuteKB, AutoGrowthEnabled: 0, LastUsageUpdateTime: rfc3339ToDBTime(item.LastUsageUpdateTime),
			CardBindStatus: item.CardBindStatus, RegionLimitStatus: item.RegionLimitStatus, AreaRestrictType: item.AreaRestrictType,
			RestrictedArea: item.RestrictedArea, Exported: boolInt(item.Exported), ExportCheckedAt: item.ExportCheckedAt,
			CreatedAt: item.OpenDate, UpdatedAt: item.LastChangeDate,
		}
		if _, err = tx.Model(dao.MockTSimCard.Table()).Ctx(ctx).Data(data).Insert(); err != nil {
			return gerror.Wrap(err, "insert sim card")
		}
	}
	return nil
}

func insertOrders(ctx context.Context, tx gdb.TX, orders []Order) error {
	for _, item := range orders {
		if _, err := tx.Model(dao.MockOrder.Table()).Ctx(ctx).Data(do.MockOrder{OrderNum: item.OrderNum, Msisdn: item.MSISDN, Iccid: item.ICCID, Imsi: item.IMSI, Type: item.Type, OperType: item.OperType, ApnName: item.APNName, Status: item.Status, CreatedAt: item.CreatedAt}).Insert(); err != nil {
			return gerror.Wrap(err, "insert order")
		}
	}
	return nil
}

func boolInt(value bool) int {
	if value {
		return 1
	}
	return 0
}