#!/usr/bin/env python

# Generate N random Bird_nests rows, including a padding column.
# Write a CSV file to stdout.  Existing database is needed to pick up
# foreign key values.
#
# Usage: generate-rows db_file N

import csv
from random import seed, randrange, choice
import sqlite3
from string import ascii_letters
import sys

seed("meds213")  # for consistency across platforms

db_file = sys.argv[1]
num_rows = int(sys.argv[2])

conn = sqlite3.connect(db_file)
c = conn.cursor()

sites = [t[0] for t in c.execute("SELECT Code FROM Site")]
species_list = [t[0] for t in c.execute("SELECT Code FROM Species")]
observers = [t[0] for t in c.execute("SELECT Abbreviation FROM Personnel")]

w = csv.writer(sys.stdout)
for n in range(num_rows):
    book_page = f"b{randrange(1, 100)}.{randrange(1, 100)}"
    year = str(randrange(1950, 2016))
    site = choice(sites)
    nest_id = f"gen{n}"
    species = choice(species_list)
    observer = choice(observers)
    date_found = f"{year}-{randrange(1, 13):02}-{randrange(1, 29):02}"
    how_found = choice(["searcher", "rope", "bander"])
    padding = "".join(choice(ascii_letters) for _ in range(3000))
    clutch_max = str(randrange(21))
    float_age = ""
    age_method = choice(["float", "lay", "hatch"])
    if age_method == "float":
        float_age = str(randrange(31))
    w.writerow(
        [
            book_page,
            year,
            site,
            nest_id,
            species,
            observer,
            date_found,
            how_found,
            padding,
            clutch_max,
            float_age,
            age_method
        ]
    )