# -*- coding: utf-8 -*-
#
#  Licensed to the Apache Software Foundation (ASF) under one
#  or more contributor license agreements.  See the NOTICE file
#  distributed with this work for additional information
#  regarding copyright ownership.  The ASF licenses this file
#  to you under the Apache License, Version 2.0 (the
#  "License"); you may not use this file except in compliance
#  with the License.  You may obtain a copy of the License at
#
#   http://www.apache.org/licenses/LICENSE-2.0
#
#  Unless required by applicable law or agreed to in writing,
#  software distributed under the License is distributed on an
#  "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
#  KIND, either express or implied.  See the License for the
#  specific language governing permissions and limitations
#  under the License.

"""Tests for multiproduct/dbcursor.py"""

import unittest
from multiproduct.dbcursor import BloodhoundProductSQLTranslate, SKIP_TABLES, TRANSLATE_TABLES, PRODUCT_COLUMN

# Test case data, each section consists of list of tuples of original and correctly translated SQL statements
data = {
    # non-translated SELECTs
    'system_select_nontranslated' : [
        (
"""SELECT id,
               name,
               value
        FROM repository
        WHERE name IN ('alias',
                       'description',
                       'dir',
                       'hidden',
                       'name',
                       'type',
                       'url')""",
"""SELECT id,
               name,
               value
        FROM repository
        WHERE name IN ('alias',
                       'description',
                       'dir',
                       'hidden',
                       'name',
                       'type',
                       'url')"""
        ),
    ],

    # translated SELECTs
    'system_select_translated' : [
        (
"""SELECT TYPE, id,
                     filename,
                     time,
                     description,
                     author
        FROM attachment
        WHERE time > %s
          AND time < %s
          AND TYPE = %s""",
"""SELECT TYPE, id,
                     filename,
                     time,
                     description,
                     author
        FROM (SELECT * FROM attachment WHERE product='PRODUCT') AS attachment
        WHERE time > %s
          AND time < %s
          AND TYPE = %s"""
        ),
        (
"""SELECT name,
               due,
               completed,
               description
        FROM milestone
        WHERE name=%s""",
"""SELECT name,
               due,
               completed,
               description
        FROM (SELECT * FROM milestone WHERE product='PRODUCT') AS milestone
        WHERE name=%s"""
        ),
        (
"""SELECT COALESCE(component, ''),
               count(COALESCE(component, ''))
        FROM ticket
        GROUP BY COALESCE(component, '')""",
"""SELECT COALESCE(component, ''),
               count(COALESCE(component, ''))
        FROM (SELECT * FROM ticket WHERE product='PRODUCT') AS ticket
        GROUP BY COALESCE(component, '')"""
        ),
        (
"""SELECT id, time, reporter, TYPE, summary,
                                         description
        FROM ticket
        WHERE time>=%s
          AND time<=%s""",
"""SELECT id, time, reporter, TYPE, summary,
                                         description
        FROM (SELECT * FROM ticket WHERE product='PRODUCT') AS ticket
        WHERE time>=%s
          AND time<=%s"""
        ),
        (
"""SELECT t.id,
               tc.time,
               tc.author,
               t.type,
               t.summary,
               tc.field,
               tc.oldvalue,
               tc.newvalue
        FROM ticket_change tc
        INNER JOIN ticket t ON t.id = tc.ticket
        AND tc.time>=1351375199999999
        AND tc.time<=1354057199999999
        ORDER BY tc.time""",
"""SELECT t.id,
               tc.time,
               tc.author,
               t.type,
               t.summary,
               tc.field,
               tc.oldvalue,
               tc.newvalue
        FROM (SELECT * FROM ticket_change WHERE product='PRODUCT') AS tc
        INNER JOIN (SELECT * FROM ticket WHERE product='PRODUCT') AS t ON t.id = tc.ticket
        AND tc.time>=1351375199999999
        AND tc.time<=1354057199999999
        ORDER BY tc.time"""
        ),
        (
"""SELECT COUNT(*)
        FROM
          (SELECT t.id AS id,
                  t.summary AS summary,
                  t.owner AS OWNER,
                  t.status AS status,
                  t.priority AS priority,
                  t.milestone AS milestone,
                  t.time AS time,
                  t.changetime AS changetime,
                  priority.value AS priority_value
           FROM ticket AS t
           LEFT OUTER JOIN enum AS priority ON (priority.type='priority'
                                                AND priority.name=priority)
           LEFT OUTER JOIN milestone ON (milestone.name=milestone)
           WHERE ((COALESCE(t.status,'')!=%s)
                  AND (COALESCE(t.OWNER,'')=%s))
           ORDER BY COALESCE(t.milestone,'')='',
                    COALESCE(milestone.completed,0)=0,
                    milestone.completed,
                    COALESCE(milestone.due,0)=0,
                    milestone.due,
                    t.milestone,
                    COALESCE(priority.value,'')='' DESC,CAST(priority.value AS integer) DESC,t.id) AS x""",
"""SELECT COUNT(*)
        FROM
          (SELECT t.id AS id,
                  t.summary AS summary,
                  t.owner AS OWNER,
                  t.status AS status,
                  t.priority AS priority,
                  t.milestone AS milestone,
                  t.time AS time,
                  t.changetime AS changetime,
                  priority.value AS priority_value
           FROM (SELECT * FROM ticket WHERE product='PRODUCT') AS t
           LEFT OUTER JOIN (SELECT * FROM enum WHERE product='PRODUCT') AS priority ON (priority.type='priority'
                                                AND priority.name=priority)
           LEFT OUTER JOIN (SELECT * FROM milestone WHERE product='PRODUCT') AS milestone ON (milestone.name=milestone)
           WHERE ((COALESCE(t.status,'')!=%s)
                  AND (COALESCE(t.OWNER,'')=%s))
           ORDER BY COALESCE(t.milestone,'')='',
                    COALESCE(milestone.completed,0)=0,
                    milestone.completed,
                    COALESCE(milestone.due,0)=0,
                    milestone.due,
                    t.milestone,
                    COALESCE(priority.value,'')='' DESC,CAST(priority.value AS integer) DESC,t.id) AS x"""
        ),
        (
"""SELECT t.id AS id,
               t.summary AS summary,
               t.owner AS OWNER,
               t.status AS status,
               t.priority AS priority,
               t.milestone AS milestone,
               t.time AS time,
               t.changetime AS changetime,
               priority.value AS priority_value
        FROM ticket AS t
        LEFT OUTER JOIN enum AS priority ON (priority.type='priority'
                                             AND priority.name=priority)
        LEFT OUTER JOIN milestone ON (milestone.name=milestone)
        WHERE ((COALESCE(t.status,'')!=%s)
               AND (COALESCE(t.OWNER,'')=%s))
        ORDER BY COALESCE(t.milestone,'')='',
                 COALESCE(milestone.completed,0)=0,
                 milestone.completed,
                 COALESCE(milestone.due,0)=0,
                 milestone.due,
                 t.milestone,
                 COALESCE(priority.value,'')='' DESC,
                 CAST(priority.value AS integer) DESC,t.id""",
"""SELECT t.id AS id,
               t.summary AS summary,
               t.owner AS OWNER,
               t.status AS status,
               t.priority AS priority,
               t.milestone AS milestone,
               t.time AS time,
               t.changetime AS changetime,
               priority.value AS priority_value
        FROM (SELECT * FROM ticket WHERE product='PRODUCT') AS t
        LEFT OUTER JOIN (SELECT * FROM enum WHERE product='PRODUCT') AS priority ON (priority.type='priority'
                                             AND priority.name=priority)
        LEFT OUTER JOIN (SELECT * FROM milestone WHERE product='PRODUCT') AS milestone ON (milestone.name=milestone)
        WHERE ((COALESCE(t.status,'')!=%s)
               AND (COALESCE(t.OWNER,'')=%s))
        ORDER BY COALESCE(t.milestone,'')='',
                 COALESCE(milestone.completed,0)=0,
                 milestone.completed,
                 COALESCE(milestone.due,0)=0,
                 milestone.due,
                 t.milestone,
                 COALESCE(priority.value,'')='' DESC,
                 CAST(priority.value AS integer) DESC,t.id"""
        ),
       (
"""SELECT COUNT(*)
        FROM
          (SELECT p.value AS __color__, id AS ticket, summary, component, VERSION, milestone, t.type AS TYPE, OWNER, status,
                                                                                                                     time AS created,
                                                                                                                     changetime AS _changetime,
                                                                                                                                    description AS _description,
                                                                                                                                                    reporter AS _reporter
           FROM ticket t
           LEFT JOIN enum p ON p.name = t.priority
           AND p.TYPE = 'priority'
           WHERE status <> 'closed'
           ORDER BY CAST(p.value AS integer),
                    milestone,
                    t.TYPE, time ) AS tab""",
"""SELECT COUNT(*)
        FROM
          (SELECT p.value AS __color__, id AS ticket, summary, component, VERSION, milestone, t.type AS TYPE, OWNER, status,
                                                                                                                     time AS created,
                                                                                                                     changetime AS _changetime,
                                                                                                                                    description AS _description,
                                                                                                                                                    reporter AS _reporter
           FROM (SELECT * FROM ticket WHERE product='PRODUCT') AS t
           LEFT JOIN (SELECT * FROM enum WHERE product='PRODUCT') AS p  ON p.name = t.priority
           AND p.TYPE = 'priority'
           WHERE status <> 'closed'
           ORDER BY CAST(p.value AS integer),
                    milestone,
                    t.TYPE, time ) AS tab"""
       ),
        (
"""SELECT COUNT(*)
        FROM
          (SELECT t.id AS id,
                  t.summary AS summary,
                  t.status AS status,
                  t.type AS TYPE,
                  t.priority AS priority,
                  t.product AS product,
                  t.milestone AS milestone,
                  t.time AS time,
                  t.changetime AS changetime,
                  t.owner AS OWNER,
                  priority.value AS priority_value
           FROM ticket AS t
           LEFT OUTER JOIN enum AS priority ON (priority.TYPE='priority'
                                                AND priority.name=priority)
           WHERE ((COALESCE(t.status,'')!=%s)
                  AND (COALESCE(t.OWNER,'')=%s))
           ORDER BY COALESCE(priority.value,'')='',
                                                CAST(priority.value AS integer),
                                                t.id) AS x""",
"""SELECT COUNT(*)
        FROM
          (SELECT t.id AS id,
                  t.summary AS summary,
                  t.status AS status,
                  t.type AS TYPE,
                  t.priority AS priority,
                  t.product AS product,
                  t.milestone AS milestone,
                  t.time AS time,
                  t.changetime AS changetime,
                  t.owner AS OWNER,
                  priority.value AS priority_value
           FROM (SELECT * FROM ticket WHERE product='PRODUCT') AS t
           LEFT OUTER JOIN (SELECT * FROM enum WHERE product='PRODUCT') AS priority ON (priority.TYPE='priority'
                                                AND priority.name=priority)
           WHERE ((COALESCE(t.status,'')!=%s)
                  AND (COALESCE(t.OWNER,'')=%s))
           ORDER BY COALESCE(priority.value,'')='',
                                                CAST(priority.value AS integer),
                                                t.id) AS x"""
        ),
        (
"""SELECT t.id AS id,
               t.summary AS summary,
               t.status AS status,
               t.type AS TYPE,
               t.priority AS priority,
               t.product AS product,
               t.milestone AS milestone,
               t.time AS time,
               t.changetime AS changetime,
               t.owner AS OWNER,
               priority.value AS priority_value
        FROM ticket AS t
        LEFT OUTER JOIN enum AS priority ON (priority.TYPE='priority'
                                             AND priority.name=priority)
        WHERE ((COALESCE(t.status,'')!=%s)
               AND (COALESCE(t.OWNER,'')=%s))
        ORDER BY COALESCE(priority.value,'')='',
                                             CAST(priority.value AS integer),
                                             t.id""",
"""SELECT t.id AS id,
               t.summary AS summary,
               t.status AS status,
               t.type AS TYPE,
               t.priority AS priority,
               t.product AS product,
               t.milestone AS milestone,
               t.time AS time,
               t.changetime AS changetime,
               t.owner AS OWNER,
               priority.value AS priority_value
        FROM (SELECT * FROM ticket WHERE product='PRODUCT') AS t
        LEFT OUTER JOIN (SELECT * FROM enum WHERE product='PRODUCT') AS priority ON (priority.TYPE='priority'
                                             AND priority.name=priority)
        WHERE ((COALESCE(t.status,'')!=%s)
               AND (COALESCE(t.OWNER,'')=%s))
        ORDER BY COALESCE(priority.value,'')='',
                                             CAST(priority.value AS integer),
                                             t.id"""
        ),
        (
"""SELECT *
        FROM
          (SELECT p.value AS __color__, id AS ticket, summary, component, VERSION, milestone, t.type AS TYPE, OWNER, status,
                                                                                                                     time AS created,
                                                                                                                     changetime AS _changetime,
                                                                                                                                    description AS _description,
                                                                                                                                                    reporter AS _reporter
           FROM ticket t
           LEFT JOIN enum p ON p.name = t.priority
           AND p.TYPE = 'priority'
           WHERE status <> 'closed'
           ORDER BY CAST(p.value AS integer),
                    milestone,
                    t.TYPE, time ) AS tab LIMIT 1""",
"""SELECT *
        FROM
          (SELECT p.value AS __color__, id AS ticket, summary, component, VERSION, milestone, t.type AS TYPE, OWNER, status,
                                                                                                                     time AS created,
                                                                                                                     changetime AS _changetime,
                                                                                                                                    description AS _description,
                                                                                                                                                    reporter AS _reporter
           FROM (SELECT * FROM ticket WHERE product='PRODUCT') AS t
           LEFT JOIN (SELECT * FROM enum WHERE product='PRODUCT') AS p  ON p.name = t.priority
           AND p.TYPE = 'priority'
           WHERE status <> 'closed'
           ORDER BY CAST(p.value AS integer),
                    milestone,
                    t.TYPE, time ) AS tab LIMIT 1"""
        ),
        (
"""SELECT p.value AS __color__, id AS ticket, summary, component, VERSION, milestone, t.type AS TYPE, OWNER, status,
                                                                                                                  time AS created,
                                                                                                                  changetime AS _changetime,
                                                                                                                  description AS _description,
                                                                                                                  reporter AS _reporter
        FROM ticket t
        LEFT JOIN enum p ON p.name = t.priority
        AND p.TYPE = 'priority'
        WHERE status <> 'closed'
        ORDER BY CAST(p.value AS integer),
                 milestone,
                 t.TYPE, time""",
"""SELECT p.value AS __color__, id AS ticket, summary, component, VERSION, milestone, t.type AS TYPE, OWNER, status,
                                                                                                                  time AS created,
                                                                                                                  changetime AS _changetime,
                                                                                                                  description AS _description,
                                                                                                                  reporter AS _reporter
        FROM (SELECT * FROM ticket WHERE product='PRODUCT') AS t
        LEFT JOIN (SELECT * FROM enum WHERE product='PRODUCT') AS p ON p.name = t.priority
        AND p.TYPE = 'priority'
        WHERE status <> 'closed'
        ORDER BY CAST(p.value AS integer),
                 milestone,
                 t.TYPE, time"""
        ),
        (
"""SELECT COALESCE(version, '') ,
               count(COALESCE(version, ''))
        FROM
          (SELECT t.id AS id,
                  t.summary AS summary,
                  t.owner AS owner,
                  t.type AS type,
                  t.status AS status,
                  t.priority AS priority,
                  t.milestone AS milestone,
                  t.version AS version,
                  t.time AS time,
                  t.changetime AS changetime,
                  t.product AS product,
                  priority.value AS priority_value
           FROM
             (SELECT *
              FROM ticket
              WHERE product="default") AS t
           LEFT OUTER JOIN
             (SELECT *
              FROM enum
              WHERE product="default") AS priority ON (priority.type='priority'
                                                       AND priority.name=priority)
           LEFT OUTER JOIN
             (SELECT *
              FROM version
              WHERE product="default") AS version ON (version.name=version)
           WHERE ((COALESCE(t.product,'')='default'))
           ORDER BY COALESCE(t.version,'')='',
                    COALESCE(version.time,0)=0,version.time,
                    t.version,COALESCE(priority.value,'')='',
                    CAST(priority.value AS integer),
                    t.id) AS foo
        GROUP BY COALESCE(version, '')""",
"""SELECT COALESCE(version, '') ,
               count(COALESCE(version, ''))
        FROM
          (SELECT t.id AS id,
                  t.summary AS summary,
                  t.owner AS owner,
                  t.type AS type,
                  t.status AS status,
                  t.priority AS priority,
                  t.milestone AS milestone,
                  t.version AS version,
                  t.time AS time,
                  t.changetime AS changetime,
                  t.product AS product,
                  priority.value AS priority_value
           FROM
             (SELECT *
              FROM (SELECT * FROM ticket WHERE product='PRODUCT') AS ticket
              WHERE product="default") AS t
           LEFT OUTER JOIN
             (SELECT *
              FROM (SELECT * FROM enum WHERE product='PRODUCT') AS enum
              WHERE product="default") AS priority ON (priority.type='priority'
                                                       AND priority.name=priority)
           LEFT OUTER JOIN
             (SELECT *
              FROM (SELECT * FROM version WHERE product='PRODUCT') AS version
              WHERE product="default") AS version ON (version.name=version)
           WHERE ((COALESCE(t.product,'')='default'))
           ORDER BY COALESCE(t.version,'')='',
                    COALESCE(version.time,0)=0,version.time,
                    t.version,COALESCE(priority.value,'')='',
                    CAST(priority.value AS integer),
                    t.id) AS foo
        GROUP BY COALESCE(version, '')"""
        ),
        (
"""SELECT w1.name, w1.time, w1.author, w1.text
        FROM wiki w1,(SELECT name, max(version) AS ver
        FROM wiki GROUP BY name) w2
        WHERE w1.version = w2.ver AND w1.name = w2.name
        AND (w1.name LIKE %s ESCAPE '/' OR w1.author LIKE %s ESCAPE '/' OR w1.text LIKE %s ESCAPE '/')""",
"""SELECT w1.name, w1.time, w1.author, w1.text
        FROM (SELECT * FROM wiki WHERE product='PRODUCT') AS w1,(SELECT name, max(version) AS ver
        FROM (SELECT * FROM wiki WHERE product='PRODUCT') AS wiki GROUP BY name)  AS w2
        WHERE w1.version = w2.ver AND w1.name = w2.name
        AND (w1.name LIKE %s ESCAPE '/' OR w1.author LIKE %s ESCAPE '/' OR w1.text LIKE %s ESCAPE '/')"""
        ),
        (
"""INSERT INTO ticket(id, type, time, changetime, component, severity, priority,
                           owner, reporter, cc, version, milestone, status, resolution,
                           summary, description, keywords)
          SELECT id, 'defect', time, changetime, component, severity, priority, owner,
                 reporter, cc, version, milestone, status, resolution, summary,
                 description, keywords FROM ticket_old
          WHERE COALESCE(severity,'') <> 'enhancement'""",
"""INSERT INTO ticket(id, type, time, changetime, component, severity, priority,
                           owner, reporter, cc, version, milestone, status, resolution,
                           summary, description, keywords, product)
          SELECT id, 'defect', time, changetime, component, severity, priority, owner,
                 reporter, cc, version, milestone, status, resolution, summary,
                 description, keywords, 'PRODUCT' FROM (SELECT * FROM "PRODUCT_ticket_old") AS ticket_old
          WHERE COALESCE(severity,'') <> 'enhancement'"""
        ),
        (
"""INSERT INTO ticket(id, type, time, changetime, component, severity, priority,
                               owner, reporter, cc, version, milestone, status, resolution,
                               summary, description, keywords)
              SELECT id, 'enhancement', time, changetime, component, 'normal', priority,
                     owner, reporter, cc, version, milestone, status, resolution, summary,
                     description, keywords FROM ticket_old
              WHERE severity = 'enhancement'""",
"""INSERT INTO ticket(id, type, time, changetime, component, severity, priority,
                               owner, reporter, cc, version, milestone, status, resolution,
                               summary, description, keywords, product)
              SELECT id, 'enhancement', time, changetime, component, 'normal', priority,
                     owner, reporter, cc, version, milestone, status, resolution, summary,
                     description, keywords, 'PRODUCT' FROM (SELECT * FROM "PRODUCT_ticket_old") AS ticket_old
              WHERE severity = 'enhancement'"""
        ),
        (
"""SELECT COUNT(*) FROM (
        SELECT  __color__, __group,
               (CASE
                 WHEN __group = 1 THEN 'Accepted'
                 WHEN __group = 2 THEN 'Owned'
                 WHEN __group = 3 THEN 'Reported'
                 ELSE 'Commented' END) AS __group__,
               ticket, summary, component, version, milestone,
               type, priority, created, _changetime, _description,
               _reporter
        FROM (
         SELECT DISTINCT CAST(p.value AS integer) AS __color__,
              (CASE
                 WHEN owner = %s AND status = 'accepted' THEN 1
                 WHEN owner = %s THEN 2
                 WHEN reporter = %s THEN 3
                 ELSE 4 END) AS __group,
               t.id AS ticket, summary, component, version, milestone,
               t.type AS type, priority, t.time AS created,
               t.changetime AS _changetime, description AS _description,
               reporter AS _reporter
          FROM ticket t
          LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
          LEFT JOIN ticket_change tc ON tc.ticket = t.id AND tc.author = %s
                                        AND tc.field = 'comment'
          WHERE t.status <> 'closed'
                AND (owner = %s OR reporter = %s OR author = %s)
        ) AS sub
        ORDER BY __group, __color__, milestone, type, created

        ) AS tab""",
"""SELECT COUNT(*) FROM (
        SELECT  __color__, __group,
               (CASE
                 WHEN __group = 1 THEN 'Accepted'
                 WHEN __group = 2 THEN 'Owned'
                 WHEN __group = 3 THEN 'Reported'
                 ELSE 'Commented' END) AS __group__,
               ticket, summary, component, version, milestone,
               type, priority, created, _changetime, _description,
               _reporter
        FROM (
         SELECT DISTINCT CAST(p.value AS integer) AS __color__,
              (CASE
                 WHEN owner = %s AND status = 'accepted' THEN 1
                 WHEN owner = %s THEN 2
                 WHEN reporter = %s THEN 3
                 ELSE 4 END) AS __group,
               t.id AS ticket, summary, component, version, milestone,
               t.type AS type, priority, t.time AS created,
               t.changetime AS _changetime, description AS _description,
               reporter AS _reporter
          FROM (SELECT * FROM ticket WHERE product='PRODUCT') AS t
          LEFT JOIN (SELECT * FROM enum WHERE product='PRODUCT') AS p  ON p.name = t.priority AND p.type = 'priority'
          LEFT JOIN (SELECT * FROM ticket_change WHERE product='PRODUCT') AS tc  ON tc.ticket = t.id AND tc.author = %s
                                        AND tc.field = 'comment'
          WHERE t.status <> 'closed'
                AND (owner = %s OR reporter = %s OR author = %s)
        ) AS sub
        ORDER BY __group, __color__, milestone, type, created

        ) AS tab"""
        ),
    ],

    # custom table SELECTs
    'custom_select' : [
        (
"""SELECT bklg_id, count(*) as total
    FROM backlog_ticket
    WHERE tkt_order IS NULL OR tkt_order > -1
    GROUP BY bklg_id
""",
"""SELECT bklg_id, count(*) as total
    FROM (SELECT * FROM "PRODUCT_backlog_ticket") AS backlog_ticket
    WHERE tkt_order IS NULL OR tkt_order > -1
    GROUP BY bklg_id
"""
        ),
        (
"""SELECT bt.bklg_id, t.status, count(*) as total
    FROM backlog_ticket bt, ticket t
    WHERE t.id = bt.tkt_id
    AND (bt.tkt_order IS NULL OR bt.tkt_order > -1)
    GROUP BY bklg_id, status""",
"""SELECT bt.bklg_id, t.status, count(*) as total
    FROM (SELECT * FROM "PRODUCT_backlog_ticket") AS bt, (SELECT * FROM ticket WHERE product='PRODUCT') AS t
    WHERE t.id = bt.tkt_id
    AND (bt.tkt_order IS NULL OR bt.tkt_order > -1)
    GROUP BY bklg_id, status"""
        ),
    ],

    # non-translated INSERTs
    'system_insert_nontranslated' : [
        (
"""INSERT INTO session VALUES (%s,%s,0)""",
"""INSERT INTO session VALUES (%s,%s,0)"""
        ),
    ],

    # translated INSERTs
    'system_insert_translated' : [
        (
"""INSERT INTO ticket_custom (ticket, name, value)
          SELECT id, 'totalhours', '0' FROM ticket WHERE id NOT IN (
            SELECT ticket from ticket_custom WHERE name='totalhours'
          )""",
"""INSERT INTO ticket_custom (ticket, name, value, product)
              SELECT id, 'totalhours', '0', 'PRODUCT' FROM (SELECT * FROM ticket WHERE product='PRODUCT') AS ticket WHERE id NOT IN (
                SELECT ticket from (SELECT * FROM ticket_custom WHERE product='PRODUCT') AS ticket_custom WHERE name='totalhours'
              )"""
        ),
        (
"""INSERT INTO ticket_custom (ticket, name, value)
                    SELECT id, 'totalhours', '0' FROM ticket WHERE id NOT IN (
                    SELECT ticket from ticket_custom WHERE name='totalhours')""",
"""INSERT INTO ticket_custom (ticket, name, value, product)
                        SELECT id, 'totalhours', '0', 'PRODUCT' FROM (SELECT * FROM ticket WHERE product='PRODUCT') AS ticket WHERE id NOT IN (
                        SELECT ticket from (SELECT * FROM ticket_custom WHERE product='PRODUCT') AS ticket_custom WHERE name='totalhours')"""
        ),
        (
"""INSERT INTO session (sid, last_visit, authenticated)
                SELECT distinct s.sid,COALESCE(%s,0),s.authenticated
                FROM session_old AS s LEFT JOIN session_old AS s2
                ON (s.sid=s2.sid AND s2.var_name='last_visit')
                WHERE s.sid IS NOT NULL""",
"""INSERT INTO session (sid, last_visit, authenticated)
                SELECT distinct s.sid,COALESCE(%s,0),s.authenticated
                FROM (SELECT * FROM "PRODUCT_session_old") AS s LEFT JOIN (SELECT * FROM "PRODUCT_session_old") AS s2
                ON (s.sid=s2.sid AND s2.var_name='last_visit')
                WHERE s.sid IS NOT NULL"""
        ),
        (
"""INSERT INTO session_attribute (sid, authenticated, name, value)
        SELECT s.sid, s.authenticated, s.var_name, s.var_value
        FROM session_old s
        WHERE s.var_name <> 'last_visit' AND s.sid IS NOT NULL""",
"""INSERT INTO session_attribute (sid, authenticated, name, value)
        SELECT s.sid, s.authenticated, s.var_name, s.var_value
        FROM (SELECT * FROM "PRODUCT_session_old") AS s
        WHERE s.var_name <> 'last_visit' AND s.sid IS NOT NULL"""
        ),
        (
"""INSERT INTO wiki(version, name, time, author, ipnr, text)
                              SELECT 1 + COALESCE(max(version), 0), %s, %s, 'trac',
                                     '127.0.0.1', %s FROM wiki WHERE name=%s""",
"""INSERT INTO wiki(version, name, time, author, ipnr, text, product)
                              SELECT 1 + COALESCE(max(version), 0), %s, %s, 'trac',
                                     '127.0.0.1', %s, 'PRODUCT' FROM (SELECT * FROM wiki WHERE product='PRODUCT') AS wiki WHERE name=%s"""
        ),
        (
"""INSERT INTO permission VALUES ('dev','WIKI_VIEW')""",
"""INSERT INTO permission VALUES ('dev','WIKI_VIEW','PRODUCT')"""
        ),
        (
"""INSERT INTO permission (username, action) VALUES ('dev','WIKI_VIEW')""",
"""INSERT INTO permission (username, action, product) VALUES ('dev','WIKI_VIEW','PRODUCT')"""
        ),
    ],

    'custom_insert' : [
        (
"""INSERT INTO node_change (rev,path,kind,change,base_path,base_rev)
            SELECT rev,path,kind,change,base_path,base_rev FROM node_change_old""",
"""INSERT INTO node_change (rev,path,kind,change,base_path,base_rev)
            SELECT rev,path,kind,change,base_path,base_rev FROM (SELECT * FROM "PRODUCT_node_change_old") AS node_change_old"""
        ),
    ],

    # translated UPDATEs
    'system_update_translated' : [
        (
"""UPDATE ticket SET changetime=%s WHERE id=%s""",
"""UPDATE ticket SET changetime=%s WHERE product='PRODUCT' AND id=%s"""
        ),
        (
"""UPDATE ticket SET changetime=(
                          SELECT time FROM ticket_change WHERE ticket=%s
                          UNION
                          SELECT time FROM (
                              SELECT time FROM ticket WHERE id=%s LIMIT 1) AS t
                          ORDER BY time DESC LIMIT 1)
                          WHERE id=%s""",
"""UPDATE ticket SET changetime=(
                          SELECT time FROM (SELECT * FROM ticket_change WHERE product='PRODUCT') AS ticket_change WHERE ticket=%s
                          UNION
                          SELECT time FROM (
                              SELECT time FROM (SELECT * FROM ticket WHERE product='PRODUCT') AS ticket WHERE id=%s LIMIT 1) AS t
                          ORDER BY time DESC LIMIT 1)
                          WHERE product='PRODUCT' AND id=%s"""
        ),
        (
"""UPDATE component SET name=%s,owner=%s, description=%s
                          WHERE name=%s""",
"""UPDATE component SET name=%s,owner=%s, description=%s
                          WHERE product='PRODUCT' AND name=%s"""
        ),
        (
"""UPDATE milestone
                          SET name=%s, due=%s, completed=%s, description=%s
                          WHERE name=%s""",
"""UPDATE milestone
                          SET name=%s, due=%s, completed=%s, description=%s
                          WHERE product='PRODUCT' AND name=%s"""
        ),
        (
"""UPDATE wiki
        SET text=%s
            WHERE name=%s""",
"""UPDATE wiki
        SET text=%s
            WHERE product='PRODUCT' AND name=%s"""
        ),
        (
"""UPDATE ticket SET product=%s
                                  WHERE product=%s""",
"""UPDATE ticket SET product=%s
                                  WHERE product='PRODUCT' AND product=%s"""
        ),
        (
"""UPDATE ticket set changetime=%s where id=%s""",
"""UPDATE ticket set changetime=%s where product='PRODUCT' AND id=%s"""
        ),
        (
"""UPDATE
                                milestone
                           SET
                                id_project='%s' WHERE milestone='%s'""",
"""UPDATE
                                milestone
                           SET
                                id_project='%s' WHERE product='PRODUCT' AND milestone='%s'"""
        ),
        (
"""UPDATE ticket_change  SET  newvalue=%s
                               WHERE ticket=%s and author=%s and time=%s and field=%s""",
"""UPDATE ticket_change  SET  newvalue=%s
                               WHERE product='PRODUCT' AND ticket=%s and author=%s and time=%s and field=%s"""
        ),
        (
"""UPDATE ticket_change  SET oldvalue=%s, newvalue=%s
                               WHERE ticket=%s and author=%s and time=%s and field=%s""",
"""UPDATE ticket_change  SET oldvalue=%s, newvalue=%s
                               WHERE product='PRODUCT' AND ticket=%s and author=%s and time=%s and field=%s"""
        ),
        (
"""UPDATE
                                ticket_custom
                              SET
                                value = '%s'
                              WHERE
                                name = 'project' AND value = '%s'""",
"""UPDATE
                                ticket_custom
                              SET
                                value = '%s'
                              WHERE
                                product='PRODUCT' AND name = 'project' AND value = '%s'"""
        ),
    ],

    # non-translated UPDATEs
    'system_update_nontranslated' : [
        (
"""UPDATE  session_attribute
                            SET value='1'
                        WHERE   sid=%s
                            AND name='password_refreshed'""",
"""UPDATE  session_attribute
                            SET value='1'
                        WHERE   sid=%s
                            AND name='password_refreshed'"""
        ),
        (
"""UPDATE  session_attribute
                    SET value=%s""",
"""UPDATE  session_attribute
                    SET value=%s"""
        ),
        (
"""UPDATE  auth_cookie
                            SET time=%s
                        WHERE   cookie=%s""",
"""UPDATE  auth_cookie
                            SET time=%s
                        WHERE   cookie=%s"""
        ),
    ],

    # custom (plugin) table UPDATEs
    'custom_update' : [
        (
"""UPDATE subscription
                       SET format=%s
                     WHERE distributor=%s
                       AND sid=%s
                       AND authenticated=%s""",
"""UPDATE "PRODUCT_subscription"
                       SET format=%s
                     WHERE distributor=%s
                       AND sid=%s
                       AND authenticated=%s"""
        ),
        (
"""UPDATE subscription
                       SET changetime=CURRENT_TIMESTAMP,
                           priority=%s
                     WHERE id=%s""",
"""UPDATE "PRODUCT_subscription"
                       SET changetime=CURRENT_TIMESTAMP,
                           priority=%s
                     WHERE id=%s"""
        ),
        (
"""UPDATE backlog_ticket SET tkt_order = NULL WHERE tkt_id = %s""",
"""UPDATE "PRODUCT_backlog_ticket" SET tkt_order = NULL WHERE tkt_id = %s"""
        ),
        (
"""UPDATE backlog_ticket SET tkt_order = -1
                      WHERE bklg_id = %s
                      AND tkt_id IN
                      (SELECT id FROM ticket
                       WHERE status = 'closed')""",
"""UPDATE "PRODUCT_backlog_ticket" SET tkt_order = -1
                      WHERE bklg_id = %s
                      AND tkt_id IN
                      (SELECT id FROM (SELECT * FROM ticket WHERE product='PRODUCT') AS ticket
                       WHERE status = 'closed')"""
        ),
        (
"""UPDATE backlog_ticket SET tkt_order = -1
                         WHERE bklg_id = %s
                         AND tkt_id IN (SELECT id FROM ticket
                          WHERE status = 'closed')""",
"""UPDATE "PRODUCT_backlog_ticket" SET tkt_order = -1
                         WHERE bklg_id = %s
                         AND tkt_id IN (SELECT id FROM (SELECT * FROM ticket WHERE product='PRODUCT') AS ticket
                          WHERE status = 'closed')"""
        ),
        (
"""UPDATE estimate SET rate=%s, variability=%s, communication=%s, tickets=%s, comment=%s
        WHERE id=%s""",
"""UPDATE "PRODUCT_estimate" SET rate=%s, variability=%s, communication=%s, tickets=%s, comment=%s
        WHERE id=%s"""
        ),
        (
"""UPDATE estimate_line_item SET estimate_id=%s ,
          description=%s, low=%s, high=%s
        WHERE id=%s""",
"""UPDATE "PRODUCT_estimate_line_item" SET estimate_id=%s ,
          description=%s, low=%s, high=%s
        WHERE id=%s"""
        ),
        (
"""UPDATE estimate SET rate=%s, variability=%s, communication=%s, tickets=%s, comment=%s,
           diffcomment=%s, saveepoch=%s
        WHERE id=%s""",
"""UPDATE "PRODUCT_estimate" SET rate=%s, variability=%s, communication=%s, tickets=%s, comment=%s,
           diffcomment=%s, saveepoch=%s
        WHERE id=%s"""
        ),
        (
"""UPDATE estimate_line_item SET estimate_id=%s ,
          description=%s, low=%s, high=%s
        WHERE id=%s""",
"""UPDATE "PRODUCT_estimate_line_item" SET estimate_id=%s ,
          description=%s, low=%s, high=%s
        WHERE id=%s"""
        ),
        (
"""UPDATE estimate SET rate=%s, variability=%s, communication=%s, tickets=%s, comment=%s,
           diffcomment=%s, saveepoch=%s
        WHERE id=%s""",
"""UPDATE "PRODUCT_estimate" SET rate=%s, variability=%s, communication=%s, tickets=%s, comment=%s,
           diffcomment=%s, saveepoch=%s
        WHERE id=%s"""
        ),
    ],

    # custom CREATE TABLE
    'custom_create_table' : [
        (
"""CREATE TABLE estimate(
            id integer PRIMARY KEY,
        rate DECIMAL,
        variability DECIMAL,
        communication DECIMAL,
        tickets VARCHAR(512),
        comment VARCHAR(8000)
    )""",
"""CREATE TABLE "PRODUCT_estimate"(
            id integer PRIMARY KEY,
        rate DECIMAL,
        variability DECIMAL,
        communication DECIMAL,
        tickets VARCHAR(512),
        comment VARCHAR(8000)
    )"""
        ),
        (
"""CREATE TABLE estimate_line_item(
        id integer PRIMARY KEY,
                           estimate_id integer,
                                       description VARCHAR(2048),
                                                   low DECIMAL,
                                                       high DECIMAL
    )""",
"""CREATE TABLE "PRODUCT_estimate_line_item"(
        id integer PRIMARY KEY,
                           estimate_id integer,
                                       description VARCHAR(2048),
                                                   low DECIMAL,
                                                       high DECIMAL
    )"""
        ),
        (
"""CREATE TABLE backlog_ticket (bklg_id INTEGER NOT NULL,"
                                                          " tkt_id INTEGER NOT NULL,"
                                                          " tkt_order REAL,"
                                                          " PRIMARY KEY(bklg_id, tkt_id))""",
"""CREATE TABLE "PRODUCT_backlog_ticket" (bklg_id INTEGER NOT NULL,"
                                                          " tkt_id INTEGER NOT NULL,"
                                                          " tkt_order REAL,"
                                                          " PRIMARY KEY(bklg_id, tkt_id))"""
        ),
        (
"""CREATE TEMPORARY TABLE backlog_ticket (bklg_id INTEGER NOT NULL,"
                                         " tkt_id INTEGER NOT NULL,"
                                         " tkt_order REAL,"
                                         " PRIMARY KEY(bklg_id, tkt_id))""",
"""CREATE TEMPORARY TABLE "PRODUCT_backlog_ticket" (bklg_id INTEGER NOT NULL,"
                                         " tkt_id INTEGER NOT NULL,"
                                         " tkt_order REAL,"
                                         " PRIMARY KEY(bklg_id, tkt_id))"""
        ),
        (
"""CREATE TEMPORARY TABLE table_old AS SELECT * FROM table""",
"""CREATE TEMPORARY TABLE "PRODUCT_table_old" AS SELECT * FROM"""
""" (SELECT * FROM "PRODUCT_table") AS table""",
        ),
    ],

    # custom ALTER TABLE
    'custom_alter_table' : [
        (
"""ALTER TABLE estimate ADD COLUMN diffcomment text""",
"""ALTER TABLE "PRODUCT_estimate" ADD COLUMN diffcomment text"""
        ),
        (
"""ALTER TABLE estimate ADD COLUMN saveepoch int""",
"""ALTER TABLE "PRODUCT_estimate" ADD COLUMN saveepoch int"""
        ),
    ],

    #lowercase select (#548)
    'lowercase_tokens': [
        (
"""select * from ticket""",
"""select * from (SELECT * FROM ticket WHERE product='PRODUCT') AS ticket"""
        ),
        (
"""create temporary table table_old as select * from table""",
"""create temporary table "PRODUCT_table_old" as select * from (SELECT * FROM "PRODUCT_table") AS table""",
        )
    ],
    # insert with specified product (#601)
    'insert_with_product': [
        (
"""INSERT INTO ticket (summary, product) VALUES ('S', 'swlcu')""",
"""INSERT INTO ticket (summary, product, id) VALUES ('S', 'swlcu',COALESCE((SELECT MAX(id) FROM\n(SELECT * FROM ticket WHERE product='PRODUCT')\nAS subquery), 0)+1)"""
        ),
    ],

}

