-- Struktur DB Paguyubrand WebUMKM
-- Export: 2026-05-17 08:04:33
-- Total tabel: 82

-- ============================================================
-- Tabel: webumkm_achievements
-- ============================================================
CREATE TABLE webumkm_achievements (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    achievement_key text NOT NULL,
    label text NOT NULL,
    deskripsi text,
    icon_url text,
    syarat jsonb NOT NULL DEFAULT '{}'::jsonb,
    is_active boolean NOT NULL DEFAULT true,
    created_at timestamp with time zone NOT NULL DEFAULT now(),
    CONSTRAINT webumkm_achievements_pkey PRIMARY KEY (id),
    CONSTRAINT webumkm_achievements_achievement_key_key UNIQUE (achievement_key)
);

CREATE UNIQUE INDEX webumkm_achievements_achievement_key_key ON public.webumkm_achievements USING btree (achievement_key);

-- ============================================================
-- Tabel: webumkm_action_clicks
-- ============================================================
CREATE TABLE webumkm_action_clicks (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    store_id uuid NOT NULL,
    tipe_aksi text NOT NULL,
    ip_address text,
    created_at timestamp with time zone NOT NULL DEFAULT now(),
    CONSTRAINT webumkm_action_clicks_tipe_aksi_check CHECK ((tipe_aksi = ANY (ARRAY['whatsapp'::text, 'maps'::text, 'telepon'::text]))),
    CONSTRAINT webumkm_action_clicks_store_id_fkey FOREIGN KEY (store_id) REFERENCES webumkm_stores(id) ON DELETE CASCADE,
    CONSTRAINT webumkm_action_clicks_pkey PRIMARY KEY (id)
);

CREATE INDEX idx_action_clicks_store ON public.webumkm_action_clicks USING btree (store_id);

-- ============================================================
-- Tabel: webumkm_addon_invoices
-- ============================================================
CREATE TABLE webumkm_addon_invoices (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    member_id uuid NOT NULL,
    addon_key text NOT NULL,
    jumlah integer NOT NULL,
    status text NOT NULL DEFAULT 'unpaid'::text,
    midtrans_order_id text,
    jatuh_tempo timestamp with time zone,
    paid_at timestamp with time zone,
    created_at timestamp with time zone NOT NULL DEFAULT now(),
    CONSTRAINT webumkm_addon_invoices_addon_key_fkey FOREIGN KEY (addon_key) REFERENCES webumkm_addon_registry(addon_key),
    CONSTRAINT webumkm_addon_invoices_member_id_fkey FOREIGN KEY (member_id) REFERENCES webumkm_members(id),
    CONSTRAINT webumkm_addon_invoices_pkey PRIMARY KEY (id),
    CONSTRAINT webumkm_addon_invoices_midtrans_order_id_key UNIQUE (midtrans_order_id)
);

CREATE UNIQUE INDEX webumkm_addon_invoices_midtrans_order_id_key ON public.webumkm_addon_invoices USING btree (midtrans_order_id);

-- ============================================================
-- Tabel: webumkm_addon_registry
-- ============================================================
CREATE TABLE webumkm_addon_registry (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    addon_key text NOT NULL,
    addon_label text NOT NULL,
    deskripsi text,
    harga integer NOT NULL DEFAULT 0,
    harga_setup integer,
    tipe_harga text NOT NULL,
    is_active boolean NOT NULL DEFAULT true,
    urutan integer NOT NULL DEFAULT 0,
    created_at timestamp with time zone NOT NULL DEFAULT now(),
    paket_minimum text,
    business_type text,
    feature_key text,
    CONSTRAINT webumkm_addon_registry_tipe_harga_check CHECK ((tipe_harga = ANY (ARRAY['sekali_bayar'::text, 'tahunan'::text, 'bulanan'::text]))),
    CONSTRAINT webumkm_addon_registry_feature_key_fkey FOREIGN KEY (feature_key) REFERENCES webumkm_feature_registry(feature_key),
    CONSTRAINT webumkm_addon_registry_pkey PRIMARY KEY (id),
    CONSTRAINT webumkm_addon_registry_addon_key_key UNIQUE (addon_key)
);

CREATE UNIQUE INDEX webumkm_addon_registry_addon_key_key ON public.webumkm_addon_registry USING btree (addon_key);

-- ============================================================
-- Tabel: webumkm_admin_dormant_notifications
-- ============================================================
CREATE TABLE webumkm_admin_dormant_notifications (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    member_id uuid NOT NULL,
    dikirim_at timestamp with time zone NOT NULL DEFAULT now(),
    periode text NOT NULL,
    channel text NOT NULL,
    created_at timestamp with time zone NOT NULL DEFAULT now(),
    CONSTRAINT webumkm_admin_dormant_notifications_channel_check CHECK ((channel = ANY (ARRAY['platform'::text, 'whatsapp'::text]))),
    CONSTRAINT webumkm_admin_dormant_notifications_member_id_fkey FOREIGN KEY (member_id) REFERENCES webumkm_members(id),
    CONSTRAINT webumkm_admin_dormant_notifications_pkey PRIMARY KEY (id),
    CONSTRAINT webumkm_admin_dormant_notificatio_member_id_periode_channel_key UNIQUE (member_id, periode, channel)
);

CREATE UNIQUE INDEX webumkm_admin_dormant_notificatio_member_id_periode_channel_key ON public.webumkm_admin_dormant_notifications USING btree (member_id, periode, channel);
CREATE INDEX idx_dormant_notif_member ON public.webumkm_admin_dormant_notifications USING btree (member_id);
CREATE INDEX idx_dormant_notif_periode ON public.webumkm_admin_dormant_notifications USING btree (periode);

-- ============================================================
-- Tabel: webumkm_admin_users
-- ============================================================
CREATE TABLE webumkm_admin_users (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    nama text NOT NULL,
    email text NOT NULL,
    password_hash text NOT NULL,
    recovery_key_hash text,
    role text NOT NULL DEFAULT 'admin'::text,
    is_active boolean NOT NULL DEFAULT true,
    created_at timestamp with time zone NOT NULL DEFAULT now(),
    last_login_at timestamp with time zone,
    updated_at timestamp with time zone NOT NULL DEFAULT now(),
    CONSTRAINT webumkm_admin_users_role_check CHECK ((role = ANY (ARRAY['super_admin'::text, 'admin'::text]))),
    CONSTRAINT webumkm_admin_users_pkey PRIMARY KEY (id),
    CONSTRAINT webumkm_admin_users_email_key UNIQUE (email)
);

CREATE UNIQUE INDEX webumkm_admin_users_email_key ON public.webumkm_admin_users USING btree (email);

-- ============================================================
-- Tabel: webumkm_affiliator_withdrawals
-- ============================================================
CREATE TABLE webumkm_affiliator_withdrawals (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    affiliator_id uuid NOT NULL,
    jumlah bigint NOT NULL,
    status text NOT NULL DEFAULT 'pending'::text,
    nama_bank text NOT NULL,
    nomor_rekening text NOT NULL,
    nama_pemilik_rekening text NOT NULL,
    catatan_admin text,
    requested_at timestamp with time zone NOT NULL DEFAULT now(),
    processed_at timestamp with time zone,
    processed_by uuid,
    CONSTRAINT webumkm_affiliator_withdrawals_status_check CHECK ((status = ANY (ARRAY['pending'::text, 'processing'::text, 'completed'::text, 'rejected'::text]))),
    CONSTRAINT webumkm_affiliator_withdrawals_affiliator_id_fkey FOREIGN KEY (affiliator_id) REFERENCES webumkm_affiliators(id),
    CONSTRAINT webumkm_affiliator_withdrawals_processed_by_fkey FOREIGN KEY (processed_by) REFERENCES webumkm_admin_users(id),
    CONSTRAINT webumkm_affiliator_withdrawals_pkey PRIMARY KEY (id)
);

CREATE INDEX idx_affiliator_withdrawals_affiliator ON public.webumkm_affiliator_withdrawals USING btree (affiliator_id);

-- ============================================================
-- Tabel: webumkm_affiliators
-- ============================================================
CREATE TABLE webumkm_affiliators (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    member_id uuid,
    nama text NOT NULL,
    email text NOT NULL,
    nomor_hp text NOT NULL,
    referral_code text NOT NULL,
    status text NOT NULL DEFAULT 'active'::text,
    nama_bank text,
    nomor_rekening text,
    nama_pemilik_rekening text,
    total_komisi bigint NOT NULL DEFAULT 0,
    total_komisi_dibayar bigint NOT NULL DEFAULT 0,
    created_at timestamp with time zone NOT NULL DEFAULT now(),
    updated_at timestamp with time zone NOT NULL DEFAULT now(),
    holding_days integer NOT NULL DEFAULT 14,
    saldo bigint NOT NULL DEFAULT 0,
    saldo_pending bigint NOT NULL DEFAULT 0,
    CONSTRAINT webumkm_affiliators_status_check CHECK ((status = ANY (ARRAY['pending'::text, 'active'::text, 'suspended'::text]))),
    CONSTRAINT webumkm_affiliators_member_id_fkey FOREIGN KEY (member_id) REFERENCES webumkm_members(id),
    CONSTRAINT webumkm_affiliators_pkey PRIMARY KEY (id),
    CONSTRAINT webumkm_affiliators_email_key UNIQUE (email),
    CONSTRAINT webumkm_affiliators_referral_code_key UNIQUE (referral_code)
);

CREATE UNIQUE INDEX webumkm_affiliators_email_key ON public.webumkm_affiliators USING btree (email);
CREATE UNIQUE INDEX webumkm_affiliators_referral_code_key ON public.webumkm_affiliators USING btree (referral_code);

-- ============================================================
-- Tabel: webumkm_ai_prompt_templates
-- ============================================================
CREATE TABLE webumkm_ai_prompt_templates (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    key text NOT NULL,
    nama text NOT NULL,
    use_case text NOT NULL,
    ai_engine text NOT NULL DEFAULT 'gemini'::text,
    system_prompt text NOT NULL,
    user_prompt text NOT NULL,
    output_format text NOT NULL,
    is_active boolean NOT NULL DEFAULT true,
    created_at timestamp with time zone NOT NULL DEFAULT now(),
    updated_at timestamp with time zone NOT NULL DEFAULT now(),
    CONSTRAINT webumkm_ai_prompt_templates_ai_engine_check CHECK ((ai_engine = ANY (ARRAY['gemini'::text, 'claude'::text, 'auto'::text]))),
    CONSTRAINT webumkm_ai_prompt_templates_use_case_check CHECK ((use_case = ANY (ARRAY['monitoring'::text, 'laporan'::text, 'konten'::text, 'komunikasi'::text, 'self_healing'::text]))),
    CONSTRAINT webumkm_ai_prompt_templates_pkey PRIMARY KEY (id),
    CONSTRAINT webumkm_ai_prompt_templates_key_key UNIQUE (key)
);

CREATE UNIQUE INDEX webumkm_ai_prompt_templates_key_key ON public.webumkm_ai_prompt_templates USING btree (key);

-- ============================================================
-- Tabel: webumkm_announcements
-- ============================================================
CREATE TABLE webumkm_announcements (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    store_id uuid NOT NULL,
    judul text NOT NULL,
    isi text,
    tipe text NOT NULL DEFAULT 'info'::text,
    berlaku_dari timestamp with time zone,
    berlaku_hingga timestamp with time zone,
    is_active boolean NOT NULL DEFAULT true,
    urutan integer NOT NULL DEFAULT 1,
    created_at timestamp with time zone NOT NULL DEFAULT now(),
    updated_at timestamp with time zone NOT NULL DEFAULT now(),
    CONSTRAINT webumkm_announcements_tipe_check CHECK ((tipe = ANY (ARRAY['info'::text, 'warning'::text, 'promo'::text]))),
    CONSTRAINT webumkm_announcements_store_id_fkey FOREIGN KEY (store_id) REFERENCES webumkm_stores(id) ON DELETE CASCADE,
    CONSTRAINT webumkm_announcements_pkey PRIMARY KEY (id)
);

CREATE INDEX idx_announcements_store_id ON public.webumkm_announcements USING btree (store_id);

