DROP TABLE IF EXISTS Person;
CREATE TABLE Person
(
    id           BIGINT AUTO_INCREMENT PRIMARY KEY,
    name         VARCHAR(128)                NOT NULL DEFAULT '',
    firstname    VARCHAR(128)                NOT NULL DEFAULT '',
    account      VARCHAR(128)                NOT NULL DEFAULT '',
    pathFileName VARCHAR(128)                NOT NULL DEFAULT '',
    gender       ENUM ('', 'male', 'female') NOT NULL DEFAULT 'male',
    groups       SET ('', 'a', 'b', 'c')     NOT NULL DEFAULT '',
    birthday     DATE                        NOT NULL DEFAULT '0000-00-00',
    noteId       BIGINT                      NOT NULL DEFAULT 0,
    modified     DATETIME                    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created      DATETIME                    NOT NULL DEFAULT '0000-00-00 00:00:00'
);

#
# Create Demo
INSERT INTO Person (id, name, firstname, gender, groups)
VALUES (NULL, 'Doe', 'John', 'male', 'c'),
       (NULL, 'Smith', 'Jane', 'female', 'a,c');


DROP TABLE IF EXISTS PersFunction;
CREATE TABLE PersFunction
(
    id       BIGINT AUTO_INCREMENT PRIMARY KEY,
    personId BIGINT            DEFAULT '0',
    type     ENUM ('Student', 'Assistant', 'Professor', 'Administration'),
    start    DATE     NOT NULL DEFAULT '0000-00-00',
    end      DATE     NOT NULL DEFAULT '0000-00-00',
    note     VARCHAR(255),
    modified DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created  DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00'
);

# ---------------------------
# Form: plain
REPLACE INTO Form (id, name, title, noteInternal, tableName, permitNew, permitEdit, render, multiSql, parameter)
VALUES (3, 'formplain', 'Form: Plain', '', 'Form', 'always', 'always', 'plain', '', '');

# FormEditor: FormElements
REPLACE INTO FormElement (id, formId, name, label, mode, type, checkType, class, ord, size, maxLength, note, clientJs,
                          value, sql1, sql2, parameter, feIdContainer, subrecordOption, modeSql)
VALUES (300, 3, 'id', 'id', 'readonly', 'text', 'all', 'native', 100, 0, 11, '', '', '', '', '', '', 0, '', ''),
       (310, 3, 'name', 'Name', 'show', 'text', 'all', 'native', 120, 0, 255, '', '', '', '', '', '', 0, '', '');

# Form: table
REPLACE INTO Form (id, name, title, noteInternal, tableName, permitNew, permitEdit, render, multiSql, parameter)
VALUES (4, 'formtable', 'Form: Table', '', 'Form', 'always', 'always', 'table', '', '');

# FormEditor: FormElements
REPLACE INTO FormElement (id, formId, name, label, mode, type, checkType, class, ord, size, maxLength, note, clientJs,
                          value, sql1, sql2, parameter, feIdContainer, subrecordOption, modeSql)
VALUES (400, 4, 'id', 'id', 'readonly', 'text', 'all', 'native', 100, 0, 11, '', '', '', '', '', '', 0, '', ''),
       (410, 4, 'name', 'Name', 'show', 'text', 'all', 'native', 120, 0, 255, '', '', '', '', '', '', 0, '', '');


# Form: Person
REPLACE INTO Form (id, name, title, noteInternal, tableName, permitNew, permitEdit, render, multiSql, parameter)
VALUES (5, 'person', 'Person {{SELECT ": ", firstName, " ", name, " (", id, ")" FROM Person WHERE id = {{r:S0}}}}',
        'Please secure the form',
        'Person', 'always', 'always', 'bootstrap', '', '');

# FormEditor: FormElements person
REPLACE INTO FormElement (id, formId, name, label, mode, type, checkType, class, ord, size, maxLength, note, clientJs,
                          value, sql1, sql2, parameter, feIdContainer, subrecordOption, modeSql)
