Administrator / spring-boot-microservices-onlineshooping-fullstack · Files

-- V1__Initial_schema.sql
-- 创建 payments 表（根据 Payment.java 实体类）
CREATE TABLE payments (
    id BIGSERIAL PRIMARY KEY,
    payment_id VARCHAR(255) UNIQUE NOT NULL,
    order_id VARCHAR(255) NOT NULL,
    order_number VARCHAR(255),
    user_id BIGINT NOT NULL,
    amount NUMERIC(12,2) NOT NULL,
    currency VARCHAR(10) NOT NULL DEFAULT 'USD',
    status VARCHAR(50) NOT NULL DEFAULT 'PENDING', -- PENDING, PROCESSING, SUCCEEDED, FAILED, CANCELLED, REFUNDED
    payment_method VARCHAR(50) NOT NULL, -- STRIPE, PAYPAL, CREDIT_CARD, BANK_TRANSFER
    payment_gateway VARCHAR(50), -- STRIPE, PAYPAL
    gateway_payment_id VARCHAR(255),
    gateway_customer_id VARCHAR(255),
    description TEXT,
    failure_reason TEXT,
    failure_code VARCHAR(255),
    metadata TEXT,
    
    -- 时间戳字段
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    processed_at TIMESTAMP,
    refunded_at TIMESTAMP,
    
    -- 版本控制
    version INTEGER DEFAULT 0
);

