import 'dart:async';
import 'dart:convert';
import 'dart:io';

import 'package:dart_jsonwebtoken/dart_jsonwebtoken.dart';
import 'package:postgres/postgres.dart';
import 'package:shelf/shelf.dart';
import 'package:shelf/shelf_io.dart' as shelf_io;
import 'package:shelf_cors_headers/shelf_cors_headers.dart';
import 'package:shelf_router/shelf_router.dart';
import 'package:uuid/uuid.dart';

final _uuid = const Uuid();

Future<void> runServer() async {
  final db = _LazyDbConnection(_openConnection);
  final jwtSecret = Platform.environment['JWT_SECRET'] ?? 'dev-secret';
  final port = int.tryParse(Platform.environment['PORT'] ?? '') ?? 8080;

  final app = _CatalogApi(db: db, jwtSecret: jwtSecret);
  final handler = Pipeline()
      .addMiddleware(corsHeaders())
      .addHandler(app.router.call);

  final server = await shelf_io.serve(handler, InternetAddress.anyIPv4, port);
  print('Server started: http://${server.address.host}:${server.port}');
}

Future<PostgreSQLConnection> _openConnection() async {
  final host = Platform.environment['PGHOST'] ?? '127.0.0.1';
  final port = int.tryParse(Platform.environment['PGPORT'] ?? '') ?? 5432;
  final database = Platform.environment['PGDATABASE'] ?? 'building_catalog';
  final user = Platform.environment['PGUSER'] ?? 'postgres';
  final password = Platform.environment['PGPASSWORD'] ?? 'postgres';
  final maxAttempts =
      int.tryParse(Platform.environment['DB_CONNECT_ATTEMPTS'] ?? '') ?? 12;
  final retryDelayMs =
      int.tryParse(Platform.environment['DB_CONNECT_DELAY_MS'] ?? '') ?? 1000;

  Object? lastError;
  for (var attempt = 1; attempt <= maxAttempts; attempt++) {
    final connection = PostgreSQLConnection(
      host,
      port,
      database,
      username: user,
      password: password,
      useSSL: false,
      timeoutInSeconds: 10,
    );

    try {
      await connection.open();
      return connection;
    } catch (e) {
      lastError = e;
      stderr.writeln(
        'Database connection attempt $attempt/$maxAttempts failed: $e',
      );
      try {
        await connection.close();
      } catch (_) {}
      if (attempt < maxAttempts) {
        await Future.delayed(Duration(milliseconds: retryDelayMs));
      }
    }
  }

  throw Exception(
    'Could not connect to Postgres at $host:$port/$database after $maxAttempts attempts. Last error: $lastError',
  );
}

class _LazyDbConnection {
  _LazyDbConnection(this._connector);

  final Future<PostgreSQLConnection> Function() _connector;
  PostgreSQLConnection? _connection;
  Future<PostgreSQLConnection>? _pendingConnection;

  Future<PostgreSQLConnection> _getConnection() async {
    final activeConnection = _connection;
    if (activeConnection != null) {
      return activeConnection;
    }

    final pendingConnection = _pendingConnection;
    if (pendingConnection != null) {
      return pendingConnection;
    }

    final connectFuture = _connector();
    _pendingConnection = connectFuture;
    try {
      final connection = await connectFuture;
      _connection = connection;
      return connection;
    } finally {
      _pendingConnection = null;
    }
  }

  Future<List<List<dynamic>>> query(
    String query, {
    Map<String, dynamic>? substitutionValues,
  }) async {
    final connection = await _getConnection();
    return connection.query(query, substitutionValues: substitutionValues);
  }

  Future<T> transaction<T>(
    Future<T> Function(PostgreSQLExecutionContext ctx) callback,
  ) async {
    final connection = await _getConnection();
    final result = await connection.transaction(callback);
    return result as T;
  }
}

class _CatalogApi {
  _CatalogApi({required _LazyDbConnection db, required String jwtSecret})
    : _db = db,
      _jwtSecret = jwtSecret {
    _registerRoutes();
  }

  final _LazyDbConnection _db;
  final String _jwtSecret;
  final Router router = Router();

