-- GENERATED FROM backend/src/db/schema.sql; DO NOT EDIT init.sql DIRECTLY.
-- GENERATED by npm run schema:generate from baseline.sql + migrations; DO NOT EDIT.
CREATE EXTENSION IF NOT EXISTS "pg_trgm" WITH SCHEMA public;
CREATE EXTENSION IF NOT EXISTS "pgcrypto" WITH SCHEMA public;

CREATE FUNCTION public.update_updated_at() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$;

CREATE TABLE public.api_keys (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    name character varying(100) NOT NULL,
    key character varying(128) NOT NULL,
    key_hash character varying(64),
    permissions jsonb DEFAULT '["upload"]'::jsonb,
    enabled boolean DEFAULT true,
    last_used_at timestamp with time zone,
    created_at timestamp with time zone DEFAULT now()
);

CREATE TABLE public.chunk_upload_chunks (
    upload_id uuid NOT NULL,
    chunk_index integer NOT NULL,
    size bigint NOT NULL,
    sha256 character varying(64) NOT NULL,
    path character varying(1000) NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    CONSTRAINT chunk_upload_chunks_chunk_index_check CHECK ((chunk_index >= 0)),
    CONSTRAINT chunk_upload_chunks_sha256_check CHECK (((sha256)::text ~ '^[0-9a-f]{64}$'::text)),
    CONSTRAINT chunk_upload_chunks_size_check CHECK ((size > 0))
);

CREATE TABLE public.chunk_upload_reconciliations (
    operation_id uuid NOT NULL,
    upload_id uuid NOT NULL,
    completion_token uuid NOT NULL,
    provider character varying(50) NOT NULL,
    account_id uuid,
    stored_path character varying(2000),
    file_id uuid,
    object_state character varying(20) NOT NULL,
    index_state character varying(20) NOT NULL,
    reason text NOT NULL,
    status character varying(20) DEFAULT 'pending'::character varying NOT NULL,
    resolved_at timestamp with time zone,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    resolution character varying(30),
    lease_token uuid,
    lease_expires_at timestamp with time zone,
    attempts integer DEFAULT 0 NOT NULL,
    CONSTRAINT chunk_upload_reconciliations_index_state_check CHECK (((index_state)::text = ANY ((ARRAY['unknown'::character varying, 'present'::character varying, 'deleted'::character varying])::text[]))),
    CONSTRAINT chunk_upload_reconciliations_object_state_check CHECK (((object_state)::text = ANY ((ARRAY['unknown'::character varying, 'present'::character varying, 'deleted'::character varying])::text[]))),
    CONSTRAINT chunk_upload_reconciliations_status_check CHECK (((status)::text = ANY ((ARRAY['pending'::character varying, 'resolved'::character varying])::text[])))
);

CREATE TABLE public.chunk_upload_sessions (
    upload_id uuid NOT NULL,
    owner_id character varying(64) NOT NULL,
    filename character varying(255) NOT NULL,
    mime_type character varying(100) NOT NULL,
    folder character varying(255),
    total_size bigint NOT NULL,
    total_chunks integer NOT NULL,
    received_bytes bigint DEFAULT 0 NOT NULL,
    status character varying(20) DEFAULT 'open'::character varying NOT NULL,
    target_provider character varying(50) NOT NULL,
    target_account_id uuid,
    expires_at timestamp with time zone NOT NULL,
    completion_token uuid,
    completion_expires_at timestamp with time zone,
    completed_file_id uuid,
    last_error text,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    CONSTRAINT chunk_upload_sessions_check CHECK (((received_bytes >= 0) AND (received_bytes <= total_size))),
    CONSTRAINT chunk_upload_sessions_status_check CHECK (((status)::text = ANY ((ARRAY['open'::character varying, 'completing'::character varying, 'completed'::character varying, 'cancelled'::character varying, 'failed'::character varying])::text[]))),
    CONSTRAINT chunk_upload_sessions_total_chunks_check CHECK ((total_chunks > 0)),
    CONSTRAINT chunk_upload_sessions_total_size_check CHECK ((total_size > 0))
);

CREATE TABLE public.files (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    name character varying(255) NOT NULL,
    stored_name character varying(255) NOT NULL,
    type character varying(20) NOT NULL,
    mime_type character varying(100),
    size bigint NOT NULL,
    path character varying(500) NOT NULL,
    thumbnail_path character varying(500),
    preview_path character varying(500),
    derivative_status character varying(20) DEFAULT 'not_required'::character varying NOT NULL,
    derivative_error text,
    derivative_source_path character varying(1000),
    derivative_cleanup_source boolean DEFAULT false NOT NULL,
    derivative_attempts integer DEFAULT 0 NOT NULL,
    derivative_started_at timestamp with time zone,
    width integer,
    height integer,
    source character varying(50) DEFAULT 'web'::character varying,
    folder character varying(255),
    storage_account_id uuid,
    is_favorite boolean DEFAULT false,
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now(),
    CONSTRAINT files_derivative_status_check CHECK (((derivative_status)::text = ANY ((ARRAY['queued'::character varying, 'processing'::character varying, 'ready'::character varying, 'failed'::character varying, 'not_required'::character varying])::text[]))),
    CONSTRAINT files_type_check CHECK (((type)::text = ANY ((ARRAY['image'::character varying, 'video'::character varying, 'audio'::character varying, 'document'::character varying, 'other'::character varying])::text[])))
);

CREATE TABLE public.oauth_pending_flows (
    state_hash character varying(64) NOT NULL,
    provider character varying(32) NOT NULL,
    auth_session_hash character varying(64) NOT NULL,
    redirect_uri text NOT NULL,
    pending_config jsonb NOT NULL,
    flow_nonce character varying(128) NOT NULL,
    expires_at timestamp with time zone NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL
);

CREATE TABLE public.schema_migrations (
    version bigint NOT NULL,
    name text NOT NULL,
    checksum character varying(64) NOT NULL,
    applied_at timestamp with time zone DEFAULT now() NOT NULL
);

