//go:build sqliteimport && !js && !wasm && !baremetal

package importer

import (
	"context"
	"crypto/sha256"
	"database/sql"
	"encoding/hex"
	"errors"
	"fmt"
	"io"
	"os"
	"path/filepath"
	"runtime/debug"
	"time"

	_ "modernc.org/sqlite"

	"github.com/SimonWaldherr/tinySQL/internal/storage"
)

type artifactSourceSchema string

const (
	artifactSourceFlat       artifactSourceSchema = "flat"
	artifactSourceNormalized artifactSourceSchema = "normalized"
)

// ImportMBTilesArtifact builds a bounded paged-index database in a temporary
// sibling directory and publishes it only after checksums and logical index
// validation succeed. The source is opened read-only and is never modified.
func ImportMBTilesArtifact(ctx context.Context, sourcePath, artifactPath string, opts *MBTilesArtifactOptions) (*MBTilesArtifactResult, error) {
	if opts == nil {
		opts = &MBTilesArtifactOptions{}
	}
	var err error
	opts, err = cloneArtifactOptions(opts)
	if err != nil {
		return nil, fmt.Errorf("copy MBTiles artifact options: %w", err)
	}
	applyArtifactOptionDefaults(opts)
	if opts.Schema == "" {
		opts.Schema = MBTilesSchemaAuto
	}
	if opts.Schema != MBTilesSchemaAuto && opts.Schema != MBTilesSchemaFlat && opts.Schema != MBTilesSchemaNormalized {
		return nil, fmt.Errorf("unsupported MBTiles artifact schema %q", opts.Schema)
	}
	if err := checkContext(ctx); err != nil {
		return nil, err
	}
	parent := filepath.Dir(filepath.Clean(artifactPath))
	if err := os.MkdirAll(parent, 0o755); err != nil {
		return nil, fmt.Errorf("create artifact parent: %w", err)
	}

	info, err := os.Stat(sourcePath)
	if err != nil {
		return nil, fmt.Errorf("stat MBTiles source: %w", err)
	}
	src, err := sql.Open("sqlite", "file:"+sourcePath+"?mode=ro&immutable=1")
	if err != nil {
		return nil, fmt.Errorf("open MBTiles source: %w", err)
	}
	defer src.Close()
	if err := src.PingContext(ctx); err != nil {
		return nil, fmt.Errorf("ping MBTiles source: %w", err)
	}

	sourceSchema, err := detectArtifactSourceSchema(ctx, src)
	if err != nil {
		return nil, err
	}
	estimate, err := estimateArtifactResources(ctx, src, sourceSchema, info.Size(), filepath.Dir(artifactPath), opts)
	if err != nil {
		return nil, err
	}
	if opts.Progress != nil {
		opts.Progress(MBTilesProgress{Phase: "preflight", TotalRows: estimate.TileCount, BatchSize: estimate.BatchSize, Estimate: &estimate})
	}
	if err := checkArtifactResourceBudget(estimate, opts, "MBTiles import"); err != nil {
		return nil, err
	}

	if err := ensureArtifactTargetAvailable(artifactPath, opts.ReplaceExisting); err != nil {
		return nil, err
	}
	tmp, err := newTempArtifactDir(parent)
	if err != nil {
		return nil, err
	}
	defer os.RemoveAll(tmp)

	db, err := storage.OpenDB(storage.StorageConfig{Mode: storage.ModePagedIndex, Path: filepath.Join(tmp, "database"), MaxMemoryBytes: opts.MaxMemoryBytes})
	if err != nil {
		return nil, fmt.Errorf("create paged-index database: %w", err)
	}
	closeDB := true
	defer func() {
		if closeDB {
			_ = db.Close()
		}
	}()

	metadataRows, metadataDigest, err := readArtifactMetadata(ctx, src)
	if err != nil {
		return nil, err
	}
	destinationSchema := opts.Schema
	if destinationSchema == MBTilesSchemaAuto {
		if sourceSchema == artifactSourceFlat {
			destinationSchema = MBTilesSchemaFlat
		} else {
			destinationSchema = MBTilesSchemaNormalized
		}
	}
	if destinationSchema == MBTilesSchemaFlat {
		if err := createArtifactTables(db, MBTilesSchemaFlat); err != nil {
			return nil, err
		}
	} else if err := createArtifactTables(db, MBTilesSchemaNormalized); err != nil {
		return nil, err
	}
	if err := appendArtifactRows(ctx, db, "default", "metadata", metadataRows, opts.BatchSize, nil, opts); err != nil {
		return nil, err
	}

	var tileDigest = sha256.New()
	progress := &artifactProgress{opts: opts, total: estimate.TileCount, batchSize: opts.BatchSize}
	switch {
	case sourceSchema == artifactSourceFlat && destinationSchema == MBTilesSchemaFlat:
		err = importFlatToFlat(ctx, src, db, progress, tileDigest)
	case sourceSchema == artifactSourceFlat && destinationSchema == MBTilesSchemaNormalized:
		err = importFlatToNormalized(ctx, src, db, progress, tileDigest)
	case sourceSchema == artifactSourceNormalized && destinationSchema == MBTilesSchemaNormalized:
		err = importNormalizedToNormalized(ctx, src, db, progress, tileDigest)
	case sourceSchema == artifactSourceNormalized && destinationSchema == MBTilesSchemaFlat:
		err = importNormalizedToFlat(ctx, src, db, progress, tileDigest)
	}
	if err != nil {
		return nil, err
	}
	if err := checkContext(ctx); err != nil {
		return nil, err
	}
	if err := db.Close(); err != nil {
		return nil, fmt.Errorf("close imported database: %w", err)
	}
	closeDB = false
	// The mutable writer cache is no longer useful once the database has been
	// checkpointed. Return it before checksum and read-only validation allocate
	// their own streaming buffers, keeping the phases from stacking in RSS.
	debug.FreeOSMemory()

	indexConfig := artifactIndexConfig(destinationSchema)
	if err := writeJSONFile(filepath.Join(tmp, "indexes", "config.json"), indexConfig); err != nil {
		return nil, err
	}
	manifest := MBTilesArtifactManifest{
		FormatVersion:  mbtilesArtifactFormatVersion,
		TinySQLVersion: "v0.29.0",
		Schema:         destinationSchema,
		CreatedAt:      time.Now().UTC(),
		Source:         filepath.Base(sourcePath),
		SourceBytes:    info.Size(),
		Resources:      estimate,
		Provenance:     opts.Provenance,
		Tables:         artifactTableManifest(destinationSchema, estimate.TileCount, estimate.ImageRows, int64(len(metadataRows))),
		IndexConfig:    indexConfig,
		Checksums:      map[string]string{},
	}
	manifest.Checksums, err = checksumFiles(tmp, artifactDataFileNames(tmp))
	if err != nil {
		return nil, err
	}
	manifest.IndexConfig["tile_digest_sha256"] = hex.EncodeToString(tileDigest.Sum(nil))
	manifest.IndexConfig["metadata_digest_sha256"] = metadataDigest
	if err := writeJSONFile(filepath.Join(tmp, "manifest.json"), manifest); err != nil {
		return nil, err
	}
	if err := writeChecksumsFile(tmp); err != nil {
		return nil, err
	}
	if _, err := validateArtifact(ctx, tmp, false); err != nil {
		return nil, fmt.Errorf("validate temporary artifact: %w", err)
	}
	if err := os.WriteFile(filepath.Join(tmp, "COMPLETE"), []byte(""), 0o644); err != nil {
		return nil, fmt.Errorf("write completion marker: %w", err)
	}
	// The only mutation after the complete validation above is creation of the
	// zero-byte marker. Readers in a new process still perform the strict
	// checksum and logical validation before opening the published path.
	if err := syncArtifactTree(tmp); err != nil {
		return nil, err
	}
	if err := publishArtifact(tmp, artifactPath, opts.ReplaceExisting); err != nil {
		return nil, err
	}
	if opts.Progress != nil {
		opts.Progress(MBTilesProgress{Phase: "published", RowsRead: estimate.TileCount, RowsWritten: estimate.TileCount, TotalRows: estimate.TileCount, BatchSize: opts.BatchSize})
	}
	return &MBTilesArtifactResult{ArtifactPath: artifactPath, Manifest: manifest, Estimate: estimate}, nil
}

