-- Drop existing tables
DROP TABLE IF EXISTS paint_card_samples;
DROP TABLE IF EXISTS product_paint;
DROP TABLE IF EXISTS paint;

-- Create paint table
CREATE TABLE paint (
    id               INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    color            TEXT NOT NULL UNIQUE,
    name             TEXT NOT NULL,
    path             character varying(255) NOT NULL UNIQUE,
    hex              character varying(7) NOT NULL UNIQUE,
    article_number   character varying(255) NOT NULL UNIQUE,
    paint_primer_id  INTEGER REFERENCES paint_primer(id) ON UPDATE CASCADE ON DELETE SET NULL,
    created          timestamp with time zone NOT NULL DEFAULT now(),
    updated          timestamp with time zone DEFAULT NULL
);

CREATE TRIGGER updated_timestamp BEFORE UPDATE ON paint FOR EACH ROW EXECUTE PROCEDURE updated_timestamp();

-- Insert data
INSERT INTO paint (color, name, path, hex, article_number, paint_primer_id)
VALUES
  ('NCS S 0500-N', 'White Dress', 'white-dress', '#FAF9F7', 'e94019', 1),
  ('NCS S 1002-R', 'Stratus', 'stratus', '#E9E5E5', 'e94020', 1),
  ('NCS S 2502-R', 'Bedrock', 'bedrock', '#C4C0C0', 'e94021', 1),
  ('NCS S 1015-Y20R', 'Creme Patissiere', 'creme-patissiere', '#F7DEB1', 'e94022', 1),
  ('NCS S 0520-R', 'Magdalena', 'magdalena', '#FFCECD', 'e94023', 1),
  ('NCS S 1040-R', 'Maeve', 'maeve', '#F29A9E', 'e94024', 1),
  ('NCS S 1005-B', 'Morning Fog', 'morning-fog', '#DBE4E9', 'e94025', 1),
  ('NCS S 2010-R80B', 'Lorna', 'lorna', '#B5C0CF', 'e94026', 1),
  ('NCS S 3020-R70B', 'Delfin', 'delfin', '#9099B3', 'e94027', 1),
  ('NCS S 4020-R70B', 'Date Night', 'date-night', '#77809E', 'e94028', 1),
  ('NCS S 1005-G20Y', 'Greta', 'greta', '#DEE7DC', 'e94029', 1),
  ('NCS S 2010-G30Y', 'Druid', 'druid', '#BCC9B', 'e94030', 1),
  ('NCS S 3005-G50Y', 'Tweed', 'tweed', '#AEB3A5', 'e94031', 1),
  ('NCS S 5005-G20Y', 'Bajou', 'bajou', '#7C867C', 'e94032', 1),
  ('NCS S 0502-Y20R', 'Vegas', 'vegas', '#F9T7ED', 'e94033', 1),
  ('NCS S 0907-Y50R', 'Harrison', 'harrison', '#F5E2D1', 'e94034', 1),
  ('NCS S 1510-Y60R', 'Boba Tea', 'boba-tea', '#E4CBBA', 'e94035', 1),
  ('NCS S 0530-Y20R', 'Sundae', 'sundae', '#FFD793', 'e94036', 1),
  ('NCS S 0530-Y50R', 'Georgia', 'georgia', '#FFC299', 'e94037', 1),
  ('NCS S 3020-Y50R', 'Cinnamon Sand', 'cinnamon-sand', '#CA9C7F', 'e94038', 1),
  ('NCS S 3030-Y90R', 'Old Money', 'old-money', '#BD7A74', 'e94039', 1),
  ('NCS S 1005-B20G', 'Daydream', 'daydream', '#D5E3E6', 'e94041', 1),
  ('NCS S 1510-B50G', 'Sea Foam', 'sea-foam', '#BED4D4', 'e94042', 1),
  ('NCS S 3010-B30G', 'Cliffside', 'cliffside', '#95AEB2', 'e94043', 1),
  ('NCS S 1010-G50Y', 'Glänta', 'glanta', '#E1E7CC', 'e94044', 1),
  ('NCS S 2005-G50Y', 'Rolling Hills', 'rolling-hills', '#C5CABC', 'e94045', 1),
  ('NCS S 3010-G60Y', 'Warren', 'warren', '#AEB298', 'e94046', 1),
  ('NCS S 1505-Y', 'Greige', 'greige', '#B0A999', 'e94047', 1), -- tmp will be adjusted later
  ('NCS S 1500-N', 'Jotuns ljusgrå', 'jotuns-ljusgra', '#DBDBD9', 'e94048', 1); -- tmp will be adjuster later


-- Create product_paint table
CREATE TABLE product_paint (
    product_id        INTEGER NOT NULL REFERENCES "product-products"(productid) ON UPDATE CASCADE ON DELETE CASCADE,
    paint_id          INTEGER NOT NULL REFERENCES paint(id) ON UPDATE CASCADE ON DELETE CASCADE,
    UNIQUE(product_id, paint_id)
);

--
CREATE TABLE paint_card_samples (
    id               INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    paint_id         INTEGER NOT NULL REFERENCES paint(id),
    stock            INTEGER NOT NULL DEFAULT 0,
    created          timestamp with time zone NOT NULL DEFAULT now(),
    updated          timestamp with time zone DEFAULT NULL
);

CREATE TRIGGER updated_timestamp BEFORE UPDATE ON paint_card_samples FOR EACH ROW EXECUTE PROCEDURE updated_timestamp();

-- For each paint row add a paint_card_sample row with stock 1
INSERT INTO paint_card_samples (paint_id, stock) SELECT id, 1 FROM paint;