

SELECT pg_catalog.set_config('search_path', '', false);


CREATE EXTENSION IF NOT EXISTS "pg_cron" WITH SCHEMA "pg_catalog";






CREATE SCHEMA IF NOT EXISTS "govtrack";


ALTER SCHEMA "govtrack" OWNER TO "postgres";


CREATE SCHEMA IF NOT EXISTS "moef";


ALTER SCHEMA "moef" OWNER TO "postgres";


CREATE SCHEMA IF NOT EXISTS "motie";


ALTER SCHEMA "motie" OWNER TO "postgres";


CREATE EXTENSION IF NOT EXISTS "pgsodium";






COMMENT ON SCHEMA "public" IS 'standard public schema';



CREATE EXTENSION IF NOT EXISTS "pg_graphql" WITH SCHEMA "graphql";






CREATE EXTENSION IF NOT EXISTS "pg_stat_statements" WITH SCHEMA "extensions";






CREATE EXTENSION IF NOT EXISTS "pgcrypto" WITH SCHEMA "extensions";






CREATE EXTENSION IF NOT EXISTS "supabase_vault" WITH SCHEMA "vault";






CREATE EXTENSION IF NOT EXISTS "uuid-ossp" WITH SCHEMA "extensions";






CREATE OR REPLACE FUNCTION "public"."close_motie_keys"("keys" "text"[], "ts" timestamp with time zone) RETURNS integer
    LANGUAGE "plpgsql" SECURITY DEFINER
    SET "search_path" TO 'public'
    AS $$
declare
  n int;
begin
  update public.motie_org_hist h
     set valid_to   = ts,
         is_current = false
   where h.is_current = true
     and h.key_hash = any(keys);

  GET DIAGNOSTICS n = ROW_COUNT;
  return n;
end;
$$;


ALTER FUNCTION "public"."close_motie_keys"("keys" "text"[], "ts" timestamp with time zone) OWNER TO "postgres";


CREATE OR REPLACE FUNCTION "public"."finalize_motie_run"("p_run_id" "uuid") RETURNS "jsonb"
    LANGUAGE "plpgsql"
    AS $$
declare
  v_now timestamptz := now();
  v_unchanged       integer := 0;
  v_changed_or_new  integer := 0;
  v_strict_new      integer := 0;
begin
  -- 0) 안전장치: 스테이징 없으면 실패
  if not exists (select 1 from public.motie_org_stg where run_id = p_run_id) then
    raise exception 'no stage rows for run_id=%', p_run_id;
  end if;

  -- 1) 현재스냅샷과 비교 (이 쿼리 **하나** 안에서만 쓰는 CTE들)
  with stg as (
    select
      s.key_hash,
      s.row_hash,
      s.name,
      s.position,
      s.department,
      nullif(btrim(s.task),  '') as task,
      nullif(btrim(s.phone), '') as phone
    from public.motie_org_stg s
    where s.run_id = p_run_id
  ),
  cur as (
    select
      key_hash, row_hash, name, position, department, task, phone
    from public.motie_org_hist
    where is_current = true and ended_at is null
  ),
  diff as (
    select
      s.*,
      c.row_hash as cur_row_hash
    from stg s
    left join cur c using (key_hash)
  )
  select count(*) into v_unchanged
  from diff
  where cur_row_hash is not null and cur_row_hash = row_hash;

  -- 2) 변경/신규 키만 temp 테이블에 물리화 (이후 스테이트먼트에서 재사용하기 위함)
  create temporary table _t_diff on commit drop as
  with stg as (
    select
      s.key_hash,
      s.row_hash,
      s.name,
      s.position,
      s.department,
      nullif(btrim(s.task),  '') as task,
      nullif(btrim(s.phone), '') as phone
    from public.motie_org_stg s
    where s.run_id = p_run_id
  ),
  cur as (
    select
      key_hash, row_hash
    from public.motie_org_hist
    where is_current = true and ended_at is null
  )
  select
    s.key_hash, s.row_hash, s.name, s.position, s.department, s.task, s.phone
  from stg s
  left join cur c using (key_hash)
  where coalesce(c.row_hash, '') <> s.row_hash;

  get diagnostics v_changed_or_new = row_count;

  -- 변경/신규가 없으면 리턴
  if v_changed_or_new = 0 then
    return jsonb_build_object(
      'status','ok',
      'run_id', p_run_id,
      'unchanged', v_unchanged,
      'changed_or_new', 0,
      'strict_new', 0
    );
  end if;

  -- 3) 기존 "현재 행" 먼저 닫기 (유니크 조건과 정확히 일치)
  update public.motie_org_hist h
  set
    is_current = false,
    is_open    = false,
    ended_at   = v_now,
    valid_to   = v_now,
    updated_at = v_now
  where h.key_hash in (select key_hash from _t_diff)
    and h.is_open = true
    and h.ended_at is null;

  -- 4) 새 행 개시
  insert into public.motie_org_hist
    (key_hash, row_hash, name, position, department, task, phone,
     valid_from, valid_to, is_current,
     started_at, ended_at, is_open, updated_at)
  select
    key_hash, row_hash, name, position, department, task, phone,
    v_now, null, true,
    v_now, null, true, v_now
  from _t_diff;

  -- 5) strict new(히스토리 자체가 없던 key)
  with stgkeys as (
    select distinct key_hash
    from public.motie_org_stg
    where run_id = p_run_id
  ),
  nonever as (
    select s.key_hash
    from stgkeys s
    left join public.motie_org_hist h using (key_hash)
    where h.key_hash is null
  )
  select count(*) into v_strict_new from nonever;

  return jsonb_build_object(
    'status','ok',
    'run_id', p_run_id,
    'unchanged', v_unchanged,
    'changed_or_new', v_changed_or_new,
    'strict_new', v_strict_new
  );
end;
$$;


ALTER FUNCTION "public"."finalize_motie_run"("p_run_id" "uuid") OWNER TO "postgres";


CREATE OR REPLACE FUNCTION "public"."handle_new_user"() RETURNS "trigger"
    LANGUAGE "plpgsql"
    AS $$
BEGIN
  BEGIN
    INSERT INTO profiles (id, role)
    VALUES (NEW.id, 'user')
    ON CONFLICT (id) DO NOTHING;
  EXCEPTION WHEN OTHERS THEN
    RAISE NOTICE 'Failed to insert into profiles: %', SQLERRM;
  END;
  RETURN NEW;
END;
$$;


ALTER FUNCTION "public"."handle_new_user"() OWNER TO "postgres";


CREATE OR REPLACE FUNCTION "public"."motie_merge_latest"() RETURNS "uuid"
    LANGUAGE "plpgsql" SECURITY DEFINER
    SET "search_path" TO 'public'
    AS $$
declare
  v_run_id uuid;
begin
  -- 우선 snapshot에 찍힌 것 중 최신
  select s.run_id
    into v_run_id
  from public.motie_org_snapshot s
  order by s.taken_at desc
  limit 1;

  -- 없으면 crawler_run에서 최신 'motie_org' passed
  if v_run_id is null then
    select c.id
      into v_run_id
    from public.crawler_run c
    where c.target = 'motie_org'
      and c.status in ('passed','collected')
    order by c.started_at desc
    limit 1;
  end if;

  if v_run_id is null then
    raise notice 'no motie_org run found';
    return null;
  end if;

  perform public.motie_merge_run(v_run_id);
  return v_run_id;
end
$$;


ALTER FUNCTION "public"."motie_merge_latest"() OWNER TO "postgres";


CREATE OR REPLACE FUNCTION "public"."motie_merge_run"("p_run_id" "uuid") RETURNS "void"
    LANGUAGE "plpgsql" SECURITY DEFINER
    SET "search_path" TO 'public'
    AS $$
begin
  -- 연속 동일 상태는 스킵
  insert into public.motie_org_hist (
    key_hash, name, position, department, task, phone,
    row_hash, valid_from, valid_to, is_current
  )
  select
    s.key_hash, s.name, s.position, s.department, s.task, s.phone,
    s.row_hash, now(), null, true
  from public.motie_org_stg s
  left join lateral (
    select h.row_hash
    from public.motie_org_hist h
    where h.key_hash = s.key_hash and h.is_current = true
    order by h.valid_from desc
    limit 1
  ) cur on true
  where s.run_id = p_run_id
    and (cur.row_hash is null or cur.row_hash is distinct from s.row_hash);

  -- 새 버전 생긴 키의 이전 current 닫기
  update public.motie_org_hist h
  set is_current = false, valid_to = now()
  where h.is_current = true
    and exists (
      select 1
      from public.motie_org_stg s
      where s.run_id = p_run_id
        and s.key_hash = h.key_hash
        and s.row_hash is distinct from h.row_hash
    );

  -- 이번 런에 '아예 안 나온' 사람 종료(=closed)
  update public.motie_org_hist h
  set is_current = false, valid_to = now()
  where h.is_current = true
    and not exists (
      select 1 from public.motie_org_stg s
      where s.run_id = p_run_id and s.key_hash = h.key_hash
    );

  -- 스냅샷 마킹(없으면 생성)
  insert into public.motie_org_snapshot(run_id, rows, status)
  select p_run_id,
         (select count(*) from public.motie_org_stg where run_id = p_run_id),
         'collected'
  on conflict (run_id) do nothing;
end
$$;


ALTER FUNCTION "public"."motie_merge_run"("p_run_id" "uuid") OWNER TO "postgres";


CREATE OR REPLACE FUNCTION "public"."motie_org_events_list"("p_since" timestamp with time zone, "p_limit" integer DEFAULT 200, "p_offset" integer DEFAULT 0, "p_event" "text" DEFAULT NULL::"text", "p_department" "text" DEFAULT NULL::"text", "p_position" "text" DEFAULT NULL::"text", "p_name" "text" DEFAULT NULL::"text") RETURNS TABLE("key_hash" "text", "name" "text", "department" "text", "position" "text", "phone" "text", "task" "text", "valid_from" timestamp with time zone, "valid_to" timestamp with time zone, "changed_fields" "text"[], "event_type" "text", "run_id" "uuid", "dep_before" "text", "pos_before" "text", "phone_before" "text", "same_run" boolean, "pair_delta_sec" integer, "total_count" bigint)
    LANGUAGE "plpgsql"
    AS $$
declare
    v_since timestamptz := coalesce(p_since, now() - interval '14 days');
    v_limit integer := case when p_limit is null or p_limit < 1 then 200 else p_limit end;
    v_offset integer := greatest(coalesce(p_offset, 0), 0);
    v_total bigint := 0;
begin
    perform set_config('statement_timeout', '20000', true);

    select count(*) into v_total
    from public.motie_org_events_mv e
    where e.valid_from >= v_since
      and (p_event is null or e.event_type = p_event)
      and (p_department is null or lower(e.department) like '%' || lower(p_department) || '%')
      and (p_position is null or lower(e."position") like '%' || lower(p_position) || '%')
      and (p_name is null or lower(e.name) like '%' || lower(p_name) || '%');

    if v_total = 0 then
        return;
    end if;

    return query
    select
        q.key_hash,
        q.name,
        q.department,
        q."position",
        q.phone,
        q.task,
        q.valid_from,
        q.valid_to,
        q.changed_fields,
        q.event_type,
        q.run_id,
        q.dep_before,
        q.pos_before,
        q.phone_before,
        q.same_run,
        q.pair_delta_sec,
        v_total
    from (
        select *
        from public.motie_org_events_mv e
        where e.valid_from >= v_since
          and (p_event is null or e.event_type = p_event)
          and (p_department is null or lower(e.department) like '%' || lower(p_department) || '%')
          and (p_position is null or lower(e."position") like '%' || lower(p_position) || '%')
          and (p_name is null or lower(e.name) like '%' || lower(p_name) || '%')
        order by e.valid_from desc
        offset v_offset
        limit v_limit
    ) as q;
end;
$$;


ALTER FUNCTION "public"."motie_org_events_list"("p_since" timestamp with time zone, "p_limit" integer, "p_offset" integer, "p_event" "text", "p_department" "text", "p_position" "text", "p_name" "text") OWNER TO "postgres";


CREATE OR REPLACE FUNCTION "public"."tg_motie_merge_after_run"() RETURNS "trigger"
    LANGUAGE "plpgsql" SECURITY DEFINER
    SET "search_path" TO 'public'
    AS $$
begin
  if (TG_OP = 'INSERT' or TG_OP = 'UPDATE')
     and new.target = 'motie_org'
     and new.status in ('passed','collected') then
    perform public.motie_merge_run(new.id);
  end if;
  return new;
end
$$;


ALTER FUNCTION "public"."tg_motie_merge_after_run"() OWNER TO "postgres";


CREATE OR REPLACE FUNCTION "public"."touch_last_seen"() RETURNS "trigger"
    LANGUAGE "plpgsql"
    AS $$
BEGIN
  NEW.last_seen_at = NOW();
  RETURN NEW;
END; $$;


ALTER FUNCTION "public"."touch_last_seen"() OWNER TO "postgres";


CREATE OR REPLACE FUNCTION "public"."touch_updated_at"() RETURNS "trigger"
    LANGUAGE "plpgsql"
    AS $$
begin
  new.updated_at := now();
  return new;
end$$;


ALTER FUNCTION "public"."touch_updated_at"() OWNER TO "postgres";




CREATE TABLE IF NOT EXISTS "govtrack"."person_identity_evidence" (
    "person_id" "uuid" NOT NULL,
    "key_type" "text" NOT NULL,
    "value" "text" NOT NULL,
    "conf" numeric DEFAULT 1.0 NOT NULL,
    "created_at" timestamp with time zone DEFAULT "now"() NOT NULL
);


ALTER TABLE "govtrack"."person_identity_evidence" OWNER TO "postgres";


CREATE TABLE IF NOT EXISTS "public"."admin_motie_change_status" (
    "person_key" "text" NOT NULL,
    "latest_valid_from" timestamp with time zone NOT NULL,
    "decision" "text" NOT NULL,
    "note" "text",
    "updated_by" "text",
    "updated_at" timestamp with time zone DEFAULT "now"() NOT NULL,
    CONSTRAINT "admin_motie_change_status_decision_check" CHECK (("decision" = ANY (ARRAY['accept'::"text", 'skip'::"text"])))
);


ALTER TABLE "public"."admin_motie_change_status" OWNER TO "postgres";


CREATE TABLE IF NOT EXISTS "public"."congress_member" (
    "id" "text" NOT NULL,
    "committee_name" "text" NOT NULL,
    "role_name" "text" DEFAULT ''::"text" NOT NULL,
    "full_name" "text" NOT NULL,
    "name_hanja" "text",
    "party" "text",
    "district" "text",
    "phone" "text",
    "room_no" "text",
    "photo_url" "text",
    "profile_url" "text",
    "member_page_url" "text",
    "aide_staff" "text",
    "aide_secretary" "text",
    "aide_secretary2" "text",
    "election_count" integer,
    "source_json" "jsonb",
    "first_seen_at" timestamp with time zone DEFAULT "now"(),
    "last_seen_at" timestamp with time zone DEFAULT "now"()
);


ALTER TABLE "public"."congress_member" OWNER TO "postgres";


CREATE TABLE IF NOT EXISTS "public"."congress_member_hist" (
    "id" bigint NOT NULL,
    "key_hash" "text" NOT NULL,
    "committee_name" "text",
    "role_name" "text",
    "full_name" "text",
    "name_hanja" "text",
    "party" "text",
    "district" "text",
    "phone" "text",
    "room_no" "text",
    "photo_url" "text",
    "profile_url" "text",
    "member_page_url" "text",
    "aide_staff" "text",
    "aide_secretary" "text",
    "aide_secretary2" "text",
    "election_count" integer,
    "row_hash" "text" NOT NULL,
    "valid_from" timestamp with time zone NOT NULL,
    "valid_to" timestamp with time zone,
    "is_current" boolean DEFAULT true NOT NULL
);


ALTER TABLE "public"."congress_member_hist" OWNER TO "postgres";


CREATE OR REPLACE VIEW "public"."congress_member_cur" WITH ("security_invoker"='on') AS
 SELECT "congress_member_hist"."key_hash",
    "congress_member_hist"."committee_name",
    "congress_member_hist"."role_name",
    "congress_member_hist"."full_name",
    "congress_member_hist"."name_hanja",
    "congress_member_hist"."party",
    "congress_member_hist"."district",
    "congress_member_hist"."phone",
    "congress_member_hist"."room_no",
    "congress_member_hist"."photo_url",
    "congress_member_hist"."profile_url",
    "congress_member_hist"."member_page_url",
    "congress_member_hist"."aide_staff",
    "congress_member_hist"."aide_secretary",
    "congress_member_hist"."aide_secretary2",
    "congress_member_hist"."election_count",
    "congress_member_hist"."row_hash",
    "congress_member_hist"."valid_from"
   FROM "public"."congress_member_hist"
  WHERE (("congress_member_hist"."is_current" = true) AND ("congress_member_hist"."valid_to" IS NULL));


ALTER TABLE "public"."congress_member_cur" OWNER TO "postgres";


CREATE OR REPLACE VIEW "public"."congress_member_events" WITH ("security_invoker"='on') AS
 WITH "base" AS (
         SELECT "h"."id",
            "h"."key_hash",
            "h"."committee_name",
            "h"."role_name",
            "h"."full_name",
            "h"."name_hanja",
            "h"."party",
            "h"."district",
            "h"."phone",
            "h"."room_no",
            "h"."photo_url",
            "h"."profile_url",
            "h"."member_page_url",
            "h"."aide_staff",
            "h"."aide_secretary",
            "h"."aide_secretary2",
            "h"."election_count",
            "h"."row_hash",
            "h"."valid_from",
            "h"."valid_to",
            "h"."is_current",
            (("h"."key_hash" || '|'::"text") || COALESCE("h"."committee_name", ''::"text")) AS "membership_key"
           FROM "public"."congress_member_hist" "h"
        ), "ordered" AS (
         SELECT "b"."id",
            "b"."key_hash",
            "b"."committee_name",
            "b"."role_name",
            "b"."full_name",
            "b"."name_hanja",
            "b"."party",
            "b"."district",
            "b"."phone",
            "b"."room_no",
            "b"."photo_url",
            "b"."profile_url",
            "b"."member_page_url",
            "b"."aide_staff",
            "b"."aide_secretary",
            "b"."aide_secretary2",
            "b"."election_count",
            "b"."row_hash",
            "b"."valid_from",
            "b"."valid_to",
            "b"."is_current",
            "b"."membership_key",
            "lag"("b"."row_hash") OVER (PARTITION BY "b"."membership_key" ORDER BY "b"."valid_from") AS "prev_row_hash",
            "lag"("b"."role_name") OVER (PARTITION BY "b"."membership_key" ORDER BY "b"."valid_from") AS "prev_role_name",
            "lag"("b"."party") OVER (PARTITION BY "b"."membership_key" ORDER BY "b"."valid_from") AS "prev_party",
            "lag"("b"."district") OVER (PARTITION BY "b"."membership_key" ORDER BY "b"."valid_from") AS "prev_district",
            "lag"("b"."phone") OVER (PARTITION BY "b"."membership_key" ORDER BY "b"."valid_from") AS "prev_phone",
            "lag"("b"."room_no") OVER (PARTITION BY "b"."membership_key" ORDER BY "b"."valid_from") AS "prev_room_no",
            "lag"("b"."photo_url") OVER (PARTITION BY "b"."membership_key" ORDER BY "b"."valid_from") AS "prev_photo_url",
            "lag"("b"."profile_url") OVER (PARTITION BY "b"."membership_key" ORDER BY "b"."valid_from") AS "prev_profile_url",
            "lag"("b"."member_page_url") OVER (PARTITION BY "b"."membership_key" ORDER BY "b"."valid_from") AS "prev_member_page_url",
            "lag"("b"."aide_staff") OVER (PARTITION BY "b"."membership_key" ORDER BY "b"."valid_from") AS "prev_aide_staff",
            "lag"("b"."aide_secretary") OVER (PARTITION BY "b"."membership_key" ORDER BY "b"."valid_from") AS "prev_aide_secretary",
            "lag"("b"."aide_secretary2") OVER (PARTITION BY "b"."membership_key" ORDER BY "b"."valid_from") AS "prev_aide_secretary2",
            "lag"("b"."election_count") OVER (PARTITION BY "b"."membership_key" ORDER BY "b"."valid_from") AS "prev_election_count"
           FROM "base" "b"
        ), "diffed" AS (
         SELECT "ordered"."key_hash",
            "ordered"."full_name",
            "ordered"."room_no",
            "ordered"."party",
            "ordered"."committee_name",
            "ordered"."role_name",
            "ordered"."valid_from",
            "ordered"."valid_to",
            "ordered"."is_current",
            "ordered"."row_hash",
            "ordered"."prev_row_hash",
            "array_remove"(ARRAY[
                CASE
                    WHEN ("ordered"."role_name" IS DISTINCT FROM "ordered"."prev_role_name") THEN 'role_name'::"text"
                    ELSE NULL::"text"
                END,
                CASE
                    WHEN ("ordered"."party" IS DISTINCT FROM "ordered"."prev_party") THEN 'party'::"text"
                    ELSE NULL::"text"
                END,
                CASE
                    WHEN ("ordered"."district" IS DISTINCT FROM "ordered"."prev_district") THEN 'district'::"text"
                    ELSE NULL::"text"
                END,
                CASE
                    WHEN ("ordered"."phone" IS DISTINCT FROM "ordered"."prev_phone") THEN 'phone'::"text"
                    ELSE NULL::"text"
                END,
                CASE
                    WHEN ("ordered"."room_no" IS DISTINCT FROM "ordered"."prev_room_no") THEN 'room_no'::"text"
                    ELSE NULL::"text"
                END,
                CASE
                    WHEN ("ordered"."photo_url" IS DISTINCT FROM "ordered"."prev_photo_url") THEN 'photo_url'::"text"
                    ELSE NULL::"text"
                END,
                CASE
                    WHEN ("ordered"."profile_url" IS DISTINCT FROM "ordered"."prev_profile_url") THEN 'profile_url'::"text"
                    ELSE NULL::"text"
                END,
                CASE
                    WHEN ("ordered"."member_page_url" IS DISTINCT FROM "ordered"."prev_member_page_url") THEN 'member_page_url'::"text"
                    ELSE NULL::"text"
                END,
                CASE
                    WHEN ("ordered"."aide_staff" IS DISTINCT FROM "ordered"."prev_aide_staff") THEN 'aide_staff'::"text"
                    ELSE NULL::"text"
                END,
                CASE
                    WHEN ("ordered"."aide_secretary" IS DISTINCT FROM "ordered"."prev_aide_secretary") THEN 'aide_secretary'::"text"
                    ELSE NULL::"text"
                END,
                CASE
                    WHEN ("ordered"."aide_secretary2" IS DISTINCT FROM "ordered"."prev_aide_secretary2") THEN 'aide_secretary2'::"text"
                    ELSE NULL::"text"
                END,
                CASE
                    WHEN ("ordered"."election_count" IS DISTINCT FROM "ordered"."prev_election_count") THEN 'election_count'::"text"
                    ELSE NULL::"text"
                END], NULL::"text") AS "changed_fields"
           FROM "ordered"
        ), "tagged" AS (
         SELECT "diffed"."key_hash",
            "diffed"."full_name",
            "diffed"."room_no",
            "diffed"."party",
            "diffed"."committee_name",
            "diffed"."role_name",
            "diffed"."valid_from",
            "diffed"."valid_to",
            "diffed"."changed_fields",
                CASE
                    WHEN (("diffed"."is_current" = false) AND ("diffed"."valid_to" IS NOT NULL)) THEN 'closed'::"text"
                    WHEN ("diffed"."prev_row_hash" IS NULL) THEN 'added'::"text"
                    WHEN ("diffed"."row_hash" IS DISTINCT FROM "diffed"."prev_row_hash") THEN 'updated'::"text"
                    ELSE 'unchanged'::"text"
                END AS "event_type"
           FROM "diffed"
        )
 SELECT "tagged"."key_hash",
    "tagged"."full_name",
    "tagged"."room_no",
    "tagged"."party",
    "tagged"."committee_name",
    "tagged"."role_name",
    "tagged"."valid_from",
    "tagged"."valid_to",
    "tagged"."changed_fields",
    "tagged"."event_type"
   FROM "tagged"
  WHERE ("tagged"."event_type" <> 'unchanged'::"text")
  ORDER BY "tagged"."valid_from" DESC;


