<?php
declare(strict_types=1);
namespace DoctrineMigrations;
use Doctrine\DBAL\Schema\Schema;
use Doctrine\Migrations\AbstractMigration;
/**
* Migration 1 - create basic tables
*/
final class Version20230908151529 extends AbstractMigration
{
public function getDescription(): string
{
return '';
}
public function up(Schema $schema): void
{
$this->addSql('CREATE TABLE IF NOT EXISTS `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`admin_id` int(11) DEFAULT NULL,
`email` varchar(180) NOT NULL,
`roles` text NOT NULL,
`user_type` int(11) NOT NULL,
`password` varchar(255) DEFAULT NULL,
`main_admin` tinyint(1) NOT NULL DEFAULT 0,
`super_admin` tinyint(1) NOT NULL DEFAULT 0,
`is_verified` tinyint(1) NOT NULL DEFAULT 1,
`is_active` tinyint(2) DEFAULT NULL,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
`deleted_at` datetime DEFAULT NULL,
`is_deleted` tinyint(1) DEFAULT 0,
`user_group_id` int(11) DEFAULT NULL,
`created_by` int(11) DEFAULT NULL,
`updated_by` int(11) DEFAULT NULL,
`deleted_by` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `user_user_group` (`user_group_id`),
KEY `user_type` (`user_type`),
KEY `admin_id` (`admin_id`),
KEY `created_by` (`created_by`),
KEY `updated_by` (`updated_by`),
KEY `deleted_by` (`deleted_by`),
CONSTRAINT `user_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `user` (`id`),
CONSTRAINT `user_ibfk_2` FOREIGN KEY (`updated_by`) REFERENCES `user` (`id`),
CONSTRAINT `user_ibfk_3` FOREIGN KEY (`deleted_by`) REFERENCES `user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=719 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;');
$this->addSql('CREATE TABLE IF NOT EXISTS `user_group` (
`id` int(11) NOT NULL,
`user_group` varchar(255) NOT NULL,
`is_active` int(1) NOT NULL,
`is_deleted` int(1) NOT NULL,
`created_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;');
$this->addSql('CREATE TABLE IF NOT EXISTS `user_profile` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) DEFAULT NULL,
`ref_id` int(11) NOT NULL DEFAULT 1,
`first_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`last_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`user_profile_image` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`phone` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`company_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`company_phone` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`language` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`company_address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`town` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`state` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`country` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`postcode` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`last_login_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`),
CONSTRAINT `user_profile_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=411 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;');
$this->addSql('CREATE TABLE IF NOT EXISTS `tc_user_roles` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`role_key` varchar(100) NOT NULL,
`category` varchar(100) NOT NULL,
`role` varchar(100) NOT NULL,
`is_active` tinyint(1) NOT NULL DEFAULT 1,
`is_deleted` tinyint(1) NOT NULL DEFAULT 0,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;');
$this->addSql('CREATE TABLE IF NOT EXISTS `terminology` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`terminology_key` varchar(50) NOT NULL,
`singular_text` varchar(255) DEFAULT NULL,
`plural_text` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `terminology_key` (`terminology_key`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;');
$this->addSql('CREATE TABLE IF NOT EXISTS `company` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`company_title` varchar(255) NOT NULL,
`is_active` tinyint(1) NOT NULL DEFAULT 1,
`is_deleted` tinyint(1) NOT NULL DEFAULT 0,
`created_at` datetime DEFAULT NULL,
`created_by` int(11) DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
`updated_by` int(11) DEFAULT NULL,
`deleted_at` datetime DEFAULT NULL,
`deleted_by` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `created_by` (`created_by`),
KEY `updated_by` (`updated_by`),
KEY `deleted_by` (`deleted_by`),
CONSTRAINT `company_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `company_ibfk_2` FOREIGN KEY (`updated_by`) REFERENCES `user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `company_ibfk_3` FOREIGN KEY (`deleted_by`) REFERENCES `user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=199 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;');
$this->addSql('CREATE TABLE IF NOT EXISTS `account_settings` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`version` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=COMPACT;');
$this->addSql("CREATE TABLE IF NOT EXISTS `company_settings` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`company_id` int(11) NOT NULL,
`company_domain` varchar(255) DEFAULT NULL,
`company_image` varchar(255) DEFAULT NULL,
`account_version` int(11) DEFAULT NULL,
`plan` enum('demo','free_trail','enterprise') NOT NULL DEFAULT 'free_trail',
`plan_expire_date` date DEFAULT NULL,
`default_form_id` int(11) DEFAULT NULL,
`system_language` varchar(10) NOT NULL,
`date_format` varchar(255) DEFAULT NULL,
`time_format` varchar(255) DEFAULT NULL,
`week_start_day` varchar(255) DEFAULT NULL,
`datetime_format` varchar(255) DEFAULT NULL,
`timezone` varchar(255) DEFAULT NULL,
`missed_audit_cron_time` time DEFAULT NULL,
`missed_audit_last_cron_date` datetime DEFAULT NULL,
`auto_schedule_operator_cron_time` time DEFAULT NULL,
`auto_schedule_operator_last_crone_date` datetime DEFAULT NULL,
`junk_file_expire` int(11) NOT NULL DEFAULT 30,
`user_refid_prefix` varchar(50) NOT NULL DEFAULT 'LT',
`card_refid_prefix` varchar(50) NOT NULL DEFAULT 'T',
`card_refid_starts_from` int(11) NOT NULL DEFAULT 1,
`no_of_users` int(11) NOT NULL DEFAULT 0,
`no_of_user_types` int(11) NOT NULL DEFAULT 0,
`no_of_boards` int(11) NOT NULL DEFAULT 0,
`no_of_columns` int(11) NOT NULL DEFAULT 0,
`no_of_rows` int(11) NOT NULL DEFAULT 0,
`no_of_card_layouts` int(11) NOT NULL DEFAULT 0,
`no_of_card_layout_fields` int(11) NOT NULL DEFAULT 0,
`has_excel_upload` tinyint(4) NOT NULL DEFAULT 0,
`has_multiple_layouts` tinyint(4) NOT NULL DEFAULT 0,
`has_multiple_section` tinyint(1) NOT NULL DEFAULT 0,
`has_card_timer` tinyint(1) NOT NULL DEFAULT 0,
`has_theme_customization` tinyint(4) NOT NULL DEFAULT 0,
`c1` tinyint(1) NOT NULL DEFAULT 0,
`has_transfer_card` tinyint(1) NOT NULL DEFAULT 0,
`has_draft_cards` tinyint(1) NOT NULL DEFAULT 0,
`has_skill_matrix` tinyint(1) NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
KEY `company_id` (`company_id`),
KEY `account_version` (`account_version`),
CONSTRAINT `company_settings_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `company` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
CONSTRAINT `company_settings_ibfk_2` FOREIGN KEY (`account_version`) REFERENCES `account_settings` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=184 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;");
$this->addSql('CREATE TABLE IF NOT EXISTS `company_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`company_id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
`is_main_user` tinyint(1) NOT NULL,
`is_default_company` tinyint(1) NOT NULL DEFAULT 1,
`assigned_date` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `company_id` (`company_id`),
KEY `user_id` (`user_id`),
CONSTRAINT `company_user_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `company` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
CONSTRAINT `company_user_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=585 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;');
$this->addSql('CREATE TABLE IF NOT EXISTS `company_terminology` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`company_id` int(11) NOT NULL,
`terminology_id` int(11) NOT NULL,
`singular_text` varchar(255) NOT NULL,
`plural_text` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `company_id_2` (`company_id`,`terminology_id`),
KEY `company_id` (`company_id`),
KEY `terminology_id` (`terminology_id`) USING BTREE,
CONSTRAINT `company_terminology_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `company` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
CONSTRAINT `company_terminology_ibfk_2` FOREIGN KEY (`terminology_id`) REFERENCES `terminology` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=127 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;');
$this->addSql('CREATE TABLE IF NOT EXISTS `tc_user_type` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`company_id` int(11) NOT NULL,
`type_title` varchar(255) NOT NULL,
`roles` mediumtext DEFAULT NULL,
`is_active` int(1) NOT NULL DEFAULT 1,
`is_default` tinyint(1) NOT NULL DEFAULT 0,
`is_deleted` int(1) NOT NULL DEFAULT 0,
`created_at` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `company_id` (`company_id`),
CONSTRAINT `tc_user_type_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `company` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=358 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;');
$this->addSql('CREATE TABLE IF NOT EXISTS `login_sessions` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) DEFAULT NULL,
`company_id` int(11) NOT NULL,
`location` mediumtext DEFAULT NULL,
`device` varchar(255) DEFAULT NULL,
`ip_address` varchar(255) DEFAULT NULL,
`action` varchar(255) NOT NULL,
`action_time` datetime DEFAULT NULL,
`last_active_time` datetime DEFAULT NULL,
`last_access_route` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`),
KEY `company_id` (`company_id`),
CONSTRAINT `login_sessions_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `company` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
CONSTRAINT `login_sessions_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=977 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;');
$this->addSql("CREATE TABLE IF NOT EXISTS `reset_password_request` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`selector` varchar(20) NOT NULL,
`hashed_token` varchar(100) NOT NULL,
`requested_at` datetime NOT NULL COMMENT '(DC2Type:datetime_immutable)',
`expires_at` datetime NOT NULL COMMENT '(DC2Type:datetime_immutable)',
PRIMARY KEY (`id`) USING BTREE,
KEY `IDX_7CE748AA76ED395` (`user_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=215 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;");
$this->addSql("CREATE TABLE IF NOT EXISTS `tc_mail_log` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`company_id` int(11) NOT NULL,
`email` varchar(255) NOT NULL,
`user_id` int(11) DEFAULT NULL,
`mail_type` enum('contact_us','audit','defect','admin_user_registration','missed_audit','invite_user') DEFAULT NULL,
`is_sent` tinyint(1) NOT NULL DEFAULT 0,
`sent_at` datetime DEFAULT NULL,
`is_opened` tinyint(1) NOT NULL DEFAULT 0,
`opened_at` datetime DEFAULT NULL,
`is_bounced` tinyint(1) NOT NULL DEFAULT 0,
`bounced_at` datetime DEFAULT NULL,
`is_resent` tinyint(1) NOT NULL DEFAULT 0,
`resent_at` datetime DEFAULT NULL,
`resent_count` int(11) NOT NULL,
`subject` text DEFAULT NULL,
`preview_mail_content` longtext DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`),
KEY `company_id` (`company_id`),
CONSTRAINT `tc_mail_log_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `company` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
CONSTRAINT `tc_mail_log_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=120 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;");
$this->addSql('CREATE TABLE IF NOT EXISTS `tc_mail_spool` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`company_id` int(11) NOT NULL,
`mail_log_id` int(11) NOT NULL,
`email` varchar(255) NOT NULL,
`subject` varchar(255) NOT NULL,
`mail_content` mediumtext NOT NULL,
`is_ready` tinyint(1) NOT NULL DEFAULT 0,
`is_cron` tinyint(1) NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
KEY `mail_log_id` (`mail_log_id`),
KEY `company_id` (`company_id`),
CONSTRAINT `tc_mail_spool_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `company` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
CONSTRAINT `tc_mail_spool_ibfk_2` FOREIGN KEY (`mail_log_id`) REFERENCES `tc_mail_log` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=480 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;');
$this->addSql('CREATE TABLE IF NOT EXISTS `tc_migraion` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`migration_key` varchar(255) NOT NULL,
`run_at` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;');
$this->addSql('CREATE TABLE IF NOT EXISTS `tc_upload_files` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`company_id` int(11) NOT NULL,
`filename` text NOT NULL,
`real_filename` text NOT NULL,
`file_type` varchar(255) NOT NULL,
`file_mime_type` varchar(255) DEFAULT NULL,
`file_size` varchar(255) NOT NULL,
`file_size_byte` int(11) NOT NULL DEFAULT 0,
`uploaded_by` int(11) NOT NULL,
`uploaded_at` datetime NOT NULL,
PRIMARY KEY (`id`),
KEY `uploaded_by` (`uploaded_by`),
KEY `company_id` (`company_id`),
CONSTRAINT `tc_upload_files_ibfk_1` FOREIGN KEY (`uploaded_by`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
CONSTRAINT `tc_upload_files_ibfk_2` FOREIGN KEY (`company_id`) REFERENCES `company` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=1201 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;');
// $this->addSql('');
}
public function down(Schema $schema): void
{
$this->addSql('DROP TABLE IF EXISTS `user`;');
$this->addSql('DROP TABLE IF EXISTS `user_group`;');
$this->addSql('DROP TABLE IF EXISTS `user_profile`;');
$this->addSql('DROP TABLE IF EXISTS `tc_user_roles`;');
$this->addSql('DROP TABLE IF EXISTS `terminology`;');
$this->addSql('DROP TABLE IF EXISTS `company`;');
$this->addSql('DROP TABLE IF EXISTS `account_settings`;');
$this->addSql('DROP TABLE IF EXISTS `company_settings`;');
$this->addSql('DROP TABLE IF EXISTS `company_user`;');
$this->addSql('DROP TABLE IF EXISTS `company_terminology`;');
$this->addSql('DROP TABLE IF EXISTS `tc_user_type`;');
$this->addSql('DROP TABLE IF EXISTS `login_sessions`;');
$this->addSql('DROP TABLE IF EXISTS `reset_password_request`;');
$this->addSql('DROP TABLE IF EXISTS `tc_mail_log`;');
$this->addSql('DROP TABLE IF EXISTS `tc_mail_spool`;');
$this->addSql('DROP TABLE IF EXISTS `tc_migraion`;');
$this->addSql('DROP TABLE IF EXISTS `tc_upload_files`;');
}
}