CREATE TABLE public.storage_account_cooldowns (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    storage_account_id uuid,
    provider character varying(50) NOT NULL,
    reason character varying(100) NOT NULL,
    cooldown_until timestamp with time zone NOT NULL,
    last_error text,
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now()
);

CREATE TABLE public.storage_account_leases (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    storage_account_id uuid NOT NULL,
    purpose character varying(50) NOT NULL,
    expires_at timestamp with time zone NOT NULL,
    released_at timestamp with time zone,
    created_at timestamp with time zone DEFAULT now() NOT NULL
);

CREATE TABLE public.storage_accounts (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    type character varying(50) NOT NULL,
    name character varying(255) NOT NULL,
    config jsonb NOT NULL,
    is_active boolean DEFAULT false,
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now(),
    last_probe_status character varying(20),
    last_probe_error text,
    last_probed_at timestamp with time zone
);

CREATE TABLE public.system_settings (
    key character varying(255) NOT NULL,
    value text NOT NULL,
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now()
);

CREATE TABLE public.task_center_dismissals (
    source_type character varying(30) NOT NULL,
    task_id character varying(128) NOT NULL,
    task_updated_at timestamp with time zone NOT NULL,
    dismissed_at timestamp with time zone DEFAULT now() NOT NULL
);

CREATE TABLE public.telegram_account_source_access (
    account_id uuid NOT NULL,
    source_key text NOT NULL,
    scope character varying(20) DEFAULT 'download'::character varying NOT NULL,
    access_state character varying(20) DEFAULT 'unknown'::character varying NOT NULL,
    last_error text,
    checked_at timestamp with time zone,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    CONSTRAINT telegram_account_source_access_access_state_check CHECK (((access_state)::text = ANY ((ARRAY['unknown'::character varying, 'allowed'::character varying, 'denied'::character varying])::text[]))),
    CONSTRAINT telegram_account_source_access_scope_check CHECK (((scope)::text = ANY ((ARRAY['download'::character varying, 'scan'::character varying, 'metadata'::character varying])::text[])))
);

CREATE TABLE public.telegram_auth (
    user_id bigint NOT NULL,
    authenticated_at timestamp with time zone DEFAULT now()
);

CREATE TABLE public.telegram_background_jobs (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    user_id bigint NOT NULL,
    chat_id bigint,
    kind character varying(50) NOT NULL,
    source text NOT NULL,
    status text DEFAULT 'pending'::text NOT NULL,
    scan_status text DEFAULT 'pending'::text,
    download_status text DEFAULT 'pending'::text,
    scan_cursor jsonb DEFAULT '{}'::jsonb,
    cooldown_until timestamp with time zone,
    paused_at timestamp with time zone,
    cancelled_at timestamp with time zone,
    params jsonb DEFAULT '{}'::jsonb,
    total_count integer DEFAULT 0,
    enqueued_count integer DEFAULT 0,
    skipped_count integer DEFAULT 0,
    duplicate_count integer DEFAULT 0,
    error text,
    started_at timestamp with time zone,
    finished_at timestamp with time zone,
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now(),
    ad_decision_id uuid,
    assigned_account_id uuid
);

CREATE TABLE public.telegram_channel_subscriptions (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    user_id bigint NOT NULL,
    chat_id bigint,
    source text NOT NULL,
    source_original text,
    source_type text DEFAULT 'public'::text,
    title text,
    last_message_id integer DEFAULT 0,
    folder_override text,
    enabled boolean DEFAULT true,
    disabled_reason text,
    disabled_at timestamp with time zone,
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now(),
    last_scan_at timestamp with time zone,
    last_success_at timestamp with time zone,
    last_error text,
    last_result jsonb,
    next_scan_at timestamp with time zone,
    target_mode character varying(20) DEFAULT 'follow_global'::character varying NOT NULL,
    target_provider character varying(50),
    target_account_id uuid,
    ad_filter_mode character varying(20) DEFAULT 'off'::character varying NOT NULL,
    file_filter_mode text DEFAULT 'all'::text NOT NULL,
    file_filter_types text[] DEFAULT '{}'::text[] NOT NULL,
    CONSTRAINT telegram_subscription_ad_filter_mode_check CHECK (((ad_filter_mode)::text = ANY ((ARRAY['off'::character varying, 'conservative'::character varying, 'aggressive'::character varying])::text[]))),
    CONSTRAINT telegram_subscription_file_filter_mode_check CHECK ((file_filter_mode = ANY (ARRAY['all'::text, 'include'::text, 'exclude'::text]))),
    CONSTRAINT telegram_subscription_file_filter_types_check CHECK (((file_filter_types <@ ARRAY['images'::text, 'raw-images'::text, 'videos'::text, 'audio'::text, 'archives'::text, 'apps'::text, 'text'::text, 'word-docs'::text, 'pdfs'::text, 'spreadsheets'::text, 'presentations'::text, 'ebooks'::text, 'code'::text, 'fonts'::text, 'design'::text, 'torrents'::text, 'documents'::text, 'other'::text]) AND (array_position(file_filter_types, NULL::text) IS NULL) AND (((file_filter_mode = 'all'::text) AND (cardinality(file_filter_types) = 0)) OR ((file_filter_mode = ANY (ARRAY['include'::text, 'exclude'::text])) AND (cardinality(file_filter_types) > 0))))),
    CONSTRAINT telegram_subscription_target_mode_check CHECK (((target_mode)::text = ANY ((ARRAY['follow_global'::character varying, 'fixed'::character varying])::text[])))
);

