/******************************************************************************/
/***          Generated by IBExpert 2011.01.11 12.05.2015 18:40:48          ***/
/******************************************************************************/

SET SQL DIALECT 3;

SET NAMES WIN1251;

CREATE DATABASE 'c:\DELIVERYRK7\DB\DELIVERYRK7.FDB'
USER 'SYSDBA' PASSWORD 'masterkey'
PAGE_SIZE 4096
DEFAULT CHARACTER SET WIN1251 COLLATION WIN1251;



/******************************************************************************/
/***                         User Defined Functions                         ***/
/******************************************************************************/

DECLARE EXTERNAL FUNCTION UDF_CLEARDAY
    DATE
    RETURNS DATE
    ENTRY_POINT 'UDF_CLEARDAY' MODULE_NAME 'CinemaUF';


DECLARE EXTERNAL FUNCTION UDF_CLEARYEAR
    DATE
    RETURNS DATE
    ENTRY_POINT 'UDF_CLEARYEAR' MODULE_NAME 'CinemaUF';


DECLARE EXTERNAL FUNCTION UDF_CRLF

    RETURNS CSTRING(10) FREE_IT
    ENTRY_POINT 'UDF_CRLF' MODULE_NAME 'CinemaUF';


DECLARE EXTERNAL FUNCTION UDF_DATESCAT
    DATE,
    DATE
    RETURNS DATE
    ENTRY_POINT 'UDF_DatesCat' MODULE_NAME 'CinemaUF';


DECLARE EXTERNAL FUNCTION UDF_DATESCONCAT
    DATE,
    DATE
    RETURNS DATE
    ENTRY_POINT 'UDF_DatesConCat' MODULE_NAME 'CinemaUF';


DECLARE EXTERNAL FUNCTION UDF_DAYOFMONTH
    DATE
    RETURNS INTEGER BY VALUE
    ENTRY_POINT 'UDF_DAYOFMONTH' MODULE_NAME 'CinemaUF';


DECLARE EXTERNAL FUNCTION UDF_DAYOFYEAR
    DATE
    RETURNS INTEGER BY VALUE
    ENTRY_POINT 'UDF_DAYOFYEAR' MODULE_NAME 'CinemaUF';


DECLARE EXTERNAL FUNCTION UDF_DAY_OF_WEEK
    DATE
    RETURNS INTEGER BY VALUE
    ENTRY_POINT 'UDF_DayOfWeek' MODULE_NAME 'CinemaUF';


DECLARE EXTERNAL FUNCTION UDF_GETINTFROMSTR
    CSTRING(100)
    RETURNS INTEGER BY VALUE
    ENTRY_POINT 'UDF_GetIntFromStr' MODULE_NAME 'CinemaUF';


DECLARE EXTERNAL FUNCTION UDF_GETNTHWORD
    CSTRING(1024),
    INTEGER
    RETURNS CSTRING(1024) FREE_IT
    ENTRY_POINT 'UDF_GETNTHWORD' MODULE_NAME 'CinemaUF';


DECLARE EXTERNAL FUNCTION UDF_GETWORDBYNUM
    CSTRING(1024),
    CSTRING(100),
    INTEGER
    RETURNS CSTRING(1024) FREE_IT
    ENTRY_POINT 'UDF_GETWORDBYNUM' MODULE_NAME 'CinemaUF';


DECLARE EXTERNAL FUNCTION UDF_GETWORDCOUNT
    CSTRING(1024)
    RETURNS INTEGER BY VALUE
    ENTRY_POINT 'UDF_GETWORDCOUNT' MODULE_NAME 'CinemaUF';


DECLARE EXTERNAL FUNCTION UDF_MONTH
    DATE
    RETURNS INTEGER BY VALUE
    ENTRY_POINT 'UDF_MONTH' MODULE_NAME 'CinemaUF';


DECLARE EXTERNAL FUNCTION UDF_PARITY
    INTEGER
    RETURNS INTEGER BY VALUE
    ENTRY_POINT 'UDF_Parity' MODULE_NAME 'CinemaUF';


DECLARE EXTERNAL FUNCTION UDF_QUARTER
    DATE
    RETURNS INTEGER BY VALUE
    ENTRY_POINT 'UDF_QUARTER' MODULE_NAME 'CinemaUF';


DECLARE EXTERNAL FUNCTION UDF_REALPHONE
    CSTRING(50)
    RETURNS CSTRING(50) FREE_IT
    ENTRY_POINT 'UDF_GETCLEARPHONE' MODULE_NAME 'CinemaUF';


DECLARE EXTERNAL FUNCTION UDF_REPLACESTRING
    CSTRING(1024),
    CSTRING(255),
    CSTRING(255),
    INTEGER
    RETURNS CSTRING(1024) FREE_IT
    ENTRY_POINT 'UDF_REPLACESTRING' MODULE_NAME 'CinemaUF';


DECLARE EXTERNAL FUNCTION UDF_ROUNDTO
    DOUBLE PRECISION,
    DOUBLE PRECISION,
    INTEGER
    RETURNS DOUBLE PRECISION BY VALUE
    ENTRY_POINT 'UDF_ROUNDTO' MODULE_NAME 'CinemaUF';