-- ============================================================
-- Tabel: webumkm_balance_holdings
-- ============================================================
CREATE TABLE webumkm_balance_holdings (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    member_id uuid NOT NULL,
    order_id uuid NOT NULL,
    jumlah integer NOT NULL,
    held_at timestamp with time zone NOT NULL DEFAULT now(),
    release_at timestamp with time zone NOT NULL,
    released_at timestamp with time zone,
    status text NOT NULL DEFAULT 'holding'::text,
    created_at timestamp with time zone NOT NULL DEFAULT now(),
    CONSTRAINT webumkm_balance_holdings_status_check CHECK ((status = ANY (ARRAY['holding'::text, 'released'::text]))),
    CONSTRAINT webumkm_balance_holdings_member_id_fkey FOREIGN KEY (member_id) REFERENCES webumkm_members(id),
    CONSTRAINT webumkm_balance_holdings_order_id_fkey FOREIGN KEY (order_id) REFERENCES webumkm_orders(id),
    CONSTRAINT webumkm_balance_holdings_pkey PRIMARY KEY (id)
);

CREATE INDEX idx_balance_holdings_release ON public.webumkm_balance_holdings USING btree (release_at, status) WHERE (status = 'holding'::text);

-- ============================================================
-- Tabel: webumkm_balance_transactions
-- ============================================================
CREATE TABLE webumkm_balance_transactions (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    member_id uuid NOT NULL,
    tipe text NOT NULL,
    jumlah integer NOT NULL,
    sumber text NOT NULL,
    referensi_id uuid,
    catatan text,
    created_at timestamp with time zone NOT NULL DEFAULT now(),
    held_until timestamp with time zone,
    released_at timestamp with time zone,
    CONSTRAINT webumkm_balance_transactions_sumber_check CHECK ((sumber = ANY (ARRAY['order_payment'::text, 'withdrawal'::text, 'adjustment'::text]))),
    CONSTRAINT webumkm_balance_transactions_tipe_check CHECK ((tipe = ANY (ARRAY['credit'::text, 'debit'::text]))),
    CONSTRAINT webumkm_balance_transactions_member_id_fkey FOREIGN KEY (member_id) REFERENCES webumkm_members(id),
    CONSTRAINT webumkm_balance_transactions_pkey PRIMARY KEY (id)
);

CREATE INDEX idx_balance_tx_member ON public.webumkm_balance_transactions USING btree (member_id);
CREATE INDEX idx_balance_tx_created ON public.webumkm_balance_transactions USING btree (created_at DESC);

-- ============================================================
-- Tabel: webumkm_business_types
-- ============================================================
CREATE TABLE webumkm_business_types (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    nama text NOT NULL,
    slug text NOT NULL,
    deskripsi text,
    is_active boolean NOT NULL DEFAULT true,
    created_at timestamp with time zone NOT NULL DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now(),
    onboarding_mode text NOT NULL DEFAULT 'standard'::text,
    template_folder text,
    CONSTRAINT webumkm_business_types_pkey PRIMARY KEY (id),
    CONSTRAINT webumkm_business_types_slug_key UNIQUE (slug)
);

CREATE UNIQUE INDEX webumkm_business_types_slug_key ON public.webumkm_business_types USING btree (slug);

-- ============================================================
-- Tabel: webumkm_chat_messages
-- ============================================================
CREATE TABLE webumkm_chat_messages (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    session_id uuid NOT NULL,
    pengirim text NOT NULL,
    isi text NOT NULL,
    is_read boolean NOT NULL DEFAULT false,
    created_at timestamp with time zone NOT NULL DEFAULT now(),
    CONSTRAINT webumkm_chat_messages_pengirim_check CHECK ((pengirim = ANY (ARRAY['pelanggan'::text, 'member'::text]))),
    CONSTRAINT webumkm_chat_messages_session_id_fkey FOREIGN KEY (session_id) REFERENCES webumkm_chat_sessions(id) ON DELETE CASCADE,
    CONSTRAINT webumkm_chat_messages_pkey PRIMARY KEY (id)
);

CREATE INDEX idx_chat_msg_session ON public.webumkm_chat_messages USING btree (session_id);
CREATE INDEX idx_chat_msg_created ON public.webumkm_chat_messages USING btree (created_at);

-- ============================================================
-- Tabel: webumkm_chat_rate_limits
-- ============================================================
CREATE TABLE webumkm_chat_rate_limits (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    ip_address text NOT NULL,
    store_id uuid NOT NULL,
    message_count integer NOT NULL DEFAULT 1,
    window_start timestamp with time zone NOT NULL DEFAULT now(),
    CONSTRAINT webumkm_chat_rate_limits_store_id_fkey FOREIGN KEY (store_id) REFERENCES webumkm_stores(id) ON DELETE CASCADE,
    CONSTRAINT webumkm_chat_rate_limits_pkey PRIMARY KEY (id),
    CONSTRAINT webumkm_chat_rate_limits_ip_address_store_id_key UNIQUE (ip_address, store_id)
);

CREATE UNIQUE INDEX webumkm_chat_rate_limits_ip_address_store_id_key ON public.webumkm_chat_rate_limits USING btree (ip_address, store_id);

-- ============================================================
-- Tabel: webumkm_chat_sessions
-- ============================================================
CREATE TABLE webumkm_chat_sessions (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    store_id uuid NOT NULL,
    member_id uuid NOT NULL,
    nama_pelanggan text NOT NULL,
    ip_pelanggan text,
    is_open boolean NOT NULL DEFAULT true,
    created_at timestamp with time zone NOT NULL DEFAULT now(),
    last_message_at timestamp with time zone NOT NULL DEFAULT now(),
    CONSTRAINT webumkm_chat_sessions_member_id_fkey FOREIGN KEY (member_id) REFERENCES webumkm_members(id),
    CONSTRAINT webumkm_chat_sessions_store_id_fkey FOREIGN KEY (store_id) REFERENCES webumkm_stores(id) ON DELETE CASCADE,
    CONSTRAINT webumkm_chat_sessions_pkey PRIMARY KEY (id)
);

CREATE INDEX idx_chat_sessions_store ON public.webumkm_chat_sessions USING btree (store_id);
CREATE INDEX idx_chat_sessions_member ON public.webumkm_chat_sessions USING btree (member_id);
CREATE INDEX idx_chat_sessions_open ON public.webumkm_chat_sessions USING btree (store_id, is_open);

-- ============================================================
-- Tabel: webumkm_crm_pipeline
-- ============================================================
CREATE TABLE webumkm_crm_pipeline (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    nama text NOT NULL,
    nomor_hp text,
    jenis_bisnis text,
    kota text,
    status text NOT NULL DEFAULT 'new'::text,
    catatan text,
    pic_admin_id uuid,
    created_at timestamp with time zone NOT NULL DEFAULT now(),
    last_contact_at timestamp with time zone,
    CONSTRAINT webumkm_crm_pipeline_status_check CHECK ((status = ANY (ARRAY['new'::text, 'contacted'::text, 'interested'::text, 'onboarding'::text, 'closed'::text]))),
    CONSTRAINT webumkm_crm_pipeline_pic_admin_id_fkey FOREIGN KEY (pic_admin_id) REFERENCES webumkm_admin_users(id),
    CONSTRAINT webumkm_crm_pipeline_pkey PRIMARY KEY (id)
);


-- ============================================================
-- Tabel: webumkm_cron_logs
-- ============================================================
CREATE TABLE webumkm_cron_logs (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    nama_cron text NOT NULL,
    status text NOT NULL,
    jumlah_data integer NOT NULL DEFAULT 0,
    pesan text,
    durasi_ms integer,
    started_at timestamp with time zone NOT NULL DEFAULT now(),
    finished_at timestamp with time zone,
    CONSTRAINT webumkm_cron_logs_status_check CHECK ((status = ANY (ARRAY['success'::text, 'failed'::text]))),
    CONSTRAINT webumkm_cron_logs_pkey PRIMARY KEY (id)
);

CREATE INDEX idx_cron_logs_nama ON public.webumkm_cron_logs USING btree (nama_cron);
CREATE INDEX idx_cron_logs_started ON public.webumkm_cron_logs USING btree (started_at DESC);

-- ============================================================
-- Tabel: webumkm_dashboard_header_items
-- ============================================================
CREATE TABLE webumkm_dashboard_header_items (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    key text NOT NULL,
    icon text NOT NULL,
    href text NOT NULL,
    badge_type text,
    has_dropdown boolean NOT NULL DEFAULT false,
    show_mobile boolean NOT NULL DEFAULT true,
    urutan integer NOT NULL DEFAULT 0,
    is_active boolean NOT NULL DEFAULT true,
    created_at timestamp with time zone NOT NULL DEFAULT now(),
    label text NOT NULL DEFAULT ''::text,
    CONSTRAINT webumkm_dashboard_header_items_pkey PRIMARY KEY (id),
    CONSTRAINT webumkm_dashboard_header_items_key_key UNIQUE (key)
);

CREATE UNIQUE INDEX webumkm_dashboard_header_items_key_key ON public.webumkm_dashboard_header_items USING btree (key);

-- ============================================================
-- Tabel: webumkm_dashboard_menus
-- ============================================================
CREATE TABLE webumkm_dashboard_menus (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    key text NOT NULL,
    file text NOT NULL,
    title text NOT NULL,
    icon text,
    group_label text,
    urutan integer NOT NULL DEFAULT 0,
    permission text,
    is_active boolean NOT NULL DEFAULT true,
    created_at timestamp with time zone NOT NULL DEFAULT now(),
    show_bottom_nav boolean NOT NULL DEFAULT false,
    bottom_nav_label text,
    sub_label text,
    show_in_sidebar boolean NOT NULL DEFAULT true,
    CONSTRAINT webumkm_dashboard_menus_pkey PRIMARY KEY (id),
    CONSTRAINT webumkm_dashboard_menus_key_key UNIQUE (key)
);

CREATE UNIQUE INDEX webumkm_dashboard_menus_key_key ON public.webumkm_dashboard_menus USING btree (key);

-- ============================================================
-- Tabel: webumkm_dashboard_themes
-- ============================================================
CREATE TABLE webumkm_dashboard_themes (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    nama text NOT NULL,
    deskripsi text,
    header_file text NOT NULL,
    sidebar_file text NOT NULL,
    footer_file text NOT NULL,
    is_active boolean NOT NULL DEFAULT false,
    created_at timestamp with time zone NOT NULL DEFAULT now(),
    CONSTRAINT webumkm_dashboard_themes_pkey PRIMARY KEY (id)
);


-- ============================================================
-- Tabel: webumkm_dashboard_widget_theme
-- ============================================================
CREATE TABLE webumkm_dashboard_widget_theme (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    template_key text NOT NULL,
    kunci text NOT NULL,
    nilai text,
    label text NOT NULL,
    grup text NOT NULL,
    tipe text NOT NULL DEFAULT 'color'::text,
    nilai_default text NOT NULL,
    deskripsi text,
    updated_at timestamp with time zone NOT NULL DEFAULT now(),
    updated_by uuid,
    CONSTRAINT webumkm_dashboard_widget_theme_tipe CHECK ((tipe = ANY (ARRAY['color'::text, 'shadow'::text, 'radius'::text, 'size'::text]))),
    CONSTRAINT webumkm_dashboard_widget_theme_pkey PRIMARY KEY (id),
    CONSTRAINT webumkm_dashboard_widget_theme_unique UNIQUE (template_key, kunci)
);

CREATE UNIQUE INDEX webumkm_dashboard_widget_theme_unique ON public.webumkm_dashboard_widget_theme USING btree (template_key, kunci);

-- ============================================================
-- Tabel: webumkm_dashboard_widgets
-- ============================================================
CREATE TABLE webumkm_dashboard_widgets (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    key text NOT NULL,
    file text NOT NULL,
    title text,
    urutan integer NOT NULL DEFAULT 0,
    col_span integer NOT NULL DEFAULT 6,
    industri text,
    is_active boolean NOT NULL DEFAULT true,
    created_at timestamp with time zone NOT NULL DEFAULT now(),
    page_key text NOT NULL DEFAULT 'beranda'::text,
    CONSTRAINT webumkm_dashboard_widgets_pkey PRIMARY KEY (id),
    CONSTRAINT webumkm_dashboard_widgets_key_key UNIQUE (key)
);

