AIyaodianzhushou/database/schema.sql

--
-- PostgreSQL database dump
--

\restrict ESQ1NFAz6xVH1qNjdPPTbrmPbtepjMOr7SzmqGUWW2SDRd1pbhCctBfDStfCiB2

-- Dumped from database version 16.14 (Debian 16.14-1.pgdg12+1)
-- Dumped by pg_dump version 16.14 (Debian 16.14-1.pgdg12+1)

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

--
-- Name: vector; Type: EXTENSION; Schema: -; Owner: -
--

CREATE EXTENSION IF NOT EXISTS vector WITH SCHEMA public;


--
-- Name: EXTENSION vector; Type: COMMENT; Schema: -; Owner: -
--

COMMENT ON EXTENSION vector IS 'vector data type and ivfflat and hnsw access methods';


SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: answer_records; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.answer_records (
    id integer NOT NULL,
    user_id integer NOT NULL,
    question_id character varying(64) NOT NULL,
    user_answer character varying(16) NOT NULL,
    is_correct boolean NOT NULL,
    study_mode character varying(32) NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL
);


--
-- Name: answer_records_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.answer_records_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: answer_records_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.answer_records_id_seq OWNED BY public.answer_records.id;


--
-- Name: conversations; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.conversations (
    id integer NOT NULL,
    conversation_id character varying(64) NOT NULL,
    user_id integer,
    title character varying(256),
    created_at timestamp with time zone DEFAULT now() NOT NULL
);


--
-- Name: conversations_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.conversations_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: conversations_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.conversations_id_seq OWNED BY public.conversations.id;


--
-- Name: drug_chunks; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.drug_chunks (
    id integer NOT NULL,
    drug_id character varying(64) NOT NULL,
    section character varying(64),
    content text NOT NULL,
    source text,
    chunk_index integer NOT NULL,
    embedding json,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    vec public.vector(1024)
);


--
-- Name: drug_chunks_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.drug_chunks_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: drug_chunks_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.drug_chunks_id_seq OWNED BY public.drug_chunks.id;


--
-- Name: drugs; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.drugs (
    id integer NOT NULL,
    drug_id character varying(64) NOT NULL,
    name character varying(256) NOT NULL,
    name_en character varying(256),
    pinyin character varying(256),
    category character varying(64),
    subcategory character varying(128),
    approval_number character varying(64),
    sections jsonb,
    source_version character varying(32),
    source_volume character varying(512),
    source_page character varying(256),
    is_active boolean NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL
);


--
-- Name: drugs_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.drugs_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: drugs_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.drugs_id_seq OWNED BY public.drugs.id;


--
-- Name: knowledge_points; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.knowledge_points (
    id integer NOT NULL,
    point_id character varying(64) NOT NULL,
    subject character varying(64) NOT NULL,
    chapter_id character varying(64) NOT NULL,
    chapter_name character varying(256) NOT NULL,
    title character varying(512) NOT NULL,
    content text NOT NULL,
    difficulty integer NOT NULL,
    frequency character varying(16),
    related_drugs jsonb,
    source text,
    vector_id character varying(128),
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL
);


--
-- Name: knowledge_points_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.knowledge_points_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: knowledge_points_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.knowledge_points_id_seq OWNED BY public.knowledge_points.id;


--
-- Name: messages; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.messages (
    id integer NOT NULL,
    conversation_id character varying(64) NOT NULL,
    role character varying(32) NOT NULL,
    content text NOT NULL,
    intent character varying(32),
    sources json,
    feedback character varying(32),
    created_at timestamp with time zone DEFAULT now() NOT NULL
);


--
-- Name: messages_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.messages_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: messages_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.messages_id_seq OWNED BY public.messages.id;


