CREATE TABLE domains (
  id                	INT AUTO_INCREMENT,
  name              	VARCHAR(255) NOT NULL,
  master            	VARCHAR(255) DEFAULT NULL,
  last_check        	INT DEFAULT NULL,
  type              	VARCHAR(8) NOT NULL,
  notified_serial   	INT UNSIGNED DEFAULT NULL,
  account           	VARCHAR(40) CHARACTER SET 'utf8' DEFAULT NULL,
  options           	VARCHAR(60000) DEFAULT NULL,
  catalog           	VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (id)
) Engine=InnoDB CHARACTER SET 'latin1';

CREATE UNIQUE INDEX name_index ON domains(name);
CREATE INDEX catalog_idx ON domains(catalog);


CREATE TABLE records (
  id                	BIGINT AUTO_INCREMENT,
  domain_id         	INT NOT NULL,
  name              	VARCHAR(255) NOT NULL,
  type              	VARCHAR(10) DEFAULT NULL,
  content           	VARCHAR(64000) DEFAULT NULL,
  ttl               	INT DEFAULT NULL,
  prio              	SMALLINT unsigned DEFAULT NULL,
  change_date       	TIMESTAMP NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  disabled          	TINYINT(1) NOT NULL DEFAULT 0,
  ordername         	VARCHAR(255) BINARY DEFAULT NULL,
  auth              	TINYINT(1) NOT NULL DEFAULT 1,
  rev_name          	VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (id)
) Engine=InnoDB CHARACTER SET 'latin1';

ALTER TABLE records ADD CONSTRAINT `_fk_domains` FOREIGN KEY (`domain_id`) REFERENCES `domains` (`id`) ON DELETE CASCADE;
CREATE INDEX nametype_index ON records(name,type);
CREATE INDEX domain_id ON records(domain_id);
CREATE INDEX rev_name_domid ON records (domain_id,rev_name);
CREATE INDEX idx_domain_ordername ON records (domain_id,ordername);


CREATE TABLE supermasters (
  ip                	VARCHAR(64) NOT NULL,
  nameserver        	VARCHAR(255) NOT NULL,
  account           	VARCHAR(40) CHARACTER SET 'utf8' NOT NULL,
  PRIMARY KEY (ip, nameserver)
) Engine=InnoDB CHARACTER SET 'latin1';



CREATE TABLE comments (
  id                	INT AUTO_INCREMENT,
  domain_id         	INT NOT NULL,
  name              	VARCHAR(255) NOT NULL,
  type              	VARCHAR(10) NOT NULL,
  modified_at       	INT unsigned NOT NULL,
  account           	VARCHAR(40) CHARACTER SET 'utf8' DEFAULT NULL,
  comment           	TEXT CHARACTER SET 'utf8' NOT NULL,
  PRIMARY KEY (id)
) Engine=InnoDB CHARACTER SET 'latin1';

CREATE INDEX comments_name_type_idx ON comments (name, type);
CREATE INDEX comments_order_idx ON comments (domain_id, modified_at);
ALTER TABLE comments ADD CONSTRAINT `comments_domain_id_ibfk` FOREIGN KEY IF NOT EXISTS (`domain_id`) REFERENCES `domains` (`id`) ON DELETE CASCADE;


CREATE TABLE domainmetadata (
  id                	INT AUTO_INCREMENT,
  domain_id         	INT NOT NULL,
  kind              	VARCHAR(32),
  content           	TEXT,
  PRIMARY KEY (id)
) Engine=InnoDB CHARACTER SET 'latin1';

CREATE INDEX domainmetadata_idx ON domainmetadata (domain_id, kind);
ALTER TABLE domainmetadata ADD CONSTRAINT `domainmetadata_domain_id_ibfk` FOREIGN KEY IF NOT EXISTS (`domain_id`) REFERENCES `domains` (`id`) ON DELETE CASCADE;

CREATE TABLE cryptokeys (
  id                	INT  NOT NULL AUTO_INCREMENT,
  domain_id         	INT NOT NULL,
  flags             	INT NOT NULL,
  active            	BOOL,
  published         	BOOL DEFAULT 1,
  content           	TEXT,
  PRIMARY KEY(id)
) Engine=InnoDB CHARACTER SET 'latin1';

CREATE INDEX cryptokeys_domain_id_idx ON cryptokeys(domain_id);
ALTER TABLE cryptokeys ADD CONSTRAINT `cryptokeys_domain_id_ibfk` FOREIGN KEY IF NOT EXISTS (`domain_id`) REFERENCES `domains` (`id`) ON DELETE CASCADE;

CREATE TABLE tsigkeys (
  id                	INT AUTO_INCREMENT,
  name              	VARCHAR(255),
  algorithm         	VARCHAR(50),
  secret            	VARCHAR(255),
  PRIMARY KEY (id)
) Engine=InnoDB CHARACTER SET 'latin1';

CREATE UNIQUE INDEX tsigkeys_namealgo_idx ON tsigkeys(name, algorithm);
