CREATE TABLE `meta` (
  `id`    INTEGER PRIMARY KEY AUTOINCREMENT,
  `name`  TEXT NOT NULL,
  `value` TEXT NOT NULL
);

CREATE TABLE `client` (
  `id`           INTEGER PRIMARY KEY AUTOINCREMENT,
  `connect_type` INT  NOT NULL,
  `addr`         TEXT NOT NULL,
  `reso`         INT  NULL,
  `name`         TEXT NOT NULL,
  `options`      TEXT NULL,
  `port`         INT  NOT NULL
);

CREATE TABLE `led` (
  `id`        INTEGER PRIMARY KEY AUTOINCREMENT,
  `name`      TEXT NULL,
  `options`   TEXT NULL,
  `local_id`  INT  NULL,
  `client_id` INT  NOT NULL,
  CONSTRAINT `fk_led_client`
  FOREIGN KEY (`client_id`)
  REFERENCES `client` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION
);

CREATE INDEX `fk_led_client_idx`
  ON `led` (`client_id` ASC);

CREATE TABLE IF NOT EXISTS `group` (
  `id`   INTEGER PRIMARY KEY AUTOINCREMENT,
  `name` TEXT NOT NULL,
  `type` INT  NOT NULL
);

CREATE TABLE IF NOT EXISTS `led_has_group` (
  `led_id`   INT NOT NULL,
  `group_id` INT NOT NULL,
  PRIMARY KEY (`led_id`, `group_id`),
  CONSTRAINT `fk_led_has_group_led1`
  FOREIGN KEY (`led_id`)
  REFERENCES `led` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION,
  CONSTRAINT `fk_led_has_group_group1`
  FOREIGN KEY (`group_id`)
  REFERENCES `group` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION
);

CREATE INDEX `fk_led_has_group_group1_idx`
  ON `led_has_group` (`group_id` ASC);

CREATE INDEX `fk_led_has_group_led1_idx`
  ON `led_has_group` (`led_id` ASC);


INSERT INTO meta ("name", "value") VALUES ("version", "1");