CREATE TABLE public.telegram_download_attempts (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    account_id uuid NOT NULL,
    source_key text NOT NULL,
    scope character varying(20) DEFAULT 'download'::character varying NOT NULL,
    job_id uuid,
    item_id uuid,
    lease_token uuid,
    status character varying(20) DEFAULT 'running'::character varying NOT NULL,
    error text,
    started_at timestamp with time zone DEFAULT now() NOT NULL,
    finished_at timestamp with time zone,
    CONSTRAINT telegram_download_attempts_check CHECK (((((status)::text = 'running'::text) AND (finished_at IS NULL)) OR (((status)::text <> 'running'::text) AND (finished_at IS NOT NULL)))),
    CONSTRAINT telegram_download_attempts_scope_check CHECK (((scope)::text = ANY ((ARRAY['download'::character varying, 'scan'::character varying, 'metadata'::character varying])::text[]))),
    CONSTRAINT telegram_download_attempts_status_check CHECK (((status)::text = ANY ((ARRAY['running'::character varying, 'succeeded'::character varying, 'failed'::character varying, 'cancelled'::character varying])::text[])))
);

CREATE TABLE public.telegram_download_items (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    job_id uuid,
    source text NOT NULL,
    source_peer text,
    origin text DEFAULT 'channel'::text,
    message_id integer NOT NULL,
    grouped_id text,
    shared_caption text,
    group_index integer,
    group_size integer,
    channel_post_id integer,
    file_name text,
    mime_type text,
    file_category text,
    generated_name boolean DEFAULT false,
    total_size bigint DEFAULT 0,
    folder_override text,
    status character varying(20) DEFAULT 'pending'::character varying NOT NULL,
    attempts integer DEFAULT 0,
    error text,
    last_error text,
    locked_at timestamp with time zone,
    completed_at timestamp with time zone,
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now(),
    lease_token uuid,
    lease_expires_at timestamp with time zone
);

CREATE TABLE public.telegram_notification_digest (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    user_id bigint NOT NULL,
    chat_id character varying(80) NOT NULL,
    kind character varying(40) NOT NULL,
    payload jsonb NOT NULL,
    claimed_at timestamp with time zone,
    delivered_at timestamp with time zone,
    created_at timestamp with time zone DEFAULT now() NOT NULL
);

CREATE TABLE public.telegram_notification_preferences (
    user_id bigint NOT NULL,
    chat_id text NOT NULL,
    preferences jsonb DEFAULT '{}'::jsonb NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL
);

CREATE TABLE public.telegram_path_states (
    chat_id text NOT NULL,
    mode character varying(10) NOT NULL,
    folder text NOT NULL,
    expires_at timestamp with time zone NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    CONSTRAINT telegram_path_states_mode_check CHECK (((mode)::text = ANY ((ARRAY['once'::character varying, 'session'::character varying])::text[])))
);

CREATE TABLE public.telegram_subscription_ad_decisions (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    subscription_id uuid NOT NULL,
    source_peer text NOT NULL,
    message_id integer NOT NULL,
    grouped_id text,
    message_ids integer[] DEFAULT '{}'::integer[] NOT NULL,
    decision character varying(20) NOT NULL,
    score integer DEFAULT 0 NOT NULL,
    reasons jsonb DEFAULT '[]'::jsonb NOT NULL,
    text_excerpt text,
    text_fingerprint text,
    domains text[] DEFAULT '{}'::text[] NOT NULL,
    usernames text[] DEFAULT '{}'::text[] NOT NULL,
    media_keys text[] DEFAULT '{}'::text[] NOT NULL,
    matched_rule_ids uuid[] DEFAULT '{}'::uuid[] NOT NULL,
    manual_label character varying(20),
    manually_reviewed_at timestamp with time zone,
    restored_job_id uuid,
    restored_at timestamp with time zone,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    restore_status character varying(20) DEFAULT 'not_requested'::character varying NOT NULL,
    restore_error text,
    restore_attempted_at timestamp with time zone,
    CONSTRAINT telegram_subscription_ad_decisions_decision_check CHECK (((decision)::text = ANY ((ARRAY['allow'::character varying, 'review'::character varying, 'blocked'::character varying])::text[]))),
    CONSTRAINT telegram_subscription_ad_decisions_manual_label_check CHECK (((manual_label)::text = ANY ((ARRAY['ad'::character varying, 'normal'::character varying])::text[]))),
    CONSTRAINT telegram_subscription_ad_decisions_score_check CHECK (((score >= 0) AND (score <= 100))),
    CONSTRAINT telegram_subscription_ad_restore_status_check CHECK (((restore_status)::text = ANY ((ARRAY['not_requested'::character varying, 'restoring'::character varying, 'restored'::character varying, 'failed'::character varying])::text[])))
);

CREATE TABLE public.telegram_subscription_ad_rules (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    subscription_id uuid NOT NULL,
    kind character varying(20) NOT NULL,
    action character varying(10) NOT NULL,
    pattern text NOT NULL,
    label text,
    enabled boolean DEFAULT true NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    CONSTRAINT telegram_subscription_ad_rules_action_check CHECK (((action)::text = ANY ((ARRAY['allow'::character varying, 'block'::character varying])::text[]))),
    CONSTRAINT telegram_subscription_ad_rules_kind_check CHECK (((kind)::text = ANY ((ARRAY['keyword'::character varying, 'domain'::character varying, 'username'::character varying, 'template'::character varying, 'media'::character varying])::text[])))
);

CREATE TABLE public.telegram_target_states (
    chat_id text NOT NULL,
    mode character varying(10) NOT NULL,
    provider character varying(50) NOT NULL,
    account_id uuid,
    expires_at timestamp with time zone NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    CONSTRAINT telegram_target_states_check CHECK (((((provider)::text = 'local'::text) AND (account_id IS NULL)) OR (((provider)::text <> 'local'::text) AND (account_id IS NOT NULL)))),
    CONSTRAINT telegram_target_states_mode_check CHECK (((mode)::text = ANY ((ARRAY['once'::character varying, 'session'::character varying])::text[])))
);