DECLARE EXTERNAL FUNCTION UDF_STRCOPY
    CSTRING(1024),
    INTEGER,
    INTEGER
    RETURNS CSTRING(1024) FREE_IT
    ENTRY_POINT 'UDF_STRCOPY' MODULE_NAME 'CinemaUF';


DECLARE EXTERNAL FUNCTION UDF_STRLENGTH
    CSTRING(1024)
    RETURNS INTEGER BY VALUE
    ENTRY_POINT 'UDF_STRLENGTH' MODULE_NAME 'CinemaUF';


DECLARE EXTERNAL FUNCTION UDF_STRPOS
    CSTRING(1024),
    CSTRING(1024)
    RETURNS INTEGER BY VALUE
    ENTRY_POINT 'UDF_STRPOS' MODULE_NAME 'CinemaUF';


DECLARE EXTERNAL FUNCTION UDF_STRTOBLOB
    CSTRING(1024),
    BLOB
    RETURNS PARAMETER 2
    ENTRY_POINT 'UDF_STRTOBLOB' MODULE_NAME 'CinemaUF';


DECLARE EXTERNAL FUNCTION UDF_TRUNC_DATE
    DATE
    RETURNS DATE
    ENTRY_POINT 'UDF_TRUNCDATE' MODULE_NAME 'CinemaUF';


DECLARE EXTERNAL FUNCTION UDF_TRUNC_TIME
    DATE
    RETURNS DATE
    ENTRY_POINT 'UDF_TRUNCTIME' MODULE_NAME 'CinemaUF';


DECLARE EXTERNAL FUNCTION UDF_VERSION

    RETURNS CSTRING(20) FREE_IT
    ENTRY_POINT 'UDF_VERSION' MODULE_NAME 'CinemaUF';


DECLARE EXTERNAL FUNCTION UDF_YEAR
    DATE
    RETURNS INTEGER BY VALUE
    ENTRY_POINT 'UDF_YEAR' MODULE_NAME 'CinemaUF';


DECLARE EXTERNAL FUNCTION WINAPI_CHARLOWER
    CSTRING(250)
    RETURNS CSTRING(250) FREE_IT
    ENTRY_POINT 'IB_ANSILOWERCASE' MODULE_NAME 'CinemaUF';


DECLARE EXTERNAL FUNCTION WINAPI_CHARUPPER
    CSTRING(250)
    RETURNS CSTRING(250) FREE_IT
    ENTRY_POINT 'IB_ANSIUPPERCASE' MODULE_NAME 'CinemaUF';




/******************************************************************************/
/***                                Domains                                 ***/
/******************************************************************************/

CREATE DOMAIN D_DATE AS
DATE;

CREATE DOMAIN D_DATETIME AS
TIMESTAMP;

CREATE DOMAIN D_DOUBLE AS
DOUBLE PRECISION;

CREATE DOMAIN D_GUIID AS
CHAR(16) CHARACTER SET OCTETS;

CREATE DOMAIN D_INT64 AS
BIGINT;

CREATE DOMAIN D_INTEGER AS
INTEGER;

CREATE DOMAIN D_MEMO AS
BLOB SUB_TYPE 0 SEGMENT SIZE 100;

CREATE DOMAIN D_NUMERIC10_10 AS
NUMERIC(10,10);

CREATE DOMAIN D_NUMERIC15_15 AS
NUMERIC(15,15);

CREATE DOMAIN D_NUMERIC15_2 AS
NUMERIC(15,2);

CREATE DOMAIN D_SMALLINT AS
SMALLINT;

CREATE DOMAIN D_STRING_10 AS
VARCHAR(10);

CREATE DOMAIN D_STRING_100 AS
VARCHAR(100);

CREATE DOMAIN D_STRING_15 AS
VARCHAR(15);

CREATE DOMAIN D_STRING_25 AS
VARCHAR(25);

CREATE DOMAIN D_STRING_255 AS
VARCHAR(255);

CREATE DOMAIN D_STRING_5 AS
VARCHAR(5);

CREATE DOMAIN D_STRING_50 AS
VARCHAR(50);

CREATE DOMAIN D_STRING_550 AS
VARCHAR(500);

CREATE DOMAIN D_SYS_NAMES AS
CHAR(31);

CREATE DOMAIN D_TIME AS
TIME;



/******************************************************************************/
/***                               Generators                               ***/
/******************************************************************************/

