Proyecto: Analítica de Ventas

CREATE TABLE categories (
    category_id  INT PRIMARY KEY,
    name         TEXT NOT NULL
);

CREATE TABLE products (
    product_id   INT PRIMARY KEY,
    category_id  INT NOT NULL REFERENCES categories(category_id),
    name         TEXT NOT NULL,
    unit_price   NUMERIC(10,2) NOT NULL   -- precio de catalogo actual
);

CREATE TABLE customers (
    customer_id  INT PRIMARY KEY,
    name         TEXT NOT NULL,
    country      TEXT,
    signup_date  DATE NOT NULL
);

CREATE TABLE orders (
    order_id     INT PRIMARY KEY,
    customer_id  INT NOT NULL REFERENCES customers(customer_id),
    order_date   DATE NOT NULL,
    status       TEXT NOT NULL            -- p.ej. 'completed', 'cancelled'
);

CREATE TABLE order_items (
    order_item_id INT PRIMARY KEY,
    order_id      INT NOT NULL REFERENCES orders(order_id),
    product_id    INT NOT NULL REFERENCES products(product_id),
    quantity      INT NOT NULL CHECK (quantity > 0),
    unit_price    NUMERIC(10,2) NOT NULL  -- precio congelado al comprar
);