CREATE TABLE public.telegram_task_receipts (
    id text NOT NULL,
    chat_id text NOT NULL,
    user_id bigint NOT NULL,
    job_ids jsonb DEFAULT '[]'::jsonb NOT NULL,
    group_ids jsonb DEFAULT '[]'::jsonb NOT NULL,
    failures jsonb DEFAULT '[]'::jsonb NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL
);

CREATE TABLE public.telegram_user_accounts (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    telegram_user_id text NOT NULL,
    username text,
    display_name text,
    session_ciphertext text NOT NULL,
    enabled boolean DEFAULT true NOT NULL,
    health_state character varying(24) DEFAULT 'healthy'::character varying NOT NULL,
    cooldown_until timestamp with time zone,
    weight numeric(8,3) DEFAULT 1 NOT NULL,
    priority integer DEFAULT 0 NOT NULL,
    max_connections integer DEFAULT 4 NOT NULL,
    last_error text,
    last_connected_at timestamp with time zone,
    last_failure_at timestamp with time zone,
    session_expired_at timestamp with time zone,
    is_legacy boolean DEFAULT false NOT NULL,
    deleted_at timestamp with time zone,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    CONSTRAINT telegram_user_accounts_health_state_check CHECK (((health_state)::text = ANY ((ARRAY['healthy'::character varying, 'degraded'::character varying, 'session_expired'::character varying])::text[]))),
    CONSTRAINT telegram_user_accounts_max_connections_check CHECK ((max_connections > 0)),
    CONSTRAINT telegram_user_accounts_weight_check CHECK ((weight > (0)::numeric))
);

CREATE TABLE public.telegram_user_locales (
    user_id bigint NOT NULL,
    locale character varying(10) DEFAULT 'zh-CN'::character varying NOT NULL,
    explicit boolean DEFAULT false NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    CONSTRAINT telegram_user_locales_locale_check CHECK (((locale)::text = ANY ((ARRAY['zh-CN'::character varying, 'en'::character varying, 'ru'::character varying])::text[])))
);

CREATE TABLE public.telegram_write_reconciliations (
    operation_id uuid NOT NULL,
    job_id uuid NOT NULL,
    item_id uuid NOT NULL,
    child_lease_token uuid NOT NULL,
    provider character varying(50) NOT NULL,
    account_id uuid,
    stored_path character varying(2000),
    file_id uuid,
    object_state character varying(20) NOT NULL,
    index_state character varying(20) NOT NULL,
    reason text NOT NULL,
    status character varying(20) DEFAULT 'pending'::character varying NOT NULL,
    resolution character varying(30),
    lease_token uuid,
    lease_expires_at timestamp with time zone,
    attempts integer DEFAULT 0 NOT NULL,
    resolved_at timestamp with time zone,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    CONSTRAINT telegram_write_reconciliations_index_state_check CHECK (((index_state)::text = ANY ((ARRAY['unknown'::character varying, 'present'::character varying, 'deleted'::character varying])::text[]))),
    CONSTRAINT telegram_write_reconciliations_object_state_check CHECK (((object_state)::text = ANY ((ARRAY['unknown'::character varying, 'present'::character varying, 'deleted'::character varying])::text[]))),
    CONSTRAINT telegram_write_reconciliations_status_check CHECK (((status)::text = ANY ((ARRAY['pending'::character varying, 'resolved'::character varying])::text[])))
);

CREATE TABLE public.transfer_tasks (
    source_type character varying(30) NOT NULL,
    id character varying(128) NOT NULL,
    kind character varying(50) NOT NULL,
    title text NOT NULL,
    status character varying(30) NOT NULL,
    stage character varying(50) DEFAULT 'waiting'::character varying NOT NULL,
    progress numeric(5,2) DEFAULT 0 NOT NULL,
    owner_user_id bigint,
    chat_id text,
    source text,
    target_provider character varying(50),
    target_account_id uuid,
    target_folder text,
    total_items integer DEFAULT 0 NOT NULL,
    completed_items integer DEFAULT 0 NOT NULL,
    failed_items integer DEFAULT 0 NOT NULL,
    total_bytes bigint DEFAULT 0 NOT NULL,
    transferred_bytes bigint DEFAULT 0 NOT NULL,
    payload jsonb DEFAULT '{}'::jsonb NOT NULL,
    error text,
    retryable boolean DEFAULT false NOT NULL,
    cancel_requested boolean DEFAULT false NOT NULL,
    started_at timestamp with time zone,
    finished_at timestamp with time zone,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    execution_generation bigint DEFAULT 0 NOT NULL,
    snapshot_version bigint DEFAULT 0 NOT NULL,
    lease_token uuid,
    lease_expires_at timestamp with time zone
);

CREATE TABLE public.update_notification_deliveries (
    version character varying(64) NOT NULL,
    channel character varying(32) NOT NULL,
    recipient_id character varying(255) NOT NULL,
    status character varying(32) DEFAULT 'pending'::character varying NOT NULL,
    attempt_count integer DEFAULT 0 NOT NULL,
    last_attempt_at timestamp with time zone,
    delivered_at timestamp with time zone,
    last_error text,
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now(),
    CONSTRAINT update_notification_deliveries_status_check CHECK (((status)::text = ANY ((ARRAY['pending'::character varying, 'sending'::character varying, 'delivered'::character varying, 'failed'::character varying])::text[])))
);

CREATE TABLE public.web_sessions (
    token_hash character varying(64) NOT NULL,
    expires_at timestamp with time zone NOT NULL,
    created_at timestamp with time zone DEFAULT now()
);

ALTER TABLE ONLY public.api_keys
    ADD CONSTRAINT api_keys_key_hash_key UNIQUE (key_hash);

ALTER TABLE ONLY public.api_keys
    ADD CONSTRAINT api_keys_key_key UNIQUE (key);

ALTER TABLE ONLY public.api_keys
    ADD CONSTRAINT api_keys_pkey PRIMARY KEY (id);