CREATE GENERATOR DLV_ADDRESSES_ID_GEN;
CREATE GENERATOR DLV_ADVERT_ID_GEN;
CREATE GENERATOR DLV_BLACKLIST_ID_GEN;
CREATE GENERATOR DLV_CAR_LIST_ID_GEN;
CREATE GENERATOR DLV_CLIENTS_ID_GEN;
CREATE GENERATOR DLV_EMAIL_CENTER_ID_GEN;
CREATE GENERATOR DLV_EMAIL_LOCAL_ID_GEN;
CREATE GENERATOR DLV_MNAME_ID_GEN;
CREATE GENERATOR DLV_NAME_ID_GEN;
CREATE GENERATOR DLV_OPTION_RESTAURANTS_ID_GEN;
CREATE GENERATOR DLV_ORDERREGULATIONS_ID_GEN;
CREATE GENERATOR DLV_ORDERS_ID_GEN;
CREATE GENERATOR DLV_ORDER_NUM_GEN;
CREATE GENERATOR DLV_ORDER_REGULATION_ID_GEN;
CREATE GENERATOR DLV_ORGANIZATIONS_ID_GEN;
CREATE GENERATOR DLV_PERSONAL_ID_GEN;
CREATE GENERATOR DLV_PHONE_NUMBER_ID_GEN;
CREATE GENERATOR DLV_RESTAURANTS_ID_GEN;
CREATE GENERATOR DLV_STREET_ID_GEN;
CREATE GENERATOR DLV_SUBWAY_ID_GEN;
CREATE GENERATOR DLV_TOWNTYPE_ID_GEN;
CREATE GENERATOR DLV_ZIP_DETAIL_ID_GEN;
CREATE GENERATOR DLV_ZIP_ID_GEN;
CREATE GENERATOR DLV_ZONE_DETAIL_ID_GEN;
CREATE GENERATOR DLV_ZONE_ID_GEN;
CREATE GENERATOR GEN_DLV_CCEVENTS_ID;
CREATE GENERATOR GEN_DLV_CCLOG_ID;
CREATE GENERATOR GEN_DLV_SMS_ID;


/******************************************************************************/
/***                           Stored Procedures                            ***/
/******************************************************************************/

CREATE PROCEDURE SP_GEN_DLV_CCLOG_ID
RETURNS (
    ID INTEGER)
AS
BEGIN
  SUSPEND;
END;







/******************************************************************************/
/***                                 Tables                                 ***/
/******************************************************************************/



CREATE TABLE DLV_ADDRESSES (
    ADDRESS_ID     D_INT64 NOT NULL,
    HOUSE          D_STRING_10,
    BUILDING       D_STRING_15,
    FLAT           D_STRING_10,
    "FLOOR"        D_STRING_5,
    ENTRY          D_STRING_15,
    ADV_INFO       D_MEMO,
    IS_MAIN        D_SMALLINT,
    CLIENT_ID      D_INT64,
    STREET_ID      D_INT64,
    ZONE_ID        D_INT64,
    DELETED        D_SMALLINT DEFAULT 0,
    SUBWAY_ID      D_INT64 DEFAULT 0,
    CODE_ENTRY     D_STRING_25,
    ORDERS_COUNT   D_INTEGER,
    PHONES_ID      D_STRING_255,
    ZIP            D_INTEGER,
    RESTAURANT_ID  D_INTEGER,
    TYPE_ID        D_INTEGER,
    TYPE_NAME      D_STRING_25
);


CREATE TABLE DLV_ADVERT (
    ADVERT_ID      D_INT64 NOT NULL,
    ADVERT_NAME    D_STRING_100,
    DELETED        D_SMALLINT DEFAULT 0,
    RESTAURANT_ID  D_INTEGER
);


CREATE TABLE DLV_BLACKLIST (
    BL_ID          D_DOUBLE NOT NULL,
    PERSONAL_NAME  D_STRING_100,
    CLIENT_ID      D_INT64,
    ORDER_ID       D_INT64,
    DATE_INCLUDE   D_DATETIME,
    COUSE          D_STRING_100,
    DELETED        D_SMALLINT,
    RKORDER_ID     D_INT64
);


CREATE TABLE DLV_CAR_LIST (
    CAR_ID      D_INT64 NOT NULL,
    CAR_TYPE    D_STRING_50,
    CAR_NUMBER  D_STRING_25,
    DELETED     D_SMALLINT DEFAULT 0,
    CAR_NAME    D_STRING_50
);


CREATE TABLE DLV_CCEVENTS (
    ID              D_INT64 NOT NULL,
    ORDER_ID        D_INT64,
    RESTAUTRANT_ID  D_INTEGER,
    EVENT_TYPE      D_SMALLINT,
    CREATETIME      D_DATETIME,
    DONETIME        D_DATETIME,
    PACKET          D_MEMO,
    ANY_ID          D_INT64,
    ANY_NAME        D_STRING_100,
    EXECUTE_TIME    D_DATETIME
);


CREATE TABLE DLV_CCLOG (
    ID          D_INT64 NOT NULL,
    DT          D_DATETIME,
    RID         D_INTEGER,
    OPERATION   D_STRING_50,
    ERROR       D_INTEGER,
    ERROR_TEXT  D_STRING_255,
    ORDER_ID    D_INT64,
    ORDER_NUM   D_STRING_15,
    RK7VISIT    D_INTEGER,
    FRK7VISIT   D_INTEGER
);


CREATE TABLE DLV_CLIENT_CARDS (
    CLIENT_ID  D_INT64 NOT NULL,
    CARD       D_STRING_100 NOT NULL,
    DELETED    D_SMALLINT DEFAULT 0
);