CREATE UNIQUE INDEX webumkm_dashboard_widgets_key_key ON public.webumkm_dashboard_widgets USING btree (key);
CREATE INDEX idx_dashboard_widgets_page ON public.webumkm_dashboard_widgets USING btree (page_key, is_active, urutan);

-- ============================================================
-- Tabel: webumkm_edu_contents
-- ============================================================
CREATE TABLE webumkm_edu_contents (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    judul text NOT NULL,
    tipe text NOT NULL,
    isi text,
    thumbnail_url text,
    kategori text,
    paket_minimum text NOT NULL DEFAULT 'semua'::text,
    is_published boolean NOT NULL DEFAULT false,
    dibuat_oleh uuid,
    created_at timestamp with time zone NOT NULL DEFAULT now(),
    updated_at timestamp with time zone NOT NULL DEFAULT now(),
    CONSTRAINT webumkm_edu_contents_paket_minimum_check CHECK ((paket_minimum = ANY (ARRAY['semua'::text, 'perintis'::text, 'tangguh'::text, 'sultan'::text]))),
    CONSTRAINT webumkm_edu_contents_tipe_check CHECK ((tipe = ANY (ARRAY['artikel'::text, 'video'::text]))),
    CONSTRAINT webumkm_edu_contents_dibuat_oleh_fkey FOREIGN KEY (dibuat_oleh) REFERENCES webumkm_admin_users(id),
    CONSTRAINT webumkm_edu_contents_pkey PRIMARY KEY (id)
);


-- ============================================================
-- Tabel: webumkm_edu_progress
-- ============================================================
CREATE TABLE webumkm_edu_progress (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    member_id uuid NOT NULL,
    content_id uuid NOT NULL,
    created_at timestamp with time zone NOT NULL DEFAULT now(),
    CONSTRAINT webumkm_edu_progress_content_id_fkey FOREIGN KEY (content_id) REFERENCES webumkm_edu_contents(id) ON DELETE CASCADE,
    CONSTRAINT webumkm_edu_progress_member_id_fkey FOREIGN KEY (member_id) REFERENCES webumkm_members(id) ON DELETE CASCADE,
    CONSTRAINT webumkm_edu_progress_pkey PRIMARY KEY (id),
    CONSTRAINT webumkm_edu_progress_member_id_content_id_key UNIQUE (member_id, content_id)
);

CREATE UNIQUE INDEX webumkm_edu_progress_member_id_content_id_key ON public.webumkm_edu_progress USING btree (member_id, content_id);
CREATE INDEX idx_edu_progress_member ON public.webumkm_edu_progress USING btree (member_id);
CREATE INDEX idx_edu_progress_content ON public.webumkm_edu_progress USING btree (content_id);

-- ============================================================
-- Tabel: webumkm_faqs
-- ============================================================
CREATE TABLE webumkm_faqs (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    store_id uuid NOT NULL,
    pertanyaan text NOT NULL,
    jawaban text NOT NULL,
    urutan integer NOT NULL DEFAULT 0,
    created_at timestamp with time zone NOT NULL DEFAULT now(),
    CONSTRAINT webumkm_faqs_store_id_fkey FOREIGN KEY (store_id) REFERENCES webumkm_stores(id) ON DELETE CASCADE,
    CONSTRAINT webumkm_faqs_pkey PRIMARY KEY (id)
);

CREATE INDEX idx_faqs_store ON public.webumkm_faqs USING btree (store_id);

-- ============================================================
-- Tabel: webumkm_feature_registry
-- ============================================================
CREATE TABLE webumkm_feature_registry (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    feature_key text NOT NULL,
    feature_label text NOT NULL,
    deskripsi text,
    kategori text NOT NULL,
    business_type text,
    paket_minimum text,
    is_active boolean NOT NULL DEFAULT true,
    urutan integer NOT NULL DEFAULT 0,
    created_at timestamp with time zone NOT NULL DEFAULT now(),
    is_core boolean NOT NULL DEFAULT false,
    section_key text,
    onboarding_mode text,
    CONSTRAINT webumkm_feature_registry_kategori_check CHECK ((kategori = ANY (ARRAY['core'::text, 'industri'::text, 'paket'::text]))),
    CONSTRAINT webumkm_feature_registry_paket_minimum_check CHECK ((paket_minimum = ANY (ARRAY['perintis'::text, 'tangguh'::text, 'sultan'::text]))),
    CONSTRAINT webumkm_feature_registry_pkey PRIMARY KEY (id),
    CONSTRAINT webumkm_feature_registry_feature_key_key UNIQUE (feature_key)
);

CREATE UNIQUE INDEX webumkm_feature_registry_feature_key_key ON public.webumkm_feature_registry USING btree (feature_key);

-- ============================================================
-- Tabel: webumkm_gallery_photos
-- ============================================================
CREATE TABLE webumkm_gallery_photos (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    store_id uuid NOT NULL,
    foto_url text NOT NULL,
    caption text,
    urutan integer NOT NULL DEFAULT 0,
    created_at timestamp with time zone NOT NULL DEFAULT now(),
    CONSTRAINT webumkm_gallery_photos_store_id_fkey FOREIGN KEY (store_id) REFERENCES webumkm_stores(id) ON DELETE CASCADE,
    CONSTRAINT webumkm_gallery_photos_pkey PRIMARY KEY (id)
);

CREATE INDEX idx_gallery_store ON public.webumkm_gallery_photos USING btree (store_id);

-- ============================================================
-- Tabel: webumkm_impersonation_logs
-- ============================================================
CREATE TABLE webumkm_impersonation_logs (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    admin_id uuid NOT NULL,
    member_id uuid NOT NULL,
    mulai_at timestamp with time zone NOT NULL DEFAULT now(),
    selesai_at timestamp with time zone,
    alasan text NOT NULL,
    ip_address text,
    created_at timestamp with time zone NOT NULL DEFAULT now(),
    CONSTRAINT webumkm_impersonation_logs_admin_id_fkey FOREIGN KEY (admin_id) REFERENCES webumkm_admin_users(id),
    CONSTRAINT webumkm_impersonation_logs_member_id_fkey FOREIGN KEY (member_id) REFERENCES webumkm_members(id),
    CONSTRAINT webumkm_impersonation_logs_pkey PRIMARY KEY (id)
);

CREATE INDEX idx_impersonation_admin ON public.webumkm_impersonation_logs USING btree (admin_id);
CREATE INDEX idx_impersonation_member ON public.webumkm_impersonation_logs USING btree (member_id);

-- ============================================================
-- Tabel: webumkm_invoice_sequence
-- ============================================================
CREATE TABLE webumkm_invoice_sequence (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    tahun integer NOT NULL,
    last_sequence integer NOT NULL DEFAULT 0,
    updated_at timestamp with time zone NOT NULL DEFAULT now(),
    CONSTRAINT webumkm_invoice_sequence_pkey PRIMARY KEY (id),
    CONSTRAINT webumkm_invoice_sequence_tahun_key UNIQUE (tahun)
);

CREATE UNIQUE INDEX webumkm_invoice_sequence_tahun_key ON public.webumkm_invoice_sequence USING btree (tahun);

-- ============================================================
-- Tabel: webumkm_invoices
-- ============================================================
CREATE TABLE webumkm_invoices (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    nomor_invoice text NOT NULL,
    member_id uuid NOT NULL,
    subscription_id uuid,
    paket text NOT NULL,
    siklus text NOT NULL,
    jumlah integer NOT NULL,
    status text NOT NULL DEFAULT 'unpaid'::text,
    midtrans_order_id text,
    jatuh_tempo timestamp with time zone,
    dibuat_oleh uuid,
    dibuat_otomatis boolean NOT NULL DEFAULT true,
    created_at timestamp with time zone NOT NULL DEFAULT now(),
    CONSTRAINT webumkm_invoices_status_check CHECK ((status = ANY (ARRAY['unpaid'::text, 'paid'::text, 'expired'::text, 'cancelled'::text]))),
    CONSTRAINT webumkm_invoices_paket_check CHECK ((paket = ANY (ARRAY['perintis'::text, 'tangguh'::text, 'sultan'::text]))),
    CONSTRAINT webumkm_invoices_subscription_id_fkey FOREIGN KEY (subscription_id) REFERENCES webumkm_subscriptions(id),
    CONSTRAINT webumkm_invoices_member_id_fkey FOREIGN KEY (member_id) REFERENCES webumkm_members(id),
    CONSTRAINT webumkm_invoices_dibuat_oleh_fkey FOREIGN KEY (dibuat_oleh) REFERENCES webumkm_admin_users(id),
    CONSTRAINT webumkm_invoices_pkey PRIMARY KEY (id),
    CONSTRAINT webumkm_invoices_midtrans_order_id_key UNIQUE (midtrans_order_id),
    CONSTRAINT webumkm_invoices_nomor_invoice_key UNIQUE (nomor_invoice)
);

CREATE UNIQUE INDEX webumkm_invoices_nomor_invoice_key ON public.webumkm_invoices USING btree (nomor_invoice);
CREATE UNIQUE INDEX webumkm_invoices_midtrans_order_id_key ON public.webumkm_invoices USING btree (midtrans_order_id);
CREATE INDEX idx_invoices_member ON public.webumkm_invoices USING btree (member_id);
CREATE INDEX idx_invoices_status ON public.webumkm_invoices USING btree (status);
CREATE INDEX idx_invoices_midtrans_order ON public.webumkm_invoices USING btree (midtrans_order_id);

-- ============================================================
-- Tabel: webumkm_komisi_config
-- ============================================================
CREATE TABLE webumkm_komisi_config (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    trigger text NOT NULL,
    tipe text NOT NULL DEFAULT 'flat'::text,
    nilai integer NOT NULL DEFAULT 0,
    is_active boolean NOT NULL DEFAULT true,
    updated_by uuid,
    updated_at timestamp with time zone NOT NULL DEFAULT now(),
    CONSTRAINT webumkm_komisi_config_tipe_check CHECK ((tipe = ANY (ARRAY['flat'::text, 'percent'::text]))),
    CONSTRAINT webumkm_komisi_config_updated_by_fkey FOREIGN KEY (updated_by) REFERENCES webumkm_admin_users(id),
    CONSTRAINT webumkm_komisi_config_pkey PRIMARY KEY (id),
    CONSTRAINT webumkm_komisi_config_trigger_key UNIQUE (trigger)
);

CREATE UNIQUE INDEX webumkm_komisi_config_trigger_key ON public.webumkm_komisi_config USING btree (trigger);

-- ============================================================
-- Tabel: webumkm_level_config
-- ============================================================
CREATE TABLE webumkm_level_config (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    level_key text NOT NULL,
    level_label text NOT NULL,
    urutan integer NOT NULL,
    revenue_minimum bigint NOT NULL DEFAULT 0,
    pesanan_minimum integer NOT NULL DEFAULT 0,
    diskon_fee_persen numeric NOT NULL DEFAULT 0,
    badge_url text,
    deskripsi_manfaat text,
    is_active boolean NOT NULL DEFAULT true,
    updated_by uuid,
    updated_at timestamp with time zone NOT NULL DEFAULT now(),
    CONSTRAINT webumkm_level_config_updated_by_fkey FOREIGN KEY (updated_by) REFERENCES webumkm_admin_users(id),
    CONSTRAINT webumkm_level_config_pkey PRIMARY KEY (id),
    CONSTRAINT webumkm_level_config_level_key_key UNIQUE (level_key)
);

CREATE UNIQUE INDEX webumkm_level_config_level_key_key ON public.webumkm_level_config USING btree (level_key);

-- ============================================================
-- Tabel: webumkm_login_attempts
-- ============================================================
CREATE TABLE webumkm_login_attempts (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    identifier text NOT NULL,
    user_type text NOT NULL,
    ip_address text,
    attempt_at timestamp with time zone NOT NULL DEFAULT now(),
    locked_until timestamp with time zone,
    CONSTRAINT webumkm_login_attempts_user_type_check CHECK ((user_type = ANY (ARRAY['member'::text, 'admin'::text, 'sub_user'::text]))),
    CONSTRAINT webumkm_login_attempts_pkey PRIMARY KEY (id)
);

CREATE INDEX idx_login_attempts_identifier ON public.webumkm_login_attempts USING btree (identifier, user_type);