  void _registerRoutes() {
    router
      ..get('/health', (Request _) => _json({'ok': true}))
      ..get('/api/v1/categories', _getCategories)
      ..get('/api/v1/categories/<id>', _getCategory)
      ..get('/api/v1/brands', _getBrands)
      ..get('/api/v1/products', _getProducts)
      ..get('/api/v1/products/facets', _getProductFacets)
      ..get('/api/v1/products/<id>', _getProductById)
      ..post('/api/v1/auth/login', _login)
      ..post('/api/v1/admin/categories', _adminCreateCategory)
      ..patch('/api/v1/admin/categories/<id>', _adminPatchCategory)
      ..delete('/api/v1/admin/categories/<id>', _adminDeleteCategory)
      ..post('/api/v1/admin/brands', _adminCreateBrand)
      ..patch('/api/v1/admin/brands/<id>', _adminPatchBrand)
      ..delete('/api/v1/admin/brands/<id>', _adminDeleteBrand)
      ..post('/api/v1/admin/products', _adminCreateProduct)
      ..patch('/api/v1/admin/products/<id>', _adminPatchProduct)
      ..delete('/api/v1/admin/products/<id>', _adminDeleteProduct)
      ..patch('/api/v1/admin/products/<id>/stock', _adminPatchStock)
      ..patch('/api/v1/admin/products/<id>/price', _adminPatchPrice);
  }

  Future<Response> _getCategories(Request request) async {
    try {
      final tree = _boolArg(request, 'tree', true);
      final activeOnly = _boolArg(request, 'activeOnly', true);
      final whereSql = activeOnly ? 'WHERE c.is_active = TRUE' : '';
      final rows = await _db.query('''
          SELECT c.id, c.parent_id, c.name, c.slug, c.is_active
          FROM categories c
          $whereSql
          ORDER BY c.name
        ''');

      final items = rows
          .map(
            (row) => {
              'id': row[0].toString(),
              'parentId': row[1]?.toString(),
              'name': row[2] as String,
              'slug': row[3] as String,
              'isActive': row[4] as bool,
            },
          )
          .toList();

      if (!tree) {
        return _json(items);
      }

      final byParent = <String?, List<Map<String, dynamic>>>{};
      for (final item in items) {
        final parentId = item['parentId'] as String?;
        byParent.putIfAbsent(parentId, () => []).add(item);
      }

      List<Map<String, dynamic>> buildTree(String? parentId) {
        final nodes = byParent[parentId] ?? [];
        return nodes
            .map(
              (node) => {
                'id': node['id'],
                'name': node['name'],
                'slug': node['slug'],
                'children': buildTree(node['id'] as String),
              },
            )
            .toList();
      }

      return _json(buildTree(null));
    } catch (e) {
      return _serverError(e);
    }
  }

  Future<Response> _getCategory(Request request, String id) async {
    try {
      final row = await _db.query(
        '''
          SELECT c.id
          FROM categories c
          WHERE c.id = @id
          LIMIT 1
        ''',
        substitutionValues: {'id': id},
      );

      if (row.isEmpty) {
        return _error('not_found', 'Category not found', status: 404);
      }

      final categories = await _db.query(
        '''
          WITH RECURSIVE category_tree AS (
            SELECT c.id, c.parent_id, c.name, c.slug, c.is_active
            FROM categories c
            WHERE c.id = @id
            UNION ALL
            SELECT child.id, child.parent_id, child.name, child.slug, child.is_active
            FROM categories child
            INNER JOIN category_tree ct ON child.parent_id = ct.id
          )
          SELECT id, parent_id, name, slug, is_active
          FROM category_tree
          ORDER BY name
        ''',
        substitutionValues: {'id': id},
      );

      final items = categories
          .map(
            (entry) => {
              'id': entry[0].toString(),
              'parentId': entry[1]?.toString(),
              'name': entry[2],
              'slug': entry[3],
            },
          )
          .toList();

      final byParent = <String?, List<Map<String, dynamic>>>{};
      for (final item in items) {
        final parentId = item['parentId'] as String?;
        byParent.putIfAbsent(parentId, () => []).add(item);
      }

      Map<String, dynamic> build(String nodeId) {
        final node = items.firstWhere((entry) => entry['id'] == nodeId);
        return {
          'id': node['id'],
          'name': node['name'],
          'slug': node['slug'],
          'children': (byParent[nodeId] ?? [])
              .map((child) => build(child['id'] as String))
              .toList(),
        };
      }

      return _json(build(id));
    } catch (e) {
      return _serverError(e);
    }
  }