-- 创建 payment_methods 表（根据 PaymentMethod.java 实体类）
CREATE TABLE payment_methods (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL,
    payment_method_id VARCHAR(255) NOT NULL,
    type VARCHAR(50) NOT NULL, -- CARD, PAYPAL, BANK_ACCOUNT
    gateway VARCHAR(50) NOT NULL, -- STRIPE, PAYPAL
    gateway_customer_id VARCHAR(255),
    last_four VARCHAR(4),
    brand VARCHAR(50),
    exp_month INTEGER,
    exp_year INTEGER,
    is_default BOOLEAN DEFAULT false,
    is_active BOOLEAN DEFAULT true,
    metadata TEXT,
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

-- 创建 payment_events 表（支付事件历史）
CREATE TABLE payment_events (
    id BIGSERIAL PRIMARY KEY,
    payment_id BIGINT NOT NULL,
    event_type VARCHAR(100) NOT NULL, -- payment.created, payment.succeeded, payment.failed, refund.created, etc.
    event_data TEXT,
    gateway_event_id VARCHAR(255), -- Event ID from payment gateway
    created_at TIMESTAMP NOT NULL,
    
    FOREIGN KEY (payment_id) REFERENCES payments(id) ON DELETE CASCADE
);

-- 创建 refunds 表（根据 Refund.java 实体类）- 已包含 user_id 字段
CREATE TABLE refunds (
    id BIGSERIAL PRIMARY KEY,
    refund_id VARCHAR(255) UNIQUE NOT NULL,
    payment_id BIGINT NOT NULL,
    user_id BIGINT NOT NULL,
    amount NUMERIC(12,2) NOT NULL,
    currency VARCHAR(10) NOT NULL DEFAULT 'USD',
    status VARCHAR(50) NOT NULL DEFAULT 'PENDING', -- PENDING, PROCESSING, SUCCEEDED, FAILED, CANCELLED
    reason TEXT,
    gateway_refund_id VARCHAR(255),
    gateway_transaction_id VARCHAR(255),
    failure_reason TEXT,
    failure_code VARCHAR(255),
    metadata TEXT,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    processed_at TIMESTAMP,
    refunded_at TIMESTAMP,
    
    FOREIGN KEY (payment_id) REFERENCES payments(id) ON DELETE CASCADE
);

-- ==================== PRODUCT SERVICE TABLES ====================
-- 创建 products 表
CREATE TABLE products (
    id BIGSERIAL PRIMARY KEY,
    product_id VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    sku VARCHAR(100) UNIQUE,
    price NUMERIC(12,2) NOT NULL,
    original_price NUMERIC(12,2),
    currency VARCHAR(10) DEFAULT 'USD',
    category_id BIGINT,
    brand VARCHAR(100),
    weight DECIMAL(8,3),
    dimensions VARCHAR(100),
    color VARCHAR(50),
    size VARCHAR(50),
    material VARCHAR(100),
    tags TEXT[],
    images TEXT[],
    video_url VARCHAR(500),
    specifications JSONB,
    features TEXT[],
    is_active BOOLEAN DEFAULT true,
    is_featured BOOLEAN DEFAULT false,
    is_available BOOLEAN DEFAULT true,
    rating DECIMAL(3,2) DEFAULT 0,
    review_count INTEGER DEFAULT 0,
    view_count INTEGER DEFAULT 0,
    purchase_count INTEGER DEFAULT 0,
    seo_title VARCHAR(255),
    seo_description TEXT,
    seo_keywords TEXT[],
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_by BIGINT,
    updated_by BIGINT,
    version INTEGER DEFAULT 0
);

-- 创建 categories 表
CREATE TABLE categories (
    id BIGSERIAL PRIMARY KEY,
    category_id VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    parent_id BIGINT,
    image_url VARCHAR(500),
    is_active BOOLEAN DEFAULT true,
    sort_order INTEGER DEFAULT 0,
    seo_title VARCHAR(255),
    seo_description TEXT,
    seo_keywords TEXT[],
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE SET NULL
);

-- 创建 inventory 表 - 包含 available_quantity 字段
CREATE TABLE inventory (
    id BIGSERIAL PRIMARY KEY,
    product_id BIGINT NOT NULL,
    sku VARCHAR(100) NOT NULL,
    quantity INTEGER NOT NULL DEFAULT 0,
    available_quantity INTEGER NOT NULL DEFAULT 0,  -- 新增的字段
    reserved_quantity INTEGER DEFAULT 0,
    low_stock_threshold INTEGER DEFAULT 10,
    reorder_point INTEGER DEFAULT 20,
    location VARCHAR(100),
    batch_number VARCHAR(100),
    expiry_date TIMESTAMP,
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    UNIQUE (product_id, sku)
);

-- 创建 product_reviews 表
CREATE TABLE product_reviews (
    id BIGSERIAL PRIMARY KEY,
    product_id BIGINT NOT NULL,
    user_id BIGINT NOT NULL,
    order_id VARCHAR(255),
    rating INTEGER NOT NULL CHECK (rating >= 1 AND rating <= 5),
    title VARCHAR(255),
    comment TEXT,
    images TEXT[],
    is_verified_purchase BOOLEAN DEFAULT false,
    is_approved BOOLEAN DEFAULT true,
    helpful_count INTEGER DEFAULT 0,
    report_count INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    UNIQUE (product_id, user_id, order_id)
);

-- ==================== INDEXES ====================
-- payments 表索引
CREATE INDEX idx_payments_payment_id ON payments(payment_id);
CREATE INDEX idx_payments_order_id ON payments(order_id);
CREATE INDEX idx_payments_order_number ON payments(order_number);
CREATE INDEX idx_payments_status ON payments(status);
CREATE INDEX idx_payments_payment_method ON payments(payment_method);
CREATE INDEX idx_payments_gateway_payment_id ON payments(gateway_payment_id);
CREATE INDEX idx_payments_created_at ON payments(created_at);
CREATE INDEX idx_payments_user_id ON payments(user_id);

-- payment_methods 表索引
CREATE INDEX idx_payment_methods_user_id ON payment_methods(user_id);
CREATE INDEX idx_payment_methods_payment_method_id ON payment_methods(payment_method_id);
CREATE INDEX idx_payment_methods_gateway_customer_id ON payment_methods(gateway_customer_id);
CREATE INDEX idx_payment_methods_is_default ON payment_methods(is_default);
CREATE INDEX idx_payment_methods_is_active ON payment_methods(is_active);

-- payment_events 表索引
CREATE INDEX idx_payment_events_payment_id ON payment_events(payment_id);
CREATE INDEX idx_payment_events_event_type ON payment_events(event_type);
CREATE INDEX idx_payment_events_created_at ON payment_events(created_at);

-- refunds 表索引
CREATE INDEX idx_refunds_refund_id ON refunds(refund_id);
CREATE INDEX idx_refunds_payment_id ON refunds(payment_id);
CREATE INDEX idx_refunds_user_id ON refunds(user_id);
CREATE INDEX idx_refunds_status ON refunds(status);
CREATE INDEX idx_refunds_created_at ON refunds(created_at);
CREATE INDEX idx_refunds_user_id_created_at ON refunds(user_id, created_at);

-- products 表索引
CREATE INDEX idx_products_product_id ON products(product_id);
CREATE INDEX idx_products_sku ON products(sku);
CREATE INDEX idx_products_name ON products(name);
CREATE INDEX idx_products_category_id ON products(category_id);
CREATE INDEX idx_products_price ON products(price);
CREATE INDEX idx_products_is_active ON products(is_active);
CREATE INDEX idx_products_is_featured ON products(is_featured);
CREATE INDEX idx_products_rating ON products(rating);
CREATE INDEX idx_products_created_at ON products(created_at);

-- categories 表索引
CREATE INDEX idx_categories_category_id ON categories(category_id);
CREATE INDEX idx_categories_parent_id ON categories(parent_id);
CREATE INDEX idx_categories_name ON categories(name);
CREATE INDEX idx_categories_is_active ON categories(is_active);
CREATE INDEX idx_categories_sort_order ON categories(sort_order);

-- inventory 表索引
CREATE INDEX idx_inventory_product_id ON inventory(product_id);
CREATE INDEX idx_inventory_sku ON inventory(sku);
CREATE INDEX idx_inventory_quantity ON inventory(quantity);
CREATE INDEX idx_inventory_available_quantity ON inventory(available_quantity);  -- 新增索引
CREATE INDEX idx_inventory_is_active ON inventory(is_active);

-- product_reviews 表索引
CREATE INDEX idx_product_reviews_product_id ON product_reviews(product_id);
CREATE INDEX idx_product_reviews_user_id ON product_reviews(user_id);
CREATE INDEX idx_product_reviews_rating ON product_reviews(rating);
CREATE INDEX idx_product_reviews_is_approved ON product_reviews(is_approved);
CREATE INDEX idx_product_reviews_created_at ON product_reviews(created_at);

-- 插入初始数据（可选）
-- INSERT INTO payments (payment_id, order_id, user_id, amount, currency, status, payment_method) 
-- VALUES ('PAY001', 'ORDER001', 1, 100.00, 'USD', 'SUCCEEDED', 'STRIPE');