CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL
    dark_mode BOOLEAN DEFAULT false
);

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price NUMERIC(10,2) NOT NULL,
    seller_id INT REFERENCES users(id)
);

ALTER TABLE public.products
ADD COLUMN user_id INT NOT NULL REFERENCES public.users(id);
ALTER TABLE public.products ADD COLUMN created_at TIMESTAMP DEFAULT NOW();

ALTER TABLE public.products ADD COLUMN description TEXT;
ALTER TABLE public.products ADD COLUMN category VARCHAR(100);
ALTER TABLE public.products ADD COLUMN image VARCHAR(255);
ALTER TABLE public.products ADD COLUMN image_url VARCHAR(600);
CREATE TABLE public.categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE
);
ALTER TABLE public.products
ADD COLUMN category_id INT REFERENCES public.categories(id);


ALTER TABLE users
ADD COLUMN dark_mode BOOLEAN DEFAULT false;

select * from users

CREATE TABLE IF NOT EXISTS orders (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    total NUMERIC(10,2) NOT NULL,
    status VARCHAR(20) DEFAULT 'pendiente',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS order_items (
    id SERIAL PRIMARY KEY,
    order_id INT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    product_id INT NOT NULL REFERENCES products(id) ON DELETE CASCADE,
    quantity INT NOT NULL,
    price NUMERIC(10,2) NOT NULL
);

CREATE TABLE comentarios (
    id SERIAL PRIMARY KEY,
    producto_id INT NOT NULL REFERENCES products(id) ON DELETE CASCADE,
    usuario_id INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    comentario TEXT NOT NULL,
    estrellas INT NOT NULL CHECK (estrellas >= 1 AND estrellas <= 5),
    fecha TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);


CREATE ROLE admin

ALTER TABLE users 
ADD COLUMN reset_token VARCHAR(255),
ADD COLUMN reset_expira TIMESTAMPTZ;

 
CREATE TABLE product_images (
    id SERIAL PRIMARY KEY,
    product_id INT REFERENCES products(id) ON DELETE CASCADE,
    image VARCHAR(255) NOT NULL
);



ALTER TABLE orders
ADD COLUMN payment_method VARCHAR(50);
ALTER TABLE users ADD COLUMN role VARCHAR(20) DEFAULT 'user';
UPDATE users SET role='admin' WHERE id=336;

SELECT id, name, role FROM users WHERE id = 1;
ALTER TABLE users ADD COLUMN created_at TIMESTAMP DEFAULT NOW();


CREATE TABLE password_resets (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    code VARCHAR(6) NOT NULL,
    expires_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);


DROP TABLE IF EXISTS password_resets;