ALTER TABLE ONLY public.chunk_upload_chunks
    ADD CONSTRAINT chunk_upload_chunks_pkey PRIMARY KEY (upload_id, chunk_index);

ALTER TABLE ONLY public.chunk_upload_reconciliations
    ADD CONSTRAINT chunk_upload_reconciliations_pkey PRIMARY KEY (operation_id);

ALTER TABLE ONLY public.chunk_upload_sessions
    ADD CONSTRAINT chunk_upload_sessions_pkey PRIMARY KEY (upload_id);

ALTER TABLE ONLY public.files
    ADD CONSTRAINT files_pkey PRIMARY KEY (id);

ALTER TABLE ONLY public.oauth_pending_flows
    ADD CONSTRAINT oauth_pending_flows_pkey PRIMARY KEY (state_hash);

ALTER TABLE ONLY public.schema_migrations
    ADD CONSTRAINT schema_migrations_pkey PRIMARY KEY (version);

ALTER TABLE ONLY public.storage_account_cooldowns
    ADD CONSTRAINT storage_account_cooldowns_pkey PRIMARY KEY (id);

ALTER TABLE ONLY public.storage_account_cooldowns
    ADD CONSTRAINT storage_account_cooldowns_storage_account_id_provider_reaso_key UNIQUE (storage_account_id, provider, reason);

ALTER TABLE ONLY public.storage_account_leases
    ADD CONSTRAINT storage_account_leases_pkey PRIMARY KEY (id);

ALTER TABLE ONLY public.storage_accounts
    ADD CONSTRAINT storage_accounts_pkey PRIMARY KEY (id);

ALTER TABLE ONLY public.system_settings
    ADD CONSTRAINT system_settings_pkey PRIMARY KEY (key);

ALTER TABLE ONLY public.task_center_dismissals
    ADD CONSTRAINT task_center_dismissals_pkey PRIMARY KEY (source_type, task_id);

ALTER TABLE ONLY public.telegram_account_source_access
    ADD CONSTRAINT telegram_account_source_access_pkey PRIMARY KEY (account_id, source_key, scope);

ALTER TABLE ONLY public.telegram_auth
    ADD CONSTRAINT telegram_auth_pkey PRIMARY KEY (user_id);

ALTER TABLE ONLY public.telegram_background_jobs
    ADD CONSTRAINT telegram_background_jobs_pkey PRIMARY KEY (id);

ALTER TABLE ONLY public.telegram_channel_subscriptions
    ADD CONSTRAINT telegram_channel_subscriptions_pkey PRIMARY KEY (id);

ALTER TABLE ONLY public.telegram_channel_subscriptions
    ADD CONSTRAINT telegram_channel_subscriptions_user_id_source_key UNIQUE (user_id, source);

ALTER TABLE ONLY public.telegram_download_attempts
    ADD CONSTRAINT telegram_download_attempts_pkey PRIMARY KEY (id);

ALTER TABLE ONLY public.telegram_download_items
    ADD CONSTRAINT telegram_download_items_pkey PRIMARY KEY (id);

ALTER TABLE ONLY public.telegram_notification_digest
    ADD CONSTRAINT telegram_notification_digest_pkey PRIMARY KEY (id);

ALTER TABLE ONLY public.telegram_notification_preferences
    ADD CONSTRAINT telegram_notification_preferences_pkey PRIMARY KEY (user_id, chat_id);

ALTER TABLE ONLY public.telegram_path_states
    ADD CONSTRAINT telegram_path_states_pkey PRIMARY KEY (chat_id, mode);

ALTER TABLE ONLY public.telegram_subscription_ad_decisions
    ADD CONSTRAINT telegram_subscription_ad_deci_subscription_id_source_peer_m_key UNIQUE (subscription_id, source_peer, message_id);

ALTER TABLE ONLY public.telegram_subscription_ad_decisions
    ADD CONSTRAINT telegram_subscription_ad_decisions_pkey PRIMARY KEY (id);

ALTER TABLE ONLY public.telegram_subscription_ad_rules
    ADD CONSTRAINT telegram_subscription_ad_rules_pkey PRIMARY KEY (id);

ALTER TABLE ONLY public.telegram_target_states
    ADD CONSTRAINT telegram_target_states_pkey PRIMARY KEY (chat_id, mode);

ALTER TABLE ONLY public.telegram_task_receipts
    ADD CONSTRAINT telegram_task_receipts_pkey PRIMARY KEY (id);

ALTER TABLE ONLY public.telegram_user_accounts
    ADD CONSTRAINT telegram_user_accounts_pkey PRIMARY KEY (id);

ALTER TABLE ONLY public.telegram_user_accounts
    ADD CONSTRAINT telegram_user_accounts_telegram_user_id_key UNIQUE (telegram_user_id);

ALTER TABLE ONLY public.telegram_user_locales
    ADD CONSTRAINT telegram_user_locales_pkey PRIMARY KEY (user_id);

ALTER TABLE ONLY public.telegram_write_reconciliations
    ADD CONSTRAINT telegram_write_reconciliations_pkey PRIMARY KEY (operation_id);

ALTER TABLE ONLY public.transfer_tasks
    ADD CONSTRAINT transfer_tasks_pkey PRIMARY KEY (source_type, id);

ALTER TABLE ONLY public.update_notification_deliveries
    ADD CONSTRAINT update_notification_deliveries_pkey PRIMARY KEY (version, channel, recipient_id);

ALTER TABLE ONLY public.web_sessions
    ADD CONSTRAINT web_sessions_pkey PRIMARY KEY (token_hash);

CREATE INDEX idx_chunk_upload_reconciliations_claim ON public.chunk_upload_reconciliations USING btree (status, lease_expires_at, created_at);

CREATE INDEX idx_chunk_upload_reconciliations_pending ON public.chunk_upload_reconciliations USING btree (status, created_at);

CREATE UNIQUE INDEX idx_chunk_upload_reconciliations_pending_completion ON public.chunk_upload_reconciliations USING btree (upload_id, completion_token) WHERE ((status)::text = 'pending'::text);

