"""Company reference data and dated payroll rates."""
from datetime import date, timedelta


def initialize_settings(conn, teams):
    conn.execute('CREATE TABLE IF NOT EXISTS company_teams (name TEXT PRIMARY KEY, enabled INTEGER NOT NULL DEFAULT 1)')
    for name in teams:
        conn.execute('INSERT INTO company_teams(name) VALUES(?) ON CONFLICT(name) DO NOTHING', (name,))
    conn.execute('''CREATE TABLE IF NOT EXISTS calendar_overrides (
        holiday_date TEXT PRIMARY KEY, name TEXT NOT NULL, non_working INTEGER NOT NULL)''')
    conn.execute('''CREATE TABLE IF NOT EXISTS payroll_rates (
        effective_date TEXT PRIMARY KEY, presence REAL NOT NULL, saturday REAL NOT NULL,
        sunday REAL NOT NULL, standby REAL NOT NULL, intervention_hour REAL NOT NULL,
        created_by INTEGER REFERENCES users(id), created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP)''')
    conn.commit()


def french_holidays(year):
    # Gregorian computus; metropolitan national calendar, excluding regional holidays.
    a = year % 19
    b, c = divmod(year, 100)
    d, e = divmod(b, 4)
    f = (b + 8) // 25
    g = (b - f + 1) // 3
    h = (19*a + b - d - g + 15) % 30
    i, k = divmod(c, 4)
    l = (32 + 2*e + 2*i - h - k) % 7
    m = (a + 11*h + 22*l) // 451
    month, rem = divmod(h + l - 7*m + 114, 31)
    easter = date(year, month, rem + 1)
    result = {date(year,mo,day).isoformat():name for mo,day,name in (
        (1,1,'Jour de l’an'),(5,1,'Fête du Travail'),(5,8,'Victoire 1945'),(7,14,'Fête nationale'),
        (8,15,'Assomption'),(11,1,'Toussaint'),(11,11,'Armistice'),(12,25,'Noël'))}
    for offset,name in ((1,'Lundi de Pâques'),(39,'Ascension'),(50,'Lundi de Pentecôte')):
        result[(easter + timedelta(days=offset)).isoformat()] = name
    return result


def company_holidays(conn, year):
    result = french_holidays(year)
    for row in conn.execute("SELECT holiday_date,name FROM public_holidays WHERE country='FR' AND holiday_date>=? AND holiday_date<=?",
                            (f'{year:04d}-01-01', f'{year:04d}-12-31')).fetchall():
        result[str(row['holiday_date'])] = row['name']
    for row in conn.execute('SELECT * FROM calendar_overrides WHERE holiday_date>=? AND holiday_date<=?',
                            (f'{year:04d}-01-01', f'{year:04d}-12-31')).fetchall():
        if row['non_working']:
            result[str(row['holiday_date'])] = row['name']
        else:
            result.pop(str(row['holiday_date']),None)
    return result


def rate_for(conn, day):
    return conn.execute('SELECT * FROM payroll_rates WHERE effective_date<=? ORDER BY effective_date DESC LIMIT 1',
                        (day.isoformat(),)).fetchone()