func detectArtifactSourceSchema(ctx context.Context, db *sql.DB) (artifactSourceSchema, error) {
	flat, err := artifactTableExists(ctx, db, "tiles")
	if err != nil {
		return "", err
	}
	normMap, err := artifactTableExists(ctx, db, "map")
	if err != nil {
		return "", err
	}
	normImages, err := artifactTableExists(ctx, db, "images")
	if err != nil {
		return "", err
	}
	if flat {
		return artifactSourceFlat, nil
	}
	if normMap && normImages {
		return artifactSourceNormalized, nil
	}
	return "", errors.New("MBTiles source has neither tiles nor map/images schema")
}

func artifactTableExists(ctx context.Context, db *sql.DB, table string) (bool, error) {
	var name string
	err := db.QueryRowContext(ctx, "SELECT name FROM sqlite_master WHERE type='table' AND name=?", table).Scan(&name)
	if errors.Is(err, sql.ErrNoRows) {
		return false, nil
	}
	if err != nil {
		return false, fmt.Errorf("inspect MBTiles schema: %w", err)
	}
	return true, nil
}

func estimateArtifactResources(ctx context.Context, db *sql.DB, schema artifactSourceSchema, sourceBytes int64, diskPath string, opts *MBTilesArtifactOptions) (MBTilesResourceEstimate, error) {
	var rows, maxTile, totalTileBytes int64
	var query string
	if schema == artifactSourceFlat {
		query = "SELECT COUNT(*), COALESCE(MAX(length(tile_data)),0), COALESCE(SUM(length(tile_data)),0) FROM tiles"
	} else {
		query = "SELECT COUNT(*), COALESCE(MAX(length(tile_data)),0), COALESCE(SUM(length(tile_data)),0) FROM images"
	}
	if err := db.QueryRowContext(ctx, query).Scan(&rows, &maxTile, &totalTileBytes); err != nil {
		return MBTilesResourceEstimate{}, fmt.Errorf("estimate MBTiles resources: %w", err)
	}
	mapRows, imageRows := rows, rows
	if schema == artifactSourceNormalized {
		if err := db.QueryRowContext(ctx, "SELECT COUNT(*) FROM map").Scan(&mapRows); err != nil {
			return MBTilesResourceEstimate{}, fmt.Errorf("count normalized MBTiles map: %w", err)
		}
	}
	var metadataRows int64
	if exists, err := artifactTableExists(ctx, db, "metadata"); err != nil {
		return MBTilesResourceEstimate{}, err
	} else if exists {
		if err := db.QueryRowContext(ctx, "SELECT COUNT(*) FROM metadata").Scan(&metadataRows); err != nil {
			return MBTilesResourceEstimate{}, fmt.Errorf("count MBTiles metadata: %w", err)
		}
	}
	batch := opts.BatchSize
	memory := maxTile*int64(batch) + int64(batch)*768 + 2*int64(1<<20)
	disk := sourceBytes + totalTileBytes + rows*320 + int64(8<<20)
	return MBTilesResourceEstimate{
		SourceBytes: sourceBytes, TileCount: mapRows, MapRows: mapRows, ImageRows: imageRows, MetadataRows: metadataRows,
		EstimatedMemory: memory, EstimatedDisk: disk,
		AvailableDisk: availableDiskBytes(diskPath), BatchSize: batch,
	}, nil
}

