`database/repositories/WatchRepository.js`

332 lines
/**
 * Watch Repository
 * Implements repository pattern for watch data access
 * Provides abstraction layer between business logic and database
 */

import pool from '../connection.js';

export class WatchRepository {
    /**
     * Get all watches with optional filtering and pagination
     */
    async findAll(options = {}) {
        const {
            series,
            minYear,
            maxYear,
            limit = 100,
            offset = 0,
            sortBy = 'year_introduced',
            sortOrder = 'DESC'
        } = options;

        let query = `
            SELECT
                w.*,
                json_build_object(
                    'caseSize', s.case_size,
                    'caseMaterial', s.case_material,
                    'movement', s.movement_calibre,
                    'waterResistance', s.water_resistance
                ) as specifications,
                (SELECT COUNT(*) FROM price_history ph WHERE ph.watch_id = w.id) as price_point_count
            FROM watches w
            LEFT JOIN specifications s ON w.id = s.watch_id
            WHERE w.is_active = true
        `;

        const params = [];
        let paramCount = 0;

        if (series) {
            paramCount++;
            query += ` AND w.series = $${paramCount}`;
            params.push(series);
        }

        if (minYear) {
            paramCount++;
            query += ` AND w.year_introduced >= $${paramCount}`;
            params.push(minYear);
        }

        if (maxYear) {
            paramCount++;
            query += ` AND w.year_introduced <= $${paramCount}`;
            params.push(maxYear);
        }

        query += ` ORDER BY ${sortBy} ${sortOrder}`;

        paramCount++;
        query += ` LIMIT $${paramCount}`;
        params.push(limit);

        paramCount++;
        query += ` OFFSET $${paramCount}`;
        params.push(offset);

        const result = await pool.query(query, params);
        return result.rows;
    }

    /**
     * Find watch by ID or slug
     */
    async findById(identifier) {
        const query = `
            SELECT
                w.*,
                json_build_object(
                    'caseSize', s.case_size,
                    'caseMaterial', s.case_material,
                    'caseThickness', s.case_thickness,
                    'lugWidth', s.lug_width,
                    'movement', s.movement_calibre,
                    'movementType', s.movement_type,
                    'powerReserve', s.power_reserve,
                    'waterResistance', s.water_resistance,
                    'crystal', s.crystal_type,
                    'dialColor', s.dial_color,
                    'bezel', s.bezel_type,
                    'weight', s.weight
                ) as specifications,
                (
                    SELECT json_agg(f.name)
                    FROM features f
                    INNER JOIN watch_features wf ON f.id = wf.feature_id
                    WHERE wf.watch_id = w.id
                ) as features
            FROM watches w
            LEFT JOIN specifications s ON w.id = s.watch_id
            WHERE (w.id::text = $1 OR w.slug = $1)
            AND w.is_active = true
        `;

        const result = await pool.query(query, [identifier]);
        return result.rows[0] || null;
    }

    /**
     * Get watch with complete price history
     */
    async findByIdWithPriceHistory(identifier) {
        const watch = await this.findById(identifier);
        if (!watch) return null;

        const priceQuery = `
            SELECT
                year,
                price,
                condition,
                source,
                currency,
                is_estimate,
                confidence_level
            FROM price_history
            WHERE watch_id = $1
            ORDER BY year ASC
        `;

        const priceResult = await pool.query(priceQuery, [watch.id]);
        watch.priceHistory = priceResult.rows;

        return watch;
    }

    /**
     * Full-text search across watches
     */
    async search(searchTerm, options = {}) {
        const { limit = 20, offset = 0 } = options;

        const query = `
            SELECT
                w.*,
                ts_rank(w.search_vector, plainto_tsquery('english', $1)) as rank
            FROM watches w
            WHERE w.search_vector @@ plainto_tsquery('english', $1)
            AND w.is_active = true
            ORDER BY rank DESC
            LIMIT $2 OFFSET $3
        `;

        const result = await pool.query(query, [searchTerm, limit, offset]);
        return result.rows;
    }

