-- Add migration script here

CREATE TABLE IF NOT EXISTS User (
      id INTEGER PRIMARY KEY NOT NULL,
      email TEXT NOT NULL,
      name TEXT NOT NULL,
      role INTEGER NOT NULL,
      tz_offset INTEGER NOT NULL,
      FOREIGN KEY(role) REFERENCES UserRole(id)
);

CREATE TABLE IF NOT EXISTS UserRole (
    id INTEGER PRIMARY KEY NOT NULL,
    name TEXT NOT NULL UNIQUE
);

INSERT INTO UserRole (id, name) VALUES
    (1,'admin'),
    (10,'edit'),
    (99,'view');

CREATE TABLE IF NOT EXISTS InventoryItem (
     id INTEGER PRIMARY KEY NOT NULL,
     name TEXT NOT NULL,
     display_unit INTEGER NOT NULL DEFAULT 1,
     reorder_point REAL NOT NULL,
     allow_fractional_units BOOLEAN NOT NULL,
     active BOOLEAN NOT NULL,
     pims_id TEXT,
     FOREIGN KEY(display_unit) REFERENCES DisplayUnit(id)
);

CREATE TABLE IF NOT EXISTS Adjustment (
    id INTEGER PRIMARY KEY NOT NULL,
    item INTEGER NOT NULL,
    user INTEGER NOT NULL,
    create_date INTEGER NOT NULL,
    target_date INTEGER NOT NULL,
    amount REAL NOT NULL,
    reason INTEGER NOT NULL,
    unit_price INTEGER,
    FOREIGN KEY(user) REFERENCES User(id),
    FOREIGN KEY(item) REFERENCES InventoryItem(id),
    FOREIGN KEY(reason) REFERENCES AdjustmentReason(id)
);

CREATE TABLE IF NOT EXISTS AdjustmentReason (
    id INTEGER PRIMARY KEY NOT NULL,
    name TEXT NOT NULL
);

INSERT INTO AdjustmentReason (id, name) VALUES
    (0,'unknown'),
    (10,'sale'),
    (20,'destruction'),
    (25,'expiration'),
    (30,'theft'),
    (50,'new-stock');

CREATE TABLE IF NOT EXISTS DisplayUnit (
    id INTEGER PRIMARY KEY NOT NULL,
    unit TEXT NOT NULL,
    abbreviation TEXT NOT NULL
);

INSERT INTO DisplayUnit (id, unit, abbreviation) VALUES
    (1,'count', 'ct'),
    (2,'milliliter', 'ml'),
    (3,'milligram', 'mg');

CREATE TABLE IF NOT EXISTS VetcoveItem
(
    vetcove_item_id INTEGER PRIMARY KEY NOT NULL,
    item_name TEXT NOT NULL,
    ignored INTEGER NOT NULL DEFAULT 0,
    manufacturer_name TEXT,
    manufacturer_number TEXT,
    category TEXT,
    units TEXT,
    covetrus_sku TEXT,
    mwi_sku TEXT,
    patterson_sku TEXT,
    midwest_sku TEXT,
    first_vet_sku TEXT,
    penn_vet_sku TEXT,
    amatheon_sku TEXT,
    victor_sku TEXT,
    vetcove_sku TEXT,
    miller_vet_sku TEXT,
    boehringer_ingelheim_sku TEXT,
    zoetis_sku TEXT,
    pharmsource_ah_sku TEXT,
    ne_animal_health_sku TEXT,
    dechra_sku TEXT,
    medline_sku TEXT,
    elanco_sku TEXT
);

CREATE TABLE IF NOT EXISTS VetcoveItemToInventoryItemMap
(
    vetcove_item_id INTEGER NOT NULL UNIQUE,
    inventory_item_id INTEGER NOT NULL,
    FOREIGN KEY(vetcove_item_id) REFERENCES VetcoveItem(vetcove_item_id)
    FOREIGN KEY(inventory_item_id) REFERENCES InventoryItem(id)
);


CREATE TABLE IF NOT EXISTS VetcoveItemHistory
(
    vetcove_history_entry_id INTEGER PRIMARY KEY NOT NULL,
    vetcove_item_id INTEGER NOT NULL,
    order_date TEXT NOT NULL,
    item_name TEXT NOT NULL,
    order_id TEXT NOT NULL,
    units REAL NOT NULL,
    quantity REAL NOT NULL,
    total_price TEXT NOT NULL,
    unit_price TEXT,
    list_price TEXT,
    cost_per_dose TEXT,
    hospital TEXT,
    po_number TEXT,
    supplier TEXT,
    manufacturer TEXT,
    manufacturer_number TEXT,
    primary_category TEXT,
    secondary_category TEXT,
    unit_measurement TEXT,
    item_status	TEXT,
    supplier_sku TEXT
);

CREATE TABLE IF NOT EXISTS VetcoveItemHistoryToAdjustmentMap
(
    vetcove_history_entry_id INTEGER NOT NULL UNIQUE,
    adjustment_id INTEGER NOT NULL UNIQUE,
    FOREIGN KEY(vetcove_history_entry_id) REFERENCES VetcoveItemHistory(vetcove_history_entry_id)
    FOREIGN KEY(adjustment_id) REFERENCES Adjustment(id)
);