ALTER TABLE "public"."congress_member_events" OWNER TO "postgres";


CREATE SEQUENCE IF NOT EXISTS "public"."congress_member_hist_id_seq"
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE "public"."congress_member_hist_id_seq" OWNER TO "postgres";


ALTER SEQUENCE "public"."congress_member_hist_id_seq" OWNED BY "public"."congress_member_hist"."id";



CREATE OR REPLACE VIEW "public"."congress_member_person" WITH ("security_invoker"='on') AS
 SELECT "h"."key_hash",
    "h"."full_name",
    "h"."committee_name",
    "h"."role_name",
    "h"."party",
    "h"."district",
    "h"."phone",
    "h"."room_no",
    "h"."photo_url",
    "h"."profile_url",
    "h"."member_page_url",
    "h"."aide_staff",
    "h"."aide_secretary",
    "h"."aide_secretary2",
    "h"."election_count",
    "h"."valid_from"
   FROM "public"."congress_member_hist" "h"
  WHERE (("h"."is_current" = true) AND ("h"."valid_to" IS NULL));


ALTER TABLE "public"."congress_member_person" OWNER TO "postgres";


CREATE TABLE IF NOT EXISTS "public"."congress_member_stg" (
    "id" bigint NOT NULL,
    "run_id" "uuid" NOT NULL,
    "committee_name" "text",
    "role_name" "text",
    "full_name" "text",
    "name_hanja" "text",
    "party" "text",
    "district" "text",
    "phone" "text",
    "room_no" "text",
    "photo_url" "text",
    "profile_url" "text",
    "member_page_url" "text",
    "aide_staff" "text",
    "aide_secretary" "text",
    "aide_secretary2" "text",
    "election_count" integer,
    "key_hash" "text" NOT NULL,
    "row_hash" "text" NOT NULL,
    "created_at" timestamp with time zone DEFAULT "now"() NOT NULL
);


ALTER TABLE "public"."congress_member_stg" OWNER TO "postgres";


CREATE OR REPLACE VIEW "public"."congress_member_run_diff" WITH ("security_invoker"='on') AS
 SELECT "s"."run_id",
    "s"."key_hash",
    "s"."full_name",
    "s"."committee_name",
    "s"."role_name",
    "s"."party",
    "s"."district",
    "s"."row_hash" AS "stg_row_hash",
    "h"."row_hash" AS "hist_row_hash",
        CASE
            WHEN ("h"."key_hash" IS NULL) THEN 'new'::"text"
            WHEN ("h"."row_hash" = "s"."row_hash") THEN 'unchanged'::"text"
            ELSE 'changed'::"text"
        END AS "diff_type"
   FROM ("public"."congress_member_stg" "s"
     LEFT JOIN LATERAL ( SELECT "h2"."id",
            "h2"."key_hash",
            "h2"."committee_name",
            "h2"."role_name",
            "h2"."full_name",
            "h2"."name_hanja",
            "h2"."party",
            "h2"."district",
            "h2"."phone",
            "h2"."room_no",
            "h2"."photo_url",
            "h2"."profile_url",
            "h2"."member_page_url",
            "h2"."aide_staff",
            "h2"."aide_secretary",
            "h2"."aide_secretary2",
            "h2"."election_count",
            "h2"."row_hash",
            "h2"."valid_from",
            "h2"."valid_to",
            "h2"."is_current"
           FROM "public"."congress_member_hist" "h2"
          WHERE ("h2"."key_hash" = "s"."key_hash")
          ORDER BY "h2"."valid_from" DESC
         LIMIT 1) "h" ON (true));


ALTER TABLE "public"."congress_member_run_diff" OWNER TO "postgres";


CREATE TABLE IF NOT EXISTS "public"."congress_member_snapshot" (
    "run_id" "uuid" NOT NULL,
    "rows" integer DEFAULT 0 NOT NULL,
    "status" "text" DEFAULT 'collected'::"text" NOT NULL,
    "note" "text",
    "created_at" timestamp with time zone DEFAULT "now"() NOT NULL
);


ALTER TABLE "public"."congress_member_snapshot" OWNER TO "postgres";


CREATE SEQUENCE IF NOT EXISTS "public"."congress_member_stg_id_seq"
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE "public"."congress_member_stg_id_seq" OWNER TO "postgres";


ALTER SEQUENCE "public"."congress_member_stg_id_seq" OWNED BY "public"."congress_member_stg"."id";



CREATE TABLE IF NOT EXISTS "public"."congress_raw" (
    "id" bigint NOT NULL,
    "run_id" "uuid" NOT NULL,
    "kind" "text" NOT NULL,
    "committee_cd" "text",
    "url" "text",
    "payload" "jsonb",
    "html" "text",
    "json" "jsonb",
    "created_at" timestamp with time zone DEFAULT "now"() NOT NULL
);


ALTER TABLE "public"."congress_raw" OWNER TO "postgres";


CREATE SEQUENCE IF NOT EXISTS "public"."congress_raw_id_seq"
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE "public"."congress_raw_id_seq" OWNER TO "postgres";


ALTER SEQUENCE "public"."congress_raw_id_seq" OWNED BY "public"."congress_raw"."id";



CREATE TABLE IF NOT EXISTS "public"."crawler_run" (
    "id" "uuid" NOT NULL,
    "target" "text" NOT NULL,
    "started_at" timestamp with time zone DEFAULT "now"() NOT NULL,
    "finished_at" timestamp with time zone,
    "status" "text" DEFAULT 'running'::"text",
    "pages" integer,
    "rows" integer,
    "fail_reason" "text",
    CONSTRAINT "crawler_run_status_check" CHECK (("status" = ANY (ARRAY['running'::"text", 'collected'::"text", 'passed'::"text", 'failed'::"text", 'aborted'::"text"])))
);


ALTER TABLE "public"."crawler_run" OWNER TO "postgres";


CREATE TABLE IF NOT EXISTS "public"."moef_org_hist" (
    "key_hash" "text" NOT NULL,
    "department" "text" NOT NULL,
    "name" "text" NOT NULL,
    "position" "text" NOT NULL,
    "task" "text",
    "phone" "text",
    "row_hash" "text" NOT NULL,
    "valid_from" timestamp with time zone NOT NULL,
    "valid_to" timestamp with time zone,
    "is_current" boolean DEFAULT true NOT NULL
);


ALTER TABLE "public"."moef_org_hist" OWNER TO "postgres";


CREATE TABLE IF NOT EXISTS "public"."motie_org_hist" (
    "key_hash" "text" NOT NULL,
    "name" "text" NOT NULL,
    "position" "text" NOT NULL,
    "department" "text" NOT NULL,
    "task" "text",
    "phone" "text",
    "row_hash" "text" NOT NULL,
    "valid_from" timestamp with time zone DEFAULT "now"() NOT NULL,
    "valid_to" timestamp with time zone,
    "is_current" boolean DEFAULT true NOT NULL,
    "updated_at" timestamp with time zone DEFAULT "now"(),
    "open_run_id" "uuid",
    "close_run_id" "uuid"
);


ALTER TABLE "public"."motie_org_hist" OWNER TO "postgres";


CREATE OR REPLACE VIEW "public"."gov_staff" WITH ("security_invoker"='true') AS
 SELECT 'MOTIE'::"text" AS "source",
    "m"."department",
    "m"."position",
    "m"."name",
    "m"."phone",
    "m"."task",
    "m"."key_hash"
   FROM "public"."motie_org_hist" "m"
UNION ALL
 SELECT 'MOEF'::"text" AS "source",
    "m"."department",
    "m"."position",
    "m"."name",
    "m"."phone",
    "m"."task",
    "m"."key_hash"
   FROM "public"."moef_org_hist" "m";


ALTER TABLE "public"."gov_staff" OWNER TO "postgres";


CREATE TABLE IF NOT EXISTS "public"."moef_org_snapshot" (
    "run_id" "uuid" NOT NULL,
    "taken_at" timestamp with time zone DEFAULT "now"() NOT NULL,
    "rows" integer NOT NULL,
    "status" "text" NOT NULL,
    "note" "text",
    CONSTRAINT "moef_org_snapshot_status_check" CHECK (("status" = ANY (ARRAY['collected'::"text", 'passed'::"text", 'failed'::"text"])))
);


ALTER TABLE "public"."moef_org_snapshot" OWNER TO "postgres";


CREATE TABLE IF NOT EXISTS "public"."moef_org_stg" (
    "run_id" "uuid" NOT NULL,
    "department" "text" NOT NULL,
    "name" "text" NOT NULL,
    "position" "text" NOT NULL,
    "task" "text",
    "phone" "text",
    "key_hash" "text" NOT NULL,
    "row_hash" "text" NOT NULL,
    "created_at" timestamp with time zone DEFAULT "now"() NOT NULL
);


ALTER TABLE "public"."moef_org_stg" OWNER TO "postgres";


CREATE OR REPLACE VIEW "public"."moef_org_cur" WITH ("security_invoker"='on') AS
 SELECT "s"."department",
    "s"."name",
    "s"."position",
    "s"."task",
    "s"."phone",
    "s"."key_hash",
    "s"."row_hash",
    "s"."run_id"
   FROM "public"."moef_org_stg" "s"
  WHERE ("s"."run_id" = ( SELECT "moef_org_snapshot"."run_id"
           FROM "public"."moef_org_snapshot"
          WHERE ("moef_org_snapshot"."status" = 'passed'::"text")
          ORDER BY "moef_org_snapshot"."taken_at" DESC
         LIMIT 1));


ALTER TABLE "public"."moef_org_cur" OWNER TO "postgres";


CREATE TABLE IF NOT EXISTS "public"."motie_org_snapshot" (
    "run_id" "uuid" NOT NULL,
    "taken_at" timestamp with time zone DEFAULT "now"() NOT NULL,
    "rows" integer NOT NULL,
    "status" "text" NOT NULL,
    "note" "text",
    CONSTRAINT "motie_org_snapshot_status_check" CHECK (("status" = ANY (ARRAY['collected'::"text", 'passed'::"text", 'failed'::"text"])))
);


ALTER TABLE "public"."motie_org_snapshot" OWNER TO "postgres";


CREATE TABLE IF NOT EXISTS "public"."motie_org_stg" (
    "run_id" "uuid" NOT NULL,
    "name" "text" NOT NULL,
    "position" "text" NOT NULL,
    "department" "text" NOT NULL,
    "task" "text",
    "phone" "text",
    "key_hash" "text" NOT NULL,
    "row_hash" "text" NOT NULL,
    "created_at" timestamp with time zone DEFAULT "now"() NOT NULL
);


ALTER TABLE "public"."motie_org_stg" OWNER TO "postgres";


CREATE OR REPLACE VIEW "public"."motie_org_cur_last_run_v" WITH ("security_invoker"='on') AS
 SELECT "s"."name",
    "s"."position",
    "s"."department",
    "s"."task",
    "s"."phone",
    "s"."key_hash",
    "s"."row_hash",
    "s"."run_id"
   FROM "public"."motie_org_stg" "s"
  WHERE ("s"."run_id" = ( SELECT "ms"."run_id"
           FROM "public"."motie_org_snapshot" "ms"
          WHERE ("ms"."status" = 'passed'::"text")
          ORDER BY "ms"."taken_at" DESC
         LIMIT 1));


ALTER TABLE "public"."motie_org_cur_last_run_v" OWNER TO "postgres";


CREATE OR REPLACE VIEW "public"."gov_staff_cur" WITH ("security_invoker"='true') AS
 SELECT 'MOTIE'::"text" AS "source",
    "m"."department",
    "m"."position",
    "m"."name",
    "m"."phone",
    "m"."task",
    "m"."key_hash"
   FROM "public"."motie_org_cur_last_run_v" "m"
UNION ALL
 SELECT 'MOEF'::"text" AS "source",
    "m"."department",
    "m"."position",
    "m"."name",
    "m"."phone",
    "m"."task",
    "m"."key_hash"
   FROM "public"."moef_org_cur" "m";


ALTER TABLE "public"."gov_staff_cur" OWNER TO "postgres";


CREATE TABLE IF NOT EXISTS "public"."kepco_history" (
    "id" "uuid" DEFAULT "gen_random_uuid"() NOT NULL,
    "department" "text" NOT NULL,
    "name" "text" NOT NULL,
    "task" "text",
    "position" "text",
    "gender" "text",
    "start" "text",
    "end" "text",
    "actual_end" "text",
    "career" "jsonb",
    "posted_at" "text",
    "pdf_url" "text",
    "created_at" timestamp with time zone DEFAULT "now"() NOT NULL,
    "extended" boolean DEFAULT false NOT NULL,
    "current" boolean DEFAULT false NOT NULL,
    "old_career" boolean DEFAULT false NOT NULL
);


ALTER TABLE "public"."kepco_history" OWNER TO "postgres";


CREATE TABLE IF NOT EXISTS "public"."kepco_id" (
    "id" "text" NOT NULL,
    "department" "text" NOT NULL,
    "disclosure_no" "text" NOT NULL,
    "title" "text",
    "posted_at" "date",
    "pdf_url" "text",
    "created_at" timestamp with time zone DEFAULT "now"() NOT NULL
);


ALTER TABLE "public"."kepco_id" OWNER TO "postgres";


CREATE TABLE IF NOT EXISTS "public"."kepco_id_test" (
    "id" "text" NOT NULL,
    "department" "text",
    "disclosure_no" "text",
    "title" "text",
    "posted_at" "date",
    "pdf_url" "text",
    "created_at" timestamp with time zone DEFAULT "now"()
);


ALTER TABLE "public"."kepco_id_test" OWNER TO "postgres";


CREATE TABLE IF NOT EXISTS "public"."kepco_org" (
    "id" bigint NOT NULL,
    "department" "text",
    "name" "text",
    "position" "text",
    "gender" "text",
    "start" "text",
    "end" "text",
    "task" "text",
    "career" "text",
    "education" "text",
    "photo_url" "text",
    "photo_key" "text"
);


ALTER TABLE "public"."kepco_org" OWNER TO "postgres";


CREATE TABLE IF NOT EXISTS "public"."kepco_org_snapshot" (
    "id" bigint NOT NULL,
    "run_id" "uuid" NOT NULL,
    "snapshot_at" timestamp with time zone DEFAULT "now"() NOT NULL,
    "department" "text" NOT NULL,
    "name" "text",
    "position" "text",
    "gender" "text",
    "start" "text",
    "end" "text",
    "task" "text",
    "reason_date" "text",
    "career" "jsonb",
    "key_hash" "text" NOT NULL
);


ALTER TABLE "public"."kepco_org_snapshot" OWNER TO "postgres";


CREATE OR REPLACE VIEW "public"."kepco_org_cur" WITH ("security_invoker"='on') AS
 WITH "latest" AS (
         SELECT "kepco_org_snapshot"."department",
            "max"("kepco_org_snapshot"."snapshot_at") AS "snap_ts"
           FROM "public"."kepco_org_snapshot"
          GROUP BY "kepco_org_snapshot"."department"
        )
 SELECT "s"."id",
    "s"."run_id",
    "s"."snapshot_at",
    "s"."department",
    "s"."name",
    "s"."position",
    "s"."gender",
    "s"."start",
    "s"."end",
    "s"."task",
    "s"."reason_date",
    "s"."career",
    "s"."key_hash"
   FROM ("public"."kepco_org_snapshot" "s"
     JOIN "latest" "l" ON ((("s"."department" = "l"."department") AND ("s"."snapshot_at" = "l"."snap_ts"))));


ALTER TABLE "public"."kepco_org_cur" OWNER TO "postgres";


ALTER TABLE "public"."kepco_org" ALTER COLUMN "id" ADD GENERATED BY DEFAULT AS IDENTITY (
    SEQUENCE NAME "public"."kepco_org_id_seq"
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);



CREATE SEQUENCE IF NOT EXISTS "public"."kepco_org_snapshot_id_seq"
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE "public"."kepco_org_snapshot_id_seq" OWNER TO "postgres";


ALTER SEQUENCE "public"."kepco_org_snapshot_id_seq" OWNED BY "public"."kepco_org_snapshot"."id";



CREATE TABLE IF NOT EXISTS "public"."kepco_org_stg" (
    "id" bigint NOT NULL,
    "run_id" "uuid" NOT NULL,
    "department" "text" NOT NULL,
    "name" "text",
    "position" "text",
    "gender" "text",
    "start" "text",
    "end" "text",
    "task" "text",
    "reason_date" "text",
    "career" "jsonb",
    "key_hash" "text" GENERATED ALWAYS AS ("md5"(((((COALESCE("department", ''::"text") || '|'::"text") || COALESCE("regexp_replace"(COALESCE("name", ''::"text"), '\s+'::"text", ''::"text", 'g'::"text"), ''::"text")) || '|'::"text") || COALESCE("start", ''::"text")))) STORED
);


ALTER TABLE "public"."kepco_org_stg" OWNER TO "postgres";


CREATE SEQUENCE IF NOT EXISTS "public"."kepco_org_stg_id_seq"
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE "public"."kepco_org_stg_id_seq" OWNER TO "postgres";


ALTER SEQUENCE "public"."kepco_org_stg_id_seq" OWNED BY "public"."kepco_org_stg"."id";