  Future<Response> _getBrands(Request request) async {
    try {
      final q = request.url.queryParameters['q']?.trim();
      final hasQuery = q != null && q.isNotEmpty;
      final whereSql = hasQuery ? 'WHERE b.name ILIKE @like_q' : '';
      final rows = await _db.query('''
          SELECT b.id, b.name, b.slug
          FROM brands b
          $whereSql
          ORDER BY b.name
        ''', substitutionValues: hasQuery ? {'like_q': '%$q%'} : {});

      return _json(
        rows
            .map(
              (row) => {
                'id': row[0].toString(),
                'name': row[1],
                'slug': row[2],
              },
            )
            .toList(),
      );
    } catch (e) {
      return _serverError(e);
    }
  }

  Future<Response> _getProducts(Request request) async {
    try {
      final page = _intArg(request, 'page', 1, min: 1);
      final pageSize = _intArg(request, 'pageSize', 20, min: 1, max: 100);

      final whereParts = <String>['p.is_active = TRUE'];
      final params = <String, dynamic>{};

      final q = request.url.queryParameters['q']?.trim();
      if (q != null && q.isNotEmpty) {
        whereParts.add(
          '(p.name ILIKE @like_q OR p.description ILIKE @like_q OR p.sku ILIKE @like_q)',
        );
        params['like_q'] = '%$q%';
      }

      final categoryId = request.url.queryParameters['categoryId'];
      if (categoryId != null && categoryId.isNotEmpty) {
        whereParts.add('p.category_id = @category_id');
        params['category_id'] = categoryId;
      }

      final brandId = request.url.queryParameters['brandId'];
      if (brandId != null && brandId.isNotEmpty) {
        whereParts.add('p.brand_id = @brand_id');
        params['brand_id'] = brandId;
      }

      final priceMin = double.tryParse(
        request.url.queryParameters['priceMin'] ?? '',
      );
      if (priceMin != null) {
        whereParts.add('pr.price >= @price_min');
        params['price_min'] = priceMin;
      }

      final priceMax = double.tryParse(
        request.url.queryParameters['priceMax'] ?? '',
      );
      if (priceMax != null) {
        whereParts.add('pr.price <= @price_max');
        params['price_max'] = priceMax;
      }

      final inStock = _nullableBoolArg(request, 'inStock');
      if (inStock != null) {
        whereParts.add(
          inStock
              ? '(st.qty - st.reserved_qty) > 0'
              : '(st.qty - st.reserved_qty) <= 0',
        );
      }

      final attrs = _parseAttrs(request.url.queryParameters['attrs']);
      for (var i = 0; i < attrs.length; i++) {
        final attr = attrs[i];
        whereParts.add('''
          EXISTS (
            SELECT 1 FROM product_attributes pa$i
            WHERE pa$i.product_id = p.id
              AND pa$i.key = @attr_key_$i
              AND pa$i.value = @attr_value_$i
          )
          ''');
        params['attr_key_$i'] = attr.$1;
        params['attr_value_$i'] = attr.$2;
      }

      final sortRaw = request.url.queryParameters['sort'] ?? 'createdAt';
      final orderRaw = request.url.queryParameters['order'] ?? 'desc';
      final sort = switch (sortRaw) {
        'price' => 'pr.price',
        'name' => 'p.name',
        _ => 'p.created_at',
      };
      final order = orderRaw.toLowerCase() == 'asc' ? 'ASC' : 'DESC';

      final whereSql = whereParts.isEmpty
          ? ''
          : 'WHERE ${whereParts.join(' AND ')}';

      final countRows = await _db.query('''
          SELECT COUNT(*)
          FROM products p
          LEFT JOIN prices pr ON pr.product_id = p.id
          LEFT JOIN stocks st ON st.product_id = p.id
          $whereSql
        ''', substitutionValues: params);
      final total = _toInt(countRows.first.first);
      final totalPages = total == 0 ? 0 : (total / pageSize).ceil();

      params['limit'] = pageSize;
      params['offset'] = (page - 1) * pageSize;
      final rows = await _db.query('''
          SELECT
            p.id,
            p.sku,
            p.name,
            p.unit,
            p.is_active,
            c.id,
            c.name,
            b.id,
            b.name,
            pr.currency,
            pr.price,
            pr.old_price,
            st.qty,
            st.reserved_qty,
            (
              SELECT pi.url
              FROM product_images pi
              WHERE pi.product_id = p.id
              ORDER BY pi.sort_order
              LIMIT 1
            )
          FROM products p
          INNER JOIN categories c ON c.id = p.category_id
          LEFT JOIN brands b ON b.id = p.brand_id
          LEFT JOIN prices pr ON pr.product_id = p.id
          LEFT JOIN stocks st ON st.product_id = p.id
          $whereSql
          ORDER BY $sort $order
          LIMIT @limit OFFSET @offset
        ''', substitutionValues: params);

      final items = rows
          .map(
            (row) => {
              'id': row[0].toString(),
              'sku': row[1],
              'name': row[2],
              'category': {'id': row[5].toString(), 'name': row[6]},
              'brand': row[7] == null
                  ? null
                  : {'id': row[7].toString(), 'name': row[8]},
              'price': {
                'currency': row[9] ?? 'RUB',
                'price': _toDouble(row[10]),
                'oldPrice': _nullableDouble(row[11]),
              },
              'unit': row[3],
              'stock': {
                'qty': _toDouble(row[12]),
                'isInStock': _toDouble(row[12]) - _toDouble(row[13]) > 0,
              },
              'image': row[14],
              'isActive': row[4] as bool,
            },
          )
          .toList();

      return _json({
        'items': items,
        'page': page,
        'pageSize': pageSize,
        'total': total,
        'totalPages': totalPages,
      });
    } catch (e) {
      return _serverError(e);
    }
  }

