# Mycroft Server - Backend
# Copyright (C) 2019 Mycroft AI Inc
# SPDX-License-Identifier: 	AGPL-3.0-or-later
#
# This file is part of the Mycroft Server.
#
# The Mycroft Server is free software: you can redistribute it and/or
# modify it under the terms of the GNU Affero General Public License as
# published by the Free Software Foundation, either version 3 of the
# License, or (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU Affero General Public License for more details.
#
# You should have received a copy of the GNU Affero General Public License
# along with this program. If not, see <https://www.gnu.org/licenses/>.
"""Defines CRUD operations for a user account."""
from datetime import datetime, timedelta
from logging import getLogger
from os import environ
from typing import Optional

from passlib.hash import sha512_crypt

from selene.util.db import DatabaseRequest, use_transaction
from ..entity.account import Account, AccountAgreement, AccountMembership
from ..entity.agreement import OPEN_DATASET
from ...repository_base import RepositoryBase

_log = getLogger(__name__)


def _encrypt_password(password: str) -> str:
    """Encrypts the plain-text password using a secret salt and SHA512 encryption.

    :param password: the plain text password as entered by the user
    :returns: the password value stored in the database.
    """
    salt = environ["SALT"]
    hash_result = sha512_crypt.using(salt=salt, rounds=5000).hash(password)
    hashed_password_index = hash_result.rindex("$") + 1

    return hash_result[hashed_password_index:]


