-- 1. 主表
CREATE TABLE inspection_basic_information (
    basic_information_id VARCHAR(64) NOT NULL,
    package_category VARCHAR(50) NOT NULL,
    package_level VARCHAR(20) NOT NULL,
    package_name VARCHAR(200) NOT NULL,
    department VARCHAR(100),
    department_id VARCHAR(64),
    user_id VARCHAR(64),
    discount DECIMAL(10,2) DEFAULT 0.00,
    is_disabled BOOLEAN NOT NULL DEFAULT FALSE,  -- 改为BOOLEAN类型
    show_package_name BOOLEAN NOT NULL DEFAULT TRUE,
    generate_service_fee BOOLEAN NOT NULL DEFAULT TRUE,
    enable_package_price BOOLEAN NOT NULL DEFAULT TRUE,
    package_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    service_fee DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    lis_group VARCHAR(64),
    blood_volume VARCHAR(50),
    remarks VARCHAR(500),
    org_name VARCHAR(200),
    create_by VARCHAR(64),
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_time TIMESTAMP DEFAULT NULL,
    del_flag BOOLEAN NOT NULL DEFAULT FALSE,  -- 改为BOOLEAN类型
    CONSTRAINT inspection_basic_information_pkey PRIMARY KEY (basic_information_id)
);

-- 2. 明细表
CREATE TABLE inspection_package_detail (
    detail_id VARCHAR(64) NOT NULL,
    basic_information_id VARCHAR(64) NOT NULL,
    package_name VARCHAR(200),
    item_name VARCHAR(200) NOT NULL,
    dosage VARCHAR(50),
    route VARCHAR(50),
    frequency VARCHAR(50),
    days INTEGER,
    quantity INTEGER NOT NULL DEFAULT 1,
    unit VARCHAR(20) NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    service_fee DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    total_amount DECIMAL(10,2) NOT NULL,
    origin VARCHAR(100),
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_time TIMESTAMP DEFAULT NULL,
    del_flag BOOLEAN NOT NULL DEFAULT FALSE,  -- 改为BOOLEAN类型
    CONSTRAINT inspection_package_detail_pkey PRIMARY KEY (detail_id),
    CONSTRAINT fk_package_detail_basic_information
        FOREIGN KEY (basic_information_id)
        REFERENCES inspection_basic_information(basic_information_id)
        ON DELETE CASCADE  -- 或 ON DELETE RESTRICT，根据业务逻辑选择
);

-- 3. 创建索引
CREATE INDEX idx_basic_information_category ON inspection_basic_information(package_category);
CREATE INDEX idx_basic_information_level ON inspection_basic_information(package_level);
CREATE INDEX idx_basic_information_disabled ON inspection_basic_information(is_disabled);
CREATE INDEX idx_basic_information_del_flag ON inspection_basic_information(del_flag);
CREATE INDEX idx_detail_basic_information_id ON inspection_package_detail(basic_information_id);
CREATE INDEX idx_detail_del_flag ON inspection_package_detail(del_flag);

-- 4. 添加表注释
COMMENT ON TABLE inspection_basic_information IS '检验套餐基本信息表';
COMMENT ON TABLE inspection_package_detail IS '检验套餐明细表';