-- 养老管理系统老人端相关RPC函数
-- 适用于 supabase/postgresql

-- 1. 获取老人基本信息
drop function if exists get_elder_info(uuid);
create or replace function get_elder_info(elder_id uuid)
returns table (
  id uuid,
  name text,
  gender text,
  birthday date,
  room_number text,
  status text
) as $$
begin
  return query
  select
    e.id,
    e.name,
    e.gender,
    e.birthday,
    e.room_number,
    e.status
  from public.ec_elders e
  where e.id = elder_id or e.user_id = elder_id
  limit 1;
end;
$$ language plpgsql stable;

-- 2. 获取生命体征历史
drop function if exists get_vital_history(uuid, int);
create or replace function get_vital_history(elder_id uuid, limit_count int default 20)
returns setof public.ec_vital_signs as $$
begin
  return query
  select *
  from public.ec_vital_signs
  where elder_id = get_vital_history.elder_id
  order by measured_at desc
  limit limit_count;
end;
$$ language plpgsql stable;

-- 3. 获取健康档案
drop function if exists get_health_records(uuid);
create or replace function get_health_records(elder_id uuid)
returns setof public.ec_health_records as $$
begin
  return query
  select *
  from public.ec_health_records
  where elder_id = get_health_records.elder_id
  order by record_date desc;
end;
$$ language plpgsql stable;

-- 4. 获取健康预警
drop function if exists get_health_alerts(uuid);
create or replace function get_health_alerts(elder_id uuid)
returns setof public.ec_health_alerts as $$
begin
  return query
  select *
  from public.ec_health_alerts
  where elder_id = get_health_alerts.elder_id
  order by created_at desc;
end;
$$ language plpgsql stable;

-- 5. 获取最近生命体征（仪表板卡片）
drop function if exists get_recent_vitals(uuid, int);
create or replace function get_recent_vitals(elder_id uuid, limit_count int default 3)
returns setof public.ec_vital_signs as $$
begin
  return query
  select *
  from public.ec_vital_signs
  where elder_id = get_recent_vitals.elder_id
  order by measured_at desc
  limit limit_count;
end;
$$ language plpgsql stable;

-- 6. 获取今日活动
drop function if exists get_elder_activities(uuid, date);
create or replace function get_elder_activities(elder_id uuid, date date)
returns setof public.ec_activities as $$
begin
  return query
  select a.*
  from public.ec_activities a
  join public.ec_activity_participations p on a.id = p.activity_id
  where p.elder_id = get_elder_activities.elder_id
    and date(a.start_time) = get_elder_activities.date
  order by a.start_time asc;
end;
$$ language plpgsql stable;

-- 7. 获取今日用药
drop function if exists get_elder_medications(uuid, date);
create or replace function get_elder_medications(elder_id uuid, date date)
returns setof public.ec_medications as $$
begin
  return query
  select m.*
  from public.ec_medications m
  where m.elder_id = get_elder_medications.elder_id
    and (m.start_date is null or m.start_date <= get_elder_medications.date)
    and (m.end_date is null or m.end_date >= get_elder_medications.date)
  order by m.medication_name asc;
end;
$$ language plpgsql stable;

-- 8. 获取分配护理员
drop function if exists get_assigned_caregiver(uuid);
create or replace function get_assigned_caregiver(elder_id uuid)
returns table (
  id uuid,
  employee_id text,
  name text,
  phone text,
  department text,
  specialization text,
  shift text
) as $$
begin
  return query
  select c.id, c.employee_id, c.name, c.phone, c.department, c.specialization, c.work_shift as shift
  from public.ec_caregivers c
  join public.ec_elders e on c.id = e.caregiver_id
  where e.id = get_assigned_caregiver.elder_id
  limit 1;
end;
$$ language plpgsql stable;

-- 9. 获取待处理服务请求数
drop function if exists get_pending_requests_count(uuid);
create or replace function get_pending_requests_count(elder_id uuid)
returns table (count int) as $$
begin
  return query
  select count(*)
  from public.ec_service_requests
  where elder_id = get_pending_requests_count.elder_id
    and status = 'pending';
end;
$$ language plpgsql stable;

-- 10. 标记用药已服用
drop function if exists update_medication_status(uuid, text, timestamptz);
create or replace function update_medication_status(medication_id uuid, status text, taken_time timestamptz)
returns void as $$
begin
  update public.ec_medications
  set status = update_medication_status.status,
      updated_at = now()
  where id = update_medication_status.medication_id;
end;
$$ language plpgsql volatile;

-- 11. 创建紧急呼叫
drop function if exists create_emergency_call(uuid, text, text);
create or replace function create_emergency_call(elder_id uuid, type text, description text)
returns void as $$
begin
  insert into public.ec_emergency_calls (elder_id, type, description, created_at)
  values (elder_id, type, description, now());
end;
$$ language plpgsql volatile;

-- 12. 创建服务请求
drop function if exists create_service_request(uuid, text, text, text);
create or replace function create_service_request(elder_id uuid, type text, priority text, description text)
returns void as $$
begin
  insert into public.ec_service_requests (elder_id, type, priority, description, status, created_at)
  values (elder_id, type, priority, description, 'pending', now());
end;
$$ language plpgsql volatile;

-- 13. ����Ա��ѯ����������Ϣ
drop function if exists caregiver_get_all_elders();
create or replace function caregiver_get_all_elders()
returns table (
  id uuid,
  name text,
  gender text,
  birthday date,
  room_number text,
  bed_number text,
  care_level text,
  status text
) as 100
begin
  return query
  select
    e.id,
    e.name,
    e.gender::text,
    e.birthday,
    e.room_number::text,
    e.bed_number::text,
    e.care_level::text,
    e.status::text
  from public.ec_elders e
  order by e.room_number, e.bed_number;
end;
100 language plpgsql stable;