CREATE TABLE DLV_CLIENT_EMAILS (
    EMAIL_ID   D_INT64 NOT NULL,
    CLIENT_ID  D_INT64 NOT NULL,
    EMAIL      D_STRING_50,
    DELETED    D_SMALLINT DEFAULT 0,
    TYPE_ID    D_INTEGER,
    TYPE_NAME  D_STRING_25
);


CREATE TABLE DLV_CLIENT_ORGANIZATIONS (
    COMPANY_ID   D_INT64 NOT NULL,
    CLIENT_ID    D_INT64 NOT NULL,
    DELETED      D_SMALLINT DEFAULT 0,
    DATE_INSERT  D_DATETIME DEFAULT 'now'
);


CREATE TABLE DLV_CLIENT_PHONES (
    PHONE_NUMBER_ID  D_INT64 NOT NULL,
    CLIENT_ID        D_INT64 NOT NULL,
    PHONE_NUMBER     D_STRING_50,
    DELETED          D_SMALLINT DEFAULT 0,
    ORDERS_COUNT     D_INTEGER,
    REAL_PN          D_STRING_50,
    TYPE_ID          D_INTEGER,
    TYPE_NAME        D_STRING_25
);


CREATE TABLE DLV_CLIENTS (
    CLIENT_ID     D_INT64 NOT NULL,
    F_NAME        D_STRING_50,
    L_NAME        D_STRING_50,
    M_NAME        D_STRING_50,
    BIRTHDAY      D_DATE,
    DELETED       D_INTEGER DEFAULT 0,
    VIP_FLAG      D_SMALLINT DEFAULT 0,
    REG_DATE      D_DATETIME,
    ADVERT_ID     D_INT64,
    ORDERS_COUNT  D_INTEGER,
    ADV_INFO      D_MEMO,
    PHOTO         D_MEMO,
    FULL_DATA     D_MEMO
);


CREATE TABLE DLV_COUNTRIES (
    COUNTRY_ID       D_INT64 NOT NULL,
    COUNTRY_NAME     D_STRING_50,
    COUNTRY_ALTNAME  D_STRING_25,
    DELETED          D_SMALLINT DEFAULT 0
);


CREATE TABLE DLV_EMAIL_CENTER (
    WORK_ID        D_INT64 NOT NULL,
    WORK_DT        D_DATETIME,
    RESTAURANT_ID  D_INTEGER,
    SEND_ERROR     D_INTEGER,
    GET_ERROR      D_INTEGER,
    RESEND_DT      D_DATETIME,
    SEND_COUNT     D_INTEGER,
    PACKAGE        D_MEMO,
    ORDERS         D_STRING_550,
    OK_ORDERS      D_STRING_550
);


CREATE TABLE DLV_EMAIL_LOCAL (
    WORK_ID        D_INT64 NOT NULL,
    WORK_DT        D_DATETIME,
    RESTAURANT_ID  D_INTEGER NOT NULL,
    SEND_ERROR     D_INTEGER,
    GET_ERROR      D_INTEGER,
    RESEND_DT      D_DATETIME,
    SEND_COUNT     D_INTEGER,
    PACKAGE        D_MEMO,
    ORDERS         D_STRING_550,
    OK_ORDERS      D_STRING_550
);


CREATE TABLE DLV_EXPEDITORS_WORK (
    ORDER_ID        D_INT64 NOT NULL,
    EXPEDITOR_ID    D_INT64,
    SEND_TIME       D_DATETIME,
    RETURN_TIME     D_DATETIME,
    EXPEDITOR_NAME  D_STRING_50,
    ROUTE           D_GUIID
);


CREATE TABLE DLV_MNAMES (
    MNAME_ID  D_INTEGER NOT NULL,
    M_NAME    D_STRING_100
);


CREATE TABLE DLV_NAMES (
    NAME_ID  D_INTEGER NOT NULL,
    F_NAME   D_STRING_100
);


CREATE TABLE DLV_OPTION_RESTAURANTS (
    OPTION_REST_ID        D_INTEGER NOT NULL,
    RESTAURANT_ID         D_INTEGER,
    TIME_BEGIN_WORK       D_TIME,
    TIME_END_WORK         D_TIME,
    DELETED               D_SMALLINT DEFAULT 0,
    DAYS_OF_WEEK          D_STRING_15,
    RESEND_RESTAURANT_ID  D_INTEGER
);


CREATE TABLE DLV_ORDER_REGULATION (
    REGULATIN_ID               D_INTEGER NOT NULL,
    DAYS_OF_WEEK               D_STRING_15,
    TIME_FROM                  D_TIME,
    MIN_SUM_FOR_FREE_DELIVERY  D_DOUBLE,
    MIN_SUM_FOR_TAKE           D_DOUBLE,
    SUM_FOR_FREE_DELIVERY      D_DOUBLE,
    SUM_FOR_SALE               D_DOUBLE,
    GLOBAL_DISCOUNT_ID         D_INTEGER,
    DELETED                    D_SMALLINT DEFAULT 0,
    DATE_BEGIN                 D_DATETIME,
    DATE_END                   D_DATETIME,
    DELIVERY_ITEM_ID           D_INTEGER,
    COUNT_ORDERS               D_INTEGER,
    EACH_FLAG                  D_SMALLINT DEFAULT 0,
    OC_DISCOUNT_ID             D_INTEGER
);


