知识图谱-Ontology能力

-- ============================================================
-- DDL for database: supabase_db  schema: public
-- Generated via information_schema + pg_catalog queries
-- ============================================================

SET search_path TO public;

-- ─── Extensions ───
CREATE EXTENSION IF NOT EXISTS "age";
CREATE EXTENSION IF NOT EXISTS "pg_cron";
CREATE EXTENSION IF NOT EXISTS "pg_graphql";
CREATE EXTENSION IF NOT EXISTS "pg_net";
CREATE EXTENSION IF NOT EXISTS "pg_stat_statements";
CREATE EXTENSION IF NOT EXISTS "pg_trgm";
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
CREATE EXTENSION IF NOT EXISTS "pgjwt";
CREATE EXTENSION IF NOT EXISTS "supabase_vault";
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "vector";

-- ─── Enum Types ───
CREATE TYPE memory_type AS ENUM ('explicit', 'implicit', 'feedback');
CREATE TYPE order_status AS ENUM ('pending', 'confirmed', 'preparing', 'delivering', 'completed', 'cancelled');

-- ─── Table: addresses ───
CREATE TABLE addresses (
    id uuid DEFAULT uuid_generate_v4() NOT NULL,
    user_id uuid NOT NULL,
    label text,
    address text NOT NULL,
    lat numeric(10,8),
    lng numeric(11,8),
    contact_name text,
    contact_phone text,
    is_default boolean DEFAULT false NOT NULL,
    created_at timestamptz DEFAULT now() NOT NULL
);

-- ─── Table: menus ───
CREATE TABLE menus (
    id uuid DEFAULT uuid_generate_v4() NOT NULL,
    shop_id uuid NOT NULL,
    name text NOT NULL,
    description text,
    price numeric(10,2) NOT NULL,
    category text,
    tags jsonb DEFAULT '[]'::jsonb,
    is_available boolean DEFAULT true NOT NULL,
    image_url text,
    created_at timestamptz DEFAULT now() NOT NULL
);

-- ─── Table: order_feedbacks ───
CREATE TABLE order_feedbacks (
    id uuid DEFAULT uuid_generate_v4() NOT NULL,
    order_id uuid NOT NULL,
    user_id uuid NOT NULL,
    rating integer NOT NULL,
    comment text,
    liked_items jsonb DEFAULT '[]'::jsonb,
    disliked_items jsonb DEFAULT '[]'::jsonb,
    created_at timestamptz DEFAULT now() NOT NULL
);

-- ─── Table: order_items ───
CREATE TABLE order_items (
    id uuid DEFAULT uuid_generate_v4() NOT NULL,
    order_id uuid NOT NULL,
    menu_id uuid NOT NULL,
    quantity integer DEFAULT 1 NOT NULL,
    unit_price numeric(10,2) NOT NULL,
    note text,
    created_at timestamptz DEFAULT now() NOT NULL
);

-- ─── Table: orders ───
CREATE TABLE orders (
    id uuid DEFAULT uuid_generate_v4() NOT NULL,
    user_id uuid NOT NULL,
    shop_id uuid NOT NULL,
    status order_status DEFAULT 'pending'::order_status NOT NULL,
    total_price numeric(10,2) NOT NULL,
    delivery_address jsonb NOT NULL,
    note text,
    created_at timestamptz DEFAULT now() NOT NULL,
    updated_at timestamptz DEFAULT now() NOT NULL
);

-- ─── Table: shops ───
CREATE TABLE shops (
    id uuid DEFAULT uuid_generate_v4() NOT NULL,
    name text NOT NULL,
    description text,
    location text,
    category text,
    is_open boolean DEFAULT true NOT NULL,
    rating numeric(2,1) DEFAULT 5.0,
    phone text,
    business_hours jsonb DEFAULT '{}'::jsonb,
    created_at timestamptz DEFAULT now() NOT NULL
);

