📊 Schema de tablas core — estructura y relaciones

SELECT
    p.id_product,
    pl.name,
    pl.description_short,
    p.price,
    p.reference,
    sa.quantity AS stock,
    m.name AS manufacturer
FROM ps_product p
INNER JOIN ps_product_lang pl
    ON p.id_product = pl.id_product
    AND pl.id_lang = 1
    AND pl.id_shop = 1
LEFT JOIN ps_stock_available sa
    ON p.id_product = sa.id_product
    AND sa.id_product_attribute = 0
LEFT JOIN ps_manufacturer m
    ON p.id_manufacturer = m.id_manufacturer
WHERE p.active = 1
    AND p.visibility IN ('both', 'catalog')
ORDER BY p.date_add DESC
LIMIT 20;

SELECT
    o.id_order,
    o.reference,
    o.total_paid_tax_incl,
    o.date_add,
    c.firstname,
    c.lastname,
    c.email,
    osl.name AS current_status,
    oc.tracking_number
FROM ps_orders o
INNER JOIN ps_customer c ON o.id_customer = c.id_customer
LEFT JOIN ps_order_state os ON o.current_state = os.id_order_state
LEFT JOIN ps_order_state_lang osl
    ON os.id_order_state = osl.id_order_state
    AND osl.id_lang = 1
LEFT JOIN ps_order_carrier oc ON o.id_order = oc.id_order
WHERE o.valid = 1
ORDER BY o.date_add DESC
LIMIT 50;