import os, subprocess

from config.settings import *
from settings import *
import reports
import util.db


def needed_indexes_all():
    for SCHEMA_NAME in [dir[1] for dir in os.walk(MONITOR_PATH_NAME)][0]:
        if SCHEMA_NAME == "server":
            continue
        db = util.db.connect()

        cur = db.cursor()
        execute_statement = """
            SELECT dbname AS database,
                   pretty_table_name(suts.schemaname::text, suts.relname::text) AS tablename,
                   seq_scan as table_scans,
                   idx_scan as index_scans,
                   pg_size_pretty(tss.pg_total_relation_size) as table_size,
                   n_tup_ins + n_tup_del + n_tup_upd + n_tup_hot_upd as write_activity
            FROM {monitor}.pg_stat_user_tables_start suts
            JOIN {monitor}.table_size_start tss USING(relid)
            WHERE seq_scan > 1000
            AND seq_scan > ( idx_scan / 10 )
            AND tss.pg_total_relation_size > ( 16000000 )
            ORDER BY tss.pg_total_relation_size desc
            """.format(monitor = SCHEMA_NAME)
        cur.execute(execute_statement)

        reports.text_report(cur, REPORT_DIR_PATH + '/needed-indexes-all.csv')

def needed_indexes_run():
    for SCHEMA_NAME in [dir[1] for dir in os.walk(MONITOR_PATH_NAME)][0]:
        if SCHEMA_NAME == "server":
            continue
        db = util.db.connect()

        cur = db.cursor()
        execute_statement = """
            SELECT suts.dbname AS database,
                   pretty_table_name(suts.schemaname::text, suts.relname::text) AS tablename,
                   suts.seq_scan as table_scans_all,
                   sute.seq_scan - suts.seq_scan as table_scans_run,
                   suts.idx_scan as index_scans,
                   pg_size_pretty(tss.pg_total_relation_size) as table_size,
                   suts.n_tup_ins + suts.n_tup_del + suts.n_tup_upd + suts.n_tup_hot_upd as write_activity
            FROM {monitor}.pg_stat_user_tables_start suts
            JOIN {monitor}.table_size_start tss USING(relid)
            JOIN {monitor}.pg_stat_user_tables_end sute USING(relid)
            WHERE suts.seq_scan > 1000
            AND (sute.seq_scan - suts.seq_scan) >5
            AND suts.seq_scan > ( suts.idx_scan / 10 )
            AND tss.pg_total_relation_size > ( 16000000 )
            ORDER BY tss.pg_total_relation_size desc
            """.format(monitor = SCHEMA_NAME)
        cur.execute(execute_statement)

        reports.text_report(cur, REPORT_DIR_PATH + '/needed-indexes-run.csv')


