import os
import pandas as pd
from string import Template
from dateutil.parser import parse
import re

import sys

# Metadata in tabular format in a spreadsheet(?!)
xlsx = '../../test/data/10_samples.xlsx'

# Template in a text file
template_yaml = 'template.yaml'

dir_output = 'yaml'

if not os.path.exists(dir_output):
    os.makedirs(dir_output)

table = pd.read_excel(xlsx)

print(table)

for index, row in table.iterrows():
    sample = row['Sample ID']
    print(f"Processing sample {sample}...")

    with open(template_yaml) as f:
      text = Template(f.read())
      with open(os.path.join(dir_output,f"{sample}.yaml"), 'w') as fw:
          sample_id = sample
          sample_name = sample
          collection_date = parse(str(row['Collection Date'])).strftime('%Y-%m-%d')
          locationx = row['City']+", "+row['State']+", USA"
          location = "http://www.wikidata.org/entity/Q16563" # Memphis by default
          map = {
              "Pegram": "http://www.wikidata.org/entity/Q3289517",
              "Alexander": "http://www.wikidata.org/entity/Q79663",
              "Smithville": "http://www.wikidata.org/entity/Q2145339",
              "Nashville": "http://www.wikidata.org/entity/Q23197",
              "Madison": "http://www.wikidata.org/entity/Q494755"
              }

          for name in map:
              p = re.compile(name)
              if p.match(locationx):
                  location = map[name]
                  break

          strain = f"SARS-CoV-2/human/USA/{sample}/2020"
          fw.write(text.substitute(sample_id=sample_id,
                                   sample_name=sample_name,
                                   collection_date=collection_date,
                                   location=location,
                                   locationx=locationx,
                                   strain=strain
                                   ))