package csvgen

import (
	"encoding/csv"
	"fmt"
	"log"
	"math"
	"math/rand"
	"os"
	"path/filepath"
	"sort"
	"strings"

	"pinscrape-allgo/internal/config"
	"pinscrape-allgo/internal/db"
)

var usCANames = []string{
	"James", "Mary", "Robert", "Patricia", "John", "Jennifer", "Michael", "Linda",
	"David", "Elizabeth", "William", "Barbara", "Richard", "Susan", "Joseph", "Jessica",
	"Thomas", "Sarah", "Charles", "Karen", "Christopher", "Lisa", "Daniel", "Nancy",
	"Matthew", "Betty", "Anthony", "Margaret", "Mark", "Sandra", "Donald", "Ashley",
	"Steven", "Kimberly", "Paul", "Emily", "Andrew", "Donna", "Joshua", "Michelle",
	"Kenneth", "Dorothy", "Kevin", "Carol", "Brian", "Amanda", "George", "Melissa",
	"Timothy", "Deborah", "Ronald", "Stephanie", "Edward", "Rebecca", "Jason", "Sharon",
	"Jeffrey", "Laura", "Ryan", "Cynthia", "Jacob", "Kathleen", "Gary", "Amy",
	"Nicholas", "Angela", "Eric", "Shirley", "Jonathan", "Anna", "Stephen", "Brenda",
	"Larry", "Pamela", "Justin", "Emma", "Scott", "Nicole", "Brandon", "Helen",
	"Benjamin", "Samantha", "Samuel", "Katherine", "Raymond", "Christine", "Gregory", "Debra",
	"Frank", "Rachel", "Alexander", "Carolyn", "Patrick", "Janet", "Jack", "Catherine",
	"Dennis", "Maria", "Jerry", "Heather", "Tyler", "Diane", "Aaron", "Ruth",
	"Jose", "Julie", "Adam", "Olivia", "Nathan", "Joyce", "Henry", "Virginia",
	"Douglas", "Victoria", "Zachary", "Kelly", "Peter", "Lauren", "Kyle", "Christina",
	"Noah", "Joan", "Ethan", "Evelyn", "Jeremy", "Judith", "Walter", "Megan",
	"Christian", "Andrea", "Keith", "Cheryl", "Roger", "Hannah", "Terry", "Jacqueline",
	"Austin", "Martha", "Sean", "Gloria", "Gerald", "Teresa", "Carl", "Ann",
	"Harold", "Sara", "Dylan", "Madison", "Arthur", "Frances", "Lawrence", "Kathryn",
	"Jordan", "Janice", "Jesse", "Jean", "Bryan", "Abigail", "Billy", "Alice",
	"Bruce", "Judy", "Gabriel", "Sophia", "Joe", "Grace", "Logan", "Denise",
	"Albert", "Amber", "Willie", "Doris", "Alan", "Marilyn", "Eugene", "Danielle",
	"Russell", "Beverly", "Vincent", "Isabella", "Philip", "Theresa", "Bobby", "Diana",
	"Johnny", "Natalie", "Bradley", "Brittany", "Roy", "Charlotte", "Elijah", "Marie",
	"Randy", "Kayla", "Wayne", "Alexis", "Howard", "Lori", "Carlos", "Tammy",
	"Russell", "Clarence", "Vincent", "Philip", "Bobby", "Johnny", "Bradley",
	"Toronto", "Vancouver", "Montreal", "Calgary", "Ottawa", "Edmonton", "Mississauga",
	"Winnipeg", "Quebec", "Hamilton", "Kitchener", "London", "Halifax", "Victoria",
	"Ethan", "Liam", "Mason", "Logan", "Lucas", "Oliver", "Aiden", "Noah",
	"Charlotte", "Amelia", "Harper", "Evelyn", "Abigail", "Emily", "Ella", "Madison",
	"Scarlett", "Victoria", "Aria", "Grace", "Chloe", "Penelope", "Layla", "Riley",
}

type dbCount struct {
	Name  string
	Count int64
}

type Mapping struct {
	Bucket    string
	Foldering string
	Name      string
	Source    string
}

