import { TestContext } from "../Context";
import { EntityDict } from "../test-app-domain";
import { v4 } from 'uuid';
import assert from 'assert';
import { generateNewIdAsync } from 'oak-domain/lib/utils/uuid';
import { describe, it, before, after } from 'mocha';
import { DbStore } from "../../lib/types/dbStore";

export default (storeGetter: () => DbStore<EntityDict, TestContext>) => {

    it('test insert', async () => {
        const store = storeGetter();
        const context = new TestContext(store);
        await context.begin();
        await store.operate('user', {
            id: v4(),
            action: 'create',
            data: [
                {
                    id: v4(),
                    name: 'xc',
                    nickname: 'xc',
                    idState: 'unverified',
                    userState: 'normal'
                },
                {
                    id: v4(),
                    name: 'zz',
                    nickname: 'zzz',
                    idState: 'unverified',
                    userState: 'normal'
                }
            ]
        }, context, {});
        await context.commit();
    });

    it('test cascade insert', async () => {
        const store = storeGetter();
        const context = new TestContext(store);
        await store.operate('user', {
            id: v4(),
            action: 'create',
            data: {
                id: v4(),
                name: 'xxxc',
                nickname: 'ddd',
                idState: 'unverified',
                userState: 'normal',
                token$player: [{
                    id: v4(),
                    action: 'create',
                    data: {
                        id: v4(),
                        env: {
                            type: 'web',
                        },
                        applicationId: v4(),
                        userId: v4(),
                        refreshedAt: Date.now(),
                        value: v4(),
                    }
                }]
            } as EntityDict['user']['CreateSingle']['data']
        }, context, {});
    });

    it('test update', async () => {
        const store = storeGetter();
        const context = new TestContext(store);
        const tokenId = v4();
        await context.begin();
        await store.operate('user', {
            id: v4(),
            action: 'create',
            data: {
                id: v4(),
                name: 'xxxc',
                nickname: 'ddd',
                idState: 'unverified', userState: 'normal',
                token$player: [{
                    id: v4(),
                    action: 'create',
                    data: {
                        id: tokenId,
                        env: {
                            type: 'web',
                        },
                        applicationId: v4(),
                        userId: v4(),
                        refreshedAt: Date.now(),
                        value: v4(),
                    }
                }]
            }
        } as EntityDict['user']['CreateSingle'], context, {});
        await store.operate('token', {
            id: v4(),
            action: 'update',
            filter: {
                id: tokenId,
            },
            data: {
                player: {
                    id: v4(),
                    action: 'activate',
                    data: {
                        name: 'xcxcxc0903'
                    },
                }
            }
        }, context, {});
        await context.commit();
    });

    it('test cascade update', async () => {
        const store = storeGetter();
        const context = new TestContext(store);
        const userId = v4();
        const tokenId1 = v4();
        const tokenId2 = v4();
        await context.begin();
        await context.operate('user', {
            id: v4(),
            action: 'create',
            data: {
                id: userId,
                name: 'xxxc',
                nickname: 'ddd', idState: 'unverified', userState: 'normal',
                token$player: [{
                    id: v4(),
                    action: 'create',
                    data: {
                        id: tokenId1,
                        env: {
                            type: 'web',
                        },
                        applicationId: v4(),
                        userId: v4(),
                        refreshedAt: Date.now(),
                        value: v4(),
                    }
                }]
            }
        } as EntityDict['user']['CreateSingle'], {});

        await context.operate('token', {
            id: v4(),
            action: 'create',
            data: {
                id: tokenId2,
                env: {
                    type: 'web',
                },
                applicationId: v4(),
                userId: v4(),
                playerId: v4(),
                refreshedAt: Date.now(),
                value: v4(),
                entity: 'email',
            } as EntityDict['token']['CreateSingle']['data'],
        }, {});

        await context.commit();

        // cascade update token of userId
        await context.operate('user', {
            id: v4(),
            action: 'update',
            data: {
                name: 'xc', idState: 'unverified', userState: 'normal',
                token$player: [{
                    id: v4(),
                    action: 'update',
                    data: {
                        entity: 'email',
                    }
                }]
            },
            filter: {
                id: userId,
            },
        }, {});

        const [row] = await context.select('token', {
            data: {
                id: 1,
                entity: 1,
                entityId: 1,
                env: {
                    type: 1,
                }
            },
            filter: {
                id: tokenId2,
            }
        }, {});

        assert(row.env!.type === 'web' && row.entity === 'email', `Cascade update failed`);
    });

    it('test delete', async () => {
        const store = storeGetter();
        const context = new TestContext(store);
        const tokenId = v4();
        await context.begin();
        await store.operate('user', {
            id: v4(),
            action: 'create',
            data: {
                id: v4(),
                name: 'xxxc',
                nickname: 'ddd', idState: 'unverified', userState: 'normal',
                token$player: [{
                    id: v4(),
                    action: 'create',
                    data: {
                        id: tokenId,
                        env: {
                            type: 'server',
                        },
                        applicationId: v4(),
                        userId: v4(),
                        refreshedAt: Date.now(),
                        value: v4(),
                    }
                }]
            }
        }, context, {});
        await store.operate('token', {
            id: v4(),
            action: 'remove',
            filter: {
                id: tokenId,
            },
            data: {
                player: {
                    id: v4(),
                    action: 'update',
                    data: {
                        name: 'xcxcxc0902'
                    },
                }
            }
        }, context, {});
        await context.commit();
    });

    it('test delete2', async () => {
        const store = storeGetter();
        // 这个例子暂在mysql上过不去，先放着吧
        const context = new TestContext(store);
        const tokenId = v4();
        await context.begin();
        await store.operate('user', {
            id: v4(),
            action: 'create',
            data: {
                id: v4(),
                name: 'xxxc',
                nickname: 'ddd', idState: 'unverified', userState: 'normal',
                token$player: [{
                    id: v4(),
                    action: 'create',
                    data: {
                        id: tokenId,
                        env: {
                            type: 'server',
                        },
                        applicationId: v4(),
                        userId: v4(),
                        refreshedAt: Date.now(),
                        value: v4(),
                    }
                }]
            }
        }, context, {});
        await store.operate('user', {
            id: v4(),
            action: 'remove',
            filter: {
                id: tokenId,
            },
            data: {
                ref: {
                    id: await generateNewIdAsync(),
                    action: 'remove',
                    data: {},
                }
            },
        }, context, {});
        await context.commit();
    });

    it('test decimal', async () => {
        const store = storeGetter();
        const id = v4();
        const context = new TestContext(store);
        await context.begin();
        await store.operate('house', {
            id: v4(),
            action: 'create',
            data: [
                {
                    id,
                    areaId: 'xc',
                    ownerId: 'xc',
                    district: '杭州',
                    size: 77.5,
                },
            ]
        }, context, {});
        await context.commit();

        const [house] = await store.select('house', {
            data: {
                id: 1,
                size: 1,
            },
            filter: {
                id,
            },
        }, context, {});
        assert(typeof house.size === 'number');
    });


    /**
     * Sub Query Predicate (#sqp) 语义说明：
     * 
     * 基于"空集的全称量化"（vacuous truth）原则：
     * 
     * 1. '#sqp': 'in' 或 无 #sqp
     *    - 含义：存在至少一个关联记录满足条件
     *    - 对于无关联记录的情况：返回 false（不匹配）
     * 
     * 2. '#sqp': 'not in'
     *    - 含义：不存在任何关联记录满足条件
     *    - 对于无关联记录的情况：返回 true（匹配）
     * 
     * 3. '#sqp': 'all'
     *    - 含义：所有关联记录都满足条件
     *    - 对于无关联记录的情况：返回 true（空集的全称量化为真）
     * 
     * 4. '#sqp': 'not all'
     *    - 含义：不是所有关联记录都满足条件（存在至少一个不满足）
     *    - 对于无关联记录的情况：返回 false（空集中不存在不满足的记录）
     * 
     * 示例：
     * - system 有 2 个 application，都满足条件 → 'all': true, 'not all': false
     * - system 有 2 个 application，1 个满足条件 → 'all': false, 'not all': true
     * - system 没有 application → 'all': true, 'not all': false
     */
    it('[1.13]sub query predicate', async () => {
        const store = storeGetter();
        const context = new TestContext(store);
        await context.begin();
        const id1 = await generateNewIdAsync();
        const id2 = await generateNewIdAsync();
        const id3 = await generateNewIdAsync();
        const systems: EntityDict['system']['CreateSingle']['data'][] = [
            {
                id: id1,
                name: 'test1.13-1',
                description: 'aaaaa',
                folder: '/test2',
                application$system: [{
                    id: v4(),
                    action: 'create',
                    data: {
                        id: v4(),
                        name: 'test1.13-1-1',
                        description: 'ttttt',
                        type: 'web',
                    }
                },
                {
                    id: v4(),
                    action: 'create',
                    data: {
                        id: v4(),
                        name: 'test1.13-1-2',
                        description: 'ttttt2',
                        type: 'wechatMp',
                    }
                }]
            },
            {
                id: id2,
                name: 'test1.13-2',
                description: 'aaaaa',
                folder: '/test2',
                application$system: [{
                    id: v4(),
                    action: 'create',
                    data: {
                        id: v4(),
                        name: 'test1.13-2-1',
                        description: 'ttttt',
                        type: 'web',
                    }
                },
                {
                    id: v4(),
                    action: 'create',
                    data: {
                        id: v4(),
                        name: 'test1.13-2-2',
                        description: 'ttttt2',
                        type: 'wechatMp',
                    }
                }]
            },
            {

                id: id3,
                name: 'test1.13-3',
                description: 'aaaaa',
                folder: '/test2',
            }
        ];

        await context.operate('system', {
            id: v4(),
            action: 'create',
            data: systems,
        }, {});

        await context.commit();

        const r1 = await context.select('system', {
            data: {
                id: 1,
            },
            filter: {
                id: id1,
                application$system: {
                },
                folder: '/test2',
            }
        }, {});
        assert(r1.length === 1, 'Expected 1 result for r1');

        const r2 = await context.select('system', {
            data: {
                id: 1,
            },
            filter: {
                id: id1,
                application$system: {
                    '#sqp': 'not in',
                },
                folder: '/test2',
            },
        }, {});
        assert(r2.length === 0, 'Expected 0 results for r2');

        const r22 = await context.select('system', {
            data: {
                id: 1,
            },
            filter: {
                id: id1,
                application$system: {
                    name: {
                        $startsWith: 'test1.13-2',
                    },
                    '#sqp': 'not in',
                },
                folder: '/test2',
            },
        }, {});
        assert(r22.length === 1, 'Expected 1 result for r22');

        const r23 = await context.select('system', {
            data: {
                id: 1,
            },
            filter: {
                id: id1,
                application$system: {
                    name: {
                        $startsWith: 'test1.13-2',
                    },
                    '#sqp': 'all',
                },
                folder: '/test2',
            },
        }, {});
        assert(r23.length === 0, 'Expected 0 results for r23');

        const r24 = await context.select('system', {
            data: {
                id: 1,
            },
            filter: {
                id: id1,
                application$system: {
                    name: {
                        $startsWith: 'test1.13-2',
                    },
                    '#sqp': 'not all',
                },
                folder: '/test2',
            },
        }, {});
        assert(r24.length === 1, 'Expected 1 result for r24');

        const r3 = await context.select('system', {
            data: {
                id: 1,
            },
            filter: {
                id: id1,
                application$system: {
                    '#sqp': 'all',
                },
                folder: '/test2',
            },
        }, {});
        assert(r3.length === 1, 'Expected 1 result for r3'); // #sqp: 'all' 无其他条件时，生成 not exists (... and false)，永远为 true

        const r4 = await context.select('system', {
            data: {
                id: 1,
            },
            filter: {
                id: id1,
                application$system: {
                    '#sqp': 'not all',
                },
                folder: '/test2',
            },
        }, {});
        assert(r4.length === 0, 'Expected 0 results for r4'); // #sqp: 'not all' 无其他条件时，生成 exists (... and false)，永远为 false

        const r5 = await context.select('system', {
            data: {
                id: 1,
            },
            filter: {
                id: {
                    $in: [id1, id2, id3],
                },
                application$system: {
                },
                folder: '/test2',
            },
        }, {});
        assert(r5.length === 2, 'Expected 2 results for r5');
        assert(r5.map(ele => ele.id).includes(id1), 'Expected r5 to include id1');
        assert(r5.map(ele => ele.id).includes(id2), 'Expected r5 to include id2');

        const r6 = await context.select('system', {
            data: {
                id: 1,
            },
            filter: {
                id: {
                    $in: [id1, id2, id3],
                },
                application$system: {
                    '#sqp': 'not in',
                },
                folder: '/test2',
            },
        }, {});
        assert(r6.length === 1, 'Expected 1 result for r6');
        assert(r6.map(ele => ele.id).includes(id3), 'Expected r6 to include id3');

        const r7 = await context.select('system', {
            data: {
                id: 1,
            },
            filter: {
                id: {
                    $in: [id1, id2, id3],
                },
                application$system: {
                    '#sqp': 'all',
                },
                folder: '/test2',
            },
        }, {});
        assert(r7.length === 3, 'Expected 3 results for r7');  // #sqp: 'all' 无其他条件时，永远为 true
        assert(r7.map(ele => ele.id).includes(id1), 'Expected r7 to include id1');
        assert(r7.map(ele => ele.id).includes(id2), 'Expected r7 to include id2');
        assert(r7.map(ele => ele.id).includes(id3), 'Expected r7 to include id3');

        const r8 = await context.select('system', {
            data: {
                id: 1,
            },
            filter: {
                id: {
                    $in: [id1, id2, id3],
                },
                application$system: {
                    '#sqp': 'not all',
                },
                folder: '/test2',
            },
        }, {});
        // r8: 'not all' 应该返回 0 条（因为所有有 application 的都满足 true）
        assert(r8.length === 0, 'Expected 0 results for r8');
    });

    it('[1.1]子查询', async () => {
        const store = storeGetter();
        const context = new TestContext(store);
        await context.begin();
        await store.operate('user', {
            id: v4(),
            action: 'create',
            data: {
                id: v4(),
                name: 'xc',
                nickname: 'xc', idState: 'unverified', userState: 'normal',
            }
        }, context, {});

        process.env.NODE_ENV = 'development';
        const rows = await store.select('user', {
            data: {
                id: 1,
                name: 1,
                nickname: 1,
            },
            filter: {
                token$user: {
                }
            },
        }, context, {});
        process.env.NODE_ENV = undefined;
        // console.log(rows);
        assert(rows.length === 0);
        await context.commit();
    });

    it('[1.2]行内属性上的表达式', async () => {
        const store = storeGetter();
        const context = new TestContext(store);
        await context.begin();
        const id = v4();
        await store.operate('user', {
            id: v4(),
            action: 'create',
            data: {
                id,
                name: 'xc',
                nickname: 'xc', idState: 'unverified', userState: 'normal',
            }
        }, context, {});

        const id2 = v4();
        await store.operate('user', {
            id: v4(),
            action: 'create',
            data: {
                id: id2,
                name: 'xzw',
                nickname: 'xzw22', idState: 'unverified', userState: 'normal',
            }
        }, context, {});

        process.env.NODE_ENV = 'development';
        const users = await store.select('user', {
            data: {
                id: 1,
                name: 1,
                nickname: 1,
            },
            filter: {
                id: {
                    $in: [id, id2],
                },
                $expr: {
                    $eq: [{
                        '#attr': 'name',
                    }, {
                        "#attr": 'nickname',
                    }]
                },
            },
        }, context, {});
        const users2 = await store.select('user', {
            data: {
                id: 1,
                name: 1,
                nickname: 1,
            },
            filter: {
                id: {
                    $in: [id, id2],
                },
                $expr: {
                    $eq: [{
                        $mod: [
                            {
                                '#attr': '$$seq$$',
                            },
                            2,
                        ],
                    }, 0]
                },
            },
        }, context, {});
        process.env.NODE_ENV = undefined;

        assert(users.length === 1);
        assert(users2.length === 1);
        await context.commit();
    });

    it('[1.3]跨filter结点的表达式', async () => {
        const store = storeGetter();
        const context = new TestContext(store);

        const id1 = v4();
        const id2 = v4();
        await context.begin();
        await store.operate('application', {
            id: v4(),
            action: 'create',
            data: [{
                id: id1,
                name: 'test',
                description: 'ttttt',
                type: 'web',
                system: {
                    id: v4(),
                    action: 'create',
                    data: {
                        id: 'bbb',
                        name: 'systest',
                        description: 'aaaaa',
                        folder: '/systest',
                    } as EntityDict['system']['CreateSingle']['data']
                },
            }, {
                id: id2,
                name: 'test2',
                description: 'ttttt2',
                type: 'web',
                system: {
                    id: v4(),
                    action: 'create',
                    data: {
                        id: 'ccc',
                        name: 'test2',
                        description: 'aaaaa2',
                        folder: '/test2',
                    }
                },
            }]
        }, context, {});

        const applications = await store.select('application', {
            data: {
                id: 1,
                name: 1,
                systemId: 1,
                system: {
                    id: 1,
                    name: 1,
                }
            },
            filter: {
                $expr: {
                    $startsWith: [
                        {
                            "#refAttr": 'name',
                            "#refId": 'node-1',
                        },
                        {
                            "#attr": 'name',
                        }
                    ]
                },
                system: {
                    "#id": 'node-1',
                },
                id: id2,
            },
            sorter: [
                {
                    $attr: {
                        system: {
                            name: 1,
                        }
                    },
                    $direction: 'asc',
                }
            ]
        }, context, {});
        console.log(applications);
        assert(applications.length === 1 && applications[0].id === id2);
        await context.commit();
    });


    it('[1.4]跨filter子查询的表达式', async () => {
        const store = storeGetter();
        const context = new TestContext(store);
        await context.begin();

        await store.operate('application', {
            id: v4(),
            action: 'create',
            data: [{
                id: 'aaaa',
                name: 'test',
                description: 'ttttt',
                type: 'web',
                system: {
                    id: v4(),
                    action: 'create',
                    data: {
                        id: 'bbbb',
                        name: 'systest',
                        description: 'aaaaa',
                        folder: '/systest',
                    }
                },
            }, {
                id: 'aaaa2',
                name: 'test2',
                description: 'ttttt2',
                type: 'web',
                system: {
                    id: v4(),
                    action: 'create',
                    data: {
                        id: 'cccc',
                        name: 'test2',
                        description: 'aaaaa2',
                        folder: '/test2',
                    }
                },
            }]
        }, context, {});

        process.env.NODE_ENV = 'development';
        let systems = await store.select('system', {
            data: {
                id: 1,
                name: 1,
            },
            filter: {
                "#id": 'node-1',
                $and: [
                    {
                        application$system: {
                            '#sqp': 'not in',
                            $expr: {
                                $eq: [
                                    {
                                        "#attr": 'name',
                                    },
                                    {
                                        '#refId': 'node-1',
                                        "#refAttr": 'name',
                                    }
                                ]
                            },
                            '#id': 'node-2',
                        }
                    }, {
                        id: {
                            $in: ['bbbb', 'cccc'],
                        }
                    }
                ]
            },
            sorter: [
                {
                    $attr: {
                        name: 1,
                    },
                    $direction: 'asc',
                }
            ]
        }, context, {});
        assert(systems.length === 1 && systems[0].id === 'bbbb');
        systems = await store.select('system', {
            data: {
                id: 1,
                name: 1,
            },
            filter: {
                "#id": 'node-1',
                $and: [
                    {
                        application$system: {
                            $expr: {
                                $eq: [
                                    {
                                        "#attr": 'name',
                                    },
                                    {
                                        '#refId': 'node-1',
                                        "#refAttr": 'name',
                                    }
                                ]
                            },
                        },
                    },
                    {
                        id: {
                            $in: ['bbbb', 'cccc'],
                        },
                    }
                ]
            },
            sorter: [
                {
                    $attr: {
                        name: 1,
                    },
                    $direction: 'asc',
                }
            ]
        }, context, {});
        process.env.NODE_ENV = undefined;
        assert(systems.length === 1 && systems[0].id === 'cccc');
        await context.commit();
    });

    // TODO: 这种情况暂不支持 by Xc
    it('[1.5]projection中的跨结点表达式', async () => {
        const store = storeGetter();
        const context = new TestContext(store);
        await context.begin();

        await store.operate('application', {
            id: v4(),
            action: 'create',
            data: [{
                id: 'aaa5',
                name: 'test',
                description: 'ttttt',
                type: 'web',
                system: {
                    id: v4(),
                    action: 'create',
                    data: {
                        id: 'bbb5',
                        name: 'systest',
                        description: 'aaaaa',
                        folder: '/systest',
                    }
                },
            }, {
                id: 'aaa5-2',
                name: 'test2',
                description: 'ttttt2',
                type: 'web',
                system: {
                    id: v4(),
                    action: 'create',
                    data: {
                        id: 'ccc5',
                        name: 'test2',
                        description: 'aaaaa2',
                        folder: '/test2',
                    } as EntityDict['system']['CreateSingle']['data'],
                },
            }]
        }, context, {});

        let applications = await store.select('application', {
            data: {
                "#id": 'node-1',
                id: 1,
                name: 1,
                system: {
                    id: 1,
                    name: 1,
                    $expr: {
                        $eq: [
                            {
                                "#attr": 'name',
                            },
                            {
                                '#refId': 'node-1',
                                "#refAttr": 'name',
                            }
                        ]
                    },
                }
            },
            filter: {
                id: {
                    $in: ['aaa5', 'aaa5-2'],
                },
            },
        }, context, {});
        // console.log(applications);
        assert(applications.length === 2);
        applications.forEach(
            (app) => {
                assert(app.id === 'aaa5' && !(app.system!.$expr)
                    || app.id === 'aaa5-2' && !!(app.system!.$expr));
            }
        );

        const applications2 = await store.select('application', {
            data: {
                $expr: {
                    $eq: [
                        {
                            "#attr": 'name',
                        },
                        {
                            '#refId': 'node-1',
                            "#refAttr": 'name',
                        }
                    ]
                },
                id: 1,
                name: 1,
                system: {
                    "#id": 'node-1',
                    id: 1,
                    name: 1,
                }
            },
            filter: {
                id: {
                    $in: ['aaa5', 'aaa5-2'],
                },
            },
        }, context, {});
        // console.log(applications2);
        // assert(applications.length === 2);
        applications2.forEach(
            (app) => {
                assert(app.id === 'aaa5' && !(app.$expr)
                    || app.id === 'aaa5-2' && !!(app.$expr));
            }
        );
        await context.commit();
    });

    // 这个貌似目前支持不了 by Xc
    it('[1.6]projection中的一对多跨结点表达式', async () => {
        const store = storeGetter();
        const context = new TestContext(store);
        await context.begin();

        await store.operate('system', {
            id: v4(),
            action: 'create',
            data: {
                id: 'bbb6',
                name: 'test2',
                description: 'aaaaa',
                folder: '/test2',
                application$system: [{
                    id: v4(),
                    action: 'create',
                    data: {
                        id: 'aaa6',
                        name: 'test',
                        description: 'ttttt',
                        type: 'web',
                    }
                },
                {
                    id: v4(),
                    action: 'create',
                    data: {
                        id: 'aaa6-2',
                        name: 'test2',
                        description: 'ttttt2',
                        type: 'wechatMp',
                    }
                }]
            }
        } as EntityDict['system']['CreateSingle'], context, {});

        // TODO: 下面这个查询会导致崩溃，暂时还不知道为什么
        // const systems = await store.select('system', {
        //     data: {
        //         "#id": 'node-1',
        //         id: 1,
        //         name: 1,
        //         application$system: {
        //             $entity: 'application',
        //             data: {
        //                 id: 1,
        //                 name: 1,
        //                 $expr: {
        //                     $eq: [
        //                         {
        //                             "#attr": 'name',
        //                         },
        //                         {
        //                             '#refId': 'node-1',
        //                             "#refAttr": 'name',
        //                         }
        //                     ]
        //                 },
        //                 $expr2: {
        //                     '#refId': 'node-1',
        //                     "#refAttr": 'id',
        //                 }
        //             }
        //         },
        //     },
        // }, context, {});
        // // console.log(systems);
        // assert(systems.length === 1);
        // const [system] = systems;
        // const { application$system: applications } = system;
        // assert(applications!.length === 2);
        // applications!.forEach(
        //     (ele) => {
        //         assert(ele.id === 'aaa' && ele.$expr === false && ele.$expr2 === 'bbb'
        //             || ele.id === 'aaa2' && ele.$expr === true && ele.$expr2 === 'bbb');
        //     }
        // );
    });

    it('[1.7]事务性测试', async () => {
        const store = storeGetter();
        const context = new TestContext(store);

        await context.begin();
        const systemId = v4();
        await store.operate('system', {
            id: v4(),
            action: 'create',
            data: {
                id: systemId,
                name: 'test2',
                description: 'aaaaa',
                folder: '/test2',
                application$system: [{
                    id: v4(),
                    action: 'create',
                    data: {
                        id: 'aaa7',
                        name: 'test',
                        description: 'ttttt',
                        type: 'web',
                    }
                }, {
                    id: v4(),
                    action: 'create',
                    data: {
                        id: 'aaa7-2',
                        name: 'test2',
                        description: 'ttttt2',
                        type: 'wechatMp',
                    }
                }]
            }
        }, context, {});
        await context.commit();

        await context.begin();
        const systems = await store.select('system', {
            data: {
                id: 1,
                name: 1,
                application$system: {
                    $entity: 'application',
                    data: {
                        id: 1,
                        name: 1,
                        systemId: 1,
                    }
                },
            },
            filter: {
                id: systemId,
            },
        }, context, {});
        assert(systems.length === 1 && systems[0].application$system!.length === 2);

        await store.operate('application', {
            id: v4(),
            action: 'remove',
            data: {},
            filter: {
                id: 'aaa7',
            }
        }, context, {});

        const systems2 = await store.select('system', {
            data: {
                id: 1,
                name: 1,
                application$system: {
                    $entity: 'application',
                    data: {
                        id: 1,
                        name: 1,
                        systemId: 1,
                    }
                },
            },
            filter: {
                id: systemId,
            },
        }, context, {});
        assert(systems2.length === 1 && systems2[0].application$system!.length === 1);
        await context.rollback();

        const systems3 = await store.select('system', {
            data: {
                id: 1,
                name: 1,
                application$system: {
                    $entity: 'application',
                    data: {
                        id: 1,
                        name: 1,
                        systemId: 1,
                    }
                },
            },
            filter: {
                id: systemId,
            },
        }, context, {});
        assert(systems3.length === 1 && systems3[0].application$system!.length === 2);
    });

    it('[1.8]aggregation', async () => {
        const store = storeGetter();
        const context = new TestContext(store);
        await context.begin();

        const systemId1 = v4();
        const systemId2 = v4();
        await store.operate('system', {
            id: v4(),
            action: 'create',
            data: [
                {
                    id: systemId1,
                    name: 'test2',
                    description: 'aaaaa',
                    folder: '/test2',
                    application$system: [{
                        id: v4(),
                        action: 'create',
                        data: {
                            id: v4(),
                            name: 'test',
                            description: 'ttttt',
                            type: 'web',
                        }
                    },
                    {
                        id: v4(),
                        action: 'create',
                        data: {
                            id: v4(),
                            name: 'test2',
                            description: 'ttttt2',
                            type: 'wechatMp',
                        }
                    }]
                },
                {
                    id: systemId2,
                    name: 'test2',
                    description: 'aaaaa',
                    folder: '/test2',
                    application$system: [{
                        id: v4(),
                        action: 'create',
                        data: {
                            id: v4(),
                            name: 'test',
                            description: 'ttttt',
                            type: 'web',
                        }
                    },
                    {
                        id: v4(),
                        action: 'create',
                        data: {
                            id: v4(),
                            name: 'test2',
                            description: 'ttttt2',
                            type: 'wechatMp',
                        }
                    }]
                }
            ]
        } as EntityDict['system']['CreateMulti'], context, {});
        await context.commit();

        await context.begin();
        const result = await store.aggregate('application', {
            data: {
                '#aggr': {
                    system: {
                        id: 1,
                    },
                },
                '#count-1': {
                    id: 1,
                }
            },
            filter: {
                systemId: {
                    $in: [systemId1, systemId2],
                },
            },
        }, context, {});
        await context.commit();
        // console.log(result);
        assert(result.length === 2);
        result.forEach(
            (row) => assert(row['#count-1'] === 2)
        );
    });

    it('[1.9]test + aggregation', async () => {
        const store = storeGetter();
        const context = new TestContext(store);
        await context.begin();

        const systemId1 = v4();
        const systemId2 = v4();
        await store.operate('system', {
            id: v4(),
            action: 'create',
            data: [
                {
                    id: systemId1,
                    name: 'test2',
                    description: 'aaaaa',
                    folder: '/test2',
                    application$system: [{
                        id: v4(),
                        action: 'create',
                        data: {
                            id: v4(),
                            name: 'test',
                            description: 'ttttt',
                            type: 'web',
                        }
                    },
                    {
                        id: v4(),
                        action: 'create',
                        data: {
                            id: v4(),
                            name: 'test2',
                            description: 'ttttt2',
                            type: 'wechatMp',
                        }
                    }]
                },
                {
                    id: systemId2,
                    name: 'test2',
                    description: 'aaaaa',
                    folder: '/test2',
                    application$system: [{
                        id: v4(),
                        action: 'create',
                        data: {
                            id: v4(),
                            name: 'test',
                            description: 'ttttt',
                            type: 'web',
                        }
                    },
                    {
                        id: v4(),
                        action: 'create',
                        data: {
                            id: v4(),
                            name: 'test2',
                            description: 'ttttt2',
                            type: 'wechatMp',
                        }
                    }]
                }
            ]
        } as EntityDict['system']['CreateMulti'], context, {});
        await context.commit();

        await context.begin();
        const result = await store.select('system', {
            data: {
                id: 1,
                name: 1,
                application$system$$aggr: {
                    $entity: 'application',
                    data: {
                        '#aggr': {
                            systemId: 1,
                        },
                        '#count-1': {
                            id: 1,
                        }
                    },
                },
            },
            filter: {
                id: {
                    $in: [systemId1, systemId2],
                },
            },
        }, context, {});
        await context.commit();
        // console.log(result);
        assert(result.length === 2);
        result.forEach(
            (row) => assert(row.application$system$$aggr?.length === 1 && row.application$system$$aggr[0]['#count-1'] === 2)
        );
    });

    // ==================== 排序中使用表达式 ====================

    it('[13.1]sorter with expression', async () => {
        const store = storeGetter();
        const context = new TestContext(store);
        await context.begin();

        const id1 = v4();
        const id2 = v4();
        const id3 = v4();

        await store.operate('house', {
            id: v4(),
            action: 'create',
            data: [
                { id: id1, areaId: v4(), ownerId: v4(), district: '杭州', size: 150.0 },
                { id: id2, areaId: v4(), ownerId: v4(), district: '上海', size: 100.0 },
                { id: id3, areaId: v4(), ownerId: v4(), district: '北京', size: 200.0 },
            ]
        }, context, {});
        await context.commit();

        // 按 size 降序排列
        const r1 = await context.select('house', {
            data: { id: 1, size: 1 },
            filter: { id: { $in: [id1, id2, id3] } },
            sorter: [
                { $attr: { size: 1 }, $direction: 'desc' }
            ]
        }, {});

        assert(r1.length === 3, `Sorter failed`);
        assert(r1[0].id === id3, `Expected first to be id3 (size 200)`);
        assert(r1[1].id === id1, `Expected second to be id1 (size 150)`);
        assert(r1[2].id === id2, `Expected third to be id2 (size 100)`);
    });

    // ==================== 分页测试 ====================

    it('[14.1]pagination with indexFrom and count', async () => {
        const store = storeGetter();
        const context = new TestContext(store);
        await context.begin();

        const ids: string[] = [];
        const data = [] as any[];
        for (let i = 0; i < 10; i++) {
            const id = v4();
            ids.push(id);
            data.push({
                id,
                areaId: v4(),
                ownerId: v4(),
                district: `district_${i}`,
                size: (i + 1) * 10.0,
            });
        }

        await store.operate('house', {
            id: v4(),
            action: 'create',
            data
        }, context, {});
        await context.commit();

        // 获取第 3-5 条记录 (indexFrom=2, count=3)
        const r1 = await context.select('house', {
            data: { id: 1, size: 1 },
            filter: { id: { $in: ids } },
            sorter: [{ $attr: { size: 1 }, $direction: 'asc' }],
            indexFrom: 2,
            count: 3,
        }, {});

        assert(r1.length === 3, `Pagination failed, expected 3, got ${r1.length}`);
        assert(r1[0].size === 30, `Expected first size 30, got ${r1[0].size}`);
        assert(r1[1].size === 40, `Expected second size 40, got ${r1[1].size}`);
        assert(r1[2].size === 50, `Expected third size 50, got ${r1[2].size}`);

        // 获取最后 2 条
        const r2 = await context.select('house', {
            data: { id: 1, size: 1 },
            filter: { id: { $in: ids } },
            sorter: [{ $attr: { size: 1 }, $direction: 'asc' }],
            indexFrom: 8,
            count: 5,  // 超出范围
        }, {});

        assert(r2.length === 2, `Pagination overflow failed, expected 2, got ${r2.length}`);
    });

    // ==================== FOR UPDATE 锁测试 ====================

    it('[15.1]select for update', async () => {
        const store = storeGetter();
        const context = new TestContext(store);
        await context.begin();

        const id1 = v4();
        await store.operate('house', {
            id: v4(),
            action: 'create',
            data: {
                id: id1,
                areaId: v4(),
                ownerId: v4(),
                district: '杭州',
                size: 100.0,
            }
        }, context, {});
        await context.commit();

        await context.begin();
        // FOR UPDATE 查询
        const r1 = await store.select('house', {
            data: { id: 1, size: 1 },
            filter: { id: id1 }
        }, context, { forUpdate: true });

        assert(r1.length === 1, `FOR UPDATE select failed`);
        await context.commit();
    });

    // ==================== includedDeleted 选项测试 ====================

    it('[16.1]select with includedDeleted option', async () => {
        const store = storeGetter();
        const context = new TestContext(store);
        await context.begin();

        const id1 = v4();
        await store.operate('house', {
            id: v4(),
            action: 'create',
            data: {
                id: id1,
                areaId: v4(),
                ownerId: v4(),
                district: '杭州',
                size: 100.0,
            }
        }, context, {});
        await context.commit();

        // 删除记录
        await context.begin();
        await store.operate('house', {
            id: v4(),
            action: 'remove',
            data: {},
            filter: { id: id1 }
        }, context, {});
        await context.commit();

        // 正常查询应该查不到
        const r1 = await context.select('house', {
            data: { id: 1 },
            filter: { id: id1 }
        }, {});
        assert(r1.length === 0, `Deleted record should not be visible`);

        // 使用 includedDeleted 应该能查到
        const r2 = await store.select('house', {
            data: { id: 1 },
            filter: { id: id1 }
        }, context, { includedDeleted: true });
        assert(r2.length === 1, `includedDeleted should show deleted record`);
    });

    // ==================== 物理删除测试 ====================

    it('[16.2]delete physically', async () => {
        const store = storeGetter();
        const context = new TestContext(store);
        await context.begin();

        const id1 = v4();
        await store.operate('house', {
            id: v4(),
            action: 'create',
            data: {
                id: id1,
                areaId: v4(),
                ownerId: v4(),
                district: '杭州',
                size: 100.0,
            }
        }, context, {});
        await context.commit();

        // TODO: 暂不支持物理删除测试
        // 物理删除
        await context.begin();
        await store.operate('house', {
            id: v4(),
            action: 'remove',
            data: {},
            filter: { id: id1 }
        }, context, { deletePhysically: true });
        await context.commit();

        // 即使使用 includedDeleted 也查不到
        const r1 = await store.select('house', {
            data: { id: 1 },
            filter: { id: id1 }
        }, context, { includedDeleted: true });
        assert(r1.length === 0, `Physically deleted record should not exist`);
    });

    // ==================== 跨表 JOIN 更新测试 ====================

    // TODO: 下面会出现死锁，暂不知道为什么（已经解决）
    it('[17.1]update with join filter', async () => {
        const store = storeGetter();
        const context = new TestContext(store);
        await context.begin();

        const systemId = v4();
        const appId1 = v4();
        const appId2 = v4();

        await store.operate('system', {
            id: v4(),
            action: 'create',
            data: {
                id: systemId,
                name: 'join_test_system',
                description: 'test',
                folder: '/test',
                application$system: [
                    { id: v4(), action: 'create', data: { id: appId1, name: 'join_app1', description: 'old', type: 'web' } },
                    { id: v4(), action: 'create', data: { id: appId2, name: 'join_app2', description: 'old', type: 'wechatMp' } },
                ]
            }
        } as EntityDict['system']['CreateSingle'], context, {});
        await context.commit();

        // 通过 system 过滤更新 application
        await context.begin();
        await store.operate('application', {
            id: v4(),
            action: 'update',
            data: { description: 'updated' },
            filter: {
                // TODO: 必须加上自身过滤条件，否则会报错死锁
                name: { $in: ['join_app1', 'join_app2'] },
                system: {
                    name: 'join_test_system'
                },
                type: 'web'
            }
        }, context, {});
        await context.commit();

        // 验证只更新了 type='web' 的记录
        const r1 = await context.select('application', {
            data: { id: 1, description: 1 },
            filter: { id: appId1 }
        }, {});
        assert(r1.length === 1 && r1[0].description === 'updated', `Join update failed for appId1`);

        const r2 = await context.select('application', {
            data: { id: 1, description: 1 },
            filter: { id: appId2 }
        }, {});
        assert(r2.length === 1 && r2[0].description === 'old', `Join update should not affect appId2`);
    });

    // ==================== 空值处理测试 ====================

    // TODO: 空值会报错
    // it('[18.1]null value handling', async () => {
    //     const store = storeGetter();
    //     const context = new TestContext(store);
    //     await context.begin();

    //     const id1 = v4();
    //     const id2 = v4();

    //     await store.operate('user', {
    //         id: v4(),
    //         action: 'create',
    //         data: [
    //             { id: id1, name: 'user_with_ref', nickname: 'u1', refId: v4(), idState: 'unverified', userState: 'normal', },
    //             { id: id2, name: 'user_without_ref', nickname: 'u2', idState: 'unverified', userState: 'normal', },  // refId 为 null
    //         ]
    //     }, context, {});
    //     await context.commit();

    //     // 查询 refId 为 null 的记录
    //     const r1 = await context.select('user', {
    //         data: { id: 1, name: 1 },
    //         filter: {
    //             id: { $in: [id1, id2] },
    //             refId: null as any
    //         }
    //     }, {});
    //     assert(r1.length === 1 && r1[0].id === id2, `Null filter failed`);

    //     // 查询 refId 不为 null 的记录
    //     const r2 = await context.select('user', {
    //         data: { id: 1, name: 1 },
    //         filter: {
    //             id: { $in: [id1, id2] },
    //             refId: { $ne: null as any }
    //         }
    //     }, {});
    //     assert(r2.length === 1 && r2[0].id === id1, `Not null filter failed`);
    // });

    // ==================== 枚举类型测试 ====================

    it('[19.1]enum type insert and filter', async () => {
        const store = storeGetter();
        const context = new TestContext(store);
        await context.begin();

        const id1 = v4();
        const id2 = v4();

        await store.operate('application', {
            id: v4(),
            action: 'create',
            data: [
                { id: id1, name: 'web_app', description: 't', type: 'web', systemId: v4() },
                { id: id2, name: 'mp_app', description: 't', type: 'wechatMp', systemId: v4() },
            ]
        }, context, {});
        await context.commit();

        // 按枚举值过滤
        const r1 = await context.select('application', {
            data: { id: 1, type: 1 },
            filter: {
                id: { $in: [id1, id2] },
                type: 'web'
            }
        }, {});
        assert(r1.length === 1 && r1[0].id === id1, `Enum filter failed`);

        // 枚举 $in 查询
        const r2 = await context.select('application', {
            data: { id: 1, type: 1 },
            filter: {
                id: { $in: [id1, id2] },
                type: { $in: ['web', 'wechatMp'] }
            }
        }, {});
        assert(r2.length === 2, `Enum $in filter failed`);
    });

    // ==================== 复杂嵌套查询测试 ====================

    it('[20.1]deeply nested filter', async () => {
        const store = storeGetter();
        const context = new TestContext(store);
        await context.begin();

        const platformId = v4();
        const systemId = v4();
        const appId = v4();

        // 创建 platform -> system -> application 层级结构
        await store.operate('platform', {
            id: v4(),
            action: 'create',
            data: {
                id: platformId,
                name: 'test_platform',
                description: 'test',
            }
        }, context, {});

        await store.operate('system', {
            id: v4(),
            action: 'create',
            data: {
                id: systemId,
                name: 'nested_system',
                description: 'test',
                folder: '/test',
                platformId,
                application$system: [
                    { id: v4(), action: 'create', data: { id: appId, name: 'nested_app', description: 't', type: 'web' } },
                ]
            }
        } as EntityDict['system']['CreateSingle'], context, {});
        await context.commit();

        // 三层嵌套查询: application -> system -> platform
        const r1 = await context.select('application', {
            data: {
                id: 1,
                name: 1,
                system: {
                    id: 1,
                    name: 1,
                    platform: {
                        id: 1,
                        name: 1,
                    }
                }
            },
            filter: {
                id: appId,
                system: {
                    platform: {
                        name: 'test_platform'
                    }
                }
            }
        }, {});

        assert(r1.length === 1, `Deeply nested query failed`);
        assert(r1[0].system?.platform?.name === 'test_platform', `Nested projection failed`);
    });

    it('[1.4.1]嵌套跨节点表达式', async () => {
        const store = storeGetter();
        const context = new TestContext(store);
        await context.begin();

        const platformId = v4();
        await store.operate('platform', {
            id: v4(),
            action: 'create',
            data: {
                id: platformId,
                name: 'test2'
            }
        }, context, {})
        await store.operate('application', {
            id: v4(),
            action: 'create',
            data: [{
                id: v4(),
                name: 'test',
                description: 'ttttt',
                type: 'web',
                system: {
                    id: v4(),
                    action: 'create',
                    data: {
                        id: v4(),
                        name: 'systest',
                        folder: 'systest',
                        platformId,
                    }
                },
            }, {
                id: v4(),
                name: 'test222',
                description: 'ttttt2',
                type: 'web',
                system: {
                    id: v4(),
                    action: 'create',
                    data: {
                        id: v4(),
                        name: 'test2222',
                        folder: 'test2',
                        platformId,
                    }
                },
            }]
        }, context, {});

        process.env.NODE_ENV = 'development';

        // 查询所有的application，过滤条件是application->system.name = application->system->platform.name
        const apps = await store.select('application', {
            data: {
                id: 1,
                name: 1,
                system: {
                    id: 1,
                    name: 1,
                    platform: {
                        id: 1,
                        name: 1,
                    }
                }
            },
            filter: {
                system: {
                    "#id": 'node-1',
                    platform: {
                        $expr: {
                            $eq: [
                                { "#attr": 'name' },
                                { "#refId": 'node-1', "#refAttr": 'folder' }
                            ]
                        }
                    }
                }
            }
        }, context, {});

        process.env.NODE_ENV = undefined;
       assert(apps.length === 1 && apps[0].name === 'test222');
        await context.commit();
    });
}