CREATE TABLE DLV_ORDER_SOURCES (
    SOURCES_ID   D_INTEGER NOT NULL,
    SOURCE_NAME  D_STRING_100,
    DELETED      D_SMALLINT default 0
);


CREATE TABLE DLV_ORDERS (
    ORDER_ID             D_INT64 NOT NULL,
    GUEST_ID             D_INT64,
    ADDRESS_ID           D_INT64,
    APPLY_TIME           D_DATETIME,
    WORK_TIME            D_DATETIME,
    STATUS               D_SMALLINT,
    ORDER_SUMM           D_DOUBLE,
    PAYED_SUMM           D_DOUBLE,
    EXPEDITOR_ID         D_INT64,
    EXPEDITOR_NAME       D_STRING_50,
    WAIT_DELIVERY_TIME   D_DATETIME,
    REAL_DELIVERY_TIME   D_DATETIME,
    WAY_TIME             D_DATETIME,
    DELETED              D_SMALLINT DEFAULT 0,
    DISCOUNT             D_DOUBLE,
    PRODUCTION_TIME      D_DATETIME,
    USE_IN_CALCULATIONS  D_INTEGER DEFAULT 1,
    DLV_TYPE             D_SMALLINT DEFAULT 0,
    SUMM_WCD             D_DOUBLE,
    DATE_INSERT          D_DATETIME,
    DATE_MODIFY          D_DATETIME,
    EMAIL_CENTER         D_INTEGER,
    EMAIL_LOCAL          D_INTEGER,
    REGULATION_ID        D_INTEGER,
    OPERATOR_ID          D_INT64,
    OPERATOR_NAME        D_STRING_50,
    RESTAURANT_ID        D_INTEGER,
    SEND_TIME            D_DATETIME,
    SOURCE_ID            D_SMALLINT,
    INVOICE_NUMBER       D_INTEGER,
    CAR_ID               D_INTEGER,
    ADV_INFO             D_MEMO,
    ORDER_NUM            D_STRING_15 NOT NULL,
    TABLE_NAME           D_STRING_5,
    CCORDERAPPLY         D_SMALLINT DEFAULT 1,
    GET_TIME             D_DATETIME,
    CENTRE_PACKS         D_STRING_100,
    LOCAL_PACKS          D_STRING_100,
    CALCSENDEXP          D_DATETIME,
    FULL_DATA            D_MEMO,
    RK7VISIT             D_INTEGER,
    RK7IDENT             D_INTEGER,
    RK7OTYPE             D_INTEGER,
    ORDER_VERSION        D_INTEGER,
    FRK7VISIT            D_INTEGER,
    FORDER_VERSION       D_INTEGER,
    RK7ORDERCRC32        D_INTEGER,
    RK7GUID              D_STRING_50,
    SMSPHONEID           D_INT64
);


CREATE TABLE DLV_ORGANIZATIONS (
    COMPANY_ID    D_INTEGER NOT NULL,
    COMPANY_NAME  D_STRING_100,
    INN           D_STRING_25,
    ADDRESS_UR    D_STRING_255,
    ADDRESS_FIZ   D_STRING_255,
    DELETED       D_SMALLINT DEFAULT 0,
    BANK          D_STRING_100,
    RS            D_STRING_25,
    KS            D_STRING_25,
    BIK           D_STRING_15,
    OKPO          D_STRING_10,
    ADV_INFO_1    D_STRING_255,
    ADV_INFO_2    D_STRING_255,
    ADV_INFO_3    D_STRING_255,
    KPP           D_STRING_25
);


CREATE TABLE DLV_PERSONAL_GROUP (
    GROUP_ID       D_INT64 NOT NULL,
    GROUP_NAME     D_STRING_50,
    RK_GROUP_NAME  D_STRING_50,
    RK_GROUP_DISH  D_STRING_5
);


CREATE TABLE DLV_PERSONALS (
    PERSONAL_ID     D_INT64 NOT NULL,
    PERSONAL_GROUP  D_INT64,
    PERSONAL_NAME   D_STRING_50,
    RK_PERSONAL_ID  D_INTEGER,
    PERSONAL_PASS   D_STRING_50,
    DELETED         D_SMALLINT DEFAULT 0,
    CAR_ID          D_INTEGER,
    IN_DLV          D_SMALLINT DEFAULT 1,
    PHONE           D_STRING_25,
    RK_PASS         D_STRING_255,
    PERSONAL_GUID   D_GUIID,
    REST_AVAILABLE  D_STRING_255
);


CREATE TABLE DLV_RESTAURANTS (
    RESTAURANT_ID    D_INTEGER NOT NULL,
    RESTAURANT_NAME  D_STRING_100,
    E_MAIL           D_STRING_100,
    DELETED          D_INTEGER DEFAULT 0,
    WORKING          D_SMALLINT DEFAULT 1,
    RBO_ID           D_INTEGER,
    PDS_ID           D_INTEGER,
    DEFCOOK_TIME     D_DATETIME,
    DEFWAY_TIME      D_DATETIME,
    DLVBASE          D_STRING_100
);