-- ─── Table: user_memories ───
CREATE TABLE user_memories (
    id uuid DEFAULT uuid_generate_v4() NOT NULL,
    user_id uuid NOT NULL,
    agent_id text DEFAULT 'default'::text NOT NULL,
    memory_type memory_type NOT NULL,
    memory_key text NOT NULL,
    memory_value jsonb NOT NULL,
    confidence numeric(3,2) DEFAULT 0.50 NOT NULL,
    source text,
    created_at timestamptz DEFAULT now() NOT NULL,
    updated_at timestamptz DEFAULT now() NOT NULL
);

-- ─── Table: user_taste_profiles ───
CREATE TABLE user_taste_profiles (
    id uuid DEFAULT uuid_generate_v4() NOT NULL,
    user_id uuid NOT NULL,
    flavor_tags jsonb DEFAULT '[]'::jsonb,
    allergy_tags jsonb DEFAULT '[]'::jsonb,
    diet_type text DEFAULT 'normal'::text NOT NULL,
    spice_level integer DEFAULT 2 NOT NULL,
    sweetness_level integer DEFAULT 2 NOT NULL,
    updated_at timestamptz DEFAULT now() NOT NULL
);

-- ─── Table: users ───
CREATE TABLE users (
    id uuid DEFAULT uuid_generate_v4() NOT NULL,
    phone text NOT NULL,
    name text,
    email text,
    default_address_id uuid,
    telegram_id text,
    whatsapp_id text,
    created_at timestamptz DEFAULT now() NOT NULL,
    updated_at timestamptz DEFAULT now() NOT NULL
);

-- ─── Primary Keys ───
ALTER TABLE _ontology_changelog ADD PRIMARY KEY (id);
ALTER TABLE addresses ADD PRIMARY KEY (id);
ALTER TABLE menus ADD PRIMARY KEY (id);
ALTER TABLE order_feedbacks ADD PRIMARY KEY (id);
ALTER TABLE order_items ADD PRIMARY KEY (id);
ALTER TABLE orders ADD PRIMARY KEY (id);
ALTER TABLE shops ADD PRIMARY KEY (id);
ALTER TABLE user_memories ADD PRIMARY KEY (id);
ALTER TABLE user_taste_profiles ADD PRIMARY KEY (id);
ALTER TABLE users ADD PRIMARY KEY (id);

-- ─── Unique Constraints ───
ALTER TABLE user_memories ADD CONSTRAINT user_memories_user_id_agent_id_memory_key_key UNIQUE (user_id, agent_id, memory_key);
ALTER TABLE user_taste_profiles ADD CONSTRAINT user_taste_profiles_user_id_key UNIQUE (user_id);
ALTER TABLE users ADD CONSTRAINT users_phone_key UNIQUE (phone);
ALTER TABLE users ADD CONSTRAINT users_telegram_id_key UNIQUE (telegram_id);
ALTER TABLE users ADD CONSTRAINT users_whatsapp_id_key UNIQUE (whatsapp_id);

-- ─── Foreign Keys ───
ALTER TABLE addresses ADD CONSTRAINT addresses_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;
ALTER TABLE menus ADD CONSTRAINT menus_shop_id_fkey FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE CASCADE;
ALTER TABLE order_feedbacks ADD CONSTRAINT order_feedbacks_order_id_fkey FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE;
ALTER TABLE order_feedbacks ADD CONSTRAINT order_feedbacks_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(id);
ALTER TABLE order_items ADD CONSTRAINT order_items_menu_id_fkey FOREIGN KEY (menu_id) REFERENCES menus(id);
ALTER TABLE order_items ADD CONSTRAINT order_items_order_id_fkey FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE;
ALTER TABLE orders ADD CONSTRAINT orders_shop_id_fkey FOREIGN KEY (shop_id) REFERENCES shops(id);
ALTER TABLE orders ADD CONSTRAINT orders_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(id);
ALTER TABLE user_memories ADD CONSTRAINT user_memories_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;
ALTER TABLE user_taste_profiles ADD CONSTRAINT user_taste_profiles_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;
ALTER TABLE users ADD CONSTRAINT fk_users_default_address FOREIGN KEY (default_address_id) REFERENCES addresses(id) ON DELETE SET NULL;