CREATE INDEX idx_chunk_upload_sessions_budget ON public.chunk_upload_sessions USING btree (status, expires_at);

CREATE INDEX idx_chunk_upload_sessions_completion_lease ON public.chunk_upload_sessions USING btree (status, completion_expires_at);

CREATE INDEX idx_chunk_upload_sessions_owner ON public.chunk_upload_sessions USING btree (owner_id, created_at DESC);

CREATE INDEX idx_files_account_created ON public.files USING btree (storage_account_id, created_at DESC, id DESC);

CREATE INDEX idx_files_account_fav_created ON public.files USING btree (storage_account_id, is_favorite, created_at DESC, id DESC);

CREATE INDEX idx_files_account_folder_created ON public.files USING btree (storage_account_id, folder, created_at DESC, id DESC);

CREATE INDEX idx_files_created_at ON public.files USING btree (created_at DESC);

CREATE INDEX idx_files_derivative_pending ON public.files USING btree (derivative_status, created_at, id) WHERE ((derivative_status)::text = ANY ((ARRAY['queued'::character varying, 'processing'::character varying])::text[]));

CREATE INDEX idx_files_folder ON public.files USING btree (folder);

CREATE INDEX idx_files_folder_trgm ON public.files USING gin (folder public.gin_trgm_ops) WHERE (folder IS NOT NULL);

CREATE INDEX idx_files_is_favorite ON public.files USING btree (is_favorite);

CREATE INDEX idx_files_name_trgm ON public.files USING gin (name public.gin_trgm_ops);

CREATE INDEX idx_files_source_created ON public.files USING btree (source, created_at DESC, id DESC);

CREATE INDEX idx_files_source_fav_created ON public.files USING btree (source, is_favorite, created_at DESC, id DESC);

CREATE INDEX idx_files_source_folder_created ON public.files USING btree (source, folder, created_at DESC, id DESC);

CREATE INDEX idx_files_storage_account_id ON public.files USING btree (storage_account_id);

CREATE INDEX idx_files_type ON public.files USING btree (type);

CREATE INDEX idx_oauth_pending_flows_expiry ON public.oauth_pending_flows USING btree (expires_at);

CREATE UNIQUE INDEX idx_oauth_pending_flows_session ON public.oauth_pending_flows USING btree (auth_session_hash);

CREATE INDEX idx_storage_account_cooldowns_until ON public.storage_account_cooldowns USING btree (cooldown_until);

CREATE INDEX idx_storage_account_leases_active ON public.storage_account_leases USING btree (storage_account_id, expires_at) WHERE (released_at IS NULL);

CREATE INDEX idx_task_center_dismissals_version ON public.task_center_dismissals USING btree (source_type, task_id, task_updated_at);

CREATE INDEX idx_telegram_account_source_access_lookup ON public.telegram_account_source_access USING btree (source_key, scope, access_state, checked_at DESC);

CREATE INDEX idx_telegram_download_attempts_account_started ON public.telegram_download_attempts USING btree (account_id, started_at DESC);

CREATE INDEX idx_telegram_download_attempts_job_item ON public.telegram_download_attempts USING btree (job_id, item_id, started_at DESC);

CREATE INDEX idx_telegram_download_attempts_running ON public.telegram_download_attempts USING btree (account_id, source_key) WHERE ((status)::text = 'running'::text);

CREATE INDEX idx_telegram_notification_digest_pending ON public.telegram_notification_digest USING btree (user_id, chat_id, created_at) WHERE (delivered_at IS NULL);

CREATE INDEX idx_telegram_path_states_expiry ON public.telegram_path_states USING btree (expires_at);

CREATE INDEX idx_telegram_target_states_account ON public.telegram_target_states USING btree (account_id) WHERE (account_id IS NOT NULL);

CREATE INDEX idx_telegram_target_states_expiry ON public.telegram_target_states USING btree (expires_at);

CREATE INDEX idx_telegram_user_accounts_schedulable ON public.telegram_user_accounts USING btree (enabled, health_state, cooldown_until, priority DESC);

CREATE INDEX idx_telegram_user_accounts_visible ON public.telegram_user_accounts USING btree (created_at, id) WHERE (deleted_at IS NULL);

CREATE UNIQUE INDEX idx_tg_background_jobs_ad_decision ON public.telegram_background_jobs USING btree (ad_decision_id) WHERE (ad_decision_id IS NOT NULL);

CREATE INDEX idx_tg_background_jobs_assigned_account ON public.telegram_background_jobs USING btree (assigned_account_id, status);

CREATE INDEX idx_tg_background_jobs_cooldown ON public.telegram_background_jobs USING btree (cooldown_until);

CREATE INDEX idx_tg_background_jobs_pipeline ON public.telegram_background_jobs USING btree (status, scan_status, download_status);

CREATE INDEX idx_tg_background_jobs_status ON public.telegram_background_jobs USING btree (status);

CREATE INDEX idx_tg_background_jobs_user_created ON public.telegram_background_jobs USING btree (user_id, created_at DESC);

CREATE INDEX idx_tg_channel_subscriptions_enabled ON public.telegram_channel_subscriptions USING btree (enabled);

CREATE INDEX idx_tg_channel_subscriptions_target_account ON public.telegram_channel_subscriptions USING btree (target_account_id) WHERE (target_account_id IS NOT NULL);

CREATE INDEX idx_tg_channel_subscriptions_user_id ON public.telegram_channel_subscriptions USING btree (user_id);

CREATE UNIQUE INDEX idx_tg_download_items_job_peer_msg ON public.telegram_download_items USING btree (job_id, source_peer, message_id);

CREATE INDEX idx_tg_download_items_job_status ON public.telegram_download_items USING btree (job_id, status);

CREATE INDEX idx_tg_download_items_lease_expiry ON public.telegram_download_items USING btree (status, lease_expires_at);

