"""
After applying all migrations create views.
"""

from yoyo import step
step(
    "CREATE VIEW `apiary_website_logs_summary` AS \
        SELECT `apiary_website_logs`.`website_id` AS `website_id`, \
            count(0) AS `log_count`, \
            max(`apiary_website_logs`.`log_date`) AS `log_date_last`, \
            min(`apiary_website_logs`.`log_date`) AS `lot_date_first` \
            FROM `apiary_website_logs` \
            GROUP BY `apiary_website_logs`.`website_id`;",
    "DROP VIEW IF EXISTS `apiary_website_logs_summary`;"
)

from yoyo import step

step("""
CREATE TABLE movements (
  id     SERIAL,
  token  VARCHAR,
  amount BIGINT
);
    """,
    """DROP TABLE movements;""")

"""
Add not-null col in Ad
"""

from yoyo import step

__depends__ = {'20151208_02_dPGPe-rename-currencys-col-in-ad'}

steps = [
    step("ALTER TABLE ad CHANGE COLUMN site site VARCHAR(30) NOT NULL"),
    step("DELETE FROM ad WHERE site_id is NULL"),
    step("ALTER TABLE ad CHANGE COLUMN site_id site_id VARCHAR(100) NOT NULL"),
    step("ALTER TABLE ad CHANGE COLUMN country country VARCHAR(2) NOT NULL"),
]

"""
Store the raw statistics collected from SMW.

This migration implements part of the manually created Apiary DB
that WikiApiary launched with.
"""

from yoyo import step
step(
    "CREATE TABLE `smwinfo` ( \
        `website_id` int(11) NOT NULL, \
        `capture_date` datetime NOT NULL, \
        `response_timer` float DEFAULT NULL, \
        `propcount` bigint(20) NOT NULL, \
        `proppagecount` int(11) NOT NULL, \
        `usedpropcount` int(11) NOT NULL, \
        `declaredpropcount` int(11) NOT NULL, \
        `querycount` int(11) DEFAULT NULL, \
        `querysize` int(11) DEFAULT NULL, \
        `conceptcount` int(11) DEFAULT NULL, \
        `subobjectcount` int(11) DEFAULT NULL, \
        PRIMARY KEY (`website_id`,`capture_date`) \
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8",
    "DROP TABLE `smwinfo`",
    ignore_errors='rollback', # This table is dropped in migration 20.
)

from yoyo import step, transaction

transaction(
    step("""CREATE TABLE summits_images (
        image varchar(64) PRIMARY KEY,
        preview varchar(64) NOT NULL,
        summit_id integer REFERENCES summits(id),
        main boolean DEFAULT false,
        comment text DEFAULT NULL
        )""")
)

from yoyo import step

step(
    """
    ALTER TABLE User ADD COLUMN registerDate timestamp not null default current_timestamp
    """,

    "ALTER TABLE User DROP COLUMN registerDate",
)

from yoyo import step

step(
    "CREATE TABLE IF NOT EXISTS softban_log (status text, source text, dated datetime DEFAULT CURRENT_TIMESTAMP)"
)

from yoyo import step, transaction

# Removing dummy images downloaded from vk.com as user pictures
# due to bug in vk_get_user and replacing them with NULL to user
# our dummy images instead;

transaction(
    step("UPDATE users SET image=NULL, preview=NULL " +
         "WHERE image='29a62e8bc3609aef88ac2bc722bf7c71f4f86a32.png'"),
    step("DELETE FROM images WHERE " +
         "name='29a62e8bc3609aef88ac2bc722bf7c71f4f86a32.png'"),
    step("DELETE FROM images WHERE " +
         "name='1fc78e0df8d470a82ed55882ac619e7aafa68051.png'")
)

"""Create media migration"""
# pylint: disable=C0103

#
# file: migrations/0002.create-media.py
#
from yoyo import step

step(
    """
    CREATE TABLE media
    (
        id serial,
        media_id text NOT NULL,
        media_created_time timestamp NOT NULL,
        data json NOT NULL,
        created_at timestamp with time zone NOT NULL,
        updated_at timestamp with time zone,
        CONSTRAINT pk_media PRIMARY KEY (id),
        CONSTRAINT uq_media_id UNIQUE (media_id)
    )
    """,
    "DROP TABLE IF EXISTS media CASCADE;",
)

"""
Add description to Ad
"""

from yoyo import step

__depends__ = {'20151217_01_mGj93-add-not-null-col-in-ad'}

steps = [
    step("ALTER TABLE ad ADD description TEXT"),
]

from yoyo import step

step("""
UPDATE nodes
SET is_input=1
WHERE rowid
IN (SELECT input FROM nodes_to_nodes)
""")
step("""
UPDATE nodes
SET is_response=1
WHERE rowid
IN (SELECT response FROM nodes_to_nodes)
""")

''' #this doesn't work in sqlite
step("""UPDATE nodes n 
JOIN nodes_to_nodes n2n 
  ON n.rowid=n2n.response
SET n.is_response=1""",
     "UPDATE nodes SET is_response=0")
step("""UPDATE nodes n 
JOIN nodes_to_nodes n2n 
  ON n.rowid=n2n.input
SET n.is_input=1""",
     "UPDATE nodes SET is_input=0")
'''

