using FamilyTreeAPI.Models;
using Microsoft.EntityFrameworkCore;

namespace FamilyTreeAPI.Repository;

public class Seed
{
    private readonly FamilyTreeDBContext _context;
    public Seed(FamilyTreeDBContext context)
    {
        _context = context;
    }

    public int InsertOneUser()
    {
        string sptext = "CREATE OR REPLACE FUNCTION public.usp_search_user( " +
                        " iemail character varying,ifirstname character varying,ilastname character varying) " +
                        " RETURNS TABLE(id integer, fistname character varying, lastname character varying, email character varying," +
                        " stype integer, sactive boolean, srole integer, spassword character varying) " +
                        " AS $$  BEGIN " +
                        " return query SELECT e.id, e.firstname, e.lastname, e.email, e.stype," +
                        " e.sactive, e.srole, e.spassword " +
                        " FROM public.staff e " +
                        " WHERE (iemail = '' or e.email ilike iemail || '%') " +
                        " and (ilastname = '' or e.lastname ilike ilastname || '%') " +
                        " and (ifirstname = '' or e.firstname ilike ifirstname || '%'); "+
                        "END; " +
                        " $$ " +
                        " LANGUAGE 'plpgsql'; ";
                        

    int id = -1;
        //password = password
        string txt = "  INSERT INTO staff ( " +
                  "firstname, lastname, email, phone, stype, srole, spassword, sactive) " +
                  "VALUES" +
            " ( 'kham', 'vilaythong', 'kham.vilaythong@gmail.com', '009', 1, 2, 'cGFzc3dvcmQ=', true), " +
             " ( 'sy', 'vilaythong', 'sy.vilaythong@gmail.com', '007', 1, 2, 'cGFzc3dvcmQ=', true), " +
            " ( 'Hung', 'Nguyen', 'hung.gnuyen@gmail.com', '008', 1, 2, 'cGFzc3dvcmQ=', true); ";
        
        string workertxt = "INSERT INTO person ( firstname, lastname,email,phone,address,dob ,alive, fatherId, image, sex) " +
            " VALUES " +
            " ('Ho 1','Tran', 'Ho.Tran@hotmail.com', '002', '1 Cabramatta','1960-09-01', true, 0,'', 'M'), " +
            " ('Jimmy 2','Tran', 'Ho.Tran@hotmail.com', '003', '34 Cabramatta','1980-09-01', true, 1,'','M'), " +
            " ('Joe 3','Tran', 'Joe.Tran@hotmail.com', '006', '32 Cabramatta','1980-10-01', true, 1,'','M'), " +
            " ('John 4','Tran', 'John.Tran@hotmail.com', '008', '30 Cabramatta','1990-12-01', true, 3,'','M')," +
        " ('Lee 5','Tran', 'Lee.Tran@hotmail.com', '030', '18 Cabramatta','2000-12-01', true, 2,'','M'), " +
        " ('Len 6','Nguyen', 'Len.nguyen@hotmail.com', '001', '1 Home','1950-01-01', true, 0,'','M'), " +
        " ('Son 7','Nguyen', 'son.Nguyen@hotmail.com', '001', '10 Home','1980-01-01', true,6,'','M'), " +
        " ('Loa 8','Tran', 'Loa.Tran@hotmail.com', '020', '11 Cabramatta','2002-12-01', true, 3,'','M'), " +
        " ('Du 9','Tran', 'Du.Tran@hotmail.com', '001', '12 Cabramatta','2002-12-01', true, 4,'','M'), " +
        " ('Linh 10','Tran', 'Du.Tran@hotmail.com', '001', '12 Cabramatta','2002-12-01', true, 5,'','M'), " +
        " ('COA 11','Tran', 'Du.Tran@hotmail.com', '001', '12 Cabramatta','2002-12-01', true, 8,'','M'), " +
        " ('Hoa 12','Tran', 'Du.Tran@hotmail.com', '001', '12 Cabramatta','2002-12-01', true, 8,'','F'), " +
        " ('Nhi 13','Tran', 'Du.Tran@hotmail.com', '001', '12 Cabramatta','2002-12-01', true, 8,'','F'), " +
        " ('Mai 14','Tran', 'Du.Tran@hotmail.com', '001', '12 Cabramatta','2002-12-01', true, 8,'','F'), " +
        " ('Trang 15','Tran', 'Du.Tran@hotmail.com', '001', '12 Cabramatta','2002-12-01', true, 7,'','F'), " +
            " ('Tom 16','Tran', 'Ho.Tran@hotmail.com', '007', '34 Cabramatta','1999-01-01', true, 2,'','M');";


        try
        {
            int count = _context.staff.Count();
            if (count < 1)
            {
                var conn = _context.Database.GetDbConnection();
                try
                {

                    conn.Open();
                    var command = conn.CreateCommand();
                    command.CommandText = sptext;
                    command.CommandType = System.Data.CommandType.Text;
                    command.ExecuteNonQuery();
                    
                    command = conn.CreateCommand();
                    command.CommandText = txt;
                    command.CommandType = System.Data.CommandType.Text;
                    command.ExecuteNonQuery();

                    command.CommandText = workertxt;
                    command.CommandType = System.Data.CommandType.Text;

                    command.ExecuteNonQuery();
                    id = 9;
                }
                catch
                {
                    id = -1;
                }
                finally
                {
                    conn.Close();
                }
            }
            else
                id = 1;                

        }
        catch (Exception ex)
        {
            id = -10;
            
        }
        return id;
    }
}