CREATE TABLE attachment (id BIGINT AUTO_INCREMENT, title VARCHAR(255), description LONGTEXT, url VARCHAR(255), type VARCHAR(255) DEFAULT 'other', object_id BIGINT, object_class VARCHAR(255), PRIMARY KEY(id)) ENGINE = INNODB;
CREATE TABLE issue_region (id BIGINT AUTO_INCREMENT, name VARCHAR(100) NOT NULL, region_id BIGINT NOT NULL, file_name VARCHAR(100), created_at DATETIME, updated_at DATETIME, INDEX region_id_idx (region_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE message (id BIGINT AUTO_INCREMENT, name VARCHAR(255), phone VARCHAR(255), email VARCHAR(255), message LONGTEXT, created_at DATETIME, updated_at DATETIME, PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE page (id BIGINT AUTO_INCREMENT, parent_id BIGINT, region_id BIGINT, title VARCHAR(255) NOT NULL, description LONGTEXT, content LONGTEXT, can_edit TINYINT(1) DEFAULT '0', can_delete TINYINT(1) DEFAULT '0', can_subpage TINYINT(1) DEFAULT '0', seq BIGINT DEFAULT '1000', created_at DATETIME, updated_at DATETIME, slug VARCHAR(255), UNIQUE INDEX sluggable_idx (slug), INDEX parent_id_idx (parent_id), INDEX region_id_idx (region_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE poll (id BIGINT AUTO_INCREMENT, name VARCHAR(255) NOT NULL, description LONGTEXT, is_published TINYINT(1) DEFAULT '0' NOT NULL, created_at DATETIME, updated_at DATETIME, PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE poll_answer (id BIGINT AUTO_INCREMENT, poll_id BIGINT, name VARCHAR(255) NOT NULL, answer_count BIGINT DEFAULT 0, answer_last DATETIME, seq BIGINT DEFAULT '10', INDEX poll_id_idx (poll_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE region (id BIGINT AUTO_INCREMENT, title VARCHAR(255) NOT NULL, subtitle VARCHAR(255) NOT NULL, created_at DATETIME, updated_at DATETIME, slug VARCHAR(255), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE sf_guard_group (id INT AUTO_INCREMENT, name VARCHAR(255) UNIQUE, description TEXT, created_at DATETIME, updated_at DATETIME, PRIMARY KEY(id)) ENGINE = INNODB;
CREATE TABLE sf_guard_group_permission (group_id INT, permission_id INT, created_at DATETIME, updated_at DATETIME, PRIMARY KEY(group_id, permission_id)) ENGINE = INNODB;
CREATE TABLE sf_guard_permission (id INT AUTO_INCREMENT, name VARCHAR(255) UNIQUE, description TEXT, created_at DATETIME, updated_at DATETIME, PRIMARY KEY(id)) ENGINE = INNODB;
CREATE TABLE sf_guard_remember_key (id INT AUTO_INCREMENT, user_id INT, remember_key VARCHAR(32), ip_address VARCHAR(50), created_at DATETIME, updated_at DATETIME, INDEX user_id_idx (user_id), PRIMARY KEY(id, ip_address)) ENGINE = INNODB;
CREATE TABLE sf_guard_user (id INT AUTO_INCREMENT, username VARCHAR(128) NOT NULL UNIQUE, algorithm VARCHAR(128) DEFAULT 'sha1' NOT NULL, salt VARCHAR(128), password VARCHAR(128), is_active TINYINT(1) DEFAULT '1', is_super_admin TINYINT(1) DEFAULT '0', last_login DATETIME, created_at DATETIME, updated_at DATETIME, INDEX is_active_idx_idx (is_active), PRIMARY KEY(id)) ENGINE = INNODB;
CREATE TABLE sf_guard_user_group (user_id INT, group_id INT, created_at DATETIME, updated_at DATETIME, PRIMARY KEY(user_id, group_id)) ENGINE = INNODB;
CREATE TABLE sf_guard_user_permission (user_id INT, permission_id INT, created_at DATETIME, updated_at DATETIME, PRIMARY KEY(user_id, permission_id)) ENGINE = INNODB;
ALTER TABLE issue_region ADD FOREIGN KEY (region_id) REFERENCES region(id);
ALTER TABLE page ADD FOREIGN KEY (region_id) REFERENCES region(id);
ALTER TABLE page ADD FOREIGN KEY (parent_id) REFERENCES page(id);
ALTER TABLE poll_answer ADD FOREIGN KEY (poll_id) REFERENCES poll(id);
ALTER TABLE sf_guard_group_permission ADD FOREIGN KEY (permission_id) REFERENCES sf_guard_permission(id) ON DELETE CASCADE;
ALTER TABLE sf_guard_group_permission ADD FOREIGN KEY (group_id) REFERENCES sf_guard_group(id) ON DELETE CASCADE;
ALTER TABLE sf_guard_remember_key ADD FOREIGN KEY (user_id) REFERENCES sf_guard_user(id) ON DELETE CASCADE;
ALTER TABLE sf_guard_user_group ADD FOREIGN KEY (user_id) REFERENCES sf_guard_user(id) ON DELETE CASCADE;
ALTER TABLE sf_guard_user_group ADD FOREIGN KEY (group_id) REFERENCES sf_guard_group(id) ON DELETE CASCADE;
ALTER TABLE sf_guard_user_permission ADD FOREIGN KEY (user_id) REFERENCES sf_guard_user(id) ON DELETE CASCADE;
ALTER TABLE sf_guard_user_permission ADD FOREIGN KEY (permission_id) REFERENCES sf_guard_permission(id) ON DELETE CASCADE;