• view_base_files
• view_files_tokens
• view_base_payments_customers_uncreated
• view_base_payments_credit_cards
• view_base_payments_credit_cards_unsynced
• view_base_places_suggestions_unconfirmed
• view_base_users
• view_base_users_summaries
• view_base_users_sessions

DROP   VIEW view_base_auth_provisional;
CREATE VIEW view_base_auth_provisional AS
    SELECT    *
    FROM      base_auth_provisional
    LEFT JOIN base_auth USING (AUTH_ID);

DROP   VIEW view_base_files;
CREATE VIEW view_base_files AS
    SELECT
        FILE,
        version,
        kind,
        original_filename,
        filename,
        filetype,
        filesize,
        fileextension,
        salt,
        token

    FROM base_files;

DROP   VIEW view_base_files_tokens;
CREATE VIEW view_base_files_tokens AS
    SELECT FILE, token FROM base_files;

DROP   VIEW view_base_forms_fields;
CREATE VIEW view_base_forms_fields AS
    SELECT
        *,

        IF('hidden' = field_type, 1, 0 )
        AS is_hidden,

        IF( field_customisable, 'Yes', 'No' )
        AS field_customisable_yn,

        IF( 0 = field_deleted, '', ' (Deleted)' )
        AS field_deleted_label,

        IF( 0 = field_deleted, '0', '1' )
        AS field_deleted_bool,

        IF( 0 = field_deleted, '', 'warning' )
        AS css_class,

        IF
        (
            'select' = field_type AND 0 != LENGTH( field_kind ),
            CONCAT( '<span title="', field_kind, '">Kind</span><br>' ),
            ''
        )
        AS field_kind_span

    FROM      base_forms_fields
    LEFT JOIN base_forms_fieldsets USING (FORM_ID,FIELDSET_ID)
    LEFT JOIN base_forms           USING (FORM_ID)
    ;

DROP   VIEW view_base_forms_fields_customised;
CREATE VIEW view_base_forms_fields_customised AS
    SELECT
        view_base_forms_fields.*,

        ORG_ID,
        FORM_FIELD_ID,
        form_field_created,
        form_field_ts,
        form_field_deleted,
        form_field_active,
        form_field_state,
        form_field_label,
        form_field_width,
        form_field_kind

    FROM      view_base_forms_fields
    LEFT JOIN base_forms_fields_customised USING (FORM_ID,FIELD_ID);

DROP   VIEW view_base_organisations_presets;
CREATE VIEW view_base_organisations_presets AS
    SELECT
        ORG_ID,
        PRESET_ID,
        preset_code,
        preset_name,
        preset_description,
        preset_type,
        preset_allow_override,

        PRESET_VALUE_ID,
        preset_value,
        preset_value_default,

        ORG_PRESET_ID,
        org_preset_deleted,
        org_preset_override_value

    FROM base_organisations_presets
    LEFT JOIN base_presets USING (PRESET_ID)
    LEFT JOIN base_presets_values USING (PRESET_ID,PRESET_VALUE_ID);

DROP   VIEW view_base_payments_customers_uncreated;
CREATE VIEW view_base_payments_customers_uncreated AS
    SELECT USER, customer_id FROM base_users
    LEFT JOIN base_payments_customers USING (USER)
    WHERE customer_id = '';

DROP   VIEW view_base_payments_credit_cards;
CREATE VIEW view_base_payments_credit_cards AS
    SELECT provided, final_four, token, processed, base_payments_customers.*
    FROM base_payments_credit_cards
    LEFT JOIN base_payments_customers USING (USER);

DROP   VIEW view_base_payments_credit_cards_unsynced;
CREATE VIEW view_base_payments_credit_cards_unsynced AS
    SELECT *
    FROM base_payments_credit_cards
    LEFT JOIN base_payments_customers USING (USER)
    WHERE processed=0;

DROP   VIEW view_base_places_suggestions_unconfirmed;
CREATE VIEW view_base_places_suggestions_unconfirmed AS
    SELECT    *
    FROM      base_places
    LEFT JOIN base_places_suggestions USING (PLACE_ID,GROUP_ID)
    WHERE     CONFIRMED_PLACE_SUGGESTION_ID=0;

DROP   VIEW view_base_users;
CREATE VIEW view_base_users AS
    SELECT
        USER,
        email,
        email_provisional,
        mobile,
        mobile_provisional,
        created,
        last_login,
        invalid_logins,
        user_hash,
        user_status,
        send_confirmation,
        sent,
        confirmation_sent,
        confirmed,
        user_deleted,
        DELETED_BY,
        IFNULL( deleted_by_email, '' ) AS deleted_by_email,
        given_name,
        family_name,
        visits,
        ts_users,
        type

    FROM      base_users
    LEFT JOIN base_users_uids USING (USER)
    LEFT JOIN
    (
        SELECT
            USER AS DELETED_BY,
            email AS deleted_by_email
            FROM base_users

    ) AS S0 USING (DELETED_BY);

DROP   VIEW view_base_users_admin;
CREATE VIEW view_base_users_admin AS
    SELECT
        USER,
        user_mfa_type,
        email,
        email_provisional,
        mobile,
        created,
        last_login,
        user_hash,
        user_status,
        send_confirmation,
        sent,
        confirmation_sent,
        confirmed,
        user_deleted,
        DELETED_BY,
        IFNULL( deleted_by_email, '' ) AS deleted_by_email,
        given_name,
        family_name,
        visits,
        ts_users,
        type

    FROM      base_users
    LEFT JOIN base_users_uids USING (USER)
    LEFT JOIN
    (
        SELECT
            USER AS DELETED_BY,
            email AS deleted_by_email
            FROM base_users

    ) AS S0 USING (DELETED_BY);

DROP   VIEW view_base_users_summaries;
CREATE VIEW view_base_users_summaries AS
    SELECT
        USER,
        given_name,
        family_name,
        email,
        type,
        user_mfa_type

  FROM      base_users
  LEFT JOIN base_users_uids USING (USER);

DROP   VIEW view_base_users_sessions;
CREATE VIEW view_base_users_sessions AS
    SELECT
        SESSION_ID,
        USER,
        SUBSTRING( sid, 1, 32 ) AS accessid,
        sid                     AS sessionid,
        type                    AS idtype,
        email,
        given_name,
        family_name,
        user_hash,
        group_code

    FROM      base_users_sessions
    LEFT JOIN base_users          USING (email)
    LEFT JOIN base_users_uids     USING (USER)
    ;