use `e-cashier`;
drop table if exists `e-cashier`.`entity_balance_control`;
drop table if exists `e-cashier`.`entity_card_limits`;
drop table if exists `e-cashier`.`entity_services_balance_log`;
drop table if exists `e-cashier`.`entity_services_balance`;
drop table if exists `e-cashier`.`entity`;


create table `entity`(
	`id` bigint(20) not null auto_increment,
    `name` varchar(255) not null,
	`agent_id` int(11) not null,
    `balance` decimal(10,2),
    index(`agent_id`),
    primary key(`id`),
    foreign key(`agent_id`) references `e-cashier`.`user`(`id`)
    ON DELETE CASCADE ON UPDATE RESTRICT
)Engine=Innodb, charset utf8mb4;

create table `entity_card_limits`(
	id bigint(20) not null auto_increment,
	`entity_id` bigint(20) not null,
	`card_number` varchar(50) not null,
	`limit` decimal(10,2),
	`by_month` decimal(10,2),
	`by_day` decimal(10,2),
    `password` varchar(255) not null,
    unique(`entity_id`, `card_number`),
    unique(`card_number`, `password`),
	primary key(id),
    index(`entity_id`),
	foreign key(`entity_id`) references `e-cashier`.`entity`(`id`)
    ON DELETE CASCADE ON UPDATE RESTRICT
)Engine=Innodb, charset utf8mb4;


create table `entity_services_balance`(
	id bigint(20) not null auto_increment,
    `balance` decimal(10,2) not null,
    `entity_id` bigint(20) not null,
    `offer_id` int(11) not null,
    index(`entity_id`),
    index(`offer_id`),
    primary key(id),
    foreign key(`entity_id`) references `e-cashier`.`entity`(`id`)
    ON DELETE CASCADE ON UPDATE RESTRICT,
	foreign key(`offer_id`) references `e-cashier`.`offer`(`id`)
    ON DELETE CASCADE ON UPDATE RESTRICT
    )Engine=Innodb, charset utf8mb4;
    
create table `e-cashier`.`entity_balance_control`(
	id bigint(20) not null auto_increment,
	`agent_id` int(11) not null,
    `entity_id` bigint(20) not null,
	`offer_id` int(11),
    `card_id` bigint(20),
    `terminal_id` int(6),
    `comment` text,
	`old_amount` decimal(10,2) not null,
    `date` datetime not null DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    index(`date`),
	index(`agent_id`),
    index(`entity_id`),
    index(`offer_id`),
    index(`card_id`),
    index(`terminal_id`),
    `amount` decimal(10,2) not null,
    `type` enum('in','out') not null,
    foreign key(`card_id`) references `e-cashier`.`entity_card_limits`(id)
    ON DELETE CASCADE ON UPDATE RESTRICT,
    foreign key(`offer_id`) references `e-cashier`.`offer`(`id`)
    ON DELETE CASCADE ON UPDATE RESTRICT,
	foreign key(`agent_id`) references `e-cashier`.`user`(`id`)
    ON DELETE CASCADE ON UPDATE RESTRICT,
	foreign key(`entity_id`) references `e-cashier`.`entity`(`id`)
    ON DELETE CASCADE ON UPDATE RESTRICT,
    primary key(id)
)Engine=Innodb, charset utf8mb4;

create table `e-cashier`.`entity_services_balance_log`(
	id bigint(20) not null auto_increment,
	`amount` decimal(10,2) not null,
    `old_amount` decimal(10,2) not null,
    `entity_services_balance_id` bigint(20) not null,
    `date` datetime not null DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    foreign key(`entity_services_balance_id`) references `e-cashier`.`entity_services_balance`(`id`)
    ON DELETE CASCADE ON UPDATE RESTRICT,
    primary key(`id`)
)Engine=Innodb, charset utf8mb4;

