
create table identification_rules(
    id NUMBER(19) primary key not null,
    title varchar2(100) not null,
    project_id NUMBER(19) references projects(id),
    channel_id NUMBER(19) references channels(id),
    "order" NUMBER(10) not null,
    active NUMBER(1) not null,
    enabled NUMBER(1) not null,
    deleted NUMBER(1) not null,
    success_message varchar2(4000),
    identify_by varchar2(100) not null ,
    ask_identificator NUMBER(1) NOT NULL,
    identification_question varchar2(1000),
    created_at timestamp with time zone NOT NULL,
    updated_at timestamp with time zone NOT NULL,
    deleted_at timestamp with time zone
);
create sequence identification_rules_id_seq;
create index identification_rules_project_id on identification_rules(project_id);
create index identification_rules_channel_id on identification_rules(channel_id);

create table client_identifications(
                                       client_id NUMBER(19) not null references clients(id),
                                       last_ticket_id NUMBER(19) not null references tickets(id),
                                       status varchar(200) not null,
                                       secret_word_validator varchar2(200),
                                       failed_rule_id NUMBER(19),
                                       created_at timestamp with time zone NOT NULL,
                                       updated_at timestamp with time zone NOT NULL
);
create index client_identifications_client_id on client_identifications(client_id);