CREATE TABLE DLV_SMS (
    SMS_ID         D_INT64 NOT NULL,
    SMS_CREATE     D_DATETIME,
    SMS_SENDED     D_DATETIME,
    SMS_DELIVERED  D_DATETIME,
    FOR_STATUS     D_SMALLINT,
    ORDER_ID       D_INT64,
    ORDER_GUID     D_STRING_50,
    CLIENT_ID      D_INT64,
    PHONENUMBER    D_STRING_25,
    SMS_TEXT       D_STRING_255
);


CREATE TABLE DLV_STREETS (
    STREET_ID    D_INT64 NOT NULL,
    STREET_NAME  D_STRING_100,
    F_USE        D_SMALLINT DEFAULT 1,
    DELETED      D_SMALLINT DEFAULT 0,
    TOWN_ID      D_INT64
);


CREATE TABLE DLV_SUBWAY_STATIONS (
    SUBWAY_ID  D_INT64 NOT NULL,
    NAME       D_STRING_50,
    DELETED    D_SMALLINT DEFAULT 0,
    TOWN_ID    D_INT64
);


CREATE TABLE DLV_TOWNS (
    TOWN_ID       D_INT64 NOT NULL,
    TOWN_NAME     D_STRING_50,
    TOWN_ALTNAME  D_STRING_25,
    TOWN_TYPE_ID  D_INTEGER,
    DELETED       D_SMALLINT DEFAULT 0,
    COUNTRY_ID    D_INT64
);


CREATE TABLE DLV_TOWNTYPE (
    TOWNTYPE_ID  D_INTEGER NOT NULL,
    TT_NAME      D_STRING_25,
    TT_ALTNAME   D_STRING_10,
    DELETED      D_SMALLINT DEFAULT 0
);


CREATE TABLE DLV_ZIP_DETAILS (
    ZIP_DETAIL_ID  D_INT64 NOT NULL,
    ZIP_ID         D_INT64 NOT NULL,
    HOUSE_ORDER    D_SMALLINT DEFAULT 0,
    MIN_HOUSE      D_INTEGER,
    MAX_HOUSE      D_INTEGER,
    DELETED        D_SMALLINT DEFAULT 0,
    STREET_ID      D_INT64
);


CREATE TABLE DLV_ZIPS (
    ZIP_ID   D_INT64 NOT NULL,
    ZIPCODE  D_STRING_15,
    DELETED  D_SMALLINT DEFAULT 0
);


CREATE TABLE DLV_ZONE_DETAILS (
    ZONE_DETAIL_ID  D_INT64 NOT NULL,
    ZONE_ID         D_INTEGER NOT NULL,
    HOUSE_ORDER     D_SMALLINT DEFAULT 0,
    MIN_HOUSE       D_INTEGER,
    MAX_HOUSE       D_INTEGER,
    DELETED         D_SMALLINT DEFAULT 0,
    STREET_ID       D_INT64
);


CREATE TABLE DLV_ZONES (
    ZONE_ID        D_INT64 NOT NULL,
    ZONE_NAME      D_STRING_100,
    DELETED        D_SMALLINT DEFAULT 0,
    RESTAURANT_ID  D_INTEGER
);




/******************************************************************************/
/***                              Primary Keys                              ***/
/******************************************************************************/

