using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.RazorPages;
using Npgsql;
namespace RestaurantWeb2.Pages;

public class DataModel : PageModel {
    public string Table="",Search="",Msg="",Role="";
    public bool IsErr,ShowForm;
    public int EditId;
    public List<string> Cols=new(),Vals=new();
    public List<List<string>> Rows=new();

   
    static readonly HashSet<string> IntCols = new(){
        "год_рождения","вес_г","id_персонала"
    };

    static readonly HashSet<string> DecCols = new(){
        "оклад","вес_кг","цена_руб"
    };

    static readonly Dictionary<string,string[]> Tbls = new(){
        ["Персонал"]=new[]{"id_персонала","фио","год_рождения","должность","оклад","телефон","адрес","паспортные_данные"},
        ["Блюда"]=new[]{"id_блюда","блюдо","категория","вес_г"},
        ["Склад"]=new[]{"id_ингредиента","ингредиент","вес_кг","цена_руб"},
        ["Заказы"]=new[]{"id_заказа","код_заказа","дата_заказа","id_персонала"},
    };

    public IActionResult OnGet(string? table,string? action,string? search,int? id){
        Role=HttpContext.Session.GetString("role")??"";
        if(Role=="") return RedirectToPage("/Index");
        Table=Tbls.ContainsKey(table??"")?table!:"Персонал";
        Search=search??"";
        Cols=Tbls[Table].ToList();
        if(action=="delete"&&id.HasValue&&Role=="admin") Del(id.Value);
        if(action=="edit"&&id.HasValue) LoadEdit(id.Value);
        if(action=="add") ShowForm=true;
        Load(); return Page();
    }

    public IActionResult OnPost(string table,int editId,IFormCollection form){
        Role=HttpContext.Session.GetString("role")??"";
        if(Role=="") return RedirectToPage("/Index");
        Table=Tbls.ContainsKey(table)?table:"Персонал";
        Cols=Tbls[Table].ToList();
        var cols=Cols.Skip(1).ToList();
        var vals=cols.Select((_,i)=>form[$"f{i+1}"].ToString()).ToList();
        try{
            using var conn=Db.Conn();conn.Open();
            if(editId>0){
                var sets=string.Join(",",cols.Select((c,i)=>$"\"{c}\"=@p{i}"));
                var cmd=new NpgsqlCommand($"UPDATE \"{Table}\" SET {sets} WHERE \"{Cols[0]}\"=@id",conn);
                for(int i=0;i<vals.Count;i++) cmd.Parameters.AddWithValue($"p{i}",TypedVal(cols[i],vals[i]));
                cmd.Parameters.AddWithValue("id",editId);
                cmd.ExecuteNonQuery();Msg="Обновлено.";
            }else{
                var cs=string.Join(",",cols.Select(c=>$"\"{c}\""));
                var ps=string.Join(",",cols.Select((_,i)=>$"@p{i}"));
                var cmd=new NpgsqlCommand($"INSERT INTO \"{Table}\"({cs}) VALUES({ps})",conn);
                for(int i=0;i<vals.Count;i++) cmd.Parameters.AddWithValue($"p{i}",TypedVal(cols[i],vals[i]));
                cmd.ExecuteNonQuery();Msg="Добавлено.";
            }
        }catch(Exception ex){Msg="Ошибка: "+ex.Message;IsErr=true;}
        Load();return Page();
    }

    // Приводим значение к нужному типу по имени колонки
    static object TypedVal(string col, string val) {
        if (string.IsNullOrWhiteSpace(val)) return DBNull.Value;
        if (IntCols.Contains(col) && int.TryParse(val, out var i)) return i;
        if (DecCols.Contains(col) && decimal.TryParse(val.Replace(",","."), System.Globalization.NumberStyles.Any, System.Globalization.CultureInfo.InvariantCulture, out var d)) return d;
        if (col == "дата_заказа" && DateTime.TryParse(val, out var dt)) return dt;
        return val;
    }

    void Load(){
        try{
            using var conn=Db.Conn();conn.Open();
            var cs=string.Join(",",Cols.Select(c=>$"\"{c}\""));
            var sql=$"SELECT {cs} FROM \"{Table}\"";
            if(!string.IsNullOrEmpty(Search)) sql+=$" WHERE \"{Cols[1]}\" ILIKE @s";
            sql+=$" ORDER BY \"{Cols[0]}\"";
            var cmd=new NpgsqlCommand(sql,conn);
            if(!string.IsNullOrEmpty(Search)) cmd.Parameters.AddWithValue("s",$"%{Search}%");
            using var r=cmd.ExecuteReader();
            while(r.Read()){var row=new List<string>();for(int i=0;i<r.FieldCount;i++)row.Add(r[i]?.ToString()??"");Rows.Add(row);}
        }catch(Exception ex){Msg="Ошибка: "+ex.Message;IsErr=true;}
    }

    void Del(int id){
        try{
            using var conn=Db.Conn();conn.Open();
            var cmd=new NpgsqlCommand($"DELETE FROM \"{Table}\" WHERE \"{Cols[0]}\"=@id",conn);
            cmd.Parameters.AddWithValue("id",id);cmd.ExecuteNonQuery();Msg="Удалено.";
        }catch(Exception ex){Msg="Ошибка: "+ex.Message;IsErr=true;}
    }

    void LoadEdit(int id){
        ShowForm=true;EditId=id;
        try{
            using var conn=Db.Conn();conn.Open();
            var cs=string.Join(",",Cols.Select(c=>$"\"{c}\""));
            var cmd=new NpgsqlCommand($"SELECT {cs} FROM \"{Table}\" WHERE \"{Cols[0]}\"=@id",conn);
            cmd.Parameters.AddWithValue("id",id);
            using var r=cmd.ExecuteReader();
            if(r.Read()) for(int i=1;i<r.FieldCount;i++) Vals.Add(r[i]?.ToString()??"");
        }catch{}
    }
}