#!/bin/bash

species=$(basename "$1");
species="${species/.fasta/}";
species="${species/_/ }";

echo "INSERT INTO \`species\` (\`name\`) VALUES ('${species}');";
echo "SET @speciesId = LAST_INSERT_ID();";

awk -F'[ ,]' '
/^>/ {
  if (NR > 1) {
    printf "\nINSERT INTO `gene_sequence` (`geneId`, `version`, `sequence`) VALUES (%s, %d, \x27%s\x27);", geneId, geneVersions[geneId], sequence;
    sequence = "";
  }

  geneId = substr($1, 2);
  
  if (geneVersions[geneId]) {
    geneVersions[geneId] = geneVersions[geneId] + 1;
  } else {
    geneVersions[geneId] = 1;
    printf "\nINSERT INTO `gene` (`id`, `speciesId`) VALUES (%s, @speciesId);", geneId;
  }
}
/[A-Z]+/ {
  sequence = sequence $1;
}

END {
  printf "\nINSERT INTO `gene_sequence` (`geneId`, `version`, `sequence`) VALUES (%s, %d, \x27%s\x27);", geneId, geneVersions[geneId], sequence;
}' $1;
