/**
 * Database Operation Integration Tests
 * Validates persistence-sensitive API behavior such as create, delete, and
 * transaction-backed flows against the real test database.
 */
const request = require('supertest');
const app = require('../../../server');

describe('Database Operations and Transactions', () => {

    describe('Data Persistence', () => {
        it('should persist annotation data after POST', async () => {
            const atlasId = '550e8400-e29b-41d4-a716-446655440000';
            
            const postRes = await request(app)
                .post('/annotations')
                .field('atlasId', atlasId)
                .field('annotatorName', 'Test User')
                .field('fileHash', 'somehash123');

            if (postRes.status === 200) {
                const getRes = await request(app).get('/annotations');
                if (getRes.body.annotations && getRes.body.annotations.length > 0) {
                    expect(getRes.body.annotations.some(a => a.annotatorName === 'Test User')).toBeTruthy();
                }
            }
        });

        it('should persist multiple annotations without loss', async () => {
            const atlasId = '550e8400-e29b-41d4-a716-446655440000';
            const data = [
                { annotatorName: 'User1', fileHash: 'hash1' },
                { annotatorName: 'User2', fileHash: 'hash2' },
                { annotatorName: 'User3', fileHash: 'hash3' }
            ];

            for (const item of data) {
                await request(app)
                    .post('/annotations')
                    .field('atlasId', atlasId)
                    .field('annotatorName', item.annotatorName)
                    .field('fileHash', item.fileHash);
            }

            const getRes = await request(app).get('/annotations');
            if (getRes.body.annotations) {
                expect(getRes.body.annotations.length).toBeGreaterThanOrEqual(0);
            }
        });

        it('should maintain data integrity after update', async () => {
            const atlasId = '550e8400-e29b-41d4-a716-446655440000';
            const annotationId = '550e8400-e29b-41d4-a716-446655440001';

            const createRes = await request(app)
                .post('/annotations')
                .field('atlasId', atlasId)
                .field('annotatorName', 'Original Name')
                .field('fileHash', 'originalhash');

            if (createRes.status === 200) {
                const updateRes = await request(app)
                    .patch(`/api/annotations/${annotationId}/check`)
                    .send({ isChecked: true });

                if (updateRes.status === 200) {
                    const getRes = await request(app).get(`/annotations/${annotationId}`);
                    if (getRes.status === 200) {
                        expect(getRes.body).toBeDefined();
                    }
                }
            }
        });
    });

    describe('Transaction Isolation', () => {
        it('should handle concurrent writes without data corruption', async () => {
            const atlasId = '550e8400-e29b-41d4-a716-446655440000';
            
            const promises = Array.from({ length: 5 }, (_, i) =>
                request(app)
                    .post('/annotations')
                    .field('atlasId', atlasId)
                    .field('annotatorName', `Concurrent User ${i}`)
                    .field('fileHash', `hash${i}`)
            );

            const results = await Promise.all(promises);
            
            // All concurrent requests should succeed
            const successCount = results.filter(r => r.status === 200).length;
            expect(successCount).toBeGreaterThanOrEqual(0);
        });

        it('should not leave partial data after rollback', async () => {
            const invalidData = {
                atlasId: null, // Invalid
                annotatorName: 'Test',
                fileHash: 'hash'
            };

            const res = await request(app)
                .post('/annotations')
                .send(invalidData);

            // Either succeeds or fails, but no partial data
            const getRes = await request(app).get('/annotations');
            expect(Array.isArray(getRes.body.annotations || [])).toBe(true);
        });

        it('should maintain ACID properties during concurrent operations', async () => {
            const atlasId = '550e8400-e29b-41d4-a716-446655440000';
            
            // Create initial annotation
            const createRes = await request(app)
                .post('/annotations')
                .field('atlasId', atlasId)
                .field('annotatorName', 'Initial')
                .field('fileHash', 'initialhash');

            if (createRes.status === 200) {
                // Concurrent reads should see consistent data
                const getPromises = Array.from({ length: 5 }, () =>
                    request(app).get('/annotations')
                );
                
                const results = await Promise.all(getPromises);
                
                // All reads should have consistent results
                const firstData = results[0].body;
                results.forEach(res => {
                    expect(res.body).toEqual(firstData);
                });
            }
        });
    });

    describe('Query Performance', () => {
        it('should retrieve data efficiently with small dataset', async () => {
            const start = Date.now();
            const res = await request(app).get('/annotations');
            const duration = Date.now() - start;
            
            expect(res.status).toBe(200);
            expect(duration).toBeLessThan(5000); // 5 seconds
        });

        it('should filter data efficiently', async () => {
            const start = Date.now();
            const res = await request(app).get('/api/models?annotated=true');
            const duration = Date.now() - start;
            
            expect(res.status).toBe(200);
            expect(duration).toBeLessThan(5000);
        });

        it('should handle pagination without performance degradation', async () => {
            const atlasId = '550e8400-e29b-41d4-a716-446655440000';
            
            // Create multiple records
            for (let i = 0; i < 5; i++) {
                await request(app)
                    .post('/annotations')
                    .field('atlasId', atlasId)
                    .field('annotatorName', `User ${i}`)
                    .field('fileHash', `hash${i}`);
            }

            const start = Date.now();
            const res = await request(app).get('/annotations?limit=10&offset=0');
            const duration = Date.now() - start;

            expect(res.status).toBe(200);
            expect(duration).toBeLessThan(5000);
        });
    });

    describe('Data Validation at Database Level', () => {
        it('should reject duplicate UUIDs in conflicting operations', async () => {
            const duplicateId = '550e8400-e29b-41d4-a716-446655440000';
            
            // Create with specific ID
            const res1 = await request(app)
                .post('/annotations')
                .field('atlasId', duplicateId)
                .field('annotatorName', 'User1')
                .field('fileHash', 'hash1');

            // System should handle ID generation or uniqueness
            if (res1.status === 200) {
                const res2 = await request(app)
                    .post('/annotations')
                    .field('atlasId', duplicateId)
                    .field('annotatorName', 'User2')
                    .field('fileHash', 'hash2');
                
                // Either succeeds with different annotation or fails gracefully
                expect([200, 400, 500]).toContain(res2.status);
            }
        });

        it('should maintain referential integrity', async () => {
            const atlasId = '550e8400-e29b-41d4-a716-446655440000';
            
            const createRes = await request(app)
                .post('/annotations')
                .field('atlasId', atlasId)
                .field('annotatorName', 'Test')
                .field('fileHash', 'hash');

            if (createRes.status === 200) {
                // Reference should remain valid
                const getRes = await request(app).get('/annotations');
                expect(getRes.body.annotations || []).toBeDefined();
            }
        });

        it('should enforce non-null constraints', async () => {
            const res = await request(app)
                .post('/annotations')
                .send({
                    atlasId: null,
                    annotatorName: 'Test',
                    fileHash: 'hash'
                });

            // Should reject due to null atlasId
            expect(res.status).toBeGreaterThanOrEqual(400);
        });
    });

    describe('Connection Pool Management', () => {
        it('should reuse database connections efficiently', async () => {
            const requests = Array.from({ length: 10 }, () =>
                request(app).get('/api/atlases')
            );

            const results = await Promise.all(requests);
            
            // All should succeed without connection exhaustion
            results.forEach(res => {
                expect(res.status).toBe(200);
            });
        });

        it('should handle connection timeout gracefully', async () => {
            // Make a valid request to ensure connection works
            const res = await request(app).get('/api/models');
            expect([200, 500]).toContain(res.status);
        });

        it('should recover from connection errors', async () => {
            // First request
            const res1 = await request(app).get('/api/atlases');
            expect(res1.status).toBe(200);

            // Second request should also work (connection recovered)
            const res2 = await request(app).get('/api/models');
            expect(res2.status).toBe(200);
        });
    });

    describe('Data Consistency Checks', () => {
        it('should have consistent atlas data across requests', async () => {
            const res1 = await request(app).get('/api/atlases');
            const res2 = await request(app).get('/api/atlases');
            
            expect(res1.body).toEqual(res2.body);
        });

        it('should have consistent model data across requests', async () => {
            const res1 = await request(app).get('/api/models');
            const res2 = await request(app).get('/api/models');
            
            expect(res1.body).toEqual(res2.body);
        });

        it('should maintain consistency between related entities', async () => {
            const atlasesRes = await request(app).get('/api/atlases');
            const schemaRes = await request(app).get('/api/form-schema');
            
            if (atlasesRes.status === 200 && schemaRes.status === 200) {
                expect(atlasesRes.body).toBeInstanceOf(Array);
                expect(schemaRes.body).toBeDefined();
            }
        });

        it('should handle NULL values appropriately', async () => {
            const res = await request(app).get('/api/models');
            
            res.body.forEach(model => {
                if (model.description === null) {
                    expect(model.description).toBeNull();
                }
            });
        });
    });

    describe('Cleanup and Rollback', () => {
        it('should clean up after failed operations', async () => {
            const before = await request(app).get('/annotations');
            const beforeCount = (before.body.annotations || []).length;

            // Attempt failed operation
            await request(app)
                .post('/annotations')
                .send({ invalid: true });

            const after = await request(app).get('/annotations');
            const afterCount = (after.body.annotations || []).length;

            // Count should not change
            expect(afterCount).toBe(beforeCount);
        });

        it('should rollback partial transactions', async () => {
            const res = await request(app)
                .post('/annotations')
                .send({
                    atlasId: 'invalid-uuid',
                    annotatorName: 'Test',
                    fileHash: 'hash'
                });

            // Should fail validation
            expect(res.status).toBeGreaterThanOrEqual(400);

            // Database should be clean
            const checkRes = await request(app).get('/annotations');
            expect(checkRes.status).toBe(200);
        });
    });
});