--
-- Name: questions; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.questions (
    id integer NOT NULL,
    question_id character varying(64) NOT NULL,
    question_type character varying(8) NOT NULL,
    subject character varying(64) NOT NULL,
    chapter_id character varying(64) NOT NULL,
    difficulty integer NOT NULL,
    content text NOT NULL,
    options jsonb NOT NULL,
    answer character varying(16) NOT NULL,
    explanation text NOT NULL,
    knowledge_point_ids jsonb,
    source text,
    frequency character varying(16),
    audited boolean NOT NULL,
    correct_count integer NOT NULL,
    attempt_count integer NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL
);


--
-- Name: questions_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.questions_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: questions_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.questions_id_seq OWNED BY public.questions.id;


--
-- Name: user_progress; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.user_progress (
    id integer NOT NULL,
    user_id integer NOT NULL,
    subject character varying(64),
    chapter_id character varying(64),
    knowledge_point_id character varying(64),
    questions_answered integer NOT NULL,
    questions_correct integer NOT NULL,
    study_seconds integer NOT NULL,
    last_study_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
);


--
-- Name: user_progress_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.user_progress_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: user_progress_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.user_progress_id_seq OWNED BY public.user_progress.id;


--
-- Name: users; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.users (
    id integer NOT NULL,
    openid character varying(128) NOT NULL,
    nickname character varying(128),
    avatar_url character varying(512),
    role character varying(32) NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL
);


--
-- Name: users_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

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


--
-- Name: users_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

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


--
-- Name: answer_records id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.answer_records ALTER COLUMN id SET DEFAULT nextval('public.answer_records_id_seq'::regclass);


--
-- Name: conversations id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.conversations ALTER COLUMN id SET DEFAULT nextval('public.conversations_id_seq'::regclass);


--
-- Name: drug_chunks id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.drug_chunks ALTER COLUMN id SET DEFAULT nextval('public.drug_chunks_id_seq'::regclass);


--
-- Name: drugs id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.drugs ALTER COLUMN id SET DEFAULT nextval('public.drugs_id_seq'::regclass);


--
-- Name: knowledge_points id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.knowledge_points ALTER COLUMN id SET DEFAULT nextval('public.knowledge_points_id_seq'::regclass);


--
-- Name: messages id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.messages ALTER COLUMN id SET DEFAULT nextval('public.messages_id_seq'::regclass);


--
-- Name: questions id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.questions ALTER COLUMN id SET DEFAULT nextval('public.questions_id_seq'::regclass);


--
-- Name: user_progress id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.user_progress ALTER COLUMN id SET DEFAULT nextval('public.user_progress_id_seq'::regclass);


--
-- Name: users id; Type: DEFAULT; Schema: public; Owner: -
--

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


--
-- Name: answer_records answer_records_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.answer_records
    ADD CONSTRAINT answer_records_pkey PRIMARY KEY (id);


--
-- Name: conversations conversations_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.conversations
    ADD CONSTRAINT conversations_pkey PRIMARY KEY (id);


--
-- Name: drug_chunks drug_chunks_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.drug_chunks
    ADD CONSTRAINT drug_chunks_pkey PRIMARY KEY (id);


--
-- Name: drugs drugs_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.drugs
    ADD CONSTRAINT drugs_pkey PRIMARY KEY (id);


--
-- Name: knowledge_points knowledge_points_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.knowledge_points
    ADD CONSTRAINT knowledge_points_pkey PRIMARY KEY (id);


--
-- Name: messages messages_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.messages
    ADD CONSTRAINT messages_pkey PRIMARY KEY (id);


--
-- Name: questions questions_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.questions
    ADD CONSTRAINT questions_pkey PRIMARY KEY (id);


--
-- Name: user_progress user_progress_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.user_progress
    ADD CONSTRAINT user_progress_pkey PRIMARY KEY (id);


--
-- Name: users users_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

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


--
-- Name: idx_drug_chunks_vec; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_drug_chunks_vec ON public.drug_chunks USING ivfflat (vec) WITH (lists='10');


