create table projectrole_actor
(
    projectrole_id                   bigint unsigned                          not null,
    actor_id                         bigint unsigned                          not null,
    favorite                         tinyint(4) unsigned default 1            not null,
    sorting                          int(11) unsigned    default 1            null,
    hidden                           tinyint(4) unsigned default 0            null,
    file_id                          bigint unsigned                          null,
    e_expirationDate                 date                default '2012-01-01' not null,
    e_key                            varchar(255)                             null,
    inquiried                        varchar(5)          default 'no'         null,
    inquiryDetailsDeprecated         text                                     null,
    e_email                          varchar(255)                             null,
    e_messageDeprecated              text                                     null,
    fileIsDefault                    tinyint(4) unsigned default 0            null,
    e_invited                        tinyint(1) unsigned default 0            not null,
    e_notifyActor                    tinyint(1) unsigned                      null,
    e_notifyAgency                   tinyint(1) unsigned                      null,
    e_canEditApplicationProfile      tinyint(1) unsigned default 0            null,
    e_successNotificationUserIds     varchar(255)                             null,
    e_notifySuccess                  tinyint(1) unsigned default 0            null,
    comment                          text                                     null,
    state                            tinyint(1) unsigned                      null,
    state3                           tinyint(1) unsigned                      null,
    e_successNotificationActorAgency tinyint(1) unsigned default 0            null,
    e_successNotificationVoiceAgency tinyint unsigned    default 0            null,
    e_notifyVoiceAgency              tinyint(1) unsigned                      null,
    customFields                     mediumtext                               null,
    e_details                        mediumtext                               null,
    state2                           tinyint(1) unsigned                      null,
    id                               bigint unsigned auto_increment
        primary key,
    e_sentByEmail                    datetime                                 null,
    availabilityRequested_at         datetime                                 null,
    tags                             text                                     null,
    lastUploaded_at                  datetime                                 null,
    a_key                            varchar(255)                             null,
    e_canManageProfileFiles          tinyint(1) unsigned default 2            not null,
    constraint `unique`
        unique (projectrole_id, actor_id)
)
    charset = utf8;

create index index1
    on projectrole_actor (actor_id, projectrole_id);

create index index2
    on projectrole_actor (actor_id, projectrole_id);

create index index3
    on projectrole_actor (file_id);

create fulltext index index4
    on projectrole_actor (comment);

create index index5
    on projectrole_actor (state);

create table shootingdaybooking
(
    id                             bigint unsigned auto_increment
        primary key,
    shootingday_id                 bigint unsigned               null,
    actor_id                       bigint unsigned               null,
    state                          tinyint(1) unsigned           null,
    active                         tinyint(1) unsigned default 1 null,
    deleted_at                     datetime                      null,
    modified                       datetime                      null,
    created                        datetime                      null,
    created_by                     bigint unsigned               null,
    modified_by                    bigint unsigned               null,
    latestShootingdaybookinglog_id bigint unsigned               null
)
    charset = utf8;

create index i1
    on shootingdaybooking (active, shootingday_id, actor_id);

create index i2
    on shootingdaybooking (actor_id);

create OR REPLACE view actor_availability as
select 'shootingdaybooking'                          AS `type`,
       1                               AS `actorId`,
       `sb`.`state`                                  AS `state`,
       case `sb`.`state` when 5 then 0 else NULL end AS `availability`,
       '2024-04-06'                                     AS `day`
from `shootingdaybooking` `sb`
union
select 'shootingdayavailability' AS `type`,
       1           AS `actorId`,
       NULL                      AS `state`,
       1       AS `availability`,
       '2024-04-06'                 AS `day`
from `projectrole_actor` `pa`