-- ============================================================
-- Tabel: webumkm_member_achievements
-- ============================================================
CREATE TABLE webumkm_member_achievements (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    member_id uuid NOT NULL,
    achievement_key text NOT NULL,
    achieved_at timestamp with time zone NOT NULL DEFAULT now(),
    CONSTRAINT webumkm_member_achievements_achievement_key_fkey FOREIGN KEY (achievement_key) REFERENCES webumkm_achievements(achievement_key),
    CONSTRAINT webumkm_member_achievements_member_id_fkey FOREIGN KEY (member_id) REFERENCES webumkm_members(id),
    CONSTRAINT webumkm_member_achievements_pkey PRIMARY KEY (id),
    CONSTRAINT webumkm_member_achievements_member_id_achievement_key_key UNIQUE (member_id, achievement_key)
);

CREATE UNIQUE INDEX webumkm_member_achievements_member_id_achievement_key_key ON public.webumkm_member_achievements USING btree (member_id, achievement_key);

-- ============================================================
-- Tabel: webumkm_member_addons
-- ============================================================
CREATE TABLE webumkm_member_addons (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    member_id uuid NOT NULL,
    addon_key text NOT NULL,
    is_active boolean NOT NULL DEFAULT true,
    konfigurasi jsonb NOT NULL DEFAULT '{}'::jsonb,
    activated_at timestamp with time zone NOT NULL DEFAULT now(),
    activated_by uuid,
    expired_at timestamp with time zone,
    deactivated_at timestamp with time zone,
    catatan text,
    CONSTRAINT webumkm_member_addons_activated_by_fkey FOREIGN KEY (activated_by) REFERENCES webumkm_admin_users(id),
    CONSTRAINT webumkm_member_addons_addon_key_fkey FOREIGN KEY (addon_key) REFERENCES webumkm_addon_registry(addon_key),
    CONSTRAINT webumkm_member_addons_member_id_fkey FOREIGN KEY (member_id) REFERENCES webumkm_members(id) ON DELETE CASCADE,
    CONSTRAINT webumkm_member_addons_pkey PRIMARY KEY (id),
    CONSTRAINT webumkm_member_addons_member_id_addon_key_key UNIQUE (member_id, addon_key)
);

CREATE UNIQUE INDEX webumkm_member_addons_member_id_addon_key_key ON public.webumkm_member_addons USING btree (member_id, addon_key);
CREATE INDEX idx_member_addons_member ON public.webumkm_member_addons USING btree (member_id);

-- ============================================================
-- Tabel: webumkm_member_balances
-- ============================================================
CREATE TABLE webumkm_member_balances (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    member_id uuid NOT NULL,
    saldo integer NOT NULL DEFAULT 0,
    saldo_pending integer NOT NULL DEFAULT 0,
    updated_at timestamp with time zone NOT NULL DEFAULT now(),
    storage_terpakai_bytes bigint NOT NULL DEFAULT 0,
    storage_kuota_bytes bigint NOT NULL DEFAULT 524288000,
    saldo_tertahan integer NOT NULL DEFAULT 0,
    CONSTRAINT webumkm_member_balances_member_id_fkey FOREIGN KEY (member_id) REFERENCES webumkm_members(id) ON DELETE CASCADE,
    CONSTRAINT webumkm_member_balances_pkey PRIMARY KEY (id),
    CONSTRAINT webumkm_member_balances_member_id_key UNIQUE (member_id)
);

CREATE UNIQUE INDEX webumkm_member_balances_member_id_key ON public.webumkm_member_balances USING btree (member_id);

-- ============================================================
-- Tabel: webumkm_member_features
-- ============================================================
CREATE TABLE webumkm_member_features (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    member_id uuid NOT NULL,
    feature_key text NOT NULL,
    is_active boolean NOT NULL DEFAULT true,
    activated_at timestamp with time zone NOT NULL DEFAULT now(),
    activated_by uuid,
    deactivated_at timestamp with time zone,
    catatan text,
    CONSTRAINT webumkm_member_features_activated_by_fkey FOREIGN KEY (activated_by) REFERENCES webumkm_admin_users(id),
    CONSTRAINT webumkm_member_features_feature_key_fkey FOREIGN KEY (feature_key) REFERENCES webumkm_feature_registry(feature_key),
    CONSTRAINT webumkm_member_features_member_id_fkey FOREIGN KEY (member_id) REFERENCES webumkm_members(id) ON DELETE CASCADE,
    CONSTRAINT webumkm_member_features_pkey PRIMARY KEY (id),
    CONSTRAINT webumkm_member_features_member_id_feature_key_key UNIQUE (member_id, feature_key)
);

CREATE UNIQUE INDEX webumkm_member_features_member_id_feature_key_key ON public.webumkm_member_features USING btree (member_id, feature_key);
CREATE INDEX idx_member_features_member ON public.webumkm_member_features USING btree (member_id);

-- ============================================================
-- Tabel: webumkm_member_fee_config
-- ============================================================
CREATE TABLE webumkm_member_fee_config (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    member_id uuid NOT NULL,
    persentase_fee numeric,
    flat_fee integer,
    berlaku_sejak timestamp with time zone NOT NULL DEFAULT now(),
    catatan text NOT NULL,
    dibuat_oleh uuid,
    created_at timestamp with time zone NOT NULL DEFAULT now(),
    CONSTRAINT webumkm_member_fee_config_dibuat_oleh_fkey FOREIGN KEY (dibuat_oleh) REFERENCES webumkm_admin_users(id),
    CONSTRAINT webumkm_member_fee_config_member_id_fkey FOREIGN KEY (member_id) REFERENCES webumkm_members(id) ON DELETE CASCADE,
    CONSTRAINT webumkm_member_fee_config_pkey PRIMARY KEY (id),
    CONSTRAINT webumkm_member_fee_config_member_id_key UNIQUE (member_id)
);

CREATE UNIQUE INDEX webumkm_member_fee_config_member_id_key ON public.webumkm_member_fee_config USING btree (member_id);

-- ============================================================
-- Tabel: webumkm_member_levels
-- ============================================================
CREATE TABLE webumkm_member_levels (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    member_id uuid NOT NULL,
    level_key text NOT NULL,
    achieved_at timestamp with time zone NOT NULL DEFAULT now(),
    revenue_saat_naik bigint NOT NULL DEFAULT 0,
    pesanan_saat_naik integer NOT NULL DEFAULT 0,
    CONSTRAINT webumkm_member_levels_member_id_fkey FOREIGN KEY (member_id) REFERENCES webumkm_members(id),
    CONSTRAINT webumkm_member_levels_pkey PRIMARY KEY (id),
    CONSTRAINT webumkm_member_levels_member_id_level_key_key UNIQUE (member_id, level_key)
);

CREATE UNIQUE INDEX webumkm_member_levels_member_id_level_key_key ON public.webumkm_member_levels USING btree (member_id, level_key);

-- ============================================================
-- Tabel: webumkm_member_rekening
-- ============================================================
CREATE TABLE webumkm_member_rekening (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    member_id uuid NOT NULL,
    label text NOT NULL DEFAULT 'Rekening Utama'::text,
    nama_bank text NOT NULL,
    nomor_rekening text NOT NULL,
    nama_pemilik_rekening text NOT NULL,
    is_utama boolean NOT NULL DEFAULT false,
    created_at timestamp with time zone NOT NULL DEFAULT now(),
    updated_at timestamp with time zone NOT NULL DEFAULT now(),
    CONSTRAINT webumkm_member_rekening_max5 CHECK (true),
    CONSTRAINT webumkm_member_rekening_member_id_fkey FOREIGN KEY (member_id) REFERENCES webumkm_members(id) ON DELETE CASCADE,
    CONSTRAINT webumkm_member_rekening_pkey PRIMARY KEY (id)
);

CREATE INDEX idx_member_rekening_member_id ON public.webumkm_member_rekening USING btree (member_id);
CREATE UNIQUE INDEX idx_member_rekening_utama ON public.webumkm_member_rekening USING btree (member_id) WHERE (is_utama = true);

-- ============================================================
-- Tabel: webumkm_member_status_logs
-- ============================================================
CREATE TABLE webumkm_member_status_logs (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    member_id uuid NOT NULL,
    status_lama text NOT NULL,
    status_baru text NOT NULL,
    diubah_oleh uuid,
    alasan text NOT NULL,
    changed_at timestamp with time zone NOT NULL DEFAULT now(),
    CONSTRAINT webumkm_member_status_logs_diubah_oleh_fkey FOREIGN KEY (diubah_oleh) REFERENCES webumkm_admin_users(id),
    CONSTRAINT webumkm_member_status_logs_member_id_fkey FOREIGN KEY (member_id) REFERENCES webumkm_members(id),
    CONSTRAINT webumkm_member_status_logs_pkey PRIMARY KEY (id)
);

CREATE INDEX idx_status_logs_member ON public.webumkm_member_status_logs USING btree (member_id);

-- ============================================================
-- Tabel: webumkm_members
-- ============================================================
CREATE TABLE webumkm_members (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    nama_pemilik text NOT NULL,
    email text NOT NULL,
    nomor_hp text NOT NULL,
    nama_bisnis text NOT NULL,
    slug_subdomain text NOT NULL,
    business_type_id uuid,
    kota text,
    alamat_lengkap text,
    password_hash text NOT NULL,
    recovery_key_hash text NOT NULL,
    must_change_password boolean NOT NULL DEFAULT true,
    recovery_key_used_at timestamp with time zone,
    status text NOT NULL DEFAULT 'trial'::text,
    nomor_rekening text,
    nama_bank text,
    nama_pemilik_rekening text,
    wa_toko text,
    aktifkan_wa_button boolean NOT NULL DEFAULT false,
    aktifkan_live_chat boolean NOT NULL DEFAULT false,
    last_login_at timestamp with time zone,
    didaftarkan_oleh uuid,
    created_at timestamp with time zone NOT NULL DEFAULT now(),
    updated_at timestamp with time zone NOT NULL DEFAULT now(),
    referred_by uuid,
    onboarding_completed boolean NOT NULL DEFAULT false,
    onboarding_step integer NOT NULL DEFAULT 0,
    level_key text NOT NULL DEFAULT 'pedagang_baru'::text,
    foto_profil text,
    tipe_akun text NOT NULL DEFAULT 'member'::text,
    kode_referral_dipakai text,
    timezone text NOT NULL DEFAULT 'WIB'::text,
    CONSTRAINT webumkm_members_timezone_check CHECK ((timezone = ANY (ARRAY['WIB'::text, 'WITA'::text, 'WIT'::text]))),
    CONSTRAINT webumkm_members_tipe_akun_check CHECK ((tipe_akun = ANY (ARRAY['member'::text, 'affiliator'::text, 'keduanya'::text]))),
    CONSTRAINT webumkm_members_status_check CHECK ((status = ANY (ARRAY['trial'::text, 'active'::text, 'expired'::text, 'suspended'::text, 'banned'::text]))),
    CONSTRAINT webumkm_members_referred_by_fkey FOREIGN KEY (referred_by) REFERENCES webumkm_affiliators(id),
    CONSTRAINT webumkm_members_didaftarkan_oleh_fkey FOREIGN KEY (didaftarkan_oleh) REFERENCES webumkm_admin_users(id),
    CONSTRAINT webumkm_members_business_type_id_fkey FOREIGN KEY (business_type_id) REFERENCES webumkm_business_types(id),
    CONSTRAINT webumkm_members_pkey PRIMARY KEY (id),
    CONSTRAINT webumkm_members_slug_subdomain_key UNIQUE (slug_subdomain),
    CONSTRAINT webumkm_members_email_key UNIQUE (email)
);

CREATE UNIQUE INDEX webumkm_members_email_key ON public.webumkm_members USING btree (email);
CREATE UNIQUE INDEX webumkm_members_slug_subdomain_key ON public.webumkm_members USING btree (slug_subdomain);
CREATE INDEX idx_members_slug ON public.webumkm_members USING btree (slug_subdomain);
CREATE INDEX idx_members_status ON public.webumkm_members USING btree (status);
CREATE INDEX idx_members_email ON public.webumkm_members USING btree (email);
CREATE INDEX idx_members_last_login ON public.webumkm_members USING btree (last_login_at);
CREATE INDEX idx_members_business_type ON public.webumkm_members USING btree (business_type_id);

