import json

from django.contrib.contenttypes.fields import GenericRelation
from django.db import models, connection


class TtDepartment(models.Model):
    code = models.CharField(max_length=150)
    name = models.CharField(max_length=255)
    desc = models.TextField(null=True)
    department = models.ForeignKey("api.TtDepartment", on_delete=models.SET_NULL,null=True,related_name="sub_department") # related name is used for get sub department
    status = models.SmallIntegerField()
    created_by = models.BigIntegerField(null=True)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_by = models.BigIntegerField(null=True)
    updated_at = models.DateTimeField(auto_now=True)

    STATUS_TO_CODE = {
        "active": 1,
        "inactive": 2,
    }

    class Meta:
        db_table = "tt_department"

    def __str__(self):
        return self.name

    @staticmethod
    def get_all_level_sub_department_ids(department_id, level=None, include_self=False):
        with connection.cursor() as cursor:
            sql = """
                WITH RECURSIVE dept_tree AS (
                    SELECT id, department_id, 0 as level
                    FROM tt_department
                    WHERE id = %s
                    UNION ALL
                    SELECT d.id, d.department_id, dt.level + 1
                    FROM tt_department d
                    INNER JOIN dept_tree dt ON d.department_id = dt.id
                    {level_limit}
                )
                SELECT id FROM dept_tree WHERE id != %s;
            """
            level_limit = ""
            params = [department_id, department_id]
            if level is not None:
                level_limit = "WHERE dt.level < %s"
                params = [department_id, level, department_id]

            cursor.execute(sql.format(level_limit=level_limit), params)
            ids = [row[0] for row in cursor.fetchall()]

            if include_self:
                ids = ids + [department_id]

        return ids