ALTER TABLE DLV_ADDRESSES ADD CONSTRAINT PK_DLV_ADDRESSES PRIMARY KEY (ADDRESS_ID);
ALTER TABLE DLV_ADVERT ADD CONSTRAINT PK_DLV_ADVERT PRIMARY KEY (ADVERT_ID);
ALTER TABLE DLV_BLACKLIST ADD CONSTRAINT PK_DLV_BLACKLIST PRIMARY KEY (BL_ID);
ALTER TABLE DLV_CAR_LIST ADD CONSTRAINT PK_DLV_CAR_LIST PRIMARY KEY (CAR_ID);
ALTER TABLE DLV_CCEVENTS ADD CONSTRAINT PK_DLV_CCEVENTS PRIMARY KEY (ID);
ALTER TABLE DLV_CCLOG ADD CONSTRAINT PK_DLV_CCLOG PRIMARY KEY (ID);
ALTER TABLE DLV_CLIENTS ADD CONSTRAINT PK_DLV_CLIENTS PRIMARY KEY (CLIENT_ID);
ALTER TABLE DLV_CLIENT_PHONES ADD CONSTRAINT PK_DLV_CLIENT_PHONES PRIMARY KEY (PHONE_NUMBER_ID);
ALTER TABLE DLV_COUNTRIES ADD CONSTRAINT PK_DLV_COUNTRIES PRIMARY KEY (COUNTRY_ID);
ALTER TABLE DLV_EMAIL_CENTER ADD CONSTRAINT PK_DLV_EMAIL_CENTER PRIMARY KEY (WORK_ID);
ALTER TABLE DLV_EMAIL_LOCAL ADD CONSTRAINT PK_DLV_EMAIL_LOCAL PRIMARY KEY (WORK_ID, RESTAURANT_ID);
ALTER TABLE DLV_EXPEDITORS_WORK ADD CONSTRAINT PK_DLV_EXPEDITORS_WORK PRIMARY KEY (ORDER_ID);
ALTER TABLE DLV_MNAMES ADD CONSTRAINT PK_DLV_MNAMES PRIMARY KEY (MNAME_ID);
ALTER TABLE DLV_NAMES ADD CONSTRAINT PK_DLV_NAMES PRIMARY KEY (NAME_ID);
ALTER TABLE DLV_OPTION_RESTAURANTS ADD CONSTRAINT PK_DLV_OPTION_RESTAURANTS PRIMARY KEY (OPTION_REST_ID);
ALTER TABLE DLV_ORDERS ADD CONSTRAINT PK_DLV_ORDERS PRIMARY KEY (ORDER_ID);
ALTER TABLE DLV_ORDER_REGULATION ADD CONSTRAINT PK_DLV_ORDER_REGULATION PRIMARY KEY (REGULATIN_ID);
ALTER TABLE DLV_ORDER_SOURCES ADD CONSTRAINT PK_ORDER_SOURCES PRIMARY KEY (SOURCES_ID);
ALTER TABLE DLV_ORGANIZATIONS ADD CONSTRAINT PK_DLV_ORGANIZATIONS PRIMARY KEY (COMPANY_ID);
ALTER TABLE DLV_PERSONALS ADD CONSTRAINT PK_DLV_PERSONALS PRIMARY KEY (PERSONAL_ID);
ALTER TABLE DLV_PERSONAL_GROUP ADD CONSTRAINT PK_DLV_PERSONAL_GROUP PRIMARY KEY (GROUP_ID);
ALTER TABLE DLV_RESTAURANTS ADD CONSTRAINT PK_DLV_RESTAURANTS PRIMARY KEY (RESTAURANT_ID);
ALTER TABLE DLV_SMS ADD CONSTRAINT PK_DLV_SMS PRIMARY KEY (SMS_ID);
ALTER TABLE DLV_STREETS ADD CONSTRAINT PK_DLV_STREETS PRIMARY KEY (STREET_ID);
ALTER TABLE DLV_SUBWAY_STATIONS ADD CONSTRAINT PK_DLV_SUBWAY_STATIONS PRIMARY KEY (SUBWAY_ID);
ALTER TABLE DLV_TOWNS ADD CONSTRAINT PK_DLV_TOWNS PRIMARY KEY (TOWN_ID);
ALTER TABLE DLV_TOWNTYPE ADD CONSTRAINT PK_DLV_TOWNTYPE PRIMARY KEY (TOWNTYPE_ID);
ALTER TABLE DLV_ZIPS ADD CONSTRAINT PK_DLV_ZIPS PRIMARY KEY (ZIP_ID);
ALTER TABLE DLV_ZIP_DETAILS ADD CONSTRAINT PK_DLV_ZIP_DETAILS PRIMARY KEY (ZIP_DETAIL_ID);
ALTER TABLE DLV_ZONES ADD CONSTRAINT PK_DLV_ZONES PRIMARY KEY (ZONE_ID);
ALTER TABLE DLV_ZONE_DETAILS ADD CONSTRAINT PK_DLV_ZONE_DETAILS PRIMARY KEY (ZONE_DETAIL_ID);


/******************************************************************************/
/***                                Indices                                 ***/
/******************************************************************************/

