"""
# Leads processing

Here is a basic example of how to handle leads using webhooks. The main idea is
to use webhooks as a signal for syncing leads, not loading them.

Please ensure that your webhook endpoint is fast enough. Do not load all leads
in the webhook handler. The LID webhooks sender uses a 20-second timeout. It is
better to make the full sync of leads a separate job. Or do an initial import
with a console program.

This script has two entrypoints. First, it is a Flask application that listens
to webhooks from the LID app and dispatches the tasks to the Celery worker to
load the leads. To start the development server, run the following command::

    $ FLASK_APP=leads.py flask run

Second, it's a Celery worker that gets tasks from the local Redis queue. The
tasks are comprised of sending requests to the LID API to get the latest leads
and storing them in the local SQLite database. It is preferred that only one
worker is loading the leads to avoid the races between two and more workers. To
start the worker, run the following command::

    $ celery --app leads.celery worker --pool solo --concurrency 1
"""

import datetime as dt
import json
import sqlite3

import click
import cub
import cub.config
from celery import Celery
from celery.utils.log import get_task_logger
from flask import Flask, jsonify, request
from flask.cli import with_appcontext

app = Flask(__name__)
app.app_context

celery = Celery(
    __name__,
    backend='redis://127.0.0.1:6379/0',
    broker='redis://127.0.0.1:6379/0',
)
celery_logger = get_task_logger(__name__)

# Set a real secret API key here.
cub.config.api_key = 'sk_xxxxxxxxxxxxxxxxx'


@app.cli.add_command
@click.command('init-db')
@with_appcontext
def init_db_command():
    """Create the tables unless they exist."""
    with get_db() as conn:
        create_lead_table(conn)
    click.echo('Initialized the database.')


@app.route('/webhook', methods=['POST'])
def webhook():
    payload = request.data
    obj = cub.objects_from_json(payload)
    if not isinstance(obj, cub.models.Lead):
        # Not interested in anything but leads.
        return jsonify(success=True)

    load_leads.delay(webhook_lead_created_time=obj.created.isoformat())
    return jsonify(success=True)


@celery.task(bind=True, ignore_result=True, max_retries=5)
def load_leads(self, *, webhook_lead_created_time):
    with get_db() as conn:
        webhook_lead_created_time = dt.datetime.fromisoformat(webhook_lead_created_time)
        last_lead_created_time = get_last_lead_created_time(conn)
        if webhook_lead_created_time < last_lead_created_time:
            # Not interested in leads that we should already have in the DB.
            celery_logger.info("Got an old lead %s", webhook_lead_created_time)
            return

        count = 50
        params = {
            'count': count,
            'order_by': 'created',
            'expand': 'organization__state__country',
        }
        from_date = last_lead_created_time.timestamp()

        while True:
            try:
                leads = cub.models.Lead.list(created__gt=from_date, **params)
            except cub.CubException as exc:
                celery_logger.exception(exc)
                raise self.retry(exc=exc, countdown=5 * 60)

            for lead in leads:
                from_date = max(from_date, lead.created.timestamp())
                save_lead(conn, lead)

            if len(leads) < count:
                break


def get_db():
    return sqlite3.connect('leads.db')


def get_last_lead_created_time(conn):
    """Return the creation date of the last lead in the local database."""
    c = conn.cursor()
    c.execute('SELECT MAX(created) FROM lead LIMIT 1')
    (max_date_str,) = c.fetchone()
    if max_date_str is None:
        return dt.datetime(1970, 1, 1, tzinfo=dt.timezone.utc)
    max_date = dt.datetime.fromisoformat(max_date_str)
    return max_date


def save_lead(conn, lead):
    organization = json.dumps(lead.organization) if lead.organization else None
    params = {
        'uid': lead.id,
        'data': json.dumps(lead.data),
        'created': lead.created,
        'site': lead.site,
        'form': lead.form,
        'remote_ip': lead.remote_ip,
        'field_labels': json.dumps(lead.field_labels),
        'url': lead.url,
        'email': lead.email,
        'organization': organization,
        'source': lead.source,
    }
    try:
        conn.execute(
            """\
            INSERT INTO lead (
                uid, data, created, site, form, remote_ip, field_labels,
                url, email, organization, source
            )
            VALUES (
                :uid, :data, :created, :site, :form, :remote_ip, :field_labels,
                :url, :email, :organization, :source
            )
            """,
            params,
        )
        conn.commit()
    except sqlite3.IntegrityError as err:
        if str(err) == 'UNIQUE constraint failed: lead.uid':
            conn.execute(
                """\
                UPDATE lead
                SET data = :data, created = :created, site = :site,
                    form = :form, remote_ip = :remote_ip,
                    field_labels = :field_labels, url = :url, email = :email,
                    organization = :organization, source = :source
                WHERE uid = :uid
                """,
                params,
            )
            conn.commit()
            return
        raise


def create_lead_table(conn):
    conn.execute("""\
        CREATE TABLE IF NOT EXISTS lead
        (
            uid VARCHAR(20) PRIMARY KEY,
            data LONGTEXT NOT NULL,
            created DATETIME(6) NOT NULL,
            site VARCHAR(20) NOT NULL,
            form VARCHAR(20) NOT NULL,
            remote_ip VARCHAR(45) NOT NULL,
            field_labels LONGTEXT NOT NULL,
            url VARCHAR(2083) NOT NULL,
            email VARCHAR(254) NOT NULL,
            organization LONGTEXT NULL,
            source VARCHAR(20) NULL,
            CONSTRAINT uid UNIQUE (uid)
        )
    """)

@@ -133,43 +133,40 @@
 nal 
-for syncing leads, not loading them
+to start pulling the newer leads
 .%0D%0A%0D
@@ -274,65 +274,255 @@
 er. 
-The LID webhooks sender uses a 20-
+If the webhook endpoint doesn't respond in 20 
 second
- timeout. It is%0D%0A
+s,%0D%0ALID drops the connection and resends the webhook later several times for the%0D%0Anext 24 hours. LID does the same if the webhook endpoint responds with an HTTP%0D%0Astatus of 400 and greater. It is 
 bett
@@ -556,17 +556,18 @@
  leads a
- 
+%0D%0A
 separate
@@ -595,18 +595,17 @@
 l import
-%0D%0A
+ 
 with a c

Add more info about webhook retries