import hashlib
from unittest import SkipTest

import django
import pytest
from django.db import connection
from django.db.models import F, FloatField, Q, Value
from django.db.models.functions import Length, Lower, Upper
from django.test import TestCase

from django_mysql.models.functions import (
    CRC32,
    ELT,
    MD5,
    SHA1,
    SHA2,
    Abs,
    AsType,
    Ceiling,
    ColumnAdd,
    ColumnDelete,
    ColumnGet,
    ConcatWS,
    Field,
    Floor,
    Greatest,
    If,
    JSONArrayAppend,
    JSONExtract,
    JSONInsert,
    JSONKeys,
    JSONLength,
    JSONReplace,
    JSONSet,
    LastInsertId,
    Least,
    RegexpInstr,
    RegexpReplace,
    RegexpSubstr,
    Round,
    Sign,
    UpdateXML,
    XMLExtractValue,
)
from django_mysql.utils import connection_is_mariadb
from tests.testapp.models import Alphabet, Author, DynamicModel, JSONModel
from tests.testapp.test_dynamicfield import DynColTestCase
from tests.testapp.test_jsonfield import JSONFieldTestCase


class ComparisonFunctionTests(TestCase):
    def test_greatest(self):
        Alphabet.objects.create(d="A", e="B")
        ab = Alphabet.objects.annotate(best=Greatest("d", "e")).first()
        assert ab.best == "B"

    def test_greatest_takes_no_kwargs(self):
        with pytest.raises(TypeError):
            Greatest("a", something="wrong")

    def test_
least(self):
        Alphabet.objects.create(a=1, b=2, c=-1)
        ab = Alphabet.objects.annotate(worst=Le
ast("a", "b", "c")).first()
        assert ab.worst == -1


class ControlFlowFunctionTests(TestCase):
    def test_if_basic(self):
        Alphabet.objects.create(d="String")
        Alphabet.objects.create(d="")
        Alphabet.objects.create(d="String")
        Alphabet.objects.create(d="")

        results = list(
            Alphabet.objects.annotate(has_d=If(Length("d"), Value(True), Value(False)))
            .order_by("id")
            .values_list("has_d", flat=True)
        )
        assert results == [True, False, True, False]

    def test_if_with_Q(self):
        Alphabet.objects.create(a=12, b=17)
        Alphabet.objects.create(a=13, b=17)
        Alphabet.objects.create(a=14, b=17)

        result = list(
            Alphabet.objects.annotate(conditional=If(Q(a__lte=13), "a", "b"))
            .order_by("id")
            .values_list("conditional", flat=True)
        )
        assert result == [12, 13, 17]

    def test_if_with_string_values(self):
        Alphabet.objects.create(a=1, d="Lentils")
        Alphabet.objects.create(a=2, d="Cabbage")
        Alphabet.objects.create(a=3, d="Rice")

        result = list(
            Alphabet.objects.annotate(conditional=If(Q(a=2), Upper("d"), Lower("d")))
            .order_by("id")
            .values_list("conditional", flat=True)
        )
        assert result == ["lentils", "CABBAGE", "rice"]

    def test_if_field_lookups_work(self):
        Alphabet.objects.create(a=1, d="Lentils")
        Alphabet.objects.create(a=2, d="Cabbage")
        Alphabet.objects.create(a=3, d="Rice")

        result = list(
            Alphabet.objects.annotate(
                conditional=If(Q(a__gte=2), Upper("d"), Value(""))
            )
            .filter(conditional__startswith="C")
            .order_by("id")
            .values_list("conditional", flat=True)
        )
        assert result == ["CABBAGE"]

    def test_if_false_default_None(self):
        Alphabet.objects.create(a=1)

        result = list(
            Alphabet.objects.annotate(conditional=If(Q(a=2), Value(1)))
            .filter(conditional__isnull=True)
            .values_list("conditional", flat=True)
        )
        assert result == [None]