VALUES (500, 5, 'name', 'Name', 'show', 'text', 'all', 'native', 10, 0, 255, '', '', '', '', '', '', 0, '', ''),
       (501, 5, 'firstname', 'Firstname', 'show', 'text', 'all', 'native', 20, 0, 255, '', '', '', '', '', '', 0, '',
        ''),
       (502, 5, 'birthday', 'Birthday', 'show', 'date', 'all', 'native', 30, 0, 255, '', '', '', '', '', '', 0, '', ''),
       (506, 5, 'gender', 'Sex', 'show', 'radio', 'alnumx', 'native', 40, 0, 0, '', '', '', '', '',
        'itemList=female,male',
        0, '', ''),
       (503, 5, 'datumZeit', 'Datum & Zeit', 'show', 'datetime', 'alnumx', 'native', 50, 0, 0, '', '', '', '', '', '',
        0,
        '', ''),
       (504, 5, 'zeit', 'Zeit', 'show', 'time', 'alnumx', 'native', 60, 0, 0, '', '', '', '', '', '', 0, '', ''),
       (505, 5, 'pathFileName', 'Picture', 'show', 'upload', 'allbut', 'native', 70, 0, 0, '', '', '', '', '',
        'fileDestination={{SELECT ''fileadmin/user/pictures/'', p.name, ''-{{_filename}}'' FROM Person AS p WHERE p.id={{r}} }}',
        0, '', ''),

       (506, 5, '', 'Address', 'show', 'subrecord', 'all', 'native', 100, 0, 0, '', '', '',
        '{{!SELECT a.id, a.street, a.city, a.country FROM Address AS a WHERE a.personId={{r:S0}} }}',
        '', 'form=address\ndetail=id:personId', 0, 'new,edit,delete', '');


# Form: Address
REPLACE INTO Form (id, name, title, noteInternal, tableName, permitNew, permitEdit, render, multiSql, parameter)
VALUES (6, 'address',
        'Person {{SELECT ": ", p.firstName, " ", p.name, " (", id, ")" FROM Person AS p WHERE p.id = {{personId:S0}}}}',
        '',
        'Address', 'always', 'always', 'bootstrap', '', '');

# FormEditor: FormElements address
REPLACE INTO FormElement (id, formId, name, label, mode, type, checkType, class, ord, size, maxLength, note, clientJs,
                          value, sql1, sql2, parameter, feIdContainer, subrecordOption, modeSql)
VALUES (600, 6, 'street', 'Street', 'show', 'text', 'all', 'native', 10, 0, 255, '', '', '', '', '', '', 0, '', ''),
       (601, 6, 'city', 'City', 'show', 'text', 'all', 'native', 20, 0, 255, '', '', '', '', '', '', 0, '', ''),
       (602, 6, 'country', 'Country', 'show', 'select', 'all', 'native', 30, 0, 255, '', '', '', '', '', '', 0, '', '');


# ----------------------------------------------------------------------
#
DROP TABLE IF EXISTS Address;
CREATE TABLE Address
(
    id       BIGINT AUTO_INCREMENT PRIMARY KEY,
    personId BIGINT                                               NOT NULL DEFAULT 0,
    street   VARCHAR(128)                                         NOT NULL DEFAULT '',
    city     VARCHAR(128)                                         NOT NULL DEFAULT '',
    country  ENUM ('Switzerland', 'Austria', 'France', 'Germany') NOT NULL,
    grIdTyp  BIGINT                                               NOT NULL DEFAULT 0,
    modified DATETIME                                             NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created  DATETIME                                             NOT NULL DEFAULT '0000-00-00 00:00:00'
);

INSERT INTO Address (personId, street, city, country)
VALUES (1, 'Side Street', 'Zurich', 'Switzerland'),
       (1, 'Park Street', 'Wien', 'Austria'),
       (1, 'Winter Street', 'Paris', 'France'),
       (2, 'Summer Street', 'Berlin', 'Germany');

#------------------------------------------------------------------------
#
DROP TABLE IF EXISTS Note;
CREATE TABLE Note
(
    id       BIGINT AUTO_INCREMENT PRIMARY KEY,
    note     VARCHAR(128) NOT NULL DEFAULT '',
    xId      BIGINT       NOT NULL DEFAULT 0,
    grIdTyp  BIGINT       NOT NULL DEFAULT 0,
    modified DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created  DATETIME     NOT NULL DEFAULT '0000-00-00 00:00:00'
);




