import csv
import glob
import json
import math
import os
import random
import sqlite3
import string

SCRIPTS_DIR = os.path.dirname(os.path.abspath(__file__))
ROOT_DIR = os.path.dirname(SCRIPTS_DIR)

US_CA_NAMES = [
    "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",
]


def count_snippet_articles_per_db(database_dir):
    db_counts = []
    for db_path in sorted(glob.glob(os.path.join(database_dir, "*.sqlite"))):
        try:
            conn = sqlite3.connect(db_path)
            cursor = conn.cursor()
            cursor.execute("""
                SELECT id, snippet FROM posts
                WHERE images IS NOT NULL AND images != '' AND images != '[]'
                AND snippet IS NOT NULL AND TRIM(snippet) != ''
            """)
            count = 0
            for row in cursor.fetchall():
                try:
                    snippet_data = json.loads(row[1])
                    if isinstance(snippet_data.get("description"), list) and len(snippet_data["description"]) > 0:
                        count += 1
                except (json.JSONDecodeError, TypeError, AttributeError):
                    continue
            conn.close()
            db_name = os.path.splitext(os.path.basename(db_path))[0]
            db_counts.append((db_name, count))
        except Exception:
            continue
    return db_counts


def compute_folder_sources(db_counts, split_size, num_folders):
    folder_sources = []
    db_idx = 0
    db_offset = 0

    for _ in range(num_folders):
        sources = []
        remaining = split_size

        while remaining > 0 and db_idx < len(db_counts):
            db_name, db_count = db_counts[db_idx]
            available = db_count - db_offset
            taken = min(available, remaining)

            if db_name not in sources:
                sources.append(db_name)

            remaining -= taken
            db_offset += taken

            if db_offset >= db_count:
                db_idx += 1
                db_offset = 0

        folder_sources.append(", ".join(sources))

    return folder_sources


def generate_bucket_name(length=None):
    if length is None:
        length = random.randint(12, 35)
    return ''.join(random.choices(string.ascii_lowercase, k=length))


def generate_name():
    return random.choice(US_CA_NAMES)


def main(split_size=None):
    config_path = os.path.join(ROOT_DIR, 'config.json')
    with open(config_path, 'r') as f:
        config = json.load(f)

    database_dir = os.path.join(ROOT_DIR, config.get('data_folder', 'database'))
    if split_size is None:
        split_size = config.get('split_size', 5000)

    db_counts = count_snippet_articles_per_db(database_dir)
    total_articles = sum(count for _, count in db_counts)
    if total_articles == 0:
        print("No snippet articles found in database. CREATE.csv not generated.")
        return

    num_folders = math.ceil(total_articles / split_size)
    folder_sources = compute_folder_sources(db_counts, split_size, num_folders)

    csv_path = os.path.join(ROOT_DIR, 'CREATE.csv')

    used_buckets = set()
    rows = []
    for i in range(1, num_folders + 1):
        while True:
            bucket = generate_bucket_name()
            if bucket not in used_buckets:
                used_buckets.add(bucket)
                break
        rows.append({
            'bucket': bucket,
            'foldering': f'folder_{i}',
            'name': generate_name(),
            'source': folder_sources[i - 1],
        })

    with open(csv_path, 'w', newline='', encoding='utf-8') as f:
        writer = csv.DictWriter(f, fieldnames=['bucket', 'foldering', 'name', 'source'])
        writer.writeheader()
        writer.writerows(rows)

    print(f"Generated {num_folders} folder mappings -> CREATE.csv")
    print(f"Total snippet articles: {total_articles}, Split size: {split_size}")
    for db_name, count in db_counts:
        print(f"  {db_name}: {count} snippet articles")


if __name__ == '__main__':
    main()