-- тест auto_increment ключей
CREATE TABLE low (
    lowID       INT NOT NULL AUTO_INCREMENT,
    name        TEXT NOT NULL,
    midID       INT NOT NULL,

    PRIMARY KEY (lowID)
);

CREATE TABLE mid (
    midID       INT NOT NULL AUTO_INCREMENT,
    name        TEXT NOT NULL,
    highID      INT NOT NULL,

    PRIMARY KEY (midID)
);

CREATE TABLE high (
    highID       INT NOT NULL AUTO_INCREMENT,
    name         TEXT NOT NULL,
    age          INT NOT NULL,

    PRIMARY KEY (highID)
);

-- тест string ключей
CREATE TABLE down (
    downID         VARCHAR(255) NOT NULL,
    name           TEXT NOT NULL,
    middleID       VARCHAR(255) NOT NULL,

    PRIMARY KEY (downID)
);

CREATE TABLE middle (
    middleID       VARCHAR(255) NOT NULL,
    name           TEXT NOT NULL,
    upID           VARCHAR(255) NOT NULL,

    PRIMARY KEY (middleID)
);

CREATE TABLE up (
    upID         VARCHAR(255) NOT NULL,
    name         TEXT NOT NULL,
    age          INT NOT NULL,

    PRIMARY KEY (upID)
);

DELETE FROM low;
DELETE FROM mid;
DELETE FROM high;
DELETE FROM up;
DELETE FROM middle;
DELETE FROM down;
delete FROM modems;
delete FROM modemDevices;
DELETE FROM users;



CREATE TABLE users (
    username        VARCHAR(255) NOT NULL,
    name            VARCHAR(255) NOT NULL,
    status          INT          NOT NULL,
    email           VARCHAR(255) NOT NULL,

    PRIMARY KEY(username),
    UNIQUE(name),
    UNIQUE(email)
)
ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;


CREATE TABLE connectors (
    connectorID     INT          NOT NULL AUTO_INCREMENT,
    name            VARCHAR(255) NOT NULL,

    PRIMARY KEY(connectorID),
    UNIQUE(name)
)
ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;


CREATE TABLE devices (
    deviceID        INT          NOT NULL AUTO_INCREMENT,
    name            VARCHAR(255) NOT NULL,
    connectorID     INT          NOT NULL,
    
    PRIMARY KEY(deviceID),
    UNIQUE(name, connectorID)
)
ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;


CREATE TABLE modems (
    imei            VARCHAR(255) NOT NULL,
    username        VARCHAR(255) NOT NULL,
    
    PRIMARY KEY(imei),

    FOREIGN KEY (username) REFERENCES users(username) ON DELETE RESTRICT
)
ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;


CREATE TABLE modemDevices (
    deviceNo        INT          NOT NULL AUTO_INCREMENT,
    imei            VARCHAR(255) NOT NULL,
    addr            INT          NOT NULL,
    deviceID        INT          NOT NULL,
    
    PRIMARY KEY(deviceNo),
    UNIQUE(imei, addr),

    FOREIGN KEY (deviceID) REFERENCES devices(deviceID) ON DELETE RESTRICT,
    FOREIGN KEY (imei) REFERENCES modems(imei) ON DELETE CASCADE
)
ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;


CREATE TABLE modemDeviceParams (
    deviceNo        INT          NOT NULL,
    name            VARCHAR(255) NOT NULL,
    value           VARCHAR(255) NOT NULL,

    PRIMARY KEY(deviceNo, name),
    
    FOREIGN KEY (deviceNo) REFERENCES modemDevices(deviceNo) ON DELETE CASCADE
)
ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;


INSERT INTO connectors VALUES (1, 'ain');
INSERT INTO connectors VALUES (2, 'm-bus');

INSERT INTO modems VALUES ('A123', 10);
INSERT INTO modems VALUES ('B456', 0);

INSERT INTO devices VALUES (100, 'Sharky 775', 1);
INSERT INTO devices VALUES (200, 'Siemens 55', 2);

INSERT INTO configs VALUES (1000, 'cfg 1000');
INSERT INTO configs VALUES (2000, 'cfg 2000');
INSERT INTO configs VALUES (3000, 'cfg 3000');

INSERT INTO modemDevices VALUES (10, 'A123', 1, 100, 1000);
INSERT INTO modemDevices VALUES (11, 'A123', 2, 100, 2000);
INSERT INTO modemDevices VALUES (12, 'A123', 254, 200, 3000);




-- https://www.youtube.com/watch?v=Q38C2QMc-nI&t=5m19s