import re
import sqlite3

from data import data, warehouse

_WHITESPACE_RE = re.compile(r'\s*[-_,]\s*')
_REMOVE_RE = re.compile(r'(\([\d\s,|]+\)|[^\w\s])')
_IGNORED = frozenset([
  'a', 'as', 'abbr', 'agcy', 'and',
  'for',
  'eg',
  'gp', 'grp',
  'in', 'it',
  'of', 'on', 'or', 'org',
  'that', 'the', 'to',
  'with',
])

def _normalize_clue(clue):
  clue = clue.strip()
  clue = re.sub(_WHITESPACE_RE, ' ', clue)
  clue = re.sub(_REMOVE_RE, '', clue)
  return clue


def tokenize_clue(clue):
  return _normalize_clue(clue.lower()).split()


def clue_keywords(clue):
  words_api = warehouse.get('/api/words')
  results = []
  for keyword in tokenize_clue(clue):
    base_form = words_api.base_form(keyword)
    if base_form:
      keyword = base_form
    if keyword not in _IGNORED and len(keyword) > 1:
      results.append(keyword)
  return results


def connect(db):
  if not db.startswith(':'):
    db = data.project_path(db)
  conn = sqlite3.connect(db)
  return (conn, conn.cursor())


def init(db):
  conn, cursor = connect(db)
  # Erase previous table.
  cursor.execute('DROP TABLE IF EXISTS clues')
  # Create table.
  cursor.execute("""
    CREATE TABLE clues (
      solution TEXT PRIMARY KEY,
      usages INT,
      keywords TEXT
    )
  """)
  conn.commit()
  return conn


def add(cursor, solution, usages, keywords):
  cmd = 'INSERT INTO clues VALUES (?, ?, ?)'
  try:
    cursor.execute(cmd, (solution, usages, _format_keyword_counts(keywords)))
  except (sqlite3.OperationalError, sqlite3.IntegrityError):
    print(cmd, solution, usages, _format_keywords(keywords))
    raise


def query(cursor, clue):
  results = []
  cmd = """
    SELECT solution, usages, keywords
    FROM clues
    WHERE keywords LIKE ?
  """
  like = '%' + _format_keywords_query(clue_keywords(clue)) + '%'
  for solution, usages, keywords in cursor.execute(cmd, (like,)):
    keywords = _query_keywords(keywords)
    results.append((solution, usages, keywords))
  return results


def _format_keywords(keywords):
  return '[%s]' % ']['.join(sorted(keywords))


def _format_keywords_query(keywords):
  return '[%s(' % '(%['.join(sorted(keywords))


def _format_keyword_counts(keywords):
  parts = []
  for kvp in keywords.items():
    parts.append('%s(%s)' % kvp)
  return _format_keywords(parts)


def _query_keywords(keywords):
  parts = keywords.strip('][').split('][')
  keywords = {}
  for part in parts:
    k, v = part.rstrip(')').split('(')
    keywords[k] = int(v)
  return keywords

@@ -17,16 +17,63 @@
  sqlite3
+%0Afrom typing import Dict, Iterable, List, Tuple
 %0A%0Afrom d
@@ -364,16 +364,48 @@
 th',%0A%5D)%0A
+_KeywordsType = Dict%5Bstr, int%5D%0A%0A
 %0Adef _no
@@ -417,25 +417,37 @@
 ze_clue(clue
-)
+: str) -%3E str
 :%0A  clue = c
@@ -573,25 +573,43 @@
 ze_clue(clue
-)
+: str) -%3E List%5Bstr%5D
 :%0A  return _
@@ -665,25 +665,43 @@
 eywords(clue
-)
+: str) -%3E List%5Bstr%5D
 :%0A  words_ap
@@ -986,25 +986,75 @@
 f connect(db
-)
+: str) -%3E Tuple%5Bsqlite3.Connection, sqlite3.Cursor%5D
 :%0A  if not d
@@ -1141,17 +1141,16 @@
  return 
-(
 conn, co
@@ -1160,17 +1160,16 @@
 cursor()
-)
 %0A%0A%0Adef i
@@ -1174,17 +1174,44 @@
  init(db
-)
+: str) -%3E sqlite3.Connection
 :%0A  conn
@@ -1494,22 +1494,43 @@
 def add(
+%0A    
 cursor
+: sqlite3.Cursor
 , soluti
@@ -1527,32 +1527,37 @@
 or, solution
+: str
 , usages
 , keywords):
@@ -1544,26 +1544,50 @@
 , usages
-, keywords
+: str,%0A    keywords: _KeywordsType
 ):%0A  cmd
@@ -1861,23 +1861,84 @@
 y(cursor
-, clue)
+: sqlite3.Cursor, clue: str) -%3E List%5BTuple%5Bstr, int, _KeywordsType%5D%5D
 :%0A  resu
@@ -2304,32 +2304,47 @@
 eywords(keywords
+: Iterable%5Bstr%5D
 ):%0A  return '%5B%25s
@@ -2409,24 +2409,35 @@
 ery(keywords
+: List%5Bstr%5D
 ):%0A  return 
@@ -2503,33 +2503,55 @@
 _counts(keywords
-)
+: _KeywordsType) -%3E str
 :%0A  parts = %5B%5D%0A 
@@ -2676,17 +2676,39 @@
 keywords
-)
+: str) -%3E _KeywordsType
 :%0A  part

Add typing to crossword.py.