--######################### item table #####################################################
define table if not exists item schemafull;
define field if not exists code on table item type string;

--######################### container table ################################################
define table if not exists container schemafull;

define field code on table container type string;
define field has_x on table container type bool default false;
define field has_y on table container type bool default false;
define field has_z on table container type bool default false;

define field x_min on table container type option<int> default None;
define field x_max on table container type option<int> default None;

define field y_min on table container type option<int> default None;
define field y_max on table container type option<int> default None;

define field z_min on table container type option<int> default None;
define field z_max on table container type option<int> default None;

//additional fields here would make sense in a more complete implementation like 
//names for the dimensions like "shelve", "rack", "slot" etc.

--######################### contained_in relation ##########################################

define table contained_in type relation in container|item out container schemafull;

define field x_pos on table contained_in type option<int>;
define field y_pos on table contained_in type option<int>;
define field z_pos on table contained_in type option<int>;


//each item|container be only in one place at a time.
define index idx_one_contained_in_only on table contained_in columns in unique;


define event contained_in_created on table contained_in
    when $event == "CREATE"
    then {
        if $after.out.has_x and $after.x_pos == None {
            THROW "Can't create contained_in relation: x position is required";
        } else if $after.out.has_y and $after.y_pos == None {
            THROW "Can't create contained_in relation: y position is required";
        } else if $after.out.has_z and $after.z_pos == None {
            THROW "Can't create contained_in relation: z position is required";
        } else if $after.out.has_x and !($after.out.x_min <= $after.x_pos and $after.x_pos <= $after.out.x_max) {
            THROW "Can't create contained_in relation: x position is out of bounds";
        } else if $after.out.has_y and !($after.out.y_min <= $after.y_pos and $after.y_pos <= $after.out.y_max) {
            THROW "Can't create contained_in relation: y position is out of bounds";
        } else if $after.out.has_z and !($after.out.z_min <= $after.z_pos and $after.z_pos <= $after.out.z_max) {
            THROW "Can't create contained_in relation: z position is out of bounds";
        } else if array::len(select *
                from contained_in
                where out == $after.out
                    and x_pos==$after.x_pos
                    and y_pos==$after.y_pos
                    and z_pos==$after.z_pos
                ) > 1
        {
            THROW "Can't create contained_in relation: position in container is already occupied";
        }
    };

--######################### test containers and items ####################################
create container:box1 content {
    code: "box1"    
};

//valid additional box entry
create container:box2 content {
    code: "box2",
    has_x: true,    
    x_min: 0,
    x_max: 10
};

create container:slot1 content {
    code: "slot1",
    has_x: true,    
    x_min: 0,
    x_max: 10
};


create container:rack1 content {
    code: "rack1",
    has_x: true,
    has_y: true,    
    x_min: 0,
    x_max: 10,
    y_min: 0,
    y_max: 10
};

create item:item1 content {
    code: "item1"
};

create item:item2 content {
    code: "item2"
};

--######################## test relations ###############################################
//valid entry
relate container:box1->contained_in->container:slot1 content {
    x_pos: 1,
    y_pos: None,
    z_pos: None
};

//valid entry
relate container:slot1->contained_in->container:rack1 content {
    x_pos: 1,
    y_pos: 1,
    z_pos: None
};

//valid entry
relate item:item1->contained_in->container:box1;


//valid relation. x_pos=1 should be ignored because hax_x is false for box1.
relate item:item2->contained_in->container:box1 content {
    x_pos: 1,
    y_pos: None,
    z_pos: None
};

//invalid entry. item1 is already related to a container. Each item|container can only 
//be in a single place at a time.

relate item:item1->contained_in->container:box1;
//this throws
//'Database index `idx_one_contained_in_only` already contains item:item1, 
//with record `contained_in:pozanudnne041vcxt917`'


//invalid entry x_pos out of bounds.

relate container:slot2->contained_in->container:rack1 content {
    x_pos: -1,
    y_pos: 1,
    z_pos: None
};
//this throws
//"An error occurred: Can't create contained_in relation: x position is out of bounds"


//invalid entry y_pos out of bounds.

relate container:slot3->contained_in->container:rack1 content {
    x_pos: 1,
    y_pos: 100,
    z_pos: None
};
//this throws
//"An error occurred: Can't create contained_in relation: y position is out of bounds"


//invalid relation. x_pos=1 in slot1 is already taken.

relate container:box2->contained_in->container:slot1 content {
    x_pos: 1,
    y_pos: None,
    z_pos: None
};
//this throws
//"An error occurred: Can't create contained_in relation: position in container is already occupied"


//invalid relation. slot1 has_x therefore a x_pos needs to be assigned.

relate container:box2->contained_in->container:slot1 content {
    x_pos: None,
    y_pos: None,
    z_pos: None
};
//this throws
//"An error occurred: Can't create contained_in relation: x position is required"