"""Extend request categories without losing existing request records."""
from datetime import datetime
from pathlib import Path
import sqlite3


def upgrade_people_contacts(conn):
    fields = ('phone', 'email', 'postal_address', 'address_line1', 'address_line2',
              'postal_code', 'city', 'country', 'emergency_contact_name', 'emergency_contact_phone',
              'birth_date', 'bank_name', 'bank_account', 'bank_account_holder')
    if conn.is_pg:
        for field in fields:
            conn.execute(f"ALTER TABLE users ADD COLUMN IF NOT EXISTS {field} TEXT NOT NULL DEFAULT ''")
            conn.execute(f'ALTER TABLE users ALTER COLUMN {field} TYPE TEXT')
    else:
        existing = {row['name'] for row in conn.execute('PRAGMA table_info(users)').fetchall()}
        for field in fields:
            if field not in existing:
                conn.execute(f"ALTER TABLE users ADD COLUMN {field} TEXT NOT NULL DEFAULT ''")
    conn.commit()


def upgrade_request_types(conn, schema, uri):
    if conn.is_pg:
        conn.execute('ALTER TABLE leave_requests DROP CONSTRAINT IF EXISTS leave_requests_leave_type_check')
        conn.execute("ALTER TABLE leave_requests ADD CONSTRAINT leave_requests_leave_type_check CHECK(leave_type IN ('annual','sick','unplanned','wfh','unpaid','menstrual'))")
        conn.commit()
        return
    definition = conn.execute("SELECT sql FROM sqlite_master WHERE type='table' AND name='leave_requests'").fetchone()['sql']
    if "'unpaid'" in definition and "'menstrual'" in definition:
        return
    backup = Path(uri).with_name(Path(uri).name + '.before-request-types-' + datetime.now().strftime('%Y%m%d%H%M%S') + '.bak')
    with sqlite3.connect(backup) as destination:
        conn.conn.backup(destination)
    create = schema.split('CREATE TABLE IF NOT EXISTS leave_requests (', 1)[1].split(');', 1)[0]
    try:
        conn.execute('BEGIN IMMEDIATE')
        conn.execute('CREATE TABLE leave_requests_upgrade (' + create + ')')
        conn.execute('INSERT INTO leave_requests_upgrade SELECT * FROM leave_requests')
        conn.execute('DROP TABLE leave_requests')
        conn.execute('ALTER TABLE leave_requests_upgrade RENAME TO leave_requests')
        conn.commit()
    except Exception:
        conn.rollback()
        raise


def upgrade_access(conn):
    fields = {'account_enabled': 1, 'must_change_password': 0, 'auth_version': 0,
              'birthday_public': 0, 'birthday_show_age': 0, 'birthday_email': 0, 'anniversary_public': 1}
    existing = set() if conn.is_pg else {r['name'] for r in conn.execute('PRAGMA table_info(users)').fetchall()}
    for field, default in fields.items():
        if conn.is_pg or field not in existing:
            optional = 'IF NOT EXISTS ' if conn.is_pg else ''
            conn.execute(f'ALTER TABLE users ADD COLUMN {optional}{field} INTEGER NOT NULL DEFAULT {default}')
    conn.execute("CREATE TABLE IF NOT EXISTS job_functions (name TEXT PRIMARY KEY, enabled INTEGER NOT NULL DEFAULT 1)")
    conn.execute("CREATE TABLE IF NOT EXISTS module_restrictions (user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE, module TEXT NOT NULL, PRIMARY KEY(user_id,module))")
    conn.execute("INSERT INTO job_functions(name) SELECT DISTINCT TRIM(position_title) FROM users WHERE position_title IS NOT NULL AND TRIM(position_title)<>'' ON CONFLICT(name) DO NOTHING")
    conn.execute('CREATE UNIQUE INDEX IF NOT EXISTS users_username_case_unique ON users(LOWER(username))')
    conn.execute("CREATE UNIQUE INDEX IF NOT EXISTS users_nic_case_unique ON users(LOWER(cin_nic)) WHERE cin_nic IS NOT NULL AND TRIM(cin_nic)<>''")
    conn.commit()