-- ─── Indexes ───
CREATE INDEX idx_addresses_default ON public.addresses USING btree (user_id, is_default) WHERE (is_default = true);
CREATE INDEX idx_addresses_user_id ON public.addresses USING btree (user_id);
CREATE INDEX idx_menus_available ON public.menus USING btree (shop_id, is_available) WHERE (is_available = true);
CREATE INDEX idx_menus_category ON public.menus USING btree (category);
CREATE INDEX idx_menus_name_trgm ON public.menus USING gin (name gin_trgm_ops);
CREATE INDEX idx_menus_shop_id ON public.menus USING btree (shop_id);
CREATE INDEX idx_menus_tags ON public.menus USING gin (tags);
CREATE INDEX idx_feedbacks_order_id ON public.order_feedbacks USING btree (order_id);
CREATE INDEX idx_feedbacks_rating ON public.order_feedbacks USING btree (user_id, rating);
CREATE INDEX idx_feedbacks_user_id ON public.order_feedbacks USING btree (user_id);
CREATE INDEX idx_order_items_menu_id ON public.order_items USING btree (menu_id);
CREATE INDEX idx_order_items_order_id ON public.order_items USING btree (order_id);
CREATE INDEX idx_orders_shop_id ON public.orders USING btree (shop_id);
CREATE INDEX idx_orders_status ON public.orders USING btree (status);
CREATE INDEX idx_orders_user_created ON public.orders USING btree (user_id, created_at DESC);
CREATE INDEX idx_orders_user_id ON public.orders USING btree (user_id);
CREATE INDEX idx_shops_category ON public.shops USING btree (category);
CREATE INDEX idx_shops_is_open ON public.shops USING btree (is_open) WHERE (is_open = true);
CREATE INDEX idx_shops_name_trgm ON public.shops USING gin (name gin_trgm_ops);
CREATE INDEX idx_memories_confidence ON public.user_memories USING btree (user_id, confidence DESC);
CREATE INDEX idx_memories_type ON public.user_memories USING btree (memory_type);
CREATE INDEX idx_memories_updated ON public.user_memories USING btree (updated_at DESC);
CREATE INDEX idx_memories_user_agent ON public.user_memories USING btree (user_id, agent_id);
CREATE INDEX idx_memories_user_id ON public.user_memories USING btree (user_id);
CREATE INDEX idx_taste_user_id ON public.user_taste_profiles USING btree (user_id);
CREATE INDEX idx_users_phone ON public.users USING btree (phone);
CREATE INDEX idx_users_telegram_id ON public.users USING btree (telegram_id) WHERE (telegram_id IS NOT NULL);
CREATE INDEX idx_users_whatsapp_id ON public.users USING btree (whatsapp_id) WHERE (whatsapp_id IS NOT NULL);

-- ─── Check Constraints ───
ALTER TABLE menus ADD CONSTRAINT menus_price_check CHECK ((price >= (0)::numeric));
ALTER TABLE order_feedbacks ADD CONSTRAINT order_feedbacks_rating_check CHECK (((rating >= 1) AND (rating <= 5)));
ALTER TABLE order_items ADD CONSTRAINT order_items_quantity_check CHECK ((quantity > 0));
ALTER TABLE order_items ADD CONSTRAINT order_items_unit_price_check CHECK ((unit_price >= (0)::numeric));
ALTER TABLE orders ADD CONSTRAINT orders_total_price_check CHECK ((total_price >= (0)::numeric));
ALTER TABLE shops ADD CONSTRAINT shops_rating_check CHECK (((rating >= (0)::numeric) AND (rating <= (5)::numeric)));
ALTER TABLE user_memories ADD CONSTRAINT user_memories_confidence_check CHECK (((confidence >= (0)::numeric) AND (confidence <= (1)::numeric)));
ALTER TABLE user_taste_profiles ADD CONSTRAINT user_taste_profiles_spice_level_check CHECK (((spice_level >= 0) AND (spice_level <= 5)));
ALTER TABLE user_taste_profiles ADD CONSTRAINT user_taste_profiles_sweetness_level_check CHECK (((sweetness_level >= 0) AND (sweetness_level <= 5)));