CREATE TABLE IF NOT EXISTS "public"."kepco_raw" (
    "id" bigint NOT NULL,
    "run_id" "uuid" NOT NULL,
    "apba_id" "text" NOT NULL,
    "organ" "text" NOT NULL,
    "disclosure_no" "text" NOT NULL,
    "title" "text",
    "posted_at" "text",
    "posted_at_iso" "date",
    "pdf_url" "text" NOT NULL,
    "pages" integer DEFAULT 0 NOT NULL,
    "merged_text" "text" NOT NULL,
    "collected_at" timestamp with time zone DEFAULT "now"() NOT NULL
);


ALTER TABLE "public"."kepco_raw" OWNER TO "postgres";


CREATE SEQUENCE IF NOT EXISTS "public"."kepco_raw_id_seq"
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE "public"."kepco_raw_id_seq" OWNER TO "postgres";


ALTER SEQUENCE "public"."kepco_raw_id_seq" OWNED BY "public"."kepco_raw"."id";



CREATE TABLE IF NOT EXISTS "public"."me_heads_target" (
    "ord" integer NOT NULL,
    "unit" "text" NOT NULL,
    "expected_pos" "text" NOT NULL,
    "indent" integer DEFAULT 0 NOT NULL
);


ALTER TABLE "public"."me_heads_target" OWNER TO "postgres";


CREATE TABLE IF NOT EXISTS "public"."me_org_hist" (
    "id" bigint NOT NULL,
    "key_hash" "text" NOT NULL,
    "department" "text" NOT NULL,
    "name" "text" NOT NULL,
    "position" "text" NOT NULL,
    "phone" "text",
    "task" "text",
    "row_hash" "text" NOT NULL,
    "valid_from" timestamp with time zone DEFAULT "now"() NOT NULL,
    "valid_to" timestamp with time zone,
    "is_current" boolean DEFAULT true NOT NULL
);


ALTER TABLE "public"."me_org_hist" OWNER TO "postgres";


CREATE OR REPLACE VIEW "public"."me_org_cur" AS
 SELECT "me_org_hist"."key_hash",
    "me_org_hist"."department",
    "me_org_hist"."name",
    "me_org_hist"."position",
    "me_org_hist"."phone",
    "me_org_hist"."task",
    "me_org_hist"."row_hash",
    "me_org_hist"."valid_from"
   FROM "public"."me_org_hist"
  WHERE (("me_org_hist"."is_current" = true) AND ("me_org_hist"."valid_to" IS NULL));


ALTER TABLE "public"."me_org_cur" OWNER TO "postgres";


CREATE OR REPLACE VIEW "public"."me_heads_cur" AS
 WITH "norm" AS (
         SELECT TRIM(BOTH FROM "me_org_cur"."department") AS "department",
            TRIM(BOTH FROM "me_org_cur"."position") AS "position",
            TRIM(BOTH FROM COALESCE("me_org_cur"."name", ''::"text")) AS "name",
            TRIM(BOTH FROM COALESCE("me_org_cur"."phone", ''::"text")) AS "phone",
            TRIM(BOTH FROM COALESCE("me_org_cur"."task", ''::"text")) AS "task"
           FROM "public"."me_org_cur"
        ), "candidates" AS (
         SELECT "t"."unit",
            "t"."expected_pos",
            "t"."ord",
            "t"."indent",
            "n"."name",
            "n"."phone",
            "n"."task",
            "n"."position",
            ((
                CASE
                    WHEN ("n"."position" = "t"."expected_pos") THEN 100
                    WHEN ("n"."position" ~~ (('%'::"text" || "t"."expected_pos") || '%'::"text")) THEN 80
                    ELSE 0
                END +
                CASE
                    WHEN ((NULLIF("n"."phone", ''::"text") IS NOT NULL) AND ("n"."phone" <> '-'::"text")) THEN 5
                    ELSE 0
                END) +
                CASE
                    WHEN (NULLIF("n"."name", ''::"text") IS NOT NULL) THEN 1
                    ELSE 0
                END) AS "score"
           FROM ("public"."me_heads_target" "t"
             LEFT JOIN "norm" "n" ON (("n"."department" = "t"."unit")))
        )
 SELECT DISTINCT ON ("candidates"."ord") "candidates"."unit",
    "candidates"."expected_pos" AS "position",
    NULLIF("candidates"."name", ''::"text") AS "name",
    COALESCE(NULLIF("candidates"."phone", ''::"text"), '-'::"text") AS "phone",
    COALESCE(NULLIF("candidates"."task", ''::"text"), '-'::"text") AS "task",
    "candidates"."ord",
    "candidates"."indent"
   FROM "candidates"
  ORDER BY "candidates"."ord", "candidates"."score" DESC, NULLIF("candidates"."name", ''::"text");


ALTER TABLE "public"."me_heads_cur" OWNER TO "postgres";


CREATE TABLE IF NOT EXISTS "public"."me_id" (
    "id" "text" NOT NULL,
    "created_at" timestamp with time zone DEFAULT "now"() NOT NULL,
    "title" "text",
    "posted_at" "date",
    "url" "text"
);


ALTER TABLE "public"."me_id" OWNER TO "postgres";


COMMENT ON TABLE "public"."me_id" IS 'This is a duplicate of motie_id';



CREATE SEQUENCE IF NOT EXISTS "public"."me_org_hist_id_seq"
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE "public"."me_org_hist_id_seq" OWNER TO "postgres";


ALTER SEQUENCE "public"."me_org_hist_id_seq" OWNED BY "public"."me_org_hist"."id";



CREATE TABLE IF NOT EXISTS "public"."me_org_raw" (
    "run_id" "uuid" NOT NULL,
    "kind" "text" NOT NULL,
    "page" "text" NOT NULL,
    "url" "text",
    "html" "text",
    "created_at" timestamp with time zone DEFAULT "now"() NOT NULL
);


ALTER TABLE "public"."me_org_raw" OWNER TO "postgres";


CREATE TABLE IF NOT EXISTS "public"."me_org_snapshot" (
    "run_id" "uuid" NOT NULL,
    "rows" integer NOT NULL,
    "status" "text" NOT NULL,
    "note" "text",
    "created_at" timestamp with time zone DEFAULT "now"() NOT NULL
);


ALTER TABLE "public"."me_org_snapshot" OWNER TO "postgres";


CREATE TABLE IF NOT EXISTS "public"."me_org_stg" (
    "run_id" "uuid" NOT NULL,
    "department" "text" NOT NULL,
    "name" "text" NOT NULL,
    "position" "text" NOT NULL,
    "phone" "text",
    "task" "text",
    "key_hash" "text" NOT NULL,
    "row_hash" "text" NOT NULL,
    "created_at" timestamp with time zone DEFAULT "now"() NOT NULL
);


ALTER TABLE "public"."me_org_stg" OWNER TO "postgres";


CREATE TABLE IF NOT EXISTS "public"."mod_requests" (
    "id" integer NOT NULL,
    "name" "text" NOT NULL,
    "file_path" "text" NOT NULL,
    "status" "text" DEFAULT 'pending'::"text" NOT NULL,
    "created_at" timestamp without time zone DEFAULT "now"()
);


ALTER TABLE "public"."mod_requests" OWNER TO "postgres";


CREATE SEQUENCE IF NOT EXISTS "public"."mod_requests_id_seq"
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE "public"."mod_requests_id_seq" OWNER TO "postgres";


ALTER SEQUENCE "public"."mod_requests_id_seq" OWNED BY "public"."mod_requests"."id";



CREATE OR REPLACE VIEW "public"."moef_head" WITH ("security_invoker"='on') AS
 WITH "base" AS (
         SELECT "o"."department",
            "o"."name",
            "o"."position",
            "o"."task",
            "o"."phone",
            "o"."key_hash",
            "o"."row_hash",
            "o"."run_id",
            "regexp_replace"("regexp_replace"(TRIM(BOTH FROM "o"."position"), '\s*\(.*\)$'::"text", ''::"text"), '\s+'::"text", ''::"text", 'g'::"text") AS "norm_position",
            TRIM(BOTH FROM "o"."department") AS "dept_trim"
           FROM "public"."moef_org_cur" "o"
        ), "mapped" AS (
         SELECT "b"."department",
            "b"."name",
            "b"."position",
            "b"."task",
            "b"."phone",
            "b"."key_hash",
            "b"."row_hash",
            "b"."run_id",
            "b"."norm_position",
            "b"."dept_trim",
                CASE
                    WHEN ("b"."dept_trim" = '부총리'::"text") THEN '부총리'::"text"
                    WHEN ("b"."dept_trim" = '대변인'::"text") THEN '대변인'::"text"
                    WHEN ("b"."dept_trim" = '장관정책보좌관'::"text") THEN '장관정책보좌관'::"text"
                    WHEN ("b"."dept_trim" = '감사관'::"text") THEN '감사관'::"text"
                    WHEN ("b"."dept_trim" = ANY (ARRAY['제1차관'::"text", '제2차관'::"text"])) THEN '차관'::"text"
                    WHEN ("b"."dept_trim" = '차관보'::"text") THEN '차관보'::"text"
                    WHEN ("b"."dept_trim" = ANY (ARRAY['국제경제관리관'::"text", '재정관리관'::"text"])) THEN '관리관'::"text"
                    WHEN ("b"."dept_trim" = '비상안전기획관'::"text") THEN '국장'::"text"
                    WHEN ("b"."dept_trim" = 'APEC 재무·구조개혁 장관회의 추진단'::"text") THEN '팀장'::"text"
                    WHEN ("b"."dept_trim" ~ '사무처$'::"text") THEN '사무처장'::"text"
                    WHEN ("b"."dept_trim" ~ '(추진단|지원단)$'::"text") THEN '단장'::"text"
                    WHEN ("b"."dept_trim" ~ '실$'::"text") THEN '실장'::"text"
                    WHEN ("b"."dept_trim" ~ '국$'::"text") THEN '국장'::"text"
                    WHEN ("b"."dept_trim" ~ '기획관$'::"text") THEN '기획관'::"text"
                    WHEN ("b"."dept_trim" ~ '정책관$'::"text") THEN '정책관'::"text"
                    WHEN ("b"."dept_trim" ~ '심의관$'::"text") THEN '심의관'::"text"
                    WHEN ("b"."dept_trim" ~ '담당관$'::"text") THEN '담당관'::"text"
                    WHEN ("b"."dept_trim" ~ '과$'::"text") THEN '과장'::"text"
                    WHEN ("b"."dept_trim" ~ '팀$'::"text") THEN '팀장'::"text"
                    ELSE NULL::"text"
                END AS "expected_position",
                CASE
                    WHEN ("b"."dept_trim" = '부총리'::"text") THEN 1
                    WHEN ("b"."dept_trim" = ANY (ARRAY['대변인'::"text", '장관정책보좌관'::"text", '감사관'::"text", '제1차관'::"text", '제2차관'::"text"])) THEN 2
                    WHEN (("b"."dept_trim" = ANY (ARRAY['차관보'::"text", '국제경제관리관'::"text", '재정관리관'::"text"])) OR ("b"."dept_trim" ~ '(실|국|사무처)$'::"text")) THEN 3
                    WHEN ("b"."dept_trim" ~ '(정책관|심의관|기획관)$'::"text") THEN 4
                    WHEN ("b"."dept_trim" ~ '(과|담당관|팀)$'::"text") THEN 5
                    ELSE NULL::integer
                END AS "org_level"
           FROM "base" "b"
        ), "filtered" AS (
         SELECT "m"."department",
            "m"."name",
            "m"."position",
            "m"."task",
            "m"."phone",
            "m"."key_hash",
            "m"."row_hash",
            "m"."run_id",
            "m"."norm_position",
            "m"."dept_trim",
            "m"."expected_position",
            "m"."org_level"
           FROM "mapped" "m"
          WHERE (("m"."expected_position" IS NOT NULL) AND ("m"."norm_position" = "regexp_replace"("m"."expected_position", '\s+'::"text", ''::"text", 'g'::"text")))
        )
 SELECT "filtered"."department",
    "filtered"."position",
    "filtered"."name",
    "filtered"."phone",
    "filtered"."task",
    "filtered"."expected_position",
    "filtered"."org_level"
   FROM "filtered";


ALTER TABLE "public"."moef_head" OWNER TO "postgres";


CREATE TABLE IF NOT EXISTS "public"."moef_heads_target" (
    "ord" integer NOT NULL,
    "unit" "text" NOT NULL,
    "expected_pos" "text" NOT NULL,
    "indent" integer DEFAULT 0 NOT NULL
);


ALTER TABLE "public"."moef_heads_target" OWNER TO "postgres";


CREATE OR REPLACE VIEW "public"."moef_heads_cur" AS
 WITH "norm" AS (
         SELECT TRIM(BOTH FROM "moef_org_cur"."department") AS "department",
            TRIM(BOTH FROM "moef_org_cur"."position") AS "position",
            TRIM(BOTH FROM COALESCE("moef_org_cur"."name", ''::"text")) AS "name",
            TRIM(BOTH FROM COALESCE("moef_org_cur"."phone", ''::"text")) AS "phone",
            TRIM(BOTH FROM COALESCE("moef_org_cur"."task", ''::"text")) AS "task"
           FROM "public"."moef_org_cur"
        ), "candidates" AS (
         SELECT "t"."unit",
            "t"."expected_pos",
            "t"."ord",
            "t"."indent",
            "n"."name",
            "n"."phone",
            "n"."task",
            "n"."position",
            ((
                CASE
                    WHEN ("n"."position" = "t"."expected_pos") THEN 100
                    WHEN ("n"."position" ~~ (('%'::"text" || "t"."expected_pos") || '%'::"text")) THEN 80
                    ELSE 0
                END +
                CASE
                    WHEN ((NULLIF("n"."phone", ''::"text") IS NOT NULL) AND ("n"."phone" <> '-'::"text")) THEN 5
                    ELSE 0
                END) +
                CASE
                    WHEN (NULLIF("n"."name", ''::"text") IS NOT NULL) THEN 1
                    ELSE 0
                END) AS "score"
           FROM ("public"."moef_heads_target" "t"
             LEFT JOIN "norm" "n" ON (("n"."department" = "t"."unit")))
        )
 SELECT DISTINCT ON ("candidates"."ord") "candidates"."unit",
    "candidates"."expected_pos" AS "position",
    NULLIF("candidates"."name", ''::"text") AS "name",
    COALESCE(NULLIF("candidates"."phone", ''::"text"), '-'::"text") AS "phone",
    COALESCE(NULLIF("candidates"."task", ''::"text"), '-'::"text") AS "task",
    "candidates"."ord",
    "candidates"."indent"
   FROM "candidates"
  ORDER BY "candidates"."ord", "candidates"."score" DESC, NULLIF("candidates"."name", ''::"text");


ALTER TABLE "public"."moef_heads_cur" OWNER TO "postgres";


CREATE TABLE IF NOT EXISTS "public"."moef_id" (
    "id" "text" NOT NULL,
    "created_at" timestamp with time zone DEFAULT "now"() NOT NULL,
    "bbsId" "text",
    "postId" "text",
    "title" "text",
    "tag" "text",
    "posted_at" "date"
);


ALTER TABLE "public"."moef_id" OWNER TO "postgres";


COMMENT ON TABLE "public"."moef_id" IS 'This is a duplicate of motie_id';



CREATE OR REPLACE VIEW "public"."moef_kps" WITH ("security_invoker"='true') AS
 WITH "pos_pairs"("dept", "pos") AS (
         VALUES ('부총리'::"text",'부총리'::"text"), ('부총리'::"text",'비서실장'::"text"), ('제1차관'::"text",'차관'::"text"), ('제2차관'::"text",'차관'::"text"), ('공공정책국'::"text",'국장'::"text"), ('공공혁신심의관'::"text",'심의관'::"text"), ('공공정책총괄과'::"text",'과장'::"text"), ('공공제도기획과'::"text",'과장'::"text"), ('재무경영과'::"text",'과장'::"text"), ('평가분석과'::"text",'과장'::"text"), ('인재경영과'::"text",'과장'::"text"), ('공공윤리정책과'::"text",'과장'::"text"), ('공공혁신기획과'::"text",'과장'::"text"), ('경영관리과'::"text",'과장'::"text")
        ), "phone_pairs"("dept", "phone_txt") AS (
         VALUES ('제1차관'::"text",'044-215-2028'::"text"), ('제2차관'::"text",'044-215-2022'::"text"), ('재무경영과'::"text",'044-215-5631'::"text")
        ), "src" AS (
         SELECT TRIM(BOTH FROM "m"."department") AS "department",
            TRIM(BOTH FROM "m"."position") AS "position",
            TRIM(BOTH FROM "m"."name") AS "name",
            TRIM(BOTH FROM "m"."task") AS "task",
            TRIM(BOTH FROM "m"."phone") AS "phone",
            "m"."key_hash"
           FROM "public"."moef_org_cur" "m"
        ), "norm" AS (
         SELECT "s"."department",
            "s"."position",
            "s"."name",
            "s"."task",
            "s"."phone",
            "s"."key_hash",
            "replace"(COALESCE("s"."department", ''::"text"), ' '::"text", ''::"text") AS "dep_nospace",
            "replace"(COALESCE("s"."position", ''::"text"), ' '::"text", ''::"text") AS "pos_nospace",
            "regexp_replace"(COALESCE("s"."phone", ''::"text"), '[^0-9]'::"text", ''::"text", 'g'::"text") AS "phone_digits"
           FROM "src" "s"
        ), "norm_phone" AS (
         SELECT "p"."dept",
            "p"."phone_txt",
            "replace"("p"."dept", ' '::"text", ''::"text") AS "dept_nospace",
            "regexp_replace"("p"."phone_txt", '[^0-9]'::"text", ''::"text", 'g'::"text") AS "phone_digits"
           FROM "phone_pairs" "p"
        ), "base" AS (
         SELECT "n"."department",
            "n"."position",
            "n"."name",
            "n"."task",
            "n"."phone",
            "n"."key_hash",
            "n"."dep_nospace",
            "n"."pos_nospace",
            "n"."phone_digits"
           FROM ("norm" "n"
             JOIN "pos_pairs" "p" ON ((("n"."dep_nospace" = "replace"("p"."dept", ' '::"text", ''::"text")) AND ("n"."pos_nospace" = "replace"("p"."pos", ' '::"text", ''::"text")))))
        UNION ALL
         SELECT "n"."department",
            "n"."position",
            "n"."name",
            "n"."task",
            "n"."phone",
            "n"."key_hash",
            "n"."dep_nospace",
            "n"."pos_nospace",
            "n"."phone_digits"
           FROM ("norm" "n"
             JOIN "norm_phone" "ph" ON ((("n"."dep_nospace" = "ph"."dept_nospace") AND ("n"."phone_digits" = "ph"."phone_digits"))))
        UNION ALL
         SELECT "n"."department",
            "n"."position",
            "n"."name",
            "n"."task",
            "n"."phone",
            "n"."key_hash",
            "n"."dep_nospace",
            "n"."pos_nospace",
            "n"."phone_digits"
           FROM "norm" "n"
          WHERE (("n"."dep_nospace" = '공공정책국'::"text") AND (COALESCE("n"."pos_nospace", ''::"text") <> '국장'::"text"))
        UNION ALL
         SELECT "n"."department",
            "n"."position",
            "n"."name",
            "n"."task",
            "n"."phone",
            "n"."key_hash",
            "n"."dep_nospace",
            "n"."pos_nospace",
            "n"."phone_digits"
           FROM "norm" "n"
          WHERE (("n"."dep_nospace" = '공공혁신심의관'::"text") AND (COALESCE("n"."pos_nospace", ''::"text") <> '심의관'::"text"))
        UNION ALL
         SELECT "n"."department",
            "n"."position",
            "n"."name",
            "n"."task",
            "n"."phone",
            "n"."key_hash",
            "n"."dep_nospace",
            "n"."pos_nospace",
            "n"."phone_digits"
           FROM "norm" "n"
          WHERE (("n"."dep_nospace" = '공공정책총괄과'::"text") AND ("n"."pos_nospace" = ANY (ARRAY['사무관'::"text", '서기관'::"text"])) AND (COALESCE("n"."task", ''::"text") ~~* '%정책%'::"text") AND (COALESCE("n"."task", ''::"text") !~~* '%총괄%'::"text"))
        UNION ALL
         SELECT "n"."department",
            "n"."position",
            "n"."name",
            "n"."task",
            "n"."phone",
            "n"."key_hash",
            "n"."dep_nospace",
            "n"."pos_nospace",
            "n"."phone_digits"
           FROM "norm" "n"
          WHERE (("n"."dep_nospace" = '공공제도기획과'::"text") AND ("n"."pos_nospace" = ANY (ARRAY['사무관'::"text", '서기관'::"text"])) AND ((COALESCE("n"."task", ''::"text") ~~* '%예산%'::"text") OR (COALESCE("n"."task", ''::"text") ~~* '%총인건비%'::"text")))
        UNION ALL
         SELECT "n"."department",
            "n"."position",
            "n"."name",
            "n"."task",
            "n"."phone",
            "n"."key_hash",
            "n"."dep_nospace",
            "n"."pos_nospace",
            "n"."phone_digits"
           FROM "norm" "n"
          WHERE (("n"."dep_nospace" = '평가분석과'::"text") AND ("n"."pos_nospace" = ANY (ARRAY['사무관'::"text", '서기관'::"text"])) AND (COALESCE("n"."task", ''::"text") ~~* '%기관평가%'::"text") AND (COALESCE("n"."task", ''::"text") ~~* '%산업진흥%'::"text"))
        UNION ALL
         SELECT "n"."department",
            "n"."position",
            "n"."name",
            "n"."task",
            "n"."phone",
            "n"."key_hash",
            "n"."dep_nospace",
            "n"."pos_nospace",
            "n"."phone_digits"
           FROM "norm" "n"
          WHERE (("n"."dep_nospace" = '인재경영과'::"text") AND ("n"."pos_nospace" = ANY (ARRAY['사무관'::"text", '서기관'::"text"])) AND (COALESCE("n"."task", ''::"text") ~~* '%임원%'::"text"))
        UNION ALL
         SELECT "n"."department",
            "n"."position",
            "n"."name",
            "n"."task",
            "n"."phone",
            "n"."key_hash",
            "n"."dep_nospace",
            "n"."pos_nospace",
            "n"."phone_digits"
           FROM "norm" "n"
          WHERE (("n"."dep_nospace" = '공공윤리정책과'::"text") AND ("n"."pos_nospace" = ANY (ARRAY['사무관'::"text", '서기관'::"text"])) AND (COALESCE("n"."task", ''::"text") ~~* '%윤리%'::"text"))
        UNION ALL
         SELECT "n"."department",
            "n"."position",
            "n"."name",
            "n"."task",
            "n"."phone",
            "n"."key_hash",
            "n"."dep_nospace",
            "n"."pos_nospace",
            "n"."phone_digits"
           FROM "norm" "n"
          WHERE (("n"."dep_nospace" = '공공혁신기획과'::"text") AND ("n"."pos_nospace" = ANY (ARRAY['사무관'::"text", '서기관'::"text"])) AND (COALESCE("n"."task", ''::"text") ~~* '%혁신%'::"text"))
        UNION ALL
         SELECT "n"."department",
            "n"."position",
            "n"."name",
            "n"."task",
            "n"."phone",
            "n"."key_hash",
            "n"."dep_nospace",
            "n"."pos_nospace",
            "n"."phone_digits"
           FROM "norm" "n"
          WHERE (("n"."dep_nospace" = '경영관리과'::"text") AND ("n"."pos_nospace" = ANY (ARRAY['사무관'::"text", '서기관'::"text"])) AND (COALESCE("n"."task", ''::"text") ~~* '%공시%'::"text"))
        )
 SELECT DISTINCT "dense_rank"() OVER (ORDER BY "b"."key_hash") AS "id",
    "b"."department",
    "b"."position",
    "b"."name",
    "b"."phone",
    "b"."task"
   FROM "base" "b";