-- ============================================================
-- Tabel: webumkm_menu_industri
-- ============================================================
CREATE TABLE webumkm_menu_industri (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    menu_key text NOT NULL,
    industri_slug text NOT NULL,
    created_at timestamp with time zone NOT NULL DEFAULT now(),
    CONSTRAINT webumkm_menu_industri_industri_slug_fkey FOREIGN KEY (industri_slug) REFERENCES webumkm_business_types(slug) ON DELETE CASCADE,
    CONSTRAINT webumkm_menu_industri_menu_key_fkey FOREIGN KEY (menu_key) REFERENCES webumkm_dashboard_menus(key) ON DELETE CASCADE,
    CONSTRAINT webumkm_menu_industri_pkey PRIMARY KEY (id),
    CONSTRAINT webumkm_menu_industri_menu_key_industri_slug_key UNIQUE (menu_key, industri_slug)
);

CREATE UNIQUE INDEX webumkm_menu_industri_menu_key_industri_slug_key ON public.webumkm_menu_industri USING btree (menu_key, industri_slug);
CREATE INDEX idx_menu_industri_menu_key ON public.webumkm_menu_industri USING btree (menu_key);
CREATE INDEX idx_menu_industri_slug ON public.webumkm_menu_industri USING btree (industri_slug);

-- ============================================================
-- Tabel: webumkm_notifications
-- ============================================================
CREATE TABLE webumkm_notifications (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    penerima_id uuid NOT NULL,
    penerima_tipe text NOT NULL,
    tipe text NOT NULL,
    judul text NOT NULL,
    isi text NOT NULL,
    jalur text NOT NULL,
    is_read boolean NOT NULL DEFAULT false,
    wa_status text,
    referensi_id uuid,
    created_at timestamp with time zone NOT NULL DEFAULT now(),
    CONSTRAINT webumkm_notifications_jalur_check CHECK ((jalur = ANY (ARRAY['dashboard'::text, 'whatsapp'::text]))),
    CONSTRAINT webumkm_notifications_penerima_tipe_check CHECK ((penerima_tipe = ANY (ARRAY['member'::text, 'admin'::text]))),
    CONSTRAINT webumkm_notifications_wa_status_check CHECK ((wa_status = ANY (ARRAY['sent'::text, 'failed'::text]))),
    CONSTRAINT webumkm_notifications_pkey PRIMARY KEY (id)
);

CREATE INDEX idx_notif_penerima ON public.webumkm_notifications USING btree (penerima_id, penerima_tipe);
CREATE INDEX idx_notif_unread ON public.webumkm_notifications USING btree (penerima_id, is_read);
CREATE INDEX idx_notif_created ON public.webumkm_notifications USING btree (created_at DESC);

-- ============================================================
-- Tabel: webumkm_onboarding_progress
-- ============================================================
CREATE TABLE webumkm_onboarding_progress (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    member_id uuid NOT NULL,
    langkah_1 boolean NOT NULL DEFAULT false,
    langkah_2 boolean NOT NULL DEFAULT false,
    langkah_3 boolean NOT NULL DEFAULT false,
    langkah_4 boolean NOT NULL DEFAULT false,
    langkah_5 boolean NOT NULL DEFAULT false,
    langkah_6 boolean NOT NULL DEFAULT false,
    langkah_7 boolean NOT NULL DEFAULT false,
    langkah_8 boolean NOT NULL DEFAULT false,
    langkah_9 boolean NOT NULL DEFAULT false,
    langkah_10 boolean NOT NULL DEFAULT false,
    langkah_11 boolean NOT NULL DEFAULT false,
    langkah_12 boolean NOT NULL DEFAULT false,
    updated_at timestamp with time zone NOT NULL DEFAULT now(),
    CONSTRAINT webumkm_onboarding_progress_member_id_fkey FOREIGN KEY (member_id) REFERENCES webumkm_members(id) ON DELETE CASCADE,
    CONSTRAINT webumkm_onboarding_progress_pkey PRIMARY KEY (id),
    CONSTRAINT webumkm_onboarding_progress_member_id_key UNIQUE (member_id)
);

CREATE UNIQUE INDEX webumkm_onboarding_progress_member_id_key ON public.webumkm_onboarding_progress USING btree (member_id);

-- ============================================================
-- Tabel: webumkm_order_konfirmasi_logs
-- ============================================================
CREATE TABLE webumkm_order_konfirmasi_logs (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    order_id uuid NOT NULL,
    token text,
    via text NOT NULL,
    ip_address text,
    user_agent text,
    created_at timestamp with time zone NOT NULL DEFAULT now(),
    CONSTRAINT webumkm_order_konfirmasi_logs_order_id_fkey FOREIGN KEY (order_id) REFERENCES webumkm_orders(id),
    CONSTRAINT webumkm_order_konfirmasi_logs_pkey PRIMARY KEY (id)
);


-- ============================================================
-- Tabel: webumkm_order_payments
-- ============================================================
CREATE TABLE webumkm_order_payments (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    order_id uuid NOT NULL,
    midtrans_transaction_id text,
    midtrans_status text,
    metode_bayar text,
    jumlah integer NOT NULL,
    paid_at timestamp with time zone NOT NULL DEFAULT now(),
    dikonfirmasi_oleh uuid,
    CONSTRAINT webumkm_order_payments_dikonfirmasi_oleh_fkey FOREIGN KEY (dikonfirmasi_oleh) REFERENCES webumkm_members(id),
    CONSTRAINT webumkm_order_payments_order_id_fkey FOREIGN KEY (order_id) REFERENCES webumkm_orders(id),
    CONSTRAINT webumkm_order_payments_pkey PRIMARY KEY (id),
    CONSTRAINT webumkm_order_payments_midtrans_transaction_id_key UNIQUE (midtrans_transaction_id),
    CONSTRAINT webumkm_order_payments_order_id_key UNIQUE (order_id)
);

CREATE UNIQUE INDEX webumkm_order_payments_order_id_key ON public.webumkm_order_payments USING btree (order_id);
CREATE UNIQUE INDEX webumkm_order_payments_midtrans_transaction_id_key ON public.webumkm_order_payments USING btree (midtrans_transaction_id);
CREATE INDEX idx_order_payments_confirmed_by ON public.webumkm_order_payments USING btree (dikonfirmasi_oleh);

-- ============================================================
-- Tabel: webumkm_order_sequence
-- ============================================================
CREATE TABLE webumkm_order_sequence (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    tahun integer NOT NULL,
    last_sequence integer NOT NULL DEFAULT 0,
    updated_at timestamp with time zone NOT NULL DEFAULT now(),
    CONSTRAINT webumkm_order_sequence_pkey PRIMARY KEY (id),
    CONSTRAINT webumkm_order_sequence_tahun_key UNIQUE (tahun)
);

CREATE UNIQUE INDEX webumkm_order_sequence_tahun_key ON public.webumkm_order_sequence USING btree (tahun);

-- ============================================================
-- Tabel: webumkm_order_status_logs
-- ============================================================
CREATE TABLE webumkm_order_status_logs (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    order_id uuid NOT NULL,
    status_lama text,
    status_baru text NOT NULL,
    diubah_oleh_tipe text NOT NULL,
    diubah_oleh_id uuid,
    catatan text,
    changed_at timestamp with time zone NOT NULL DEFAULT now(),
    CONSTRAINT webumkm_order_status_logs_diubah_oleh_tipe_check CHECK ((diubah_oleh_tipe = ANY (ARRAY['system'::text, 'member'::text, 'sub_user'::text, 'admin'::text]))),
    CONSTRAINT webumkm_order_status_logs_order_id_fkey FOREIGN KEY (order_id) REFERENCES webumkm_orders(id) ON DELETE CASCADE,
    CONSTRAINT webumkm_order_status_logs_pkey PRIMARY KEY (id)
);

CREATE INDEX idx_order_status_logs_order ON public.webumkm_order_status_logs USING btree (order_id);

-- ============================================================
-- Tabel: webumkm_orders
-- ============================================================
CREATE TABLE webumkm_orders (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    nomor_pesanan text NOT NULL,
    store_id uuid NOT NULL,
    member_id uuid NOT NULL,
    nama_pelanggan text NOT NULL,
    nomor_hp_pelanggan text,
    catatan text,
    detail_pesanan jsonb,
    harga_asli integer NOT NULL DEFAULT 0,
    diskon_promo integer NOT NULL DEFAULT 0,
    promo_code_id uuid,
    harga_pesanan integer NOT NULL DEFAULT 0,
    biaya_admin_pgb integer NOT NULL DEFAULT 0,
    biaya_midtrans integer NOT NULL DEFAULT 0,
    total_dibayar integer NOT NULL DEFAULT 0,
    metode_bayar text NOT NULL,
    status text NOT NULL DEFAULT 'pending'::text,
    midtrans_order_id text,
    sumber_tampilan text DEFAULT 'form'::text,
    created_at timestamp with time zone NOT NULL DEFAULT now(),
    updated_at timestamp with time zone NOT NULL DEFAULT now(),
    paid_at timestamp with time zone,
    payment_type text,
    email_pelanggan text,
    konfirmasi_token text,
    konfirmasi_token_exp timestamp with time zone,
    dikonfirmasi_at timestamp with time zone,
    dikonfirmasi_via text,
    pengiriman_tipe text,
    pengiriman_kurir text,
    pengiriman_resi text,
    pengiriman_estimasi text,
    pengiriman_catatan text,
    pengiriman_diisi_at timestamp with time zone,
    CONSTRAINT webumkm_orders_sumber_tampilan_check CHECK ((sumber_tampilan = ANY (ARRAY['form'::text, 'product'::text]))),
    CONSTRAINT webumkm_orders_metode_bayar_check CHECK ((metode_bayar = ANY (ARRAY['online'::text, 'transfer_manual'::text, 'cod'::text]))),
    CONSTRAINT webumkm_orders_pengiriman_tipe_check CHECK (((pengiriman_tipe IS NULL) OR (pengiriman_tipe = ANY (ARRAY['kurir_eksternal'::text, 'antar_sendiri'::text, 'ambil_sendiri'::text])))),
    CONSTRAINT webumkm_orders_status_check CHECK ((status = ANY (ARRAY['pending'::text, 'paid'::text, 'processing'::text, 'completing'::text, 'completed'::text, 'cancelled'::text]))),
    CONSTRAINT webumkm_orders_dikonfirmasi_via_check CHECK (((dikonfirmasi_via IS NULL) OR (dikonfirmasi_via = ANY (ARRAY['pelanggan'::text, 'auto'::text])))),
    CONSTRAINT webumkm_orders_member_id_fkey FOREIGN KEY (member_id) REFERENCES webumkm_members(id),
    CONSTRAINT webumkm_orders_promo_code_id_fkey FOREIGN KEY (promo_code_id) REFERENCES webumkm_promo_codes(id),
    CONSTRAINT webumkm_orders_store_id_fkey FOREIGN KEY (store_id) REFERENCES webumkm_stores(id),
    CONSTRAINT webumkm_orders_pkey PRIMARY KEY (id),
    CONSTRAINT webumkm_orders_konfirmasi_token_key UNIQUE (konfirmasi_token),
    CONSTRAINT webumkm_orders_nomor_pesanan_key UNIQUE (nomor_pesanan),
    CONSTRAINT webumkm_orders_midtrans_order_id_key UNIQUE (midtrans_order_id)
);

CREATE UNIQUE INDEX webumkm_orders_nomor_pesanan_key ON public.webumkm_orders USING btree (nomor_pesanan);
CREATE UNIQUE INDEX webumkm_orders_midtrans_order_id_key ON public.webumkm_orders USING btree (midtrans_order_id);
CREATE INDEX idx_orders_store ON public.webumkm_orders USING btree (store_id);
CREATE INDEX idx_orders_member ON public.webumkm_orders USING btree (member_id);
CREATE INDEX idx_orders_status ON public.webumkm_orders USING btree (status);
CREATE INDEX idx_orders_created ON public.webumkm_orders USING btree (created_at DESC);
CREATE INDEX idx_orders_promo_code ON public.webumkm_orders USING btree (promo_code_id);
CREATE UNIQUE INDEX webumkm_orders_konfirmasi_token_key ON public.webumkm_orders USING btree (konfirmasi_token);

