/*
 *    This program is free software; you can redistribute it and/or modify
 *    it under the terms of the GNU General Public License as published by
 *    the Free Software Foundation; either version 2 of the License, or
 *    (at your option) any later version.
 *
 *    This program is distributed in the hope that it will be useful,
 *    but WITHOUT ANY WARRANTY; without even the implied warranty of
 *    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *    GNU General Public License for more details.
 *
 *    You should have received a copy of the GNU General Public License
 *    along with this program; if not, write to the Free Software
 *    Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
 */

/*
 *****************************************************************************
 *
 * Скрипт генерации структуры базы для хранения данных Stargazer-a
 *
 * Примечание.
 *      * dm_permission_flag. Представляет собой битовую маску - rw.
 *          r - чтение, w - изменение параметра.
 *          0 - дествие запрещено, 1 - действие разрешено
 *
 *      * dm_traff_type. Число определяющее тип подсчета трафика:
 *          0 - up - считается по upload
 *          1 - down - считается по download
 *          2 - max - считается по максимальному среди upload/download
 *          3 - up+down - считается по сумме upload и download
 *
 *      * dm_session_event_type. Указывает тип записи в логе о сессии.
 *        'c' - connect, 'd' - disconnect.
 *
 *****************************************************************************
 */

/*
 *  $Revision: 1.12 $
 *  $Date: 2009/08/20 14:58:43 $
 */


/*
 *****************************************************************************
 * -= Создание типов и доменов =-
 *****************************************************************************
 */

CREATE DOMAIN dm_name AS VARCHAR(32) NOT NULL;
CREATE DOMAIN dm_password AS VARCHAR(64) NOT NULL;
CREATE DOMAIN dm_permission_flag AS SMALLINT NOT NULL
    CHECK ( value BETWEEN 0 AND 3 );
CREATE DOMAIN dm_money AS NUMERIC(12, 4) NOT NULL DEFAULT 0;
CREATE DOMAIN dm_traff_type AS SMALLINT NOT NULL
    CHECK ( value BETWEEN 0 AND 3 );
CREATE DOMAIN dm_day AS SMALLINT NOT NULL
    CHECK ( value BETWEEN 0 AND 31 )
    DEFAULT 0;
CREATE DOMAIN dm_session_event_type AS CHAR(1) NOT NULL
    CHECK ( value = 'c' OR value = 'd' );

/*
 *****************************************************************************
 * -= Создание таблиц =-
 *****************************************************************************
 */

CREATE TABLE tb_info
(
    version INTEGER NOT NULL
);

CREATE TABLE tb_tariffs
(
    pk_tariff SERIAL PRIMARY KEY,
    name dm_name UNIQUE,
    fee dm_money,
    free dm_money,
    passive_cost dm_money,
    traff_type dm_traff_type
);

CREATE TABLE tb_tariffs_params
(
    pk_tariff_param SERIAL PRIMARY KEY,
    fk_tariff INTEGER NOT NULL,
    dir_num SMALLINT NOT NULL,
    price_day_a dm_money,
    price_day_b dm_money,
    price_night_a dm_money,
    price_night_b dm_money,
    threshold INTEGER NOT NULL,
    time_day_begins TIME NOT NULL,
    time_day_ends TIME NOT NULL,

    FOREIGN KEY (fk_tariff)
        REFERENCES tb_tariffs (pk_tariff)
        ON DELETE CASCADE
);

CREATE TABLE tb_corporations
(
    pk_corporation SERIAL PRIMARY KEY,
    name dm_name UNIQUE,
    cash dm_money
);

CREATE TABLE tb_detail_stats
(
    pk_detail_stat BIGSERIAL PRIMARY KEY,
    fk_user INTEGER NOT NULL,
    dir_num SMALLINT NOT NULL,
    ip INET NOT NULL,
    download BIGINT NOT NULL,
    upload BIGINT NOT NULL,
    cost dm_money,
    from_time TIMESTAMP NOT NULL,
    till_time TIMESTAMP NOT NULL,

    FOREIGN KEY (fk_user)
        REFERENCES tb_users (pk_user)
        ON DELETE CASCADE
);

CREATE TABLE tb_services
(
    pk_service SERIAL PRIMARY KEY,
    name dm_name UNIQUE,
    comment TEXT NOT NULL,
    cost dm_money,
    pay_day dm_day
);

CREATE TABLE tb_users_services
(
    pk_user_service SERIAL PRIMARY KEY,
    fk_user INTEGER NOT NULL,
    fk_service INTEGER NOT NULL,

    FOREIGN KEY (fk_user)
        REFERENCES tb_users (pk_user)
        ON DELETE CASCADE,
    FOREIGN KEY (fk_service)
        REFERENCES tb_services (pk_service)
);