ALTER TABLE "public"."moef_kps" OWNER TO "postgres";


CREATE TABLE IF NOT EXISTS "public"."moef_org" (
    "id" bigint NOT NULL,
    "department" "text" NOT NULL,
    "name" "text",
    "position" "text",
    "task" "text",
    "phone" "text"
);


ALTER TABLE "public"."moef_org" OWNER TO "postgres";


COMMENT ON TABLE "public"."moef_org" IS 'This is a duplicate of motie_org';



ALTER TABLE "public"."moef_org" ALTER COLUMN "id" ADD GENERATED BY DEFAULT AS IDENTITY (
    SEQUENCE NAME "public"."moef_org_id_seq"
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);



CREATE TABLE IF NOT EXISTS "public"."moef_org_raw" (
    "run_id" "uuid" NOT NULL,
    "kind" "text" NOT NULL,
    "page" integer DEFAULT 0 NOT NULL,
    "url" "text" DEFAULT ''::"text" NOT NULL,
    "html" "text" NOT NULL,
    "fetched_at" timestamp with time zone DEFAULT "now"() NOT NULL,
    CONSTRAINT "moef_org_raw_kind_check" CHECK (("kind" = ANY (ARRAY['list'::"text", 'detail'::"text"])))
);


ALTER TABLE "public"."moef_org_raw" OWNER TO "postgres";


CREATE TABLE IF NOT EXISTS "public"."motie_heads_target" (
    "unit" "text" NOT NULL,
    "expected_pos" "text" NOT NULL,
    "ord" integer DEFAULT 0 NOT NULL,
    "indent" integer DEFAULT 0 NOT NULL
);


ALTER TABLE "public"."motie_heads_target" OWNER TO "postgres";


CREATE OR REPLACE VIEW "public"."motie_org_cur" AS
 SELECT "h"."key_hash",
    "h"."name",
    "h"."position",
    "h"."department",
    "h"."task",
    "h"."phone",
    "h"."row_hash",
    "h"."valid_from" AS "started_at",
    "h"."valid_to" AS "ended_at",
    ("h"."valid_to" IS NULL) AS "is_open",
    "h"."updated_at"
   FROM "public"."motie_org_hist" "h"
  WHERE ("h"."valid_to" IS NULL);


ALTER TABLE "public"."motie_org_cur" OWNER TO "postgres";


CREATE OR REPLACE VIEW "public"."motie_heads_cur" AS
 WITH "norm" AS (
         SELECT TRIM(BOTH FROM "motie_org_cur"."department") AS "department",
            TRIM(BOTH FROM "motie_org_cur"."position") AS "position",
            TRIM(BOTH FROM COALESCE("motie_org_cur"."name", ''::"text")) AS "name",
            TRIM(BOTH FROM COALESCE("motie_org_cur"."phone", ''::"text")) AS "phone",
            TRIM(BOTH FROM COALESCE("motie_org_cur"."task", ''::"text")) AS "task"
           FROM "public"."motie_org_cur"
        ), "candidates" AS (
         SELECT "t"."unit",
            "t"."expected_pos",
            "t"."ord",
            "t"."indent",
            "n"."name",
            "n"."phone",
            "n"."task",
            "n"."position",
            ((
                CASE
                    WHEN ("n"."position" = "t"."expected_pos") THEN 100
                    WHEN (("t"."expected_pos" = '대변인'::"text") AND ("n"."position" ~~ '%대변인%'::"text") AND ("n"."position" !~~ '%부대변인%'::"text")) THEN 90
                    WHEN ("n"."position" ~~ (('%'::"text" || "t"."expected_pos") || '%'::"text")) THEN 80
                    ELSE 0
                END +
                CASE
                    WHEN ((NULLIF("n"."phone", ''::"text") IS NOT NULL) AND ("n"."phone" <> '-'::"text")) THEN 5
                    ELSE 0
                END) +
                CASE
                    WHEN (NULLIF("n"."name", ''::"text") IS NOT NULL) THEN 1
                    ELSE 0
                END) AS "score"
           FROM ("public"."motie_heads_target" "t"
             LEFT JOIN "norm" "n" ON (("n"."department" = "t"."unit")))
        )
 SELECT DISTINCT ON ("candidates"."ord") "candidates"."unit",
    "candidates"."expected_pos" AS "position",
    NULLIF("candidates"."name", ''::"text") AS "name",
    COALESCE(NULLIF("candidates"."phone", ''::"text"), '-'::"text") AS "phone",
    COALESCE(NULLIF("candidates"."task", ''::"text"), '-'::"text") AS "task",
    "candidates"."ord",
    "candidates"."indent"
   FROM "candidates"
  ORDER BY "candidates"."ord", "candidates"."score" DESC, NULLIF("candidates"."name", ''::"text");


ALTER TABLE "public"."motie_heads_cur" OWNER TO "postgres";


CREATE TABLE IF NOT EXISTS "public"."motie_id" (
    "id" "text" NOT NULL,
    "created_at" timestamp with time zone DEFAULT "now"() NOT NULL,
    "title" "text",
    "posted_at" "date"
);


ALTER TABLE "public"."motie_id" OWNER TO "postgres";


CREATE OR REPLACE VIEW "public"."motie_kps" WITH ("security_invoker"='true') AS
 WITH "wanted_pos"("dept", "pos") AS (
         VALUES ('장관실'::"text",'장관'::"text"), ('제1차관'::"text",'1차관'::"text"), ('기획조정실'::"text",'실장'::"text"), ('정책기획관'::"text",'국장'::"text"), ('혁신행정담당관'::"text",'과장'::"text"), ('제2차관'::"text",'2차관'::"text"), ('에너지정책실'::"text",'실장'::"text"), ('전력정책관'::"text",'국장'::"text"), ('전력산업정책과'::"text",'과장'::"text"), ('원전산업정책국'::"text",'국장'::"text"), ('원전산업정책과'::"text",'과장'::"text"), ('원전환경과'::"text",'과장'::"text"), ('원전지역협력과'::"text",'과장'::"text"), ('원전전략기획관'::"text",'국장'::"text"), ('원전수출진흥과'::"text",'과장'::"text"), ('원전수출협력과'::"text",'과장'::"text")
        ), "wanted_phone"("dept", "phone_txt") AS (
         VALUES ('장관실'::"text",'044-203-5003'::"text"), ('장관실'::"text",'044-203-5005'::"text"), ('장관실'::"text",'044-203-5000'::"text"), ('제1차관'::"text",'044-203-5016'::"text"), ('기획조정실'::"text",'044-203-5201'::"text"), ('혁신행정담당관'::"text",'044-203-5563'::"text"), ('제2차관'::"text",'044-203-5022'::"text"), ('에너지정책실'::"text",'044-203-5701'::"text"), ('전력산업정책과'::"text",'044-203-3925'::"text"), ('전력산업정책과'::"text",'044-203-3890'::"text")
        ), "src" AS (
         SELECT TRIM(BOTH FROM "motie_org_cur_last_run_v"."department") AS "department",
            TRIM(BOTH FROM "motie_org_cur_last_run_v"."position") AS "position",
            TRIM(BOTH FROM "motie_org_cur_last_run_v"."name") AS "name",
            TRIM(BOTH FROM "motie_org_cur_last_run_v"."task") AS "task",
            TRIM(BOTH FROM "motie_org_cur_last_run_v"."phone") AS "phone"
           FROM "public"."motie_org_cur_last_run_v"
        ), "norm_src" AS (
         SELECT "s"."department",
            "s"."position",
            "s"."name",
            "s"."task",
            "s"."phone",
            "replace"("s"."department", ' '::"text", ''::"text") AS "dep_nospace",
            "replace"(COALESCE("s"."position", ''::"text"), ' '::"text", ''::"text") AS "pos_nospace",
            "regexp_replace"(COALESCE("s"."phone", ''::"text"), '[^0-9]'::"text", ''::"text", 'g'::"text") AS "phone_digits"
           FROM "src" "s"
        ), "norm_phone" AS (
         SELECT "w"."dept",
            "w"."phone_txt",
            "regexp_replace"("w"."phone_txt", '[^0-9]'::"text", ''::"text", 'g'::"text") AS "phone_digits"
           FROM "wanted_phone" "w"
        )
 SELECT DISTINCT "m"."department",
    "m"."position",
    "m"."name",
    "m"."task",
    "m"."phone"
   FROM (("norm_src" "m"
     LEFT JOIN "wanted_pos" "wp" ON ((("m"."dep_nospace" = "replace"("wp"."dept", ' '::"text", ''::"text")) AND ("m"."pos_nospace" = "replace"("wp"."pos", ' '::"text", ''::"text")))))
     LEFT JOIN "norm_phone" "wph" ON ((("m"."dep_nospace" = "replace"("wph"."dept", ' '::"text", ''::"text")) AND ("m"."phone_digits" = "wph"."phone_digits"))))
  WHERE (("wp"."dept" IS NOT NULL) OR ("wph"."dept" IS NOT NULL));


ALTER TABLE "public"."motie_kps" OWNER TO "postgres";


CREATE OR REPLACE VIEW "public"."motie_norm_hist" WITH ("security_invoker"='on') AS
 SELECT "motie_org_hist"."key_hash",
    "motie_org_hist"."name",
    TRIM(BOTH FROM "motie_org_hist"."department") AS "department",
    TRIM(BOTH FROM "motie_org_hist"."position") AS "position",
    COALESCE("regexp_replace"("motie_org_hist"."phone", '\s+'::"text", ''::"text", 'g'::"text"), ''::"text") AS "phone",
    COALESCE(TRIM(BOTH FROM "motie_org_hist"."task"), ''::"text") AS "task",
    "motie_org_hist"."row_hash",
    "motie_org_hist"."valid_from",
    "motie_org_hist"."valid_to",
    "motie_org_hist"."is_current"
   FROM "public"."motie_org_hist";


ALTER TABLE "public"."motie_norm_hist" OWNER TO "postgres";


CREATE TABLE IF NOT EXISTS "public"."motie_org" (
    "id" bigint NOT NULL,
    "department" "text" NOT NULL,
    "name" "text",
    "position" "text",
    "task" "text",
    "phone" "text"
);


ALTER TABLE "public"."motie_org" OWNER TO "postgres";


COMMENT ON TABLE "public"."motie_org" IS '산업부';



CREATE TABLE IF NOT EXISTS "public"."motie_org_chart" (
    "id" bigint NOT NULL,
    "parent_id" bigint,
    "department" "text" NOT NULL,
    "leader_id" bigint,
    "order_no" integer DEFAULT 0,
    "level" integer DEFAULT 0
);


ALTER TABLE "public"."motie_org_chart" OWNER TO "postgres";


ALTER TABLE "public"."motie_org_chart" ALTER COLUMN "id" ADD GENERATED BY DEFAULT AS IDENTITY (
    SEQUENCE NAME "public"."motie_org_chart_id_seq"
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);



CREATE TABLE IF NOT EXISTS "public"."motie_org_event_overrides" (
    "id" bigint NOT NULL,
    "from_key_hash" "text",
    "to_key_hash" "text",
    "key_hash" "text",
    "override_type" "text" NOT NULL,
    "note" "text",
    "created_by" "text",
    "created_at" timestamp with time zone DEFAULT "now"() NOT NULL,
    CONSTRAINT "motie_org_event_overrides_override_type_check" CHECK (("override_type" = ANY (ARRAY['transfer'::"text", 'replacement'::"text", 'ignore'::"text"])))
);


ALTER TABLE "public"."motie_org_event_overrides" OWNER TO "postgres";


CREATE SEQUENCE IF NOT EXISTS "public"."motie_org_event_overrides_id_seq"
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE "public"."motie_org_event_overrides_id_seq" OWNER TO "postgres";


ALTER SEQUENCE "public"."motie_org_event_overrides_id_seq" OWNED BY "public"."motie_org_event_overrides"."id";



CREATE OR REPLACE VIEW "public"."motie_org_events" AS
 WITH "base" AS (
         SELECT "h"."key_hash",
            "h"."name",
            "h"."department",
            "h"."position",
            "h"."phone",
            "h"."task",
            "h"."valid_from",
            "h"."valid_to",
            "h"."open_run_id",
            "h"."close_run_id"
           FROM "public"."motie_org_hist" "h"
        ), "closed" AS (
         SELECT "b"."key_hash",
            "b"."name",
            "b"."department",
            "b"."position",
            "b"."phone",
            "b"."task",
            "b"."valid_from",
            "b"."valid_to",
            "b"."open_run_id",
            "b"."close_run_id"
           FROM "base" "b"
          WHERE ("b"."valid_to" IS NOT NULL)
        ), "opened" AS (
         SELECT "b"."key_hash",
            "b"."name",
            "b"."department",
            "b"."position",
            "b"."phone",
            "b"."task",
            "b"."valid_from",
            "b"."valid_to",
            "b"."open_run_id",
            "b"."close_run_id"
           FROM "base" "b"
          WHERE ("b"."valid_to" IS NULL)
        ), "pairs" AS (
         SELECT "c"."key_hash" AS "key_hash_before",
            "a"."key_hash" AS "key_hash_after",
            "c"."name",
            "c"."department" AS "dep_before",
            "a"."department" AS "dep_after",
            "c"."position" AS "pos_before",
            "a"."position" AS "pos_after",
            "c"."phone" AS "phone_before",
            "a"."phone" AS "phone_after",
            COALESCE("a"."task", "c"."task") AS "task_after",
            "a"."valid_from",
            "c"."valid_to",
            "a"."open_run_id" AS "run_after",
            "c"."close_run_id" AS "run_before",
            (("a"."open_run_id" IS NOT NULL) AND ("a"."open_run_id" = "c"."close_run_id")) AS "same_run",
            ("abs"(EXTRACT(epoch FROM ("a"."valid_from" - "c"."valid_to"))))::integer AS "pair_delta_sec",
            "array_remove"(ARRAY[
                CASE
                    WHEN ("a"."department" IS DISTINCT FROM "c"."department") THEN 'department'::"text"
                    ELSE NULL::"text"
                END,
                CASE
                    WHEN ("a"."position" IS DISTINCT FROM "c"."position") THEN 'position'::"text"
                    ELSE NULL::"text"
                END,
                CASE
                    WHEN ("a"."phone" IS DISTINCT FROM "c"."phone") THEN 'phone'::"text"
                    ELSE NULL::"text"
                END,
                CASE
                    WHEN ("a"."task" IS DISTINCT FROM "c"."task") THEN 'task'::"text"
                    ELSE NULL::"text"
                END], NULL::"text") AS "changed_fields",
                CASE
                    WHEN (("a"."open_run_id" IS NOT NULL) AND ("a"."open_run_id" = "c"."close_run_id")) THEN 0
                    ELSE 1
                END AS "pair_rank"
           FROM ("closed" "c"
             JOIN LATERAL ( SELECT "a1"."key_hash",
                    "a1"."name",
                    "a1"."department",
                    "a1"."position",
                    "a1"."phone",
                    "a1"."task",
                    "a1"."valid_from",
                    "a1"."valid_to",
                    "a1"."open_run_id",
                    "a1"."close_run_id"
                   FROM "opened" "a1"
                  WHERE (("a1"."name" = "c"."name") AND ((("a1"."open_run_id" IS NOT NULL) AND ("a1"."open_run_id" = "c"."close_run_id")) OR (("a1"."valid_from" >= ("c"."valid_to" - '00:03:00'::interval)) AND ("a1"."valid_from" <= ("c"."valid_to" + '00:03:00'::interval)))))
                  ORDER BY (("a1"."open_run_id" IS NOT NULL) AND ("a1"."open_run_id" = "c"."close_run_id")) DESC, ("abs"(EXTRACT(epoch FROM ("a1"."valid_from" - "c"."valid_to"))))
                 LIMIT 1) "a" ON (true))
        ), "updated" AS (
         SELECT COALESCE("p"."key_hash_after", "p"."key_hash_before") AS "key_hash",
            "p"."name",
            "p"."dep_after" AS "department",
            "p"."pos_after" AS "position",
            "p"."phone_after" AS "phone",
            "p"."task_after" AS "task",
            "p"."valid_from",
            "p"."valid_to",
            "p"."changed_fields",
            'updated'::"text" AS "event_type",
            "p"."run_after" AS "run_id",
            "p"."dep_before",
            "p"."pos_before",
            "p"."phone_before",
            "p"."same_run",
            "p"."pair_delta_sec",
            "p"."pair_rank"
           FROM "pairs" "p"
        ), "removed" AS (
         SELECT "c"."key_hash",
            "c"."name",
            "c"."department",
            "c"."position",
            "c"."phone",
            "c"."task",
            "c"."valid_from",
            "c"."valid_to",
            ARRAY[]::"text"[] AS "changed_fields",
            'removed'::"text" AS "event_type",
            "c"."close_run_id" AS "run_id",
            "c"."department" AS "dep_before",
            "c"."position" AS "pos_before",
            "c"."phone" AS "phone_before",
            false AS "same_run",
            NULL::integer AS "pair_delta_sec",
            2 AS "pair_rank"
           FROM "closed" "c"
          WHERE (NOT (EXISTS ( SELECT 1
                   FROM "pairs" "p"
                  WHERE ("p"."key_hash_before" = "c"."key_hash"))))
        ), "added" AS (
         SELECT "a"."key_hash",
            "a"."name",
            "a"."department",
            "a"."position",
            "a"."phone",
            "a"."task",
            "a"."valid_from",
            "a"."valid_to",
            ARRAY['department'::"text", 'position'::"text", 'phone'::"text", 'task'::"text"] AS "changed_fields",
            'added'::"text" AS "event_type",
            "a"."open_run_id" AS "run_id",
            NULL::"text" AS "dep_before",
            NULL::"text" AS "pos_before",
            NULL::"text" AS "phone_before",
            false AS "same_run",
            NULL::integer AS "pair_delta_sec",
            2 AS "pair_rank"
           FROM "opened" "a"
          WHERE (NOT (EXISTS ( SELECT 1
                   FROM "pairs" "p"
                  WHERE ("p"."key_hash_after" = "a"."key_hash"))))
        )
 SELECT "e"."key_hash",
    "e"."name",
    "e"."department",
    "e"."position",
    "e"."phone",
    "e"."task",
    "e"."valid_from",
    "e"."valid_to",
    "e"."changed_fields",
    "e"."event_type",
    "e"."run_id",
    "e"."dep_before",
    "e"."pos_before",
    "e"."phone_before",
    "e"."same_run",
    "e"."pair_delta_sec"
   FROM ( SELECT "updated"."key_hash",
            "updated"."name",
            "updated"."department",
            "updated"."position",
            "updated"."phone",
            "updated"."task",
            "updated"."valid_from",
            "updated"."valid_to",
            "updated"."changed_fields",
            "updated"."event_type",
            "updated"."run_id",
            "updated"."dep_before",
            "updated"."pos_before",
            "updated"."phone_before",
            "updated"."same_run",
            "updated"."pair_delta_sec",
            "updated"."pair_rank"
           FROM "updated"
        UNION ALL
         SELECT "removed"."key_hash",
            "removed"."name",
            "removed"."department",
            "removed"."position",
            "removed"."phone",
            "removed"."task",
            "removed"."valid_from",
            "removed"."valid_to",
            "removed"."changed_fields",
            "removed"."event_type",
            "removed"."run_id",
            "removed"."dep_before",
            "removed"."pos_before",
            "removed"."phone_before",
            "removed"."same_run",
            "removed"."pair_delta_sec",
            "removed"."pair_rank"
           FROM "removed"
        UNION ALL
         SELECT "added"."key_hash",
            "added"."name",
            "added"."department",
            "added"."position",
            "added"."phone",
            "added"."task",
            "added"."valid_from",
            "added"."valid_to",
            "added"."changed_fields",
            "added"."event_type",
            "added"."run_id",
            "added"."dep_before",
            "added"."pos_before",
            "added"."phone_before",
            "added"."same_run",
            "added"."pair_delta_sec",
            "added"."pair_rank"
           FROM "added") "e";