def unused_indexes():

    for SCHEMA_NAME in [dir[1] for dir in os.walk(MONITOR_PATH_NAME)][0]:
        if SCHEMA_NAME == "server":
            continue

        db = util.db.connect()

        cur = db.cursor()
        execute_statement = """
            WITH table_scans as (
                SELECT relid,
                    COALESCE(ends.idx_scan + ends.seq_scan,0) - COALESCE(starts.idx_scan + starts.seq_scan,0) as all_scans,
                    ( ends.n_tup_ins + ends.n_tup_upd + ends.n_tup_del )
                        - ( starts.n_tup_ins + starts.n_tup_upd + starts.n_tup_del )
                            as writes,
                            pg_relation_size as table_bytes,
                            pg_total_relation_size as total_table_bytes
                    FROM {monitor}.pg_stat_user_tables_start as starts
                        JOIN {monitor}.table_size_start as sizes
                            USING (relid)
                        JOIN {monitor}.pg_stat_user_tables_end as ends
                            USING (relid)
            ),
            all_writes as (
                SELECT sum(writes) as total_writes
                FROM table_scans
            ),
            dbsize as (
                SELECT sum(total_table_bytes) as db_bytes
                FROM table_scans
            ),
            indexes as (
                SELECT idx_stat.relid, idx_stat.indexrelid,
                    idx_stat.schemaname, idx_stat.dbname as database, idx_stat.relname as tablename,
                    idx_stat.indexrelname as indexname,
                    (idx_ends.idx_scan - idx_stat.idx_scan) as idx_scan,
                    index_size as index_bytes,
                    indexdef ~* 'USING btree' AS idx_is_btree
                FROM {monitor}.pg_stat_user_indexes_start as idx_stat
                    JOIN {monitor}.pg_stat_user_indexes_end as idx_ends
                        USING (indexrelid)
                    JOIN {monitor}.pg_index_start
                        USING (indexrelid)
                    JOIN {monitor}.pg_index_size_start
                        USING (indexrelid)
                    JOIN {monitor}.pg_indexes_start as indexes
                        ON idx_stat.schemaname = indexes.schemaname
                            AND idx_stat.relname = indexes.tablename
                            AND idx_stat.indexrelname = indexes.indexname
                WHERE {monitor}.pg_index_start.indisunique = FALSE AND NOT indisvalid
            ),
            index_ratios AS (
            SELECT schemaname, tablename, indexname,
                idx_scan, all_scans,
                round(( CASE WHEN all_scans = 0 THEN 0.0::NUMERIC
                    ELSE idx_scan::NUMERIC/all_scans * 100 END),2) as index_scan_pct,
                writes,
                round((CASE WHEN writes = 0 THEN idx_scan::NUMERIC ELSE idx_scan::NUMERIC/writes END),2)
                    as scans_per_write,
                pg_size_pretty(index_bytes) as index_size,
                pg_size_pretty(table_bytes) as table_size,
                idx_is_btree, index_bytes, table_bytes
                FROM indexes
                JOIN table_scans USING (relid)
            ),
            index_groups AS (
            SELECT 'Never Used Indexes' as reason, *, 1 as grp
            FROM index_ratios
            WHERE
                idx_scan = 0
                and idx_is_btree
                and index_bytes > 100000
            UNION ALL
            SELECT 'Low Scans, High Writes' as reason, index_ratios.*, 2 as grp
            FROM index_ratios, all_writes
            WHERE
                scans_per_write <= 1
                AND index_scan_pct < 10
                AND idx_scan > 0
                AND writes > 100
                AND ( writes::NUMERIC / total_writes ) > 0.01
                AND idx_is_btree
            UNION ALL
            SELECT 'Seldom Used Large Indexes' as reason, index_ratios.*, 3 as grp
            FROM index_ratios, dbsize
            WHERE
                index_scan_pct < 5
                AND scans_per_write > 1
                AND idx_scan > 0
                AND idx_is_btree
                AND index_bytes > 1000000
                AND ( index_bytes::NUMERIC / db_bytes ) >= 0.01
            UNION ALL
            SELECT 'High-Write Large Non-Btree' as reason, index_ratios.*, 4 as grp
            FROM index_ratios, all_writes, dbsize
            WHERE
                ( writes::NUMERIC / total_writes ) > 0.02
                AND writes > 100
                AND NOT idx_is_btree
                AND index_bytes > 1000000
                AND ( index_bytes::NUMERIC / db_bytes ) >= 0.01
            ORDER BY grp, index_bytes DESC )
            SELECT reason, schemaname, tablename, indexname,
                index_scan_pct, scans_per_write, index_size, table_size
            FROM index_groups;
            """.format(monitor=SCHEMA_NAME)

        cur.execute(execute_statement)

        reports.text_report(cur, REPORT_DIR_PATH + '/unused-indexes.csv')


def duplicate_indexes():

    for SCHEMA_NAME in [dir[1] for dir in os.walk(MONITOR_PATH_NAME)][0]:
        if SCHEMA_NAME == "server":
            continue

        db = util.db.connect()

        cur = db.cursor()
        cur.execute("SET search_path=%s, public, server"%SCHEMA_NAME)
        execute_statement = """-- check for containment
-- i.e. index A contains index B
-- and both share the same first column
-- but they are NOT identical

WITH index_cols_ord as (
    SELECT attrelid, attnum, attname
    FROM pg_attribute_start
        JOIN pg_index_start ON indexrelid = attrelid
    WHERE indkey[0] > 0
    ORDER BY attrelid, attnum
),
index_col_list AS (
    SELECT attrelid,
        array_agg(attname) as cols
    FROM index_cols_ord
    GROUP BY attrelid
),
dup_natts AS (
SELECT indrelid, indexrelid
FROM pg_index_start as ind
WHERE EXISTS ( SELECT 1
    FROM pg_index_start as ind2
    WHERE ind.indrelid = ind2.indrelid
    AND ( ind.indkey @> ind2.indkey
     OR ind.indkey <@ ind2.indkey )
    AND ind.indkey[0] = ind2.indkey[0]
    AND ind.indkey <> ind2.indkey
    AND ind.indexrelid <> ind2.indexrelid
) )
SELECT userdex.schemaname as schema_name,
    userdex.dbname as dbname,
    userdex.relname as table_name,
    userdex.indexrelname as index_name,
    array_to_string(cols, ', ') as index_cols,
    indexdef,
    idx_scan as index_scans
FROM pg_stat_user_indexes_start as userdex
    JOIN index_col_list ON index_col_list.attrelid = userdex.indexrelid
    JOIN dup_natts ON userdex.indexrelid = dup_natts.indexrelid
    JOIN pg_indexes_start ON userdex.schemaname = pg_indexes_start.schemaname
        AND userdex.indexrelname = pg_indexes_start.indexname
	AND userdex.dbname=pg_indexes_start.dbname
ORDER BY userdex.dbname, userdex.schemaname, userdex.relname, cols, userdex.indexrelname;

        """.format(monitor=SCHEMA_NAME)

        cur.execute(execute_statement)

        reports.text_report(cur, REPORT_DIR_PATH + '/duplicate_indexes.csv')

