DROP TABLE IF EXISTS drug.FarmadatiActiveIngredient;

DROP TABLE IF EXISTS drug.FarmadatiAtc;

DROP TABLE IF EXISTS drug.FarmadatiDrug;

DROP TABLE IF EXISTS drug.FarmadatiDrugPackageSize;

DROP TABLE IF EXISTS drug.FarmadatiPharmaceutical;

DROP TABLE IF EXISTS drug.FarmadatiProductInfo;

DROP TABLE IF EXISTS drug.FarmadatiVendor;

CREATE TABLE drug.FarmadatiActiveIngredient
(
    activePrincipleCode        VARCHAR(255) NOT NULL,
    activePrincipleDescription VARCHAR(max),
    PRIMARY KEY (activePrincipleCode)
);

CREATE TABLE drug.FarmadatiAtc
(
    atcCode        VARCHAR(255) NOT NULL,
    atcDescription VARCHAR(255),
    PRIMARY KEY (atcCode)
);

CREATE TABLE drug.FarmadatiDrug
(
    productCode             VARCHAR(255) NOT NULL,
    activePrincipleCode     VARCHAR(255),
    atcCode                 VARCHAR(255),
    companyCode             VARCHAR(255),
    extendedDescription     VARCHAR(255),
    productDescription      VARCHAR(255),
    shapeCodePharmaceutical VARCHAR(255),
    PRIMARY KEY (productCode)
);

CREATE TABLE drug.FarmadatiDrugPackageSize
(
    productCode              VARCHAR(255) NOT NULL,
    umrQuantityCapacity      FLOAT,
    umrUnitOfMeasureCapacity VARCHAR(255),
    PRIMARY KEY (productCode)
);

CREATE TABLE drug.FarmadatiPharmaceutical
(
    pharmaceuticalShapeCode               VARCHAR(255) NOT NULL,
    pharmaceuticalExtendedFormDescription VARCHAR(max),
    PRIMARY KEY (pharmaceuticalShapeCode)
);

CREATE TABLE drug.FarmadatiProductInfo
(
    productCode            VARCHAR(255) NOT NULL,
    activePrinciples       VARCHAR(max),
    contraindications      VARCHAR(max),
    dosage                 VARCHAR(max),
    excipients             VARCHAR(max),
    interactions           VARCHAR(max),
    name                   VARCHAR(max),
    overdose               VARCHAR(max),
    pregnancyFeedingTime   VARCHAR(max),
    sideEffects            VARCHAR(max),
    storage                VARCHAR(max),
    therapeuticIndications VARCHAR(max),
    warnings               VARCHAR(max),
    PRIMARY KEY (productCode)
);

CREATE TABLE drug.FarmadatiVendor
(
    companyCode VARCHAR(255) NOT NULL,
    companyName VARCHAR(255),
    PRIMARY KEY (companyCode)
);