ALTER TABLE "public"."motie_org_events" OWNER TO "postgres";


CREATE MATERIALIZED VIEW "public"."motie_org_events_mv" AS
 SELECT "motie_org_events"."key_hash",
    "motie_org_events"."name",
    "motie_org_events"."department",
    "motie_org_events"."position",
    "motie_org_events"."phone",
    "motie_org_events"."task",
    "motie_org_events"."valid_from",
    "motie_org_events"."valid_to",
    "motie_org_events"."changed_fields",
    "motie_org_events"."event_type",
    "motie_org_events"."run_id",
    "motie_org_events"."dep_before",
    "motie_org_events"."pos_before",
    "motie_org_events"."phone_before",
    "motie_org_events"."same_run",
    "motie_org_events"."pair_delta_sec"
   FROM "public"."motie_org_events"
  WITH NO DATA;


ALTER TABLE "public"."motie_org_events_mv" OWNER TO "postgres";


CREATE OR REPLACE VIEW "public"."motie_org_events_v3" AS
 WITH "base" AS (
         SELECT "h"."key_hash",
            "h"."name",
            "h"."department",
            "h"."position",
            "h"."phone",
            "h"."task",
            "h"."valid_from",
            "h"."valid_to",
            "h"."open_run_id",
            "h"."close_run_id"
           FROM "public"."motie_org_hist" "h"
        ), "closed" AS (
         SELECT "base"."key_hash",
            "base"."name",
            "base"."department",
            "base"."position",
            "base"."phone",
            "base"."task",
            "base"."valid_from",
            "base"."valid_to",
            "base"."open_run_id",
            "base"."close_run_id"
           FROM "base"
          WHERE ("base"."valid_to" IS NOT NULL)
        ), "added" AS (
         SELECT "base"."key_hash",
            "base"."name",
            "base"."department",
            "base"."position",
            "base"."phone",
            "base"."task",
            "base"."valid_from",
            "base"."valid_to",
            "base"."open_run_id",
            "base"."close_run_id"
           FROM "base"
          WHERE ("base"."valid_to" IS NULL)
        ), "pairs" AS (
         SELECT "c"."key_hash" AS "key_hash_before",
            "a"."key_hash" AS "key_hash_after",
            "c"."name",
            "c"."department" AS "dep_before",
            "a"."department" AS "dep_after",
            "c"."position" AS "pos_before",
            "a"."position" AS "pos_after",
            "c"."phone" AS "phone_before",
            "a"."phone" AS "phone_after",
            COALESCE("a"."task", "c"."task") AS "task_after",
            "a"."valid_from",
            "c"."valid_to",
            "a"."open_run_id" AS "run_after",
            "c"."close_run_id" AS "run_before",
            (("a"."open_run_id" IS NOT NULL) AND ("a"."open_run_id" = "c"."close_run_id")) AS "same_run",
            ("abs"(EXTRACT(epoch FROM ("a"."valid_from" - "c"."valid_to"))))::integer AS "pair_delta_sec",
            "array_remove"(ARRAY[
                CASE
                    WHEN ("a"."department" IS DISTINCT FROM "c"."department") THEN 'department'::"text"
                    ELSE NULL::"text"
                END,
                CASE
                    WHEN ("a"."position" IS DISTINCT FROM "c"."position") THEN 'position'::"text"
                    ELSE NULL::"text"
                END,
                CASE
                    WHEN ("a"."phone" IS DISTINCT FROM "c"."phone") THEN 'phone'::"text"
                    ELSE NULL::"text"
                END,
                CASE
                    WHEN ("a"."task" IS DISTINCT FROM "c"."task") THEN 'task'::"text"
                    ELSE NULL::"text"
                END], NULL::"text") AS "changed_fields"
           FROM ("closed" "c"
             JOIN LATERAL ( SELECT "a_1"."key_hash",
                    "a_1"."name",
                    "a_1"."department",
                    "a_1"."position",
                    "a_1"."phone",
                    "a_1"."task",
                    "a_1"."valid_from",
                    "a_1"."valid_to",
                    "a_1"."open_run_id",
                    "a_1"."close_run_id"
                   FROM "added" "a_1"
                  WHERE (("a_1"."name" = "c"."name") AND ((("a_1"."open_run_id" IS NOT NULL) AND ("a_1"."open_run_id" = "c"."close_run_id")) OR (("a_1"."valid_from" >= ("c"."valid_to" - '00:03:00'::interval)) AND ("a_1"."valid_from" <= ("c"."valid_to" + '00:03:00'::interval)))))
                  ORDER BY (("a_1"."open_run_id" IS NOT NULL) AND ("a_1"."open_run_id" = "c"."close_run_id")) DESC, ("abs"(EXTRACT(epoch FROM ("a_1"."valid_from" - "c"."valid_to"))))
                 LIMIT 1) "a" ON (true))
        ), "moved" AS (
         SELECT COALESCE("p"."key_hash_after", "p"."key_hash_before") AS "key_hash",
            "p"."name",
            "p"."dep_after" AS "department",
            "p"."pos_after" AS "position",
            "p"."phone_after" AS "phone",
            "p"."task_after" AS "task",
            "p"."valid_from",
            "p"."valid_to",
            "p"."changed_fields",
            'moved'::"text" AS "event_type",
            "p"."run_after" AS "run_id",
            "p"."dep_before",
            "p"."pos_before",
            "p"."phone_before",
            "p"."same_run",
            "p"."pair_delta_sec"
           FROM "pairs" "p"
        ), "closed_only" AS (
         SELECT "c"."key_hash",
            "c"."name",
            "c"."department",
            "c"."position",
            "c"."phone",
            "c"."task",
            "c"."valid_from",
            "c"."valid_to",
            ARRAY[]::"text"[] AS "changed_fields",
            'closed'::"text" AS "event_type",
            "c"."close_run_id" AS "run_id",
            "c"."department" AS "dep_before",
            "c"."position" AS "pos_before",
            "c"."phone" AS "phone_before",
            false AS "same_run",
            NULL::integer AS "pair_delta_sec"
           FROM "closed" "c"
          WHERE (NOT (EXISTS ( SELECT 1
                   FROM "pairs" "p"
                  WHERE ("p"."key_hash_before" = "c"."key_hash"))))
        ), "added_only" AS (
         SELECT "a"."key_hash",
            "a"."name",
            "a"."department",
            "a"."position",
            "a"."phone",
            "a"."task",
            "a"."valid_from",
            "a"."valid_to",
            ARRAY['department'::"text", 'position'::"text", 'phone'::"text", 'task'::"text"] AS "changed_fields",
            'added'::"text" AS "event_type",
            "a"."open_run_id" AS "run_id",
            NULL::"text" AS "dep_before",
            NULL::"text" AS "pos_before",
            NULL::"text" AS "phone_before",
            false AS "same_run",
            NULL::integer AS "pair_delta_sec"
           FROM "added" "a"
          WHERE (NOT (EXISTS ( SELECT 1
                   FROM "pairs" "p"
                  WHERE ("p"."key_hash_after" = "a"."key_hash"))))
        )
 SELECT "moved"."key_hash",
    "moved"."name",
    "moved"."department",
    "moved"."position",
    "moved"."phone",
    "moved"."task",
    "moved"."valid_from",
    "moved"."valid_to",
    "moved"."changed_fields",
    "moved"."event_type",
    "moved"."run_id",
    "moved"."dep_before",
    "moved"."pos_before",
    "moved"."phone_before",
    "moved"."same_run",
    "moved"."pair_delta_sec"
   FROM "moved"
UNION ALL
 SELECT "closed_only"."key_hash",
    "closed_only"."name",
    "closed_only"."department",
    "closed_only"."position",
    "closed_only"."phone",
    "closed_only"."task",
    "closed_only"."valid_from",
    "closed_only"."valid_to",
    "closed_only"."changed_fields",
    "closed_only"."event_type",
    "closed_only"."run_id",
    "closed_only"."dep_before",
    "closed_only"."pos_before",
    "closed_only"."phone_before",
    "closed_only"."same_run",
    "closed_only"."pair_delta_sec"
   FROM "closed_only"
UNION ALL
 SELECT "added_only"."key_hash",
    "added_only"."name",
    "added_only"."department",
    "added_only"."position",
    "added_only"."phone",
    "added_only"."task",
    "added_only"."valid_from",
    "added_only"."valid_to",
    "added_only"."changed_fields",
    "added_only"."event_type",
    "added_only"."run_id",
    "added_only"."dep_before",
    "added_only"."pos_before",
    "added_only"."phone_before",
    "added_only"."same_run",
    "added_only"."pair_delta_sec"
   FROM "added_only";


ALTER TABLE "public"."motie_org_events_v3" OWNER TO "postgres";