CREATE INDEX DLV_ADDRESSES_IDX_BY_CLIENTS ON DLV_ADDRESSES (CLIENT_ID);
CREATE INDEX DLV_ADDRESSES_IDX_ZONE_ID ON DLV_ADDRESSES (ZONE_ID);
CREATE INDEX DLV_ADDRESSES_STREET_IDX ON DLV_ADDRESSES (STREET_ID);
CREATE INDEX DLV_ADDRESSES_ZIP ON DLV_ADDRESSES (ZIP);
CREATE INDEX DLV_BLACKLIST_IDX_CLIENT_ID ON DLV_BLACKLIST (CLIENT_ID);
CREATE INDEX DLV_CCEVENTS_CREATETIME ON DLV_CCEVENTS (CREATETIME);
CREATE INDEX DLV_CCEVENTS_DONE_TIME ON DLV_CCEVENTS (DONETIME);
CREATE INDEX DLV_CCEVENTS_IDX1 ON DLV_CCEVENTS (RESTAUTRANT_ID);
CREATE INDEX DLV_CCEVENTS_ORDER ON DLV_CCEVENTS (ORDER_ID);
CREATE INDEX DT_IDX ON DLV_CCLOG (DT);
CREATE INDEX DLV_CLIENTS_IDX_BIRTHDAY ON DLV_CLIENTS (BIRTHDAY);
CREATE INDEX DLV_CLIENTS_IDX_F_NAME ON DLV_CLIENTS (F_NAME);
CREATE INDEX DLV_CLIENTS_IDX_L_NAME ON DLV_CLIENTS (L_NAME);
CREATE INDEX DLV_CLIENTS_IDX_M_NAME ON DLV_CLIENTS (M_NAME);
CREATE INDEX DLV_CLIENTS_IDX_REG_DATE ON DLV_CLIENTS (REG_DATE);
CREATE INDEX DLV_CLIENT_PHONES_IDX_BY_CLIENT ON DLV_CLIENT_PHONES (CLIENT_ID);
CREATE INDEX DLV_CLIENT_PHONES_IDX_PN ON DLV_CLIENT_PHONES (PHONE_NUMBER);
CREATE INDEX DLV_CLIENT_PHONES_IDX_RPN ON DLV_CLIENT_PHONES (REAL_PN);
CREATE INDEX DLV_EMAIL_CENTER_DATETIME ON DLV_EMAIL_CENTER (WORK_DT);
CREATE INDEX DLV_EMAIL_CENTER_RESENDTIME ON DLV_EMAIL_CENTER (RESEND_DT);
CREATE INDEX DLV_EMAIL_LOCAL_DATETIME ON DLV_EMAIL_LOCAL (WORK_DT);
CREATE INDEX DLV_EMAIL_LOCAL_RESENDTIME ON DLV_EMAIL_LOCAL (RESEND_DT);
CREATE INDEX DLV_EXPEDITORS_WORK_IDX1 ON DLV_EXPEDITORS_WORK (SEND_TIME);
CREATE INDEX DLV_EXPEDITORS_WORK_IDX2 ON DLV_EXPEDITORS_WORK (RETURN_TIME);
CREATE INDEX DLV_EXPEDITORS_WORK_IDX_EN ON DLV_EXPEDITORS_WORK (EXPEDITOR_NAME);
CREATE INDEX IDX_DLV_EXPEDITORS_WORK ON DLV_EXPEDITORS_WORK (ORDER_ID, EXPEDITOR_ID);
CREATE DESCENDING INDEX DLV_ORDERS_DIDX_ORDER_ID ON DLV_ORDERS (ORDER_ID);
CREATE INDEX DLV_ORDERS_IDX_ADDRESS_ID ON DLV_ORDERS (ADDRESS_ID);
CREATE INDEX DLV_ORDERS_IDX_APPLY_TIME ON DLV_ORDERS (APPLY_TIME);
CREATE INDEX DLV_ORDERS_IDX_CALCSENDEXP ON DLV_ORDERS (CALCSENDEXP);
CREATE INDEX DLV_ORDERS_IDX_CLIENT ON DLV_ORDERS (GUEST_ID);
CREATE INDEX DLV_ORDERS_IDX_DATE_MODIFY ON DLV_ORDERS (DATE_MODIFY);
CREATE INDEX DLV_ORDERS_IDX_SEND_TIME ON DLV_ORDERS (SEND_TIME);
CREATE INDEX DLV_ORDERS_IDX_WAIT_DELIVERY_TI ON DLV_ORDERS (WAIT_DELIVERY_TIME);
CREATE INDEX DLV_ORDERS_IDX_WORK_TIME ON DLV_ORDERS (WORK_TIME);
CREATE INDEX DLV_SMS_IDX_CREATE ON DLV_SMS (SMS_CREATE);
CREATE INDEX DLV_SMS_IDX_DONE ON DLV_SMS (SMS_DELIVERED);
CREATE INDEX DLV_SMS_IDX_ORDER ON DLV_SMS (ORDER_ID);
CREATE INDEX DLV_SMS_IDX_SENDED ON DLV_SMS (SMS_SENDED);
CREATE INDEX DLV_STREETS_BY_NAME_IDX ON DLV_STREETS (STREET_NAME);
CREATE INDEX DLV_ZIP_DETAILS_IDX_STREET_ID ON DLV_ZIP_DETAILS (STREET_ID);
CREATE INDEX DLV_ZIP_DETAILS_IDX_ZONE_ID ON DLV_ZIP_DETAILS (ZIP_ID);
CREATE INDEX DLV_ZONE_DETAILS_IDX_STREET_ID ON DLV_ZONE_DETAILS (STREET_ID);
CREATE INDEX DLV_ZONE_DETAILS_IDX_ZONE_ID ON DLV_ZONE_DETAILS (ZONE_ID);


/******************************************************************************/
/***                                Triggers                                ***/
/******************************************************************************/



/******************************************************************************/
/***                          Triggers for tables                           ***/
/******************************************************************************/



/* Trigger: DLV_CCLOG_BI */
CREATE TRIGGER DLV_CCLOG_BI FOR DLV_CCLOG
ACTIVE BEFORE INSERT POSITION 0
as
begin
  if (new.id is null) then
    new.id = gen_id(gen_dlv_cclog_id,1);
end;




/******************************************************************************/
/***                           Stored Procedures                            ***/
/******************************************************************************/

ALTER PROCEDURE SP_GEN_DLV_CCLOG_ID
RETURNS (
    ID INTEGER)
AS
begin
  id = gen_id(gen_dlv_cclog_id, 1);
  suspend;
end;