func readArtifactMetadata(ctx context.Context, db *sql.DB) ([][]any, string, error) {
	rows := [][]any{}
	h := sha256.New()
	exists, err := artifactTableExists(ctx, db, "metadata")
	if err != nil || !exists {
		return rows, hex.EncodeToString(h.Sum(nil)), err
	}
	sqlRows, err := db.QueryContext(ctx, "SELECT name,value FROM metadata ORDER BY name")
	if err != nil {
		return nil, "", fmt.Errorf("read MBTiles metadata: %w", err)
	}
	defer sqlRows.Close()
	for sqlRows.Next() {
		var name, value sql.NullString
		if err := sqlRows.Scan(&name, &value); err != nil {
			return nil, "", fmt.Errorf("scan MBTiles metadata: %w", err)
		}
		if !name.Valid || !value.Valid {
			return nil, "", errors.New("MBTiles metadata contains NULL name/value")
		}
		rows = append(rows, []any{name.String, value.String})
		hashMetadata(h, name.String, value.String)
	}
	if err := sqlRows.Err(); err != nil {
		return nil, "", fmt.Errorf("read MBTiles metadata: %w", err)
	}
	return rows, hex.EncodeToString(h.Sum(nil)), nil
}

func importFlatToFlat(ctx context.Context, src *sql.DB, db *storage.DB, p *artifactProgress, digest io.Writer) error {
	rows, err := src.QueryContext(ctx, "SELECT zoom_level,tile_column,tile_row,tile_data FROM tiles ORDER BY zoom_level,tile_column,tile_row")
	if err != nil {
		return fmt.Errorf("query flat MBTiles: %w", err)
	}
	defer rows.Close()
	batch := make([][]any, 0, p.batchSize)
	for rows.Next() {
		var z, x, y int
		var data []byte
		if err := rows.Scan(&z, &x, &y, &data); err != nil {
			return err
		}
		if err := validateTileCoordinate(z, x, y); err != nil {
			return err
		}
		hashTile(digest, z, x, y, data)
		p.read++
		p.bytesRead += int64(len(data))
		batch = append(batch, []any{z, x, y, data})
		if len(batch) == p.batchSize {
			if err := appendArtifactRows(ctx, db, "default", "tiles", batch, p.batchSize, p, nil); err != nil {
				return err
			}
			batch = batch[:0]
		}
	}
	if err := rows.Err(); err != nil {
		return err
	}
	return appendArtifactRows(ctx, db, "default", "tiles", batch, p.batchSize, p, nil)
}