class NumericFunctionTests(TestCase):
    def test_abs(self):
        Alphabet.objects.create(a=-2)
        ab = Alphabet.objects.annotate(aaa=Abs("a")).first()
        assert ab.aaa == 2

    def test_ceiling(self):
        Alphabet.objects.create(g=0.5)
        ab = Alphabet.objects.annotate(gceil=Ceiling("g")).first()
        assert ab.gceil == 1

    def test_crc32(self):
        Alphabet.objects.create(d="AAAAAA")
        ab = Alphabet.objects.annotate(crc=CRC32("d")).first()
        # Precalculated this in MySQL prompt. Python's binascii.crc32 doesn't
        # match - maybe sign issues?
        assert ab.crc == 2854018686

    def test_crc32_only_takes_one_arg_no_kwargs(self):
        with pytest.raises(TypeError):
            CRC32("d", "c")

        with pytest.raises(TypeError):
            CRC32("d", something="wrong")

    def test_floor(self):
        Alphabet.objects.create(g=1.5)
        ab = Alphabet.objects.annotate(gfloor=Floor("g")).first()
        assert ab.gfloor == 1

    def test_round(self):
        Alphabet.objects.create(g=24.459)
        ab = Alphabet.objects.annotate(ground=Round("g")).get()
        assert ab.ground == 24

    def test_round_up(self):
        Alphabet.objects.create(g=27.859)
        ab = Alphabet.objects.annotate(ground=Round("g")).get()
        assert ab.ground == 28

    def test_round_places(self):
        Alphabet.objects.create(a=81731)
        ab = Alphabet.objects.annotate(around=Round("a", -2)).get()
        assert ab.around == 81700

    def test_sign(self):
        Alphabet.objects.create(a=123, b=0, c=-999)
        ab = Alphabet.objects.annotate(
            asign=Sign("a"), bsign=Sign("b"), csign=Sign("c")
        ).first()
        assert ab.asign == 1
        assert ab.bsign == 0
        assert ab.csign == -1


class StringFunctionTests(TestCase):
    def test_concat_ws(self):
        Alphabet.objects.create(d="AAA", e="BBB")
        ab = Alphabet.objects.annotate(de=ConcatWS("d", "e")).first()
        assert ab.de == "AAA,BBB"

    def test_concat_ws_integers(self):
        Alphabet.objects.create(a=1, b=2)
        ab = Alphabet.objects.annotate(ab=ConcatWS("a", "b")).first()
        assert ab.ab == "1,2"

    def test_concat_ws_skips_nulls(self):
        Alphabet.objects.create(d="AAA", e=None, f=2)
        ab = Alphabet.objects.annotate(de=ConcatWS("d", "e", "f")).first()
        assert ab.de == "AAA,2"

    def test_concat_ws_separator(self):
        Alphabet.objects.create(d="AAA", e="BBB")
        ab = Alphabet.objects.annotate(de=ConcatWS("d", "e", separator=":")).first()
        assert ab.de == "AAA:BBB"

    def test_concat_ws_separator_null_returns_none(self):
        Alphabet.objects.create(a=1, b=2)
        concat = ConcatWS("a", "b", separator=None)
        ab = Alphabet.objects.annotate(ab=concat).first()
        assert ab.ab is None

    def test_concat_ws_separator_field(self):
        Alphabet.objects.create(a=1, d="AAA", e="BBB")
        concat = ConcatWS("d", "e", separator=F("a"))
        ab = Alphabet.objects.annotate(de=concat).first()
        assert ab.de == "AAA1BBB"

    def test_concat_ws_bad_arg(self):
        with pytest.raises(ValueError) as excinfo:
            ConcatWS("a", "b", separataaa=",")
        assert "Invalid keyword arguments for ConcatWS: separataaa" in str(
            excinfo.value
        )

    def test_concat_ws_too_few_fields(self):
        with pytest.raises(ValueError) as excinfo:
            ConcatWS("a")
        assert "ConcatWS must take at least two expressions" in str(excinfo.value)

    def test_concat_ws_then_lookups_from_textfield(self):
        Alphabet.objects.create(d="AAA", e="BBB")
        Alphabet.objects.create(d="AAA", e="CCC")
        ab = (
            Alphabet.objects.annotate(de=ConcatWS("d", "e", separator=":"))
            .filter(de__endswith=":BBB")
            .first()
        )
        assert ab.de == "AAA:BBB"

    def test_elt_simple(self):
        Alphabet.objects.create(a=2)
        ab = Alphabet.objects.annotate(elt=ELT("a", ["apple", "orange"])).get()
        assert ab.elt == "orange"
        ab = Alphabet.objects.annotate(elt=ELT("a", ["apple"])).get()
        assert ab.elt is None

    def test_field_simple(self):
        Alphabet.objects.create(d="a")
        ab = Alphabet.objects.annotate(dp=Field("d", ["a", "b"])).first()
        assert ab.dp == 1
        ab = Alphabet.objects.annotate(dp=Field("d", ["b", "a"])).first()
        assert ab.dp == 2
        ab = Alphabet.objects.annotate(d