CREATE INDEX idx_tg_download_items_recover ON public.telegram_download_items USING btree (status, locked_at);

CREATE INDEX idx_tg_subscription_ad_decisions_blocked ON public.telegram_subscription_ad_decisions USING btree (subscription_id, decision, created_at DESC);

CREATE INDEX idx_tg_subscription_ad_decisions_subscription ON public.telegram_subscription_ad_decisions USING btree (subscription_id, created_at DESC);

CREATE INDEX idx_tg_subscription_ad_decisions_template ON public.telegram_subscription_ad_decisions USING btree (subscription_id, manual_label, created_at DESC) WHERE (manual_label IS NOT NULL);

CREATE INDEX idx_tg_subscription_ad_rules_subscription ON public.telegram_subscription_ad_rules USING btree (subscription_id, enabled, created_at DESC);

CREATE UNIQUE INDEX idx_tg_subscription_ad_rules_unique ON public.telegram_subscription_ad_rules USING btree (subscription_id, kind, action, pattern);

CREATE INDEX idx_tg_write_reconciliations_claim ON public.telegram_write_reconciliations USING btree (status, lease_expires_at, created_at);

CREATE UNIQUE INDEX idx_tg_write_reconciliations_pending_item ON public.telegram_write_reconciliations USING btree (item_id) WHERE ((status)::text = 'pending'::text);

CREATE INDEX idx_transfer_tasks_claim ON public.transfer_tasks USING btree (source_type, status, lease_expires_at, updated_at);

CREATE INDEX idx_transfer_tasks_owner ON public.transfer_tasks USING btree (owner_user_id, chat_id, updated_at DESC);

CREATE INDEX idx_transfer_tasks_status ON public.transfer_tasks USING btree (status, updated_at DESC);

CREATE INDEX idx_transfer_tasks_updated ON public.transfer_tasks USING btree (updated_at DESC);

CREATE INDEX idx_update_notification_pending ON public.update_notification_deliveries USING btree (status, last_attempt_at) WHERE ((status)::text <> 'delivered'::text);

CREATE INDEX idx_web_sessions_expires ON public.web_sessions USING btree (expires_at);

CREATE TRIGGER chunk_upload_sessions_updated_at BEFORE UPDATE ON public.chunk_upload_sessions FOR EACH ROW EXECUTE FUNCTION public.update_updated_at();

CREATE TRIGGER files_updated_at BEFORE UPDATE ON public.files FOR EACH ROW EXECUTE FUNCTION public.update_updated_at();

CREATE TRIGGER storage_account_cooldowns_updated_at BEFORE UPDATE ON public.storage_account_cooldowns FOR EACH ROW EXECUTE FUNCTION public.update_updated_at();

CREATE TRIGGER storage_accounts_updated_at BEFORE UPDATE ON public.storage_accounts FOR EACH ROW EXECUTE FUNCTION public.update_updated_at();

CREATE TRIGGER system_settings_updated_at BEFORE UPDATE ON public.system_settings FOR EACH ROW EXECUTE FUNCTION public.update_updated_at();

CREATE TRIGGER telegram_account_source_access_updated_at BEFORE UPDATE ON public.telegram_account_source_access FOR EACH ROW EXECUTE FUNCTION public.update_updated_at();

CREATE TRIGGER telegram_background_jobs_updated_at BEFORE UPDATE ON public.telegram_background_jobs FOR EACH ROW EXECUTE FUNCTION public.update_updated_at();

CREATE TRIGGER telegram_channel_subscriptions_updated_at BEFORE UPDATE ON public.telegram_channel_subscriptions FOR EACH ROW EXECUTE FUNCTION public.update_updated_at();

CREATE TRIGGER telegram_download_items_updated_at BEFORE UPDATE ON public.telegram_download_items FOR EACH ROW EXECUTE FUNCTION public.update_updated_at();

CREATE TRIGGER telegram_notification_preferences_updated_at BEFORE UPDATE ON public.telegram_notification_preferences FOR EACH ROW EXECUTE FUNCTION public.update_updated_at();

CREATE TRIGGER telegram_path_states_updated_at BEFORE UPDATE ON public.telegram_path_states FOR EACH ROW EXECUTE FUNCTION public.update_updated_at();

CREATE TRIGGER telegram_subscription_ad_decisions_updated_at BEFORE UPDATE ON public.telegram_subscription_ad_decisions FOR EACH ROW EXECUTE FUNCTION public.update_updated_at();

CREATE TRIGGER telegram_subscription_ad_rules_updated_at BEFORE UPDATE ON public.telegram_subscription_ad_rules FOR EACH ROW EXECUTE FUNCTION public.update_updated_at();

CREATE TRIGGER telegram_target_states_updated_at BEFORE UPDATE ON public.telegram_target_states FOR EACH ROW EXECUTE FUNCTION public.update_updated_at();

CREATE TRIGGER telegram_user_accounts_updated_at BEFORE UPDATE ON public.telegram_user_accounts FOR EACH ROW EXECUTE FUNCTION public.update_updated_at();

CREATE TRIGGER telegram_user_locales_updated_at BEFORE UPDATE ON public.telegram_user_locales FOR EACH ROW EXECUTE FUNCTION public.update_updated_at();

CREATE TRIGGER transfer_tasks_updated_at BEFORE UPDATE ON public.transfer_tasks FOR EACH ROW EXECUTE FUNCTION public.update_updated_at();

CREATE TRIGGER update_notification_deliveries_updated_at BEFORE UPDATE ON public.update_notification_deliveries FOR EACH ROW EXECUTE FUNCTION public.update_updated_at();

ALTER TABLE ONLY public.chunk_upload_chunks
    ADD CONSTRAINT chunk_upload_chunks_upload_id_fkey FOREIGN KEY (upload_id) REFERENCES public.chunk_upload_sessions(upload_id) ON DELETE CASCADE;