func importFlatToNormalized(ctx context.Context, src *sql.DB, db *storage.DB, p *artifactProgress, digest io.Writer) error {
	rows, err := src.QueryContext(ctx, "SELECT zoom_level,tile_column,tile_row,tile_data FROM tiles ORDER BY zoom_level,tile_column,tile_row")
	if err != nil {
		return fmt.Errorf("query flat MBTiles: %w", err)
	}
	defer rows.Close()
	mapRows := make([][]any, 0, p.batchSize)
	imageRows := make([][]any, 0, p.batchSize)
	for rows.Next() {
		var z, x, y int
		var data []byte
		if err := rows.Scan(&z, &x, &y, &data); err != nil {
			return err
		}
		if err := validateTileCoordinate(z, x, y); err != nil {
			return err
		}
		id := fmt.Sprintf("%d/%d/%d", z, x, y)
		hashTile(digest, z, x, y, data)
		p.read++
		p.bytesRead += int64(len(data))
		mapRows = append(mapRows, []any{z, x, y, id})
		imageRows = append(imageRows, []any{id, data})
		if len(mapRows) == p.batchSize {
			if err := appendArtifactRows(ctx, db, "default", "map", mapRows, p.batchSize, p, nil); err != nil {
				return err
			}
			if err := appendArtifactRows(ctx, db, "default", "images", imageRows, p.batchSize, nil, nil); err != nil {
				return err
			}
			mapRows = mapRows[:0]
			imageRows = imageRows[:0]
		}
	}
	if err := rows.Err(); err != nil {
		return err
	}
	if err := appendArtifactRows(ctx, db, "default", "map", mapRows, p.batchSize, p, nil); err != nil {
		return err
	}
	return appendArtifactRows(ctx, db, "default", "images", imageRows, p.batchSize, nil, nil)
}