  Future<Response> _getProductById(Request request, String id) async {
    try {
      final rows = await _db.query(
        '''
          SELECT
            p.id,
            p.sku,
            p.name,
            p.description,
            p.unit,
            p.is_active,
            p.created_at,
            p.updated_at,
            c.id,
            c.name,
            c.slug,
            b.id,
            b.name,
            b.slug,
            pr.currency,
            pr.price,
            pr.old_price,
            st.qty,
            st.reserved_qty
          FROM products p
          INNER JOIN categories c ON c.id = p.category_id
          LEFT JOIN brands b ON b.id = p.brand_id
          LEFT JOIN prices pr ON pr.product_id = p.id
          LEFT JOIN stocks st ON st.product_id = p.id
          WHERE p.id = @id AND p.is_active = TRUE
          LIMIT 1
        ''',
        substitutionValues: {'id': id},
      );

      if (rows.isEmpty) {
        return _error('not_found', 'Product not found', status: 404);
      }

      final row = rows.first;
      final imageRows = await _db.query(
        '''
          SELECT url, sort_order
          FROM product_images
          WHERE product_id = @id
          ORDER BY sort_order
        ''',
        substitutionValues: {'id': id},
      );
      final attrRows = await _db.query(
        '''
          SELECT key, value, unit
          FROM product_attributes
          WHERE product_id = @id
          ORDER BY key
        ''',
        substitutionValues: {'id': id},
      );

      return _json({
        'id': row[0].toString(),
        'sku': row[1],
        'name': row[2],
        'description': row[3],
        'category': {'id': row[8].toString(), 'name': row[9], 'slug': row[10]},
        'brand': row[11] == null
            ? null
            : {'id': row[11].toString(), 'name': row[12], 'slug': row[13]},
        'price': {
          'currency': row[14] ?? 'RUB',
          'price': _toDouble(row[15]),
          'oldPrice': _nullableDouble(row[16]),
        },
        'unit': row[4],
        'stock': {
          'qty': _toDouble(row[17]),
          'reservedQty': _toDouble(row[18]),
          'isInStock': _toDouble(row[17]) - _toDouble(row[18]) > 0,
        },
        'images': imageRows
            .map((img) => {'url': img[0], 'sortOrder': _toInt(img[1])})
            .toList(),
        'attributes': attrRows
            .map((attr) => {'key': attr[0], 'value': attr[1], 'unit': attr[2]})
            .toList(),
        'isActive': row[5] as bool,
        'createdAt': (row[6] as DateTime).toUtc().toIso8601String(),
        'updatedAt': (row[7] as DateTime).toUtc().toIso8601String(),
      });
    } catch (e) {
      return _serverError(e);
    }
  }