class AccountRepository(RepositoryBase):
    """Collection of methods that access or manipulate the account table."""

    def __init__(self, db):
        super().__init__(db, __file__)
        self.db = db

    @use_transaction
    def add(self, account: Account, password: str) -> str:
        """Adds the account and required agreements to the database.

        :param account: dataclass representation of an account entity
        :param password: password supplied by the user
        :returns: the internal identifier for the account
        """
        account_id = self._add_account(account, password)
        for agreement in account.agreements:
            self.add_agreement(account_id, agreement)
        _log.info("Added account %s", account.email_address)

        return account_id

    def _add_account(self, account: Account, password: str):
        """Inserts a row into the account table.

        :param account: dataclass representation of an account entity
        :param password: password supplied by the user
        :returns: the internal identifier for the account
        """
        if password is None:
            encrypted_password = None
        else:
            encrypted_password = _encrypt_password(password)
        request = self._build_db_request(
            sql_file_name="add_account.sql",
            args=dict(
                email_address=account.email_address,
                password=encrypted_password,
                username=account.username,
            ),
        )
        result = self.cursor.insert_returning(request)

        return result["id"]

    def add_agreement(self, account_id: str, agreement: AccountAgreement):
        """Adds the agreements required at account creation time to the database.

        :param account_id: internal identifier for an account
        :param agreement: data necessary to insert a row into account_agreement table.
        """
        request = self._build_db_request(
            sql_file_name="add_account_agreement.sql",
            args=dict(account_id=account_id, agreement_name=agreement.type),
        )
        self.cursor.insert(request)

    def remove(self, account: Account):
        """Delete and account and all of its children.

        As part of the privacy focus of Mycroft AI, all data related to a user is
        deleted when the account is deleted.  This is enforced in the database using
        cascading deletes on any table with a foreign key to the account table.

        :param account: instance of the account entity.
        """
        request = self._build_db_request(
            sql_file_name="remove_account.sql", args=dict(id=account.id)
        )
        self.cursor.delete(request)
        log_msg = "Deleted account {} and all it's related data"
        _log.info(log_msg.format(account.email_address))

    def get_account_by_id(self, account_id: str) -> Optional[Account]:
        """Use a given uuid to query the database for an account

        :param account_id: uuid
        :return: an account entity, if one is found
        """
        account_id_resolver = "%(account_id)s"
        request = self._build_db_request(
            sql_file_name="get_account.sql",
            args=dict(account_id=account_id),
        )
        request.sql = request.sql.format(account_id_resolver=account_id_resolver)

        return self._get_account(request)

    def get_account_by_email(self, email_address: str) -> Optional[Account]:
        """Retrieves the account using the email address provided by the user at login.

        :param email_address: email address provided by user at login
        :return: the account associated with the given email address
        """
        account_id_resolver = (
            "(SELECT id FROM account.account "
            "WHERE email_address = %(email_address)s)"
        )
        request = self._build_db_request(
            sql_file_name="get_account.sql",
            args=dict(email_address=email_address),
        )
        request.sql = request.sql.format(account_id_resolver=account_id_resolver)

        return self._get_account(request)

    def get_account_from_credentials(
        self, email: str, password: str
    ) -> Optional[Account]:
        """Validates email/password combination against the database

        :param email: the user provided email address
        :param password: the user provided password
        :return: the matching account record, if one is found
        """
        account_id_resolver = (
            "(SELECT id FROM account.account "
            "WHERE email_address = %(email_address)s and password=%(password)s)"
        )
        encrypted_password = _encrypt_password(password)
        request = self._build_db_request(
            sql_file_name="get_account.sql",
            args=dict(email_address=email, password=encrypted_password),
        )
        request.sql = request.sql.format(account_id_resolver=account_id_resolver)

        return self._get_account(request)

    def get_account_by_device_id(self, device_id: str) -> Optional[Account]:
        """Return the account associated with the specified device.

        :param device_id: internal identifier of a Mycroft voice assistant device
        """
        request = self._build_db_request(
            sql_file_name="get_account_by_device_id.sql", args=dict(device_id=device_id)
        )
        return self._get_account(request)

    def _get_account(self, db_request: DatabaseRequest) -> Optional[Account]:
        """Builds an account entity from the database.

        There are many ways an account can be retrieved using differing WHERE clauses.
        The query is built appending one of these WHERE clauses to a common SELECT
        clause.

        :param db_request: the SQL and arguments necessary for the query
        :return: An account entity if a match to the query is found on the database
        """
        account = None
        result = self.cursor.select_one(db_request)

        if result is not None:
            account_agreements = []
            if result["account"]["agreements"] is not None:
                for agreement in result["account"]["agreements"]:
                    account_agreements.append(AccountAgreement(**agreement))
            result["account"]["agreements"] = account_agreements
            if result["account"]["membership"] is not None:
                result["account"]["membership"] = AccountMembership(
                    **result["account"]["membership"]
                )
            if result["account"]["last_activity"] is not None:
                no_milliseconds = len(result["account"]["last_activity"]) == 19
                if no_milliseconds:
                    parse_string = "%Y-%m-%dT%H:%M:%S"
                else:
                    parse_string = "%Y-%m-%dT%H:%M:%S.%f"
                result["account"]["last_activity"] = datetime.strptime(
                    result["account"]["last_activity"], parse_string
                )
            account = Account(**result["account"])

        return account

    def update_password(self, account_id: str, password: str):
        """Changes the password for an account on the database.

        :param account_id: internal account identifier
        :param password: unencrypted password supplied by user
        """
        encrypted_password = _encrypt_password(password)
        db_request = self._build_db_request(
            sql_file_name="change_password.sql",
            args=dict(account_id=account_id, password=encrypted_password),
        )
        self.cursor.update(db_request)

    def update_email_address(self, account_id: str, email_address: str):
        """Update the email address for the specified account ID.

        :param account_id: Internal identifier for a user's account
        :param email_address: The new email address for the account
        """
        db_request = self._build_db_request(
            sql_file_name="change_email_address.sql",
            args=dict(account_id=account_id, email_address=email_address),
        )
        self.cursor.update(db_request)

    def update_username(self, account_id: str, username: str):
        """Changes the username associated with an account.

        :param account_id: internal account identifier
        :param username: the new value of the username
        """
        db_request = self._build_db_request(
            sql_file_name="update_username.sql",
            args=dict(account_id=account_id, username=username),
        )
        self.cursor.update(db_request)

    def expire_open_dataset_agreement(self, account_id: str):
        """Expires the open dataset agreement when the user opts out.

        :param account_id: internal account identifier
        """
        db_request = self._build_db_request(
            sql_file_name="expire_account_agreement.sql",
            args=dict(account_id=account_id, agreement_type=OPEN_DATASET),
        )
        self.cursor.delete(db_request)

    def update_last_activity_ts(self, account_id: str):
        """Updates the user's last activity time when activity is detected.

        :param account_id: internal account identifier
        """
        db_request = self._build_db_request(
            sql_file_name="update_last_activity_ts.sql",
            args=dict(account_id=account_id, last_activity_ts=datetime.utcnow()),
        )

        self.cursor.update(db_request)

    def daily_report(self, date: datetime):
        """Builds a daily report from account data on the database.

        :param date: the date of the report
        """
        base = date - timedelta(days=1)
        end_date = base.strftime("%Y-%m-%d")
        start_date_1_day = (base - timedelta(days=1)).strftime("%Y-%m-%d")
        start_date_15_days = (base - timedelta(days=15)).strftime("%Y-%m-%d")
        start_date_30_days = (base - timedelta(days=30)).strftime("%Y-%m-%d")
        db_request = self._build_db_request(
            sql_file_name="daily_report.sql",
            args=dict(start_date=start_date_1_day, end_date=end_date),
        )
        report_1_day = self.cursor.select_one(db_request)
        db_request = self._build_db_request(
            sql_file_name="daily_report.sql",
            args=dict(start_date=start_date_15_days, end_date=end_date),
        )
        report_15_days = self.cursor.select_one(db_request)
        db_request = self._build_db_request(
            sql_file_name="daily_report.sql",
            args=dict(start_date=start_date_30_days, end_date=end_date),
        )
        report_30_days = self.cursor.select_one(db_request)

        report_table = [
            dict(
                type="User",
                current=report_1_day["total"],
                oneDay=report_1_day["total"] - report_1_day["total_new"],
                oneDayDelta=report_1_day["total_new"],
                oneDayMinus=0,
                fifteenDays=report_15_days["total"] - report_15_days["total_new"],
                fifteenDaysDelta=report_15_days["total_new"],
                fifteenDaysMinus=0,
                thirtyDays=report_30_days["total"] - report_30_days["total_new"],
                thirtyDaysDelta=report_30_days["total_new"],
                thirtyDaysMinus=0,
            ),
            dict(
                type="Free Account",
                current=report_1_day["total"] - report_1_day["paid_total"],
                oneDay=report_1_day["total"]
                - report_1_day["paid_total"]
                - report_1_day["total_new"]
                + report_1_day["paid_new"],
                oneDayDelta=report_1_day["total_new"] - report_1_day["paid_new"],
                oneDayMinus=0,
                fifteenDays=report_15_days["total"]
                - report_15_days["paid_total"]
                - report_15_days["total_new"]
                + report_15_days["paid_new"],
                fifteenDaysDelta=report_15_days["total_new"]
                - report_15_days["paid_new"],
                fifteenDaysMinus=0,
                thirtyDays=report_30_days["total"]
                - report_30_days["paid_total"]
                - report_30_days["total_new"]
                + report_30_days["paid_new"],
                thirtyDaysDelta=report_30_days["total_new"]
                - report_30_days["paid_new"],
                thirtyDaysMinus=0,
            ),
            dict(
                type="Monthly Account",
                current=report_1_day["monthly_total"],
                oneDay=report_1_day["monthly_total"]
                - report_1_day["monthly_new"]
                + report_1_day["monthly_minus"],
                oneDayDelta=report_1_day["monthly_new"],
                oneDayMinus=report_1_day["monthly_minus"],
                fifteenDays=report_15_days["monthly_total"]
                - report_15_days["monthly_new"]
                + report_15_days["monthly_minus"],
                fifteenDaysDelta=report_15_days["monthly_new"],
                fifteenDaysMinus=report_15_days["monthly_minus"],
                thirtyDays=report_30_days["monthly_total"]
                - report_30_days["monthly_new"]
                + report_30_days["monthly_minus"],
                thirtyDaysDelta=report_30_days["monthly_new"],
                thirtyDaysMinus=report_30_days["monthly_minus"],
            ),
            dict(
                type="Yearly Account",
                current=report_1_day["yearly_total"],
                oneDay=report_1_day["yearly_total"]
                - report_1_day["yearly_new"]
                + report_1_day["yearly_minus"],
                oneDayDelta=report_1_day["yearly_new"],
                oneDayMinus=report_1_day["yearly_minus"],
                fifteenDays=report_15_days["yearly_total"]
                - report_15_days["yearly_new"]
                + report_15_days["yearly_minus"],
                fifteenDaysDelta=report_15_days["yearly_new"],
                fifteenDaysMinus=report_15_days["yearly_minus"],
                thirtyDays=report_30_days["yearly_total"]
                - report_30_days["yearly_new"]
                + report_30_days["yearly_minus"],
                thirtyDaysDelta=report_30_days["yearly_new"],
                thirtyDaysMinus=report_30_days["yearly_minus"],
            ),
            dict(
                type="Paid Account",
                current=report_1_day["paid_total"],
                oneDay=report_1_day["paid_total"]
                - report_1_day["paid_new"]
                + report_1_day["paid_minus"],
                oneDayDelta=report_1_day["paid_new"],
                oneDayMinus=report_1_day["paid_minus"],
                fifteenDays=report_15_days["paid_total"]
                - report_15_days["paid_new"]
                + report_15_days["paid_minus"],
                fifteenDaysDelta=report_15_days["paid_new"],
                fifteenDaysMinus=report_15_days["paid_minus"],
                thirtyDays=report_30_days["paid_total"]
                - report_30_days["paid_new"]
                + report_30_days["paid_minus"],
                thirtyDaysDelta=report_30_days["paid_new"],
                thirtyDaysMinus=report_30_days["paid_minus"],
            ),
        ]

        return report_table

    def add_membership(self, acct_id: str, membership: AccountMembership):
        """Adds a membership to the database if one was selected by the user.

        :param acct_id: internal identifier for the account
        :param membership: data used to populate the account_membership table.
        """
        request = self._build_db_request(
            sql_file_name="add_account_membership.sql",
            args=dict(
                account_id=acct_id,
                membership_type=membership.type,
                payment_method=membership.payment_method,
                payment_account_id=membership.payment_account_id,
                payment_id=membership.payment_id,
            ),
        )
        self.cursor.insert(request)

    def end_membership(self, membership: AccountMembership):
        """Expires a membership when a user discontinues payment.

        :param membership: the membership to expire
        """
        db_request = self._build_db_request(
            sql_file_name="end_membership.sql",
            args=dict(
                id=membership.id,
                membership_ts_range=f"[{membership.start_date},{membership.end_date}]",
            ),
        )
        self.cursor.update(db_request)

    def end_active_membership(self, customer_id: str):
        """Expires a membership when a user discontinues payment.

        :param customer_id: Payment system identifier of the user's subscription
        """
        db_request = self._build_db_request(
            sql_file_name="get_active_membership_by_payment_account_id.sql",
            args=dict(payment_account_id=customer_id),
        )
        db_result = self.cursor.select_one(db_request)
        if db_result is not None:
            account_membership = AccountMembership(**db_result)
            account_membership.end_date = datetime.utcnow()
            self.end_membership(account_membership)

    def get_active_account_membership(self, account_id) -> Optional[AccountMembership]:
        """Retrieves an active membership for the given account, if one exists.

        :param account_id: internal account identifier
        :returns: membership information if the account is a member
        """
        account_membership = None
        db_request = self._build_db_request(
            sql_file_name="get_active_membership_by_account_id.sql",
            args=dict(account_id=account_id),
        )
        db_result = self.cursor.select_one(db_request)
        if db_result:
            account_membership = AccountMembership(**db_result)

        return account_membership