-- ============================================================
-- Tabel: webumkm_package_prices
-- ============================================================
CREATE TABLE webumkm_package_prices (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    paket text NOT NULL,
    siklus text NOT NULL,
    harga integer NOT NULL,
    setup_fee integer NOT NULL DEFAULT 0,
    is_active boolean NOT NULL DEFAULT true,
    updated_at timestamp with time zone NOT NULL DEFAULT now(),
    updated_by uuid,
    CONSTRAINT webumkm_package_prices_paket_check CHECK ((paket = ANY (ARRAY['perintis'::text, 'tangguh'::text, 'sultan'::text]))),
    CONSTRAINT webumkm_package_prices_siklus_check CHECK ((siklus = ANY (ARRAY['monthly'::text, 'yearly'::text]))),
    CONSTRAINT webumkm_package_prices_updated_by_fkey FOREIGN KEY (updated_by) REFERENCES webumkm_admin_users(id),
    CONSTRAINT webumkm_package_prices_pkey PRIMARY KEY (id),
    CONSTRAINT webumkm_package_prices_paket_siklus_key UNIQUE (paket, siklus)
);

CREATE UNIQUE INDEX webumkm_package_prices_paket_siklus_key ON public.webumkm_package_prices USING btree (paket, siklus);

-- ============================================================
-- Tabel: webumkm_page_views
-- ============================================================
CREATE TABLE webumkm_page_views (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    store_id uuid NOT NULL,
    ip_address text,
    user_agent text,
    referrer text,
    created_at timestamp with time zone NOT NULL DEFAULT now(),
    visitor_id text,
    CONSTRAINT webumkm_page_views_store_id_fkey FOREIGN KEY (store_id) REFERENCES webumkm_stores(id) ON DELETE CASCADE,
    CONSTRAINT webumkm_page_views_pkey PRIMARY KEY (id)
);

CREATE INDEX idx_page_views_store ON public.webumkm_page_views USING btree (store_id);
CREATE INDEX idx_page_views_created ON public.webumkm_page_views USING btree (created_at DESC);

-- ============================================================
-- Tabel: webumkm_paket_config
-- ============================================================
CREATE TABLE webumkm_paket_config (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    paket text NOT NULL,
    label text NOT NULL,
    fee_persen numeric NOT NULL DEFAULT 3.00,
    max_sub_user integer NOT NULL DEFAULT 0,
    max_section_opsional integer,
    storage_bytes bigint NOT NULL DEFAULT 104857600,
    penarikan_per_minggu integer,
    is_active boolean NOT NULL DEFAULT true,
    updated_by uuid,
    updated_at timestamp with time zone NOT NULL DEFAULT now(),
    max_layanan integer,
    max_galeri integer,
    max_testimoni integer,
    max_faq integer,
    max_promo_aktif integer,
    max_promo_per_bulan integer,
    max_pesanan_per_bulan integer,
    max_chat_session_aktif integer,
    max_tiket_per_bulan integer,
    retensi_data_hari integer,
    max_produk integer,
    CONSTRAINT webumkm_paket_config_updated_by_fkey FOREIGN KEY (updated_by) REFERENCES webumkm_admin_users(id),
    CONSTRAINT webumkm_paket_config_pkey PRIMARY KEY (id),
    CONSTRAINT webumkm_paket_config_paket_key UNIQUE (paket)
);

CREATE UNIQUE INDEX webumkm_paket_config_paket_key ON public.webumkm_paket_config USING btree (paket);

-- ============================================================
-- Tabel: webumkm_paket_fitur_config
-- ============================================================
CREATE TABLE webumkm_paket_fitur_config (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    business_type_id uuid,
    paket text NOT NULL,
    feature_key text NOT NULL,
    is_default boolean NOT NULL DEFAULT false,
    is_optional boolean NOT NULL DEFAULT true,
    max_optional integer,
    created_at timestamp with time zone NOT NULL DEFAULT now(),
    CONSTRAINT webumkm_paket_fitur_config_business_type_id_fkey FOREIGN KEY (business_type_id) REFERENCES webumkm_business_types(id),
    CONSTRAINT webumkm_paket_fitur_config_feature_key_fkey FOREIGN KEY (feature_key) REFERENCES webumkm_feature_registry(feature_key),
    CONSTRAINT webumkm_paket_fitur_config_pkey PRIMARY KEY (id)
);


-- ============================================================
-- Tabel: webumkm_payment_funnels
-- ============================================================
CREATE TABLE webumkm_payment_funnels (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    konteks text NOT NULL,
    referensi_id uuid NOT NULL,
    store_id uuid,
    member_id uuid NOT NULL,
    status text NOT NULL DEFAULT 'clicked_buy'::text,
    metode_dipilih text,
    ip_address text,
    clicked_at timestamp with time zone NOT NULL DEFAULT now(),
    paid_at timestamp with time zone,
    updated_at timestamp with time zone NOT NULL DEFAULT now(),
    CONSTRAINT webumkm_payment_funnels_konteks_check CHECK ((konteks = ANY (ARRAY['order'::text, 'subscription'::text]))),
    CONSTRAINT webumkm_payment_funnels_status_check CHECK ((status = ANY (ARRAY['clicked_buy'::text, 'opened_payment'::text, 'method_selected'::text, 'paid'::text, 'failed'::text, 'expired'::text, 'cancelled'::text]))),
    CONSTRAINT webumkm_payment_funnels_member_id_fkey FOREIGN KEY (member_id) REFERENCES webumkm_members(id),
    CONSTRAINT webumkm_payment_funnels_store_id_fkey FOREIGN KEY (store_id) REFERENCES webumkm_stores(id),
    CONSTRAINT webumkm_payment_funnels_pkey PRIMARY KEY (id)
);

CREATE INDEX idx_funnels_referensi ON public.webumkm_payment_funnels USING btree (referensi_id);
CREATE INDEX idx_funnels_member ON public.webumkm_payment_funnels USING btree (member_id);

-- ============================================================
-- Tabel: webumkm_payments
-- ============================================================
CREATE TABLE webumkm_payments (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    invoice_id uuid NOT NULL,
    midtrans_transaction_id text,
    midtrans_order_id text,
    midtrans_status text,
    metode_bayar text,
    jumlah integer NOT NULL,
    jumlah_invoice integer,
    raw_notification jsonb,
    paid_at timestamp with time zone NOT NULL DEFAULT now(),
    created_at timestamp with time zone NOT NULL DEFAULT now(),
    CONSTRAINT webumkm_payments_invoice_id_fkey FOREIGN KEY (invoice_id) REFERENCES webumkm_invoices(id),
    CONSTRAINT webumkm_payments_pkey PRIMARY KEY (id),
    CONSTRAINT webumkm_payments_midtrans_order_id_key UNIQUE (midtrans_order_id),
    CONSTRAINT webumkm_payments_midtrans_transaction_id_key UNIQUE (midtrans_transaction_id)
);

CREATE UNIQUE INDEX webumkm_payments_midtrans_transaction_id_key ON public.webumkm_payments USING btree (midtrans_transaction_id);
CREATE UNIQUE INDEX webumkm_payments_midtrans_order_id_key ON public.webumkm_payments USING btree (midtrans_order_id);
CREATE INDEX idx_payments_invoice ON public.webumkm_payments USING btree (invoice_id);

-- ============================================================
-- Tabel: webumkm_platform_fee_config
-- ============================================================
CREATE TABLE webumkm_platform_fee_config (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    fee_flat integer NOT NULL DEFAULT 1000,
    fee_percent numeric NOT NULL DEFAULT 2.00,
    updated_at timestamp with time zone NOT NULL DEFAULT now(),
    updated_by uuid,
    CONSTRAINT webumkm_platform_fee_config_updated_by_fkey FOREIGN KEY (updated_by) REFERENCES webumkm_admin_users(id),
    CONSTRAINT webumkm_platform_fee_config_pkey PRIMARY KEY (id)
);


-- ============================================================
-- Tabel: webumkm_platform_settings
-- ============================================================
CREATE TABLE webumkm_platform_settings (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    kunci text NOT NULL,
    nilai text,
    deskripsi text,
    updated_at timestamp with time zone NOT NULL DEFAULT now(),
    updated_by uuid,
    CONSTRAINT webumkm_platform_settings_updated_by_fkey FOREIGN KEY (updated_by) REFERENCES webumkm_admin_users(id),
    CONSTRAINT webumkm_platform_settings_pkey PRIMARY KEY (id),
    CONSTRAINT webumkm_platform_settings_kunci_key UNIQUE (kunci)
);

CREATE UNIQUE INDEX webumkm_platform_settings_kunci_key ON public.webumkm_platform_settings USING btree (kunci);

-- ============================================================
-- Tabel: webumkm_products
-- ============================================================
CREATE TABLE webumkm_products (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    store_id uuid NOT NULL,
    nama text NOT NULL,
    deskripsi text,
    harga integer NOT NULL,
    harga_coret integer,
    satuan text NOT NULL DEFAULT 'per pcs'::text,
    kategori text,
    foto_url text,
    is_active boolean NOT NULL DEFAULT true,
    urutan integer NOT NULL DEFAULT 0,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamp with time zone NOT NULL DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now(),
    CONSTRAINT webumkm_products_store_id_fkey FOREIGN KEY (store_id) REFERENCES webumkm_stores(id) ON DELETE CASCADE,
    CONSTRAINT webumkm_products_pkey PRIMARY KEY (id)
);

CREATE INDEX idx_products_store ON public.webumkm_products USING btree (store_id);
CREATE INDEX idx_products_aktif ON public.webumkm_products USING btree (store_id, is_active);

-- ============================================================
-- Tabel: webumkm_promo_codes
-- ============================================================
CREATE TABLE webumkm_promo_codes (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    store_id uuid NOT NULL,
    member_id uuid NOT NULL,
    kode text NOT NULL,
    tipe_diskon text NOT NULL,
    nilai_diskon integer NOT NULL,
    maks_diskon integer,
    berlaku_mulai date,
    berlaku_sampai date,
    kuota_total integer,
    kuota_terpakai integer NOT NULL DEFAULT 0,
    is_active boolean NOT NULL DEFAULT true,
    dinonaktifkan_oleh uuid,
    created_at timestamp with time zone NOT NULL DEFAULT now(),
    CONSTRAINT webumkm_promo_codes_tipe_diskon_check CHECK ((tipe_diskon = ANY (ARRAY['percent'::text, 'flat'::text]))),
    CONSTRAINT webumkm_promo_codes_dinonaktifkan_oleh_fkey FOREIGN KEY (dinonaktifkan_oleh) REFERENCES webumkm_admin_users(id),
    CONSTRAINT webumkm_promo_codes_member_id_fkey FOREIGN KEY (member_id) REFERENCES webumkm_members(id),
    CONSTRAINT webumkm_promo_codes_store_id_fkey FOREIGN KEY (store_id) REFERENCES webumkm_stores(id) ON DELETE CASCADE,
    CONSTRAINT webumkm_promo_codes_pkey PRIMARY KEY (id),
    CONSTRAINT webumkm_promo_codes_store_id_kode_key UNIQUE (store_id, kode)
);

CREATE UNIQUE INDEX webumkm_promo_codes_store_id_kode_key ON public.webumkm_promo_codes USING btree (store_id, kode);
CREATE INDEX idx_promo_store ON public.webumkm_promo_codes USING btree (store_id);

-- ============================================================
-- Tabel: webumkm_promo_usages
-- ============================================================
CREATE TABLE webumkm_promo_usages (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    promo_code_id uuid NOT NULL,
    order_id uuid NOT NULL,
    nomor_hp_pelanggan text NOT NULL,
    nilai_diskon_aktual integer NOT NULL,
    used_at timestamp with time zone NOT NULL DEFAULT now(),
    CONSTRAINT webumkm_promo_usages_order_id_fkey FOREIGN KEY (order_id) REFERENCES webumkm_orders(id),
    CONSTRAINT webumkm_promo_usages_promo_code_id_fkey FOREIGN KEY (promo_code_id) REFERENCES webumkm_promo_codes(id),
    CONSTRAINT webumkm_promo_usages_pkey PRIMARY KEY (id)
);

CREATE INDEX idx_promo_usage_code ON public.webumkm_promo_usages USING btree (promo_code_id);
CREATE INDEX idx_promo_usage_hp ON public.webumkm_promo_usages USING btree (promo_code_id, nomor_hp_pelanggan);