that WikiApiary launched with.
"""

from yoyo import step
step(
    "CREATE TABLE `statistics_daily` ( \
        `website_id` int(11) NOT NULL, \
        `website_date` date NOT NULL, \
        `users_min` bigint(20) NOT NULL, \
        `users_max` bigint(20) NOT NULL, \
        `activeusers_max` bigint(20) NOT NULL, \
        `admins_max` bigint(20) NOT NULL, \
        `articles_min` bigint(20) NOT NULL, \
        `articles_max` bigint(20) NOT NULL, \
        `edits_min` bigint(20) NOT NULL, \
        `edits_max` bigint(20) NOT NULL, \
        `jobs_max` bigint(20) NOT NULL, \
        `pages_min` bigint(20) NOT NULL, \
        `pages_max` bigint(20) NOT NULL, \
        `pages_last` bigint(20) NOT NULL, \
        `views_min` bigint(20) NOT NULL, \
        `views_max` bigint(20) NOT NULL, \
        `smw_propcount_min` bigint(20) NOT NULL, \
        `smw_propcount_max` bigint(20) NOT NULL, \
        `smw_proppagecount_last` int(11) NOT NULL, \
        `smw_usedpropcount_last` int(11) NOT NULL, \
        `smw_declaredpropcount_last` int(11) NOT NULL, \
        PRIMARY KEY (`website_id`,`website_date`) \
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8",
    "DROP TABLE `statistics_daily`",
)

from yoyo import step

step(
    '''CREATE TABLE IF NOT EXISTS loc (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name VARCHAR(50) UNIQUE NOT NULL,
        map UNIQUE NOT NULL,
        phone INTEGER UNIQUE NOT NULL,
        fax INTEGER UNIQUE NOT NULL,
        address TEXT UNIQUE NOT NULL,
        city TEXT UNIQUE NOT NULL,
        zipcode INTEGER UNIQUE NOT NUll,
        state TEXT UNIQUE NOT NULL,
        email TEXT UNIQUE NOT NULL)''',
    '''DROP TABLE IF EXISTS loc''',
)

"""
Logging table to keep log entries for related to a specific
website.

This migration implements part of the manually created Apiary DB
that WikiApiary launched with.
"""

from yoyo import step
step(
    "CREATE TABLE `apiary_website_logs` ( \
        `log_id` int(11) NOT NULL AUTO_INCREMENT, \
        `website_id` int(11) NOT NULL, \
        `log_date` datetime NOT NULL, \
        `website_name` varchar(255) NOT NULL, \
        `log_type` varchar(30) NOT NULL, \
        `log_severity` varchar(30) NOT NULL, \
        `log_message` varchar(255) NOT NULL, \
        `log_bot` varchar(30) DEFAULT NULL, \
        `log_url` varchar(255) DEFAULT NULL, \
        PRIMARY KEY (`log_id`), \
        KEY `idx_log_date` (`log_date`) USING BTREE, \
        KEY `idx_website_id_log_date` (`website_id`,`log_date`) USING BTREE \
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8",
    "DROP TABLE `apiary_website_logs`",
)

from yoyo import step

step("""
CREATE TABLE prices (
  id     SERIAL,
  bytes  BIGINT,
  amount BIGINT
);
    """,
    """DROP TABLE prices;""")

"""
Added moveIn to Rent model
"""

from yoyo import step

__depends__ = {'20160408_01_OgXf5-added-rent-model'}

steps = [
    step("ALTER TABLE rent ADD move_in DATE"),
]

from yoyo import step


def create_tables(conn):
    c = conn.cursor()
    c.executescript("""
        CREATE TABLE log (
            timestamp INTEGER,
            source TEXT,
            text TEXT
        );
    """)

step(create_tables)

);

CREATE TABLE IF NOT EXISTS images (
    id serial PRIMARY KEY,
    type varchar(256),
    payload bytea
);

CREATE TABLE IF NOT EXISTS users (
    id serial PRIMARY KEY,
    oauth_id varchar(256) NOT NULL,
    src smallint NOT NULL,
    name varchar(256) NOT NULL,
    email varchar(256),
    img_id int REFERENCES images DEFAULT NULL,
    pub boolean NOT NULL DEFAULT true,
    admin boolean NOT NULL DEFAULT false,
    UNIQUE (oauth_id, src)
);

CREATE TABLE IF NOT EXISTS climbs (
    user_id integer NOT NULL REFERENCES users,
    summit_id integer NOT NULL REFERENCES summits,
    ts date,
    comment text,
    PRIMARY KEY (user_id, summit_id)
);
"""

step(sql)

"""
Added ipHash and createdAt to Rent model
"""

from yoyo import step

__depends__ = {'20160408_02_wg1lz-added-movein-to-rent-model'}

steps = [
    step("ALTER TABLE rent ADD created_at TIMESTAMP NOT NULL DEFAULT now()"),
    step("ALTER TABLE rent ADD ip_hash VARCHAR(512) NOT NULL DEFAULT ''"),
]

"""
This table is used to store property values for websites that may
contain multiple results. For example, the database version number
used in a wiki farm may be different if there are multiple database
backends. Using multiprops we can store all of the various versions
used as well as the time periods involved.

This migration implements part of the manually created Apiary DB
that WikiApiary launched with.
"""

from yoyo import step
step(
    "CREATE TABLE `apiary_multiprops` ( \
        `website_id` int(11) NOT NULL, \
        `t_name` varchar(255) NOT NULL, \
        `t_value` varchar(255) NOT NULL, \
        `first_date` datetime NOT NULL, \
        `last_date` datetime NOT NULL, \
        `occurrences` int(11) NOT NULL, \
        PRIMARY KEY (`website_id`,`t_name`,`t_value`) \
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8",
    "DROP TABLE `apiary_multiprops`",
)