ALTER TABLE ONLY public.chunk_upload_reconciliations
    ADD CONSTRAINT chunk_upload_reconciliations_account_id_fkey FOREIGN KEY (account_id) REFERENCES public.storage_accounts(id) ON DELETE SET NULL;

ALTER TABLE ONLY public.chunk_upload_reconciliations
    ADD CONSTRAINT chunk_upload_reconciliations_upload_id_fkey FOREIGN KEY (upload_id) REFERENCES public.chunk_upload_sessions(upload_id) ON DELETE CASCADE;

ALTER TABLE ONLY public.chunk_upload_sessions
    ADD CONSTRAINT chunk_upload_sessions_completed_file_id_fkey FOREIGN KEY (completed_file_id) REFERENCES public.files(id) ON DELETE SET NULL;

ALTER TABLE ONLY public.chunk_upload_sessions
    ADD CONSTRAINT chunk_upload_sessions_target_account_id_fkey FOREIGN KEY (target_account_id) REFERENCES public.storage_accounts(id) ON DELETE SET NULL;

ALTER TABLE ONLY public.files
    ADD CONSTRAINT files_storage_account_id_fkey FOREIGN KEY (storage_account_id) REFERENCES public.storage_accounts(id) ON DELETE CASCADE;

ALTER TABLE ONLY public.storage_account_cooldowns
    ADD CONSTRAINT storage_account_cooldowns_storage_account_id_fkey FOREIGN KEY (storage_account_id) REFERENCES public.storage_accounts(id) ON DELETE CASCADE;

ALTER TABLE ONLY public.storage_account_leases
    ADD CONSTRAINT storage_account_leases_storage_account_id_fkey FOREIGN KEY (storage_account_id) REFERENCES public.storage_accounts(id) ON DELETE CASCADE;

ALTER TABLE ONLY public.telegram_account_source_access
    ADD CONSTRAINT telegram_account_source_access_account_id_fkey FOREIGN KEY (account_id) REFERENCES public.telegram_user_accounts(id) ON DELETE CASCADE;

ALTER TABLE ONLY public.telegram_background_jobs
    ADD CONSTRAINT telegram_background_jobs_ad_decision_id_fkey FOREIGN KEY (ad_decision_id) REFERENCES public.telegram_subscription_ad_decisions(id) ON DELETE SET NULL;

ALTER TABLE ONLY public.telegram_background_jobs
    ADD CONSTRAINT telegram_background_jobs_assigned_account_id_fkey FOREIGN KEY (assigned_account_id) REFERENCES public.telegram_user_accounts(id) ON DELETE SET NULL;

ALTER TABLE ONLY public.telegram_channel_subscriptions
    ADD CONSTRAINT telegram_channel_subscriptions_target_account_id_fkey FOREIGN KEY (target_account_id) REFERENCES public.storage_accounts(id) ON DELETE RESTRICT;

ALTER TABLE ONLY public.telegram_download_attempts
    ADD CONSTRAINT telegram_download_attempts_account_id_fkey FOREIGN KEY (account_id) REFERENCES public.telegram_user_accounts(id) ON DELETE RESTRICT;

ALTER TABLE ONLY public.telegram_download_attempts
    ADD CONSTRAINT telegram_download_attempts_item_id_fkey FOREIGN KEY (item_id) REFERENCES public.telegram_download_items(id) ON DELETE SET NULL;

ALTER TABLE ONLY public.telegram_download_attempts
    ADD CONSTRAINT telegram_download_attempts_job_id_fkey FOREIGN KEY (job_id) REFERENCES public.telegram_background_jobs(id) ON DELETE SET NULL;

ALTER TABLE ONLY public.telegram_download_items
    ADD CONSTRAINT telegram_download_items_job_id_fkey FOREIGN KEY (job_id) REFERENCES public.telegram_background_jobs(id) ON DELETE CASCADE;

ALTER TABLE ONLY public.telegram_subscription_ad_decisions
    ADD CONSTRAINT telegram_subscription_ad_decisions_restored_job_id_fkey FOREIGN KEY (restored_job_id) REFERENCES public.telegram_background_jobs(id) ON DELETE SET NULL;

ALTER TABLE ONLY public.telegram_subscription_ad_decisions
    ADD CONSTRAINT telegram_subscription_ad_decisions_subscription_id_fkey FOREIGN KEY (subscription_id) REFERENCES public.telegram_channel_subscriptions(id) ON DELETE CASCADE;

ALTER TABLE ONLY public.telegram_subscription_ad_rules
    ADD CONSTRAINT telegram_subscription_ad_rules_subscription_id_fkey FOREIGN KEY (subscription_id) REFERENCES public.telegram_channel_subscriptions(id) ON DELETE CASCADE;

ALTER TABLE ONLY public.telegram_target_states
    ADD CONSTRAINT telegram_target_states_account_id_fkey FOREIGN KEY (account_id) REFERENCES public.storage_accounts(id) ON DELETE RESTRICT;

ALTER TABLE ONLY public.telegram_write_reconciliations
    ADD CONSTRAINT telegram_write_reconciliations_account_id_fkey FOREIGN KEY (account_id) REFERENCES public.storage_accounts(id) ON DELETE SET NULL;

ALTER TABLE ONLY public.telegram_write_reconciliations
    ADD CONSTRAINT telegram_write_reconciliations_item_id_fkey FOREIGN KEY (item_id) REFERENCES public.telegram_download_items(id) ON DELETE CASCADE;

ALTER TABLE ONLY public.telegram_write_reconciliations
    ADD CONSTRAINT telegram_write_reconciliations_job_id_fkey FOREIGN KEY (job_id) REFERENCES public.telegram_background_jobs(id) ON DELETE CASCADE;

ALTER TABLE ONLY public.transfer_tasks
    ADD CONSTRAINT transfer_tasks_target_account_id_fkey FOREIGN KEY (target_account_id) REFERENCES public.storage_accounts(id) ON DELETE SET NULL;