  Future<Response> _getProductFacets(Request request) async {
    try {
      final base = await _getProducts(request);
      if (base.statusCode != 200) {
        return base;
      }
      final payload =
          jsonDecode(await base.readAsString()) as Map<String, dynamic>;
      final items = (payload['items'] as List).cast<Map<String, dynamic>>();
      if (items.isEmpty) {
        return _json({
          'price': {'min': 0, 'max': 0},
          'brands': [],
          'attrs': {},
        });
      }

      final productIds = items
          .map((e) => "'${e['id'].toString().replaceAll("'", "''")}'")
          .join(',');
      final priceRows = await _db.query('''
          SELECT MIN(price), MAX(price)
          FROM prices
          WHERE product_id IN ($productIds)
        ''');

      final brandRows = await _db.query('''
          SELECT b.id, b.name, COUNT(*)
          FROM products p
          INNER JOIN brands b ON b.id = p.brand_id
          WHERE p.id IN ($productIds)
          GROUP BY b.id, b.name
          ORDER BY b.name
        ''');

      final attrRows = await _db.query('''
          SELECT key, value, COUNT(*)
          FROM product_attributes
          WHERE product_id IN ($productIds)
          GROUP BY key, value
          ORDER BY key, value
        ''');

      final attrs = <String, List<Map<String, dynamic>>>{};
      for (final row in attrRows) {
        final key = row[0] as String;
        attrs.putIfAbsent(key, () => []).add({
          'value': row[1],
          'count': _toInt(row[2]),
        });
      }

      return _json({
        'price': {
          'min': _toDouble(priceRows.first[0]),
          'max': _toDouble(priceRows.first[1]),
        },
        'brands': brandRows
            .map(
              (row) => {
                'id': row[0].toString(),
                'name': row[1],
                'count': _toInt(row[2]),
              },
            )
            .toList(),
        'attrs': attrs,
      });
    } catch (e) {
      return _serverError(e);
    }
  }

  Future<Response> _login(Request request) async {
    try {
      final body = await _decodeBody(request);
      final email = body['email']?.toString();
      final password = body['password']?.toString();

      final adminEmail =
          Platform.environment['ADMIN_EMAIL'] ?? 'admin@shop.local';
      final adminPassword = Platform.environment['ADMIN_PASSWORD'] ?? 'secret';

      if (email != adminEmail || password != adminPassword) {
        return _error('unauthorized', 'Invalid credentials', status: 401);
      }

      final jwt = JWT({'sub': email, 'role': 'admin'});
      final accessToken = jwt.sign(
        SecretKey(_jwtSecret),
        expiresIn: const Duration(hours: 1),
      );
      return _json({'accessToken': accessToken, 'expiresIn': 3600});
    } catch (e) {
      return _error('validation_error', 'Invalid request body', status: 400);
    }
  }

  Future<bool> _isAdmin(Request request) async {
    final authHeader = request.headers['authorization'];
    if (authHeader == null || !authHeader.startsWith('Bearer ')) {
      return false;
    }
    final token = authHeader.substring(7);
    try {
      final jwt = JWT.verify(token, SecretKey(_jwtSecret));
      return jwt.payload['role'] == 'admin';
    } on JWTException {
      return false;
    }
  }

  Future<Response?> _requireAdmin(Request request) async {
    if (await _isAdmin(request)) {
      return null;
    }
    return _error('unauthorized', 'Admin token required', status: 401);
  }

  Future<Response> _adminCreateCategory(Request request) async {
    final forbidden = await _requireAdmin(request);
    if (forbidden != null) {
      return forbidden;
    }

    try {
      final body = await _decodeBody(request);
      final id = _uuid.v4();
      await _db.query(
        '''
          INSERT INTO categories (id, parent_id, name, slug, is_active)
          VALUES (@id, @parent_id, @name, @slug, @is_active)
        ''',
        substitutionValues: {
          'id': id,
          'parent_id': body['parentId'],
          'name': body['name'],
          'slug': body['slug'],
          'is_active': body['isActive'] ?? true,
        },
      );
      return _json({'id': id}, status: 201);
    } catch (e) {
      return _serverError(e);
    }
  }

  Future<Response> _adminPatchCategory(Request request, String id) async {
    final forbidden = await _requireAdmin(request);
    if (forbidden != null) {
      return forbidden;
    }

    try {
      final body = await _decodeBody(request);
      await _db.query(
        '''
          UPDATE categories
          SET
            parent_id = COALESCE(@parent_id, parent_id),
            name = COALESCE(@name, name),
            slug = COALESCE(@slug, slug),
            is_active = COALESCE(@is_active, is_active)
          WHERE id = @id
        ''',
        substitutionValues: {
          'id': id,
          'parent_id': body['parentId'],
          'name': body['name'],
          'slug': body['slug'],
          'is_active': body['isActive'],
        },
      );
      return _json({'ok': true});
    } catch (e) {
      return _serverError(e);
    }
  }

  Future<Response> _adminDeleteCategory(Request request, String id) async {
    final forbidden = await _requireAdmin(request);
    if (forbidden != null) {
      return forbidden;
    }
    try {
      await _db.query(
        'DELETE FROM categories WHERE id = @id',
        substitutionValues: {'id': id},
      );
      return _json({'ok': true});
    } catch (e) {
      return _serverError(e);
    }
  }

  Future<Response> _adminCreateBrand(Request request) async {
    final forbidden = await _requireAdmin(request);
    if (forbidden != null) {
      return forbidden;
    }

    try {
      final body = await _decodeBody(request);
      final id = _uuid.v4();
      await _db.query(
        'INSERT INTO brands (id, name, slug) VALUES (@id, @name, @slug)',
        substitutionValues: {
          'id': id,
          'name': body['name'],
          'slug': body['slug'],
        },
      );
      return _json({'id': id}, status: 201);
    } catch (e) {
      return _serverError(e);
    }
  }

  Future<Response> _adminPatchBrand(Request request, String id) async {
    final forbidden = await _requireAdmin(request);
    if (forbidden != null) {
      return forbidden;
    }
    try {
      final body = await _decodeBody(request);
      await _db.query(
        '''
          UPDATE brands
          SET
            name = COALESCE(@name, name),
            slug = COALESCE(@slug, slug)
          WHERE id = @id
        ''',
        substitutionValues: {
          'id': id,
          'name': body['name'],
          'slug': body['slug'],
        },
      );
      return _json({'ok': true});
    } catch (e) {
      return _serverError(e);
    }
  }

  Future<Response> _adminDeleteBrand(Request request, String id) async {
    final forbidden = await _requireAdmin(request);
    if (forbidden != null) {
      return forbidden;
    }
    try {
      await _db.query(
        'DELETE FROM brands WHERE id = @id',
        substitutionValues: {'id': id},
      );
      return _json({'ok': true});
    } catch (e) {
      return _serverError(e);
    }
  }

  Future<Response> _adminCreateProduct(Request request) async {
    final forbidden = await _requireAdmin(request);
    if (forbidden != null) {
      return forbidden;
    }

    try {
      final body = await _decodeBody(request);
      final id = _uuid.v4();

      await _db.transaction((ctx) async {
        await ctx.query(
          '''
            INSERT INTO products (
              id, sku, name, description, category_id, brand_id, unit, is_active
            ) VALUES (
              @id, @sku, @name, @description, @category_id, @brand_id, @unit, @is_active
            )
          ''',
          substitutionValues: {
            'id': id,
            'sku': body['sku'],
            'name': body['name'],
            'description': body['description'],
            'category_id': body['categoryId'],
            'brand_id': body['brandId'],
            'unit': body['unit'],
            'is_active': body['isActive'] ?? true,
          },
        );

        final price = body['price'] as Map<String, dynamic>?;
        if (price != null) {
          await ctx.query(
            '''
              INSERT INTO prices (product_id, currency, price, old_price)
              VALUES (@product_id, @currency, @price, @old_price)
            ''',
            substitutionValues: {
              'product_id': id,
              'currency': price['currency'] ?? 'RUB',
              'price': price['price'] ?? 0,
              'old_price': price['oldPrice'],
            },
          );
        }

        final stock = body['stock'] as Map<String, dynamic>?;
        await ctx.query(
          '''
            INSERT INTO stocks (product_id, qty, reserved_qty)
            VALUES (@product_id, @qty, @reserved_qty)
          ''',
          substitutionValues: {
            'product_id': id,
            'qty': stock?['qty'] ?? 0,
            'reserved_qty': stock?['reservedQty'] ?? 0,
          },
        );

        final attributes = (body['attributes'] as List<dynamic>? ?? <dynamic>[])
            .whereType<Map<String, dynamic>>()
            .toList();
        for (final attr in attributes) {
          await ctx.query(
            '''
              INSERT INTO product_attributes (id, product_id, key, value, unit)
              VALUES (@id, @product_id, @key, @value, @unit)
            ''',
            substitutionValues: {
              'id': _uuid.v4(),
              'product_id': id,
              'key': attr['key'],
              'value': attr['value'],
              'unit': attr['unit'],
            },
          );
        }

        final images = (body['images'] as List<dynamic>? ?? <dynamic>[])
            .whereType<Map<String, dynamic>>()
            .toList();
        for (final image in images) {
          await ctx.query(
            '''
              INSERT INTO product_images (id, product_id, url, sort_order)
              VALUES (@id, @product_id, @url, @sort_order)
            ''',
            substitutionValues: {
              'id': _uuid.v4(),
              'product_id': id,
              'url': image['url'],
              'sort_order': image['sortOrder'] ?? 1,
            },
          );
        }
      });

      return _json({'id': id}, status: 201);
    } catch (e) {
      return _serverError(e);
    }
  }

  Future<Response> _adminPatchProduct(Request request, String id) async {
    final forbidden = await _requireAdmin(request);
    if (forbidden != null) {
      return forbidden;
    }

    try {
      final body = await _decodeBody(request);

      await _db.transaction((ctx) async {
        await ctx.query(
          '''
            UPDATE products
            SET
              sku = COALESCE(@sku, sku),
              name = COALESCE(@name, name),
              description = COALESCE(@description, description),
              category_id = COALESCE(@category_id, category_id),
              brand_id = COALESCE(@brand_id, brand_id),
              unit = COALESCE(@unit, unit),
              is_active = COALESCE(@is_active, is_active),
              updated_at = NOW()
            WHERE id = @id
          ''',
          substitutionValues: {
            'id': id,
            'sku': body['sku'],
            'name': body['name'],
            'description': body['description'],
            'category_id': body['categoryId'],
            'brand_id': body['brandId'],
            'unit': body['unit'],
            'is_active': body['isActive'],
          },
        );

        final price = body['price'];
        if (price is Map<String, dynamic>) {
          await ctx.query(
            '''
              INSERT INTO prices (product_id, currency, price, old_price)
              VALUES (@product_id, @currency, @price, @old_price)
              ON CONFLICT (product_id)
              DO UPDATE SET
                currency = EXCLUDED.currency,
                price = EXCLUDED.price,
                old_price = EXCLUDED.old_price
            ''',
            substitutionValues: {
              'product_id': id,
              'currency': price['currency'] ?? 'RUB',
              'price': price['price'] ?? 0,
              'old_price': price['oldPrice'],
            },
          );
        }

        final stock = body['stock'];
        if (stock is Map<String, dynamic>) {
          await ctx.query(
            '''
              INSERT INTO stocks (product_id, qty, reserved_qty)
              VALUES (@product_id, @qty, @reserved_qty)
              ON CONFLICT (product_id)
              DO UPDATE SET
                qty = EXCLUDED.qty,
                reserved_qty = EXCLUDED.reserved_qty
            ''',
            substitutionValues: {
              'product_id': id,
              'qty': stock['qty'] ?? 0,
              'reserved_qty': stock['reservedQty'] ?? 0,
            },
          );
        }

        final attributes = body['attributes'];
        if (attributes is List<dynamic>) {
          await ctx.query(
            'DELETE FROM product_attributes WHERE product_id = @id',
            substitutionValues: {'id': id},
          );
          for (final attr in attributes.whereType<Map<String, dynamic>>()) {
            await ctx.query(
              '''
                INSERT INTO product_attributes (id, product_id, key, value, unit)
                VALUES (@id, @product_id, @key, @value, @unit)
              ''',
              substitutionValues: {
                'id': _uuid.v4(),
                'product_id': id,
                'key': attr['key'],
                'value': attr['value'],
                'unit': attr['unit'],
              },
            );
          }
        }

        final images = body['images'];
        if (images is List<dynamic>) {
          await ctx.query(
            'DELETE FROM product_images WHERE product_id = @id',
            substitutionValues: {'id': id},
          );
          for (final image in images.whereType<Map<String, dynamic>>()) {
            await ctx.query(
              '''
                INSERT INTO product_images (id, product_id, url, sort_order)
                VALUES (@id, @product_id, @url, @sort_order)
              ''',
              substitutionValues: {
                'id': _uuid.v4(),
                'product_id': id,
                'url': image['url'],
                'sort_order': image['sortOrder'] ?? 1,
              },
            );
          }
        }
      });

      return _json({'ok': true});
    } catch (e) {
      return _serverError(e);
    }
  }

  Future<Response> _adminDeleteProduct(Request request, String id) async {
    final forbidden = await _requireAdmin(request);
    if (forbidden != null) {
      return forbidden;
    }

    try {
      await _db.query(
        'UPDATE products SET is_active = FALSE, updated_at = NOW() WHERE id = @id',
        substitutionValues: {'id': id},
      );
      return _json({'ok': true});
    } catch (e) {
      return _serverError(e);
    }
  }

  Future<Response> _adminPatchStock(Request request, String id) async {
    final forbidden = await _requireAdmin(request);
    if (forbidden != null) {
      return forbidden;
    }

    try {
      final body = await _decodeBody(request);
      final qty = body['qty'];
      final reservedQty = body['reservedQty'] ?? 0;
      if (qty is! num || reservedQty is! num || qty < 0 || reservedQty < 0) {
        return _error(
          'validation_error',
          'qty and reservedQty must be >= 0',
          status: 400,
        );
      }

      await _db.query(
        '''
          INSERT INTO stocks (product_id, qty, reserved_qty)
          VALUES (@product_id, @qty, @reserved_qty)
          ON CONFLICT (product_id)
          DO UPDATE SET
            qty = EXCLUDED.qty,
            reserved_qty = EXCLUDED.reserved_qty
        ''',
        substitutionValues: {
          'product_id': id,
          'qty': qty,
          'reserved_qty': reservedQty,
        },
      );

      return _json({'ok': true});
    } catch (e) {
      return _serverError(e);
    }
  }

  Future<Response> _adminPatchPrice(Request request, String id) async {
    final forbidden = await _requireAdmin(request);
    if (forbidden != null) {
      return forbidden;
    }

    try {
      final body = await _decodeBody(request);
      final price = body['price'];
      if (price is! num || price < 0) {
        return _error('validation_error', 'price must be >= 0', status: 400);
      }

      await _db.query(
        '''
          INSERT INTO prices (product_id, currency, price, old_price)
          VALUES (@product_id, @currency, @price, @old_price)
          ON CONFLICT (product_id)
          DO UPDATE SET
            currency = EXCLUDED.currency,
            price = EXCLUDED.price,
            old_price = EXCLUDED.old_price
        ''',
        substitutionValues: {
          'product_id': id,
          'currency': body['currency'] ?? 'RUB',
          'price': price,
          'old_price': body['oldPrice'],
        },
      );

      return _json({'ok': true});
    } catch (e) {
      return _serverError(e);
    }
  }
}

Response _json(Object body, {int status = 200}) {
  return Response(
    status,
    body: jsonEncode(body),
    headers: {'content-type': 'application/json; charset=utf-8'},
  );
}

Response _error(
  String code,
  String message, {
  int status = 400,
  List<Map<String, dynamic>> details = const <Map<String, dynamic>>[],
}) {
  return _json({
    'error': {'code': code, 'message': message, 'details': details},
  }, status: status);
}

Response _serverError(Object error) {
  return _error('internal_error', error.toString(), status: 500);
}

bool _boolArg(Request request, String name, bool defaultValue) {
  final value = request.url.queryParameters[name];
  if (value == null) {
    return defaultValue;
  }
  return value.toLowerCase() == 'true';
}

bool? _nullableBoolArg(Request request, String name) {
  final value = request.url.queryParameters[name];
  if (value == null) {
    return null;
  }
  return value.toLowerCase() == 'true';
}

int _intArg(
  Request request,
  String name,
  int defaultValue, {
  int min = 0,
  int? max,
}) {
  final raw = request.url.queryParameters[name];
  final value = raw == null ? defaultValue : int.tryParse(raw) ?? defaultValue;
  var safeValue = value;
  if (safeValue < min) {
    safeValue = min;
  }
  if (max != null && safeValue > max) {
    safeValue = max;
  }
  return safeValue;
}

Future<Map<String, dynamic>> _decodeBody(Request request) async {
  final body = await request.readAsString();
  if (body.trim().isEmpty) {
    return <String, dynamic>{};
  }
  return jsonDecode(body) as Map<String, dynamic>;
}

List<(String, String)> _parseAttrs(String? raw) {
  if (raw == null || raw.isEmpty) {
    return const [];
  }

  final attrs = <(String, String)>[];
  for (final chunk in raw.split(';')) {
    final entry = chunk.trim();
    if (entry.isEmpty || !entry.contains(':')) {
      continue;
    }
    final parts = entry.split(':');
    if (parts.length < 2) {
      continue;
    }
    attrs.add((parts.first.trim(), parts.sublist(1).join(':').trim()));
  }
  return attrs;
}

double _toDouble(Object? value) {
  if (value == null) {
    return 0;
  }
  if (value is num) {
    return value.toDouble();
  }
  return double.tryParse(value.toString()) ?? 0;
}

double? _nullableDouble(Object? value) {
  if (value == null) {
    return null;
  }
  if (value is num) {
    return value.toDouble();
  }
  return double.tryParse(value.toString());
}

int _toInt(Object? value) {
  if (value == null) {
    return 0;
  }
  if (value is int) {
    return value;
  }
  if (value is num) {
    return value.toInt();
  }
  return int.tryParse(value.toString()) ?? 0;
}