-- ============================================================
-- Tabel: webumkm_referral_conversions
-- ============================================================
CREATE TABLE webumkm_referral_conversions (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    affiliator_id uuid NOT NULL,
    member_id uuid NOT NULL,
    trigger text NOT NULL,
    referensi_id uuid,
    komisi_amount bigint NOT NULL DEFAULT 0,
    status text NOT NULL DEFAULT 'pending'::text,
    approved_by uuid,
    approved_at timestamp with time zone,
    paid_at timestamp with time zone,
    created_at timestamp with time zone NOT NULL DEFAULT now(),
    holding_until timestamp with time zone,
    catatan text,
    CONSTRAINT webumkm_referral_conversions_status_check CHECK ((status = ANY (ARRAY['pending'::text, 'approved'::text, 'paid'::text, 'rejected'::text]))),
    CONSTRAINT webumkm_referral_conversions_trigger_check CHECK ((trigger = ANY (ARRAY['register'::text, 'first_payment'::text, 'renewal'::text]))),
    CONSTRAINT webumkm_referral_conversions_affiliator_id_fkey FOREIGN KEY (affiliator_id) REFERENCES webumkm_affiliators(id),
    CONSTRAINT webumkm_referral_conversions_approved_by_fkey FOREIGN KEY (approved_by) REFERENCES webumkm_admin_users(id),
    CONSTRAINT webumkm_referral_conversions_member_id_fkey FOREIGN KEY (member_id) REFERENCES webumkm_members(id),
    CONSTRAINT webumkm_referral_conversions_pkey PRIMARY KEY (id)
);

CREATE INDEX idx_referral_conversions_affiliator ON public.webumkm_referral_conversions USING btree (affiliator_id);
CREATE INDEX idx_referral_conversions_status ON public.webumkm_referral_conversions USING btree (status);

-- ============================================================
-- Tabel: webumkm_section_features
-- ============================================================
CREATE TABLE webumkm_section_features (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    section_key text NOT NULL,
    feature_key text NOT NULL,
    feature_label text NOT NULL,
    business_type text,
    paket_minimum text,
    layout_options jsonb NOT NULL DEFAULT '["single"]'::jsonb,
    urutan integer NOT NULL DEFAULT 0,
    is_active boolean NOT NULL DEFAULT true,
    created_at timestamp with time zone NOT NULL DEFAULT now(),
    CONSTRAINT webumkm_section_features_pkey PRIMARY KEY (id),
    CONSTRAINT webumkm_section_features_section_key_feature_key_key UNIQUE (section_key, feature_key)
);

CREATE UNIQUE INDEX webumkm_section_features_section_key_feature_key_key ON public.webumkm_section_features USING btree (section_key, feature_key);

-- ============================================================
-- Tabel: webumkm_section_order
-- ============================================================
CREATE TABLE webumkm_section_order (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    section_key text NOT NULL,
    urutan integer NOT NULL,
    is_core boolean NOT NULL DEFAULT false,
    paket_minimum text,
    onboarding_mode text,
    created_at timestamp with time zone NOT NULL DEFAULT now(),
    CONSTRAINT webumkm_section_order_pkey PRIMARY KEY (id),
    CONSTRAINT webumkm_section_order_section_key_key UNIQUE (section_key)
);

CREATE UNIQUE INDEX webumkm_section_order_section_key_key ON public.webumkm_section_order USING btree (section_key);

-- ============================================================
-- Tabel: webumkm_section_templates
-- ============================================================
CREATE TABLE webumkm_section_templates (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    section_key text NOT NULL,
    template_id text NOT NULL,
    label text NOT NULL,
    deskripsi text,
    preview_url text,
    business_type text,
    is_active boolean NOT NULL DEFAULT true,
    created_at timestamp with time zone NOT NULL DEFAULT now(),
    konten_default jsonb NOT NULL DEFAULT '{}'::jsonb,
    CONSTRAINT webumkm_section_templates_pkey PRIMARY KEY (id),
    CONSTRAINT webumkm_section_templates_section_key_template_id_key UNIQUE (section_key, template_id)
);

CREATE UNIQUE INDEX webumkm_section_templates_section_key_template_id_key ON public.webumkm_section_templates USING btree (section_key, template_id);

-- ============================================================
-- Tabel: webumkm_service_categories
-- ============================================================
CREATE TABLE webumkm_service_categories (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    store_id uuid NOT NULL,
    nama text NOT NULL,
    deskripsi text,
    warna text NOT NULL DEFAULT '#00453F'::text,
    urutan integer NOT NULL DEFAULT 0,
    is_active boolean NOT NULL DEFAULT true,
    created_at timestamp with time zone NOT NULL DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now(),
    CONSTRAINT webumkm_service_categories_store_id_fkey FOREIGN KEY (store_id) REFERENCES webumkm_stores(id) ON DELETE CASCADE,
    CONSTRAINT webumkm_service_categories_pkey PRIMARY KEY (id)
);


-- ============================================================
-- Tabel: webumkm_services
-- ============================================================
CREATE TABLE webumkm_services (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    store_id uuid NOT NULL,
    nama text NOT NULL,
    deskripsi text,
    harga integer NOT NULL DEFAULT 0,
    satuan text NOT NULL DEFAULT 'per item'::text,
    foto_url text,
    is_active boolean NOT NULL DEFAULT true,
    urutan integer NOT NULL DEFAULT 0,
    created_at timestamp with time zone NOT NULL DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now(),
    kategori_id uuid,
    CONSTRAINT webumkm_services_kategori_id_fkey FOREIGN KEY (kategori_id) REFERENCES webumkm_service_categories(id) ON DELETE SET NULL,
    CONSTRAINT webumkm_services_store_id_fkey FOREIGN KEY (store_id) REFERENCES webumkm_stores(id) ON DELETE CASCADE,
    CONSTRAINT webumkm_services_pkey PRIMARY KEY (id)
);

CREATE INDEX idx_services_store ON public.webumkm_services USING btree (store_id);

-- ============================================================
-- Tabel: webumkm_sessions
-- ============================================================
CREATE TABLE webumkm_sessions (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    user_id uuid NOT NULL,
    user_type text NOT NULL,
    token text NOT NULL,
    ip_address text,
    user_agent text,
    created_at timestamp with time zone NOT NULL DEFAULT now(),
    expires_at timestamp with time zone NOT NULL,
    CONSTRAINT webumkm_sessions_user_type_check CHECK ((user_type = ANY (ARRAY['member'::text, 'admin'::text, 'sub_user'::text]))),
    CONSTRAINT webumkm_sessions_pkey PRIMARY KEY (id),
    CONSTRAINT webumkm_sessions_token_key UNIQUE (token)
);

CREATE UNIQUE INDEX webumkm_sessions_token_key ON public.webumkm_sessions USING btree (token);
CREATE INDEX idx_sessions_token ON public.webumkm_sessions USING btree (token);
CREATE INDEX idx_sessions_user ON public.webumkm_sessions USING btree (user_id, user_type);
CREATE INDEX idx_sessions_expires ON public.webumkm_sessions USING btree (expires_at);

-- ============================================================
-- Tabel: webumkm_store_section_features
-- ============================================================
CREATE TABLE webumkm_store_section_features (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    store_id uuid NOT NULL,
    section_key text NOT NULL,
    feature_key text NOT NULL,
    is_active boolean NOT NULL DEFAULT false,
    layout text NOT NULL DEFAULT 'single'::text,
    konten jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamp with time zone NOT NULL DEFAULT now(),
    updated_at timestamp with time zone NOT NULL DEFAULT now(),
    CONSTRAINT webumkm_store_section_features_store_id_fkey FOREIGN KEY (store_id) REFERENCES webumkm_stores(id),
    CONSTRAINT webumkm_store_section_features_pkey PRIMARY KEY (id),
    CONSTRAINT webumkm_store_section_feature_store_id_section_key_feature__key UNIQUE (store_id, section_key, feature_key)
);

CREATE UNIQUE INDEX webumkm_store_section_feature_store_id_section_key_feature__key ON public.webumkm_store_section_features USING btree (store_id, section_key, feature_key);

-- ============================================================
-- Tabel: webumkm_store_sections
-- ============================================================
CREATE TABLE webumkm_store_sections (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    store_id uuid NOT NULL,
    section_key text NOT NULL,
    template_id text NOT NULL DEFAULT 'A'::text,
    is_active boolean NOT NULL DEFAULT true,
    konten jsonb NOT NULL DEFAULT '{}'::jsonb,
    urutan integer NOT NULL DEFAULT 0,
    created_at timestamp with time zone NOT NULL DEFAULT now(),
    updated_at timestamp with time zone NOT NULL DEFAULT now(),
    CONSTRAINT webumkm_store_sections_store_id_fkey FOREIGN KEY (store_id) REFERENCES webumkm_stores(id),
    CONSTRAINT webumkm_store_sections_pkey PRIMARY KEY (id),
    CONSTRAINT webumkm_store_sections_store_id_section_key_key UNIQUE (store_id, section_key)
);

CREATE UNIQUE INDEX webumkm_store_sections_store_id_section_key_key ON public.webumkm_store_sections USING btree (store_id, section_key);

-- ============================================================
-- Tabel: webumkm_store_toggle_logs
-- ============================================================
CREATE TABLE webumkm_store_toggle_logs (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    store_id uuid NOT NULL,
    admin_id uuid NOT NULL,
    aksi text NOT NULL,
    alasan text NOT NULL,
    created_at timestamp with time zone NOT NULL DEFAULT now(),
    CONSTRAINT webumkm_store_toggle_logs_aksi_check CHECK ((aksi = ANY (ARRAY['on'::text, 'off'::text]))),
    CONSTRAINT webumkm_store_toggle_logs_admin_id_fkey FOREIGN KEY (admin_id) REFERENCES webumkm_admin_users(id),
    CONSTRAINT webumkm_store_toggle_logs_store_id_fkey FOREIGN KEY (store_id) REFERENCES webumkm_stores(id),
    CONSTRAINT webumkm_store_toggle_logs_pkey PRIMARY KEY (id)
);


-- ============================================================
-- Tabel: webumkm_stores
-- ============================================================
CREATE TABLE webumkm_stores (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    member_id uuid NOT NULL,
    nama_toko text NOT NULL,
    deskripsi text,
    logo_url text,
    banner_url text,
    alamat text,
    maps_url text,
    telepon text,
    jam_buka jsonb,
    instagram_url text,
    facebook_url text,
    tiktok_url text,
    is_published boolean NOT NULL DEFAULT false,
    is_toggled_off boolean NOT NULL DEFAULT false,
    created_at timestamp with time zone NOT NULL DEFAULT now(),
    updated_at timestamp with time zone NOT NULL DEFAULT now(),
    warna_utama text DEFAULT '#0F4D35'::text,
    warna_kedua text DEFAULT '#ffffff'::text,
    font_utama text DEFAULT 'sans-serif'::text,
    radius_card text DEFAULT '8px'::text,
    section_gap text DEFAULT '4rem'::text,
    anim_speed text DEFAULT '0.5s'::text,
    theme_id text DEFAULT 'default'::text,
    icon_style text DEFAULT 'regular'::text,
    ga_measurement_id text,
    tahun_berdiri smallint,
    nomor_rekening_toko text,
    nama_bank_toko text,
    nama_pemilik_rekening_toko text,
    whatsapp_url text,
    youtube_url text,
    twitter_url text,
    shopee_url text,
    tokopedia_url text,
    CONSTRAINT webumkm_stores_member_id_fkey FOREIGN KEY (member_id) REFERENCES webumkm_members(id) ON DELETE CASCADE,
    CONSTRAINT webumkm_stores_pkey PRIMARY KEY (id),
    CONSTRAINT webumkm_stores_member_id_key UNIQUE (member_id)
);

CREATE UNIQUE INDEX webumkm_stores_member_id_key ON public.webumkm_stores USING btree (member_id);
CREATE INDEX idx_stores_member ON public.webumkm_stores USING btree (member_id);