func Generate(cfg *config.Config, splitSize int) ([]Mapping, error) {
	if splitSize <= 0 {
		splitSize = cfg.SplitSize
	}
	files, err := db.ListDBFiles(cfg.Path(cfg.DataFolder))
	if err != nil {
		return nil, err
	}
	sort.Strings(files)

	var counts []dbCount
	var total int64
	for _, f := range files {
		conn, err := db.Open(f)
		if err != nil {
			continue
		}
		n, err := db.CountExportable(conn)
		db.Close(conn)
		if err != nil {
			continue
		}
		name := strings.TrimSuffix(filepath.Base(f), filepath.Ext(f))
		counts = append(counts, dbCount{Name: name, Count: n})
		total += n
	}
	if total == 0 {
		log.Println("No articles found in database. CREATE.csv not generated.")
		return nil, nil
	}

	numFolders := int(math.Ceil(float64(total) / float64(splitSize)))
	folderSources := computeFolderSources(counts, splitSize, numFolders)

	used := map[string]bool{}
	var rows []Mapping
	for i := 1; i <= numFolders; i++ {
		var bucket string
		for {
			bucket = generateBucketName(0)
			if !used[bucket] {
				used[bucket] = true
				break
			}
		}
		rows = append(rows, Mapping{
			Bucket:    bucket,
			Foldering: fmt.Sprintf("folder_%d", i),
			Name:      usCANames[rand.Intn(len(usCANames))],
			Source:    folderSources[i-1],
		})
	}

	csvPath := cfg.Path("CREATE.csv")
	f, err := os.Create(csvPath)
	if err != nil {
		return nil, err
	}
	defer f.Close()
	w := csv.NewWriter(f)
	defer w.Flush()
	if err := w.Write([]string{"bucket", "foldering", "name", "source"}); err != nil {
		return nil, err
	}
	for _, r := range rows {
		if err := w.Write([]string{r.Bucket, r.Foldering, r.Name, r.Source}); err != nil {
			return nil, err
		}
	}

	log.Printf("Generated %d folder mappings -> CREATE.csv", numFolders)
	log.Printf("Total articles: %d, Split size: %d", total, splitSize)
	for _, c := range counts {
		log.Printf("  %s: %d articles", c.Name, c.Count)
	}
	return rows, nil
}

func computeFolderSources(counts []dbCount, splitSize, numFolders int) []string {
	var out []string
	dbIdx := 0
	dbOffset := int64(0)
	for i := 0; i < numFolders; i++ {
		var sources []string
		remaining := int64(splitSize)
		for remaining > 0 && dbIdx < len(counts) {
			c := counts[dbIdx]
			available := c.Count - dbOffset
			taken := available
			if taken > remaining {
				taken = remaining
			}
			found := false
			for _, s := range sources {
				if s == c.Name {
					found = true
					break
				}
			}
			if !found {
				sources = append(sources, c.Name)
			}
			remaining -= taken
			dbOffset += taken
			if dbOffset >= c.Count {
				dbIdx++
				dbOffset = 0
			}
		}
		out = append(out, strings.Join(sources, ", "))
	}
	return out
}

func generateBucketName(length int) string {
	if length <= 0 {
		length = 12 + rand.Intn(24)
	}
	const chars = "abcdefghijklmnopqrstuvwxyz"
	b := make([]byte, length)
	for i := range b {
		b[i] = chars[rand.Intn(len(chars))]
	}
	return string(b)
}

// ReadMappings loads CREATE.csv (used by export, deploy and submit).
func ReadMappings(cfg *config.Config, path string) ([]Mapping, error) {
	p := cfg.Path(path)
	f, err := os.Open(p)
	if err != nil {
		return nil, err
	}
	defer f.Close()
	r := csv.NewReader(f)
	records, err := r.ReadAll()
	if err != nil {
		return nil, err
	}
	if len(records) < 2 {
		return nil, fmt.Errorf("CSV has no data rows")
	}
	headers := records[0]
	bi, fi, ni, si := -1, -1, -1, -1
	for i, h := range headers {
		switch strings.ToLower(strings.TrimSpace(h)) {
		case "bucket":
			bi = i
		case "foldering":
			fi = i
		case "name":
			ni = i
		case "source":
			si = i
		}
	}
	if bi == -1 || fi == -1 || ni == -1 {
		return nil, fmt.Errorf("CSV missing required columns (bucket, foldering, name)")
	}
	var out []Mapping
	for _, row := range records[1:] {
		if len(row) <= bi || len(row) <= fi || len(row) <= ni {
			continue
		}
		m := Mapping{
			Bucket:    strings.TrimSpace(row[bi]),
			Foldering: strings.TrimSpace(row[fi]),
			Name:      strings.TrimSpace(row[ni]),
		}
		if si >= 0 && len(row) > si {
			m.Source = strings.TrimSpace(row[si])
		}
		out = append(out, m)
	}
	return out, nil
}