"""
Create database file and tables if not exists.
::

    +--------------+
    |services      |                 +---------------+
    +--------------+                 |notifications  |
    |service_id    |<--------+       +---------------+
    |token         |         |       |notification_id|
    |name          |         +-------|service_id     |
    |description   |                 |timestamp      |
    |representation|                 |content_json   |
    |by_request    |                 +---------------+
    +--------------+

* **services table**
    * **service_id:**      service id
                               *(integer primary key)*.
    * **token:**           service token for access to api
                               *(text, not null)*.
    * **name:**            service name
                               *(text, not null)*.
    * **description:**     service description
                               *(text with default value)*.
    * **representation:**  how to display notification
                               *(integer with 0 as default)*.
    * **by_request:**      send notifications automatically or not
                               *(integer with 0 as default)*.
* **notifications table**
    * **notification_id:** notification id
                               *(integer primary key)*.
    * **service_id:**      service id
                               *(integer, not null, references to*
                               *service_id in service table)*.
    * **timestamp:**       UTC timestamp, example: **"2022-02-14T13:45:15Z"**
                               *(text, not null)*.

    * **content_json:**    content in json_format
                               *(text, not null)*.
"""
import sqlite3
import logging


CREATE_SERVICES_TABLE = '''CREATE TABLE IF NOT EXISTS services (
service_id INTEGER PRIMARY KEY,
token TEXT NOT NULL,
name TEXT NOT NULL,
description TEXT DEFAULT "Нет описания.",
representation INTEGER DEFAULT 0,
by_request INTEGER DEFAULT 0
);'''

CREATE_NOTIFICATIONS_TABLE = '''CREATE TABLE IF NOT EXISTS notifications ( 
notification_id INTEGER PRIMARY KEY,
service_id INTEGER NOT NULL,
timestamp TEXT NOT NULL,
content_json TEXT NOT NULL,
FOREIGN KEY (service_id) REFERENCES services(service_id) 
ON UPDATE CASCADE ON DELETE CASCADE
);'''


def create_tables_if_not_exists(dbfile: str) -> None:
    """
    Create database file and tables if not exists.
    *See module docstring*.

    :param dbfile: path to database file.
    """
    connection = sqlite3.connect(dbfile)
    cursor = connection.cursor()
    cursor.execute('PRAGMA foreign_keys=0')
    cursor.execute(CREATE_SERVICES_TABLE)
    cursor.execute(CREATE_NOTIFICATIONS_TABLE)
    connection.commit()
    cursor.close()
    connection.close()
    logging.info('Database created!')