CREATE TABLE product (
    id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    customer_id BIGINT,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    category TEXT,
    brand TEXT,
    product TEXT,
    variant TEXT,
    size TEXT,
    barcode_ending TEXT,
    article TEXT,
    image_path TEXT
);

-- Trigger to update the updated_at column on row updates
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$
 LANGUAGE plpgsql;

CREATE TRIGGER update_product_updated_at
BEFORE UPDATE ON product
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();


ALTER TABLE product
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id)
REFERENCES customer(id)
ON DELETE SET NULL; 


INSERT INTO product (category, brand, product, variant, size)
SELECT DISTINCT 
    category,
    brand,
    product,
    variant,
    size
FROM sjreport;


UPDATE product
SET customer_id = (
    SELECT c.id
    FROM customer c
    WHERE UPPER(c.name) = UPPER(product.brand)
)
WHERE EXISTS (
    SELECT 1
    FROM customer c
    WHERE UPPER(c.name) = UPPER(product.brand)
);


select * from product where customer_id = 67



ALTER TABLE task
ADD product_id BIGINT;

UPDATE task t
SET product_id = (
    SELECT p.id
    FROM product p
    WHERE p.category = t.category
      AND p.brand = t.brand
      AND p.product = t.product
      AND p.variant = t.variant
      AND p.size = t.size
)
WHERE EXISTS (
    SELECT 1
    FROM product p
    WHERE p.category = t.category
      AND p.brand = t.brand
      AND p.product = t.product
      AND p.variant = t.variant
      AND p.size = t.size
);


ALTER TABLE task
ADD CONSTRAINT fk_product
FOREIGN KEY (product_id)
REFERENCES product(id)
ON DELETE SET NULL;  -- Optional: Set product_id to NULL if the referenced product is deleted


CREATE TABLE product_images (
    id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    product_id BIGINT NOT NULL,
    image_path TEXT NOT NULL,
    uploaded_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES product(id) ON DELETE CASCADE
);


CREATE TABLE product_ml (
    id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    product_id BIGINT NOT NULL,
    ml_image_path TEXT NOT NULL,
    ml_result JSONB,  -- Change to JSONB type
    FOREIGN KEY (product_id) REFERENCES product(id) ON DELETE CASCADE
);
