<?xml version="1.0"?>
<!--
/**
 * @author Amasty Team
 * @copyright Copyright (c) Amasty (https://www.amasty.com)
 * @package Advanced Reports Base for Magento 2
 */-->
<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
    <table name="amasty_reports_customers_customers_daily" resource="default" engine="innodb" comment="Amasty Customers Daily">
        <column xsi:type="int" name="id" padding="10" unsigned="true" nullable="false" identity="true" comment="Id"/>
        <column xsi:type="date" name="period" comment="Period"/>
        <column xsi:type="smallint" name="store_id" padding="5" unsigned="true" nullable="true" identity="false" comment="Store Id"/>
        <column xsi:type="int" name="new_accounts" padding="10" unsigned="true" nullable="true" identity="false" comment="New Accounts"/>
        <column xsi:type="int" name="orders" padding="10" unsigned="true" nullable="true" identity="false" comment="Orders"/>
        <column xsi:type="int" name="reviews" padding="10" unsigned="true" nullable="true" identity="false" comment="Reviews"/>
        <constraint xsi:type="primary" referenceId="PRIMARY">
          <column name="id"/>
        </constraint>
        <constraint xsi:type="foreign" referenceId="AMASTY_REPORTS_CUSTOMERS_CUSTOMERS_DAILY_STORE_ID_STORE_STORE_ID" table="amasty_reports_customers_customers_daily" column="store_id" referenceTable="store" referenceColumn="store_id" onDelete="CASCADE"/>
        <constraint xsi:type="unique" referenceId="AMASTY_REPORTS_CUSTOMERS_CUSTOMERS_DAILY_PERIOD_STORE_ID">
          <column name="period"/>
          <column name="store_id"/>
        </constraint>
        <index referenceId="AMASTY_REPORTS_CUSTOMERS_CUSTOMERS_DAILY_STORE_ID" indexType="btree">
          <column name="store_id"/>
        </index>
    </table>

    <table name="amasty_reports_customers_customers_weekly" resource="default" engine="innodb" comment="Amasty Customers Weekly">
        <column xsi:type="int" name="id" padding="10" unsigned="true" nullable="false" identity="true" comment="Id"/>
        <column xsi:type="date" name="period" comment="Period"/>
        <column xsi:type="smallint" name="store_id" padding="5" unsigned="true" nullable="true" identity="false" comment="Store Id"/>
        <column xsi:type="int" name="new_accounts" padding="10" unsigned="true" nullable="true" identity="false" comment="New Accounts"/>
        <column xsi:type="int" name="orders" padding="10" unsigned="true" nullable="true" identity="false" comment="Orders"/>
        <column xsi:type="int" name="reviews" padding="10" unsigned="true" nullable="true" identity="false" comment="Reviews"/>
        <constraint xsi:type="primary" referenceId="PRIMARY">
          <column name="id"/>
        </constraint>
        <constraint xsi:type="foreign" referenceId="AMASTY_REPORTS_CSTRS_CSTRS_WEEKLY_STORE_ID_STORE_STORE_ID" table="amasty_reports_customers_customers_weekly" column="store_id" referenceTable="store" referenceColumn="store_id" onDelete="CASCADE"/>
        <constraint xsi:type="unique" referenceId="AMASTY_REPORTS_CUSTOMERS_CUSTOMERS_WEEKLY_PERIOD_STORE_ID">
          <column name="period"/>
          <column name="store_id"/>
        </constraint>
        <index referenceId="AMASTY_REPORTS_CUSTOMERS_CUSTOMERS_WEEKLY_STORE_ID" indexType="btree">
          <column name="store_id"/>
        </index>
    </table>

    <table name="amasty_reports_customers_customers_monthly" resource="default" engine="innodb" comment="Amasty Customers Monthly">
        <column xsi:type="int" name="id" padding="10" unsigned="true" nullable="false" identity="true" comment="Id"/>
        <column xsi:type="date" name="period" comment="Period"/>
        <column xsi:type="smallint" name="store_id" padding="5" unsigned="true" nullable="true" identity="false" comment="Store Id"/>
        <column xsi:type="int" name="new_accounts" padding="10" unsigned="true" nullable="true" identity="false" comment="New Accounts"/>
        <column xsi:type="int" name="orders" padding="10" unsigned="true" nullable="true" identity="false" comment="Orders"/>
        <column xsi:type="int" name="reviews" padding="10" unsigned="true" nullable="true" identity="false" comment="Reviews"/>
        <constraint xsi:type="primary" referenceId="PRIMARY">
          <column name="id"/>
        </constraint>
        <constraint xsi:type="foreign" referenceId="AMASTY_REPORTS_CSTRS_CSTRS_MONTHLY_STORE_ID_STORE_STORE_ID" table="amasty_reports_customers_customers_monthly" column="store_id" referenceTable="store" referenceColumn="store_id" onDelete="CASCADE"/>
        <constraint xsi:type="unique" referenceId="AMASTY_REPORTS_CUSTOMERS_CUSTOMERS_MONTHLY_PERIOD_STORE_ID">
          <column name="period"/>
          <column name="store_id"/>
        </constraint>
        <index referenceId="AMASTY_REPORTS_CUSTOMERS_CUSTOMERS_MONTHLY_STORE_ID" indexType="btree">
          <column name="store_id"/>
        </index>
    </table>

    <table name="amasty_reports_customers_customers_yearly" resource="default" engine="innodb" comment="Amasty Customers Yearly">
        <column xsi:type="int" name="id" padding="10" unsigned="true" nullable="false" identity="true" comment="Id"/>
        <column xsi:type="date" name="period" comment="Period"/>
        <column xsi:type="smallint" name="store_id" padding="5" unsigned="true" nullable="true" identity="false" comment="Store Id"/>
        <column xsi:type="int" name="new_accounts" padding="10" unsigned="true" nullable="true" identity="false" comment="New Accounts"/>
        <column xsi:type="int" name="orders" padding="10" unsigned="true" nullable="true" identity="false" comment="Orders"/>
        <column xsi:type="int" name="reviews" padding="10" unsigned="true" nullable="true" identity="false" comment="Reviews"/>
        <constraint xsi:type="primary" referenceId="PRIMARY">
          <column name="id"/>
        </constraint>
        <constraint xsi:type="foreign" referenceId="AMASTY_REPORTS_CSTRS_CSTRS_YEARLY_STORE_ID_STORE_STORE_ID" table="amasty_reports_customers_customers_yearly" column="store_id" referenceTable="store" referenceColumn="store_id" onDelete="CASCADE"/>
        <constraint xsi:type="unique" referenceId="AMASTY_REPORTS_CUSTOMERS_CUSTOMERS_YEARLY_PERIOD_STORE_ID">
          <column name="period"/>
          <column name="store_id"/>
        </constraint>
        <index referenceId="AMASTY_REPORTS_CUSTOMERS_CUSTOMERS_YEARLY_STORE_ID" indexType="btree">
          <column name="store_id"/>
        </index>
    </table>

    <table name="amasty_reports_rule" resource="default" engine="innodb" comment="Amasty Reports Rule Table">
        <column xsi:type="int" name="entity_id" padding="10" unsigned="true" nullable="false" identity="true" comment="Entity ID"/>
        <column xsi:type="text" name="title" nullable="false" comment="Rule Title"/>
        <column xsi:type="smallint" name="status" padding="5" unsigned="true" nullable="false" identity="false" comment="Status"/>
        <column xsi:type="datetime" name="updated_at" on_update="false" nullable="true" comment="Last Updated Date"/>
        <column xsi:type="mediumtext" name="conditions_serialized" nullable="true" comment="Conditions"/>
        <column xsi:type="boolean" name="pin" nullable="false" comment="Pin in Custom Reports"/>
        <constraint xsi:type="primary" referenceId="PRIMARY">
          <column name="entity_id"/>
        </constraint>
        <index referenceId="AMASTY_REPORTS_RULE_ENTITY_ID" indexType="btree">
          <column name="entity_id"/>
        </index>
    </table>

    <table name="amasty_reports_rule_index" resource="default" engine="innodb" comment="Amasty Reports Rule Index Table">
        <column xsi:type="int" name="rule_id" padding="10" unsigned="true" nullable="false" identity="false" comment="Rule ID"/>
        <column xsi:type="smallint" name="store_id" padding="5" unsigned="true" nullable="false" identity="false" comment="Store ID"/>
        <column xsi:type="int" name="product_id" padding="10" unsigned="true" nullable="false" identity="false" comment="Product ID"/>
        <constraint xsi:type="foreign" referenceId="AMASTY_REPORTS_RULE_IDX_PRD_ID_CAT_PRD_ENTT_ENTT_ID" table="amasty_reports_rule_index" column="product_id" referenceTable="catalog_product_entity" referenceColumn="entity_id" onDelete="CASCADE"/>
        <constraint xsi:type="foreign" referenceId="AMASTY_REPORTS_RULE_INDEX_RULE_ID_AMASTY_REPORTS_RULE_ENTITY_ID" table="amasty_reports_rule_index" column="rule_id" referenceTable="amasty_reports_rule" referenceColumn="entity_id" onDelete="CASCADE"/>
        <constraint xsi:type="foreign" referenceId="AMASTY_REPORTS_RULE_INDEX_STORE_ID_STORE_STORE_ID" table="amasty_reports_rule_index" column="store_id" referenceTable="store" referenceColumn="store_id" onDelete="CASCADE"/>
        <index referenceId="AMASTY_REPORTS_RULE_INDEX_RULE_ID_STORE_ID" indexType="btree">
          <column name="rule_id"/>
          <column name="store_id"/>
        </index>

        <constraint xsi:type="primary" referenceId="PRIMARY">
            <column name="rule_id"/>
            <column name="store_id"/>
            <column name="product_id"/>
        </constraint>
    </table>

    <table name="amasty_reports_abandoned_cart" resource="default" engine="innodb" comment="Amasty Reports Abandoned Carts">
        <column xsi:type="int" name="entity_id" padding="10" unsigned="true" nullable="false" identity="true" comment="Entity ID"/>
        <column xsi:type="int" name="quote_id" padding="10" unsigned="true" nullable="false" identity="false" comment="Quote ID"/>
        <column xsi:type="smallint" name="store_id" padding="5" unsigned="true" nullable="false" identity="false" comment="Store ID"/>
        <column xsi:type="text" name="customer_name" nullable="false" comment="Customer Name"/>
        <column xsi:type="smallint" name="status" padding="5" unsigned="true" nullable="false" identity="false" comment="Status"/>
        <column xsi:type="datetime" name="created_at" on_update="false" nullable="true" comment="Created Date"/>
        <column xsi:type="int" name="items_qty" padding="11" unsigned="false" nullable="true" identity="false" comment="Items Qty"/>
        <column xsi:type="text" name="products" nullable="false" comment="Products in Quote"/>
        <column xsi:type="decimal" name="grand_total" scale="4" precision="12" unsigned="false" nullable="true" default="0" comment="Grand Total"/>
        <column xsi:type="varchar" name="coupon_code" nullable="true" length="255" comment="Coupon Code"/>
        <column xsi:type="int" name="customer_id" padding="10" unsigned="true" nullable="true" identity="false" comment="Customer Id"/>
        <constraint xsi:type="primary" referenceId="PRIMARY">
          <column name="entity_id"/>
          <column name="quote_id"/>
        </constraint>
    </table>

    <table name="amasty_reports_notification" resource="default" engine="innodb" comment="Amasty Reports Schedule Notifications Table">
        <column xsi:type="int" name="entity_id" padding="10" unsigned="true" nullable="false" identity="true" comment="Entity ID"/>
        <column xsi:type="text" name="name" nullable="false" comment="Notification Title"/>
        <column xsi:type="text" name="reports" nullable="false" comment="Reports"/>
        <column xsi:type="varchar" name="store_ids" nullable="false" length="255" default="" comment="Store Ids"/>
        <column xsi:type="smallint" name="interval_qty" padding="6" unsigned="false" nullable="false" identity="false" comment="Interval Qty"/>
        <column xsi:type="smallint" name="interval" padding="6" unsigned="false" nullable="false" identity="false" comment="Interval"/>
        <column xsi:type="varchar" name="display_period" nullable="false" length="10"/>
        <column xsi:type="text" name="receiver" nullable="false" comment="Email Receiver"/>
        <column xsi:type="smallint" name="frequency" padding="6" unsigned="false" nullable="false" identity="false" comment="Frequency"/>
        <column xsi:type="varchar" name="cron_schedule" nullable="false" length="20" default="" comment="Cron Schedule"/>
        <constraint xsi:type="primary" referenceId="PRIMARY">
          <column name="entity_id"/>
        </constraint>
    </table>
</schema>
