CREATE TABLE cs_setting (id INT AUTO_INCREMENT, name VARCHAR(255) NOT NULL UNIQUE, type VARCHAR(255) DEFAULT 'input' NOT NULL, widget_options LONGTEXT, value LONGTEXT, setting_group VARCHAR(255) DEFAULT NULL, setting_default LONGTEXT, slug VARCHAR(255), UNIQUE INDEX sluggable_idx (slug), 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_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_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_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_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_permission (user_id INT, permission_id INT, created_at DATETIME, updated_at DATETIME, PRIMARY KEY(user_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 application_log (id BIGINT AUTO_INCREMENT, source_module VARCHAR(100), source_action VARCHAR(100), object_class VARCHAR(100), object_id BIGINT, object_action VARCHAR(100), params TEXT, created_at DATETIME, updated_at DATETIME, created_by BIGINT, updated_by BIGINT, PRIMARY KEY(id)) ENGINE = INNODB;
CREATE TABLE application_log_user (id BIGINT AUTO_INCREMENT, user_id BIGINT NOT NULL, application_log_id BIGINT NOT NULL, visited_at DATETIME, created_at DATETIME, updated_at DATETIME, INDEX application_log_id_idx (application_log_id), PRIMARY KEY(id)) ENGINE = INNODB;
CREATE TABLE attachment (id BIGINT AUTO_INCREMENT, card_id BIGINT NOT NULL, supplier_code VARCHAR(50), contract_type INT, logic_state VARCHAR(2), logic_state_date DATETIME, refund_type VARCHAR(50), item VARCHAR(255), dealer_name VARCHAR(255), color VARCHAR(200), price DECIMAL(10,2), numberplate VARCHAR(200), serialnumber VARCHAR(200), vintage INT, cert_info VARCHAR(200), cert_taken TINYINT(1) DEFAULT '0', deposit DECIMAL(10,2), price_funding DECIMAL(10,2), price_payment DECIMAL(10,2), lease_period INT, lease_date DATE, requisition_date DATE, update_date DATE, agency_name VARCHAR(255), agency_person VARCHAR(255), guarantee_code VARCHAR(200), guarantee_name VARCHAR(255), guarantee_cell VARCHAR(200), guarantee_address_street VARCHAR(200), guarantee_address_city VARCHAR(200), guarantee_address_zipcode VARCHAR(200), guarantee_address_cell VARCHAR(200), guarantee_address_phone VARCHAR(200), guarantee_address_phone2 VARCHAR(200), guarantee_altaddress_street VARCHAR(200), guarantee_altaddress_city VARCHAR(200), guarantee_altaddress_zipcode VARCHAR(200), guarantee_altaddress_cell VARCHAR(200), guarantee_altaddress_phone VARCHAR(200), guarantee_altaddress_phone2 VARCHAR(200), created_at DATETIME, updated_at DATETIME, created_by BIGINT, updated_by BIGINT, INDEX card_id_idx (card_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = INNODB;
CREATE TABLE regal (id BIGINT AUTO_INCREMENT, name VARCHAR(100), surname VARCHAR(100), code VARCHAR(20), company VARCHAR(100), company_code VARCHAR(20), city VARCHAR(200), street VARCHAR(200), zipcode VARCHAR(10), phone VARCHAR(20), description LONGTEXT, serialnumber VARCHAR(100), note LONGTEXT, created_at DATETIME, updated_at DATETIME, created_by BIGINT, updated_by BIGINT, PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = INNODB;
CREATE TABLE invoice_item (id BIGINT AUTO_INCREMENT, invoice_id BIGINT, card_id BIGINT, price DECIMAL(10,2), has_removal TINYINT(1), has_own TINYINT(1), has_registration TINYINT(1), costs DECIMAL(10,2), costs_note VARCHAR(255), INDEX invoice_id_idx (invoice_id), INDEX card_id_idx (card_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = INNODB;
CREATE TABLE sf_guard_user_profile (id BIGINT AUTO_INCREMENT, user_id INT NOT NULL, first_name VARCHAR(200), last_name VARCHAR(200), email VARCHAR(255), pass_plain VARCHAR(200), salary_percent MEDIUMINT DEFAULT '50', INDEX user_id_idx (user_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = INNODB;
CREATE TABLE debt (id BIGINT AUTO_INCREMENT, attachment_id BIGINT NOT NULL, update_date DATE, penalty DECIMAL(10,2), balance DECIMAL(10,2), payment DECIMAL(10,2), insurance DECIMAL(10,2), fee DECIMAL(10,2), payed DECIMAL(10,2), created_at DATETIME, updated_at DATETIME, created_by BIGINT, updated_by BIGINT, INDEX attachment_id_idx (attachment_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = INNODB;
CREATE TABLE supplier_state_def (id BIGINT AUTO_INCREMENT, supplier_id BIGINT NOT NULL, name VARCHAR(100), costs_percent BIGINT, costs_price DECIMAL(10,2), is_default TINYINT(1) DEFAULT '0', created_at DATETIME, updated_at DATETIME, created_by BIGINT, updated_by BIGINT, INDEX supplier_id_idx (supplier_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = INNODB;
CREATE TABLE message_file (id BIGINT AUTO_INCREMENT, message_id BIGINT NOT NULL, file_name VARCHAR(255), name VARCHAR(255), created_at DATETIME, updated_at DATETIME, created_by BIGINT, updated_by BIGINT, INDEX message_id_idx (message_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = INNODB;
CREATE TABLE attachment_file (id BIGINT AUTO_INCREMENT, attachment_id BIGINT NOT NULL, file_name VARCHAR(255), name VARCHAR(255), created_at DATETIME, updated_at DATETIME, created_by BIGINT, updated_by BIGINT, INDEX attachment_id_idx (attachment_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = INNODB;
CREATE TABLE invoice (id BIGINT AUTO_INCREMENT, supplier_id BIGINT, code VARCHAR(20), tax_code VARCHAR(20), address_street VARCHAR(200), address_city VARCHAR(200), address_zipcode VARCHAR(20), note LONGTEXT, created_at DATETIME, updated_at DATETIME, created_by BIGINT, updated_by BIGINT, INDEX supplier_id_idx (supplier_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = INNODB;
CREATE TABLE supplier (id BIGINT AUTO_INCREMENT, name VARCHAR(100), template_report LONGTEXT, code VARCHAR(20), tax_code VARCHAR(20), address_street VARCHAR(200), address_city VARCHAR(200), address_zipcode VARCHAR(20), created_at DATETIME, updated_at DATETIME, created_by BIGINT, updated_by BIGINT, PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = INNODB;
CREATE TABLE card (id BIGINT AUTO_INCREMENT, user_id BIGINT, state VARCHAR(50), state_date DATETIME, supplier_id BIGINT NOT NULL, days_to_solve BIGINT DEFAULT '30' NOT NULL, due_day SMALLINT, refund_numbers VARCHAR(100), user_finished TINYINT(1) DEFAULT '0', salary_percent SMALLINT, case_end_date DATE, case_income DECIMAL(10,2), case_penalty DECIMAL(10,2), regal_connected TINYINT(1) DEFAULT '0', regal_note LONGTEXT, client_code VARCHAR(20), client_name VARCHAR(255), cell VARCHAR(20), phone VARCHAR(20), phone_note LONGTEXT, address_street VARCHAR(200), address_city VARCHAR(200), address_zipcode VARCHAR(200), address_cell VARCHAR(200), address_phone VARCHAR(200), address_phone2 VARCHAR(200), altaddress_street VARCHAR(200), altaddress_city VARCHAR(200), altaddress_zipcode VARCHAR(200), altaddress_cell VARCHAR(200), altaddress_phone VARCHAR(200), altaddress_phone2 VARCHAR(200), income DECIMAL(10,2), income_other DECIMAL(10,2), income_partner DECIMAL(10,2), employer VARCHAR(255), employer_position VARCHAR(200), employer_phone VARCHAR(200), employer_street VARCHAR(200), employer_city VARCHAR(200), employer_zipcode VARCHAR(200), created_at DATETIME, updated_at DATETIME, created_by BIGINT, updated_by BIGINT, INDEX supplier_id_idx (supplier_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = INNODB;
CREATE TABLE payment (id BIGINT AUTO_INCREMENT, attachment_id BIGINT NOT NULL, update_date DATE, price DECIMAL(10,2), code VARCHAR(50), bank_code VARCHAR(50), payment_date DATE, payment_type VARCHAR(50), handover_date DATE, note LONGTEXT, created_at DATETIME, updated_at DATETIME, created_by BIGINT, updated_by BIGINT, UNIQUE INDEX one_price_per_day_idx (attachment_id, price, bank_code, payment_date, payment_type), INDEX attachment_id_idx (attachment_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = INNODB;
CREATE TABLE card_regal (id BIGINT AUTO_INCREMENT, card_id BIGINT, regal_id BIGINT, column_match VARCHAR(50), created_at DATETIME, updated_at DATETIME, UNIQUE INDEX unique_one_idx (card_id, regal_id, column_match), INDEX card_id_idx (card_id), INDEX regal_id_idx (regal_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = INNODB;
CREATE TABLE message (id BIGINT AUTO_INCREMENT, card_id BIGINT NOT NULL, description LONGTEXT NOT NULL, state VARCHAR(50), created_at DATETIME, updated_at DATETIME, created_by BIGINT, updated_by BIGINT, INDEX card_id_idx (card_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = INNODB;
CREATE TABLE regal_file (id BIGINT AUTO_INCREMENT, regal_id BIGINT NOT NULL, file_name VARCHAR(255), name VARCHAR(255), created_at DATETIME, updated_at DATETIME, created_by BIGINT, updated_by BIGINT, INDEX regal_id_idx (regal_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = INNODB;
CREATE TABLE statistic (attachment_id BIGINT, card_id INT NOT NULL, supplier_id INT NOT NULL, user_id INT NOT NULL, user_first_name VARCHAR(200), user_last_name VARCHAR(200), contract_type INT, supplier VARCHAR(200), supplier_code VARCHAR(200), client VARCHAR(200), start_date DATE, end_date DATE, case_days INT, state VARCHAR(200), sum_supplier DECIMAL(10,2), sum_payment DECIMAL(10,2), case_penalty DECIMAL(10,2), cert_taken TINYINT(1) DEFAULT '0', case_income DECIMAL(10,2), user_finished TINYINT(1), PRIMARY KEY(attachment_id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = INNODB;
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_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_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;
ALTER TABLE application_log_user ADD FOREIGN KEY (application_log_id) REFERENCES application_log(id);
ALTER TABLE attachment ADD FOREIGN KEY (card_id) REFERENCES card(id);
ALTER TABLE invoice_item ADD FOREIGN KEY (invoice_id) REFERENCES invoice(id);
ALTER TABLE invoice_item ADD FOREIGN KEY (card_id) REFERENCES card(id);
ALTER TABLE sf_guard_user_profile ADD FOREIGN KEY (user_id) REFERENCES sf_guard_user(id) ON DELETE CASCADE;
ALTER TABLE debt ADD FOREIGN KEY (attachment_id) REFERENCES attachment(id);
ALTER TABLE supplier_state_def ADD FOREIGN KEY (supplier_id) REFERENCES supplier(id);
ALTER TABLE message_file ADD FOREIGN KEY (message_id) REFERENCES message(id);
ALTER TABLE attachment_file ADD FOREIGN KEY (attachment_id) REFERENCES attachment(id);
ALTER TABLE invoice ADD FOREIGN KEY (supplier_id) REFERENCES supplier(id);
ALTER TABLE card ADD FOREIGN KEY (supplier_id) REFERENCES supplier(id);
ALTER TABLE payment ADD FOREIGN KEY (attachment_id) REFERENCES attachment(id);
ALTER TABLE card_regal ADD FOREIGN KEY (regal_id) REFERENCES regal(id);
ALTER TABLE card_regal ADD FOREIGN KEY (card_id) REFERENCES card(id);
ALTER TABLE message ADD FOREIGN KEY (card_id) REFERENCES card(id);
ALTER TABLE regal_file ADD FOREIGN KEY (regal_id) REFERENCES regal(id);