-- ============================================================
-- Tabel: webumkm_sub_users
-- ============================================================
CREATE TABLE webumkm_sub_users (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    member_id uuid NOT NULL,
    nama text NOT NULL,
    email text NOT NULL,
    password_hash text NOT NULL,
    permissions jsonb NOT NULL DEFAULT '{"balas_chat": false, "lihat_laporan": false, "kelola_pesanan": false, "konfirmasi_bayar": false}'::jsonb,
    is_active boolean NOT NULL DEFAULT true,
    created_at timestamp with time zone NOT NULL DEFAULT now(),
    deleted_at timestamp with time zone,
    role text NOT NULL DEFAULT 'operasional'::text,
    nomor_hp text,
    jabatan text,
    foto_url text,
    catatan text,
    updated_at timestamp with time zone NOT NULL DEFAULT now(),
    last_login_at timestamp with time zone,
    invitation_token text,
    invitation_accepted_at timestamp with time zone,
    CONSTRAINT webumkm_sub_users_member_id_fkey FOREIGN KEY (member_id) REFERENCES webumkm_members(id) ON DELETE CASCADE,
    CONSTRAINT webumkm_sub_users_pkey PRIMARY KEY (id),
    CONSTRAINT webumkm_sub_users_email_key UNIQUE (email)
);

CREATE UNIQUE INDEX webumkm_sub_users_email_key ON public.webumkm_sub_users USING btree (email);
CREATE INDEX idx_sub_users_member ON public.webumkm_sub_users USING btree (member_id);

-- ============================================================
-- Tabel: webumkm_subscription_price_history
-- ============================================================
CREATE TABLE webumkm_subscription_price_history (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    member_id uuid,
    paket text NOT NULL,
    siklus text NOT NULL,
    harga_lama integer NOT NULL,
    harga_baru integer NOT NULL,
    diubah_oleh uuid,
    alasan text NOT NULL,
    changed_at timestamp with time zone NOT NULL DEFAULT now(),
    CONSTRAINT webumkm_subscription_price_history_diubah_oleh_fkey FOREIGN KEY (diubah_oleh) REFERENCES webumkm_admin_users(id),
    CONSTRAINT webumkm_subscription_price_history_member_id_fkey FOREIGN KEY (member_id) REFERENCES webumkm_members(id),
    CONSTRAINT webumkm_subscription_price_history_pkey PRIMARY KEY (id)
);


-- ============================================================
-- Tabel: webumkm_subscription_promos
-- ============================================================
CREATE TABLE webumkm_subscription_promos (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    kode text NOT NULL,
    deskripsi text,
    tipe_diskon text NOT NULL,
    nilai_diskon integer NOT NULL,
    maks_diskon integer,
    berlaku_mulai timestamp with time zone,
    berlaku_sampai timestamp with time zone,
    paket_minimum text,
    siklus text,
    kuota_total integer,
    kuota_terpakai integer NOT NULL DEFAULT 0,
    is_active boolean NOT NULL DEFAULT true,
    dibuat_oleh uuid,
    created_at timestamp with time zone NOT NULL DEFAULT now(),
    updated_at timestamp with time zone NOT NULL DEFAULT now(),
    CONSTRAINT webumkm_subscription_promos_paket_check CHECK (((paket_minimum IS NULL) OR (paket_minimum = ANY (ARRAY['perintis'::text, 'tangguh'::text, 'sultan'::text])))),
    CONSTRAINT webumkm_subscription_promos_siklus_check CHECK (((siklus IS NULL) OR (siklus = ANY (ARRAY['monthly'::text, 'yearly'::text])))),
    CONSTRAINT webumkm_subscription_promos_tipe_check CHECK ((tipe_diskon = ANY (ARRAY['percent'::text, 'flat'::text]))),
    CONSTRAINT webumkm_subscription_promos_dibuat_oleh_fkey FOREIGN KEY (dibuat_oleh) REFERENCES webumkm_admin_users(id),
    CONSTRAINT webumkm_subscription_promos_pkey PRIMARY KEY (id),
    CONSTRAINT webumkm_subscription_promos_kode_key UNIQUE (kode)
);

CREATE UNIQUE INDEX webumkm_subscription_promos_kode_key ON public.webumkm_subscription_promos USING btree (kode);

-- ============================================================
-- Tabel: webumkm_subscriptions
-- ============================================================
CREATE TABLE webumkm_subscriptions (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    member_id uuid NOT NULL,
    paket text NOT NULL,
    siklus text NOT NULL,
    status text NOT NULL DEFAULT 'trial'::text,
    trial_start_at timestamp with time zone,
    trial_end_at timestamp with time zone,
    subscription_start_at timestamp with time zone,
    current_period_start timestamp with time zone,
    current_period_end timestamp with time zone,
    grace_period_end timestamp with time zone,
    harga_per_periode integer NOT NULL DEFAULT 0,
    created_at timestamp with time zone NOT NULL DEFAULT now(),
    scheduled_paket text,
    scheduled_siklus text,
    scheduled_at timestamp with time zone,
    CONSTRAINT webumkm_subscriptions_scheduled_paket_check CHECK (((scheduled_paket IS NULL) OR (scheduled_paket = ANY (ARRAY['perintis'::text, 'tangguh'::text, 'sultan'::text])))),
    CONSTRAINT webumkm_subscriptions_paket_check CHECK ((paket = ANY (ARRAY['perintis'::text, 'tangguh'::text, 'sultan'::text]))),
    CONSTRAINT webumkm_subscriptions_siklus_check CHECK ((siklus = ANY (ARRAY['monthly'::text, 'yearly'::text]))),
    CONSTRAINT webumkm_subscriptions_status_check CHECK ((status = ANY (ARRAY['trial'::text, 'active'::text, 'expired'::text, 'suspended'::text, 'banned'::text]))),
    CONSTRAINT webumkm_subscriptions_scheduled_siklus_check CHECK (((scheduled_siklus IS NULL) OR (scheduled_siklus = ANY (ARRAY['monthly'::text, 'yearly'::text])))),
    CONSTRAINT webumkm_subscriptions_member_id_fkey FOREIGN KEY (member_id) REFERENCES webumkm_members(id) ON DELETE CASCADE,
    CONSTRAINT webumkm_subscriptions_pkey PRIMARY KEY (id)
);

CREATE INDEX idx_subs_member ON public.webumkm_subscriptions USING btree (member_id);
CREATE INDEX idx_subs_status ON public.webumkm_subscriptions USING btree (status);
CREATE INDEX idx_subs_period_end ON public.webumkm_subscriptions USING btree (current_period_end);
CREATE INDEX idx_subs_grace ON public.webumkm_subscriptions USING btree (grace_period_end);

-- ============================================================
-- Tabel: webumkm_support_tickets
-- ============================================================
CREATE TABLE webumkm_support_tickets (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    member_id uuid NOT NULL,
    judul text NOT NULL,
    prioritas text NOT NULL DEFAULT 'normal'::text,
    status text NOT NULL DEFAULT 'open'::text,
    direspons_oleh uuid,
    first_response_at timestamp with time zone,
    resolved_at timestamp with time zone,
    created_at timestamp with time zone NOT NULL DEFAULT now(),
    CONSTRAINT webumkm_support_tickets_prioritas_check CHECK ((prioritas = ANY (ARRAY['normal'::text, 'priority'::text]))),
    CONSTRAINT webumkm_support_tickets_status_check CHECK ((status = ANY (ARRAY['open'::text, 'in_progress'::text, 'resolved'::text, 'closed'::text]))),
    CONSTRAINT webumkm_support_tickets_direspons_oleh_fkey FOREIGN KEY (direspons_oleh) REFERENCES webumkm_admin_users(id),
    CONSTRAINT webumkm_support_tickets_member_id_fkey FOREIGN KEY (member_id) REFERENCES webumkm_members(id),
    CONSTRAINT webumkm_support_tickets_pkey PRIMARY KEY (id)
);

CREATE INDEX idx_tickets_member ON public.webumkm_support_tickets USING btree (member_id);
CREATE INDEX idx_tickets_status ON public.webumkm_support_tickets USING btree (status);

-- ============================================================
-- Tabel: webumkm_testimonials
-- ============================================================
CREATE TABLE webumkm_testimonials (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    store_id uuid NOT NULL,
    nama_pelanggan text NOT NULL,
    isi text NOT NULL,
    rating integer NOT NULL DEFAULT 5,
    foto_url text,
    urutan integer NOT NULL DEFAULT 0,
    created_at timestamp with time zone NOT NULL DEFAULT now(),
    CONSTRAINT webumkm_testimonials_rating_check CHECK (((rating >= 1) AND (rating <= 5))),
    CONSTRAINT webumkm_testimonials_store_id_fkey FOREIGN KEY (store_id) REFERENCES webumkm_stores(id) ON DELETE CASCADE,
    CONSTRAINT webumkm_testimonials_pkey PRIMARY KEY (id)
);

CREATE INDEX idx_testimonials_store ON public.webumkm_testimonials USING btree (store_id);

-- ============================================================
-- Tabel: webumkm_ticket_replies
-- ============================================================
CREATE TABLE webumkm_ticket_replies (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    ticket_id uuid NOT NULL,
    pengirim_tipe text NOT NULL,
    pengirim_id uuid NOT NULL,
    isi text NOT NULL,
    created_at timestamp with time zone NOT NULL DEFAULT now(),
    CONSTRAINT webumkm_ticket_replies_pengirim_tipe_check CHECK ((pengirim_tipe = ANY (ARRAY['member'::text, 'admin'::text]))),
    CONSTRAINT webumkm_ticket_replies_ticket_id_fkey FOREIGN KEY (ticket_id) REFERENCES webumkm_support_tickets(id) ON DELETE CASCADE,
    CONSTRAINT webumkm_ticket_replies_pkey PRIMARY KEY (id)
);

CREATE INDEX idx_ticket_replies ON public.webumkm_ticket_replies USING btree (ticket_id);

-- ============================================================
-- Tabel: webumkm_upgrade_threshold
-- ============================================================
CREATE TABLE webumkm_upgrade_threshold (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    dari_paket text NOT NULL,
    ke_paket text NOT NULL,
    revenue_2bln bigint,
    pesanan_2bln integer,
    is_active boolean NOT NULL DEFAULT true,
    updated_by uuid,
    updated_at timestamp with time zone NOT NULL DEFAULT now(),
    CONSTRAINT webumkm_upgrade_threshold_updated_by_fkey FOREIGN KEY (updated_by) REFERENCES webumkm_admin_users(id),
    CONSTRAINT webumkm_upgrade_threshold_pkey PRIMARY KEY (id),
    CONSTRAINT webumkm_upgrade_threshold_dari_paket_ke_paket_key UNIQUE (dari_paket, ke_paket)
);

CREATE UNIQUE INDEX webumkm_upgrade_threshold_dari_paket_ke_paket_key ON public.webumkm_upgrade_threshold USING btree (dari_paket, ke_paket);

-- ============================================================
-- Tabel: webumkm_withdrawals
-- ============================================================
CREATE TABLE webumkm_withdrawals (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    member_id uuid NOT NULL,
    jumlah integer NOT NULL,
    nama_bank text NOT NULL,
    nomor_rekening text NOT NULL,
    nama_pemilik_rekening text NOT NULL,
    status text NOT NULL DEFAULT 'pending'::text,
    batas_waktu timestamp with time zone NOT NULL,
    diproses_oleh uuid,
    iris_reference_id text,
    catatan_admin text,
    created_at timestamp with time zone NOT NULL DEFAULT now(),
    completed_at timestamp with time zone,
    fee integer NOT NULL DEFAULT 0,
    jumlah_diterima integer,
    CONSTRAINT webumkm_withdrawals_status_check CHECK ((status = ANY (ARRAY['pending'::text, 'processing'::text, 'completed'::text, 'rejected'::text]))),
    CONSTRAINT webumkm_withdrawals_diproses_oleh_fkey FOREIGN KEY (diproses_oleh) REFERENCES webumkm_admin_users(id),
    CONSTRAINT webumkm_withdrawals_member_id_fkey FOREIGN KEY (member_id) REFERENCES webumkm_members(id),
    CONSTRAINT webumkm_withdrawals_pkey PRIMARY KEY (id)
);

CREATE INDEX idx_withdrawals_member ON public.webumkm_withdrawals USING btree (member_id);
CREATE INDEX idx_withdrawals_status ON public.webumkm_withdrawals USING btree (status);
CREATE INDEX idx_withdrawals_batas ON public.webumkm_withdrawals USING btree (batas_waktu);