CREATE TABLE tb_messages
(
    pk_message SERIAL PRIMARY KEY,
    fk_user INTEGER NOT NULL,
    ver SMALLINT NOT NULL,
    msg_type SMALLINT NOT NULL,
    last_send_time TIMESTAMP NOT NULL,
    creation_time TIMESTAMP NOT NULL,
    show_time INTEGER NOT NULL,
    repeat SMALLINT NOT NULL,
    repeat_period INTEGER NOT NULL,
    msg_text TEXT NOT NULL,

    FOREIGN KEY (fk_user)
        REFERENCES tb_users (pk_user)
        ON DELETE CASCADE
);

CREATE TABLE tb_stats_traffic
(
    pk_stat_traffic BIGSERIAL PRIMARY KEY,
    fk_user INTEGER NOT NULL,
    stats_date DATE NOT NULL,
    dir_num SMALLINT NOT NULL,
    download BIGINT NOT NULL,
    upload BIGINT NOT NULL,

    FOREIGN KEY (fk_user)
        REFERENCES tb_users (pk_user)
        ON DELETE CASCADE,
    UNIQUE (fk_user, stats_date, dir_num)
);

CREATE TABLE tb_users_data
(
    pk_user_data SERIAL PRIMARY KEY,
    fk_user INTEGER NOT NULL,
    num SMALLINT NOT NULL,
    data VARCHAR(256) NOT NULL,

    FOREIGN KEY (fk_user)
        REFERENCES tb_users (pk_user)
        ON DELETE CASCADE
);

CREATE TABLE tb_allowed_ip
(
    pk_allowed_ip SERIAL PRIMARY KEY,
    fk_user INTEGER NOT NULL,
    ip INET NOT NULL,

    FOREIGN KEY (fk_user)
        REFERENCES tb_users (pk_user)
        ON DELETE CASCADE
);

CREATE TABLE tb_sessions_log
(
    pk_session_log SERIAL PRIMARY KEY,
    fk_user INTEGER NOT NULL,
    event_time TIMESTAMP NOT NULL,
    event_type dm_session_event_type,
    ip INET NOT NULL,
    cash dm_money,

    FOREIGN KEY (fk_user)
        REFERENCES tb_users (pk_user)
        ON DELETE CASCADE
);

CREATE TABLE tb_sessions_data
(
    pk_session_data SERIAL PRIMARY KEY,
    fk_session_log INTEGER NOT NULL,
    dir_num SMALLINT NOT NULL,
    session_upload BIGINT NOT NULL,
    session_download BIGINT NOT NULL,
    month_upload BIGINT NOT NULL,
    month_download BIGINT NOT NULL,

    FOREIGN KEY (fk_session_log)
        REFERENCES tb_sessions_log (pk_session_log)
        ON DELETE CASCADE
);

CREATE TABLE tb_parameters
(
    pk_parameter SERIAL PRIMARY KEY,
    name dm_name UNIQUE
);

CREATE TABLE tb_params_log
(
    pk_param_log SERIAL PRIMARY KEY,
    fk_user INTEGER NOT NULL,
    fk_parameter INTEGER NOT NULL,
    fk_admin INTEGER NOT NULL,
    ip INET NOT NULL,
    event_time TIMESTAMP NOT NULL,
    from_val VARCHAR(256),
    to_val VARCHAR(256),
    comment TEXT,

    FOREIGN KEY (fk_user)
        REFERENCES tb_users (pk_user)
        ON DELETE CASCADE,
    FOREIGN KEY (fk_parameter)
        REFERENCES tb_parameters (pk_parameter),
    FOREIGN KEY (fk_admin)
        REFERENCES tb_admins (pk_admin)
        ON DELETE CASCADE
);

CREATE FUNCTION sp_set_user_data (_pk_user INTEGER,
                                  _num SMALLINT,
                                  _data VARCHAR(256))
RETURNS INTEGER
AS $$
BEGIN
    UPDATE tb_users_data SET
        data = _data
    WHERE fk_user = _pk_user AND num = _num;

    IF NOT FOUND THEN
        INSERT INTO tb_users_data
            (fk_user,
             num,
             data)
        VALUES
            (_pk_user,
             _num,
             _data);
    END IF;

    RETURN 1;
END;
$$ LANGUAGE plpgsql;

INSERT INTO tb_info
    (version)
VALUES
    (5);