CREATE OR REPLACE VIEW "public"."motie_org_hist_coalesced" WITH ("security_invoker"='on') AS
 WITH "base" AS (
         SELECT "motie_org_hist"."key_hash",
            "motie_org_hist"."name",
            TRIM(BOTH FROM "motie_org_hist"."department") AS "department",
            TRIM(BOTH FROM "motie_org_hist"."position") AS "position",
            COALESCE("regexp_replace"("motie_org_hist"."phone", '\s+'::"text", ''::"text", 'g'::"text"), ''::"text") AS "phone",
            COALESCE(TRIM(BOTH FROM "motie_org_hist"."task"), ''::"text") AS "task",
            "motie_org_hist"."valid_from",
            "motie_org_hist"."valid_to",
            "motie_org_hist"."is_current"
           FROM "public"."motie_org_hist"
        ), "ord" AS (
         SELECT "base"."key_hash",
            "base"."name",
            "base"."department",
            "base"."position",
            "base"."phone",
            "base"."task",
            "base"."valid_from",
            "base"."valid_to",
            "base"."is_current",
            "lag"("base"."department") OVER (PARTITION BY "base"."key_hash" ORDER BY "base"."valid_from") AS "prev_department",
            "lag"("base"."position") OVER (PARTITION BY "base"."key_hash" ORDER BY "base"."valid_from") AS "prev_position",
            "lag"("base"."phone") OVER (PARTITION BY "base"."key_hash" ORDER BY "base"."valid_from") AS "prev_phone",
            "lag"("base"."task") OVER (PARTITION BY "base"."key_hash" ORDER BY "base"."valid_from") AS "prev_task"
           FROM "base"
        ), "marked" AS (
         SELECT "ord"."key_hash",
            "ord"."name",
            "ord"."department",
            "ord"."position",
            "ord"."phone",
            "ord"."task",
            "ord"."valid_from",
            "ord"."valid_to",
            "ord"."is_current",
            "ord"."prev_department",
            "ord"."prev_position",
            "ord"."prev_phone",
            "ord"."prev_task",
                CASE
                    WHEN (("ord"."department" IS DISTINCT FROM "ord"."prev_department") OR ("ord"."position" IS DISTINCT FROM "ord"."prev_position") OR ("ord"."phone" IS DISTINCT FROM "ord"."prev_phone") OR ("ord"."task" IS DISTINCT FROM "ord"."prev_task")) THEN 1
                    ELSE 0
                END AS "boundary"
           FROM "ord"
        ), "grp" AS (
         SELECT "marked"."key_hash",
            "marked"."name",
            "marked"."department",
            "marked"."position",
            "marked"."phone",
            "marked"."task",
            "marked"."valid_from",
            "marked"."valid_to",
            "marked"."is_current",
            "marked"."prev_department",
            "marked"."prev_position",
            "marked"."prev_phone",
            "marked"."prev_task",
            "marked"."boundary",
            "sum"("marked"."boundary") OVER (PARTITION BY "marked"."key_hash" ORDER BY "marked"."valid_from" ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS "grp_id"
           FROM "marked"
        )
 SELECT "grp"."key_hash",
    "min"("grp"."name") AS "name",
    "min"("grp"."department") AS "department",
    "min"("grp"."position") AS "position",
    NULLIF("min"("grp"."phone"), ''::"text") AS "phone",
    NULLIF("min"("grp"."task"), ''::"text") AS "task",
    "min"("grp"."valid_from") AS "valid_from",
        CASE
            WHEN "bool_or"("grp"."is_current") THEN NULL::timestamp with time zone
            ELSE "max"("grp"."valid_to")
        END AS "valid_to",
    "bool_or"("grp"."is_current") AS "is_current"
   FROM "grp"
  GROUP BY "grp"."key_hash", "grp"."grp_id"
  ORDER BY ("min"("grp"."valid_from")) DESC;


ALTER TABLE "public"."motie_org_hist_coalesced" OWNER TO "postgres";


ALTER TABLE "public"."motie_org" ALTER COLUMN "id" ADD GENERATED BY DEFAULT AS IDENTITY (
    SEQUENCE NAME "public"."motie_org_id_seq"
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);



CREATE TABLE IF NOT EXISTS "public"."motie_org_raw" (
    "run_id" "uuid" NOT NULL,
    "page" "text" NOT NULL,
    "html" "text" NOT NULL,
    "fetched_at" timestamp with time zone DEFAULT "now"() NOT NULL
);


ALTER TABLE "public"."motie_org_raw" OWNER TO "postgres";


CREATE TABLE IF NOT EXISTS "public"."motie_org_units" (
    "scope" "text" NOT NULL,
    "code" "text" NOT NULL,
    "name" "text" NOT NULL,
    "parent_code" "text",
    "level" integer NOT NULL,
    "path" "text"[] DEFAULT '{}'::"text"[] NOT NULL,
    "suffix" "text",
    "expected_position" "text",
    "order_weight" integer DEFAULT 0 NOT NULL,
    "created_at" timestamp with time zone DEFAULT "now"(),
    "updated_at" timestamp with time zone DEFAULT "now"(),
    "idx" integer DEFAULT 0 NOT NULL,
    CONSTRAINT "motie_org_units_scope_check" CHECK (("scope" = ANY (ARRAY['headquarters'::"text", 'institution'::"text"])))
);


ALTER TABLE "public"."motie_org_units" OWNER TO "postgres";


CREATE TABLE IF NOT EXISTS "public"."motie_person" (
    "id" "uuid" NOT NULL,
    "canonical_name" "text" NOT NULL,
    "created_at" timestamp with time zone DEFAULT "now"() NOT NULL
);


ALTER TABLE "public"."motie_person" OWNER TO "postgres";


CREATE TABLE IF NOT EXISTS "public"."motie_person_keys" (
    "person_id" "uuid" NOT NULL,
    "key_hash" "text" NOT NULL
);


ALTER TABLE "public"."motie_person_keys" OWNER TO "postgres";


CREATE MATERIALIZED VIEW "public"."motie_phone_common" AS
 SELECT NULLIF("regexp_replace"(COALESCE("motie_org_cur"."phone", ''::"text"), '\D'::"text", ''::"text", 'g'::"text"), ''::"text") AS "phone_norm",
    "count"(*) AS "cnt"
   FROM "public"."motie_org_cur"
  GROUP BY NULLIF("regexp_replace"(COALESCE("motie_org_cur"."phone", ''::"text"), '\D'::"text", ''::"text", 'g'::"text"), ''::"text")
 HAVING ("count"(*) >= 5)
  WITH NO DATA;


ALTER TABLE "public"."motie_phone_common" OWNER TO "postgres";


CREATE TABLE IF NOT EXISTS "public"."motie_position_map" (
    "raw" "text" NOT NULL,
    "normalized" "text" NOT NULL,
    "rank_weight" integer NOT NULL
);


ALTER TABLE "public"."motie_position_map" OWNER TO "postgres";


CREATE OR REPLACE VIEW "public"."motie_replacement_candidates" WITH ("security_invoker"='on') AS
 WITH "ord" AS (
         SELECT "h"."key_hash",
            "h"."name",
            "h"."department",
            "h"."position",
            "h"."phone",
            "h"."task",
            "h"."row_hash",
            "h"."valid_from",
            "h"."valid_to",
            "h"."is_current",
            "lag"("h"."row_hash") OVER (PARTITION BY "h"."key_hash" ORDER BY "h"."valid_from") AS "prev_row_hash",
            "lag"("h"."department") OVER (PARTITION BY "h"."key_hash" ORDER BY "h"."valid_from") AS "prev_department"
           FROM "public"."motie_norm_hist" "h"
        )
 SELECT "ord"."key_hash",
    "ord"."name",
    "ord"."department",
    "ord"."position",
    "ord"."valid_from",
    "ord"."prev_row_hash",
    "ord"."row_hash"
   FROM "ord"
  WHERE (("ord"."prev_row_hash" IS NOT NULL) AND ("ord"."row_hash" IS DISTINCT FROM "ord"."prev_row_hash") AND ("ord"."department" = "ord"."prev_department"));


ALTER TABLE "public"."motie_replacement_candidates" OWNER TO "postgres";


CREATE OR REPLACE VIEW "public"."motie_transfer_candidates" WITH ("security_invoker"='on') AS
 WITH "closed" AS (
         SELECT "motie_norm_hist"."key_hash",
            "motie_norm_hist"."name",
            "motie_norm_hist"."department",
            "motie_norm_hist"."position",
            "motie_norm_hist"."phone",
            "motie_norm_hist"."task",
            "motie_norm_hist"."row_hash",
            "motie_norm_hist"."valid_from",
            "motie_norm_hist"."valid_to",
            "motie_norm_hist"."is_current"
           FROM "public"."motie_norm_hist"
          WHERE (("motie_norm_hist"."is_current" = false) AND ("motie_norm_hist"."valid_to" IS NOT NULL))
        ), "added" AS (
         SELECT "h"."key_hash",
            "h"."name",
            "h"."department",
            "h"."position",
            "h"."phone",
            "h"."task",
            "h"."row_hash",
            "h"."valid_from",
            "h"."valid_to",
            "h"."is_current",
            "h"."prev_row_hash"
           FROM ( SELECT "h_1"."key_hash",
                    "h_1"."name",
                    "h_1"."department",
                    "h_1"."position",
                    "h_1"."phone",
                    "h_1"."task",
                    "h_1"."row_hash",
                    "h_1"."valid_from",
                    "h_1"."valid_to",
                    "h_1"."is_current",
                    "lag"("h_1"."row_hash") OVER (PARTITION BY "h_1"."key_hash" ORDER BY "h_1"."valid_from") AS "prev_row_hash"
                   FROM "public"."motie_norm_hist" "h_1") "h"
          WHERE ("h"."prev_row_hash" IS NULL)
        )
 SELECT "c"."key_hash" AS "from_key_hash",
    "a"."key_hash" AS "to_key_hash",
    "c"."name",
    "c"."department" AS "from_department",
    "a"."department" AS "to_department",
    "c"."valid_to" AS "closed_at",
    "a"."valid_from" AS "added_at",
    ("a"."valid_from" - "c"."valid_to") AS "gap"
   FROM ("closed" "c"
     JOIN "added" "a" ON ((("a"."name" = "c"."name") AND ("a"."department" <> "c"."department") AND (("a"."valid_from" >= ("c"."valid_to" - '14 days'::interval)) AND ("a"."valid_from" <= ("c"."valid_to" + '14 days'::interval))))))
  ORDER BY "c"."valid_to" DESC;


ALTER TABLE "public"."motie_transfer_candidates" OWNER TO "postgres";


CREATE TABLE IF NOT EXISTS "public"."subscribers" (
    "chat_id" bigint NOT NULL,
    "first_name" "text",
    "subscribed_at" timestamp without time zone DEFAULT "now"()
);


ALTER TABLE "public"."subscribers" OWNER TO "postgres";


CREATE TABLE IF NOT EXISTS "public"."users" (
    "id" integer NOT NULL,
    "discord_id" character varying(50) NOT NULL,
    "email" character varying(255),
    "username" character varying(255),
    "avatar" character varying(255),
    "role" character varying(10) DEFAULT 'user'::character varying,
    "created_at" timestamp without time zone DEFAULT "now"(),
    "updated_at" timestamp without time zone DEFAULT "now"()
);


ALTER TABLE "public"."users" OWNER TO "postgres";


CREATE SEQUENCE IF NOT EXISTS "public"."users_id_seq"
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE "public"."users_id_seq" OWNER TO "postgres";


ALTER SEQUENCE "public"."users_id_seq" OWNED BY "public"."users"."id";



CREATE OR REPLACE VIEW "public"."v_motie_org_chart" WITH ("security_invoker"='on') AS
 SELECT "c"."id",
    "c"."parent_id",
    "c"."department",
    "c"."order_no",
    "c"."level",
    "o"."id" AS "leader_id",
    "o"."name" AS "leader_name",
    "o"."position" AS "leader_position",
    "o"."phone" AS "leader_phone"
   FROM ("public"."motie_org_chart" "c"
     LEFT JOIN "public"."motie_org" "o" ON (("c"."leader_id" = "o"."id")))
  ORDER BY "c"."level", "c"."order_no", "c"."department";


ALTER TABLE "public"."v_motie_org_chart" OWNER TO "postgres";


ALTER TABLE ONLY "public"."congress_member_hist" ALTER COLUMN "id" SET DEFAULT "nextval"('"public"."congress_member_hist_id_seq"'::"regclass");



ALTER TABLE ONLY "public"."congress_member_stg" ALTER COLUMN "id" SET DEFAULT "nextval"('"public"."congress_member_stg_id_seq"'::"regclass");



ALTER TABLE ONLY "public"."congress_raw" ALTER COLUMN "id" SET DEFAULT "nextval"('"public"."congress_raw_id_seq"'::"regclass");



ALTER TABLE ONLY "public"."kepco_org_snapshot" ALTER COLUMN "id" SET DEFAULT "nextval"('"public"."kepco_org_snapshot_id_seq"'::"regclass");



ALTER TABLE ONLY "public"."kepco_org_stg" ALTER COLUMN "id" SET DEFAULT "nextval"('"public"."kepco_org_stg_id_seq"'::"regclass");



ALTER TABLE ONLY "public"."kepco_raw" ALTER COLUMN "id" SET DEFAULT "nextval"('"public"."kepco_raw_id_seq"'::"regclass");



ALTER TABLE ONLY "public"."me_org_hist" ALTER COLUMN "id" SET DEFAULT "nextval"('"public"."me_org_hist_id_seq"'::"regclass");



ALTER TABLE ONLY "public"."mod_requests" ALTER COLUMN "id" SET DEFAULT "nextval"('"public"."mod_requests_id_seq"'::"regclass");



ALTER TABLE ONLY "public"."motie_org_event_overrides" ALTER COLUMN "id" SET DEFAULT "nextval"('"public"."motie_org_event_overrides_id_seq"'::"regclass");



ALTER TABLE ONLY "public"."users" ALTER COLUMN "id" SET DEFAULT "nextval"('"public"."users_id_seq"'::"regclass");



ALTER TABLE ONLY "govtrack"."person_identity_evidence"
    ADD CONSTRAINT "person_identity_evidence_pkey" PRIMARY KEY ("person_id", "key_type", "value");



ALTER TABLE ONLY "public"."admin_motie_change_status"
    ADD CONSTRAINT "admin_motie_change_status_pkey" PRIMARY KEY ("person_key", "latest_valid_from");



ALTER TABLE ONLY "public"."congress_member_hist"
    ADD CONSTRAINT "congress_member_hist_pkey" PRIMARY KEY ("id");



ALTER TABLE ONLY "public"."congress_member"
    ADD CONSTRAINT "congress_member_pkey" PRIMARY KEY ("id");



ALTER TABLE ONLY "public"."congress_member_snapshot"
    ADD CONSTRAINT "congress_member_snapshot_pkey" PRIMARY KEY ("run_id");



ALTER TABLE ONLY "public"."congress_member_stg"
    ADD CONSTRAINT "congress_member_stg_pkey" PRIMARY KEY ("id");



ALTER TABLE ONLY "public"."congress_raw"
    ADD CONSTRAINT "congress_raw_pkey" PRIMARY KEY ("id");



ALTER TABLE ONLY "public"."crawler_run"
    ADD CONSTRAINT "crawler_run_pkey" PRIMARY KEY ("id");



ALTER TABLE ONLY "public"."kepco_history"
    ADD CONSTRAINT "kepco_history_pkey" PRIMARY KEY ("id");



ALTER TABLE ONLY "public"."kepco_id"
    ADD CONSTRAINT "kepco_id_pkey1" PRIMARY KEY ("id");



ALTER TABLE ONLY "public"."kepco_id_test"
    ADD CONSTRAINT "kepco_id_test_pkey" PRIMARY KEY ("id");



ALTER TABLE ONLY "public"."kepco_id"
    ADD CONSTRAINT "kepco_id_unique_disc" UNIQUE ("department", "disclosure_no");



ALTER TABLE ONLY "public"."kepco_org"
    ADD CONSTRAINT "kepco_org_pkey" PRIMARY KEY ("id");



ALTER TABLE ONLY "public"."kepco_org_snapshot"
    ADD CONSTRAINT "kepco_org_snapshot_pkey" PRIMARY KEY ("id");



ALTER TABLE ONLY "public"."kepco_org_stg"
    ADD CONSTRAINT "kepco_org_stg_pkey" PRIMARY KEY ("id");



ALTER TABLE ONLY "public"."kepco_raw"
    ADD CONSTRAINT "kepco_raw_pkey" PRIMARY KEY ("id");



ALTER TABLE ONLY "public"."me_heads_target"
    ADD CONSTRAINT "me_heads_target_pkey" PRIMARY KEY ("ord");



ALTER TABLE ONLY "public"."me_id"
    ADD CONSTRAINT "me_id_pkey" PRIMARY KEY ("id");



ALTER TABLE ONLY "public"."me_org_hist"
    ADD CONSTRAINT "me_org_hist_pkey" PRIMARY KEY ("id");



ALTER TABLE ONLY "public"."me_org_snapshot"
    ADD CONSTRAINT "me_org_snapshot_pkey" PRIMARY KEY ("run_id");



ALTER TABLE ONLY "public"."mod_requests"
    ADD CONSTRAINT "mod_requests_pkey" PRIMARY KEY ("id");



ALTER TABLE ONLY "public"."moef_heads_target"
    ADD CONSTRAINT "moef_heads_target_pkey" PRIMARY KEY ("ord");



ALTER TABLE ONLY "public"."moef_id"
    ADD CONSTRAINT "moef_id_duplicate_pkey" PRIMARY KEY ("id");



ALTER TABLE ONLY "public"."moef_org"
    ADD CONSTRAINT "moef_org_pkey" PRIMARY KEY ("id");



ALTER TABLE ONLY "public"."moef_org_raw"
    ADD CONSTRAINT "moef_org_raw_pkey" PRIMARY KEY ("run_id", "kind", "page", "url");



ALTER TABLE ONLY "public"."moef_org_snapshot"
    ADD CONSTRAINT "moef_org_snapshot_pkey" PRIMARY KEY ("run_id");



ALTER TABLE ONLY "public"."moef_org_stg"
    ADD CONSTRAINT "moef_org_stg_pkey" PRIMARY KEY ("run_id", "key_hash");



ALTER TABLE ONLY "public"."motie_heads_target"
    ADD CONSTRAINT "motie_heads_target_pkey" PRIMARY KEY ("ord");



ALTER TABLE ONLY "public"."motie_id"
    ADD CONSTRAINT "motie_id_pkey" PRIMARY KEY ("id");



ALTER TABLE ONLY "public"."motie_org_chart"
    ADD CONSTRAINT "motie_org_chart_pkey" PRIMARY KEY ("id");



ALTER TABLE ONLY "public"."motie_org_event_overrides"
    ADD CONSTRAINT "motie_org_event_overrides_pkey" PRIMARY KEY ("id");



ALTER TABLE ONLY "public"."motie_org"
    ADD CONSTRAINT "motie_org_pkey" PRIMARY KEY ("id");



ALTER TABLE ONLY "public"."motie_org_raw"
    ADD CONSTRAINT "motie_org_raw_pkey" PRIMARY KEY ("run_id", "page");



ALTER TABLE ONLY "public"."motie_org_snapshot"
    ADD CONSTRAINT "motie_org_snapshot_pkey" PRIMARY KEY ("run_id");



ALTER TABLE ONLY "public"."motie_org_stg"
    ADD CONSTRAINT "motie_org_stg_pkey" PRIMARY KEY ("run_id", "key_hash");



ALTER TABLE ONLY "public"."motie_org_units"
    ADD CONSTRAINT "motie_org_units_pkey" PRIMARY KEY ("scope", "code");



ALTER TABLE ONLY "public"."motie_person_keys"
    ADD CONSTRAINT "motie_person_keys_key_hash_key" UNIQUE ("key_hash");



ALTER TABLE ONLY "public"."motie_person_keys"
    ADD CONSTRAINT "motie_person_keys_pkey" PRIMARY KEY ("person_id", "key_hash");



ALTER TABLE ONLY "public"."motie_person"
    ADD CONSTRAINT "motie_person_pkey" PRIMARY KEY ("id");



ALTER TABLE ONLY "public"."motie_position_map"
    ADD CONSTRAINT "motie_position_map_pkey" PRIMARY KEY ("raw");



ALTER TABLE ONLY "public"."subscribers"
    ADD CONSTRAINT "subscribers_pkey" PRIMARY KEY ("chat_id");



ALTER TABLE ONLY "public"."congress_member_stg"
    ADD CONSTRAINT "uq_congress_member_stg_run_key" UNIQUE ("run_id", "key_hash");



ALTER TABLE ONLY "public"."congress_member"
    ADD CONSTRAINT "uq_congress_member_triplet_cols" UNIQUE ("committee_name", "full_name", "role_name");



ALTER TABLE ONLY "public"."users"
    ADD CONSTRAINT "users_discord_id_key" UNIQUE ("discord_id");



ALTER TABLE ONLY "public"."users"
    ADD CONSTRAINT "users_pkey" PRIMARY KEY ("id");



CREATE INDEX "congress_member_events_vf_idx" ON "public"."congress_member_hist" USING "btree" ("valid_from");



CREATE INDEX "congress_member_hist_current_idx" ON "public"."congress_member_hist" USING "btree" ("is_current");



CREATE INDEX "congress_member_hist_district_idx" ON "public"."congress_member_hist" USING "btree" ("district");



CREATE INDEX "congress_member_hist_key_hash_idx" ON "public"."congress_member_hist" USING "btree" ("key_hash");



CREATE INDEX "congress_member_hist_kv_idx" ON "public"."congress_member_hist" USING "btree" ("key_hash", "valid_from");



CREATE INDEX "congress_member_hist_name_idx" ON "public"."congress_member_hist" USING "btree" ("full_name");



CREATE INDEX "congress_member_hist_party_idx" ON "public"."congress_member_hist" USING "btree" ("party");



CREATE INDEX "congress_member_stg_key_hash_idx" ON "public"."congress_member_stg" USING "btree" ("key_hash");



CREATE INDEX "congress_member_stg_run_id_idx" ON "public"."congress_member_stg" USING "btree" ("run_id");



CREATE INDEX "congress_raw_run_id_idx" ON "public"."congress_raw" USING "btree" ("run_id");



CREATE INDEX "idx_admin_motie_change_status_decision" ON "public"."admin_motie_change_status" USING "btree" ("decision");



CREATE INDEX "idx_congress_member_district" ON "public"."congress_member" USING "btree" ("district");



CREATE INDEX "idx_congress_member_party" ON "public"."congress_member" USING "btree" ("party");



CREATE INDEX "idx_kepco_history_current_true" ON "public"."kepco_history" USING "btree" ("department", "position", "name") WHERE ("current" = true);



CREATE INDEX "idx_kepco_history_department" ON "public"."kepco_history" USING "btree" ("department");



CREATE INDEX "idx_kepco_history_dept_name_start_nullend" ON "public"."kepco_history" USING "btree" ("department", "name", "start") WHERE ("actual_end" IS NULL);



CREATE INDEX "idx_kepco_history_name" ON "public"."kepco_history" USING "btree" ("name");



CREATE INDEX "idx_kepco_id_department" ON "public"."kepco_id" USING "btree" ("department");



CREATE INDEX "idx_moef_org_hist_current" ON "public"."moef_org_hist" USING "btree" ("is_current");



CREATE INDEX "idx_moef_org_hist_key" ON "public"."moef_org_hist" USING "btree" ("key_hash");



CREATE INDEX "idx_moef_org_hist_valid" ON "public"."moef_org_hist" USING "btree" ("key_hash", "valid_from", "valid_to");



CREATE INDEX "idx_moef_org_position" ON "public"."moef_org" USING "btree" ("position");



CREATE INDEX "idx_moef_org_snapshot_status" ON "public"."moef_org_snapshot" USING "btree" ("status", "taken_at" DESC);



CREATE INDEX "idx_moef_org_stg_key" ON "public"."moef_org_stg" USING "btree" ("key_hash");



CREATE INDEX "idx_moef_org_stg_run" ON "public"."moef_org_stg" USING "btree" ("run_id");



CREATE INDEX "idx_motie_org_hist_close_run" ON "public"."motie_org_hist" USING "btree" ("close_run_id");



CREATE INDEX "idx_motie_org_hist_current" ON "public"."motie_org_hist" USING "btree" ("is_current");



CREATE INDEX "idx_motie_org_hist_key" ON "public"."motie_org_hist" USING "btree" ("key_hash");



CREATE INDEX "idx_motie_org_hist_name" ON "public"."motie_org_hist" USING "btree" ("name");



CREATE INDEX "idx_motie_org_hist_open_run" ON "public"."motie_org_hist" USING "btree" ("open_run_id");



CREATE INDEX "idx_motie_org_hist_valid" ON "public"."motie_org_hist" USING "btree" ("key_hash", "valid_from", "valid_to");



CREATE INDEX "idx_motie_org_hist_valid_from" ON "public"."motie_org_hist" USING "btree" ("valid_from");



CREATE INDEX "idx_motie_org_hist_valid_to" ON "public"."motie_org_hist" USING "btree" ("valid_to");



CREATE INDEX "idx_motie_org_position" ON "public"."motie_org" USING "btree" ("position");



CREATE INDEX "idx_motie_org_raw_run" ON "public"."motie_org_raw" USING "btree" ("run_id");



CREATE INDEX "idx_motie_org_snapshot_status" ON "public"."motie_org_snapshot" USING "btree" ("status", "taken_at" DESC);



CREATE INDEX "idx_motie_org_stg_key" ON "public"."motie_org_stg" USING "btree" ("key_hash");



CREATE INDEX "idx_motie_org_stg_run" ON "public"."motie_org_stg" USING "btree" ("run_id");



CREATE INDEX "idx_motie_org_units_level" ON "public"."motie_org_units" USING "btree" ("level");



CREATE INDEX "idx_motie_org_units_parent" ON "public"."motie_org_units" USING "btree" ("parent_code");



CREATE INDEX "idx_motie_overrides_from_to" ON "public"."motie_org_event_overrides" USING "btree" ("from_key_hash", "to_key_hash");



CREATE INDEX "idx_motie_overrides_key" ON "public"."motie_org_event_overrides" USING "btree" ("key_hash");



CREATE INDEX "kepco_hist_dept_curr_idx" ON "public"."kepco_history" USING "btree" ("department", "current");



CREATE INDEX "kepco_hist_key_idx" ON "public"."kepco_history" USING "btree" ("department", "name", "start");



CREATE INDEX "kepco_org_snapshot_dept_idx" ON "public"."kepco_org_snapshot" USING "btree" ("department", "snapshot_at" DESC);



CREATE INDEX "kepco_org_snapshot_run_idx" ON "public"."kepco_org_snapshot" USING "btree" ("run_id");



CREATE INDEX "kepco_org_stg_keyhash_idx" ON "public"."kepco_org_stg" USING "btree" ("key_hash");



CREATE INDEX "kepco_org_stg_run_id_idx" ON "public"."kepco_org_stg" USING "btree" ("run_id");



CREATE INDEX "kepco_raw_disclosure_unique" ON "public"."kepco_raw" USING "btree" ("disclosure_no", "organ");



CREATE INDEX "kepco_raw_run_id_idx" ON "public"."kepco_raw" USING "btree" ("run_id");



CREATE INDEX "me_org_hist_current_idx" ON "public"."me_org_hist" USING "btree" ("is_current");



CREATE INDEX "me_org_hist_key_idx" ON "public"."me_org_hist" USING "btree" ("key_hash");



CREATE INDEX "me_org_raw_run_id_idx" ON "public"."me_org_raw" USING "btree" ("run_id");



CREATE INDEX "me_org_stg_key_idx" ON "public"."me_org_stg" USING "btree" ("key_hash");



CREATE INDEX "me_org_stg_run_id_idx" ON "public"."me_org_stg" USING "btree" ("run_id");



CREATE INDEX "motie_org_events_mv_department_idx" ON "public"."motie_org_events_mv" USING "btree" ("lower"("department"));



CREATE INDEX "motie_org_events_mv_event_type_idx" ON "public"."motie_org_events_mv" USING "btree" ("event_type", "valid_from" DESC);



CREATE INDEX "motie_org_events_mv_name_idx" ON "public"."motie_org_events_mv" USING "btree" ("lower"("name"));



CREATE INDEX "motie_org_events_mv_position_idx" ON "public"."motie_org_events_mv" USING "btree" ("lower"("position"));



CREATE INDEX "motie_org_events_mv_valid_from_idx" ON "public"."motie_org_events_mv" USING "btree" ("valid_from" DESC);



CREATE INDEX "motie_org_hist_is_current_idx" ON "public"."motie_org_hist" USING "btree" ("is_current");



CREATE INDEX "motie_org_hist_key_hash_idx" ON "public"."motie_org_hist" USING "btree" ("key_hash");



CREATE INDEX "motie_org_hist_name_phone_valid_from_idx" ON "public"."motie_org_hist" USING "btree" ("name", "phone", "valid_from" DESC);



CREATE INDEX "motie_org_hist_valid_from_idx" ON "public"."motie_org_hist" USING "btree" ("valid_from");



CREATE INDEX "motie_phone_common_phone_idx" ON "public"."motie_phone_common" USING "btree" ("phone_norm");



CREATE UNIQUE INDEX "uniq_kepco_term_nonvac" ON "public"."kepco_history" USING "btree" ("department", "name", "position", "start", "end", "task") WHERE (("name" IS NOT NULL) AND ("btrim"("name") <> ALL (ARRAY['공석'::"text", '(공석)'::"text", '결원'::"text", '-'::"text"])));



CREATE UNIQUE INDEX "uq_congress_hist_current" ON "public"."congress_member_hist" USING "btree" ("key_hash") WHERE (("is_current" IS TRUE) AND ("valid_to" IS NULL));



CREATE UNIQUE INDEX "uq_congress_member_triplet" ON "public"."congress_member" USING "btree" ("committee_name", "full_name", COALESCE("role_name", ''::"text"));



CREATE OR REPLACE TRIGGER "trg_motie_merge_after_run" AFTER INSERT OR UPDATE OF "status" ON "public"."crawler_run" FOR EACH ROW EXECUTE FUNCTION "public"."tg_motie_merge_after_run"();



CREATE OR REPLACE TRIGGER "trg_touch_last_seen" BEFORE UPDATE ON "public"."congress_member" FOR EACH ROW EXECUTE FUNCTION "public"."touch_last_seen"();



ALTER TABLE ONLY "public"."moef_org_raw"
    ADD CONSTRAINT "moef_org_raw_run_id_fkey" FOREIGN KEY ("run_id") REFERENCES "public"."crawler_run"("id") ON DELETE CASCADE;



ALTER TABLE ONLY "public"."moef_org_snapshot"
    ADD CONSTRAINT "moef_org_snapshot_run_id_fkey" FOREIGN KEY ("run_id") REFERENCES "public"."crawler_run"("id") ON DELETE CASCADE;



ALTER TABLE ONLY "public"."moef_org_stg"
    ADD CONSTRAINT "moef_org_stg_run_id_fkey" FOREIGN KEY ("run_id") REFERENCES "public"."crawler_run"("id") ON DELETE CASCADE;



ALTER TABLE ONLY "public"."motie_org_chart"
    ADD CONSTRAINT "motie_org_chart_leader_id_fkey" FOREIGN KEY ("leader_id") REFERENCES "public"."motie_org"("id");



ALTER TABLE ONLY "public"."motie_org_chart"
    ADD CONSTRAINT "motie_org_chart_parent_id_fkey" FOREIGN KEY ("parent_id") REFERENCES "public"."motie_org_chart"("id") ON DELETE CASCADE;



ALTER TABLE ONLY "public"."motie_org_raw"
    ADD CONSTRAINT "motie_org_raw_run_id_fkey" FOREIGN KEY ("run_id") REFERENCES "public"."crawler_run"("id") ON DELETE CASCADE;



ALTER TABLE ONLY "public"."motie_org_snapshot"
    ADD CONSTRAINT "motie_org_snapshot_run_id_fkey" FOREIGN KEY ("run_id") REFERENCES "public"."crawler_run"("id") ON DELETE CASCADE;



ALTER TABLE ONLY "public"."motie_org_stg"
    ADD CONSTRAINT "motie_org_stg_run_id_fkey" FOREIGN KEY ("run_id") REFERENCES "public"."crawler_run"("id") ON DELETE CASCADE;



ALTER TABLE ONLY "public"."motie_person_keys"
    ADD CONSTRAINT "motie_person_keys_person_id_fkey" FOREIGN KEY ("person_id") REFERENCES "public"."motie_person"("id") ON DELETE CASCADE;



CREATE POLICY "bot" ON "govtrack"."person_identity_evidence" USING (true);



ALTER TABLE "govtrack"."person_identity_evidence" ENABLE ROW LEVEL SECURITY;


CREATE POLICY "admin_motie_change_status_rw" ON "public"."admin_motie_change_status" TO "authenticated" USING (true) WITH CHECK (true);



CREATE POLICY "all" ON "public"."mod_requests" TO "anon" USING (true);



CREATE POLICY "all" ON "public"."users" USING (true);



CREATE POLICY "all_access" ON "public"."congress_member" USING (true) WITH CHECK (true);



CREATE POLICY "all_access" ON "public"."congress_member_hist" USING (true) WITH CHECK (true);



CREATE POLICY "all_access" ON "public"."congress_member_snapshot" USING (true) WITH CHECK (true);



CREATE POLICY "all_access" ON "public"."congress_member_stg" USING (true) WITH CHECK (true);



CREATE POLICY "all_access" ON "public"."congress_raw" USING (true) WITH CHECK (true);



CREATE POLICY "all_access" ON "public"."crawler_run" USING (true) WITH CHECK (true);



CREATE POLICY "all_access" ON "public"."kepco_history" USING (true) WITH CHECK (true);



CREATE POLICY "all_access" ON "public"."kepco_id" USING (true) WITH CHECK (true);



CREATE POLICY "all_access" ON "public"."kepco_id_test" USING (true) WITH CHECK (true);



CREATE POLICY "all_access" ON "public"."kepco_org" USING (true) WITH CHECK (true);



CREATE POLICY "all_access" ON "public"."kepco_org_snapshot" USING (true) WITH CHECK (true);



CREATE POLICY "all_access" ON "public"."kepco_org_stg" USING (true) WITH CHECK (true);



CREATE POLICY "all_access" ON "public"."kepco_raw" USING (true) WITH CHECK (true);



CREATE POLICY "all_access" ON "public"."mod_requests" USING (true) WITH CHECK (true);



CREATE POLICY "all_access" ON "public"."moef_id" USING (true) WITH CHECK (true);



CREATE POLICY "all_access" ON "public"."moef_org" USING (true) WITH CHECK (true);



CREATE POLICY "all_access" ON "public"."moef_org_hist" USING (true) WITH CHECK (true);



CREATE POLICY "all_access" ON "public"."moef_org_raw" USING (true) WITH CHECK (true);



CREATE POLICY "all_access" ON "public"."moef_org_snapshot" USING (true) WITH CHECK (true);



CREATE POLICY "all_access" ON "public"."moef_org_stg" USING (true) WITH CHECK (true);



CREATE POLICY "all_access" ON "public"."motie_id" USING (true) WITH CHECK (true);



CREATE POLICY "all_access" ON "public"."motie_org" USING (true) WITH CHECK (true);



CREATE POLICY "all_access" ON "public"."motie_org_chart" USING (true) WITH CHECK (true);



CREATE POLICY "all_access" ON "public"."motie_org_event_overrides" USING (true) WITH CHECK (true);



CREATE POLICY "all_access" ON "public"."motie_org_hist" USING (true) WITH CHECK (true);



CREATE POLICY "all_access" ON "public"."motie_org_raw" USING (true) WITH CHECK (true);



CREATE POLICY "all_access" ON "public"."motie_org_snapshot" USING (true) WITH CHECK (true);



CREATE POLICY "all_access" ON "public"."motie_org_stg" USING (true) WITH CHECK (true);



CREATE POLICY "all_access" ON "public"."motie_org_units" USING (true) WITH CHECK (true);



CREATE POLICY "all_access" ON "public"."motie_person" USING (true) WITH CHECK (true);



CREATE POLICY "all_access" ON "public"."motie_person_keys" USING (true) WITH CHECK (true);



CREATE POLICY "all_access" ON "public"."motie_position_map" USING (true) WITH CHECK (true);



CREATE POLICY "all_access" ON "public"."subscribers" USING (true) WITH CHECK (true);



CREATE POLICY "all_access" ON "public"."users" USING (true) WITH CHECK (true);



CREATE POLICY "bot" ON "public"."kepco_history" USING (true);



CREATE POLICY "bot" ON "public"."kepco_id" USING (true);



CREATE POLICY "bot" ON "public"."kepco_org" USING (true);



CREATE POLICY "bot" ON "public"."moef_org" USING (true);



CREATE POLICY "bot" ON "public"."motie_id" USING (true);



CREATE POLICY "bot" ON "public"."motie_org" USING (true);



CREATE POLICY "bot" ON "public"."subscribers" USING (true);



CREATE POLICY "chatbot" ON "public"."moef_id" USING (true);



ALTER TABLE "public"."congress_member" ENABLE ROW LEVEL SECURITY;


ALTER TABLE "public"."congress_member_hist" ENABLE ROW LEVEL SECURITY;


ALTER TABLE "public"."congress_member_snapshot" ENABLE ROW LEVEL SECURITY;


ALTER TABLE "public"."congress_member_stg" ENABLE ROW LEVEL SECURITY;


ALTER TABLE "public"."congress_raw" ENABLE ROW LEVEL SECURITY;


ALTER TABLE "public"."crawler_run" ENABLE ROW LEVEL SECURITY;


ALTER TABLE "public"."kepco_history" ENABLE ROW LEVEL SECURITY;


ALTER TABLE "public"."kepco_id" ENABLE ROW LEVEL SECURITY;


ALTER TABLE "public"."kepco_id_test" ENABLE ROW LEVEL SECURITY;


ALTER TABLE "public"."kepco_org" ENABLE ROW LEVEL SECURITY;


ALTER TABLE "public"."kepco_org_snapshot" ENABLE ROW LEVEL SECURITY;


ALTER TABLE "public"."kepco_org_stg" ENABLE ROW LEVEL SECURITY;


ALTER TABLE "public"."kepco_raw" ENABLE ROW LEVEL SECURITY;


ALTER TABLE "public"."me_id" ENABLE ROW LEVEL SECURITY;


ALTER TABLE "public"."mod_requests" ENABLE ROW LEVEL SECURITY;


ALTER TABLE "public"."moef_id" ENABLE ROW LEVEL SECURITY;


ALTER TABLE "public"."moef_org" ENABLE ROW LEVEL SECURITY;


ALTER TABLE "public"."moef_org_hist" ENABLE ROW LEVEL SECURITY;


ALTER TABLE "public"."moef_org_raw" ENABLE ROW LEVEL SECURITY;


ALTER TABLE "public"."moef_org_snapshot" ENABLE ROW LEVEL SECURITY;


ALTER TABLE "public"."moef_org_stg" ENABLE ROW LEVEL SECURITY;


ALTER TABLE "public"."motie_id" ENABLE ROW LEVEL SECURITY;


ALTER TABLE "public"."motie_org" ENABLE ROW LEVEL SECURITY;


ALTER TABLE "public"."motie_org_chart" ENABLE ROW LEVEL SECURITY;


ALTER TABLE "public"."motie_org_event_overrides" ENABLE ROW LEVEL SECURITY;


ALTER TABLE "public"."motie_org_hist" ENABLE ROW LEVEL SECURITY;


ALTER TABLE "public"."motie_org_raw" ENABLE ROW LEVEL SECURITY;


ALTER TABLE "public"."motie_org_snapshot" ENABLE ROW LEVEL SECURITY;


ALTER TABLE "public"."motie_org_stg" ENABLE ROW LEVEL SECURITY;


ALTER TABLE "public"."motie_org_units" ENABLE ROW LEVEL SECURITY;


ALTER TABLE "public"."motie_person" ENABLE ROW LEVEL SECURITY;


ALTER TABLE "public"."motie_person_keys" ENABLE ROW LEVEL SECURITY;


ALTER TABLE "public"."motie_position_map" ENABLE ROW LEVEL SECURITY;


ALTER TABLE "public"."subscribers" ENABLE ROW LEVEL SECURITY;


CREATE POLICY "true" ON "public"."congress_member" USING (true);



CREATE POLICY "true" ON "public"."congress_member_hist" USING (true);



CREATE POLICY "true" ON "public"."congress_member_snapshot" USING (true);



CREATE POLICY "true" ON "public"."congress_member_stg" USING (true);



CREATE POLICY "true" ON "public"."congress_raw" USING (true);



CREATE POLICY "true" ON "public"."crawler_run" USING (true);



CREATE POLICY "true" ON "public"."kepco_id_test" USING (true);



CREATE POLICY "true" ON "public"."kepco_org_snapshot" USING (true);



CREATE POLICY "true" ON "public"."kepco_org_stg" USING (true);



CREATE POLICY "true" ON "public"."kepco_raw" USING (true);



CREATE POLICY "true" ON "public"."me_id" USING (true);



CREATE POLICY "true" ON "public"."moef_org_hist" USING (true);



CREATE POLICY "true" ON "public"."moef_org_raw" USING (true);



CREATE POLICY "true" ON "public"."moef_org_snapshot" USING (true);



CREATE POLICY "true" ON "public"."moef_org_stg" USING (true);



CREATE POLICY "true" ON "public"."motie_org_chart" USING (true);



CREATE POLICY "true" ON "public"."motie_org_hist" USING (true);



CREATE POLICY "true" ON "public"."motie_org_raw" USING (true);



CREATE POLICY "true" ON "public"."motie_org_snapshot" USING (true);



CREATE POLICY "true" ON "public"."motie_org_stg" USING (true);



CREATE POLICY "true" ON "public"."motie_position_map" USING (true);



ALTER TABLE "public"."users" ENABLE ROW LEVEL SECURITY;




ALTER PUBLICATION "supabase_realtime" OWNER TO "postgres";





GRANT USAGE ON SCHEMA "public" TO "postgres";
GRANT USAGE ON SCHEMA "public" TO "anon";
GRANT USAGE ON SCHEMA "public" TO "authenticated";
GRANT USAGE ON SCHEMA "public" TO "service_role";























































































































































































GRANT ALL ON FUNCTION "public"."close_motie_keys"("keys" "text"[], "ts" timestamp with time zone) TO "anon";
GRANT ALL ON FUNCTION "public"."close_motie_keys"("keys" "text"[], "ts" timestamp with time zone) TO "authenticated";
GRANT ALL ON FUNCTION "public"."close_motie_keys"("keys" "text"[], "ts" timestamp with time zone) TO "service_role";



GRANT ALL ON FUNCTION "public"."finalize_motie_run"("p_run_id" "uuid") TO "anon";
GRANT ALL ON FUNCTION "public"."finalize_motie_run"("p_run_id" "uuid") TO "authenticated";
GRANT ALL ON FUNCTION "public"."finalize_motie_run"("p_run_id" "uuid") TO "service_role";



GRANT ALL ON FUNCTION "public"."handle_new_user"() TO "anon";
GRANT ALL ON FUNCTION "public"."handle_new_user"() TO "authenticated";
GRANT ALL ON FUNCTION "public"."handle_new_user"() TO "service_role";



GRANT ALL ON FUNCTION "public"."motie_merge_latest"() TO "anon";
GRANT ALL ON FUNCTION "public"."motie_merge_latest"() TO "authenticated";
GRANT ALL ON FUNCTION "public"."motie_merge_latest"() TO "service_role";



GRANT ALL ON FUNCTION "public"."motie_merge_run"("p_run_id" "uuid") TO "anon";
GRANT ALL ON FUNCTION "public"."motie_merge_run"("p_run_id" "uuid") TO "authenticated";
GRANT ALL ON FUNCTION "public"."motie_merge_run"("p_run_id" "uuid") TO "service_role";



GRANT ALL ON FUNCTION "public"."motie_org_events_list"("p_since" timestamp with time zone, "p_limit" integer, "p_offset" integer, "p_event" "text", "p_department" "text", "p_position" "text", "p_name" "text") TO "anon";
GRANT ALL ON FUNCTION "public"."motie_org_events_list"("p_since" timestamp with time zone, "p_limit" integer, "p_offset" integer, "p_event" "text", "p_department" "text", "p_position" "text", "p_name" "text") TO "authenticated";
GRANT ALL ON FUNCTION "public"."motie_org_events_list"("p_since" timestamp with time zone, "p_limit" integer, "p_offset" integer, "p_event" "text", "p_department" "text", "p_position" "text", "p_name" "text") TO "service_role";



GRANT ALL ON FUNCTION "public"."tg_motie_merge_after_run"() TO "anon";
GRANT ALL ON FUNCTION "public"."tg_motie_merge_after_run"() TO "authenticated";
GRANT ALL ON FUNCTION "public"."tg_motie_merge_after_run"() TO "service_role";



GRANT ALL ON FUNCTION "public"."touch_last_seen"() TO "anon";
GRANT ALL ON FUNCTION "public"."touch_last_seen"() TO "authenticated";
GRANT ALL ON FUNCTION "public"."touch_last_seen"() TO "service_role";



GRANT ALL ON FUNCTION "public"."touch_updated_at"() TO "anon";
GRANT ALL ON FUNCTION "public"."touch_updated_at"() TO "authenticated";
GRANT ALL ON FUNCTION "public"."touch_updated_at"() TO "service_role";
























GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE "public"."admin_motie_change_status" TO PUBLIC;
GRANT ALL ON TABLE "public"."admin_motie_change_status" TO "anon";
GRANT ALL ON TABLE "public"."admin_motie_change_status" TO "authenticated";
GRANT ALL ON TABLE "public"."admin_motie_change_status" TO "service_role";



GRANT ALL ON TABLE "public"."congress_member" TO "anon";
GRANT ALL ON TABLE "public"."congress_member" TO "authenticated";
GRANT ALL ON TABLE "public"."congress_member" TO "service_role";
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE "public"."congress_member" TO PUBLIC;



GRANT ALL ON TABLE "public"."congress_member_hist" TO "anon";
GRANT ALL ON TABLE "public"."congress_member_hist" TO "authenticated";
GRANT ALL ON TABLE "public"."congress_member_hist" TO "service_role";
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE "public"."congress_member_hist" TO PUBLIC;



GRANT ALL ON TABLE "public"."congress_member_cur" TO "anon";
GRANT ALL ON TABLE "public"."congress_member_cur" TO "authenticated";
GRANT ALL ON TABLE "public"."congress_member_cur" TO "service_role";
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE "public"."congress_member_cur" TO PUBLIC;



GRANT ALL ON TABLE "public"."congress_member_events" TO "anon";
GRANT ALL ON TABLE "public"."congress_member_events" TO "authenticated";
GRANT ALL ON TABLE "public"."congress_member_events" TO "service_role";
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE "public"."congress_member_events" TO PUBLIC;



GRANT ALL ON SEQUENCE "public"."congress_member_hist_id_seq" TO "anon";
GRANT ALL ON SEQUENCE "public"."congress_member_hist_id_seq" TO "authenticated";
GRANT ALL ON SEQUENCE "public"."congress_member_hist_id_seq" TO "service_role";
GRANT ALL ON SEQUENCE "public"."congress_member_hist_id_seq" TO PUBLIC;



GRANT ALL ON TABLE "public"."congress_member_person" TO "anon";
GRANT ALL ON TABLE "public"."congress_member_person" TO "authenticated";
GRANT ALL ON TABLE "public"."congress_member_person" TO "service_role";
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE "public"."congress_member_person" TO PUBLIC;



GRANT ALL ON TABLE "public"."congress_member_stg" TO "anon";
GRANT ALL ON TABLE "public"."congress_member_stg" TO "authenticated";
GRANT ALL ON TABLE "public"."congress_member_stg" TO "service_role";
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE "public"."congress_member_stg" TO PUBLIC;



GRANT ALL ON TABLE "public"."congress_member_run_diff" TO "anon";
GRANT ALL ON TABLE "public"."congress_member_run_diff" TO "authenticated";
GRANT ALL ON TABLE "public"."congress_member_run_diff" TO "service_role";
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE "public"."congress_member_run_diff" TO PUBLIC;



GRANT ALL ON TABLE "public"."congress_member_snapshot" TO "anon";
GRANT ALL ON TABLE "public"."congress_member_snapshot" TO "authenticated";
GRANT ALL ON TABLE "public"."congress_member_snapshot" TO "service_role";
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE "public"."congress_member_snapshot" TO PUBLIC;



GRANT ALL ON SEQUENCE "public"."congress_member_stg_id_seq" TO "anon";
GRANT ALL ON SEQUENCE "public"."congress_member_stg_id_seq" TO "authenticated";
GRANT ALL ON SEQUENCE "public"."congress_member_stg_id_seq" TO "service_role";
GRANT ALL ON SEQUENCE "public"."congress_member_stg_id_seq" TO PUBLIC;



GRANT ALL ON TABLE "public"."congress_raw" TO "anon";
GRANT ALL ON TABLE "public"."congress_raw" TO "authenticated";
GRANT ALL ON TABLE "public"."congress_raw" TO "service_role";
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE "public"."congress_raw" TO PUBLIC;



GRANT ALL ON SEQUENCE "public"."congress_raw_id_seq" TO "anon";
GRANT ALL ON SEQUENCE "public"."congress_raw_id_seq" TO "authenticated";
GRANT ALL ON SEQUENCE "public"."congress_raw_id_seq" TO "service_role";
GRANT ALL ON SEQUENCE "public"."congress_raw_id_seq" TO PUBLIC;



GRANT ALL ON TABLE "public"."crawler_run" TO "anon";
GRANT ALL ON TABLE "public"."crawler_run" TO "authenticated";
GRANT ALL ON TABLE "public"."crawler_run" TO "service_role";
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE "public"."crawler_run" TO PUBLIC;



GRANT ALL ON TABLE "public"."moef_org_hist" TO "anon";
GRANT ALL ON TABLE "public"."moef_org_hist" TO "authenticated";
GRANT ALL ON TABLE "public"."moef_org_hist" TO "service_role";
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE "public"."moef_org_hist" TO PUBLIC;



GRANT ALL ON TABLE "public"."motie_org_hist" TO "anon";
GRANT ALL ON TABLE "public"."motie_org_hist" TO "authenticated";
GRANT ALL ON TABLE "public"."motie_org_hist" TO "service_role";
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE "public"."motie_org_hist" TO PUBLIC;



GRANT ALL ON TABLE "public"."gov_staff" TO "anon";
GRANT ALL ON TABLE "public"."gov_staff" TO "authenticated";
GRANT ALL ON TABLE "public"."gov_staff" TO "service_role";
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE "public"."gov_staff" TO PUBLIC;



GRANT ALL ON TABLE "public"."moef_org_snapshot" TO "anon";
GRANT ALL ON TABLE "public"."moef_org_snapshot" TO "authenticated";
GRANT ALL ON TABLE "public"."moef_org_snapshot" TO "service_role";
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE "public"."moef_org_snapshot" TO PUBLIC;



GRANT ALL ON TABLE "public"."moef_org_stg" TO "anon";
GRANT ALL ON TABLE "public"."moef_org_stg" TO "authenticated";
GRANT ALL ON TABLE "public"."moef_org_stg" TO "service_role";
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE "public"."moef_org_stg" TO PUBLIC;



GRANT ALL ON TABLE "public"."moef_org_cur" TO "anon";
GRANT ALL ON TABLE "public"."moef_org_cur" TO "authenticated";
GRANT ALL ON TABLE "public"."moef_org_cur" TO "service_role";
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE "public"."moef_org_cur" TO PUBLIC;



GRANT ALL ON TABLE "public"."motie_org_snapshot" TO "anon";
GRANT ALL ON TABLE "public"."motie_org_snapshot" TO "authenticated";
GRANT ALL ON TABLE "public"."motie_org_snapshot" TO "service_role";
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE "public"."motie_org_snapshot" TO PUBLIC;



GRANT ALL ON TABLE "public"."motie_org_stg" TO "anon";
GRANT ALL ON TABLE "public"."motie_org_stg" TO "authenticated";
GRANT ALL ON TABLE "public"."motie_org_stg" TO "service_role";
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE "public"."motie_org_stg" TO PUBLIC;



GRANT ALL ON TABLE "public"."motie_org_cur_last_run_v" TO "anon";
GRANT ALL ON TABLE "public"."motie_org_cur_last_run_v" TO "authenticated";
GRANT ALL ON TABLE "public"."motie_org_cur_last_run_v" TO "service_role";
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE "public"."motie_org_cur_last_run_v" TO PUBLIC;



GRANT ALL ON TABLE "public"."gov_staff_cur" TO "anon";
GRANT ALL ON TABLE "public"."gov_staff_cur" TO "authenticated";
GRANT ALL ON TABLE "public"."gov_staff_cur" TO "service_role";
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE "public"."gov_staff_cur" TO PUBLIC;



GRANT ALL ON TABLE "public"."kepco_history" TO "anon";
GRANT ALL ON TABLE "public"."kepco_history" TO "authenticated";
GRANT ALL ON TABLE "public"."kepco_history" TO "service_role";
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE "public"."kepco_history" TO PUBLIC;



GRANT ALL ON TABLE "public"."kepco_id" TO "anon";
GRANT ALL ON TABLE "public"."kepco_id" TO "authenticated";
GRANT ALL ON TABLE "public"."kepco_id" TO "service_role";
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE "public"."kepco_id" TO PUBLIC;



GRANT ALL ON TABLE "public"."kepco_id_test" TO "anon";
GRANT ALL ON TABLE "public"."kepco_id_test" TO "authenticated";
GRANT ALL ON TABLE "public"."kepco_id_test" TO "service_role";
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE "public"."kepco_id_test" TO PUBLIC;



GRANT ALL ON TABLE "public"."kepco_org" TO "anon";
GRANT ALL ON TABLE "public"."kepco_org" TO "authenticated";
GRANT ALL ON TABLE "public"."kepco_org" TO "service_role";
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE "public"."kepco_org" TO PUBLIC;



GRANT ALL ON TABLE "public"."kepco_org_snapshot" TO "anon";
GRANT ALL ON TABLE "public"."kepco_org_snapshot" TO "authenticated";
GRANT ALL ON TABLE "public"."kepco_org_snapshot" TO "service_role";
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE "public"."kepco_org_snapshot" TO PUBLIC;



GRANT ALL ON TABLE "public"."kepco_org_cur" TO "anon";
GRANT ALL ON TABLE "public"."kepco_org_cur" TO "authenticated";
GRANT ALL ON TABLE "public"."kepco_org_cur" TO "service_role";
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE "public"."kepco_org_cur" TO PUBLIC;



GRANT ALL ON SEQUENCE "public"."kepco_org_id_seq" TO "anon";
GRANT ALL ON SEQUENCE "public"."kepco_org_id_seq" TO "authenticated";
GRANT ALL ON SEQUENCE "public"."kepco_org_id_seq" TO "service_role";
GRANT ALL ON SEQUENCE "public"."kepco_org_id_seq" TO PUBLIC;



GRANT ALL ON SEQUENCE "public"."kepco_org_snapshot_id_seq" TO "anon";
GRANT ALL ON SEQUENCE "public"."kepco_org_snapshot_id_seq" TO "authenticated";
GRANT ALL ON SEQUENCE "public"."kepco_org_snapshot_id_seq" TO "service_role";
GRANT ALL ON SEQUENCE "public"."kepco_org_snapshot_id_seq" TO PUBLIC;



GRANT ALL ON TABLE "public"."kepco_org_stg" TO "anon";
GRANT ALL ON TABLE "public"."kepco_org_stg" TO "authenticated";
GRANT ALL ON TABLE "public"."kepco_org_stg" TO "service_role";
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE "public"."kepco_org_stg" TO PUBLIC;



GRANT ALL ON SEQUENCE "public"."kepco_org_stg_id_seq" TO "anon";
GRANT ALL ON SEQUENCE "public"."kepco_org_stg_id_seq" TO "authenticated";
GRANT ALL ON SEQUENCE "public"."kepco_org_stg_id_seq" TO "service_role";
GRANT ALL ON SEQUENCE "public"."kepco_org_stg_id_seq" TO PUBLIC;



GRANT ALL ON TABLE "public"."kepco_raw" TO "anon";
GRANT ALL ON TABLE "public"."kepco_raw" TO "authenticated";
GRANT ALL ON TABLE "public"."kepco_raw" TO "service_role";
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE "public"."kepco_raw" TO PUBLIC;



GRANT ALL ON SEQUENCE "public"."kepco_raw_id_seq" TO "anon";
GRANT ALL ON SEQUENCE "public"."kepco_raw_id_seq" TO "authenticated";
GRANT ALL ON SEQUENCE "public"."kepco_raw_id_seq" TO "service_role";
GRANT ALL ON SEQUENCE "public"."kepco_raw_id_seq" TO PUBLIC;



GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE "public"."me_heads_target" TO PUBLIC;
GRANT ALL ON TABLE "public"."me_heads_target" TO "anon";
GRANT ALL ON TABLE "public"."me_heads_target" TO "authenticated";
GRANT ALL ON TABLE "public"."me_heads_target" TO "service_role";



GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE "public"."me_org_hist" TO PUBLIC;
GRANT ALL ON TABLE "public"."me_org_hist" TO "anon";
GRANT ALL ON TABLE "public"."me_org_hist" TO "authenticated";
GRANT ALL ON TABLE "public"."me_org_hist" TO "service_role";



GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE "public"."me_org_cur" TO PUBLIC;
GRANT ALL ON TABLE "public"."me_org_cur" TO "anon";
GRANT ALL ON TABLE "public"."me_org_cur" TO "authenticated";
GRANT ALL ON TABLE "public"."me_org_cur" TO "service_role";



GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE "public"."me_heads_cur" TO PUBLIC;
GRANT ALL ON TABLE "public"."me_heads_cur" TO "anon";
GRANT ALL ON TABLE "public"."me_heads_cur" TO "authenticated";
GRANT ALL ON TABLE "public"."me_heads_cur" TO "service_role";



GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE "public"."me_id" TO PUBLIC;
GRANT ALL ON TABLE "public"."me_id" TO "anon";
GRANT ALL ON TABLE "public"."me_id" TO "authenticated";
GRANT ALL ON TABLE "public"."me_id" TO "service_role";



GRANT ALL ON SEQUENCE "public"."me_org_hist_id_seq" TO PUBLIC;
GRANT ALL ON SEQUENCE "public"."me_org_hist_id_seq" TO "anon";
GRANT ALL ON SEQUENCE "public"."me_org_hist_id_seq" TO "authenticated";
GRANT ALL ON SEQUENCE "public"."me_org_hist_id_seq" TO "service_role";



GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE "public"."me_org_raw" TO PUBLIC;
GRANT ALL ON TABLE "public"."me_org_raw" TO "anon";
GRANT ALL ON TABLE "public"."me_org_raw" TO "authenticated";
GRANT ALL ON TABLE "public"."me_org_raw" TO "service_role";



GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE "public"."me_org_snapshot" TO PUBLIC;
GRANT ALL ON TABLE "public"."me_org_snapshot" TO "anon";
GRANT ALL ON TABLE "public"."me_org_snapshot" TO "authenticated";
GRANT ALL ON TABLE "public"."me_org_snapshot" TO "service_role";



GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE "public"."me_org_stg" TO PUBLIC;
GRANT ALL ON TABLE "public"."me_org_stg" TO "anon";
GRANT ALL ON TABLE "public"."me_org_stg" TO "authenticated";
GRANT ALL ON TABLE "public"."me_org_stg" TO "service_role";



GRANT ALL ON TABLE "public"."mod_requests" TO "anon";
GRANT ALL ON TABLE "public"."mod_requests" TO "authenticated";
GRANT ALL ON TABLE "public"."mod_requests" TO "service_role";
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE "public"."mod_requests" TO PUBLIC;



GRANT ALL ON SEQUENCE "public"."mod_requests_id_seq" TO "anon";
GRANT ALL ON SEQUENCE "public"."mod_requests_id_seq" TO "authenticated";
GRANT ALL ON SEQUENCE "public"."mod_requests_id_seq" TO "service_role";
GRANT ALL ON SEQUENCE "public"."mod_requests_id_seq" TO PUBLIC;



GRANT ALL ON TABLE "public"."moef_head" TO "anon";
GRANT ALL ON TABLE "public"."moef_head" TO "authenticated";
GRANT ALL ON TABLE "public"."moef_head" TO "service_role";
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE "public"."moef_head" TO PUBLIC;



GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE "public"."moef_heads_target" TO PUBLIC;
GRANT ALL ON TABLE "public"."moef_heads_target" TO "anon";
GRANT ALL ON TABLE "public"."moef_heads_target" TO "authenticated";
GRANT ALL ON TABLE "public"."moef_heads_target" TO "service_role";



GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE "public"."moef_heads_cur" TO PUBLIC;
GRANT ALL ON TABLE "public"."moef_heads_cur" TO "anon";
GRANT ALL ON TABLE "public"."moef_heads_cur" TO "authenticated";
GRANT ALL ON TABLE "public"."moef_heads_cur" TO "service_role";



GRANT ALL ON TABLE "public"."moef_id" TO "anon";
GRANT ALL ON TABLE "public"."moef_id" TO "authenticated";
GRANT ALL ON TABLE "public"."moef_id" TO "service_role";
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE "public"."moef_id" TO PUBLIC;



GRANT ALL ON TABLE "public"."moef_kps" TO "anon";
GRANT ALL ON TABLE "public"."moef_kps" TO "authenticated";
GRANT ALL ON TABLE "public"."moef_kps" TO "service_role";
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE "public"."moef_kps" TO PUBLIC;



GRANT ALL ON TABLE "public"."moef_org" TO "anon";
GRANT ALL ON TABLE "public"."moef_org" TO "authenticated";
GRANT ALL ON TABLE "public"."moef_org" TO "service_role";
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE "public"."moef_org" TO PUBLIC;



GRANT ALL ON SEQUENCE "public"."moef_org_id_seq" TO "anon";
GRANT ALL ON SEQUENCE "public"."moef_org_id_seq" TO "authenticated";
GRANT ALL ON SEQUENCE "public"."moef_org_id_seq" TO "service_role";
GRANT ALL ON SEQUENCE "public"."moef_org_id_seq" TO PUBLIC;



GRANT ALL ON TABLE "public"."moef_org_raw" TO "anon";
GRANT ALL ON TABLE "public"."moef_org_raw" TO "authenticated";
GRANT ALL ON TABLE "public"."moef_org_raw" TO "service_role";
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE "public"."moef_org_raw" TO PUBLIC;



GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE "public"."motie_heads_target" TO PUBLIC;
GRANT ALL ON TABLE "public"."motie_heads_target" TO "anon";
GRANT ALL ON TABLE "public"."motie_heads_target" TO "authenticated";
GRANT ALL ON TABLE "public"."motie_heads_target" TO "service_role";



GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE "public"."motie_org_cur" TO PUBLIC;
GRANT ALL ON TABLE "public"."motie_org_cur" TO "anon";
GRANT ALL ON TABLE "public"."motie_org_cur" TO "authenticated";
GRANT ALL ON TABLE "public"."motie_org_cur" TO "service_role";



GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE "public"."motie_heads_cur" TO PUBLIC;
GRANT ALL ON TABLE "public"."motie_heads_cur" TO "anon";
GRANT ALL ON TABLE "public"."motie_heads_cur" TO "authenticated";
GRANT ALL ON TABLE "public"."motie_heads_cur" TO "service_role";



GRANT ALL ON TABLE "public"."motie_id" TO "anon";
GRANT ALL ON TABLE "public"."motie_id" TO "authenticated";
GRANT ALL ON TABLE "public"."motie_id" TO "service_role";
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE "public"."motie_id" TO PUBLIC;



GRANT ALL ON TABLE "public"."motie_kps" TO "anon";
GRANT ALL ON TABLE "public"."motie_kps" TO "authenticated";
GRANT ALL ON TABLE "public"."motie_kps" TO "service_role";
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE "public"."motie_kps" TO PUBLIC;



GRANT ALL ON TABLE "public"."motie_norm_hist" TO "anon";
GRANT ALL ON TABLE "public"."motie_norm_hist" TO "authenticated";
GRANT ALL ON TABLE "public"."motie_norm_hist" TO "service_role";
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE "public"."motie_norm_hist" TO PUBLIC;



GRANT ALL ON TABLE "public"."motie_org" TO "anon";
GRANT ALL ON TABLE "public"."motie_org" TO "authenticated";
GRANT ALL ON TABLE "public"."motie_org" TO "service_role";
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE "public"."motie_org" TO PUBLIC;



GRANT ALL ON TABLE "public"."motie_org_chart" TO "anon";
GRANT ALL ON TABLE "public"."motie_org_chart" TO "authenticated";
GRANT ALL ON TABLE "public"."motie_org_chart" TO "service_role";
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE "public"."motie_org_chart" TO PUBLIC;



GRANT ALL ON SEQUENCE "public"."motie_org_chart_id_seq" TO "anon";
GRANT ALL ON SEQUENCE "public"."motie_org_chart_id_seq" TO "authenticated";
GRANT ALL ON SEQUENCE "public"."motie_org_chart_id_seq" TO "service_role";
GRANT ALL ON SEQUENCE "public"."motie_org_chart_id_seq" TO PUBLIC;



GRANT ALL ON TABLE "public"."motie_org_event_overrides" TO "anon";
GRANT ALL ON TABLE "public"."motie_org_event_overrides" TO "authenticated";
GRANT ALL ON TABLE "public"."motie_org_event_overrides" TO "service_role";
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE "public"."motie_org_event_overrides" TO PUBLIC;



GRANT ALL ON SEQUENCE "public"."motie_org_event_overrides_id_seq" TO "anon";
GRANT ALL ON SEQUENCE "public"."motie_org_event_overrides_id_seq" TO "authenticated";
GRANT ALL ON SEQUENCE "public"."motie_org_event_overrides_id_seq" TO "service_role";
GRANT ALL ON SEQUENCE "public"."motie_org_event_overrides_id_seq" TO PUBLIC;



GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE "public"."motie_org_events" TO PUBLIC;
GRANT ALL ON TABLE "public"."motie_org_events" TO "anon";
GRANT ALL ON TABLE "public"."motie_org_events" TO "authenticated";
GRANT ALL ON TABLE "public"."motie_org_events" TO "service_role";



GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE "public"."motie_org_events_mv" TO PUBLIC;
GRANT ALL ON TABLE "public"."motie_org_events_mv" TO "anon";
GRANT ALL ON TABLE "public"."motie_org_events_mv" TO "authenticated";
GRANT ALL ON TABLE "public"."motie_org_events_mv" TO "service_role";



GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE "public"."motie_org_events_v3" TO PUBLIC;
GRANT ALL ON TABLE "public"."motie_org_events_v3" TO "anon";
GRANT ALL ON TABLE "public"."motie_org_events_v3" TO "authenticated";
GRANT ALL ON TABLE "public"."motie_org_events_v3" TO "service_role";



GRANT ALL ON TABLE "public"."motie_org_hist_coalesced" TO "anon";
GRANT ALL ON TABLE "public"."motie_org_hist_coalesced" TO "authenticated";
GRANT ALL ON TABLE "public"."motie_org_hist_coalesced" TO "service_role";
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE "public"."motie_org_hist_coalesced" TO PUBLIC;



GRANT ALL ON SEQUENCE "public"."motie_org_id_seq" TO "anon";
GRANT ALL ON SEQUENCE "public"."motie_org_id_seq" TO "authenticated";
GRANT ALL ON SEQUENCE "public"."motie_org_id_seq" TO "service_role";
GRANT ALL ON SEQUENCE "public"."motie_org_id_seq" TO PUBLIC;



GRANT ALL ON TABLE "public"."motie_org_raw" TO "anon";
GRANT ALL ON TABLE "public"."motie_org_raw" TO "authenticated";
GRANT ALL ON TABLE "public"."motie_org_raw" TO "service_role";
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE "public"."motie_org_raw" TO PUBLIC;



GRANT ALL ON TABLE "public"."motie_org_units" TO "anon";
GRANT ALL ON TABLE "public"."motie_org_units" TO "authenticated";
GRANT ALL ON TABLE "public"."motie_org_units" TO "service_role";
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE "public"."motie_org_units" TO PUBLIC;



GRANT ALL ON TABLE "public"."motie_person" TO "anon";
GRANT ALL ON TABLE "public"."motie_person" TO "authenticated";
GRANT ALL ON TABLE "public"."motie_person" TO "service_role";
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE "public"."motie_person" TO PUBLIC;



GRANT ALL ON TABLE "public"."motie_person_keys" TO "anon";
GRANT ALL ON TABLE "public"."motie_person_keys" TO "authenticated";
GRANT ALL ON TABLE "public"."motie_person_keys" TO "service_role";
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE "public"."motie_person_keys" TO PUBLIC;



GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE "public"."motie_phone_common" TO PUBLIC;
GRANT ALL ON TABLE "public"."motie_phone_common" TO "anon";
GRANT ALL ON TABLE "public"."motie_phone_common" TO "authenticated";
GRANT ALL ON TABLE "public"."motie_phone_common" TO "service_role";



GRANT ALL ON TABLE "public"."motie_position_map" TO "anon";
GRANT ALL ON TABLE "public"."motie_position_map" TO "authenticated";
GRANT ALL ON TABLE "public"."motie_position_map" TO "service_role";
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE "public"."motie_position_map" TO PUBLIC;



GRANT ALL ON TABLE "public"."motie_replacement_candidates" TO "anon";
GRANT ALL ON TABLE "public"."motie_replacement_candidates" TO "authenticated";
GRANT ALL ON TABLE "public"."motie_replacement_candidates" TO "service_role";
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE "public"."motie_replacement_candidates" TO PUBLIC;



GRANT ALL ON TABLE "public"."motie_transfer_candidates" TO "anon";
GRANT ALL ON TABLE "public"."motie_transfer_candidates" TO "authenticated";
GRANT ALL ON TABLE "public"."motie_transfer_candidates" TO "service_role";
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE "public"."motie_transfer_candidates" TO PUBLIC;



GRANT ALL ON TABLE "public"."subscribers" TO "anon";
GRANT ALL ON TABLE "public"."subscribers" TO "authenticated";
GRANT ALL ON TABLE "public"."subscribers" TO "service_role";
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE "public"."subscribers" TO PUBLIC;



GRANT ALL ON TABLE "public"."users" TO "anon";
GRANT ALL ON TABLE "public"."users" TO "authenticated";
GRANT ALL ON TABLE "public"."users" TO "service_role";
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE "public"."users" TO PUBLIC;



GRANT ALL ON SEQUENCE "public"."users_id_seq" TO "anon";
GRANT ALL ON SEQUENCE "public"."users_id_seq" TO "authenticated";
GRANT ALL ON SEQUENCE "public"."users_id_seq" TO "service_role";
GRANT ALL ON SEQUENCE "public"."users_id_seq" TO PUBLIC;



GRANT ALL ON TABLE "public"."v_motie_org_chart" TO "anon";
GRANT ALL ON TABLE "public"."v_motie_org_chart" TO "authenticated";
GRANT ALL ON TABLE "public"."v_motie_org_chart" TO "service_role";
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE "public"."v_motie_org_chart" TO PUBLIC;



ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT ALL ON SEQUENCES  TO "postgres";
ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT ALL ON SEQUENCES  TO PUBLIC;
ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT ALL ON SEQUENCES  TO "anon";
ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT ALL ON SEQUENCES  TO "authenticated";
ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT ALL ON SEQUENCES  TO "service_role";






ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT ALL ON FUNCTIONS  TO "postgres";
ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT ALL ON FUNCTIONS  TO PUBLIC;
ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT ALL ON FUNCTIONS  TO "anon";
ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT ALL ON FUNCTIONS  TO "authenticated";
ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT ALL ON FUNCTIONS  TO "service_role";






ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT ALL ON TABLES  TO "postgres";
ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT SELECT,INSERT,DELETE,UPDATE ON TABLES  TO PUBLIC;
ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT ALL ON TABLES  TO "anon";
ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT ALL ON TABLES  TO "authenticated";
ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT ALL ON TABLES  TO "service_role";






























RESET ALL;