--
-- Name: ix_answer_records_question_id; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX ix_answer_records_question_id ON public.answer_records USING btree (question_id);


--
-- Name: ix_answer_records_user_id; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX ix_answer_records_user_id ON public.answer_records USING btree (user_id);


--
-- Name: ix_conversations_conversation_id; Type: INDEX; Schema: public; Owner: -
--

CREATE UNIQUE INDEX ix_conversations_conversation_id ON public.conversations USING btree (conversation_id);


--
-- Name: ix_drug_chunks_drug_id; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX ix_drug_chunks_drug_id ON public.drug_chunks USING btree (drug_id);


--
-- Name: ix_drugs_category; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX ix_drugs_category ON public.drugs USING btree (category);


--
-- Name: ix_drugs_drug_id; Type: INDEX; Schema: public; Owner: -
--

CREATE UNIQUE INDEX ix_drugs_drug_id ON public.drugs USING btree (drug_id);


--
-- Name: ix_drugs_name; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX ix_drugs_name ON public.drugs USING btree (name);


--
-- Name: ix_knowledge_points_chapter_id; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX ix_knowledge_points_chapter_id ON public.knowledge_points USING btree (chapter_id);


--
-- Name: ix_knowledge_points_point_id; Type: INDEX; Schema: public; Owner: -
--

CREATE UNIQUE INDEX ix_knowledge_points_point_id ON public.knowledge_points USING btree (point_id);


--
-- Name: ix_knowledge_points_subject; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX ix_knowledge_points_subject ON public.knowledge_points USING btree (subject);


--
-- Name: ix_knowledge_points_vector_id; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX ix_knowledge_points_vector_id ON public.knowledge_points USING btree (vector_id);


--
-- Name: ix_messages_conversation_id; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX ix_messages_conversation_id ON public.messages USING btree (conversation_id);


--
-- Name: ix_questions_chapter_id; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX ix_questions_chapter_id ON public.questions USING btree (chapter_id);


--
-- Name: ix_questions_question_id; Type: INDEX; Schema: public; Owner: -
--

CREATE UNIQUE INDEX ix_questions_question_id ON public.questions USING btree (question_id);


--
-- Name: ix_questions_subject; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX ix_questions_subject ON public.questions USING btree (subject);


--
-- Name: ix_user_progress_chapter_id; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX ix_user_progress_chapter_id ON public.user_progress USING btree (chapter_id);


--
-- Name: ix_user_progress_knowledge_point_id; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX ix_user_progress_knowledge_point_id ON public.user_progress USING btree (knowledge_point_id);


--
-- Name: ix_user_progress_subject; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX ix_user_progress_subject ON public.user_progress USING btree (subject);


--
-- Name: ix_user_progress_user_id; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX ix_user_progress_user_id ON public.user_progress USING btree (user_id);


--
-- Name: ix_users_openid; Type: INDEX; Schema: public; Owner: -
--

CREATE UNIQUE INDEX ix_users_openid ON public.users USING btree (openid);


--
-- Name: ix_drugs_sections_gin; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX ix_drugs_sections_gin ON public.drugs USING gin (sections);


--
-- Name: ix_drugs_source_version; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX ix_drugs_source_version ON public.drugs USING btree (source_version);


--
-- Name: answer_records answer_records_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.answer_records
    ADD CONSTRAINT answer_records_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(id);


--
-- Name: messages messages_conversation_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.messages
    ADD CONSTRAINT messages_conversation_id_fkey FOREIGN KEY (conversation_id) REFERENCES public.conversations(conversation_id);


--
-- Name: user_progress user_progress_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.user_progress
    ADD CONSTRAINT user_progress_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(id);


--
-- PostgreSQL database dump complete
--

\unrestrict ESQ1NFAz6xVH1qNjdPPTbrmPbtepjMOr7SzmqGUWW2SDRd1pbhCctBfDStfCiB2