using Sonex.Data.Database;

namespace Sonex.Data.Records;

public sealed class AccountRecord
{
    public int Id { get; set; }
    public string AccountName { get; set; } = string.Empty;
    public string? FirstName { get; set; }
    public string? LastName { get; set; }
    public string FullName => $"{FirstName} {LastName}".Trim();
    public string? Email { get; set; }
    public string? PhoneNumber { get; set; }
    public string? Department { get; set; }
    public string? JobTitle { get; set; }
    public string? AccountLocusWms { get; set; }
    public string? AccountLocusTerminal { get; set; }
    public DateTime? CreatedAt { get; set; }
    public bool Superadmin { get; set; }
    public DateTime? UpdatedAt { get; set; }
    public bool IsEnabled { get; set; }
    public string[] Groups { get; set; } = [];
    public string[] GrantedPermissions { get; set; } = [];

    public string Initials
    {
        get
        {
            string initials = string.Empty;

            if (!string.IsNullOrEmpty(FullName))
            {
                var splited = FullName.Split(' ');
                if(splited.Length==1)
                    initials = splited[0][0].ToString();
                else
                    initials = splited[0][0].ToString() + splited[splited.Length - 1][0].ToString();
            }
            return initials;
        }
    }
    public static Task<DB.SingleResult<AccountRecord>> Get(string accountName)
    {
        return DB.QuerySingleAsync<AccountRecord>(
            """
            SELECT v.id,
                   v.account_name,
                   v.first_name,
                   v.last_name,
                   v.email,
                   v.phone_number,
                   COALESCE(d.name, v.department) AS department,
                   v.job_title,
                   v.account_locus_wms,
                   v.account_locus_terminal,
                   v.created_at,
                   v.superadmin,
                   v.updated_at,
                   v.is_enabled,
                   v.groups,
                   v.permissions_all AS granted_permissions
            FROM sonex.v_accounts v
            LEFT JOIN sonex.accounts a ON a.account_name = v.account_name
            LEFT JOIN sonex.departments d ON d.id = a.department_id
            WHERE v.account_name = @account_name
            LIMIT 1;
            """,
            new { account_name = accountName });
    }

    public static Task<DB.SingleResult<AccountRecord>> GetCurrentAccount()
    {
        return DB.QuerySingleAsync<AccountRecord>(
            """
            SELECT v.id,
                   v.account_name,
                   v.first_name,
                   v.last_name,
                   v.email,
                   v.phone_number,
                   COALESCE(d.name, v.department) AS department,
                   v.job_title,
                   v.account_locus_wms,
                   v.account_locus_terminal,
                   v.created_at,
                   v.superadmin,
                   v.updated_at,
                   v.is_enabled,
                   v.groups,
                   v.permissions_all AS granted_permissions
            FROM sonex.current_account_get() v
            LEFT JOIN sonex.accounts a ON a.account_name = v.account_name
            LEFT JOIN sonex.departments d ON d.id = a.department_id
            LIMIT 1;
            """);
    }
    public Task<DB.SingleResult<bool>> ChangeCurrentPassword(string password)
    {
        return DB.QuerySingleAsync<bool>(
            "SELECT sonex.current_account_change_password(@Password);",
            new
            {
                Password = password
            });
    }
    public static Task<DB.Result<AccountRecord>> GetAll()
    {
        return DB.QueryListAsync<AccountRecord>(
            """
            SELECT v.id,
                   v.account_name,
                   v.first_name,
                   v.last_name,
                   v.email,
                   v.phone_number,
                   COALESCE(d.name, v.department) AS department,
                   v.job_title,
                   v.account_locus_wms,
                   v.account_locus_terminal,
                   v.created_at,
                   v.superadmin,
                   v.updated_at,
                   v.is_enabled,
                   v.groups,
                   v.permissions_all AS granted_permissions
            FROM sonex.v_accounts v
            LEFT JOIN sonex.accounts a ON a.account_name = v.account_name
            LEFT JOIN sonex.departments d ON d.id = a.department_id;
            """);
    }

    public static Task<DB.SingleResult<int>> Create(string firstName, string lastName, string accountName, string password)
    {
        return DB.QuerySingleAsync<int>(
            "SELECT sonex.accounts_create(@AccountName, @Password, @FirstName, @Lastname); ",
            new
            {
                AccountName = accountName,
                Password = password,
                FirstName = firstName,
                Lastname = lastName
            });
    }

    public Task<DB.SingleResult<bool>> ChangePassword(string password)
    {
        return DB.QuerySingleAsync<bool>(
            "SELECT sonex.accounts_password_change(@AccountName, @Password);",
            new
            {
                AccountName,
                Password = password
            });
    }

    public Task<DB.SingleResult<bool>> Disable()
    {
        return DB.QuerySingleAsync<bool>(
            "SELECT sonex.accounts_disable(@AccountName);",
            new
            {
                AccountName
            });
    }

    public Task<DB.SingleResult<bool>> Enable()
    {
        return DB.QuerySingleAsync<bool>(
            "SELECT sonex.accounts_enable(@AccountName);",
            new
            {
                AccountName
            });
    }

    public Task<DB.SingleResult<bool>> SetSuperAdmin(bool value)
    {
        return DB.QuerySingleAsync<bool>(
            "SELECT sonex.accounts_access_superadmin(@AccountName, @Value);",
            new
            {
                AccountName,
                Value = value
            });
    }

    public Task<DB.SingleResult<bool>> SavePersonalInformation()
    {
        return DB.QuerySingleAsync<bool>(
            """
            UPDATE sonex.accounts
            SET first_name = @FirstName,
                last_name = @LastName,
                department_id = (
                    SELECT d.id
                    FROM sonex.departments d
                    WHERE lower(d.name) = lower(NULLIF(@Department, ''))
                    LIMIT 1
                ),
                updated_at = NOW()
            WHERE account_name = @AccountName
            RETURNING TRUE;
            """,
            new
            {
                AccountName,
                FirstName,
                LastName,
                Department
            });
    }

    public Task<DB.SingleResult<bool>> SaveLinkedAccounts()
    {
        return DB.QuerySingleAsync<bool>(
            "UPDATE sonex.accounts SET account_locus_wms = @AccountLocusWms, account_locus_terminal = @AccountLocusTerminal, updated_at = NOW() WHERE account_name = @AccountName RETURNING TRUE;",
            new
            {
                AccountName,
                AccountLocusWms,
                AccountLocusTerminal
            });
    }

    public Task<DB.SingleResult<bool>> AddPermission(string permission)
    {
        return DB.QuerySingleAsync<bool>(
            "SELECT sonex.accounts_access_add(@AccountName, @Permission);",
            new
            {
                AccountName,
                Permission = permission
            });
    }

    public Task<DB.SingleResult<bool>> RemovePermission(string permission)
    {
        return DB.QuerySingleAsync<bool>(
            "SELECT sonex.accounts_access_remove(@AccountName, @Permission);",
            new
            {
                AccountName,
                Permission = permission
            });
    }

    public Task<DB.SingleResult<bool>> Delete()
    {
        return DB.QuerySingleAsync<bool>(
            "SELECT sonex.accounts_delete(@AccountName); ",
            new
            {
                AccountName
            });
    }
}