    /**
     * Get watches by series
     */
    async findBySeries(series) {
        const query = `
            SELECT w.*, s.*
            FROM watches w
            LEFT JOIN specifications s ON w.id = s.watch_id
            WHERE w.series = $1
            AND w.is_active = true
            ORDER BY w.year_introduced DESC
        `;

        const result = await pool.query(query, [series]);
        return result.rows;
    }

    /**
     * Get trending watches (most viewed recently)
     */
    async getTrending(daysBack = 7, limit = 10) {
        const result = await pool.query(
            'SELECT * FROM get_trending_watches($1, $2)',
            [daysBack, limit]
        );
        return result.rows;
    }

    /**
     * Get watch statistics
     */
    async getStatistics(watchId) {
        const query = `
            SELECT * FROM watch_statistics
            WHERE id = $1
        `;

        const result = await pool.query(query, [watchId]);
        return result.rows[0] || null;
    }

    /**
     * Get all statistics (from materialized view)
     */
    async getAllStatistics(options = {}) {
        const {
            sortBy = 'appreciation_percentage',
            sortOrder = 'DESC',
            limit = 100,
            offset = 0
        } = options;

        const query = `
            SELECT * FROM watch_statistics
            ORDER BY ${sortBy} ${sortOrder} NULLS LAST
            LIMIT $1 OFFSET $2
        `;

        const result = await pool.query(query, [limit, offset]);
        return result.rows;
    }

    /**
     * Create new watch
     */
    async create(watchData) {
        const {
            slug,
            model,
            series,
            reference,
            yearIntroduced,
            description,
            detailedDescription,
            imageUrl,
            isLimitedEdition,
            limitedEditionCount
        } = watchData;

        const query = `
            INSERT INTO watches (
                slug, model, series, reference, year_introduced,
                description, detailed_description, image_url,
                is_limited_edition, limited_edition_count
            ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
            RETURNING *
        `;

        const result = await pool.query(query, [
            slug,
            model,
            series,
            reference,
            yearIntroduced,
            description,
            detailedDescription,
            imageUrl,
            isLimitedEdition || false,
            limitedEditionCount || null
        ]);

        return result.rows[0];
    }

    /**
     * Update watch
     */
    async update(watchId, updateData) {
        const allowedFields = [
            'model', 'series', 'reference', 'year_introduced',
            'description', 'detailed_description', 'image_url',
            'is_active', 'is_limited_edition', 'limited_edition_count'
        ];

        const updates = [];
        const params = [];
        let paramCount = 0;

        for (const [key, value] of Object.entries(updateData)) {
            const snakeKey = key.replace(/([A-Z])/g, '_$1').toLowerCase();
            if (allowedFields.includes(snakeKey)) {
                paramCount++;
                updates.push(`${snakeKey} = $${paramCount}`);
                params.push(value);
            }
        }

        if (updates.length === 0) {
            throw new Error('No valid fields to update');
        }

        paramCount++;
        params.push(watchId);

        const query = `
            UPDATE watches
            SET ${updates.join(', ')}
            WHERE id = $${paramCount}
            RETURNING *
        `;

        const result = await pool.query(query, params);
        return result.rows[0];
    }

    /**
     * Track view (for trending analytics)
     */
    async trackView(watchId, metadata = {}) {
        const { sessionId, userAgent, ipAddress } = metadata;

        const query = `
            INSERT INTO watch_views (watch_id, session_id, user_agent, ip_address)
            VALUES ($1, $2, $3, $4)
        `;

        await pool.query(query, [
            watchId,
            sessionId || null,
            userAgent || null,
            ipAddress || null
        ]);
    }

    /**
     * Refresh materialized views
     */
    async refreshStatistics() {
        await pool.query('REFRESH MATERIALIZED VIEW CONCURRENTLY watch_statistics');
    }
}

export default new WatchRepository();