class DbCursorTestCase(unittest.TestCase):
    """Unit tests covering the BloodhoundProductSQLTranslate"""
    def setUp(self):
        self.translator = BloodhoundProductSQLTranslate(SKIP_TABLES, TRANSLATE_TABLES, PRODUCT_COLUMN, 'PRODUCT')
        for section in data.keys():
            if not getattr(self, 'test_%s' % section, None):
                raise Exception("Section '%s' not covered in test case" % section)

    def tearDown(self):
        pass

    def _run_test(self, section):
        for (sql, translated_sql_check) in data[section]:
            translated_sql = self.translator.translate(sql)
            stripped_sql_check = '\n'.join([l.strip() for l in translated_sql_check.splitlines()])
            stripped_translated_sql = '\n'.join([l.strip() for l in translated_sql.splitlines()])
            self.assertEquals(stripped_sql_check, stripped_translated_sql)

    def test_system_select_nontranslated(self):
        self._run_test('system_select_nontranslated')

    def test_system_select_translated(self):
        self._run_test('system_select_translated')

    def test_custom_select(self):
        self._run_test('custom_select')

    def test_system_insert_nontranslated(self):
        self._run_test('system_insert_nontranslated')

    def test_system_insert_translated(self):
        self._run_test('system_insert_translated')

    def test_custom_insert(self):
        self._run_test('custom_insert')

    def test_system_update_translated(self):
        self._run_test('system_update_translated')

    def test_system_update_nontranslated(self):
        self._run_test('system_update_nontranslated')

    def test_custom_update(self):
        self._run_test('custom_update')

    def test_custom_create_table(self):
        self._run_test('custom_create_table')

    def test_custom_alter_table(self):
        self._run_test('custom_alter_table')

    def test_lowercase_tokens(self):
        self._run_test('lowercase_tokens')

    def test_insert_with_product(self):
        self._run_test('insert_with_product')

if __name__ == '__main__':
    unittest.main()
