DROP TABLE IF EXISTS MADAXELLYANDEXRU__STAGING.groups;
DROP TABLE IF EXISTS MADAXELLYANDEXRU__STAGING.dialogs;
DROP TABLE IF EXISTS MADAXELLYANDEXRU__STAGING.users;


CREATE TABLE IF NOT EXISTS MADAXELLYANDEXRU__STAGING.users
(
	id 				int 		 	NOT NULL PRIMARY KEY,
	chat_name 		varchar(200) 	NOT NULL,
	registration_dt timestamp	 	NOT NULL,
	country 		varchar(200) 	NOT NULL,
	age 			int				NOT NULL
)
order by id
SEGMENTED BY hash(id) all nodes
;

CREATE TABLE IF NOT EXISTS MADAXELLYANDEXRU__STAGING.groups
(
	id					int 		 	NOT NULL PRIMARY KEY,
	admin_id			int 			REFERENCES users(id) NOT NULL,
	group_name			varchar(100) 	NOT NULL,
	registration_dt		timestamp	 	NOT NULL,
	is_private			BOOLEAN			NOT NULL
)
order by id, admin_id
PARTITION BY registration_dt::date
GROUP BY calendar_hierarchy_day(registration_dt::date, 3, 2)
/*SEGMENTED BY hash(id) all nodes*/
;

CREATE TABLE IF NOT EXISTS MADAXELLYANDEXRU__STAGING.dialogs
(
    message_id   	int 			NOT NULL PRIMARY KEY,
    message_ts   	timestamp	 	NOT NULL,
    message_from 	int 			REFERENCES users(id) NOT NULL,
    message_to   	int 			REFERENCES users(id) NOT NULL,
    message      	varchar(1000)	NOT NULL,
	message_group 	int
    -- message_type varchar(100)
)
order by message_id/*, message_from, message_ts*/
/*SEGMENTED BY hash(message_id) all nodes*/
PARTITION BY message_ts::DATE
GROUP BY calendar_hierarchy_day(message_ts::DATE, 3, 2)
;

COPY MADAXELLYANDEXRU__STAGING.users
FROM LOCAL 'D:\YandexDisk\Education\Yandex Практикум\DataEngineer\Sprint 6\s6-lessons-main\s6-lessons-main\data\users.csv'
DELIMITER ','
REJECTED DATA AS TABLE MADAXELLYANDEXRU__STAGING.users_rej;

COPY MADAXELLYANDEXRU__STAGING.groups
FROM LOCAL 'D:\YandexDisk\Education\Yandex Практикум\DataEngineer\Sprint 6\s6-lessons-main\s6-lessons-main\data\groups.csv'
DELIMITER ','
REJECTED DATA AS TABLE MADAXELLYANDEXRU__STAGING.groups_rej;

COPY MADAXELLYANDEXRU__STAGING.dialogs
FROM LOCAL 'D:\YandexDisk\Education\Yandex Практикум\DataEngineer\Sprint 6\s6-lessons-main\s6-lessons-main\data\dialogs.csv'
DELIMITER ','
REJECTED DATA AS TABLE MADAXELLYANDEXRU__STAGING.dialogs_rej;