func importNormalizedToNormalized(ctx context.Context, src *sql.DB, db *storage.DB, p *artifactProgress, digest io.Writer) error {
	rows, err := src.QueryContext(ctx, "SELECT m.zoom_level,m.tile_column,m.tile_row,m.tile_id,i.tile_data FROM map m JOIN images i ON i.tile_id=m.tile_id ORDER BY m.zoom_level,m.tile_column,m.tile_row")
	if err != nil {
		return fmt.Errorf("query normalized MBTiles: %w", err)
	}
	defer rows.Close()
	batch := make([][]any, 0, p.batchSize)
	for rows.Next() {
		var z, x, y int
		var id string
		var data []byte
		if err := rows.Scan(&z, &x, &y, &id, &data); err != nil {
			return err
		}
		if err := validateTileCoordinate(z, x, y); err != nil {
			return err
		}
		hashTile(digest, z, x, y, data)
		p.read++
		p.bytesRead += int64(len(data))
		batch = append(batch, []any{z, x, y, id})
		if len(batch) == p.batchSize {
			if err := appendArtifactRows(ctx, db, "default", "map", batch, p.batchSize, p, nil); err != nil {
				return err
			}
			batch = batch[:0]
		}
	}
	if err := rows.Err(); err != nil {
		return err
	}
	if err := appendArtifactRows(ctx, db, "default", "map", batch, p.batchSize, p, nil); err != nil {
		return err
	}
	return importNormalizedImages(ctx, src, db, p)
}

func importNormalizedToFlat(ctx context.Context, src *sql.DB, db *storage.DB, p *artifactProgress, digest io.Writer) error {
	rows, err := src.QueryContext(ctx, "SELECT m.zoom_level,m.tile_column,m.tile_row,i.tile_data FROM map m JOIN images i ON i.tile_id=m.tile_id ORDER BY m.zoom_level,m.tile_column,m.tile_row")
	if err != nil {
		return fmt.Errorf("query normalized MBTiles: %w", err)
	}
	defer rows.Close()
	batch := make([][]any, 0, p.batchSize)
	for rows.Next() {
		var z, x, y int
		var data []byte
		if err := rows.Scan(&z, &x, &y, &data); err != nil {
			return err
		}
		if err := validateTileCoordinate(z, x, y); err != nil {
			return err
		}
		hashTile(digest, z, x, y, data)
		p.read++
		p.bytesRead += int64(len(data))
		batch = append(batch, []any{z, x, y, data})
		if len(batch) == p.batchSize {
			if err := appendArtifactRows(ctx, db, "default", "tiles", batch, p.batchSize, p, nil); err != nil {
				return err
			}
			batch = batch[:0]
		}
	}
	if err := rows.Err(); err != nil {
		return err
	}
	return appendArtifactRows(ctx, db, "default", "tiles", batch, p.batchSize, p, nil)
}

func importNormalizedImages(ctx context.Context, src *sql.DB, db *storage.DB, p *artifactProgress) error {
	rows, err := src.QueryContext(ctx, "SELECT tile_id,tile_data FROM images ORDER BY tile_id")
	if err != nil {
		return err
	}
	defer rows.Close()
	batch := make([][]any, 0, p.batchSize)
	for rows.Next() {
		var id string
		var data []byte
		if err := rows.Scan(&id, &data); err != nil {
			return err
		}
		batch = append(batch, []any{id, data})
		if len(batch) == p.batchSize {
			if err := appendArtifactRows(ctx, db, "default", "images", batch, p.batchSize, nil, nil); err != nil {
				return err
			}
			batch = batch[:0]
		}
	}
	if err := rows.Err(); err != nil {
		return err
	}
	return appendArtifactRows(ctx, db, "default", "images", batch, p.batchSize, nil, nil)
}
