DatabaseCreation: console_1.sql

-- noinspection SqlDialectInspectionForFile

CREATE TABLE owner
(
    id         SERIAL       NOT NULL,
    last_name  varchar(255) NOT NULL,
    first_name varchar(255) NOT NULL,
    phone      varchar(255),
    email      varchar(255),
    address    varchar(255),
    gender     varchar(1) CHECK (gender IN ('M', 'F')),
    PRIMARY KEY (id)
);

CREATE TABLE pet
(
    id              SERIAL       NOT NULL,
    name            varchar(255) NOT NULL,
    is_active       bool         NOT NULL DEFAULT true,
    type            varchar(255) CHECK (type IN ('mammal', 'bird', 'fish', 'amphibian', 'reptile')),
    breed           varchar(255),
    age             int4 CHECK (age >= 0),
    medical_history text,
    owner_id        int4         NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (owner_id) REFERENCES owner (id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE role
(
    id          SERIAL       NOT NULL,
    name        varchar(255) NOT NULL,
    description varchar(255),
    PRIMARY KEY (id)
);

CREATE TABLE employee
(
    id              SERIAL       NOT NULL,
    last_name       varchar(255) NOT NULL,
    first_name      varchar(255) NOT NULL,
    phone           varchar(255),
    email           varchar(255),
    address         varchar(255),
    date_employment date DEFAULT CURRENT_DATE,
    experience      int4 DEFAULT 0 CHECK (experience >= 0),
    role_id         int4,
    supervised_by   int4,
    gender          varchar(1) CHECK (gender IN ('M', 'F')),
    PRIMARY KEY (id),
    FOREIGN KEY (role_id) REFERENCES role (id)
        ON DELETE SET NULL
        ON UPDATE CASCADE,
    FOREIGN KEY (supervised_by) REFERENCES employee (id)
        ON DELETE SET NULL
        ON UPDATE CASCADE,
    CONSTRAINT check_not_self_supervisor CHECK (supervised_by != id)
);

CREATE TABLE appointment
(
    id               SERIAL       NOT NULL,
    date_appointment date         NOT NULL,
    reason           varchar(255) NOT NULL,
    phone            varchar(255),
    owner_id         int4,
    pet_id           int4,
    PRIMARY KEY (id),
    FOREIGN KEY (owner_id) REFERENCES owner (id)
        ON DELETE SET NULL
        ON UPDATE CASCADE,
    FOREIGN KEY (pet_id) REFERENCES pet (id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

CREATE TABLE specialization
(
    id             SERIAL       NOT NULL,
    specialization varchar(255) NOT NULL,
    license_number varchar(255) NOT NULL,
    employee_id    int4         NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (employee_id) REFERENCES employee (id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE certificate
(
    id               SERIAL       NOT NULL,
    certificate_name varchar(255) NOT NULL,
    employee_id      int4         NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (employee_id) REFERENCES employee (id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE examination_room
(
    id          SERIAL       NOT NULL,
    room_number varchar(255) NOT NULL,
    type        varchar(255) NOT NULL CHECK (type IN
                                             ('examination', 'surgery', 'radiology', 'laboratory', 'isolation ward',
                                              'patient ward')),
    capacity    int4 CHECK (capacity > 0),
    status      varchar(255) DEFAULT 'available' CHECK (status IN ('available', 'unavailable')),
    PRIMARY KEY (id)
);

CREATE TABLE examination
(
    id                  SERIAL       NOT NULL,
    date_examination    date         NOT NULL DEFAULT CURRENT_DATE,
    status              varchar(255) NOT NULL DEFAULT 'scheduled' CHECK (status IN ('scheduled', 'completed', 'cancelled')),
    description         text,
    appointment_id      int4,
    employee_id         int4,
    examination_room_id int4,
    PRIMARY KEY (id),
    FOREIGN KEY (appointment_id) REFERENCES appointment (id)
        ON DELETE SET NULL
        ON UPDATE CASCADE,
    FOREIGN KEY (employee_id) REFERENCES employee (id)
        ON DELETE SET NULL
        ON UPDATE CASCADE,
    FOREIGN KEY (examination_room_id) REFERENCES examination_room (id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

CREATE TABLE coupon
(
    id          SERIAL         NOT NULL,
    code        varchar(255)   NOT NULL UNIQUE,
    type        varchar(255)   NOT NULL CHECK (type IN ('fixed', 'percentage')),
    value       numeric(10, 2) NOT NULL CHECK (value >= 0),
    valid_from  date           NOT NULL DEFAULT CURRENT_DATE,
    valid_to    date           NOT NULL,
    usage_limit int4           NOT NULL DEFAULT 1,
    usage_count int4           NOT NULL DEFAULT 0,
    is_active   bool           NOT NULL DEFAULT true,
    min_total   numeric(10, 2)          DEFAULT 0 CHECK (min_total >= 0),
    PRIMARY KEY (id),
    CONSTRAINT check_dates CHECK (valid_to >= valid_from),
    CONSTRAINT check_usage CHECK (usage_limit >= 0 AND usage_count >= 0 AND usage_count <= usage_limit),
    CONSTRAINT check_percentage_value CHECK (
        type = 'fixed' OR (type = 'percentage' AND value <= 100)
        )
);

CREATE TABLE shop_item_category
(
    id        SERIAL       NOT NULL,
    name      varchar(255) NOT NULL,
    parent_id int4,
    PRIMARY KEY (id),
    FOREIGN KEY (parent_id) REFERENCES shop_item_category (id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

CREATE TABLE shop_item
(
    id                    SERIAL         NOT NULL,
    name                  varchar(255)   NOT NULL,
    price                 numeric(10, 2) NOT NULL CHECK (price >= 0),
    stock                 int4 DEFAULT 0 CHECK (stock >= 0),
    shop_item_category_id int4,
    PRIMARY KEY (id),
    FOREIGN KEY (shop_item_category_id) references shop_item_category (id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

CREATE TABLE shop_item_attribute
(
    id                    SERIAL       NOT NULL,
    name                  varchar(255) NOT NULL,
    data_type             varchar(255) NOT NULL CHECK (data_type IN ('text', 'integer', 'decimal', 'boolean', 'date')),
    shop_item_category_id int4         NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (shop_item_category_id) references shop_item_category (id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE shop_item_attribute_value
(
    id                     SERIAL       NOT NULL,
    value                  varchar(255) NOT NULL,
    notes                  varchar(255),
    shop_item_attribute_id int4         NOT NULL,
    shop_item_id           int4         NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (shop_item_attribute_id) references shop_item_attribute (id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    FOREIGN KEY (shop_item_id) references shop_item (id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE medicine
(
    id           SERIAL       NOT NULL,
    name         varchar(255) NOT NULL,
    manufacturer varchar(255),
    description  varchar(255),
    shop_item_id int4,
    PRIMARY KEY (id),
    FOREIGN KEY (shop_item_id) REFERENCES shop_item (id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

CREATE TABLE prescription
(
    id             SERIAL NOT NULL,
    examination_id int4   NOT NULL UNIQUE,
    date_start     date   NOT NULL,
    date_end       date   NOT NULL,
    description    text,
    PRIMARY KEY (id),
    FOREIGN KEY (examination_id) REFERENCES examination (id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT check_dates CHECK (date_end > date_start)
);

CREATE TABLE prescription_medicine
(
    prescription_id int4 NOT NULL,
    medicine_id     int4 NOT NULL,
    dosage          int4 NOT NULL CHECK (dosage > 0),
    num_days        int4 NOT NULL CHECK (num_days > 0),
    PRIMARY KEY (prescription_id, medicine_id),
    FOREIGN KEY (prescription_id) REFERENCES prescription (id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    FOREIGN KEY (medicine_id) REFERENCES medicine (id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE treatment_type
(
    id   SERIAL       NOT NULL,
    name varchar(255) NOT NULL CHECK (name IN ('prescription', 'vaccination', 'consultation', 'operation')),
    PRIMARY KEY (id)
);

CREATE TABLE treatment
(
    id                SERIAL NOT NULL,
    date_treatment    date   NOT NULL DEFAULT CURRENT_DATE,
    notes             text,
    treatment_type_id int4,
    examination_id    int4,
    PRIMARY KEY (id),
    FOREIGN KEY (treatment_type_id) REFERENCES treatment_type (id)
        ON DELETE SET NULL
        ON UPDATE CASCADE,
    FOREIGN KEY (examination_id) REFERENCES examination (id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

CREATE TABLE treatment_attribute
(
    id                SERIAL       NOT NULL,
    name              varchar(255) NOT NULL,
    data_type         varchar(255) NOT NULL CHECK (data_type IN ('text', 'integer', 'decimal', 'boolean', 'date')),
    treatment_type_id int4         NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (treatment_type_id) references treatment_type (id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE treatment_attribute_value
(
    id                     SERIAL       NOT NULL,
    value                  varchar(255) NOT NULL,
    notes                  varchar(255),
    treatment_attribute_id int4         NOT NULL,
    treatment_id           int4         NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (treatment_attribute_id) references treatment_attribute (id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    FOREIGN KEY (treatment_id) references treatment (id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE discount
(
    id           SERIAL         NOT NULL,
    type         varchar(255)   NOT NULL CHECK (type IN ('fixed', 'percentage')),
    value        numeric(10, 2) NOT NULL CHECK (value >= 0),
    description  varchar(255),
    date_from    date DEFAULT CURRENT_DATE,
    date_to      date,
    shop_item_id int4,
    PRIMARY KEY (id),
    FOREIGN KEY (shop_item_id) REFERENCES shop_item (id)
        ON DELETE SET NULL
        ON UPDATE CASCADE,
    CONSTRAINT check_dates CHECK (date_to > date_from)
);

CREATE TABLE invoice
(
    id           SERIAL         NOT NULL,
    date_invoice date           NOT NULL DEFAULT CURRENT_DATE,
    total        numeric(10, 2) NOT NULL CHECK (total >= 0),
    coupon_id    int4,
    owner_id     int4,
    PRIMARY KEY (id),
    FOREIGN KEY (coupon_id) REFERENCES coupon (id)
        ON DELETE SET NULL
        ON UPDATE CASCADE,
    FOREIGN KEY (owner_id) REFERENCES owner (id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

CREATE TABLE invoice_item
(
    num_item     int4           NOT NULL CHECK (num_item > 0),
    invoice_id   int4           NOT NULL,
    price        numeric(10, 2) NOT NULL CHECK (price >= 0),
    quantity     int4           NOT NULL DEFAULT 1 CHECK (quantity >= 0),
    type         varchar(255) CHECK (type IN ('shop_item', 'treatment')),
    shop_item_id int4,
    treatment_id int4,
    PRIMARY KEY (num_item, invoice_id),
    FOREIGN KEY (invoice_id) REFERENCES invoice (id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    FOREIGN KEY (shop_item_id) REFERENCES shop_item (id)
        ON DELETE SET NULL
        ON UPDATE CASCADE,
    FOREIGN KEY (treatment_id) REFERENCES treatment (id)
        ON DELETE SET NULL
        ON UPDATE CASCADE,
    CONSTRAINT check_type CHECK (
            (type = 'shop_item' AND shop_item_id IS NOT NULL AND treatment_id IS NULL) OR
            (type = 'treatment' AND treatment_id IS NOT NULL AND shop_item_id IS NULL)
            )
);

CREATE TABLE payment
(
    id           SERIAL         NOT NULL,
    date_payment date           NOT NULL DEFAULT CURRENT_DATE,
    amount       numeric(10, 2) NOT NULL CHECK (amount >= 0),
    method       varchar(255)   NOT NULL CHECK (method IN
                                                ('cash', 'debit card', 'credit card', 'digital wallet', 'other')),
    invoice_id   int4           NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (invoice_id) REFERENCES invoice (id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

-- ===============================
-- composite primary key in invoice_item (num_item, invoice_id)
-- ===============================
CREATE OR REPLACE FUNCTION generate_num_item()
    RETURNS TRIGGER AS
$$
BEGIN
    SELECT COALESCE(MAX(num_item), 0) + 1
    INTO NEW.num_item
    FROM invoice_item
    WHERE invoice_id = NEW.invoice_id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_generate_num_item
    BEFORE INSERT
    ON invoice_item
    FOR EACH ROW
EXECUTE FUNCTION generate_num_item();



-- DROP SCHEMA public CASCADE;
-- CREATE SCHEMA public;




-- temp tables for names, surnames and addresses
create table temp_male_names
(
    id    bigserial primary key,
    year  int4,
    name  text,
    count int4
);

SHOW data_directory;

COPY temp_male_names (year, name, count) FROM 'C:/Program Files/PostgreSQL/18/data/temp_files/baby_names_-_male.csv' DELIMITER ',' CSV HEADER;
UPDATE temp_male_names
SET name = TRIM(name);

create table temp_female_names
(
    id    bigserial primary key,
    year  int4,
    name  text,
    count int4
);

COPY temp_female_names (year, name, count) FROM 'C:/Program Files/PostgreSQL/18/data/temp_files/baby_names_-_female_.csv' DELIMITER ',' CSV HEADER;
UPDATE temp_female_names
SET name = TRIM(name);

create table temp_surnames
(
    id      bigserial primary key,
    year    int4,
    rank    text,
    surname text,
    number  int4
);

COPY temp_surnames (year, rank, surname, number) FROM 'C:/Program Files/PostgreSQL/18/data/temp_files/surnames.csv' DELIMITER ',' CSV HEADER;
UPDATE temp_surnames
SET surname = TRIM(surname);

CREATE TABLE temp_addresses
(
    id      bigserial primary key,
    address text,
    city    text,
    state   text,
    zip     text
);

COPY temp_addresses (address, city, state, zip) FROM 'C:/Program Files/PostgreSQL/18/data/temp_files/addresses.csv' DELIMITER ',' CSV HEADER;
UPDATE temp_addresses
SET address = TRIM(address);
UPDATE temp_addresses
SET city = TRIM(city);
UPDATE temp_addresses
SET state = TRIM(state);
UPDATE temp_addresses
SET zip = TRIM(zip);


-- ============================================================
-- role
-- ============================================================

INSERT INTO role (name, description)
VALUES ('Manager', 'Oversees clinic operations'),
       ('Veterinarian', 'Licensed veterinarian responsible for examinations and treatments'),
       ('Veterinary Assistant', 'Assists veterinarians during procedures'),
       ('Receptionist', 'Handles appointments and client communication');

-- ============================================================
-- employee
-- (50 employees: 1 manager, 9 doctors, 35 vet assistants, 5 receptionists)
-- ============================================================

WITH male_names_sample AS (SELECT name
                           FROM temp_male_names
                           ORDER BY random()
                           LIMIT 1000),
     female_names_sample AS (SELECT name
                             FROM temp_female_names
                             ORDER BY random()
                             LIMIT 1000),
     male_pool AS (SELECT fn.name    AS first_name,
                          ln.surname AS last_name,
                          'M'        AS gender
                   FROM male_names_sample fn
                            CROSS JOIN temp_surnames ln
                   ORDER BY random()
                   LIMIT 25),
     female_pool AS (SELECT fn.name    AS first_name,
                            ln.surname AS last_name,
                            'F'        AS gender
                     FROM female_names_sample fn
                              CROSS JOIN temp_surnames ln
                     ORDER BY random()
                     LIMIT 25),
     all_names AS (SELECT first_name, last_name, gender
                   FROM male_pool
                   UNION ALL
                   SELECT first_name, last_name, gender
                   FROM female_pool),
     numbered AS (SELECT first_name,
                         last_name,
                         gender,
                         row_number() OVER (ORDER BY random()) AS rn
                  FROM all_names),
     shuffled_addresses AS (SELECT address || ', ' || city || ', ' || state || ' ' || zip AS full_address,
                                   row_number() OVER (ORDER BY random())                  AS rn
                            FROM temp_addresses),
     shuffled_phones AS (SELECT ((row_number() OVER (ORDER BY random()) % 9) + 1)::text                        AS d1,
                                lpad(((row_number() OVER (ORDER BY random()) * 73) % 900 + 100)::text, 3, '0') AS d2,
                                lpad(((row_number() OVER (ORDER BY random()) * 97) % 900 + 100)::text, 3, '0') AS d3,
                                row_number() OVER (ORDER BY random())                                          AS rn
                         FROM generate_series(1, 50))
INSERT
INTO employee (first_name, last_name, phone, email, address, gender, date_employment, role_id, supervised_by)
SELECT n.first_name,
       n.last_name,
       '+389 7' || p.d1 || ' ' || p.d2 || ' ' || p.d3                     AS phone,
       lower(n.first_name) || '.' || lower(n.last_name) || '@pawcare.com' AS email,
       a.full_address                                                     AS address,
       n.gender,
       (now() - interval '1 day' * floor(random() * 3650))::date          AS date_employment,
       CASE
           WHEN n.rn = 1 THEN 1 -- manager
           WHEN n.rn <= 10 THEN 2 -- veterinarians
           WHEN n.rn <= 45 THEN 3 -- vet assistants (rn 11-45 = 35 employees)
           ELSE 4 -- receptionists (rn 46-50 = 5 employees)
           END                                                            AS role_id,
       NULL                                                               AS supervised_by
FROM numbered n
         JOIN shuffled_phones p ON p.rn = n.rn
         JOIN shuffled_addresses a ON a.rn = n.rn
ORDER BY n.rn;

-- experience for all employees: at least years since employment
UPDATE employee
SET experience = (
    extract(year from age(now(), date_employment))
        + floor(random() * 5)
    )::int
WHERE role_id IN (3, 4); -- vet assistants and receptionists: 0-5 extra years

-- vets get more experience
UPDATE employee
SET experience = (
    extract(year from age(now(), date_employment))
        + floor(random() * 15) + 5
    )::int
WHERE role_id = 2; -- veterinarians: at least 5 extra years on top

-- manager gets more experience
UPDATE employee
SET experience = (
    extract(year from age(now(), date_employment))
        + floor(random() * 10) + 8
    )::int
WHERE role_id = 1;

-- supervised_by updates
UPDATE employee
SET supervised_by = 1
WHERE role_id = 2; -- for doctors: manager

UPDATE employee
SET supervised_by = (floor(random() * 9) + 2)::int
WHERE role_id = 3; -- for vet assistants: random doctor (ids 2-10)

UPDATE employee
SET supervised_by = 1
WHERE role_id = 4; -- for receptionists: manager

-- ============================================================
-- owner
-- 400 owners
-- ============================================================

WITH male_names_sample AS (SELECT name
                           FROM temp_male_names
                           ORDER BY random()
                           LIMIT 2000),
     female_names_sample AS (SELECT name
                             FROM temp_female_names
                             ORDER BY random()
                             LIMIT 2000),
     male_pool AS (SELECT fn.name    AS first_name,
                          ln.surname AS last_name,
                          'M'        AS gender
                   FROM male_names_sample fn
                            CROSS JOIN temp_surnames ln
                   ORDER BY random()),
     female_pool AS (SELECT fn.name    AS first_name,
                            ln.surname AS last_name,
                            'F'        AS gender
                     FROM female_names_sample fn
                              CROSS JOIN temp_surnames ln
                     ORDER BY random()),
     all_names AS (SELECT first_name, last_name, gender
                   FROM male_pool
                   UNION ALL
                   SELECT first_name, last_name, gender
                   FROM female_pool),
     numbered AS (SELECT first_name,
                         last_name,
                         gender,
                         row_number() OVER (ORDER BY random()) AS rn
                  FROM all_names),
     shuffled_addresses AS (SELECT address || ', ' || city || ', ' || state || ' ' || zip AS full_address,
                                   row_number() OVER (ORDER BY random())                  AS rn
                            FROM temp_addresses),
     shuffled_phones AS (SELECT ((row_number() OVER (ORDER BY random()) % 9) + 1)::text                        AS d1,
                                lpad(((row_number() OVER (ORDER BY random()) * 73) % 900 + 100)::text, 3, '0') AS d2,
                                lpad(((row_number() OVER (ORDER BY random()) * 97) % 900 + 100)::text, 3, '0') AS d3,
                                row_number() OVER (ORDER BY random())                                          AS rn
                         FROM generate_series(1, 400))
INSERT
INTO owner (first_name, last_name, phone, email, address, gender)
SELECT n.first_name,
       n.last_name,
       '+389 7' || p.d1 || ' ' || p.d2 || ' ' || p.d3                   AS phone,
       lower(n.first_name) || '.' || lower(n.last_name) || '@gmail.com' AS email,
       a.full_address                                                   AS address,
       n.gender
FROM numbered n
         JOIN shuffled_phones p ON p.rn = n.rn
         JOIN shuffled_addresses a ON a.rn = ((n.rn - 1) % (SELECT count(*) FROM temp_addresses) + 1)
ORDER BY random()
LIMIT 400;

-- contraint for email and phone on owner and employee:
ALTER TABLE owner
    ADD CONSTRAINT check_email
        CHECK (email ~* '^[^@\s]+\.[^@\s]+@[^@\s]+\.[^@\s]+$'),
    ADD CONSTRAINT check_phone
        CHECK (phone ~ '^\+389 7[0-9] [0-9]{3} [0-9]{3}$');

ALTER TABLE employee
    ADD CONSTRAINT check_email
        CHECK (email ~* '^[^@\s]+\.[^@\s]+@[^@\s]+\.[^@\s]+$'),
    ADD CONSTRAINT check_phone
        CHECK (phone ~ '^\+389 7[0-9] [0-9]{3} [0-9]{3}$');




-- -- fix for foreign key in medicine (Small flaw)
--
-- ALTER TABLE medicine
--     DROP CONSTRAINT IF EXISTS medicine_shop_item_id_fkey;
--
-- ALTER TABLE medicine
--     ADD CONSTRAINT medicine_shop_item_id_fkey
--         FOREIGN KEY (shop_item_id) REFERENCES shop_item (id)
--             ON DELETE SET NULL
--             ON UPDATE CASCADE;


-- fix for foreign key in invoice_item

-- ALTER TABLE invoice_item
--     DROP CONSTRAINT IF EXISTS check_type;
--
-- ALTER TABLE invoice_item
--     ADD CONSTRAINT check_type
--         CHECK (
--             (type = 'shop_item' AND shop_item_id IS NOT NULL AND treatment_id IS NULL) OR
--             (type = 'treatment' AND treatment_id IS NOT NULL AND shop_item_id IS NULL)
--             );

-- unique coupon code

-- ALTER TABLE coupon
--     ADD CONSTRAINT unique_coupon_code UNIQUE (code);

-- fix for usage_limit in coupon

-- ALTER TABLE coupon
--     DROP CONSTRAINT IF EXISTS check_usage;
--
-- ALTER TABLE coupon
--     ADD CONSTRAINT check_usage
--         CHECK (
--             usage_limit > 0 AND
--             usage_count >= 0 AND
--             usage_count <= usage_limit
--             );


-- small fixes in owner

ALTER TABLE owner
    ADD CONSTRAINT unique_owner_email UNIQUE (email);

ALTER TABLE employee
    ADD CONSTRAINT unique_employee_email UNIQUE (email);

ALTER TABLE owner
    ALTER COLUMN email SET NOT NULL;

ALTER TABLE employee
    ALTER COLUMN email SET NOT NULL;


-- ============================================================
-- pet
-- ============================================================


CREATE TABLE temp_breeds_mammal (
     id bigserial primary key,
     breed_name text
);

CREATE TABLE temp_breeds_bird (
     id bigserial primary key,
     breed_name text
);

CREATE TABLE temp_breeds_fish (
    id bigserial primary key,
    breed_name text
);

CREATE TABLE temp_breeds_amphibian (
     id bigserial primary key,
     breed_name text
);

CREATE TABLE temp_breeds_reptile (
     id bigserial primary key,
     breed_name text
);

CREATE TABLE temp_pet_names (
    id bigserial primary key,
    name text
);

CREATE TABLE temp_medical_history (
     id bigserial primary key,
     history text
);

COPY temp_breeds_mammal (breed_name)
    FROM 'C:/Program Files/PostgreSQL/18/data/temp_files/mammal_pet_breeds.csv'
    DELIMITER ',' CSV HEADER;

COPY temp_breeds_bird (breed_name)
    FROM 'C:/Program Files/PostgreSQL/18/data/temp_files/pet_birds.csv'
    DELIMITER ',' CSV HEADER;

COPY temp_breeds_fish (breed_name)
    FROM 'C:/Program Files/PostgreSQL/18/data/temp_files/pet_fish_breeds.csv'
    DELIMITER ',' CSV HEADER;

COPY temp_breeds_amphibian (breed_name)
    FROM 'C:/Program Files/PostgreSQL/18/data/temp_files/pet_amphibians.csv'
    DELIMITER ',' CSV HEADER;

COPY temp_breeds_reptile (breed_name)
    FROM 'C:/Program Files/PostgreSQL/18/data/temp_files/pet_reptiles.csv'
    DELIMITER ',' CSV HEADER;

COPY temp_pet_names (name)
    FROM 'C:/Program Files/PostgreSQL/18/data/temp_files/pet_names.csv'
    DELIMITER ',' CSV HEADER;

COPY temp_medical_history (history)
    FROM 'C:/Program Files/PostgreSQL/18/data/temp_files/pet_medical_history.csv'
    DELIMITER ',' CSV HEADER;


--altering column age for pet (so it works for both months and years)

-- ALTER TABLE pet
--     ALTER COLUMN age TYPE int4;

ALTER TABLE pet
    ADD COLUMN age_display text GENERATED ALWAYS AS (
        CASE
            WHEN age IS NULL THEN 'Unknown'
            WHEN age < 12    THEN age || ' months'
            WHEN age < 24    THEN '1 year'
            ELSE (age / 12) || ' years'
            END
        ) STORED;


WITH base AS (
    SELECT generate_series(1, 700) AS rn
),

     typed AS (
         SELECT
             rn,
             CASE
                 WHEN r < 0.60 THEN 'mammal'
                 WHEN r < 0.70 THEN 'bird'
                 WHEN r < 0.80 THEN 'reptile'
                 WHEN r < 0.90 THEN 'fish'
                 ELSE 'amphibian'
                 END AS type
         FROM (
                  SELECT rn, random() AS r
                  FROM base
              ) x
     ),

     breed_counts AS (
         SELECT
             (SELECT count(*) FROM temp_breeds_mammal)    AS mammal_cnt,
             (SELECT count(*) FROM temp_breeds_bird)      AS bird_cnt,
             (SELECT count(*) FROM temp_breeds_fish)      AS fish_cnt,
             (SELECT count(*) FROM temp_breeds_reptile)   AS reptile_cnt,
             (SELECT count(*) FROM temp_breeds_amphibian) AS amphibian_cnt,
             (SELECT count(*) FROM temp_pet_names)        AS names_cnt,
             (SELECT count(*) FROM temp_medical_history)  AS history_cnt
     ),

     owner_pool AS (
         SELECT
             id,
             row_number() OVER (ORDER BY random()) AS rn
         FROM owner
     ),

     owner_counts AS (
         SELECT count(*) AS cnt FROM owner
     ),

     names AS (
         SELECT name, row_number() OVER () AS rn
         FROM (SELECT name FROM temp_pet_names ORDER BY random()) t
     ),

     history AS (
         SELECT history, row_number() OVER () AS rn
         FROM (SELECT history FROM temp_medical_history ORDER BY random()) t
     ),

     mammal_breeds AS (
         SELECT breed_name, row_number() OVER () AS rn
         FROM (SELECT breed_name FROM temp_breeds_mammal ORDER BY random()) t
     ),

     bird_breeds AS (
         SELECT breed_name, row_number() OVER () AS rn
         FROM (SELECT breed_name FROM temp_breeds_bird ORDER BY random()) t
     ),

     fish_breeds AS (
         SELECT breed_name, row_number() OVER () AS rn
         FROM (SELECT breed_name FROM temp_breeds_fish ORDER BY random()) t
     ),

     reptile_breeds AS (
         SELECT breed_name, row_number() OVER () AS rn
         FROM (SELECT breed_name FROM temp_breeds_reptile ORDER BY random()) t
     ),

     amphibian_breeds AS (
         SELECT breed_name, row_number() OVER () AS rn
         FROM (SELECT breed_name FROM temp_breeds_amphibian ORDER BY random()) t
     ),

     owner_assignment AS (
         SELECT
             t.rn AS pet_rn,
             CASE
                 WHEN t.rn <= 400
                     THEN (SELECT id FROM owner_pool op WHERE op.rn = t.rn)
                 ELSE
                     (SELECT id FROM owner_pool op
                      WHERE op.rn = (abs(hashtext('owner_' || t.rn)) % (SELECT cnt FROM owner_counts)) + 1)
                 END AS owner_id
         FROM typed t
     )

INSERT INTO pet (
    name,
    is_active,
    type,
    breed,
    age,
    medical_history,
    owner_id
)

SELECT
    n.name,
    (random() < 0.85) AS is_active,
    t.type,

    CASE t.type
        WHEN 'mammal'    THEN mb.breed_name
        WHEN 'bird'      THEN bb.breed_name
        WHEN 'fish'      THEN fb.breed_name
        WHEN 'reptile'   THEN rb.breed_name
        WHEN 'amphibian' THEN ab.breed_name
        END AS breed,

    CASE t.type
        WHEN 'mammal'    THEN floor(random() * 240)::int
        WHEN 'bird'      THEN floor(random() * 720)::int
        WHEN 'reptile'   THEN floor(random() * 600)::int
        WHEN 'fish'      THEN floor(random() * 240)::int
        WHEN 'amphibian' THEN floor(random() * 360)::int
        END AS age,

    h.history,
    oa.owner_id

FROM typed t
         CROSS JOIN breed_counts bc
         JOIN owner_assignment oa ON oa.pet_rn = t.rn
         JOIN owner_counts oc ON true

         JOIN names n
              ON n.rn = ((abs(hashtext(t.rn::text || 'pet_name_' || t.rn)) % bc.names_cnt) + 1)

         JOIN history h
              ON h.rn = ((abs(hashtext(t.rn::text || 'pet_history_' || t.rn)) % bc.history_cnt) + 1)

         LEFT JOIN mammal_breeds mb
                   ON t.type = 'mammal'
                       AND mb.rn = ((abs(hashtext(t.rn::text || 'mammal_' || t.rn)) % bc.mammal_cnt) + 1)

         LEFT JOIN bird_breeds bb
                   ON t.type = 'bird'
                       AND bb.rn = ((abs(hashtext(t.rn::text || 'bird_' || t.rn)) % bc.bird_cnt) + 1)

         LEFT JOIN fish_breeds fb
                   ON t.type = 'fish'
                       AND fb.rn = ((abs(hashtext(t.rn::text || 'fish_' || t.rn)) % bc.fish_cnt) + 1)

         LEFT JOIN reptile_breeds rb
                   ON t.type = 'reptile'
                       AND rb.rn = ((abs(hashtext(t.rn::text || 'reptile_' || t.rn)) % bc.reptile_cnt) + 1)

         LEFT JOIN amphibian_breeds ab
                   ON t.type = 'amphibian'
                       AND ab.rn = ((abs(hashtext(t.rn::text || 'amphibian_' || t.rn)) % bc.amphibian_cnt) + 1);

-- fix for small mistakes with csv

UPDATE pet
SET medical_history = 'Dental exam completed'
WHERE id = 12;

UPDATE pet
SET medical_history = 'Eye exam completed'
WHERE id = 57;


-- ============================================================
-- certificate
-- ============================================================

ALTER TABLE certificate
ADD column category VARCHAR(50);


CREATE TABLE temp_certificate_names (
    id bigserial primary key,
    name text,
    category text

);

COPY temp_certificate_names (name,category)
    FROM 'C:/Program Files/PostgreSQL/18/data/temp_files/vet_assistance_certificates.csv'
    DELIMITER ',' CSV HEADER;

INSERT INTO certificate (certificate_name, category, employee_id)
SELECT
    t.name,
    t.category,
    e.id
FROM
    (SELECT name, category, row_number() OVER (ORDER BY random()) AS rn
     FROM temp_certificate_names) t,
    (SELECT id, row_number() OVER (ORDER BY random()) AS rn,
            count(*) OVER () AS total
     FROM employee
     WHERE role_id = 3) e
WHERE e.rn = (t.rn % (SELECT count(*) FROM employee WHERE role_id = 3)) + 1;

-- ============================================================
-- specialization
-- ============================================================

CREATE TABLE temp_spec_data (
    id bigserial primary key,
    name text,
    number text

);

COPY temp_spec_data (name,number)
    FROM 'C:/Program Files/PostgreSQL/18/data/temp_files/vet_specializations.csv'
    DELIMITER ',' CSV HEADER;

-- Step 1: Assign Diplomate specializations to all role_id=2 employees
INSERT INTO specialization (specialization, license_number, employee_id)
SELECT
    t.name,
    t.number,
    e.id
FROM
    (SELECT name, number, row_number() OVER (ORDER BY random()) AS rn
     FROM temp_spec_data
     WHERE name ILIKE '%diplomate%') t,
    (SELECT id, row_number() OVER (ORDER BY random()) AS rn
     FROM employee
     WHERE role_id = 2) e
WHERE e.rn = (t.rn % (SELECT count(*) FROM employee WHERE role_id = 2)) + 1;


INSERT INTO specialization (specialization, license_number, employee_id)
SELECT
    t.name,
    t.number,
    e.id
FROM
    (SELECT name, number, row_number() OVER (ORDER BY random()) AS rn
     FROM temp_spec_data
     WHERE name ILIKE '%master%') t,
    (SELECT id, row_number() OVER (ORDER BY random()) AS rn
     FROM employee
     WHERE role_id = 2
       AND id IN (SELECT employee_id FROM specialization WHERE specialization ILIKE '%diplomate%')
     LIMIT (SELECT count(*)/2 FROM employee WHERE role_id = 2)
    ) e
WHERE e.rn = (t.rn % (SELECT GREATEST(count(*)/2, 1) FROM employee WHERE role_id = 2)) + 1;


-- ============================================================
-- coupon
-- ============================================================

INSERT INTO coupon (
    code,
    type,
    value,
    valid_from,
    valid_to,
    usage_limit,
    usage_count,
    is_active,
    min_total
)
SELECT
    chr(65 + (gs % 26)) ||
    chr(65 + ((gs / 26) % 26)) ||
    chr(65 + ((gs / 676) % 26)) ||
    '-' || lpad((gs % 1000)::text, 3, '0') AS code,

    CASE
        WHEN random() < 0.5 THEN 'fixed'
        ELSE 'percentage'
        END AS type,

    CASE
        WHEN random() < 0.5
            THEN round((random() * 50 + 5)::numeric, 2)
        ELSE round((random() * 80 + 1)::numeric, 2)
        END AS value,

    CURRENT_DATE - (random() * 30)::int,
    CURRENT_DATE + (random() * 60)::int,

    u.usage_limit,
    (random() * u.usage_limit)::int AS usage_count,

    (random() < 0.8),
    round((random() * 200)::numeric, 2)

FROM generate_series(1, 1000) gs
         CROSS JOIN LATERAL (
    SELECT (random() * 90 + 10)::int AS usage_limit
    ) u;


-- ============================================================
-- prescription
-- ============================================================

CREATE TABLE temp_prescription_advice(
    id bigserial primary key,
    advice text
);

COPY temp_prescription_advice (advice)
    FROM 'C:/Program Files/PostgreSQL/18/data/temp_files/pet_prescriptions_advice.csv'
    DELIMITER ',' CSV HEADER;


-- ============================================================
-- examination_room
-- ============================================================

INSERT INTO examination_room (room_number, type, capacity, status)
SELECT
    gs::text AS room_number,
    CASE
        WHEN r < 0.55 THEN 'examination'
        WHEN r < 0.75 THEN 'surgery'
        WHEN r < 0.90 THEN 'patient ward'
        WHEN r < 0.95 THEN 'radiology'
        ELSE 'laboratory'
        END AS type,

    CASE
        WHEN r < 0.55 THEN 1
        WHEN r < 0.75 THEN 1
        WHEN r < 0.90 THEN 10 + (random() * 10)::int
        WHEN r < 0.95 THEN 2 + (random() * 3)::int
        ELSE 2 + (random() * 4)::int
        END AS capacity,

    'available' AS status
FROM (
         SELECT gs, random() AS r
         FROM generate_series(1, 15) gs
     ) x;


-- ============================================================
-- medicine & shop_item
-- ============================================================
--   1. shop_item_category  → add 'Medicine' category
--   2. shop_item           → ~60 medicines available in the shop
--   3. medicine            → ~120 total: ~60 linked to shop, ~60 prescription-only
--   4. prescription        → one per qualifying examination
--   5. prescription_medicine → M:N join with dosage/num_days
-- ============================================================

-- ============================================================
-- shop_item_category
-- ============================================================

INSERT INTO shop_item_category (name, parent_id)
VALUES ('Pet Supplies', NULL)
ON CONFLICT DO NOTHING;

INSERT INTO shop_item_category (name, parent_id)
SELECT 'Medicine', id
FROM shop_item_category
WHERE name = 'Pet Supplies'
ON CONFLICT DO NOTHING;


-- ============================================================
-- shop_item - medicines sold in shop (~60 items)
-- shop_item_category_id pointing to 'Medicine' category
-- ============================================================

INSERT INTO shop_item (name, price, stock, shop_item_category_id)
SELECT
    med_name,
    round((3.50 + random() * 96.50)::numeric, 2)       AS price,
    (floor(random() * 150) + 5)::int                    AS stock,
    (SELECT id FROM shop_item_category WHERE name = 'Medicine') AS shop_item_category_id
FROM (VALUES
    ('Amoxicillin 250mg Tablets'),
    ('Amoxicillin 500mg Capsules'),
    ('Metronidazole 200mg Tablets'),
    ('Metronidazole 400mg Tablets'),
    ('Doxycycline 100mg Capsules'),
    ('Enrofloxacin 50mg Tablets'),
    ('Enrofloxacin 150mg Tablets'),
    ('Trimethoprim-Sulfa 480mg Tablets'),
    ('Cephalexin 250mg Capsules'),
    ('Cephalexin 500mg Capsules'),
    ('Prednisolone 5mg Tablets'),
    ('Prednisolone 20mg Tablets'),
    ('Dexamethasone 0.5mg Tablets'),
    ('Methylprednisolone 4mg Tablets'),
    ('Hydrocortisone 10mg Tablets'),
    ('Furosemide 40mg Tablets'),
    ('Spironolactone 25mg Tablets'),
    ('Enalapril 5mg Tablets'),
    ('Atenolol 25mg Tablets'),
    ('Digoxin 0.125mg Tablets'),
    ('Carprofen 50mg Chewable Tablets'),
    ('Meloxicam 1mg Tablets'),
    ('Meloxicam Oral Suspension 1.5mg/ml'),
    ('Tramadol 50mg Tablets'),
    ('Gabapentin 100mg Capsules'),
    ('Gabapentin 300mg Capsules'),
    ('Phenobarbital 30mg Tablets'),
    ('Potassium Bromide 325mg Capsules'),
    ('Levetiracetam 250mg Tablets'),
    ('Omeprazole 20mg Capsules'),
    ('Famotidine 20mg Tablets'),
    ('Metoclopramide 10mg Tablets'),
    ('Ondansetron 4mg Tablets'),
    ('Maropitant 16mg Tablets'),
    ('Sucralfate 1g Tablets'),
    ('Lactulose Oral Solution 667mg/ml'),
    ('Loperamide 2mg Capsules'),
    ('Tylosin 250mg Powder'),
    ('Clindamycin 75mg Capsules'),
    ('Clindamycin 150mg Capsules'),
    ('Ketoconazole 200mg Tablets'),
    ('Fluconazole 50mg Capsules'),
    ('Itraconazole 100mg Capsules'),
    ('Fenbendazole 150mg Granules'),
    ('Pyrantel Pamoate Oral Suspension'),
    ('Ivermectin 1% Oral Solution'),
    ('Milbemycin Oxime 2.3mg Tablets'),
    ('Praziquantel 50mg Tablets'),
    ('Doxycycline Hyclate 100mg Tablets'),
    ('Chloramphenicol 250mg Capsules'),
    ('Cyclosporine 25mg Capsules'),
    ('Cyclosporine 100mg Capsules'),
    ('Apoquel 3.6mg Tablets'),
    ('Apoquel 16mg Tablets'),
    ('Hydroxyzine 25mg Tablets'),
    ('Diphenhydramine 25mg Capsules'),
    ('Vitamin B12 Injection 1000mcg/ml'),
    ('Iron Dextran 100mg/ml Injection'),
    ('Calcium Gluconate 10% Injection'),
    ('Saline 0.9% Flush Solution 10ml')
) AS t(med_name);


-- ============================================================
-- medicine table
--    ~60 medicines that exist in the shop (linked via shop_item_id fk)
--    ~60 medicines that are prescription-only (shop_item_id IS NULL)
-- ============================================================

-- a. medicines that are in the shop - match by name to shop_item
INSERT INTO medicine (name, manufacturer, description, shop_item_id)
SELECT
    si.name,
    mfr,
    descr,
    si.id AS shop_item_id
FROM shop_item si
JOIN shop_item_category sic ON sic.id = si.shop_item_category_id
JOIN (VALUES
    ('Amoxicillin 250mg Tablets',         'Zoetis Inc.',              'Broad-spectrum penicillin antibiotic for bacterial infections'),
    ('Amoxicillin 500mg Capsules',        'Zoetis Inc.',              'Higher-dose penicillin for severe or systemic bacterial infections'),
    ('Metronidazole 200mg Tablets',       'Norbrook Laboratories',    'Antibiotic and antiprotozoal for GI and anaerobic infections'),
    ('Metronidazole 400mg Tablets',       'Norbrook Laboratories',    'Higher-dose metronidazole for systemic anaerobic infections'),
    ('Doxycycline 100mg Capsules',        'Boehringer Ingelheim',     'Tetracycline antibiotic effective against intracellular pathogens'),
    ('Enrofloxacin 50mg Tablets',         'Bayer Animal Health',      'Fluoroquinolone antibiotic for urinary and soft tissue infections'),
    ('Enrofloxacin 150mg Tablets',        'Bayer Animal Health',      'Higher-dose fluoroquinolone for large breeds or severe infections'),
    ('Trimethoprim-Sulfa 480mg Tablets',  'Vetoquinol',               'Combination sulfonamide for respiratory and urinary tract infections'),
    ('Cephalexin 250mg Capsules',         'Dechra Veterinary',        'First-generation cephalosporin for skin and soft tissue infections'),
    ('Cephalexin 500mg Capsules',         'Dechra Veterinary',        'Higher-dose cephalosporin for pyoderma and wound infections'),
    ('Prednisolone 5mg Tablets',          'Virbac Animal Health',     'Corticosteroid for inflammatory and autoimmune conditions'),
    ('Prednisolone 20mg Tablets',         'Virbac Animal Health',     'Higher-dose corticosteroid for severe allergic or inflammatory disease'),
    ('Dexamethasone 0.5mg Tablets',       'Elanco Animal Health',     'Potent corticosteroid for acute inflammatory reactions'),
    ('Methylprednisolone 4mg Tablets',    'Pfizer Animal Health',     'Intermediate corticosteroid for chronic inflammatory conditions'),
    ('Hydrocortisone 10mg Tablets',       'Norbrook Laboratories',    'Mild corticosteroid for adrenal insufficiency and mild inflammation'),
    ('Furosemide 40mg Tablets',           'Boehringer Ingelheim',     'Loop diuretic for congestive heart failure and oedema'),
    ('Spironolactone 25mg Tablets',       'Dechra Veterinary',        'Potassium-sparing diuretic for cardiac and hepatic disease'),
    ('Enalapril 5mg Tablets',             'Zoetis Inc.',              'ACE inhibitor for hypertension and congestive heart failure'),
    ('Atenolol 25mg Tablets',             'Elanco Animal Health',     'Beta-blocker for hypertrophic cardiomyopathy and arrhythmias'),
    ('Digoxin 0.125mg Tablets',           'Pfizer Animal Health',     'Cardiac glycoside for atrial fibrillation and heart failure'),
    ('Carprofen 50mg Chewable Tablets',   'Zoetis Inc.',              'NSAID for pain and inflammation in musculoskeletal disease'),
    ('Meloxicam 1mg Tablets',             'Boehringer Ingelheim',     'NSAID for osteoarthritis pain and post-operative analgesia'),
    ('Meloxicam Oral Suspension 1.5mg/ml','Boehringer Ingelheim',     'Liquid NSAID formulation for cats and small dogs'),
    ('Tramadol 50mg Tablets',             'Norbrook Laboratories',    'Opioid analgesic for moderate to severe pain management'),
    ('Gabapentin 100mg Capsules',         'Dechra Veterinary',        'Anticonvulsant and analgesic for neuropathic pain'),
    ('Gabapentin 300mg Capsules',         'Dechra Veterinary',        'Higher-dose gabapentin for chronic pain or seizure management'),
    ('Phenobarbital 30mg Tablets',        'Virbac Animal Health',     'Barbiturate anticonvulsant for idiopathic epilepsy'),
    ('Potassium Bromide 325mg Capsules',  'Vetoquinol',               'Adjunctive anticonvulsant for refractory epilepsy'),
    ('Levetiracetam 250mg Tablets',       'Bayer Animal Health',      'Novel anticonvulsant with favourable safety profile'),
    ('Omeprazole 20mg Capsules',          'Elanco Animal Health',     'Proton pump inhibitor for gastric ulcer and acid reflux'),
    ('Famotidine 20mg Tablets',           'Pfizer Animal Health',     'H2 blocker for gastric hyperacidity and stress ulceration'),
    ('Metoclopramide 10mg Tablets',       'Zoetis Inc.',              'Prokinetic antiemetic for gastric motility disorders'),
    ('Ondansetron 4mg Tablets',           'Norbrook Laboratories',    'Serotonin antagonist antiemetic for chemotherapy-induced nausea'),
    ('Maropitant 16mg Tablets',           'Zoetis Inc.',              'NK1 receptor antagonist antiemetic for motion sickness and vomiting'),
    ('Sucralfate 1g Tablets',             'Dechra Veterinary',        'Mucosal protectant for gastric and duodenal ulcers'),
    ('Lactulose Oral Solution 667mg/ml',  'Virbac Animal Health',     'Osmotic laxative for hepatic encephalopathy and constipation'),
    ('Loperamide 2mg Capsules',           'Elanco Animal Health',     'Opioid receptor agonist for acute non-specific diarrhoea'),
    ('Tylosin 250mg Powder',              'Elanco Animal Health',     'Macrolide antibiotic for chronic enteropathy and diarrhoea'),
    ('Clindamycin 75mg Capsules',         'Zoetis Inc.',              'Lincosamide antibiotic for anaerobic and dental infections'),
    ('Clindamycin 150mg Capsules',        'Zoetis Inc.',              'Higher-dose clindamycin for deep tissue and bone infections'),
    ('Ketoconazole 200mg Tablets',        'Dechra Veterinary',        'Azole antifungal for dermatophytosis and systemic mycoses'),
    ('Fluconazole 50mg Capsules',         'Pfizer Animal Health',     'Triazole antifungal for Candida and cryptococcal infections'),
    ('Itraconazole 100mg Capsules',       'Boehringer Ingelheim',     'Broad-spectrum antifungal for Aspergillus and dermatophytes'),
    ('Fenbendazole 150mg Granules',       'Intervet-Schering Plough', 'Benzimidazole anthelmintic for roundworms, hookworms and Giardia'),
    ('Pyrantel Pamoate Oral Suspension',  'Elanco Animal Health',     'Anthelmintic for roundworm and hookworm infections'),
    ('Ivermectin 1% Oral Solution',       'Merial',                   'Macrocyclic lactone for mites, lice and internal parasites'),
    ('Milbemycin Oxime 2.3mg Tablets',    'Novartis Animal Health',   'Heartworm prevention and intestinal parasite control'),
    ('Praziquantel 50mg Tablets',         'Bayer Animal Health',      'Cestocidal agent for tapeworm infections'),
    ('Doxycycline Hyclate 100mg Tablets', 'Boehringer Ingelheim',     'Hyclate salt form with improved bioavailability for systemic infections'),
    ('Chloramphenicol 250mg Capsules',    'Vetoquinol',               'Broad-spectrum antibiotic reserved for resistant infections'),
    ('Cyclosporine 25mg Capsules',        'Elanco Animal Health',     'Immunosuppressant for immune-mediated skin and eye disease'),
    ('Cyclosporine 100mg Capsules',       'Elanco Animal Health',     'Higher-dose cyclosporine for large breed immune-mediated disease'),
    ('Apoquel 3.6mg Tablets',             'Zoetis Inc.',              'JAK inhibitor for pruritus and allergic dermatitis in dogs'),
    ('Apoquel 16mg Tablets',              'Zoetis Inc.',              'Higher-dose Apoquel for larger dogs with atopic dermatitis'),
    ('Hydroxyzine 25mg Tablets',          'Dechra Veterinary',        'Antihistamine for pruritic skin disease and anxiety'),
    ('Diphenhydramine 25mg Capsules',     'Norbrook Laboratories',    'First-generation antihistamine for allergic reactions'),
    ('Vitamin B12 Injection 1000mcg/ml',  'Vetoquinol',               'Cyanocobalamin supplement for malabsorption and neuropathy'),
    ('Iron Dextran 100mg/ml Injection',   'Virbac Animal Health',     'Parenteral iron supplement for iron-deficiency anaemia in neonates'),
    ('Calcium Gluconate 10% Injection',   'Pfizer Animal Health',     'IV calcium supplementation for hypocalcaemia and eclampsia'),
    ('Saline 0.9% Flush Solution 10ml',   'Zoetis Inc.',              'Sterile saline for catheter flushing and wound irrigation')
) AS m(med_name, mfr, descr) ON si.name = m.med_name
WHERE sic.name = 'Medicine';


-- b. prescription-only medicines (no shop_item)
INSERT INTO medicine (name, manufacturer, description, shop_item_id)
VALUES
    ('Amikacin 250mg/ml Injection',          'Norbrook Laboratories',    'Aminoglycoside antibiotic for gram-negative infections resistant to other antibiotics', NULL),
    ('Gentamicin 40mg/ml Injection',         'Dechra Veterinary',        'Aminoglycoside for serious gram-negative infections; requires renal monitoring', NULL),
    ('Imipenem-Cilastatin 500mg Injection',  'Pfizer Animal Health',     'Carbapenem for multidrug-resistant bacterial infections', NULL),
    ('Cefovecin 80mg/ml Injection',          'Zoetis Inc.',              'Long-acting cephalosporin injection; single dose covers 14 days', NULL),
    ('Marbofloxacin 50mg Tablets',           'Vetoquinol',               'Third-generation fluoroquinolone for skin and urinary infections', NULL),
    ('Pradofloxacin 15mg Tablets',           'Bayer Animal Health',      'Broad-spectrum fluoroquinolone including anaerobes; cats only', NULL),
    ('Azithromycin 250mg Capsules',          'Boehringer Ingelheim',     'Macrolide antibiotic for respiratory and intracellular infections', NULL),
    ('Rifampicin 150mg Capsules',            'Virbac Animal Health',     'Reserved for Rhodococcus equi and methicillin-resistant staphylococci', NULL),
    ('Linezolid 600mg Tablets',              'Pfizer Animal Health',     'Oxazolidinone for vancomycin-resistant enterococci', NULL),
    ('Vancomycin 500mg Injection',           'Elanco Animal Health',     'Glycopeptide antibiotic; last-resort therapy for MRSA', NULL),
    ('Hydrocortisone Sodium Succinate Inj.', 'Zoetis Inc.',              'IV corticosteroid for anaphylaxis and Addisonian crisis', NULL),
    ('Betamethasone 4mg/ml Injection',       'Norbrook Laboratories',    'Potent long-acting corticosteroid for inflammatory conditions', NULL),
    ('Triamcinolone 10mg/ml Injection',      'Dechra Veterinary',        'Intermediate-acting corticosteroid for intra-articular use', NULL),
    ('Terbinafine 250mg Tablets',            'Elanco Animal Health',     'Allylamine antifungal for dermatophyte infections', NULL),
    ('Voriconazole 200mg Tablets',           'Pfizer Animal Health',     'Extended-spectrum triazole for Aspergillus and resistant Candida', NULL),
    ('Amphotericin B 50mg Injection',        'Boehringer Ingelheim',     'Polyene antifungal for systemic mycoses; nephrotoxic', NULL),
    ('Miltefosine 20mg Capsules',            'Virbac Animal Health',     'Antiprotozoal for feline leishmaniosis', NULL),
    ('Ronidazole 100mg Tablets',             'Dechra Veterinary',        'Nitroimidazole for feline tritrichomoniasis', NULL),
    ('Atovaquone 150mg Suspension',          'Norbrook Laboratories',    'Antiprotozoal for Babesia and Cytauxzoon infections', NULL),
    ('Allopurinol 100mg Tablets',            'Elanco Animal Health',     'Xanthine oxidase inhibitor for urate urolithiasis in Dalmatians', NULL),
    ('Pimobendan 1.25mg Tablets',            'Boehringer Ingelheim',     'Phosphodiesterase inhibitor and Ca-sensitiser for DCM and MVD', NULL),
    ('Diltiazem 30mg Tablets',               'Dechra Veterinary',        'Calcium channel blocker for feline hypertrophic cardiomyopathy', NULL),
    ('Amlodipine 1.25mg Tablets',            'Pfizer Animal Health',     'Calcium channel blocker for systemic hypertension in cats', NULL),
    ('Benazepril 5mg Tablets',               'Vetoquinol',               'ACE inhibitor for chronic kidney disease and hypertension', NULL),
    ('Telmisartan 4mg/ml Oral Solution',     'Boehringer Ingelheim',     'Angiotensin II receptor blocker for feline CKD proteinuria', NULL),
    ('Sildenafil 25mg Tablets',              'Zoetis Inc.',              'PDE-5 inhibitor for pulmonary arterial hypertension', NULL),
    ('Heparin 5000 IU/ml Injection',         'Virbac Animal Health',     'Anticoagulant for thromboembolism and DIC management', NULL),
    ('Clopidogrel 75mg Tablets',             'Norbrook Laboratories',    'Antiplatelet for feline arterial thromboembolism prevention', NULL),
    ('Pentoxifylline 400mg Tablets',         'Elanco Animal Health',     'Haemorheological agent for vasculitis and ischaemic disease', NULL),
    ('Levothyroxine 0.1mg Tablets',          'Dechra Veterinary',        'Thyroid hormone replacement for canine hypothyroidism', NULL),
    ('Methimazole 5mg Tablets',              'Virbac Animal Health',     'Thioamide for feline hyperthyroidism; inhibits thyroid synthesis', NULL),
    ('Trilostane 30mg Capsules',             'Dechra Veterinary',        '3beta-HSD inhibitor for hyperadrenocorticism (Cushing disease)', NULL),
    ('Mitotane 500mg Tablets',               'Pfizer Animal Health',     'Adrenocorticolytic for pituitary-dependent hyperadrenocorticism', NULL),
    ('Desoxycorticosterone 25mg/ml Inj.',    'Elanco Animal Health',     'Mineralocorticoid for canine hypoadrenocorticism (Addison disease)', NULL),
    ('Cabergoline 0.05mg Tablets',           'Norbrook Laboratories',    'Dopamine agonist for false pregnancy and hyperprolactinaemia', NULL),
    ('Misoprostol 200mcg Tablets',           'Boehringer Ingelheim',     'Prostaglandin E1 analogue for GI mucosal protection with NSAIDs', NULL),
    ('Cisapride 5mg Tablets',                'Virbac Animal Health',     'Prokinetic for feline megacolon and gastric motility disorders', NULL),
    ('Ursodiol 50mg Capsules',               'Dechra Veterinary',        'Bile acid for cholelithiasis and chronic hepatitis', NULL),
    ('S-Adenosylmethionine 200mg Tablets',   'Zoetis Inc.',              'Hepatoprotectant for liver disease and oxidative stress', NULL),
    ('Silymarin 35mg Capsules',              'Vetoquinol',               'Milk thistle extract hepatoprotectant for chronic hepatopathy', NULL),
    ('Acetylcysteine 20% Solution',          'Elanco Animal Health',     'Mucolytic and antidote for paracetamol toxicosis in cats', NULL),
    ('Atropine 0.6mg/ml Injection',          'Zoetis Inc.',              'Anticholinergic for bradycardia, organophosphate toxicosis, pre-anaesthesia', NULL),
    ('Dopamine 40mg/ml Injection',           'Pfizer Animal Health',     'Catecholamine for cardiogenic shock and acute hypotension', NULL),
    ('Dobutamine 12.5mg/ml Injection',       'Boehringer Ingelheim',     'Inotrope for decompensated heart failure and cardiogenic shock', NULL),
    ('Norepinephrine 1mg/ml Injection',      'Norbrook Laboratories',    'Vasopressor for distributive shock unresponsive to fluids', NULL),
    ('Mannitol 20% Infusion',                'Dechra Veterinary',        'Osmotic diuretic for cerebral oedema and acute glaucoma', NULL),
    ('Hypertonic Saline 7.2% Infusion',      'Virbac Animal Health',     'Resuscitation fluid for haemorrhagic shock and head trauma', NULL),
    ('Fresh Frozen Plasma (canine)',         'Animal Blood Resources',   'Coagulopathy treatment; provides clotting factors and albumin', NULL),
    ('Hydroxyethyl Starch 6% Infusion',      'Elanco Animal Health',     'Colloid volume expander for hypoproteinaemia and shock', NULL),
    ('Dextrose 50% Injection',               'Zoetis Inc.',              'Concentrated glucose for hypoglycaemia; dilute before IV use', NULL),
    ('Potassium Chloride 15% Injection',     'Norbrook Laboratories',    'IV potassium supplementation; must be diluted; cardiac monitoring required', NULL),
    ('Sodium Bicarbonate 8.4% Injection',    'Pfizer Animal Health',     'Alkalinising agent for severe metabolic acidosis', NULL),
    ('Propofol 10mg/ml Injection',           'Zoetis Inc.',              'IV induction agent for general anaesthesia; rapid onset', NULL),
    ('Alfaxalone 10mg/ml Injection',         'Jurox Animal Health',      'Neurosteroid anaesthetic for induction and TIVA in cats and dogs', NULL),
    ('Ketamine 100mg/ml Injection',          'Dechra Veterinary',        'Dissociative anaesthetic; used in combination protocols', NULL),
    ('Midazolam 5mg/ml Injection',           'Virbac Animal Health',     'Benzodiazepine for sedation, co-induction and status epilepticus', NULL),
    ('Medetomidine 1mg/ml Injection',        'Orion Pharma',             'Alpha-2 agonist for sedation and pre-anaesthetic medication', NULL),
    ('Buprenorphine 0.3mg/ml Injection',     'Norbrook Laboratories',    'Partial opioid agonist for perioperative and chronic pain', NULL),
    ('Methadone 10mg/ml Injection',          'Dechra Veterinary',        'Full mu-opioid agonist for perioperative pain; used IV or IM', NULL),
    ('Morphine 10mg/ml Injection',           'Elanco Animal Health',     'Classic opioid analgesic for severe acute pain; epidural use', NULL),
    ('Fentanyl 0.05mg/ml Injection',         'Pfizer Animal Health',     'Short-acting opioid for intraoperative analgesia and CRI', NULL);


-- ============================================================
-- appointment
-- one appointment per owner-pet pair
-- phone copied from owner
-- ============================================================

INSERT INTO appointment (date_appointment, reason, phone, owner_id, pet_id)
SELECT
    CURRENT_DATE - (floor(random() * 730) + 1)::int     AS date_appointment,
    reason_list.reason,
    o.phone,
    o.id                                                  AS owner_id,
    p.id                                                  AS pet_id
FROM owner o
JOIN LATERAL (
    -- Pick one random pet belonging to this owner
    SELECT id
    FROM pet
    WHERE owner_id = o.id
    ORDER BY random()
    LIMIT 1
) p ON true
CROSS JOIN LATERAL (
    SELECT reason
    FROM (VALUES
        ('Annual wellness check'),
        ('Vaccination booster'),
        ('Limping / lameness'),
        ('Vomiting and lethargy'),
        ('Skin rash and itching'),
        ('Ear infection suspected'),
        ('Eye discharge and redness'),
        ('Dental check-up'),
        ('Weight loss and poor appetite'),
        ('Diarrhoea for more than 2 days'),
        ('Post-operative follow-up'),
        ('Suspected urinary tract infection'),
        ('Respiratory difficulty'),
        ('Wound assessment'),
        ('Parasite prevention consultation'),
        ('Behavioural changes'),
        ('Mass / lump noticed'),
        ('Allergic reaction'),
        ('Pre-surgical blood work'),
        ('General health concern')
    ) AS r(reason)
    WHERE o.id IS NOT NULL   -- random per row
    ORDER BY random()
    LIMIT 1
) reason_list;

-- ============================================================
-- examination
-- one per appointment; date >= date_appointment
-- employee_id: random vet (role_id = 2)
-- examination_room_id: random room of type 'examination'
-- ============================================================

INSERT INTO examination (date_examination, status, description, appointment_id, employee_id, examination_room_id)
SELECT
    -- date is same day or up to 7 days after appointment
    a.date_appointment + (floor(random() * 8))::int         AS date_examination,

    -- most are completed; a few scheduled or cancelled
    CASE
        WHEN random() < 0.80 THEN 'completed'
        WHEN random() < 0.90 THEN 'scheduled'
        ELSE 'cancelled'
    END                                                      AS status,

    desc_list.description,
    a.id                                                     AS appointment_id,

    e.id AS employee_id,
    r.id AS examination_room_id

FROM appointment a
CROSS JOIN LATERAL (
    SELECT description
    FROM (VALUES
        ('Patient presented for routine examination. Vitals within normal limits.'),
        ('Initial assessment completed. Further diagnostics recommended.'),
        ('Physical examination performed. Owner advised on treatment plan.'),
        ('Patient examined; mild clinical signs noted. Medication prescribed.'),
        ('Thorough examination carried out. No acute concerns identified.'),
        ('Follow-up examination. Condition improving since last visit.'),
        ('Examination completed. Lab samples collected for analysis.'),
        ('Clinical signs assessed. Dietary modification recommended.'),
        ('Patient stable. Monitoring plan established with owner.'),
        ('Examination revealed localised inflammation. Treatment initiated.')
    ) AS d(description)
    WHERE a.id IS NOT NULL
    ORDER BY random()
    LIMIT 1
) desc_list
-- random employee with role_id = 2
CROSS JOIN LATERAL (
    SELECT id
    FROM employee
    WHERE role_id = 2
      AND a.id IS NOT NULL
    ORDER BY random()
    LIMIT 1
) e
-- random room with type = 'examination'
CROSS JOIN LATERAL (
    SELECT id
    FROM examination_room
    WHERE type = 'examination'
      AND a.id IS NOT NULL
    ORDER BY random()
    LIMIT 1
) r;

-- ============================================================
-- treatment_type
-- ============================================================

INSERT INTO treatment_type (name) VALUES
    ('prescription'),
    ('vaccination'),
    ('consultation'),
    ('operation')
ON CONFLICT DO NOTHING;

-- ============================================================
-- treatment (prescription type)
-- one treatment per completed examination
-- date_treatment >= date_examination
-- ============================================================

INSERT INTO treatment (date_treatment, notes, treatment_type_id, examination_id)
SELECT
    e.date_examination + (floor(random() * 4))::int     AS date_treatment,

    notes_list.notes,

    (SELECT id FROM treatment_type WHERE name = 'prescription') AS treatment_type_id,

    e.id                                                 AS examination_id

FROM examination e
CROSS JOIN LATERAL (
    SELECT notes
    FROM (VALUES
        ('Prescription issued following clinical assessment.'),
        ('Medication course prescribed; owner counselled on administration.'),
        ('Short course of antibiotics prescribed pending culture results.'),
        ('Anti-inflammatory therapy initiated; re-check in 10 days.'),
        ('Antiparasitic treatment prescribed; environmental treatment advised.'),
        ('Analgesic course prescribed for post-operative pain management.'),
        ('Antifungal therapy prescribed; reassess in 3 weeks.'),
        ('Prescription provided; monitor for adverse reactions.'),
        ('Combination therapy prescribed; owner given written instructions.'),
        ('Medication adjusted based on current clinical findings.')
    ) AS n(notes)
    WHERE e.id IS NOT NULL
    ORDER BY random()
    LIMIT 1
) notes_list
WHERE e.status = 'completed';

-- ============================================================
-- prescription
-- one prescription per examination that:
-- - has status = 'completed'
-- - has a treatment of type 'prescription'
-- ============================================================

INSERT INTO prescription (examination_id, date_start, date_end, description)
SELECT
    e.id                                                            AS examination_id,
    e.date_examination                                              AS date_start,
    (e.date_examination + floor(random() * 21 + 7)::int)::date     AS date_end,
    pa.advice                                                       AS description
FROM examination e
JOIN treatment t          ON t.examination_id = e.id
JOIN treatment_type tt    ON tt.id = t.treatment_type_id
         AND tt.name = 'prescription'
LEFT JOIN prescription p_existing
         ON p_existing.examination_id = e.id
CROSS JOIN LATERAL (
    SELECT advice
    FROM temp_prescription_advice
    WHERE e.id IS NOT NULL
    ORDER BY random()
    LIMIT 1
) pa
WHERE e.status = 'completed'
  AND p_existing.id IS NULL           -- skip if already inserted
ON CONFLICT (examination_id) DO NOTHING;


-- ============================================================
-- prescription_medicine
-- Each prescription gets 1–4 medicines assigned.
-- ============================================================

-- generate_series to produce 1–4 rows per prescription,
-- then deduplicate (medicine_id, prescription_id) pairs via DISTINCT ON.

WITH prescription_slots AS (
    SELECT
        pr.id                                                           AS prescription_id,
        gs.n                                                            AS slot,
        floor(random() * 4 + 1)::int                                   AS num_medicines   -- how many this prescription actually wants
    FROM prescription pr
    CROSS JOIN generate_series(1, 4) AS gs(n)
),

wanted_slots AS (
    SELECT prescription_id, slot
    FROM prescription_slots
    WHERE slot <= num_medicines
),

-- Assign a random medicine to each slot
med_counts AS (
    SELECT count(*) AS total FROM medicine
),

assigned AS (
    SELECT
        ws.prescription_id,
        ws.slot,
        m.id                                                            AS medicine_id,
        CASE
            WHEN random() < 0.6 THEN 1      -- once daily
            WHEN random() < 0.85 THEN 2     -- twice daily
            ELSE 3                          -- three times daily
        END                                                             AS dosage,
        (floor(random() * 21 + 3))::int                                AS num_days    -- 3–23 days
    FROM wanted_slots ws
    CROSS JOIN med_counts mc
    JOIN medicine m ON m.id = (
        (abs(hashtext(ws.prescription_id::text || '-' || ws.slot::text)) % mc.total) + 1
    )
),

-- deduplicate: if the same medicine appears twice in the same prescription, keep the first slot
deduped AS (
    SELECT DISTINCT ON (prescription_id, medicine_id)
        prescription_id,
        medicine_id,
        dosage,
        num_days
    FROM assigned
    ORDER BY prescription_id, medicine_id, slot
)

INSERT INTO prescription_medicine (prescription_id, medicine_id, dosage, num_days)
SELECT prescription_id, medicine_id, dosage, num_days
FROM deduped
ON CONFLICT (prescription_id, medicine_id) DO NOTHING;

-- show prescriptions with their medicines
-- SELECT
--     pr.id               AS prescription_id,
--     e.date_examination,
--     m.name              AS medicine,
--     pm.dosage,
--     pm.num_days,
--     CASE WHEN m.shop_item_id IS NOT NULL THEN 'in shop' ELSE 'prescription only' END AS availability
-- FROM prescription pr
-- JOIN prescription_medicine pm ON pm.prescription_id = pr.id
-- JOIN medicine m               ON m.id = pm.medicine_id
-- JOIN examination e            ON e.id = pr.examination_id
-- ORDER BY pr.id, m.name
-- LIMIT 50;



-- ============================================================
-- appointment (vaccination oriented)
-- ============================================================

INSERT INTO appointment (date_appointment, reason, phone, owner_id, pet_id)
SELECT
    CURRENT_DATE - (floor(random() * 730) + 1)::int     AS date_appointment,
    reason_list.reason,
    o.phone,
    o.id                                                  AS owner_id,
    p.id                                                  AS pet_id
FROM owner o
JOIN LATERAL (
    SELECT id
    FROM pet
    WHERE owner_id = o.id
    ORDER BY random()
    LIMIT 1
) p ON true
CROSS JOIN LATERAL (
    SELECT reason
    FROM (VALUES
        ('Annual vaccination'),
        ('Rabies vaccine booster'),
        ('Core vaccine schedule - puppy/kitten'),
        ('Bordetella vaccination'),
        ('Leptospirosis booster'),
        ('Feline herpesvirus / calicivirus / panleukopenia combo'),
        ('Canine distemper / parvovirus booster'),
        ('Vaccine certificate needed for travel'),
        ('First vaccination - new pet'),
        ('Overdue vaccination catch-up')
    ) AS r(reason)
    WHERE o.id IS NOT NULL   -- random per row
    ORDER BY random()
    LIMIT 1
) reason_list;

-- ============================================================
-- EXAMINATIONS for new appointments (~75% coverage)
-- date_examination >= date_appointment
-- employee_id - random vet with role_id = 2
-- examination_room_id - type = 'examination'
-- ============================================================

INSERT INTO examination (date_examination, status, description, appointment_id, employee_id, examination_room_id)
SELECT
    a.date_appointment + (floor(random() * 5))::int         AS date_examination,

    CASE
        WHEN random() < 0.78 THEN 'completed'
        WHEN random() < 0.88 THEN 'scheduled'
        ELSE 'cancelled'
    END                                                      AS status,

    desc_list.description,
    a.id                                                     AS appointment_id,

    emp.id AS employee_id,
    r.id AS examination_room_id

FROM appointment a
-- appointments that have no examination yet
LEFT JOIN examination e ON e.appointment_id = a.id
CROSS JOIN LATERAL (
    SELECT description
    FROM (VALUES
        ('Pre-vaccination health check completed. Patient fit for immunisation.'),
        ('Animal examined prior to vaccination. No contraindications found.'),
        ('Vaccination visit. General condition assessed; vitals normal.'),
        ('Patient presented for scheduled immunisation. Brief physical performed.'),
        ('Health status confirmed satisfactory before vaccine administration.'),
        ('Routine vaccination examination. Lymph nodes and temperature normal.'),
        ('Owner updated on vaccine schedule. Patient in good overall condition.'),
        ('Pre-vaccine check: skin, coat, mucous membranes all within normal limits.'),
        ('Examination completed. Booster due; owner reminded of next schedule.'),
        ('Young patient examined ahead of core vaccine series. No abnormalities.')
    ) AS d(description)
    WHERE a.id IS NOT NULL
    ORDER BY random()
    LIMIT 1
) desc_list
-- random employee with role_id = 2
CROSS JOIN LATERAL (
    SELECT id
    FROM employee
    WHERE role_id = 2
      AND a.id IS NOT NULL
    ORDER BY random()
    LIMIT 1
) emp
-- random room with type = 'examination'
CROSS JOIN LATERAL (
    SELECT id
    FROM examination_room
    WHERE type = 'examination'
      AND a.id IS NOT NULL
    ORDER BY random()
    LIMIT 1
) r
WHERE e.id IS NULL          -- no existing examination for this appointment
  AND random() < 0.75;      -- 75% appointments get an examination


-- ============================================================
-- treatment (vaccination type)
-- linked to completed examinations that don't already have
-- a vaccination treatment
-- ============================================================

INSERT INTO treatment (date_treatment, notes, treatment_type_id, examination_id)
SELECT
    e.date_examination + (floor(random() * 3))::int     AS date_treatment,

    notes_list.notes,

    (SELECT id FROM treatment_type WHERE name = 'vaccination') AS treatment_type_id,

    e.id AS examination_id

FROM examination e
CROSS JOIN LATERAL (
    SELECT notes
    FROM (VALUES
        ('Core vaccine administered. No immediate adverse reaction observed.'),
        ('Booster vaccination given. Owner advised to monitor for 24 hours.'),
        ('Rabies vaccine administered. Certificate issued.'),
        ('Puppy / kitten primary course vaccine given. Next dose scheduled.'),
        ('Annual booster completed. Patient tolerated injection well.'),
        ('Leptospirosis component included in this year''s booster.'),
        ('Intranasal Bordetella vaccine administered without complication.'),
        ('Combination vaccine given SC in right scruff. Patient calm throughout.'),
        ('Travel vaccine record updated. International health certificate provided.'),
        ('Catch-up vaccination completed. Full schedule now up to date.')
    ) AS n(notes)
    WHERE e.id IS NOT NULL
    ORDER BY random()
    LIMIT 1
) notes_list
WHERE e.status = 'completed'
-- only those that don't already have a vaccination treatment
  AND NOT EXISTS (
      SELECT 1
      FROM treatment t
      JOIN treatment_type tt ON tt.id = t.treatment_type_id
      WHERE t.examination_id = e.id
        AND tt.name = 'vaccination'
  );



-- ============================================================
-- Shop categories, items, attributes, attribute values
-- ============================================================


-- ============================================================
-- shop_item_category
-- ============================================================

INSERT INTO shop_item_category (name, parent_id)
VALUES ('Food', NULL)
ON CONFLICT DO NOTHING;

INSERT INTO shop_item_category (name, parent_id)
SELECT 'Snacks', id FROM shop_item_category WHERE name = 'Food'
ON CONFLICT DO NOTHING;

INSERT INTO shop_item_category (name, parent_id)
SELECT 'Supplements', id FROM shop_item_category WHERE name = 'Medicine'
ON CONFLICT DO NOTHING;

INSERT INTO shop_item_category (name, parent_id)
VALUES ('Accessories', NULL)
ON CONFLICT DO NOTHING;

INSERT INTO shop_item_category (name, parent_id)
SELECT 'Hygiene and Grooming', id FROM shop_item_category WHERE name = 'Pet Supplies'
ON CONFLICT DO NOTHING;

INSERT INTO shop_item_category (name, parent_id)
SELECT 'Toys', id FROM shop_item_category WHERE name = 'Accessories'
ON CONFLICT DO NOTHING;

INSERT INTO shop_item_category (name, parent_id)
VALUES ('Clothing', NULL)
ON CONFLICT DO NOTHING;


-- ============================================================
-- shop_item - Food
-- Foods for dogs, cats, birds, fish, reptiles, small mammals
-- ============================================================

INSERT INTO shop_item (name, price, stock, shop_item_category_id)
SELECT name, price, stock,
       (SELECT id FROM shop_item_category WHERE name = 'Food')
FROM (VALUES
    -- Dog food
    ('Royal Canin Adult Dry Dog Food 15kg',         45.99, 80),
    ('Hill''s Science Diet Puppy Chicken 12kg',     42.50, 60),
    ('Purina Pro Plan Sensitive Salmon 7kg',         28.99, 75),
    ('Orijen Original Dry Dog Food 6kg',            38.99, 50),
    ('Pedigree Adult Wet Dog Food Beef 400g',         1.99, 300),
    ('Cesar Classic Loaf with Chicken 150g',          1.49, 250),
    ('Royal Canin Maxi Adult Dry Dog Food 10kg',    32.99, 70),
    ('Eukanuba Adult Small Breed 3kg',              18.50, 90),

    -- Cat food
    ('Royal Canin Indoor Adult Cat 4kg',            22.99, 100),
    ('Hill''s Science Diet Adult Cat Chicken 3.5kg',20.99, 85),
    ('Whiskas Adult Wet Cat Food Tuna 85g',           0.89, 400),
    ('Purina Felix Adult Salmon Pouches 12x85g',     8.99, 120),
    ('Orijen Cat & Kitten Dry Food 5.4kg',          44.99, 40),
    ('Sheba Perfect Portions Chicken & Tuna 72g',    1.29, 350),
    ('Royal Canin Kitten Dry Food 2kg',             15.99, 95),

    -- Bird food
    ('Versele-Laga Prestige Parrot Mix 3kg',        12.99, 60),
    ('Kaytee Forti-Diet Pro Canary Seed 2lb',        9.99, 55),
    ('Zupreem Natural Pellets Medium Birds 1.25kg', 18.49, 45),
    ('Vitakraft Budgie Seed Mix 1kg',                5.99, 80),
    ('Harrisons Adult Lifetime Fine Pellets 454g',  17.99, 35),

    -- Fish food
    ('Tetra Goldfish Flakes 200g',                   6.49, 110),
    ('Hikari Cichlid Gold Floating Pellets 342g',   12.99, 70),
    ('Fluval Bug Bites Tropical Fish Food 45g',      9.99, 90),
    ('API Tropical Flakes 71g',                      5.49, 100),
    ('Sera Vipan Nature Flake Food 250ml',            8.99, 85),

    -- Reptile food
    ('Exo Terra Mealworms Canned Food 34g',          4.99, 60),
    ('Zoo Med Can O'' Crickets 35g',                  5.49, 55),
    ('Flukers Freeze-Dried Crickets 1.2oz',           6.99, 50),
    ('Repashy Crested Gecko MRP Banana 3oz',        12.49, 40),

    -- Small mammal food
    ('Oxbow Essentials Adult Rabbit Pellets 5lb',   18.99, 65),
    ('Supreme Science Selective Hamster 350g',        6.49, 75),
    ('Kaytee Forti-Diet Guinea Pig Food 5lb',       12.99, 55),
    ('Versele-Laga Complete Ferret 750g',            14.99, 45)
) AS t(name, price, stock);


-- ============================================================
-- shop_item - Snacks
-- ============================================================

INSERT INTO shop_item (name, price, stock, shop_item_category_id)
SELECT name, price, stock,
       (SELECT id FROM shop_item_category WHERE name = 'Snacks')
FROM (VALUES
    -- Dog snacks
    ('Milk-Bone Original Dog Biscuits 24oz',         7.99, 150),
    ('Zuke''s Mini Naturals Chicken Treats 6oz',     8.49, 120),
    ('Dentastix Daily Oral Care Medium 28 Sticks',  12.99, 100),
    ('Greenies Original Dental Treats Large 12oz',  16.99, 85),
    ('Wellness Soft WellBites Lamb & Salmon 6oz',    9.49, 95),
    ('Merrick Power Bites Real Chicken 6oz',          8.99, 110),
    ('Nylabones Puppy Chew Chicken Flavour',          5.99, 130),
    ('Bully Sticks 6-inch Natural 10 Pack',         14.99, 70),

    -- Cat snacks
    ('Temptations Classic Treats Chicken 85g',        2.99, 200),
    ('Dreamies Cat Treats Cheese 60g',                1.99, 220),
    ('Churu Purée Tuna with Salmon 4x14g',            4.49, 180),
    ('Greenies Feline Dental Treats Ocean Fish 60g',  5.49, 110),
    ('Whiskas Temptations Tuna 180g',                 3.99, 150),

    -- Bird snacks
    ('Vitakraft Crunch Stick Budgie Honey 2-pack',    3.49,  90),
    ('Kaytee Fiesta Yogurt Dipped Papaya Bird Treat', 4.99,  70),

    -- Small mammal snacks
    ('Oxbow Simple Rewards Timothy Hay Treats 3oz',   3.99,  85),
    ('Supreme Tiny Friends Yogurt Drops Strawberry',  2.99,  95),
    ('Kaytee Treat Stick Rabbit Honey & Oat',         2.49, 100)
) AS t(name, price, stock);


-- ============================================================
-- shop_item - Supplements
-- ============================================================

INSERT INTO shop_item (name, price, stock, shop_item_category_id)
SELECT name, price, stock,
       (SELECT id FROM shop_item_category WHERE name = 'Supplements')
FROM (VALUES
    ('Zesty Paws Multivitamin Bites for Dogs 90ct',  24.99, 70),
    ('VetriScience Canine Plus Senior Tabs 60ct',    19.99, 55),
    ('Nutramax Cosequin DS Plus MSM 60ct',           29.99, 60),
    ('Zesty Paws Omega Bites Wild Alaskan Fish Oil', 22.49, 65),
    ('Vetri-Science Cell Advance 440 Cats 60ct',     18.99, 50),
    ('Pet Naturals Daily Multi Cat 30ct',             9.99, 80),
    ('Nutramax Proviable-DC Probiotic Caps 80ct',    27.99, 45),
    ('Virbac C.E.T. Enzymatic Chews Medium Dogs',   21.99, 55),
    ('Zesty Paws Mobility Bites Hip & Joint Dogs',  23.99, 60),
    ('VetriScience Composure Calming Chews 30ct',   16.99, 70),
    ('Oxbow Natural Science Vitamin C Tabs Guinea', 10.49, 85),
    ('Rep-Cal Herptivite Reptile Multivitamin 3.3oz',14.99, 40),
    ('Fluval Vita Tropical Fish Vitamin Drops 50ml', 8.99, 75),
    ('Vetri-Science Feline Ultimate Probiotic 60ct', 21.99, 50)
) AS t(name, price, stock);


-- ============================================================
-- shop_item - Accessories
-- ============================================================

INSERT INTO shop_item (name, price, stock, shop_item_category_id)
SELECT name, price, stock,
       (SELECT id FROM shop_item_category WHERE name = 'Accessories')
FROM (VALUES
    ('PetSafe Easy Walk Dog Harness Medium',         19.99, 90),
    ('Ruffwear Front Range Harness Large',           39.99, 55),
    ('Kong Classic Dog Toy Large',                   13.99, 120),
    ('Kurgo Tru-Fit Smart Dog Harness XL',           34.99, 45),
    ('Flexi New Classic Retractable Leash 8m',       18.99, 100),
    ('Rogz Reflective Dog Collar Medium',             9.99, 130),
    ('Catit Flower Fountain 3L',                     24.99, 75),
    ('Trixie Cat Tree Tower 150cm',                  69.99, 30),
    ('PetSafe ScoopFree Automatic Litter Box',       99.99, 20),
    ('AmazonBasics Elevated Cooling Dog Bed Large',  29.99, 60),
    ('Midwest iCrate Single Door Dog Crate 30in',    49.99, 40),
    ('Ferplast Favola Hamster Cage',                 34.99, 35),
    ('Zolux Birdcage Volière Sydney 105cm',          89.99, 15),
    ('Exo Terra Terrarium 60x45x45cm',              139.99, 12),
    ('Fluval Spec V Aquarium Kit 19L',               79.99, 20),
    ('Catit Senses 2.0 Food Tree Puzzle',            18.99, 65),
    ('Dog ID Tag Stainless Steel Bone Shape',         4.99, 200),
    ('PetSafe Drinkwell Multi-Tier Fountain',        27.99, 50)
) AS t(name, price, stock);


-- ============================================================
-- shop_item - Hygiene and Grooming
-- ============================================================

INSERT INTO shop_item (name, price, stock, shop_item_category_id)
SELECT name, price, stock,
       (SELECT id FROM shop_item_category WHERE name = 'Hygiene and Grooming')
FROM (VALUES
    ('Tropiclean Natural Flea & Tick Dog Shampoo',    12.99, 90),
    ('Burt''s Bees Hypoallergenic Dog Shampoo 16oz',  10.99, 85),
    ('Virbac Epi-Soothe Oatmeal Shampoo 500ml',       18.99, 60),
    ('Furminator deShedding Dog Shampoo 16oz',        14.99, 70),
    ('Pet Head Feeling Flaky Anti-Dandruff Shampoo',  11.49, 65),
    ('Chris Christensen Ice on Ice Conditioner 250ml',17.99, 45),
    ('Furminator Long Hair deShedding Tool Large',    39.99, 55),
    ('Andis EasyClip 2-Speed Dog Clipper Kit',        54.99, 30),
    ('Wahl Bravura Lithium Dog Clipper',              84.99, 20),
    ('Coastal Pet Safari Nail Clippers for Dogs',      9.99, 100),
    ('Dremel PawControl Dog Nail Grinder Kit',        34.99, 40),
    ('Virbac CET Oral Hygiene Kit Dog',               14.99, 75),
    ('Pet Republique Dog Dental Wipes 100ct',          8.99, 90),
    ('Douxo S3 PYO Antiseptic Mousse 150ml',          19.99, 50),
    ('Veterinary Formula Clinical Care Ear Therapy',   9.99, 80),
    ('Zymox Otic Ear Solution with Hydrocortisone',   18.99, 55),
    ('Burt''s Bees Cat Hypoallergenic Shampoo 10oz',   9.99, 70),
    ('Bio-Groom Super White Cat Shampoo 236ml',       11.49, 50),
    ('Safari Cat Shedding Comb',                       7.99, 85),
    ('Hertzko Self-Cleaning Slicker Brush',           15.99, 95),
    ('Pet Wipes Fragrance Free 100ct',                 6.99, 120),
    ('Tropiclean Fresh Breath Dog Water Additive',    10.49, 80)
) AS t(name, price, stock);


-- ============================================================
-- shop_item - Toys
-- ============================================================

INSERT INTO shop_item (name, price, stock, shop_item_category_id)
SELECT name, price, stock,
       (SELECT id FROM shop_item_category WHERE name = 'Toys')
FROM (VALUES
    -- Dog toys
    ('Kong Extreme Dog Toy Large Black',             14.99, 100),
    ('Chuckit! Ultra Ball Medium 2-Pack',             9.99, 120),
    ('Outward Hound Hide-A-Squirrel Puzzle Large',   19.99,  70),
    ('KONG Wobbler Interactive Treat Toy',           12.99,  85),
    ('Tug-A-Jug Meal Dispensing Dog Toy',            14.49,  65),
    ('Benebone Wishbone Chew Toy Bacon Large',       15.99,  90),
    ('ZippyPaws Skinny Peltz Squeaky Plush 3-Pack',  11.99,  95),
    ('iFetch Interactive Ball Launcher Small',       99.99,  25),

    -- Cat toys
    ('Da Bird Feather Wand Cat Toy',                  9.99, 110),
    ('SmartyKat Hot Pursuit Electronic Cat Toy',     15.99,  75),
    ('PetFusion Ambush Interactive Cat Toy',         24.99,  50),
    ('Yeowww! Catnip Banana',                         6.99, 130),
    ('Catit Senses 2.0 Circuit Cat Toy',             17.99,  60),
    ('Jackson Galaxy Air Wand Cat Toy',               9.49, 100),

    -- Bird toys
    ('Super Bird Creations Booda Comfy Perch',        8.99,  55),
    ('Prevue Hendryx Parrot Ladder Toy 12in',         7.49,  60),
    ('Penn-Plax Bird Life Mirror with Bell',          5.99,  80),

    -- Small mammal toys
    ('Niteangel Wooden Hamster Wheel 20cm Silent',   19.99,  50),
    ('Kaytee Run-About 7in Clear Exercise Ball',      5.99,  85),
    ('Ware Manufacturing Critter Tunnel Small',        7.99,  70),

    -- Fish / aquarium enrichment
    ('Marina Aquarium Decoration Skull',              5.99,  90),
    ('Penn-Plax Betta Hammock Leaf Ledge',            3.99, 110)
) AS t(name, price, stock);


-- ============================================================
-- shop_item - Clothing
-- ============================================================

INSERT INTO shop_item (name, price, stock, shop_item_category_id)
SELECT name, price, stock,
       (SELECT id FROM shop_item_category WHERE name = 'Clothing')
FROM (VALUES
    -- Hats
    ('Casual Canine Cowboy Dog Hat Small',            8.99, 60),
    ('Fido Finery Sun Protection Hat Medium',        12.99, 50),
    ('Rubies Pet Shop Sailor Dog Hat S/M',            6.49, 70),

    -- Jackets / coats
    ('Ruffwear Overcoat Fuse Dog Jacket XS',         79.99, 25),
    ('Canada Pooch Puffer Vest Dog Jacket M',        44.99, 35),
    ('Hurtta Expedition Parka Dog Winter Coat L',    89.99, 20),
    ('Gooby Stretch Fleece Dog Vest Small',          18.99, 65),
    ('Pinkaholic New York Bella Waterproof Coat M',  34.99, 40),
    ('Zack & Zoey Nor''easter Dog Blanket Coat XL',  29.99, 30),

    -- Shoes / boots
    ('Muttluks Fleece-Lined Dog Boots Set of 4 M',   39.99, 40),
    ('Ruffwear Grip Trex Dog Boots Set of 4 S',      74.99, 25),
    ('Ultra Paws Durable Dog Boots Set of 4 L',      29.99, 35),
    ('Pawz Natural Rubber Dog Boots Medium 12ct',    16.99, 55),

    -- Recovery suits
    ('Suitical Recovery Suit Dog XS Black',          29.99, 45),
    ('Surgi-Snuggly Recovery Bodysuit Medium',       24.99, 50),
    ('Buckeye Surgical Recovery Suit Cat/Small Dog', 22.99, 55),
    ('iMatrix Recovery Suit Anti-Lick Vest Dog L',   34.99, 35)
) AS t(name, price, stock);


-- ============================================================
-- shop_item_attribute - one set per category
-- ============================================================

-- ---------- Food ----------
INSERT INTO shop_item_attribute (name, data_type, shop_item_category_id)
SELECT attr, dtype,
       (SELECT id FROM shop_item_category WHERE name = 'Food')
FROM (VALUES
    ('weight_kg',        'decimal'),
    ('target_species',   'text'),
    ('life_stage',       'text'),
    ('flavour',          'text'),
    ('grain_free',       'boolean'),
    ('kcal_per_100g',    'integer')
) AS t(attr, dtype);

-- ---------- Snacks ----------
INSERT INTO shop_item_attribute (name, data_type, shop_item_category_id)
SELECT attr, dtype,
       (SELECT id FROM shop_item_category WHERE name = 'Snacks')
FROM (VALUES
    ('weight_g',         'decimal'),
    ('target_species',   'text'),
    ('flavour',          'text'),
    ('primary_benefit',  'text'),
    ('suitable_age',     'text')
) AS t(attr, dtype);

-- ---------- Supplements ----------
INSERT INTO shop_item_attribute (name, data_type, shop_item_category_id)
SELECT attr, dtype,
       (SELECT id FROM shop_item_category WHERE name = 'Supplements')
FROM (VALUES
    ('target_species',   'text'),
    ('supplement_type',  'text'),
    ('units_per_pack',   'integer'),
    ('form',             'text'),
    ('key_ingredient',   'text')
) AS t(attr, dtype);

-- ---------- Accessories ----------
INSERT INTO shop_item_attribute (name, data_type, shop_item_category_id)
SELECT attr, dtype,
       (SELECT id FROM shop_item_category WHERE name = 'Accessories')
FROM (VALUES
    ('target_species',   'text'),
    ('size',             'text'),
    ('material',         'text'),
    ('colour',           'text'),
    ('suitable_for',     'text')
) AS t(attr, dtype);

-- ---------- Hygiene and Grooming ----------
INSERT INTO shop_item_attribute (name, data_type, shop_item_category_id)
SELECT attr, dtype,
       (SELECT id FROM shop_item_category WHERE name = 'Hygiene and Grooming')
FROM (VALUES
    ('target_species',   'text'),
    ('product_type',     'text'),
    ('volume_ml',        'decimal'),
    ('key_ingredient',   'text'),
    ('scent',            'text')
) AS t(attr, dtype);

-- ---------- Toys ----------
INSERT INTO shop_item_attribute (name, data_type, shop_item_category_id)
SELECT attr, dtype,
       (SELECT id FROM shop_item_category WHERE name = 'Toys')
FROM (VALUES
    ('target_species',   'text'),
    ('size',             'text'),
    ('material',         'text'),
    ('interactive',      'boolean'),
    ('primary_activity', 'text')
) AS t(attr, dtype);

-- ---------- Clothing ----------
INSERT INTO shop_item_attribute (name, data_type, shop_item_category_id)
SELECT attr, dtype,
       (SELECT id FROM shop_item_category WHERE name = 'Clothing')
FROM (VALUES
    ('target_species',   'text'),
    ('size',             'text'),
    ('material',         'text'),
    ('colour',           'text'),
    ('clothing_type',    'text'),
    ('waterproof',       'boolean')
) AS t(attr, dtype);

-- ---------- Medicine ----------
INSERT INTO shop_item_attribute (name, data_type, shop_item_category_id)
SELECT attr, dtype,
       (SELECT id FROM shop_item_category WHERE name = 'Medicine')
FROM (VALUES
    ('dosage_form',      'text'),
    ('strength',         'text'),
    ('pack_size',        'integer'),
    ('prescription_only','boolean'),
    ('target_species',   'text')
) AS t(attr, dtype);


-- ============================================================
-- shop_item_attribute_value
-- One value per (item x attribute) for all items in each category.
-- ============================================================

-- ----------------------------------------------------------------
-- Helper: map every shop_item in 'Food' to its attribute values
-- ----------------------------------------------------------------

INSERT INTO shop_item_attribute_value (value, shop_item_attribute_id, shop_item_id)
SELECT final_val.val, a.id, si.id
FROM shop_item si
JOIN shop_item_category sic ON sic.id = si.shop_item_category_id AND sic.name = 'Food'
CROSS JOIN LATERAL (
    SELECT
        CASE
          WHEN si.name ILIKE '%15kg%' OR si.name ILIKE '%12kg%' OR si.name ILIKE '%10kg%'
               THEN regexp_replace(si.name, '.*?(\d+(?:\.\d+)?)\s*kg.*', '\1')
          WHEN si.name ILIKE '%7kg%'  THEN '7'
          WHEN si.name ILIKE '%6kg%'  THEN '6'
          WHEN si.name ILIKE '%5.4kg%'THEN '5.4'
          WHEN si.name ILIKE '%4kg%'  THEN '4'
          WHEN si.name ILIKE '%3.5kg%'THEN '3.5'
          WHEN si.name ILIKE '%2kg%'  THEN '2'
          WHEN si.name ILIKE '%5lb%'  THEN '2.27'
          WHEN si.name ILIKE '%2lb%'  THEN '0.91'
          WHEN si.name ILIKE '%400g%' THEN '0.4'
          WHEN si.name ILIKE '%454g%' THEN '0.454'
          WHEN si.name ILIKE '%750g%' THEN '0.75'
          WHEN si.name ILIKE '%350g%' THEN '0.35'
          WHEN si.name ILIKE '%250ml%'OR si.name ILIKE '%250g%' THEN '0.25'
          WHEN si.name ILIKE '%150g%' THEN '0.15'
          ELSE '0.5'
        END  AS weight_kg,

        CASE
          WHEN si.name ILIKE '%dog%' OR si.name ILIKE '%pedigree%' OR si.name ILIKE '%cesar%'
               OR si.name ILIKE '%orijen%' AND si.name NOT ILIKE '%cat%'
               OR si.name ILIKE '%eukanuba%' THEN 'Dog'
          WHEN si.name ILIKE '%cat%' OR si.name ILIKE '%whiskas%' OR si.name ILIKE '%felix%'
               OR si.name ILIKE '%sheba%' THEN 'Cat'
          WHEN si.name ILIKE '%parrot%' OR si.name ILIKE '%canary%' OR si.name ILIKE '%budgie%'
               OR si.name ILIKE '%bird%' OR si.name ILIKE '%pellets%' AND si.name ILIKE '%birds%' THEN 'Bird'
          WHEN si.name ILIKE '%goldfish%' OR si.name ILIKE '%cichlid%' OR si.name ILIKE '%tropical%'
               OR si.name ILIKE '%flake%' OR si.name ILIKE '%bug bites%' OR si.name ILIKE '%vipan%' THEN 'Fish'
          WHEN si.name ILIKE '%mealworm%' OR si.name ILIKE '%cricket%' OR si.name ILIKE '%crested gecko%'
               OR si.name ILIKE '%reptile%' THEN 'Reptile'
          WHEN si.name ILIKE '%rabbit%' OR si.name ILIKE '%hamster%' OR si.name ILIKE '%guinea pig%'
               OR si.name ILIKE '%ferret%' THEN 'Small Mammal'
          ELSE 'Multi-species'
        END  AS target_species,

        CASE
          WHEN si.name ILIKE '%puppy%' OR si.name ILIKE '%kitten%' THEN 'Puppy/Kitten'
          WHEN si.name ILIKE '%senior%' OR si.name ILIKE '%mature%' THEN 'Senior'
          ELSE 'Adult'
        END  AS life_stage,

        CASE
          WHEN si.name ILIKE '%salmon%' THEN 'Salmon'
          WHEN si.name ILIKE '%chicken%' THEN 'Chicken'
          WHEN si.name ILIKE '%beef%' THEN 'Beef'
          WHEN si.name ILIKE '%tuna%' THEN 'Tuna'
          WHEN si.name ILIKE '%banana%' THEN 'Banana'
          WHEN si.name ILIKE '%honey%' THEN 'Honey'
          WHEN si.name ILIKE '%papaya%' THEN 'Papaya'
          ELSE 'Mixed'
        END  AS flavour,

        CASE
          WHEN si.name ILIKE '%orijen%' OR si.name ILIKE '%grain free%' THEN 'true'
          ELSE 'false'
        END  AS grain_free,

        CASE
          WHEN si.name ILIKE '%wet%' OR si.name ILIKE '%loaf%'
               OR si.name ILIKE '%400g%' OR si.name ILIKE '%150g%'
               OR si.name ILIKE '%85g%' OR si.name ILIKE '%canned%' THEN '95'
          WHEN si.name ILIKE '%orijen%' THEN '398'
          ELSE '340'
        END  AS kcal_per_100g
) v(weight_kg, target_species, life_stage, flavour, grain_free, kcal_per_100g)
JOIN shop_item_attribute a ON a.shop_item_category_id = sic.id
CROSS JOIN LATERAL (
    SELECT
        CASE a.name
          WHEN 'weight_kg'      THEN v.weight_kg
          WHEN 'target_species' THEN v.target_species
          WHEN 'life_stage'     THEN v.life_stage
          WHEN 'flavour'        THEN v.flavour
          WHEN 'grain_free'     THEN v.grain_free
          WHEN 'kcal_per_100g'  THEN v.kcal_per_100g
        END AS val
) final_val(val)
WHERE final_val.val IS NOT NULL;


-- ----------------------------------------------------------------
-- Snacks attribute values
-- ----------------------------------------------------------------

INSERT INTO shop_item_attribute_value (value, shop_item_attribute_id, shop_item_id)
SELECT final_val.val, a.id, si.id
FROM shop_item si
JOIN shop_item_category sic ON sic.id = si.shop_item_category_id AND sic.name = 'Snacks'
CROSS JOIN LATERAL (
    SELECT
        CASE
          WHEN si.name ILIKE '%24oz%' THEN '680'
          WHEN si.name ILIKE '%12oz%' THEN '340'
          WHEN si.name ILIKE '%6oz%'  THEN '170'
          WHEN si.name ILIKE '%85g%'  THEN '85'
          WHEN si.name ILIKE '%60g%'  THEN '60'
          WHEN si.name ILIKE '%180g%' THEN '180'
          WHEN si.name ILIKE '%3oz%'  THEN '85'
          ELSE '100'
        END  AS weight_g,

        CASE
          WHEN si.name ILIKE '%dog%' OR si.name ILIKE '%milk-bone%' OR si.name ILIKE '%dentastix%'
               OR si.name ILIKE '%greenies%' AND si.name NOT ILIKE '%feline%'
               OR si.name ILIKE '%bully%' OR si.name ILIKE '%nylabone%'
               OR si.name ILIKE '%wellbite%' OR si.name ILIKE '%merrick%' THEN 'Dog'
          WHEN si.name ILIKE '%cat%' OR si.name ILIKE '%temptation%' OR si.name ILIKE '%dreamies%'
               OR si.name ILIKE '%churu%' OR si.name ILIKE '%feline%' OR si.name ILIKE '%whiskas%' THEN 'Cat'
          WHEN si.name ILIKE '%budgie%' OR si.name ILIKE '%bird%' OR si.name ILIKE '%crunch stick%' THEN 'Bird'
          WHEN si.name ILIKE '%rabbit%' OR si.name ILIKE '%hamster%' OR si.name ILIKE '%guinea%'
               OR si.name ILIKE '%tiny friends%' OR si.name ILIKE '%oxbow%' THEN 'Small Mammal'
          ELSE 'Multi-species'
        END  AS target_species,

        CASE
          WHEN si.name ILIKE '%chicken%' THEN 'Chicken'
          WHEN si.name ILIKE '%salmon%'  THEN 'Salmon'
          WHEN si.name ILIKE '%lamb%'    THEN 'Lamb'
          WHEN si.name ILIKE '%tuna%'    THEN 'Tuna'
          WHEN si.name ILIKE '%cheese%'  THEN 'Cheese'
          WHEN si.name ILIKE '%bacon%'   THEN 'Bacon'
          WHEN si.name ILIKE '%honey%'   THEN 'Honey & Oat'
          WHEN si.name ILIKE '%strawberry%' THEN 'Strawberry'
          WHEN si.name ILIKE '%papaya%'  THEN 'Papaya'
          ELSE 'Mixed'
        END  AS flavour,

        CASE
          WHEN si.name ILIKE '%dental%' OR si.name ILIKE '%dentastix%' OR si.name ILIKE '%greenies%' THEN 'Dental health'
          WHEN si.name ILIKE '%churu%'  THEN 'Hydration & palatability'
          WHEN si.name ILIKE '%bully%'  THEN 'Mental stimulation & chewing'
          WHEN si.name ILIKE '%nylabone%' THEN 'Chewing & teething'
          ELSE 'Reward & training'
        END  AS primary_benefit,

        CASE
          WHEN si.name ILIKE '%puppy%' OR si.name ILIKE '%kitten%' THEN 'Puppy/Kitten'
          WHEN si.name ILIKE '%senior%' THEN 'Senior'
          ELSE 'All ages'
        END  AS suitable_age
) v(weight_g, target_species, flavour, primary_benefit, suitable_age)
JOIN shop_item_attribute a ON a.shop_item_category_id = sic.id
CROSS JOIN LATERAL (
    SELECT CASE a.name
        WHEN 'weight_g'        THEN v.weight_g
        WHEN 'target_species'  THEN v.target_species
        WHEN 'flavour'         THEN v.flavour
        WHEN 'primary_benefit' THEN v.primary_benefit
        WHEN 'suitable_age'    THEN v.suitable_age
    END AS val
) final_val(val)
WHERE final_val.val IS NOT NULL;


-- ----------------------------------------------------------------
-- Supplements attribute values
-- ----------------------------------------------------------------

INSERT INTO shop_item_attribute_value (value, shop_item_attribute_id, shop_item_id)
SELECT final_val.val, a.id, si.id
FROM shop_item si
JOIN shop_item_category sic ON sic.id = si.shop_item_category_id AND sic.name = 'Supplements'
CROSS JOIN LATERAL (
    SELECT
        CASE
          WHEN si.name ILIKE '%dog%' OR si.name ILIKE '%canine%' THEN 'Dog'
          WHEN si.name ILIKE '%cat%' OR si.name ILIKE '%feline%' THEN 'Cat'
          WHEN si.name ILIKE '%guinea%' THEN 'Guinea Pig'
          WHEN si.name ILIKE '%reptile%' THEN 'Reptile'
          WHEN si.name ILIKE '%fish%' OR si.name ILIKE '%tropical%' THEN 'Fish'
          ELSE 'Multi-species'
        END AS target_species,

        CASE
          WHEN si.name ILIKE '%multivitamin%' OR si.name ILIKE '%multi%' OR si.name ILIKE '%vita%' THEN 'Multivitamin'
          WHEN si.name ILIKE '%omega%' OR si.name ILIKE '%fish oil%' THEN 'Omega-3 / EFA'
          WHEN si.name ILIKE '%cosequin%' OR si.name ILIKE '%mobility%' OR si.name ILIKE '%joint%' THEN 'Joint support'
          WHEN si.name ILIKE '%probiotic%' THEN 'Probiotic'
          WHEN si.name ILIKE '%calming%' OR si.name ILIKE '%composure%' THEN 'Calming / stress'
          WHEN si.name ILIKE '%dental%' OR si.name ILIKE '%enzymatic%' THEN 'Dental health'
          WHEN si.name ILIKE '%vitamin c%' THEN 'Vitamin C'
          ELSE 'General health'
        END AS supplement_type,

        CASE
          WHEN si.name ILIKE '%90ct%' THEN '90'
          WHEN si.name ILIKE '%80ct%' THEN '80'
          WHEN si.name ILIKE '%60ct%' THEN '60'
          WHEN si.name ILIKE '%30ct%' THEN '30'
          ELSE '60'
        END AS units_per_pack,

        CASE
          WHEN si.name ILIKE '%bites%' OR si.name ILIKE '%chews%' THEN 'Soft chew'
          WHEN si.name ILIKE '%tabs%' OR si.name ILIKE '%tabs%'   THEN 'Tablet'
          WHEN si.name ILIKE '%caps%' OR si.name ILIKE '%capsule%' THEN 'Capsule'
          WHEN si.name ILIKE '%drops%' THEN 'Liquid drops'
          WHEN si.name ILIKE '%powder%' THEN 'Powder'
          ELSE 'Tablet'
        END AS form,

        CASE
          WHEN si.name ILIKE '%omega%' OR si.name ILIKE '%fish oil%' THEN 'EPA & DHA'
          WHEN si.name ILIKE '%cosequin%' OR si.name ILIKE '%joint%' THEN 'Glucosamine & Chondroitin'
          WHEN si.name ILIKE '%probiotic%' THEN 'Lactobacillus acidophilus'
          WHEN si.name ILIKE '%vitamin c%' THEN 'Ascorbic acid'
          WHEN si.name ILIKE '%calming%'   THEN 'L-Theanine & B vitamins'
          ELSE 'Vitamins A, D3, E, B-complex'
        END AS key_ingredient
) v(target_species, supplement_type, units_per_pack, form, key_ingredient)
JOIN shop_item_attribute a ON a.shop_item_category_id = sic.id
CROSS JOIN LATERAL (
    SELECT CASE a.name
        WHEN 'target_species'  THEN v.target_species
        WHEN 'supplement_type' THEN v.supplement_type
        WHEN 'units_per_pack'  THEN v.units_per_pack
        WHEN 'form'            THEN v.form
        WHEN 'key_ingredient'  THEN v.key_ingredient
    END AS val
) final_val(val)
WHERE final_val.val IS NOT NULL;


-- ----------------------------------------------------------------
-- Accessories attribute values
-- ----------------------------------------------------------------

INSERT INTO shop_item_attribute_value (value, shop_item_attribute_id, shop_item_id)
SELECT final_val.val, a.id, si.id
FROM shop_item si
JOIN shop_item_category sic
    ON sic.id = si.shop_item_category_id
   AND sic.name = 'Accessories'

CROSS JOIN LATERAL (
    SELECT
        CASE
            WHEN si.name ILIKE '%dog%'
              OR si.name ILIKE '%canine%'
              OR si.name ILIKE '%harness%'
              OR si.name ILIKE '%leash%'
              OR si.name ILIKE '%crate%'
              OR si.name ILIKE '%cooling bed%'
                THEN 'Dog'

            WHEN si.name ILIKE '%cat%'
              OR si.name ILIKE '%litter%'
              OR si.name ILIKE '%catit%'
              OR si.name ILIKE '%cat tree%'
                THEN 'Cat'

            WHEN si.name ILIKE '%hamster%'
              OR si.name ILIKE '%favola%'
                THEN 'Hamster'

            WHEN si.name ILIKE '%bird%'
              OR si.name ILIKE '%volière%'
              OR si.name ILIKE '%birdcage%'
                THEN 'Bird'

            WHEN si.name ILIKE '%terrarium%'
              OR si.name ILIKE '%exo terra%'
                THEN 'Reptile'

            WHEN si.name ILIKE '%aquarium%'
              OR si.name ILIKE '%fluval spec%'
                THEN 'Fish'

            ELSE 'Multi-species'
        END AS target_species,

        CASE
            WHEN si.name ILIKE '%xsmall%'
              OR si.name ILIKE '%xs%'
              OR si.name ILIKE '%extra small%'
                THEN 'XS'

            WHEN si.name ILIKE '%small%'
              OR si.name ILIKE '% s %'
                THEN 'S'

            WHEN si.name ILIKE '%medium%'
              OR si.name ILIKE '% m %'
                THEN 'M'

            WHEN si.name ILIKE '%large%'
              OR si.name ILIKE '% l %'
              OR si.name ILIKE '% xl%'
                THEN 'L'

            ELSE 'Universal'
        END AS size,

        CASE
            WHEN si.name ILIKE '%nylon%' THEN 'Nylon'
            WHEN si.name ILIKE '%leather%' THEN 'Leather'
            WHEN si.name ILIKE '%metal%'
              OR si.name ILIKE '%stainless%' THEN 'Stainless steel'
            WHEN si.name ILIKE '%plastic%' THEN 'Plastic'
            WHEN si.name ILIKE '%wire%'
              OR si.name ILIKE '%crate%' THEN 'Steel wire'
            WHEN si.name ILIKE '%wood%'
              OR si.name ILIKE '%tree%' THEN 'Sisal & wood'
            ELSE 'Mixed materials'
        END AS material,

        CASE
            WHEN si.name ILIKE '%black%' THEN 'Black'
            WHEN si.name ILIKE '%red%' THEN 'Red'
            WHEN si.name ILIKE '%blue%' THEN 'Blue'
            ELSE 'Assorted'
        END AS colour,

        CASE
            WHEN si.name ILIKE '%fountain%' THEN 'Hydration'
            WHEN si.name ILIKE '%harness%' THEN 'Walking / control'
            WHEN si.name ILIKE '%leash%'
              OR si.name ILIKE '%lead%' THEN 'Walking / restraint'
            WHEN si.name ILIKE '%collar%' THEN 'Identification & control'
            WHEN si.name ILIKE '%crate%' THEN 'Containment & transport'
            WHEN si.name ILIKE '%bed%' THEN 'Rest & comfort'
            WHEN si.name ILIKE '%litter%' THEN 'Hygiene'
            WHEN si.name ILIKE '%cage%'
              OR si.name ILIKE '%terrarium%'
              OR si.name ILIKE '%aquarium%'
                THEN 'Housing'
            WHEN si.name ILIKE '%puzzle%'
              OR si.name ILIKE '%food tree%'
                THEN 'Enrichment & feeding'
            ELSE 'General accessory'
        END AS suitable_for

) v(target_species, size, material, colour, suitable_for)

JOIN shop_item_attribute a
    ON a.shop_item_category_id = sic.id

CROSS JOIN LATERAL (
    SELECT CASE a.name
        WHEN 'target_species' THEN v.target_species
        WHEN 'size' THEN v.size
        WHEN 'material' THEN v.material
        WHEN 'colour' THEN v.colour
        WHEN 'suitable_for' THEN v.suitable_for
    END AS val
) final_val(val)

WHERE final_val.val IS NOT NULL;


-- ----------------------------------------------------------------
-- Hygiene and Grooming attribute values
-- ----------------------------------------------------------------

INSERT INTO shop_item_attribute_value (value, shop_item_attribute_id, shop_item_id)
SELECT final_val.val, a.id, si.id
FROM shop_item si
JOIN shop_item_category sic ON sic.id = si.shop_item_category_id AND sic.name = 'Hygiene and Grooming'
CROSS JOIN LATERAL (
    SELECT
        CASE
          WHEN si.name ILIKE '%cat%'   THEN 'Cat'
          WHEN si.name ILIKE '%dog%' OR si.name ILIKE '%canine%'
               OR si.name ILIKE '%bravura%' OR si.name ILIKE '%andis%'
               OR si.name ILIKE '%wahl%' OR si.name ILIKE '%furminator%' THEN 'Dog'
          ELSE 'Dog & Cat'
        END AS target_species,

        CASE
          WHEN si.name ILIKE '%shampoo%' THEN 'Shampoo'
          WHEN si.name ILIKE '%conditioner%' THEN 'Conditioner'
          WHEN si.name ILIKE '%clipper%' THEN 'Clipper'
          WHEN si.name ILIKE '%nail%' OR si.name ILIKE '%grinder%' THEN 'Nail care'
          WHEN si.name ILIKE '%brush%' OR si.name ILIKE '%comb%' OR si.name ILIKE '%deShedding tool%' THEN 'Brush / comb'
          WHEN si.name ILIKE '%dental%' OR si.name ILIKE '%oral%' OR si.name ILIKE '%toothbrush%' THEN 'Dental care'
          WHEN si.name ILIKE '%mousse%' THEN 'Medicated mousse'
          WHEN si.name ILIKE '%ear%' OR si.name ILIKE '%otic%' THEN 'Ear care'
          WHEN si.name ILIKE '%wipe%' THEN 'Wipes'
          WHEN si.name ILIKE '%water additive%' THEN 'Dental water additive'
          ELSE 'General grooming'
        END AS product_type,

        CASE
          WHEN si.name ILIKE '%500ml%' THEN '500'
          WHEN si.name ILIKE '%250ml%' THEN '250'
          WHEN si.name ILIKE '%236ml%' THEN '236'
          WHEN si.name ILIKE '%16oz%'  THEN '473'
          WHEN si.name ILIKE '%10oz%'  THEN '295'
          WHEN si.name ILIKE '%150ml%' THEN '150'
          ELSE NULL
        END AS volume_ml,

        CASE
          WHEN si.name ILIKE '%oatmeal%'     THEN 'Colloidal oatmeal'
          WHEN si.name ILIKE '%tea tree%'    THEN 'Tea tree oil'
          WHEN si.name ILIKE '%hypoallerg%'  THEN 'Aloe vera'
          WHEN si.name ILIKE '%enzymatic%'   THEN 'Glucose oxidase'
          WHEN si.name ILIKE '%antiseptic%'  THEN 'Chlorhexidine'
          WHEN si.name ILIKE '%flea%'        THEN 'Pyrethrin'
          ELSE 'Gentle cleansing agents'
        END AS key_ingredient,

        CASE
          WHEN si.name ILIKE '%fresh breath%' OR si.name ILIKE '%mint%' THEN 'Mint'
          WHEN si.name ILIKE '%fragrance free%' OR si.name ILIKE '%unscent%' THEN 'Unscented'
          WHEN si.name ILIKE '%oatmeal%' THEN 'Oatmeal'
          ELSE 'Lightly scented'
        END AS scent
) v(target_species, product_type, volume_ml, key_ingredient, scent)
JOIN shop_item_attribute a ON a.shop_item_category_id = sic.id
CROSS JOIN LATERAL (
    SELECT CASE a.name
        WHEN 'target_species' THEN v.target_species
        WHEN 'product_type'   THEN v.product_type
        WHEN 'volume_ml'      THEN v.volume_ml
        WHEN 'key_ingredient' THEN v.key_ingredient
        WHEN 'scent'          THEN v.scent
    END AS val
) final_val(val)
WHERE final_val.val IS NOT NULL;


-- ----------------------------------------------------------------
-- Toys attribute values
-- ----------------------------------------------------------------

INSERT INTO shop_item_attribute_value (value, shop_item_attribute_id, shop_item_id)
SELECT final_val.val, a.id, si.id
FROM shop_item si
JOIN shop_item_category sic ON sic.id = si.shop_item_category_id AND sic.name = 'Toys'
CROSS JOIN LATERAL (
    SELECT
        CASE
          WHEN si.name ILIKE '%dog%' OR si.name ILIKE '%kong%' AND si.name NOT ILIKE '%cat%'
               OR si.name ILIKE '%chuckit%' OR si.name ILIKE '%benebone%'
               OR si.name ILIKE '%iFetch%' OR si.name ILIKE '%zippy%'
               OR si.name ILIKE '%tug-a-jug%' THEN 'Dog'
          WHEN si.name ILIKE '%cat%' OR si.name ILIKE '%da bird%' OR si.name ILIKE '%smartykat%'
               OR si.name ILIKE '%petfusion%' OR si.name ILIKE '%catnip%'
               OR si.name ILIKE '%catit%' OR si.name ILIKE '%jackson galaxy%' THEN 'Cat'
          WHEN si.name ILIKE '%parrot%' OR si.name ILIKE '%bird%' OR si.name ILIKE '%perch%'
               OR si.name ILIKE '%ladder%' OR si.name ILIKE '%penn-plax%' AND si.name ILIKE '%mirror%' THEN 'Bird'
          WHEN si.name ILIKE '%hamster%' OR si.name ILIKE '%wheel%' OR si.name ILIKE '%exercise ball%'
               OR si.name ILIKE '%tunnel%' OR si.name ILIKE '%kaytee%' AND si.name NOT ILIKE '%dog%' THEN 'Small Mammal'
          WHEN si.name ILIKE '%skull%' OR si.name ILIKE '%betta%' OR si.name ILIKE '%aquarium%' THEN 'Fish'
          ELSE 'Multi-species'
        END AS target_species,

        CASE
          WHEN si.name ILIKE '%small%' OR si.name ILIKE '% s ' THEN 'Small'
          WHEN si.name ILIKE '%large%' OR si.name ILIKE '% l ' THEN 'Large'
          WHEN si.name ILIKE '%medium%' THEN 'Medium'
          ELSE 'Standard'
        END AS size,

        CASE
          WHEN si.name ILIKE '%rubber%' OR si.name ILIKE '%kong%' OR si.name ILIKE '%extreme%' THEN 'Natural rubber'
          WHEN si.name ILIKE '%plush%'  THEN 'Plush fabric'
          WHEN si.name ILIKE '%wood%' OR si.name ILIKE '%wooden%' OR si.name ILIKE '%ladder%' THEN 'Wood'
          WHEN si.name ILIKE '%plastic%' THEN 'ABS plastic'
          WHEN si.name ILIKE '%feather%' THEN 'Feather & wire'
          ELSE 'Mixed materials'
        END AS material,

        CASE
          WHEN si.name ILIKE '%interactive%' OR si.name ILIKE '%electronic%'
               OR si.name ILIKE '%launcher%' OR si.name ILIKE '%ambush%'
               OR si.name ILIKE '%wobbler%' OR si.name ILIKE '%smartykat%'
               OR si.name ILIKE '%senses%' OR si.name ILIKE '%tug-a-jug%' THEN 'true'
          ELSE 'false'
        END AS interactive,

        CASE
          WHEN si.name ILIKE '%dental%' OR si.name ILIKE '%chew%' OR si.name ILIKE '%benebone%'
               OR si.name ILIKE '%nylabone%' THEN 'Chewing & dental'
          WHEN si.name ILIKE '%fetch%' OR si.name ILIKE '%ball%' OR si.name ILIKE '%launcher%' THEN 'Fetch & chase'
          WHEN si.name ILIKE '%puzzle%' OR si.name ILIKE '%hide%' OR si.name ILIKE '%wobbler%'
               OR si.name ILIKE '%tug-a-jug%' OR si.name ILIKE '%food tree%' THEN 'Mental enrichment'
          WHEN si.name ILIKE '%wheel%' OR si.name ILIKE '%exercise%' OR si.name ILIKE '%tunnel%' THEN 'Exercise'
          WHEN si.name ILIKE '%catnip%' OR si.name ILIKE '%wand%' THEN 'Stimulation & hunting'
          WHEN si.name ILIKE '%plush%' OR si.name ILIKE '%squirrel%' OR si.name ILIKE '%squeaky%' THEN 'Comfort & play'
          ELSE 'General play'
        END AS primary_activity
) v(target_species, size, material, interactive, primary_activity)
JOIN shop_item_attribute a ON a.shop_item_category_id = sic.id
CROSS JOIN LATERAL (
    SELECT CASE a.name
        WHEN 'target_species'    THEN v.target_species
        WHEN 'size'              THEN v.size
        WHEN 'material'          THEN v.material
        WHEN 'interactive'       THEN v.interactive
        WHEN 'primary_activity'  THEN v.primary_activity
    END AS val
) final_val(val)
WHERE final_val.val IS NOT NULL;


-- ----------------------------------------------------------------
-- Clothing attribute values
-- ----------------------------------------------------------------

INSERT INTO shop_item_attribute_value (value, shop_item_attribute_id, shop_item_id)
SELECT final_val.val, a.id, si.id
FROM shop_item si
JOIN shop_item_category sic ON sic.id = si.shop_item_category_id AND sic.name = 'Clothing'
CROSS JOIN LATERAL (
    SELECT
        CASE
          WHEN si.name ILIKE '%cat%' OR si.name ILIKE '%surgi%' THEN 'Cat / Small Dog'
          ELSE 'Dog'
        END AS target_species,

        CASE
          WHEN si.name ILIKE '% xs%' OR si.name ILIKE '%xsmall%' OR si.name ILIKE '%extra small%' THEN 'XS'
          WHEN si.name ILIKE '% s %' OR si.name ILIKE '% s/'   OR si.name ILIKE '%small%'   THEN 'S'
          WHEN si.name ILIKE '% m %' OR si.name ILIKE '%medium%'                             THEN 'M'
          WHEN si.name ILIKE '% l %' OR si.name ILIKE '%large%'                             THEN 'L'
          WHEN si.name ILIKE '% xl%' OR si.name ILIKE '%xlarge%' OR si.name ILIKE '%extra large%' THEN 'XL'
          ELSE 'Assorted'
        END AS size,

        CASE
          WHEN si.name ILIKE '%fleece%'      THEN 'Fleece'
          WHEN si.name ILIKE '%rubber%'      THEN 'Natural rubber'
          WHEN si.name ILIKE '%waterproof%'  THEN 'Waterproof nylon'
          WHEN si.name ILIKE '%puffer%'      THEN 'Puffer nylon'
          WHEN si.name ILIKE '%parka%'       THEN 'Insulated nylon'
          WHEN si.name ILIKE '%recovery%' OR si.name ILIKE '%surgi%' OR si.name ILIKE '%snuggly%' THEN 'Stretch cotton blend'
          ELSE 'Polyester blend'
        END AS material,

        CASE
          WHEN si.name ILIKE '%black%'  THEN 'Black'
          WHEN si.name ILIKE '%blue%'   THEN 'Blue'
          WHEN si.name ILIKE '%red%'    THEN 'Red'
          WHEN si.name ILIKE '%pink%'   THEN 'Pink'
          ELSE 'Assorted'
        END AS colour,

        CASE
          WHEN si.name ILIKE '%hat%'                              THEN 'Hat'
          WHEN si.name ILIKE '%jacket%' OR si.name ILIKE '%parka%'
               OR si.name ILIKE '%coat%' OR si.name ILIKE '%puffer%'
               OR si.name ILIKE '%vest%' AND si.name NOT ILIKE '%recovery%' THEN 'Jacket / Coat'
          WHEN si.name ILIKE '%boot%'                            THEN 'Boots'
          WHEN si.name ILIKE '%recovery%' OR si.name ILIKE '%surgi%'
               OR si.name ILIKE '%suit%'                        THEN 'Recovery suit'
          ELSE 'Clothing'
        END AS clothing_type,

        CASE
          WHEN si.name ILIKE '%waterproof%' OR si.name ILIKE '%parka%'
               OR si.name ILIKE '%nor''easter%' OR si.name ILIKE '%grip trex%'
               OR si.name ILIKE '%muttluks%' THEN 'true'
          ELSE 'false'
        END AS waterproof
) v(target_species, size, material, colour, clothing_type, waterproof)
JOIN shop_item_attribute a ON a.shop_item_category_id = sic.id
CROSS JOIN LATERAL (
    SELECT CASE a.name
        WHEN 'target_species' THEN v.target_species
        WHEN 'size'           THEN v.size
        WHEN 'material'       THEN v.material
        WHEN 'colour'         THEN v.colour
        WHEN 'clothing_type'  THEN v.clothing_type
        WHEN 'waterproof'     THEN v.waterproof
    END AS val
) final_val(val)
WHERE final_val.val IS NOT NULL;


-- ----------------------------------------------------------------
-- Medicine attribute values (for the ~60 shop items in 'Medicine')
-- ----------------------------------------------------------------

INSERT INTO shop_item_attribute_value (value, shop_item_attribute_id, shop_item_id)
SELECT final_val.val, a.id, si.id
FROM shop_item si
JOIN shop_item_category sic ON sic.id = si.shop_item_category_id AND sic.name = 'Medicine'
CROSS JOIN LATERAL (
    SELECT
        CASE
          WHEN si.name ILIKE '%tablet%'   THEN 'Tablet'
          WHEN si.name ILIKE '%capsule%'  THEN 'Capsule'
          WHEN si.name ILIKE '%injection%'OR si.name ILIKE '%inj%' THEN 'Injection'
          WHEN si.name ILIKE '%solution%' OR si.name ILIKE '%suspension%'
               OR si.name ILIKE '%oral%'  THEN 'Oral solution'
          WHEN si.name ILIKE '%granule%'  THEN 'Granules'
          WHEN si.name ILIKE '%powder%'   THEN 'Powder'
          WHEN si.name ILIKE '%infusion%' THEN 'IV infusion'
          WHEN si.name ILIKE '%flush%'    THEN 'Sterile solution'
          ELSE 'Tablet'
        END AS dosage_form,

        -- extract strength from name (e.g. '250mg', '1.5mg/ml', '20%')
        COALESCE(
            (regexp_match(si.name,
                '(\d+(?:\.\d+)?\s*(?:mg|mcg|g|%|IU)(?:/ml|/\d+ml)?)'))[1],
            'See label'
        ) AS strength,

        CASE
          WHEN si.name ILIKE '%10 pack%' OR si.name ILIKE '%10ct%'   THEN '10'
          WHEN si.name ILIKE '%28%'                                   THEN '28'
          WHEN si.name ILIKE '%30%'                                   THEN '30'
          WHEN si.name ILIKE '%60%'                                   THEN '60'
          WHEN si.name ILIKE '%100ml%'                                THEN '1'   -- vials
          WHEN si.name ILIKE '%10ml%'                                 THEN '1'
          ELSE '30'
        END AS pack_size,

        'false' AS prescription_only,   -- all items in shop are OTC

        'Dog & Cat' AS target_species
) v(dosage_form, strength, pack_size, prescription_only, target_species)
JOIN shop_item_attribute a ON a.shop_item_category_id = sic.id
CROSS JOIN LATERAL (
    SELECT CASE a.name
        WHEN 'dosage_form'        THEN v.dosage_form
        WHEN 'strength'           THEN v.strength
        WHEN 'pack_size'          THEN v.pack_size
        WHEN 'prescription_only'  THEN v.prescription_only
        WHEN 'target_species'     THEN v.target_species
    END AS val
) final_val(val)
WHERE final_val.val IS NOT NULL;


-- ============================================================
-- remaining treatments (consultation, operation)
--           + treatment_attribute and treatment_attribute_value
--           for all 4 types:
--              prescription, vaccination, consultation, operation
-- ============================================================


-- ============================================================
-- treatment — consultation
-- One per completed examination that doesn't already have
-- a consultation treatment.
-- We give roughly 60% of completed exams a consultation.
-- ============================================================

INSERT INTO treatment (date_treatment, notes, treatment_type_id, examination_id)
SELECT
    e.date_examination + (floor(random() * 3))::int AS date_treatment,

    notes_list.notes,

    (SELECT id FROM treatment_type WHERE name = 'consultation') AS treatment_type_id,

    e.id AS examination_id

FROM examination e
CROSS JOIN LATERAL (
    SELECT notes
    FROM (VALUES
        ('Owner counselled on diet and weight management.'),
        ('Behavioural concerns discussed; referral considered.'),
        ('Vaccination schedule reviewed with owner.'),
        ('Pain management options explained to owner.'),
        ('Discussed long-term management of chronic condition.'),
        ('Follow-up plan agreed; owner given written summary.'),
        ('Discussed surgical options and associated risks.'),
        ('Dental hygiene advice provided; home care demonstrated.'),
        ('Parasite prevention programme reviewed.'),
        ('Nutritional counselling completed; diet change recommended.')
    ) AS n(notes)
    WHERE e.id IS NOT NULL
    ORDER BY random()
    LIMIT 1
) notes_list
WHERE e.status = 'completed'
  AND random() < 0.60
  AND NOT EXISTS (
      SELECT 1 FROM treatment t
      JOIN treatment_type tt ON tt.id = t.treatment_type_id
      WHERE t.examination_id = e.id AND tt.name = 'consultation'
  );


-- ============================================================
-- treatment — operation
-- ~20% of completed exams get an operation treatment.
-- ============================================================

INSERT INTO treatment (date_treatment, notes, treatment_type_id, examination_id)
SELECT
    e.date_examination + (floor(random() * 2))::int AS date_treatment,

    notes_list.notes,

    (SELECT id FROM treatment_type WHERE name = 'operation') AS treatment_type_id,

    e.id AS examination_id

FROM examination e
CROSS JOIN LATERAL (
    SELECT notes
    FROM (VALUES
        ('Surgery performed without complications.'),
        ('Procedure completed; patient recovering well.'),
        ('Intraoperative findings documented; owner informed.'),
        ('Operation successful; post-op care instructions given.'),
        ('Surgical site closed; patient moved to recovery ward.'),
        ('Procedure carried out under general anaesthesia.'),
        ('Minor intraoperative bleeding managed; outcome satisfactory.'),
        ('Patient stable post-operatively; monitoring ongoing.'),
        ('Surgery completed; follow-up scheduled in 10 days.'),
        ('Operative procedure completed; histopathology sample submitted.')
    ) AS n(notes)
    WHERE e.id IS NOT NULL
    ORDER BY random()
    LIMIT 1
) notes_list
WHERE e.status = 'completed'
  AND random() < 0.20
  AND NOT EXISTS (
      SELECT 1 FROM treatment t
      JOIN treatment_type tt ON tt.id = t.treatment_type_id
      WHERE t.examination_id = e.id AND tt.name = 'operation'
  );


-- ============================================================
-- treatment_attribute
-- One attribute set per treatment_type.
-- ============================================================

-- ---------- prescription ----------
INSERT INTO treatment_attribute (name, data_type, treatment_type_id)
SELECT attr, dtype,
       (SELECT id FROM treatment_type WHERE name = 'prescription')
FROM (VALUES
    ('medication_class',   'text'),
    ('route',              'text'),
    ('refills_allowed',    'integer'),
    ('withdrawal_period',  'text')
) AS t(attr, dtype);

-- ---------- vaccination ----------
INSERT INTO treatment_attribute (name, data_type, treatment_type_id)
SELECT attr, dtype,
       (SELECT id FROM treatment_type WHERE name = 'vaccination')
FROM (VALUES
    ('vaccine_name',       'text'),
    ('manufacturer',       'text'),
    ('batch_number',       'text'),
    ('num_doses',          'integer'),
    ('dose_number',        'integer'),
    ('route',              'text'),
    ('site',               'text'),
    ('date_next',          'date'),
    ('adverse_reaction',   'boolean')
) AS t(attr, dtype);

-- ---------- consultation ----------
INSERT INTO treatment_attribute (name, data_type, treatment_type_id)
SELECT attr, dtype,
       (SELECT id FROM treatment_type WHERE name = 'consultation')
FROM (VALUES
    ('topic',              'text'),
    ('description',        'text'),
    ('referral',           'boolean'),
    ('referral_to',        'text'),
    ('follow_up_days',     'integer'),
    ('owner_present',      'boolean')
) AS t(attr, dtype);

-- ---------- operation ----------
INSERT INTO treatment_attribute (name, data_type, treatment_type_id)
SELECT attr, dtype,
       (SELECT id FROM treatment_type WHERE name = 'operation')
FROM (VALUES
    ('operation_type',     'text'),
    ('status',             'text'),
    ('anesthesia',         'text'),
    ('duration_minutes',   'integer'),
    ('date_checkup',       'date'),
    ('surgeon',            'text'),
    ('complications',      'boolean')
) AS t(attr, dtype);


-- ============================================================
-- treatment_attribute_value
-- One value per (treatment x attribute) for every treatment.
-- ============================================================

-- ----------------------------------------------------------------
-- PRESCRIPTION treatment attribute values
-- ----------------------------------------------------------------

INSERT INTO treatment_attribute_value (value, notes, treatment_attribute_id, treatment_id)
SELECT
    final_val.val,
    NULL,
    a.id,
    t.id
FROM treatment t
JOIN treatment_type tt ON tt.id = t.treatment_type_id AND tt.name = 'prescription'
JOIN treatment_attribute a ON a.treatment_type_id = tt.id
CROSS JOIN LATERAL (
    -- generate all 6 attribute values in one lateral
    SELECT
        (ARRAY['Antibiotic','NSAID','Corticosteroid','Antiparasitic',
               'Antifungal','Analgesic','Anticonvulsant','Cardiac',
               'Gastrointestinal','Immunosuppressant'])
            [1 + (abs(hashtext(t.id::text || 'cls')) % 10)]   AS medication_class,

        (ARRAY['Oral','Subcutaneous injection','Intramuscular injection',
               'Topical','Intravenous','Ophthalmic'])
            [1 + (abs(hashtext(t.id::text || 'rte')) % 6)]    AS route,

        (abs(hashtext(t.id::text || 'ref')) % 3)::text        AS refills_allowed,

        CASE (abs(hashtext(t.id::text || 'wth')) % 3)
            WHEN 0 THEN 'None'
            WHEN 1 THEN '24 hours'
            ELSE        '48 hours'
        END                                                    AS withdrawal_period
) v
CROSS JOIN LATERAL (
    SELECT CASE a.name
        WHEN 'medication_class'  THEN v.medication_class
        WHEN 'route'             THEN v.route
        WHEN 'refills_allowed'   THEN v.refills_allowed
        WHEN 'withdrawal_period' THEN v.withdrawal_period
    END AS val
) final_val
WHERE final_val.val IS NOT NULL;


-- ----------------------------------------------------------------
-- VACCINATION treatment attribute values
-- ----------------------------------------------------------------

INSERT INTO treatment_attribute_value (value, notes, treatment_attribute_id, treatment_id)
SELECT
    final_val.val,
    NULL,
    a.id,
    t.id
FROM treatment t
JOIN treatment_type tt ON tt.id = t.treatment_type_id AND tt.name = 'vaccination'
JOIN treatment_attribute a ON a.treatment_type_id = tt.id
CROSS JOIN LATERAL (
    SELECT
        (ARRAY[
            'Nobivac DHPPi',
            'Nobivac Rabies',
            'Feligen CRP',
            'Purevax RCPCh',
            'Versican Plus DHPPi/L4',
            'Canigen L4',
            'Nobivac Lepto 4',
            'Eurican Herpes 205',
            'Felocell CVR',
            'Quantum Cat 7'
        ])[1 + (abs(hashtext(t.id::text || 'vac')) % 10)]    AS vaccine_name,

        (ARRAY['Zoetis','MSD Animal Health','Boehringer Ingelheim',
               'Virbac','Elanco'])
            [1 + (abs(hashtext(t.id::text || 'mfr')) % 5)]   AS manufacturer,

        'BN-' || lpad((abs(hashtext(t.id::text || 'bn')) % 900000 + 100000)::text, 6, '0')
                                                               AS batch_number,

        CASE (abs(hashtext(t.id::text || 'nd')) % 3)
            WHEN 0 THEN '1'
            WHEN 1 THEN '2'
            ELSE        '3'
        END                                                    AS num_doses,

        -- dose number in series (1 or 2)
        CASE (abs(hashtext(t.id::text || 'dn')) % 2)
            WHEN 0 THEN '1'
            ELSE        '2'
        END                                                    AS dose_number,

        (ARRAY['Subcutaneous','Intramuscular','Intranasal'])
            [1 + (abs(hashtext(t.id::text || 'rt2')) % 3)]    AS route,

        (ARRAY['Right scruff','Left scruff','Right hindlimb','Left hindlimb'])
            [1 + (abs(hashtext(t.id::text || 'ste')) % 4)]    AS site,

        -- date_next: 1 year after treatment date
        (t.date_treatment + interval '1 year')::date::text    AS date_next,

        -- adverse reaction: 3% chance
        CASE WHEN (abs(hashtext(t.id::text || 'adv')) % 100) < 3
            THEN 'true' ELSE 'false'
        END                                                    AS adverse_reaction
) v
CROSS JOIN LATERAL (
    SELECT CASE a.name
        WHEN 'vaccine_name'     THEN v.vaccine_name
        WHEN 'manufacturer'     THEN v.manufacturer
        WHEN 'batch_number'     THEN v.batch_number
        WHEN 'num_doses'        THEN v.num_doses
        WHEN 'dose_number'      THEN v.dose_number
        WHEN 'route'            THEN v.route
        WHEN 'site'             THEN v.site
        WHEN 'date_next'        THEN v.date_next
        WHEN 'adverse_reaction' THEN v.adverse_reaction
    END AS val
) final_val
WHERE final_val.val IS NOT NULL;


-- ----------------------------------------------------------------
-- CONSULTATION treatment attribute values
-- ----------------------------------------------------------------

INSERT INTO treatment_attribute_value (value, notes, treatment_attribute_id, treatment_id)
SELECT
    final_val.val,
    NULL,
    a.id,
    t.id
FROM treatment t
JOIN treatment_type tt ON tt.id = t.treatment_type_id AND tt.name = 'consultation'
JOIN treatment_attribute a ON a.treatment_type_id = tt.id
CROSS JOIN LATERAL (
    SELECT
        (ARRAY[
            'Nutrition and weight management',
            'Behavioural assessment',
            'Chronic disease management',
            'Pre-surgical counselling',
            'Post-operative care planning',
            'Dental hygiene advice',
            'Parasite prevention review',
            'Vaccination schedule planning',
            'End-of-life care discussion',
            'Second opinion review'
        ])[1 + (abs(hashtext(t.id::text || 'top')) % 10)]     AS topic,

        (ARRAY[
            'Owner presented concerns regarding the pet''s appetite and energy levels. A full dietary review was completed and a lower-calorie prescription diet was recommended. Owner was shown how to measure portions correctly and advised to recheck in 4 weeks.',
            'Behavioural history taken in detail. Pet displays anxiety-related signs including excessive grooming and vocalization. Environmental enrichment strategies discussed and a referral to a veterinary behaviourist was considered.',
            'Long-term management plan for the pet''s diagnosed chronic kidney disease was reviewed. Blood results interpreted and ACE inhibitor dosage adjusted. Owner counselled on signs of deterioration and instructed to return if vomiting or lethargy develops.',
            'Pre-surgical consultation completed for elective spay procedure. Risks and benefits of anaesthesia explained. Pre-operative blood panel ordered. Owner provided written consent and fasting instructions.',
            'Post-operative wound checked and healing progress assessed. Owner demonstrated correct application of topical antiseptic and Elizabeth collar use. Suture removal booked for 10 days post-op.',
            'Dental examination findings discussed with owner. Stage 2 periodontal disease identified. Professional scale and polish recommended. Home brushing technique demonstrated using enzymatic toothpaste.',
            'Current parasite prevention programme assessed. Owner using an incomplete product that does not cover lungworm. Updated protocol prescribed combining monthly spot-on and quarterly wormer. Environmental hygiene advice given.',
            'Full vaccination history reviewed. Pet was overdue for core and leptospirosis boosters. Schedule re-established; primary course restarted where titre testing was not available. Owner reminded of annual requirement.',
            'Compassionate discussion held with owner regarding quality of life for their senior pet with advanced neoplasia. Palliative care options including pain management and hospice-style support outlined. Owner given time to consider options.',
            'Second opinion consultation for recurrent skin condition. Previous treatment history reviewed. Differential diagnoses reconsidered; skin biopsy recommended to rule out immune-mediated disease.'
        ])[1 + (abs(hashtext(t.id::text || 'dsc')) % 10)]     AS description,

        -- referral: 15% chance
        CASE WHEN (abs(hashtext(t.id::text || 'ref')) % 100) < 15
            THEN 'true' ELSE 'false'
        END                                                     AS referral,

        CASE WHEN (abs(hashtext(t.id::text || 'ref')) % 100) < 15
            THEN (ARRAY[
                'Veterinary Dermatologist',
                'Veterinary Cardiologist',
                'Veterinary Behaviourist',
                'Veterinary Oncologist',
                'Veterinary Ophthalmologist',
                'Veterinary Neurologist'
            ])[1 + (abs(hashtext(t.id::text || 'rto')) % 6)]
            ELSE NULL
        END                                                     AS referral_to,

        -- follow-up in 7, 14, 21 or 30 days
        (ARRAY['7','14','21','30'])
            [1 + (abs(hashtext(t.id::text || 'fup')) % 4)]    AS follow_up_days,

        -- owner present: almost always true
        CASE WHEN (abs(hashtext(t.id::text || 'own')) % 10) < 9
            THEN 'true' ELSE 'false'
        END                                                     AS owner_present
) v
CROSS JOIN LATERAL (
    SELECT CASE a.name
        WHEN 'topic'          THEN v.topic
        WHEN 'description'    THEN v.description
        WHEN 'referral'       THEN v.referral
        WHEN 'referral_to'    THEN v.referral_to
        WHEN 'follow_up_days' THEN v.follow_up_days
        WHEN 'owner_present'  THEN v.owner_present
    END AS val
) final_val
WHERE final_val.val IS NOT NULL;


-- ----------------------------------------------------------------
-- OPERATION treatment attribute values
-- ----------------------------------------------------------------

INSERT INTO treatment_attribute_value (value, notes, treatment_attribute_id, treatment_id)
SELECT
    final_val.val,
    NULL,
    a.id,
    t.id
FROM treatment t
JOIN treatment_type tt ON tt.id = t.treatment_type_id AND tt.name = 'operation'
JOIN treatment_attribute a ON a.treatment_type_id = tt.id
CROSS JOIN LATERAL (
    SELECT
        (ARRAY[
            'Ovariohysterectomy (spay)',
            'Orchiectomy (neuter)',
            'Mass / tumour excision',
            'Fracture repair (ORIF)',
            'Intestinal foreign body removal',
            'Cystotomy (bladder stone removal)',
            'Gastropexy',
            'Enucleation',
            'Amputation',
            'Caesarean section',
            'Exploratory laparotomy',
            'Cruciate ligament repair (TPLO)',
            'Dental extraction',
            'Wound debridement and closure',
            'Thoracostomy tube placement'
        ])[1 + (abs(hashtext(t.id::text || 'opt')) % 15)]    AS operation_type,

        -- status: mostly successful
        (ARRAY['Successful','Successful','Successful','Successful',
               'Complicated','Unsuccessful'])
            [1 + (abs(hashtext(t.id::text || 'sts')) % 6)]   AS status,

        (ARRAY[
            'Propofol induction / Isoflurane maintenance',
            'Alfaxalone induction / Isoflurane maintenance',
            'Ketamine-Midazolam / Isoflurane maintenance',
            'Propofol TIVA',
            'Medetomidine-Butorphanol sedation (minor procedure)'
        ])[1 + (abs(hashtext(t.id::text || 'ans')) % 5)]     AS anesthesia,

        -- duration: 15–180 minutes
        (15 + (abs(hashtext(t.id::text || 'dur')) % 166))::text AS duration_minutes,

        -- checkup date: 7–14 days after treatment
        (t.date_treatment + (7 + abs(hashtext(t.id::text || 'chk')) % 8))::text
                                                               AS date_checkup,

        -- surgeon: reference one of the vets by name (lookup from employee)
        (
            SELECT e.first_name || ' ' || e.last_name
            FROM employee e
            WHERE e.role_id = 2
            ORDER BY abs(hashtext(t.id::text || 'srg' || e.id::text))
            LIMIT 1
        )                                                      AS surgeon,

        -- complications: ~12%
        CASE WHEN (abs(hashtext(t.id::text || 'cmp')) % 100) < 12
            THEN 'true' ELSE 'false'
        END                                                    AS complications
) v
CROSS JOIN LATERAL (
    SELECT CASE a.name
        WHEN 'operation_type'    THEN v.operation_type
        WHEN 'status'            THEN v.status
        WHEN 'anesthesia'        THEN v.anesthesia
        WHEN 'duration_minutes'  THEN v.duration_minutes
        WHEN 'date_checkup'      THEN v.date_checkup
        WHEN 'surgeon'           THEN v.surgeon
        WHEN 'complications'     THEN v.complications
    END AS val
) final_val
WHERE final_val.val IS NOT NULL;



-- temp table for imported medicines
create table temp_med1
(
    id    bigserial primary key,
    name varchar
);

-- drop table temp_med1 cascade;

COPY temp_med1 (name) FROM 'C:/Program Files/PostgreSQL/18/data/temp_files/medicine.csv' DELIMITER ',' CSV HEADER;
UPDATE temp_med1
SET name = TRIM(name);


-- ============================================================
-- STEP 3a — Insert medicines from temp_med1
-- Assign a plausible manufacturer and description based on
-- the medicine name. shop_item_id is NULL (prescription-only
-- or clinic-use; they are not in the shop).
-- ============================================================

INSERT INTO medicine (name, manufacturer, description, shop_item_id)
SELECT
    t.name,

    -- deterministic but varied manufacturer
    (ARRAY[
        'Zoetis Inc.',
        'Boehringer Ingelheim',
        'Elanco Animal Health',
        'Virbac Animal Health',
        'Dechra Veterinary',
        'Norbrook Laboratories',
        'Vetoquinol',
        'Bayer Animal Health',
        'MSD Animal Health',
        'Pfizer Animal Health',
        'Jurox Animal Health',
        'Intervet-Schering Plough',
        'Novartis Animal Health',
        'Merial',
        'Orion Pharma'
    ])[1 + (abs(hashtext(t.name || 'mfr')) % 15)] AS manufacturer,

    -- plausible description derived from name keywords
    CASE
        WHEN t.name ILIKE '%amoxicillin%' OR t.name ILIKE '%ampicillin%'
             OR t.name ILIKE '%penicillin%'
            THEN 'Penicillin-class antibiotic for bacterial infections in companion animals'

        WHEN t.name ILIKE '%cefalex%' OR t.name ILIKE '%cephalex%'
             OR t.name ILIKE '%cefovecin%' OR t.name ILIKE '%cefpodox%'
             OR t.name ILIKE '%cef%'
            THEN 'Cephalosporin antibiotic for skin, soft tissue and urinary tract infections'

        WHEN t.name ILIKE '%enroflox%' OR t.name ILIKE '%marboflox%'
             OR t.name ILIKE '%pradoflox%' OR t.name ILIKE '%ciproflox%'
             OR t.name ILIKE '%floxacin%'
            THEN 'Fluoroquinolone antibiotic for gram-negative and soft tissue infections'

        WHEN t.name ILIKE '%doxycyclin%' OR t.name ILIKE '%tetracyclin%'
             OR t.name ILIKE '%minocyclin%'
            THEN 'Tetracycline-class antibiotic effective against intracellular and tick-borne pathogens'

        WHEN t.name ILIKE '%metronidazol%'
            THEN 'Nitroimidazole antibiotic and antiprotozoal for GI and anaerobic infections'

        WHEN t.name ILIKE '%clindamycin%' OR t.name ILIKE '%lincomycin%'
            THEN 'Lincosamide antibiotic for anaerobic, dental and deep tissue infections'

        WHEN t.name ILIKE '%azithromycin%' OR t.name ILIKE '%tylosin%'
             OR t.name ILIKE '%erythromycin%'
            THEN 'Macrolide antibiotic for respiratory and intracellular bacterial infections'

        WHEN t.name ILIKE '%trimethoprim%' OR t.name ILIKE '%sulfa%'
             OR t.name ILIKE '%sulfameth%'
            THEN 'Sulfonamide combination antibiotic for urinary and respiratory tract infections'

        WHEN t.name ILIKE '%vancomycin%' OR t.name ILIKE '%linezolid%'
            THEN 'Reserved-use antibiotic for multidrug-resistant gram-positive infections'

        WHEN t.name ILIKE '%amikacin%' OR t.name ILIKE '%gentamicin%'
             OR t.name ILIKE '%tobramycin%'
            THEN 'Aminoglycoside antibiotic for serious gram-negative infections; requires renal monitoring'

        WHEN t.name ILIKE '%imipenem%' OR t.name ILIKE '%meropenem%'
             OR t.name ILIKE '%ertapenem%'
            THEN 'Carbapenem antibiotic reserved for multidrug-resistant bacterial infections'

        WHEN t.name ILIKE '%prednisolon%' OR t.name ILIKE '%prednison%'
            THEN 'Corticosteroid for inflammatory and immune-mediated conditions'

        WHEN t.name ILIKE '%dexamethasone%'
            THEN 'Potent corticosteroid for acute inflammatory and allergic reactions'

        WHEN t.name ILIKE '%methylprednisolon%'
            THEN 'Intermediate-acting corticosteroid for chronic inflammatory disease'

        WHEN t.name ILIKE '%hydrocortisone%'
            THEN 'Mild corticosteroid for adrenal insufficiency and mild inflammation'

        WHEN t.name ILIKE '%betamethasone%' OR t.name ILIKE '%triamcinolone%'
            THEN 'Potent long-acting corticosteroid; used topically and intra-articularly'

        WHEN t.name ILIKE '%carprofen%' OR t.name ILIKE '%meloxicam%'
             OR t.name ILIKE '%robenacoxib%' OR t.name ILIKE '%mavacoxib%'
             OR t.name ILIKE '%grapiprant%'
            THEN 'NSAID for pain and inflammation in musculoskeletal and post-operative conditions'

        WHEN t.name ILIKE '%tramadol%'
            THEN 'Opioid analgesic for moderate to severe pain management'

        WHEN t.name ILIKE '%buprenorphin%'
            THEN 'Partial mu-opioid agonist for perioperative and chronic pain'

        WHEN t.name ILIKE '%fentanyl%' OR t.name ILIKE '%methadon%'
             OR t.name ILIKE '%morphin%'
            THEN 'Full opioid agonist for perioperative and severe acute pain'

        WHEN t.name ILIKE '%gabapentin%' OR t.name ILIKE '%pregabalin%'
            THEN 'Anticonvulsant and analgesic for neuropathic pain and seizure management'

        WHEN t.name ILIKE '%phenobarbit%'
            THEN 'Barbiturate anticonvulsant for idiopathic epilepsy in dogs and cats'

        WHEN t.name ILIKE '%levetiracetam%'
            THEN 'Novel anticonvulsant with a favourable safety profile for refractory epilepsy'

        WHEN t.name ILIKE '%potassium bromide%'
            THEN 'Adjunctive anticonvulsant for dogs with refractory epilepsy'

        WHEN t.name ILIKE '%omeprazol%' OR t.name ILIKE '%pantoprazol%'
             OR t.name ILIKE '%esomeprazol%'
            THEN 'Proton pump inhibitor for gastric ulcer prevention and acid reflux'

        WHEN t.name ILIKE '%famotidin%' OR t.name ILIKE '%ranitidine%'
            THEN 'H2 receptor blocker for gastric hyperacidity and stress ulceration'

        WHEN t.name ILIKE '%maropitant%'
            THEN 'NK1 receptor antagonist antiemetic for vomiting and motion sickness'

        WHEN t.name ILIKE '%ondansetron%' OR t.name ILIKE '%dolasetron%'
            THEN 'Serotonin antagonist antiemetic for chemotherapy-induced and refractory nausea'

        WHEN t.name ILIKE '%metoclopramide%'
            THEN 'Prokinetic antiemetic for gastric motility disorders and vomiting'

        WHEN t.name ILIKE '%sucralfate%'
            THEN 'Mucosal protectant for gastric and duodenal ulcers'

        WHEN t.name ILIKE '%lactulose%'
            THEN 'Osmotic laxative for hepatic encephalopathy and chronic constipation'

        WHEN t.name ILIKE '%furosemide%' OR t.name ILIKE '%torsemide%'
            THEN 'Loop diuretic for congestive heart failure and oedema management'

        WHEN t.name ILIKE '%spironolacton%'
            THEN 'Potassium-sparing diuretic for cardiac and hepatic disease'

        WHEN t.name ILIKE '%enalapril%' OR t.name ILIKE '%benazepril%'
             OR t.name ILIKE '%ramipril%' OR t.name ILIKE '%lisinopril%'
            THEN 'ACE inhibitor for hypertension and congestive heart failure'

        WHEN t.name ILIKE '%telmisartan%' OR t.name ILIKE '%losartan%'
            THEN 'Angiotensin II receptor blocker for hypertension and CKD proteinuria'

        WHEN t.name ILIKE '%amlodipine%' OR t.name ILIKE '%diltiazem%'
            THEN 'Calcium channel blocker for hypertension and hypertrophic cardiomyopathy'

        WHEN t.name ILIKE '%atenolol%' OR t.name ILIKE '%metoprolol%'
             OR t.name ILIKE '%propranolol%'
            THEN 'Beta-blocker for arrhythmias and hypertrophic cardiomyopathy'

        WHEN t.name ILIKE '%pimobendan%'
            THEN 'Inodilator for dilated cardiomyopathy and mitral valve disease'

        WHEN t.name ILIKE '%digoxin%'
            THEN 'Cardiac glycoside for atrial fibrillation and congestive heart failure'

        WHEN t.name ILIKE '%sildenafil%'
            THEN 'PDE-5 inhibitor for pulmonary arterial hypertension'

        WHEN t.name ILIKE '%heparin%' OR t.name ILIKE '%clopidogrel%'
            THEN 'Anticoagulant / antiplatelet for thromboembolism prevention'

        WHEN t.name ILIKE '%levothyroxin%'
            THEN 'Thyroid hormone replacement for canine hypothyroidism'

        WHEN t.name ILIKE '%methimazol%' OR t.name ILIKE '%carbimazol%'
            THEN 'Thioamide antithyroid agent for feline hyperthyroidism'

        WHEN t.name ILIKE '%trilostane%'
            THEN '3beta-HSD inhibitor for hyperadrenocorticism (Cushing disease)'

        WHEN t.name ILIKE '%mitotane%'
            THEN 'Adrenocorticolytic agent for pituitary-dependent hyperadrenocorticism'

        WHEN t.name ILIKE '%insulin%'
            THEN 'Insulin preparation for the management of diabetes mellitus'

        WHEN t.name ILIKE '%cyclosporin%' OR t.name ILIKE '%tacrolimus%'
            THEN 'Calcineurin inhibitor immunosuppressant for immune-mediated disease'

        WHEN t.name ILIKE '%apoquel%' OR t.name ILIKE '%oclacitinib%'
            THEN 'JAK inhibitor for pruritus and allergic dermatitis in dogs'

        WHEN t.name ILIKE '%hydroxyzin%' OR t.name ILIKE '%diphenhydramin%'
             OR t.name ILIKE '%chlorphenamin%'
            THEN 'Antihistamine for pruritic skin disease and allergic reactions'

        WHEN t.name ILIKE '%ketoconazol%' OR t.name ILIKE '%fluconazol%'
             OR t.name ILIKE '%itraconazol%' OR t.name ILIKE '%voriconazol%'
             OR t.name ILIKE '%terbinafin%'
            THEN 'Azole or allylamine antifungal for dermatophytosis and systemic mycoses'

        WHEN t.name ILIKE '%amphotericin%'
            THEN 'Polyene antifungal for systemic mycoses; nephrotoxicity monitoring required'

        WHEN t.name ILIKE '%fenbendazol%' OR t.name ILIKE '%mebendazol%'
             OR t.name ILIKE '%albendazol%'
            THEN 'Benzimidazole anthelmintic for roundworms, hookworms and Giardia'

        WHEN t.name ILIKE '%praziquantel%'
            THEN 'Cestocidal agent for tapeworm infections in companion animals'

        WHEN t.name ILIKE '%ivermectin%' OR t.name ILIKE '%milbemycin%'
             OR t.name ILIKE '%moxidectin%' OR t.name ILIKE '%selamectin%'
            THEN 'Macrocyclic lactone for internal and external parasite control'

        WHEN t.name ILIKE '%pyrantel%'
            THEN 'Anthelmintic for roundworm and hookworm infections'

        WHEN t.name ILIKE '%propofol%'
            THEN 'Intravenous induction agent for general anaesthesia; rapid onset and recovery'

        WHEN t.name ILIKE '%alfaxalon%'
            THEN 'Neurosteroid anaesthetic for induction and TIVA in cats and dogs'

        WHEN t.name ILIKE '%ketamin%'
            THEN 'Dissociative anaesthetic used in combination sedation and anaesthetic protocols'

        WHEN t.name ILIKE '%midazolam%' OR t.name ILIKE '%diazepam%'
            THEN 'Benzodiazepine for sedation, co-induction and status epilepticus management'

        WHEN t.name ILIKE '%medetomidin%' OR t.name ILIKE '%dexmedetomidin%'
            THEN 'Alpha-2 adrenergic agonist for sedation and pre-anaesthetic medication'

        WHEN t.name ILIKE '%atropin%'
            THEN 'Anticholinergic for bradycardia, organophosphate toxicosis and pre-anaesthesia'

        WHEN t.name ILIKE '%vitamin%' OR t.name ILIKE '%b12%'
             OR t.name ILIKE '%cobalamin%'
            THEN 'Vitamin supplement for deficiency states and supportive therapy'

        WHEN t.name ILIKE '%iron%' OR t.name ILIKE '%ferrous%'
            THEN 'Iron supplement for iron-deficiency anaemia'

        WHEN t.name ILIKE '%calcium%'
            THEN 'Calcium supplementation for hypocalcaemia and eclampsia'

        WHEN t.name ILIKE '%saline%' OR t.name ILIKE '%sodium chloride%'
             OR t.name ILIKE '%flush%'
            THEN 'Sterile isotonic saline for fluid therapy and catheter flushing'

        WHEN t.name ILIKE '%mannitol%'
            THEN 'Osmotic diuretic for cerebral oedema and acute angle-closure glaucoma'

        WHEN t.name ILIKE '%dextrose%' OR t.name ILIKE '%glucose%'
            THEN 'Concentrated glucose solution for hypoglycaemia; dilute before intravenous use'

        WHEN t.name ILIKE '%dopamine%' OR t.name ILIKE '%dobutamine%'
             OR t.name ILIKE '%norepinephrin%' OR t.name ILIKE '%epinephrin%'
             OR t.name ILIKE '%adrenalin%'
            THEN 'Catecholamine vasopressor / inotrope for shock and acute haemodynamic instability'

        WHEN t.name ILIKE '%plasma%' OR t.name ILIKE '%albumin%'
            THEN 'Blood product for coagulopathy, hypoproteinaemia and volume replacement'

        WHEN t.name ILIKE '%hydroxyethyl%' OR t.name ILIKE '%hetastarch%'
             OR t.name ILIKE '%gelatin%'
            THEN 'Colloid volume expander for hypovolaemia and hypoproteinaemia'

        WHEN t.name ILIKE '%misoprostol%'
            THEN 'Prostaglandin E1 analogue for GI mucosal protection during NSAID therapy'

        WHEN t.name ILIKE '%ursodiol%' OR t.name ILIKE '%ursodeoxycholic%'
            THEN 'Bile acid for cholelithiasis and chronic hepatitis management'

        WHEN t.name ILIKE '%acetylcysteine%'
            THEN 'Mucolytic and antidote for paracetamol toxicosis in cats'

        ELSE 'Veterinary pharmaceutical for use in companion animals; see datasheet for full indication'
    END AS description,

    NULL AS shop_item_id   -- all imported medicines are not shop-linked

FROM temp_med1 t
-- skip any that are already in medicine by name (case-insensitive)
WHERE NOT EXISTS (
    SELECT 1 FROM medicine m
    WHERE lower(trim(m.name)) = lower(trim(t.name))
);


-- ============================================================
-- STEP 3b — Bulk prescription_medicine using generate_series
-- Goal: simulate historical records reaching millions of rows.
--
-- Strategy:
--   • Generate a large set of (prescription, medicine) pairs
--     using generate_series to multiply existing prescriptions.
--   • Each prescription gets up to 6 medicines.
--   • We use hashtext for deterministic medicine assignment
--     so re-runs are idempotent with ON CONFLICT DO NOTHING.
-- ============================================================

WITH
-- total medicines available
med_count AS (
    SELECT count(*) AS total FROM medicine
),

-- total prescriptions
presc_count AS (
    SELECT count(*) AS total FROM prescription
),

-- cross prescriptions with slots 1-6
slots AS (
    SELECT
        p.id        AS prescription_id,
        gs.slot
    FROM prescription p
    CROSS JOIN generate_series(1, 6) AS gs(slot)
),

-- assign a medicine to each slot deterministically
assigned AS (
    SELECT
        s.prescription_id,
        s.slot,
        (
            SELECT m.id
            FROM medicine m
            WHERE m.id = (
                (abs(hashtext(s.prescription_id::text || '-slot-' || s.slot::text))
                 % mc.total) + 1
            )
            LIMIT 1
        )                                                          AS medicine_id,
        -- dosage: 1, 2 or 3 times daily
        CASE (abs(hashtext(s.prescription_id::text || '-dos-' || s.slot::text)) % 3)
            WHEN 0 THEN 1
            WHEN 1 THEN 2
            ELSE        3
        END                                                        AS dosage,
        -- duration: 3 to 28 days
        3 + (abs(hashtext(s.prescription_id::text || '-day-' || s.slot::text)) % 26)
                                                                   AS num_days
    FROM slots s
    CROSS JOIN med_count mc
),

-- medicine IDs may not be perfectly contiguous; resolve via row_number
med_ranked AS (
    SELECT id, row_number() OVER (ORDER BY id) AS rn
    FROM medicine
),

assigned_resolved AS (
    SELECT
        a.prescription_id,
        a.slot,
        mr.id   AS medicine_id,
        a.dosage,
        a.num_days
    FROM assigned a
    CROSS JOIN med_count mc
    JOIN med_ranked mr
        ON mr.rn = (abs(hashtext(a.prescription_id::text || '-slot-' || a.slot::text)) % mc.total) + 1
),

-- keep only slots within the per-prescription medicine count (2-6)
wanted AS (
    SELECT
        ar.prescription_id,
        ar.slot,
        ar.medicine_id,
        ar.dosage,
        ar.num_days,
        -- how many medicines does this prescription want?
        2 + (abs(hashtext(ar.prescription_id::text || '-cnt')) % 5) AS want_count
    FROM assigned_resolved ar
),

filtered AS (
    SELECT prescription_id, medicine_id, dosage, num_days
    FROM wanted
    WHERE slot <= want_count
),

-- deduplicate: if the same medicine appears twice in one prescription keep first
deduped AS (
    SELECT DISTINCT ON (prescription_id, medicine_id)
        prescription_id,
        medicine_id,
        dosage,
        num_days
    FROM filtered
    ORDER BY prescription_id, medicine_id
)

INSERT INTO prescription_medicine (prescription_id, medicine_id, dosage, num_days)
SELECT prescription_id, medicine_id, dosage, num_days
FROM deduped
ON CONFLICT (prescription_id, medicine_id) DO NOTHING;


-- ============================================================
-- STEP 3c — Historical bulk expansion
-- Simulate several years of past prescriptions by generating
-- synthetic prescription_id × medicine_id pairs via
-- generate_series, without needing real prescription rows.
--
-- We create extra prescription rows tied to existing examinations,
-- then fill prescription_medicine for them.
-- This is the cleanest way to reach millions of rows while
-- keeping referential integrity.
-- ============================================================

-- First: generate a large batch of additional prescriptions
-- linked to completed examinations (one extra prescription
-- per examination per "historical year", 3 extra years back).

INSERT INTO prescription (examination_id, date_start, date_end, description)
SELECT
    sub.examination_id,
    sub.date_start,
    sub.date_end,
    sub.description
FROM (
    SELECT
        e.id                                                        AS examination_id,
        (e.date_examination - (yr.y * interval '1 year'))::date    AS date_start,
        (e.date_examination - (yr.y * interval '1 year')
            + (7 + (abs(hashtext(e.id::text || yr.y::text)) % 21)) * interval '1 day'
        )::date                                                     AS date_end,
        (ARRAY[
            'Historical prescription record. Long-term medication course.',
            'Repeat prescription issued for ongoing condition management.',
            'Annual medication renewal following routine examination.',
            'Prescription reissued; owner reported good compliance.',
            'Maintenance therapy continued from previous year.',
            'Chronic condition management; dosage reviewed and maintained.',
            'Preventive medication course prescribed at annual check.',
            'Medication course extended following positive clinical response.'
        ])[1 + (abs(hashtext(e.id::text || yr.y::text)) % 8)]      AS description
    FROM examination e
    CROSS JOIN (VALUES (1),(2),(3)) AS yr(y)
    WHERE e.status = 'completed'
) sub
-- prescription has a UNIQUE constraint on examination_id,
-- so we can only have one prescription per examination.
-- Instead we'll use a workaround: generate series on medicines directly.
WHERE false;  -- intentionally inserting 0 rows here; see note below

-- NOTE: Because prescription has UNIQUE(examination_id), we cannot
-- add multiple prescriptions per examination. The volume therefore
-- comes from increasing medicines per prescription (up to 6 above)
-- combined with the full prescription set.
--
-- To genuinely reach millions of rows we expand via generate_series
-- on the medicine dimension: assign up to 20 medicines per prescription
-- using a larger series, relying on ON CONFLICT DO NOTHING to skip
-- already-inserted pairs.

WITH
med_count AS (SELECT count(*) AS total FROM medicine),
med_ranked AS (
    SELECT id, row_number() OVER (ORDER BY id) AS rn FROM medicine
),
extra_slots AS (
    SELECT
        p.id   AS prescription_id,
        gs.slot
    FROM prescription p
    CROSS JOIN generate_series(7, 20) AS gs(slot)
),
extra_assigned AS (
    SELECT
        es.prescription_id,
        mr.id   AS medicine_id,
        1 + (abs(hashtext(es.prescription_id::text || '-edos-' || es.slot::text)) % 3) AS dosage,
        3 + (abs(hashtext(es.prescription_id::text || '-eday-' || es.slot::text)) % 26) AS num_days,
        -- only keep this slot if the prescription "wants" this many medicines
        -- (want_count now drawn from 7-20 range for the extra batch)
        7 + (abs(hashtext(es.prescription_id::text || '-ecnt')) % 14) AS want_count,
        es.slot
    FROM extra_slots es
    CROSS JOIN med_count mc
    JOIN med_ranked mr
        ON mr.rn = (abs(hashtext(es.prescription_id::text || '-eslot-' || es.slot::text)) % mc.total) + 1
),
extra_filtered AS (
    SELECT prescription_id, medicine_id, dosage, num_days
    FROM extra_assigned
    WHERE slot <= want_count
),
extra_deduped AS (
    SELECT DISTINCT ON (prescription_id, medicine_id)
        prescription_id, medicine_id, dosage, num_days
    FROM extra_filtered
    ORDER BY prescription_id, medicine_id
)
INSERT INTO prescription_medicine (prescription_id, medicine_id, dosage, num_days)
SELECT prescription_id, medicine_id, dosage, num_days
FROM extra_deduped
ON CONFLICT (prescription_id, medicine_id) DO NOTHING;


-- ============================================================
-- EXPAND MEDICINE TABLE
-- Combines drug bases x strengths x forms
-- Skips any name already in medicine (case-insensitive).
-- ============================================================

WITH drug_bases AS (
    SELECT unnest(ARRAY[
        -- Antibiotics
        'Amoxicillin', 'Amoxicillin-Clavulanate', 'Ampicillin',
        'Cephalexin', 'Cefpodoxime', 'Cefovecin', 'Cefazolin',
        'Enrofloxacin', 'Marbofloxacin', 'Pradofloxacin', 'Orbifloxacin',
        'Doxycycline', 'Tetracycline', 'Minocycline',
        'Metronidazole', 'Tinidazole', 'Ronidazole',
        'Clindamycin', 'Lincomycin',
        'Azithromycin', 'Tylosin', 'Erythromycin',
        'Trimethoprim-Sulfamethoxazole', 'Trimethoprim-Sulfadiazine',
        'Chloramphenicol', 'Rifampicin',
        'Amikacin', 'Gentamicin', 'Tobramycin',
        'Imipenem-Cilastatin', 'Meropenem',
        'Vancomycin', 'Linezolid',
        -- NSAIDs & Analgesics
        'Carprofen', 'Meloxicam', 'Robenacoxib', 'Mavacoxib',
        'Grapiprant', 'Ketoprofen', 'Tolfenamic Acid',
        'Tramadol', 'Buprenorphine', 'Methadone', 'Morphine',
        'Fentanyl', 'Butorphanol', 'Nalbuphine',
        'Gabapentin', 'Pregabalin', 'Amantadine',
        -- Corticosteroids
        'Prednisolone', 'Prednisone', 'Dexamethasone',
        'Methylprednisolone', 'Hydrocortisone', 'Betamethasone',
        'Triamcinolone', 'Budesonide', 'Fluticasone',
        -- Cardiac
        'Furosemide', 'Torsemide', 'Spironolactone',
        'Enalapril', 'Benazepril', 'Ramipril', 'Lisinopril',
        'Telmisartan', 'Amlodipine', 'Diltiazem',
        'Atenolol', 'Metoprolol', 'Propranolol', 'Sotalol',
        'Pimobendan', 'Digoxin', 'Sildenafil', 'Clopidogrel',
        'Heparin', 'Warfarin',
        -- GI
        'Omeprazole', 'Pantoprazole', 'Esomeprazole',
        'Famotidine', 'Ranitidine', 'Sucralfate',
        'Metoclopramide', 'Cisapride', 'Ondansetron',
        'Maropitant', 'Dolasetron', 'Prochlorperazine',
        'Lactulose', 'Loperamide', 'Bismuth Subsalicylate',
        'Misoprostol', 'Ursodiol',
        -- Anticonvulsants
        'Phenobarbital', 'Potassium Bromide', 'Levetiracetam',
        'Zonisamide', 'Gabapentin', 'Pregabalin', 'Imepitoin',
        -- Endocrine
        'Levothyroxine', 'Methimazole', 'Carbimazole',
        'Trilostane', 'Mitotane', 'Insulin Glargine',
        'Insulin NPH', 'Cabergoline', 'Deslorelin',
        -- Immunosuppressants / Dermatology
        'Cyclosporine', 'Tacrolimus', 'Azathioprine',
        'Mycophenolate Mofetil', 'Chlorambucil',
        'Oclacitinib', 'Lokivetmab',
        'Hydroxyzine', 'Diphenhydramine', 'Chlorphenamine',
        'Cetirizine', 'Loratadine',
        -- Antifungals
        'Ketoconazole', 'Fluconazole', 'Itraconazole',
        'Voriconazole', 'Terbinafine', 'Amphotericin B',
        'Griseofulvin', 'Clotrimazole', 'Miconazole',
        -- Antiparasitics
        'Fenbendazole', 'Mebendazole', 'Albendazole',
        'Praziquantel', 'Epsiprantel',
        'Ivermectin', 'Milbemycin Oxime', 'Moxidectin',
        'Selamectin', 'Pyrantel Pamoate', 'Nitenpyram',
        'Afoxolaner', 'Fluralaner', 'Sarolaner', 'Lotilaner',
        -- Anaesthetics / Sedatives
        'Propofol', 'Alfaxalone', 'Ketamine', 'Tiletamine',
        'Midazolam', 'Diazepam', 'Zolazepam',
        'Medetomidine', 'Dexmedetomidine', 'Romifidine',
        'Atropine', 'Glycopyrrolate',
        'Isoflurane', 'Sevoflurane',
        -- Fluids / Electrolytes
        'Sodium Chloride 0.9%', 'Lactated Ringers Solution',
        'Hartmann Solution', 'Dextrose 5%', 'Dextrose 50%',
        'Potassium Chloride', 'Sodium Bicarbonate',
        'Calcium Gluconate', 'Mannitol 20%',
        'Hydroxyethyl Starch 6%', 'Gelatin 4%',
        -- Supportive / Other
        'Acetylcysteine', 'S-Adenosylmethionine', 'Silymarin',
        'Vitamin B12', 'Iron Dextran', 'Folic Acid',
        'Vitamin K1', 'Doxapram', 'Atipamezole',
        'Naloxone', 'Flumazenil', 'Pralidoxime',
        'Dopamine', 'Dobutamine', 'Norepinephrine',
        'Epinephrine', 'Vasopressin',
        'Allopurinol', 'Colchicine', 'Pentoxifylline',
        'Pilocarpine', 'Dorzolamide', 'Latanoprost'
    ]) AS base
),

strengths AS (
    SELECT unnest(ARRAY[
        '2.5mg', '5mg', '10mg', '12.5mg', '20mg', '25mg',
        '30mg', '40mg', '50mg', '75mg', '100mg', '125mg',
        '150mg', '200mg', '250mg', '300mg', '400mg', '500mg',
        '600mg', '750mg', '1g',
        '0.5mg/ml', '1mg/ml', '2mg/ml', '2.5mg/ml', '4mg/ml',
        '5mg/ml', '10mg/ml', '20mg/ml', '50mg/ml', '100mg/ml',
        '1.5mg/ml', '0.1mg/ml', '0.3mg/ml', '0.5%', '1%', '2%',
        '2.27mg', '3.6mg', '16mg', '68mg', '136mg'
    ]) AS strength
),

forms AS (
    SELECT unnest(ARRAY[
        'Tablets', 'Chewable Tablets', 'Film-Coated Tablets',
        'Capsules', 'Oral Solution', 'Oral Suspension',
        'Injection', 'Lyophilisate for Injection',
        'Spot-On Solution', 'Transdermal Gel',
        'Ear Drops', 'Eye Drops', 'Ophthalmic Ointment',
        'Powder for Oral Solution', 'Granules',
        'Prolonged-Release Tablets', 'Soft Chews'
    ]) AS form
),

-- not all base+strength+form combos make sense;
-- filter to plausible combinations
plausible AS (
    SELECT
        b.base || ' ' || s.strength || ' ' || f.form AS med_name,
        -- assign manufacturer
        (ARRAY[
            'Zoetis Inc.', 'Boehringer Ingelheim', 'Elanco Animal Health',
            'Virbac Animal Health', 'Dechra Veterinary', 'Norbrook Laboratories',
            'Vetoquinol', 'Bayer Animal Health', 'MSD Animal Health',
            'Pfizer Animal Health', 'Jurox Animal Health',
            'Intervet-Schering Plough', 'Novartis Animal Health',
            'Merial', 'Orion Pharma'
        ])[1 + (abs(hashtext(b.base || s.strength || f.form)) % 15)] AS manufacturer
    FROM drug_bases b
    CROSS JOIN strengths s
    CROSS JOIN forms f
    WHERE
        -- injections go with mg/ml or % strengths
        (f.form IN ('Injection','Lyophilisate for Injection',
                    'Eye Drops','Ear Drops','Spot-On Solution',
                    'Transdermal Gel','Ophthalmic Ointment')
            AND (s.strength ~ 'mg/ml' OR s.strength ~ '%' OR s.strength ~ 'mg$'))
        OR
        -- oral solids go with mg or g strengths
        (f.form IN ('Tablets','Chewable Tablets','Film-Coated Tablets',
                    'Prolonged-Release Tablets','Capsules',
                    'Powder for Oral Solution','Granules',
                    'Soft Chews')
            AND s.strength ~ '^[0-9]' AND s.strength NOT LIKE '%ml%'
            AND s.strength NOT LIKE '%%')
        OR
        -- liquids go with mg/ml or %
        (f.form IN ('Oral Solution','Oral Suspension')
            AND (s.strength ~ 'mg/ml' OR s.strength ~ '%'
                 OR (s.strength ~ '^[0-9]' AND s.strength NOT LIKE '%ml%')))
    -- exclude nonsensical size+form pairs (very large mg in eye drops etc.)
    AND NOT (f.form IN ('Eye Drops','Ear Drops')
             AND s.strength IN ('500mg','750mg','1g','600mg','400mg',
                                '300mg','250mg','200mg','150mg'))
    AND NOT (f.form = 'Spot-On Solution'
             AND s.strength NOT IN ('2.27mg','3.6mg','16mg','68mg','136mg',
                                    '0.5%','1%','2%','10mg/ml','50mg/ml'))
),

-- cap at 5000 new medicines
ranked AS (
    SELECT
        med_name,
        manufacturer,
        row_number() OVER (ORDER BY md5(med_name)) AS rn
    FROM plausible
)

INSERT INTO medicine (name, manufacturer, description, shop_item_id)
SELECT
    r.med_name,
    r.manufacturer,
    'Veterinary pharmaceutical for use in companion animals; see datasheet for full indication.' AS description,
    NULL
FROM ranked r
WHERE r.rn <= 5000
  AND NOT EXISTS (
      SELECT 1 FROM medicine m
      WHERE lower(trim(m.name)) = lower(trim(r.med_name))
  );



-- ============================================================
-- More addresses — add 1600 more (for no duplicates)
-- ============================================================

TRUNCATE temp_addresses;

COPY temp_addresses (address, city, state, zip)
FROM 'C:/Program Files/PostgreSQL/18/data/temp_files/addresses.csv'
DELIMITER ',' CSV HEADER;

UPDATE temp_addresses SET address = TRIM(address);
UPDATE temp_addresses SET city = TRIM(city);
UPDATE temp_addresses SET state = TRIM(state);
UPDATE temp_addresses SET zip = TRIM(zip);

-- ============================================================
-- More owners — add 1600 more (total ~2000)
-- ============================================================

WITH male_names_sample AS (
    SELECT name FROM temp_male_names ORDER BY random() LIMIT 5000
),
female_names_sample AS (
    SELECT name FROM temp_female_names ORDER BY random() LIMIT 5000
),
male_pool AS (
    SELECT fn.name AS first_name, ln.surname AS last_name, 'M' AS gender
    FROM male_names_sample fn
    CROSS JOIN temp_surnames ln
    ORDER BY random()
    LIMIT 800
),
female_pool AS (
    SELECT fn.name AS first_name, ln.surname AS last_name, 'F' AS gender
    FROM female_names_sample fn
    CROSS JOIN temp_surnames ln
    ORDER BY random()
    LIMIT 800
),
all_names AS (
    SELECT first_name, last_name, gender FROM male_pool
    UNION ALL
    SELECT first_name, last_name, gender FROM female_pool
),
numbered AS (
    SELECT first_name, last_name, gender,
           row_number() OVER (ORDER BY random()) AS rn
    FROM all_names
),
addr_count AS (
    SELECT count(*) AS cnt FROM temp_addresses
),
addr_ranked AS (
    SELECT
        address || ', ' || city || ', ' || state || ' ' || zip AS full_address,
        row_number() OVER (ORDER BY id) AS rn
    FROM temp_addresses
),
shuffled_phones AS (
    SELECT
        ((row_number() OVER (ORDER BY random()) % 9) + 1)::text AS d1,
        lpad(((row_number() OVER (ORDER BY random()) * 73) % 900 + 100)::text, 3, '0') AS d2,
        lpad(((row_number() OVER (ORDER BY random()) * 97) % 900 + 100)::text, 3, '0') AS d3,
        row_number() OVER (ORDER BY random()) AS rn
    FROM generate_series(1, 1600)
)
INSERT INTO owner (first_name, last_name, phone, email, address, gender)
SELECT
    n.first_name,
    n.last_name,
    '+389 7' || p.d1 || ' ' || p.d2 || ' ' || p.d3       AS phone,
    lower(n.first_name) || '.' || lower(n.last_name)
        || n.rn::text || '@gmail.com'                      AS email,
    ar.full_address                                        AS address,
    n.gender
FROM numbered n
JOIN shuffled_phones p ON p.rn = n.rn
CROSS JOIN addr_count ac
JOIN addr_ranked ar
    ON ar.rn = ((n.rn - 1) % ac.cnt) + 1
LIMIT 1600;

-- ============================================================
-- MORE PETS — ~3 pets per new owner on average (~5000 more)
-- ============================================================

WITH new_owners AS (
    -- owners that have no pets yet
    SELECT o.id
    FROM owner o
    LEFT JOIN pet p ON p.owner_id = o.id
    WHERE p.id IS NULL
),
breed_counts AS (
    SELECT
        (SELECT count(*) FROM temp_breeds_mammal)    AS mammal_cnt,
        (SELECT count(*) FROM temp_breeds_bird)      AS bird_cnt,
        (SELECT count(*) FROM temp_breeds_fish)      AS fish_cnt,
        (SELECT count(*) FROM temp_breeds_reptile)   AS reptile_cnt,
        (SELECT count(*) FROM temp_breeds_amphibian) AS amphibian_cnt,
        (SELECT count(*) FROM temp_pet_names)        AS names_cnt,
        (SELECT count(*) FROM temp_medical_history)  AS history_cnt
),
-- expand: each owner gets 2-4 pets
owner_slots AS (
    SELECT
        o.id AS owner_id,
        gs.slot
    FROM new_owners o
    CROSS JOIN generate_series(1, 4) AS gs(slot)
    -- keep slot if <= random per-owner pet count (2-4)
    WHERE gs.slot <= 2 + (abs(hashtext(o.id::text || 'petcnt')) % 3)
),
typed AS (
    SELECT
        os.owner_id,
        os.slot,
        CASE (abs(hashtext(os.owner_id::text || os.slot::text || 'type')) % 10)
            WHEN 0 THEN 'mammal'
            WHEN 1 THEN 'mammal'
            WHEN 2 THEN 'mammal'
            WHEN 3 THEN 'mammal'
            WHEN 4 THEN 'mammal'
            WHEN 5 THEN 'mammal'
            WHEN 6 THEN 'bird'
            WHEN 7 THEN 'reptile'
            WHEN 8 THEN 'fish'
            ELSE        'amphibian'
        END AS type
    FROM owner_slots os
),
names_ranked AS (
    SELECT name, row_number() OVER (ORDER BY id) AS rn FROM temp_pet_names
),
history_ranked AS (
    SELECT history, row_number() OVER (ORDER BY id) AS rn FROM temp_medical_history
),
mammal_ranked  AS (SELECT breed_name, row_number() OVER (ORDER BY id) AS rn FROM temp_breeds_mammal),
bird_ranked    AS (SELECT breed_name, row_number() OVER (ORDER BY id) AS rn FROM temp_breeds_bird),
fish_ranked    AS (SELECT breed_name, row_number() OVER (ORDER BY id) AS rn FROM temp_breeds_fish),
reptile_ranked AS (SELECT breed_name, row_number() OVER (ORDER BY id) AS rn FROM temp_breeds_reptile),
amphibian_ranked AS (SELECT breed_name, row_number() OVER (ORDER BY id) AS rn FROM temp_breeds_amphibian)

INSERT INTO pet (name, is_active, type, breed, age, medical_history, owner_id)
SELECT
    nr.name,
    (abs(hashtext(t.owner_id::text || t.slot::text || 'active')) % 10 < 9) AS is_active,
    t.type,
    CASE t.type
        WHEN 'mammal'    THEN mbr.breed_name
        WHEN 'bird'      THEN bbr.breed_name
        WHEN 'fish'      THEN fbr.breed_name
        WHEN 'reptile'   THEN rbr.breed_name
        WHEN 'amphibian' THEN abr.breed_name
    END AS breed,
    CASE t.type
        WHEN 'mammal'    THEN (abs(hashtext(t.owner_id::text || t.slot::text || 'age')) % 240)
        WHEN 'bird'      THEN (abs(hashtext(t.owner_id::text || t.slot::text || 'age')) % 720)
        WHEN 'reptile'   THEN (abs(hashtext(t.owner_id::text || t.slot::text || 'age')) % 600)
        WHEN 'fish'      THEN (abs(hashtext(t.owner_id::text || t.slot::text || 'age')) % 240)
        WHEN 'amphibian' THEN (abs(hashtext(t.owner_id::text || t.slot::text || 'age')) % 360)
    END AS age,
    hr.history,
    t.owner_id
FROM typed t
CROSS JOIN breed_counts bc
JOIN names_ranked nr
    ON nr.rn = (abs(hashtext(t.owner_id::text || t.slot::text || 'nm')) % bc.names_cnt) + 1
JOIN history_ranked hr
    ON hr.rn = (abs(hashtext(t.owner_id::text || t.slot::text || 'hi')) % bc.history_cnt) + 1
LEFT JOIN mammal_ranked mbr
    ON t.type = 'mammal'
    AND mbr.rn = (abs(hashtext(t.owner_id::text || t.slot::text || 'mb')) % bc.mammal_cnt) + 1
LEFT JOIN bird_ranked bbr
    ON t.type = 'bird'
    AND bbr.rn = (abs(hashtext(t.owner_id::text || t.slot::text || 'bb')) % bc.bird_cnt) + 1
LEFT JOIN fish_ranked fbr
    ON t.type = 'fish'
    AND fbr.rn = (abs(hashtext(t.owner_id::text || t.slot::text || 'fb')) % bc.fish_cnt) + 1
LEFT JOIN reptile_ranked rbr
    ON t.type = 'reptile'
    AND rbr.rn = (abs(hashtext(t.owner_id::text || t.slot::text || 'rb')) % bc.reptile_cnt) + 1
LEFT JOIN amphibian_ranked abr
    ON t.type = 'amphibian'
    AND abr.rn = (abs(hashtext(t.owner_id::text || t.slot::text || 'ab')) % bc.amphibian_cnt) + 1;


-- ============================================================
-- Large tables
-- TRUNCATE dependent tables (reverse FK order)
-- ============================================================

TRUNCATE prescription_medicine CASCADE;
TRUNCATE prescription CASCADE;
TRUNCATE treatment_attribute_value CASCADE;
TRUNCATE treatment CASCADE;
TRUNCATE examination CASCADE;
TRUNCATE appointment CASCADE;


-- ============================================================
-- APPOINTMENT — 850,000 rows
-- ============================================================

INSERT INTO appointment (date_appointment, reason, phone, owner_id, pet_id)
WITH owner_pet_pairs AS (
    SELECT
        o.id    AS owner_id,
        o.phone AS phone,
        p.id    AS pet_id,
        row_number() OVER (ORDER BY o.id, p.id) AS rn
    FROM owner o
    JOIN pet p ON p.owner_id = o.id
),
pair_count AS (
    SELECT count(*) AS cnt FROM owner_pet_pairs
)
SELECT
    (CURRENT_DATE - (abs(hashtext(gs.n::text || 'apdate')) % 1825))::date AS date_appointment,

    (ARRAY[
        'Annual wellness check',
        'Vaccination booster',
        'Limping / lameness',
        'Vomiting and lethargy',
        'Skin rash and itching',
        'Ear infection suspected',
        'Eye discharge and redness',
        'Dental check-up',
        'Weight loss and poor appetite',
        'Diarrhoea for more than 2 days',
        'Post-operative follow-up',
        'Suspected urinary tract infection',
        'Respiratory difficulty',
        'Wound assessment',
        'Parasite prevention consultation',
        'Behavioural changes',
        'Mass / lump noticed',
        'Allergic reaction',
        'Pre-surgical blood work',
        'General health concern',
        'Annual vaccination',
        'Rabies vaccine booster',
        'Core vaccine schedule - puppy/kitten',
        'Bordetella vaccination',
        'Leptospirosis booster',
        'Feline herpesvirus / calicivirus / panleukopenia combo',
        'Canine distemper / parvovirus booster',
        'Vaccine certificate needed for travel',
        'First vaccination - new pet',
        'Overdue vaccination catch-up'
    ])[1 + (abs(hashtext(gs.n::text || 'rsn')) % 30)]  AS reason,

    op.phone,
    op.owner_id,
    op.pet_id

FROM generate_series(1, 850000) AS gs(n)
CROSS JOIN pair_count pc
JOIN owner_pet_pairs op
    ON op.rn = ((gs.n - 1) % pc.cnt) + 1;


-- ============================================================
-- EXAMINATION — 720,000 rows
-- Takes first 720k appointments ordered by id
-- ~80% completed, ~13% cancelled, ~7% scheduled
-- ============================================================

INSERT INTO examination (date_examination, status, description, appointment_id, employee_id, examination_room_id)
WITH apt_sample AS (
    SELECT
        a.id               AS appointment_id,
        a.date_appointment
    FROM appointment a
    ORDER BY a.id
    LIMIT 720000
),
emp_ranked AS (
    SELECT id, row_number() OVER (ORDER BY id) AS rn
    FROM employee WHERE role_id = 2
),
emp_count AS (
    SELECT count(*) AS cnt FROM employee WHERE role_id = 2
),
room_ranked AS (
    SELECT id, row_number() OVER (ORDER BY id) AS rn
    FROM examination_room WHERE type = 'examination'
),
room_count AS (
    SELECT count(*) AS cnt FROM examination_room WHERE type = 'examination'
)
SELECT
    (a.date_appointment + (abs(hashtext(a.appointment_id::text || 'edate')) % 8))::date AS date_examination,

    CASE (abs(hashtext(a.appointment_id::text || 'stat')) % 100)
        WHEN 0  THEN 'cancelled'
        WHEN 1  THEN 'cancelled'
        WHEN 2  THEN 'cancelled'
        WHEN 3  THEN 'cancelled'
        WHEN 4  THEN 'cancelled'
        WHEN 5  THEN 'cancelled'
        WHEN 6  THEN 'cancelled'
        WHEN 7  THEN 'cancelled'
        WHEN 8  THEN 'cancelled'
        WHEN 9  THEN 'cancelled'
        WHEN 10 THEN 'cancelled'
        WHEN 11 THEN 'cancelled'
        WHEN 12 THEN 'cancelled'
        WHEN 13 THEN 'scheduled'
        WHEN 14 THEN 'scheduled'
        WHEN 15 THEN 'scheduled'
        WHEN 16 THEN 'scheduled'
        WHEN 17 THEN 'scheduled'
        WHEN 18 THEN 'scheduled'
        WHEN 19 THEN 'scheduled'
        ELSE         'completed'
    END AS status,

    (ARRAY[
        'Patient presented for routine examination. Vitals within normal limits.',
        'Initial assessment completed. Further diagnostics recommended.',
        'Physical examination performed. Owner advised on treatment plan.',
        'Patient examined; mild clinical signs noted. Medication prescribed.',
        'Thorough examination carried out. No acute concerns identified.',
        'Follow-up examination. Condition improving since last visit.',
        'Examination completed. Lab samples collected for analysis.',
        'Clinical signs assessed. Dietary modification recommended.',
        'Patient stable. Monitoring plan established with owner.',
        'Examination revealed localised inflammation. Treatment initiated.',
        'Pre-vaccination health check completed. Patient fit for immunisation.',
        'Animal examined prior to vaccination. No contraindications found.',
        'Vaccination visit. General condition assessed; vitals normal.',
        'Patient presented for scheduled immunisation. Brief physical performed.',
        'Health status confirmed satisfactory before vaccine administration.'
    ])[1 + (abs(hashtext(a.appointment_id::text || 'dsc')) % 15)] AS description,

    a.appointment_id,

    (SELECT id FROM emp_ranked
     WHERE rn = (abs(hashtext(a.appointment_id::text || 'emp')) % (SELECT cnt FROM emp_count)) + 1
     LIMIT 1) AS employee_id,

    (SELECT id FROM room_ranked
     WHERE rn = (abs(hashtext(a.appointment_id::text || 'rom')) % (SELECT cnt FROM room_count)) + 1
     LIMIT 1) AS examination_room_id

FROM apt_sample a;


-- ============================================================
-- TREATMENT — one per completed examination
-- 75% prescription / 15% vaccination / 7% consultation / 3% operation
-- ~576,000 completed exams - ~432,000 prescriptions
-- ============================================================

INSERT INTO treatment (date_treatment, notes, treatment_type_id, examination_id)
WITH type_ids AS (
    SELECT
        (SELECT id FROM treatment_type WHERE name = 'prescription') AS presc_id,
        (SELECT id FROM treatment_type WHERE name = 'vaccination')  AS vacc_id,
        (SELECT id FROM treatment_type WHERE name = 'consultation') AS cons_id,
        (SELECT id FROM treatment_type WHERE name = 'operation')    AS oper_id
)
SELECT
    (e.date_examination + (abs(hashtext(e.id::text || 'tdate')) % 4))::date AS date_treatment,

    CASE (abs(hashtext(e.id::text || 'ttype')) % 100)
        -- 75% prescription (0-74)
        WHEN  0 THEN 'Prescription issued following clinical assessment.'
        WHEN  1 THEN 'Medication course prescribed; owner counselled on administration.'
        WHEN  2 THEN 'Short course of antibiotics prescribed pending culture results.'
        WHEN  3 THEN 'Anti-inflammatory therapy initiated; re-check in 10 days.'
        WHEN  4 THEN 'Antiparasitic treatment prescribed; environmental treatment advised.'
        WHEN  5 THEN 'Analgesic course prescribed for post-operative pain management.'
        WHEN  6 THEN 'Antifungal therapy prescribed; reassess in 3 weeks.'
        WHEN  7 THEN 'Prescription provided; monitor for adverse reactions.'
        WHEN  8 THEN 'Combination therapy prescribed; owner given written instructions.'
        WHEN  9 THEN 'Medication adjusted based on current clinical findings.'
        -- 15% vaccination (75-89)
        WHEN 75 THEN 'Core vaccine administered. No immediate adverse reaction observed.'
        WHEN 76 THEN 'Booster vaccination given. Owner advised to monitor for 24 hours.'
        WHEN 77 THEN 'Rabies vaccine administered. Certificate issued.'
        WHEN 78 THEN 'Annual booster completed. Patient tolerated injection well.'
        WHEN 79 THEN 'Catch-up vaccination completed. Full schedule now up to date.'
        -- 7% consultation (90-96)
        WHEN 90 THEN 'Owner counselled on diet and weight management.'
        WHEN 91 THEN 'Behavioural concerns discussed; referral considered.'
        WHEN 92 THEN 'Discussed long-term management of chronic condition.'
        -- 3% operation (97-99)
        WHEN 97 THEN 'Surgery performed without complications.'
        WHEN 98 THEN 'Procedure completed; patient recovering well.'
        WHEN 99 THEN 'Operation successful; post-op care instructions given.'
        -- fill remaining slots to cover all 100 values
        ELSE
            CASE (abs(hashtext(e.id::text || 'ttype')) % 100)
                WHEN (abs(hashtext(e.id::text || 'ttype')) % 100) THEN
                    CASE
                        WHEN (abs(hashtext(e.id::text || 'ttype')) % 100) < 75
                            THEN 'Prescription reissued; owner reported good compliance.'
                        WHEN (abs(hashtext(e.id::text || 'ttype')) % 100) < 90
                            THEN 'Intranasal Bordetella vaccine administered without complication.'
                        WHEN (abs(hashtext(e.id::text || 'ttype')) % 100) < 97
                            THEN 'Follow-up plan agreed; owner given written summary.'
                        ELSE 'Patient stable post-operatively; monitoring ongoing.'
                    END
            END
    END AS notes,

    CASE
        WHEN (abs(hashtext(e.id::text || 'ttype')) % 100) < 75
            THEN (SELECT presc_id FROM type_ids)
        WHEN (abs(hashtext(e.id::text || 'ttype')) % 100) < 90
            THEN (SELECT vacc_id  FROM type_ids)
        WHEN (abs(hashtext(e.id::text || 'ttype')) % 100) < 97
            THEN (SELECT cons_id  FROM type_ids)
        ELSE    (SELECT oper_id  FROM type_ids)
    END AS treatment_type_id,

    e.id AS examination_id

FROM examination e
WHERE e.status = 'completed';


-- ============================================================
-- TREATMENT_ATTRIBUTE_VALUE
-- ~576k treatments × avg 8 attrs = ~4.6M rows
-- ============================================================

-- PRESCRIPTION
INSERT INTO treatment_attribute_value (value, notes, treatment_attribute_id, treatment_id)
SELECT final_val.val, NULL, a.id, t.id
FROM treatment t
JOIN treatment_type tt ON tt.id = t.treatment_type_id AND tt.name = 'prescription'
JOIN treatment_attribute a ON a.treatment_type_id = tt.id
CROSS JOIN LATERAL (
    SELECT
        (ARRAY['Antibiotic','NSAID','Corticosteroid','Antiparasitic',
               'Antifungal','Analgesic','Anticonvulsant','Cardiac',
               'Gastrointestinal','Immunosuppressant'])
            [1 + (abs(hashtext(t.id::text || 'cls')) % 10)]   AS medication_class,
        (ARRAY['Oral','Subcutaneous injection','Intramuscular injection',
               'Topical','Intravenous','Ophthalmic'])
            [1 + (abs(hashtext(t.id::text || 'rte')) % 6)]    AS route,
        (ARRAY['Once daily','Twice daily','Three times daily',
               'Every 48 hours','Every 72 hours','With food'])
            [1 + (abs(hashtext(t.id::text || 'frq')) % 6)]    AS frequency,
        (7 + (abs(hashtext(t.id::text || 'dur')) % 18))::text  AS duration_days,
        (abs(hashtext(t.id::text || 'ref')) % 3)::text         AS refills_allowed,
        CASE (abs(hashtext(t.id::text || 'wth')) % 3)
            WHEN 0 THEN 'None'
            WHEN 1 THEN '24 hours'
            ELSE        '48 hours'
        END                                                    AS withdrawal_period
) v
CROSS JOIN LATERAL (
    SELECT CASE a.name
        WHEN 'medication_class'  THEN v.medication_class
        WHEN 'route'             THEN v.route
        WHEN 'frequency'         THEN v.frequency
        WHEN 'duration_days'     THEN v.duration_days
        WHEN 'refills_allowed'   THEN v.refills_allowed
        WHEN 'withdrawal_period' THEN v.withdrawal_period
    END AS val
) final_val
WHERE final_val.val IS NOT NULL;

-- VACCINATION
INSERT INTO treatment_attribute_value (value, notes, treatment_attribute_id, treatment_id)
SELECT final_val.val, NULL, a.id, t.id
FROM treatment t
JOIN treatment_type tt ON tt.id = t.treatment_type_id AND tt.name = 'vaccination'
JOIN treatment_attribute a ON a.treatment_type_id = tt.id
CROSS JOIN LATERAL (
    SELECT
        (ARRAY['Nobivac DHPPi','Nobivac Rabies','Feligen CRP',
               'Purevax RCPCh','Versican Plus DHPPi/L4','Canigen L4',
               'Nobivac Lepto 4','Eurican Herpes 205',
               'Felocell CVR','Quantum Cat 7'])
            [1 + (abs(hashtext(t.id::text || 'vac')) % 10)]   AS vaccine_name,
        (ARRAY['Zoetis','MSD Animal Health','Boehringer Ingelheim','Virbac','Elanco'])
            [1 + (abs(hashtext(t.id::text || 'mfr')) % 5)]    AS manufacturer,
        'BN-' || lpad((abs(hashtext(t.id::text || 'bn')) % 900000 + 100000)::text, 6, '0')
                                                               AS batch_number,
        CASE (abs(hashtext(t.id::text || 'nd')) % 3)
            WHEN 0 THEN '1' WHEN 1 THEN '2' ELSE '3'
        END                                                    AS num_doses,
        CASE (abs(hashtext(t.id::text || 'dn')) % 2)
            WHEN 0 THEN '1' ELSE '2'
        END                                                    AS dose_number,
        (ARRAY['Subcutaneous','Intramuscular','Intranasal'])
            [1 + (abs(hashtext(t.id::text || 'rt2')) % 3)]    AS route,
        (ARRAY['Right scruff','Left scruff','Right hindlimb','Left hindlimb'])
            [1 + (abs(hashtext(t.id::text || 'ste')) % 4)]    AS site,
        (t.date_treatment + interval '1 year')::date::text     AS date_next,
        CASE WHEN (abs(hashtext(t.id::text || 'adv')) % 100) < 3
            THEN 'true' ELSE 'false'
        END                                                    AS adverse_reaction
) v
CROSS JOIN LATERAL (
    SELECT CASE a.name
        WHEN 'vaccine_name'     THEN v.vaccine_name
        WHEN 'manufacturer'     THEN v.manufacturer
        WHEN 'batch_number'     THEN v.batch_number
        WHEN 'num_doses'        THEN v.num_doses
        WHEN 'dose_number'      THEN v.dose_number
        WHEN 'route'            THEN v.route
        WHEN 'site'             THEN v.site
        WHEN 'date_next'        THEN v.date_next
        WHEN 'adverse_reaction' THEN v.adverse_reaction
    END AS val
) final_val
WHERE final_val.val IS NOT NULL;

-- CONSULTATION
INSERT INTO treatment_attribute_value (value, notes, treatment_attribute_id, treatment_id)
SELECT final_val.val, NULL, a.id, t.id
FROM treatment t
JOIN treatment_type tt ON tt.id = t.treatment_type_id AND tt.name = 'consultation'
JOIN treatment_attribute a ON a.treatment_type_id = tt.id
CROSS JOIN LATERAL (
    SELECT
        (ARRAY[
            'Nutrition and weight management','Behavioural assessment',
            'Chronic disease management','Pre-surgical counselling',
            'Post-operative care planning','Dental hygiene advice',
            'Parasite prevention review','Vaccination schedule planning',
            'End-of-life care discussion','Second opinion review'
        ])[1 + (abs(hashtext(t.id::text || 'top')) % 10)]     AS topic,
        (ARRAY[
            'Owner presented concerns regarding the pet''s appetite and energy levels. A full dietary review was completed and a lower-calorie prescription diet was recommended. Owner was shown how to measure portions correctly and advised to recheck in 4 weeks.',
            'Behavioural history taken in detail. Pet displays anxiety-related signs including excessive grooming and vocalization. Environmental enrichment strategies discussed and a referral to a veterinary behaviourist was considered.',
            'Long-term management plan for the pet''s diagnosed chronic kidney disease was reviewed. Blood results interpreted and ACE inhibitor dosage adjusted. Owner counselled on signs of deterioration.',
            'Pre-surgical consultation completed for elective spay procedure. Risks and benefits of anaesthesia explained. Pre-operative blood panel ordered. Owner provided written consent and fasting instructions.',
            'Post-operative wound checked and healing progress assessed. Owner demonstrated correct application of topical antiseptic. Suture removal booked for 10 days post-op.',
            'Dental examination findings discussed with owner. Stage 2 periodontal disease identified. Professional scale and polish recommended. Home brushing technique demonstrated.',
            'Current parasite prevention programme assessed. Updated protocol prescribed combining monthly spot-on and quarterly wormer. Environmental hygiene advice given.',
            'Full vaccination history reviewed. Pet was overdue for core and leptospirosis boosters. Schedule re-established and owner reminded of annual requirement.',
            'Compassionate discussion held with owner regarding quality of life for their senior pet with advanced neoplasia. Palliative care options outlined.',
            'Second opinion consultation for recurrent skin condition. Differential diagnoses reconsidered; skin biopsy recommended to rule out immune-mediated disease.'
        ])[1 + (abs(hashtext(t.id::text || 'dsc')) % 10)]     AS description,
        CASE WHEN (abs(hashtext(t.id::text || 'ref')) % 100) < 15
            THEN 'true' ELSE 'false'
        END                                                    AS referral,
        CASE WHEN (abs(hashtext(t.id::text || 'ref')) % 100) < 15
            THEN (ARRAY['Veterinary Dermatologist','Veterinary Cardiologist',
                        'Veterinary Behaviourist','Veterinary Oncologist',
                        'Veterinary Ophthalmologist','Veterinary Neurologist'])
                 [1 + (abs(hashtext(t.id::text || 'rto')) % 6)]
            ELSE NULL
        END                                                    AS referral_to,
        (ARRAY['7','14','21','30'])
            [1 + (abs(hashtext(t.id::text || 'fup')) % 4)]    AS follow_up_days,
        CASE WHEN (abs(hashtext(t.id::text || 'own')) % 10) < 9
            THEN 'true' ELSE 'false'
        END                                                    AS owner_present
) v
CROSS JOIN LATERAL (
    SELECT CASE a.name
        WHEN 'topic'          THEN v.topic
        WHEN 'description'    THEN v.description
        WHEN 'referral'       THEN v.referral
        WHEN 'referral_to'    THEN v.referral_to
        WHEN 'follow_up_days' THEN v.follow_up_days
        WHEN 'owner_present'  THEN v.owner_present
    END AS val
) final_val
WHERE final_val.val IS NOT NULL;

-- OPERATION
INSERT INTO treatment_attribute_value (value, notes, treatment_attribute_id, treatment_id)
SELECT final_val.val, NULL, a.id, t.id
FROM treatment t
JOIN treatment_type tt ON tt.id = t.treatment_type_id AND tt.name = 'operation'
JOIN treatment_attribute a ON a.treatment_type_id = tt.id
CROSS JOIN LATERAL (
    SELECT
        (ARRAY[
            'Ovariohysterectomy (spay)','Orchiectomy (neuter)',
            'Mass / tumour excision','Fracture repair (ORIF)',
            'Intestinal foreign body removal',
            'Cystotomy (bladder stone removal)','Gastropexy',
            'Enucleation','Amputation','Caesarean section',
            'Exploratory laparotomy','Cruciate ligament repair (TPLO)',
            'Dental extraction','Wound debridement and closure',
            'Thoracostomy tube placement'
        ])[1 + (abs(hashtext(t.id::text || 'opt')) % 15)]     AS operation_type,
        (ARRAY['Successful','Successful','Successful','Successful',
               'Complicated','Unsuccessful'])
            [1 + (abs(hashtext(t.id::text || 'sts')) % 6)]    AS status,
        (ARRAY[
            'Propofol induction / Isoflurane maintenance',
            'Alfaxalone induction / Isoflurane maintenance',
            'Ketamine-Midazolam / Isoflurane maintenance',
            'Propofol TIVA',
            'Medetomidine-Butorphanol sedation (minor procedure)'
        ])[1 + (abs(hashtext(t.id::text || 'ans')) % 5)]      AS anesthesia,
        (15 + (abs(hashtext(t.id::text || 'dur')) % 166))::text AS duration_minutes,
        (t.date_treatment + (7 + abs(hashtext(t.id::text || 'chk')) % 8))::text
                                                               AS date_checkup,
        (SELECT e.first_name || ' ' || e.last_name
         FROM employee e
         WHERE e.role_id = 2
         ORDER BY abs(hashtext(t.id::text || 'srg' || e.id::text))
         LIMIT 1)                                              AS surgeon,
        CASE WHEN (abs(hashtext(t.id::text || 'cmp')) % 100) < 12
            THEN 'true' ELSE 'false'
        END                                                    AS complications
) v
CROSS JOIN LATERAL (
    SELECT CASE a.name
        WHEN 'operation_type'   THEN v.operation_type
        WHEN 'status'           THEN v.status
        WHEN 'anesthesia'       THEN v.anesthesia
        WHEN 'duration_minutes' THEN v.duration_minutes
        WHEN 'date_checkup'     THEN v.date_checkup
        WHEN 'surgeon'          THEN v.surgeon
        WHEN 'complications'    THEN v.complications
    END AS val
) final_val
WHERE final_val.val IS NOT NULL;


-- ============================================================
-- PRESCRIPTION — one per completed examination
-- with a prescription treatment (~432,000 rows)
-- ============================================================

INSERT INTO prescription (examination_id, date_start, date_end, description)
SELECT
    e.id AS examination_id,
    e.date_examination AS date_start,
    (e.date_examination + (7 + abs(hashtext(e.id::text || 'pend')) % 21))::date AS date_end,
    (ARRAY[
        'Administer as directed. Complete the full course.',
        'Give with food to reduce gastric upset.',
        'Monitor for adverse reactions; contact clinic if vomiting occurs.',
        'Store in a cool dry place. Keep out of reach of children.',
        'Re-examine if no improvement within 5 days.',
        'Do not crush tablets; administer whole.',
        'Shake oral suspension well before each use.',
        'Continue until finished even if pet appears better.',
        'Avoid direct sunlight on treated area.',
        'Return for recheck at end of course.'
    ])[1 + (abs(hashtext(e.id::text || 'pdsc')) % 10)] AS description
FROM examination e
JOIN treatment t       ON t.examination_id = e.id
JOIN treatment_type tt ON tt.id = t.treatment_type_id AND tt.name = 'prescription'
WHERE e.status = 'completed'
ON CONFLICT (examination_id) DO NOTHING;


-- ============================================================
-- PRESCRIPTION_MEDICINE — target ~10M rows
-- ~432,000 prescriptions × avg 25 medicines = ~10.8M
-- generate_series 1-30, want_count 20-30
-- ============================================================

WITH med_ranked AS (
    SELECT id, row_number() OVER (ORDER BY id) AS rn
    FROM medicine
),
med_count AS (
    SELECT count(*) AS total FROM medicine
),
slots AS (
    SELECT
        p.id   AS prescription_id,
        gs.slot
    FROM prescription p
    CROSS JOIN generate_series(1, 30) AS gs(slot)
),
assigned AS (
    SELECT
        s.prescription_id,
        s.slot,
        mr.id AS medicine_id,
        1 + (abs(hashtext(s.prescription_id::text || '-dos-' || s.slot::text)) % 3) AS dosage,
        3 + (abs(hashtext(s.prescription_id::text || '-day-' || s.slot::text)) % 26) AS num_days,
        -- each prescription wants between 20 and 30 medicines
        20 + (abs(hashtext(s.prescription_id::text || '-cnt')) % 11) AS want_count
    FROM slots s
    CROSS JOIN med_count mc
    JOIN med_ranked mr
        ON mr.rn = (abs(hashtext(s.prescription_id::text || '-med-' || s.slot::text)) % mc.total) + 1
),
filtered AS (
    SELECT prescription_id, medicine_id, dosage, num_days
    FROM assigned
    WHERE slot <= want_count
),
deduped AS (
    SELECT DISTINCT ON (prescription_id, medicine_id)
        prescription_id, medicine_id, dosage, num_days
    FROM filtered
    ORDER BY prescription_id, medicine_id
)
INSERT INTO prescription_medicine (prescription_id, medicine_id, dosage, num_days)
SELECT prescription_id, medicine_id, dosage, num_days
FROM deduped
ON CONFLICT (prescription_id, medicine_id) DO NOTHING;


-- SELECT count(*) FROM appointment;
-- SELECT * FROM appointment;
-- SELECT count(*) FROM examination;
-- SELECT * FROM examination;
-- SELECT count(*) FROM treatment;
-- SELECT * FROM treatment;
-- SELECT count(*) FROM treatment_attribute_value;
-- SELECT * FROM treatment_attribute_value;
-- SELECT count(*) FROM prescription;
-- SELECT * FROM prescription;
-- SELECT count(*) FROM prescription_medicine;
--
-- SELECT pg_size_pretty(pg_database_size(current_database()));


-- ============================================================
-- discount
-- ~25% of shop items currently carry an active promotional discount
-- ============================================================

INSERT INTO discount (type, value, description, date_from, date_to, shop_item_id)
SELECT
    CASE
        WHEN (abs(hashtext(si.id::text || 'dtype')) % 2) = 0
            THEN 'fixed'
        ELSE 'percentage'
        END AS type,

    CASE
        WHEN (abs(hashtext(si.id::text || 'dtype')) % 2) = 0
            THEN round(
                (1 + (abs(hashtext(si.id::text || 'dval')) % 1500) / 100.0)::numeric,
                2
                 )
        ELSE round(
                (5 + (abs(hashtext(si.id::text || 'dval')) % 3500) / 100.0)::numeric,
                2
             )
        END AS value,

    (ARRAY[
        'Seasonal promotion',
        'Clearance discount',
        'Loyalty customer discount',
        'Bulk purchase discount',
        'New product launch offer',
        'Holiday special',
        'Overstock clearance',
        'Limited time offer',
        'Member exclusive discount',
        'Weekend flash sale'
        ])[1 + (abs(hashtext(si.id::text || 'ddsc')) % 10)] AS description,

    CURRENT_DATE - (abs(hashtext(si.id::text || 'dfrom')) % 30) AS date_from,

    CURRENT_DATE + (abs(hashtext(si.id::text || 'dto')) % 60 + 1) AS date_to,

    si.id AS shop_item_id

FROM shop_item si
WHERE (abs(hashtext(si.id::text || 'dpick')) % 100) < 25;

ALTER TABLE invoice_item DISABLE TRIGGER trg_generate_num_item;

-- ============================================================
-- invoice + invoice_item — clinical visits
-- One invoice per completed examination that has at least one
-- treatment; each treatment becomes its own invoice_item line.
-- ~15% of invoices redeem an active coupon.
-- ============================================================

WITH billable_examinations AS (
    SELECT
        e.id AS examination_id,
        e.date_examination,
        a.owner_id
    FROM examination e
             JOIN appointment a ON a.id = e.appointment_id
    WHERE e.status = 'completed'
      AND EXISTS (SELECT 1 FROM treatment t WHERE t.examination_id = e.id)
),

     invoice_src AS (
         SELECT
             nextval(pg_get_serial_sequence('invoice', 'id'))               AS invoice_id,
             be.examination_id,
             be.date_examination                                            AS date_invoice,
             be.owner_id,
             CASE WHEN (abs(hashtext(be.examination_id::text || 'cpn')) % 100) < 15
                      THEN (
                     SELECT c.id FROM coupon c
                     WHERE c.is_active = true
                     ORDER BY abs(hashtext(be.examination_id::text || 'cpnpick' || c.id::text))
                     LIMIT 1
                 )
                  ELSE NULL
                 END                                                             AS coupon_id
         FROM billable_examinations be
     ),

     new_invoices AS (
         INSERT INTO invoice (id, date_invoice, total, coupon_id, owner_id)
             SELECT invoice_id, date_invoice, 0.00, coupon_id, owner_id
             FROM invoice_src
             RETURNING id
     ),

     invoice_lines AS (
         SELECT
             isrc.invoice_id,
             t.id AS treatment_id,
             CASE tt.name
                 WHEN 'prescription' THEN round((15 + random() * 60)::numeric, 2)
                 WHEN 'vaccination'  THEN round((20 + random() * 30)::numeric, 2)
                 WHEN 'consultation' THEN round((25 + random() * 45)::numeric, 2)
                 WHEN 'operation'    THEN round((150 + random() * 850)::numeric, 2)
                 END AS price
         FROM invoice_src isrc
                  JOIN treatment t       ON t.examination_id = isrc.examination_id
                  JOIN treatment_type tt ON tt.id = t.treatment_type_id
     )

INSERT INTO invoice_item (num_item, invoice_id, price, quantity, type, treatment_id)
SELECT
            row_number() OVER (PARTITION BY invoice_id ORDER BY treatment_id) AS num_item,
            invoice_id,
            price,
            1                AS quantity,
            'treatment'      AS type,
            treatment_id
FROM invoice_lines;

-- ============================================================
-- invoice + invoice_item — retail purchases (bulk)
-- 200,000 standalone shop invoices spread across all owners
-- over the last 3 years, each with 5-15 line items
-- ============================================================

WITH owner_count AS (
    SELECT count(*) AS cnt FROM owner
),

     owner_ranked AS (
         SELECT id, row_number() OVER (ORDER BY id) AS rn FROM owner
     ),

     invoice_src AS (
         SELECT
             nextval(pg_get_serial_sequence('invoice', 'id'))                          AS invoice_id,
             o.id                                                                      AS owner_id,
             CURRENT_DATE - (abs(hashtext(gs.n::text || 'shpdate')) % 1095)            AS date_invoice,
             5 + (abs(hashtext(gs.n::text || 'itemcnt')) % 11)                         AS num_items,  -- 5-15 items
             CASE WHEN (abs(hashtext(gs.n::text || 'shpcpn')) % 100) < 15
                      THEN (
                     SELECT c.id FROM coupon c
                     WHERE c.is_active = true
                     ORDER BY abs(hashtext(gs.n::text || 'shpcpnpick' || c.id::text))
                     LIMIT 1
                 )
                  ELSE NULL
                 END                                                                        AS coupon_id
         FROM generate_series(1, 200000) AS gs(n)
                  CROSS JOIN owner_count oc
                  JOIN owner_ranked o
                       ON o.rn = ((abs(hashtext(gs.n::text || 'ownerpick')) % oc.cnt) + 1)
     ),

     new_invoices AS (
         INSERT INTO invoice (id, date_invoice, total, coupon_id, owner_id)
             SELECT invoice_id, date_invoice, 0.00, coupon_id, owner_id
             FROM invoice_src
             RETURNING id
     ),

     item_slots AS (
         SELECT isrc.invoice_id, gs.slot
         FROM invoice_src isrc
                  CROSS JOIN generate_series(1, 15) AS gs(slot)
         WHERE gs.slot <= isrc.num_items
     ),

     item_count AS (
         SELECT count(*) AS total FROM shop_item
     ),

     item_ranked AS (
         SELECT id, price, row_number() OVER (ORDER BY id) AS rn FROM shop_item
     )

INSERT INTO invoice_item (num_item, invoice_id, price, quantity, type, shop_item_id)
SELECT
    isl.slot                                                                  AS num_item,
    isl.invoice_id,
    ir.price,
    1 + (abs(hashtext(isl.invoice_id::text || 'qty' || isl.slot::text)) % 3) AS quantity,
    'shop_item'                                                              AS type,
    ir.id                                                                     AS shop_item_id
FROM item_slots isl
         CROSS JOIN item_count ic
         JOIN item_ranked ir
              ON ir.rn = (abs(hashtext(isl.invoice_id::text || 'item' || isl.slot::text)) % ic.total) + 1;


ALTER TABLE invoice_item ENABLE TRIGGER trg_generate_num_item;

-- ============================================================
-- invoice.total — recompute from invoice_item lines and apply
-- the redeemed coupon's discount, then bump coupon usage_count
-- ============================================================

UPDATE coupon c
SET usage_count = LEAST(c.usage_count + sub.uses, c.usage_limit)
FROM (
         SELECT coupon_id, count(*) AS uses
         FROM invoice
         WHERE coupon_id IS NOT NULL
         GROUP BY coupon_id
     ) sub
WHERE c.id = sub.coupon_id;

WITH invoice_calc AS (
    SELECT
        i.id         AS invoice_id,
        i.coupon_id,
        SUM(ii.price * ii.quantity) AS subtotal
    FROM invoice i
             JOIN invoice_item ii ON ii.invoice_id = i.id
    GROUP BY i.id, i.coupon_id
)
UPDATE invoice i
SET total = GREATEST(
        ROUND(
                CASE
                    WHEN c.id IS NOT NULL AND ic.subtotal >= COALESCE(c.min_total, 0) THEN
                        CASE c.type
                            WHEN 'fixed'      THEN ic.subtotal - c.value
                            WHEN 'percentage' THEN ic.subtotal * (1 - c.value / 100)
                            END
                    ELSE ic.subtotal
                    END,
                2),
        0.00)
FROM invoice_calc ic
         LEFT JOIN coupon c ON c.id = ic.coupon_id
WHERE i.id = ic.invoice_id;

-- ============================================================
-- payment
-- ============================================================

INSERT INTO payment (date_payment, amount, method, invoice_id)
SELECT
    i.date_invoice + (abs(hashtext(i.id::text || 'paydate')) % 6) AS date_payment,
    i.total AS amount,
    CASE
        WHEN (abs(hashtext(i.id::text || 'paymethod')) % 100) < 30 THEN 'cash'
        WHEN (abs(hashtext(i.id::text || 'paymethod')) % 100) < 55 THEN 'debit card'
        WHEN (abs(hashtext(i.id::text || 'paymethod')) % 100) < 80 THEN 'credit card'
        WHEN (abs(hashtext(i.id::text || 'paymethod')) % 100) < 95 THEN 'digital wallet'
        ELSE 'other'
        END AS method,
    i.id AS invoice_id
FROM invoice i;

-- ========================
-- Drop temporary tables
-- ========================
DROP TABLE IF EXISTS temp_addresses;
DROP TABLE IF EXISTS temp_breeds_mammal;
DROP TABLE IF EXISTS temp_breeds_bird;
DROP TABLE IF EXISTS temp_breeds_fish;
DROP TABLE IF EXISTS temp_breeds_amphibian;
DROP TABLE IF EXISTS temp_breeds_reptile;
DROP TABLE IF EXISTS temp_pet_names;
DROP TABLE IF EXISTS temp_medical_history;
DROP TABLE IF EXISTS temp_certificate_names;
DROP TABLE IF EXISTS temp_spec_data;
DROP TABLE IF EXISTS temp_prescription_advice;
DROP TABLE IF EXISTS temp_surnames;
DROP TABLE IF EXISTS temp_male_names;
DROP TABLE IF EXISTS temp_female_names;
DROP TABLE IF EXISTS temp_med1;