/****** Object:  User [atb_tariff_api]    Script Date: 11.12.2023. 14:30:40 ******/
CREATE USER [atb_tariff_api] FOR LOGIN [atb_tariff_api] WITH DEFAULT_SCHEMA=[dbo]
GO
ALTER ROLE [db_owner] ADD MEMBER [atb_tariff_api]
GO
ALTER ROLE [db_datareader] ADD MEMBER [atb_tariff_api]
GO
ALTER ROLE [db_datawriter] ADD MEMBER [atb_tariff_api]
GO
/****** Object:  Table [dbo].[city]    Script Date: 11.12.2023. 14:30:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[city](
	[cty_id] [int] IDENTITY(1,1) NOT NULL,
	[cty_label] [nvarchar](50) NOT NULL,
	[cty_delete_date] [datetimeoffset](7) NULL,
 CONSTRAINT [PK_city] PRIMARY KEY CLUSTERED
(
	[cty_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[payment_currency]    Script Date: 11.12.2023. 14:30:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[payment_currency](
	[pcu_id] [int] IDENTITY(1,1) NOT NULL,
	[pcu_sign] [nvarchar](50) NOT NULL,
	[pcu_major] [nvarchar](50) NOT NULL,
	[pcu_minor] [nvarchar](50) NULL,
	[pcu_active] [bit] NOT NULL,
 CONSTRAINT [PK_payment_currency] PRIMARY KEY CLUSTERED
(
	[pcu_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[payment_method]    Script Date: 11.12.2023. 14:30:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[payment_method](
	[pme_id] [int] IDENTITY(1,1) NOT NULL,
	[pme_label] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_payment_method] PRIMARY KEY CLUSTERED
(
	[pme_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[payment_option]    Script Date: 11.12.2023. 14:30:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[payment_option](
	[pop_id] [int] IDENTITY(1,1) NOT NULL,
	[pop_label] [nvarchar](50) NOT NULL,
	[pop_payment_method_id] [int] NOT NULL,
	[pop_day_end_time] [time](7) NOT NULL,
	[pop_day_night_end_time] [time](7) NOT NULL,
	[pop_price_night] [float] NULL,
	[pop_min_time] [int] NULL,
	[pop_max_time] [int] NULL,
	[pop_min_price] [decimal](18, 0) NULL,
	[pop_carry_over] [bit] NULL,
	[pop_period_week_id] [int] NULL,
	[pop_currency_id] [int] NULL,
	[pop_daily_card_price] [int] NULL,
	[pop_city_id] [int] NULL,
	[pop_multi_hour_price] [float] NULL,
 CONSTRAINT [PK_payment_option] PRIMARY KEY CLUSTERED
(
	[pop_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[payment_rate]    Script Date: 11.12.2023. 14:30:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[payment_rate](
	[pra_id] [int] IDENTITY(1,1) NOT NULL,
	[pra_payment_option_id] [int] NOT NULL,
	[pra_payment_unit_id] [int] NOT NULL,
	[pra_price] [float] NOT NULL,
	[pra_currency_id] [int] NULL,
 CONSTRAINT [PK_payment_rate_1] PRIMARY KEY CLUSTERED
(
	[pra_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[payment_unit]    Script Date: 11.12.2023. 14:30:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[payment_unit](
	[pun_id] [int] IDENTITY(1,1) NOT NULL,
	[pun_label] [nvarchar](50) NOT NULL,
	[pun_duration] [int] NOT NULL,
 CONSTRAINT [PK_payment_unit] PRIMARY KEY CLUSTERED
(
	[pun_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[period_day_in_week]    Script Date: 11.12.2023. 14:30:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[period_day_in_week](
	[pdiw_id] [tinyint] IDENTITY(1,1) NOT NULL,
	[pdiw_label] [nvarchar](50) NOT NULL,
	[pdiw_index] [tinyint] NOT NULL,
	[pdiw_index_device] [tinyint] NOT NULL,
 CONSTRAINT [PK_period_day_in_week] PRIMARY KEY CLUSTERED
(
	[pdiw_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[period_exceptional_day_work_time]    Script Date: 11.12.2023. 14:30:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[period_exceptional_day_work_time](
	[pedwt_id] [int] IDENTITY(1,1) NOT NULL,
	[pedwt_period_exc_day_id] [int] NOT NULL,
	[pedwt_time_from] [time](7) NOT NULL,
	[pedwt_time_to] [time](7) NOT NULL,
	[pedwt_price] [float] NOT NULL,
	[pedwt_currency_id] [int] NULL,
 CONSTRAINT [PK_period_exceptional_day_work_time] PRIMARY KEY CLUSTERED
(
	[pedwt_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[period_exceptional_days]    Script Date: 11.12.2023. 14:30:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[period_exceptional_days](
	[ped_id] [int] IDENTITY(1,1) NOT NULL,
	[ped_label] [nvarchar](50) NOT NULL,
	[ped_date_start] [date] NOT NULL,
	[ped_date_end] [date] NOT NULL,
	[ped_period_special_day_id] [int] NOT NULL,
	[ped_year] [int] NULL,
	[ped_city_id] [int] NULL,
 CONSTRAINT [PK_period_exceptional_days] PRIMARY KEY CLUSTERED
(
	[ped_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[period_special_days]    Script Date: 11.12.2023. 14:30:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[period_special_days](
	[psd_id] [int] IDENTITY(1,1) NOT NULL,
	[psd_label] [nvarchar](50) NOT NULL,
	[psd_chargeable] [bit] NOT NULL,
	[psd_priority] [tinyint] NOT NULL,
 CONSTRAINT [PK_period_special_days] PRIMARY KEY CLUSTERED
(
	[psd_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[period_week]    Script Date: 11.12.2023. 14:30:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[period_week](
	[pwe_id] [int] IDENTITY(1,1) NOT NULL,
	[pwe_label] [nvarchar](150) NOT NULL,
	[pwe_period_year_id] [int] NOT NULL,
	[pwe_city_id] [int] NULL,
 CONSTRAINT [PK_period_week] PRIMARY KEY CLUSTERED
(
	[pwe_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[period_week_day]    Script Date: 11.12.2023. 14:30:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[period_week_day](
	[pwd_id] [int] IDENTITY(1,1) NOT NULL,
	[pwd_period_week_day_id] [int] NOT NULL,
	[pwd_period_day_in_week_id] [tinyint] NOT NULL,
	[pwd_time_from] [time](7) NOT NULL,
	[pwd_time_to] [time](7) NOT NULL,
	[pwd_ui_group] [int] NULL,
 CONSTRAINT [PK_period_week_day] PRIMARY KEY CLUSTERED
(
	[pwd_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[period_year]    Script Date: 11.12.2023. 14:30:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[period_year](
	[pye_id] [int] IDENTITY(1,1) NOT NULL,
	[pye_label] [nvarchar](50) NOT NULL,
	[pye_start_month] [int] NOT NULL,
	[pye_start_day] [int] NOT NULL,
	[pye_end_month] [int] NOT NULL,
	[pye_end_day] [int] NOT NULL,
	[pye_city_id] [int] NULL,
 CONSTRAINT [PK_period_year] PRIMARY KEY CLUSTERED
(
	[pye_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[payment_option]  WITH CHECK ADD  CONSTRAINT [FK_payment_option_payment_currency] FOREIGN KEY([pop_currency_id])
REFERENCES [dbo].[payment_currency] ([pcu_id])
GO
ALTER TABLE [dbo].[payment_option] CHECK CONSTRAINT [FK_payment_option_payment_currency]
GO
ALTER TABLE [dbo].[payment_option]  WITH CHECK ADD  CONSTRAINT [FK_payment_option_payment_method] FOREIGN KEY([pop_payment_method_id])
REFERENCES [dbo].[payment_method] ([pme_id])
GO
ALTER TABLE [dbo].[payment_option] CHECK CONSTRAINT [FK_payment_option_payment_method]
GO
ALTER TABLE [dbo].[payment_option]  WITH CHECK ADD  CONSTRAINT [FK_payment_option_period_week] FOREIGN KEY([pop_period_week_id])
REFERENCES [dbo].[period_week] ([pwe_id])
GO
ALTER TABLE [dbo].[payment_option] CHECK CONSTRAINT [FK_payment_option_period_week]
GO
ALTER TABLE [dbo].[payment_rate]  WITH CHECK ADD  CONSTRAINT [FK_payment_rate_payment_currency] FOREIGN KEY([pra_currency_id])
REFERENCES [dbo].[payment_currency] ([pcu_id])
GO
ALTER TABLE [dbo].[payment_rate] CHECK CONSTRAINT [FK_payment_rate_payment_currency]
GO
ALTER TABLE [dbo].[payment_rate]  WITH CHECK ADD  CONSTRAINT [FK_payment_rate_payment_option] FOREIGN KEY([pra_payment_option_id])
REFERENCES [dbo].[payment_option] ([pop_id])
GO
ALTER TABLE [dbo].[payment_rate] CHECK CONSTRAINT [FK_payment_rate_payment_option]
GO
ALTER TABLE [dbo].[payment_rate]  WITH CHECK ADD  CONSTRAINT [FK_payment_rate_payment_unit] FOREIGN KEY([pra_payment_unit_id])
REFERENCES [dbo].[payment_unit] ([pun_id])
GO
ALTER TABLE [dbo].[payment_rate] CHECK CONSTRAINT [FK_payment_rate_payment_unit]
GO
ALTER TABLE [dbo].[period_exceptional_day_work_time]  WITH CHECK ADD  CONSTRAINT [FK_period_exceptional_day_work_time_payment_currency] FOREIGN KEY([pedwt_currency_id])
REFERENCES [dbo].[payment_currency] ([pcu_id])
GO
ALTER TABLE [dbo].[period_exceptional_day_work_time] CHECK CONSTRAINT [FK_period_exceptional_day_work_time_payment_currency]
GO
ALTER TABLE [dbo].[period_exceptional_day_work_time]  WITH CHECK ADD  CONSTRAINT [FK_period_exceptional_day_work_time_period_exceptional_days] FOREIGN KEY([pedwt_period_exc_day_id])
REFERENCES [dbo].[period_exceptional_days] ([ped_id])
GO
ALTER TABLE [dbo].[period_exceptional_day_work_time] CHECK CONSTRAINT [FK_period_exceptional_day_work_time_period_exceptional_days]
GO
ALTER TABLE [dbo].[period_exceptional_days]  WITH CHECK ADD  CONSTRAINT [FK_period_exceptional_days_period_special_days] FOREIGN KEY([ped_period_special_day_id])
REFERENCES [dbo].[period_special_days] ([psd_id])
GO
ALTER TABLE [dbo].[period_exceptional_days] CHECK CONSTRAINT [FK_period_exceptional_days_period_special_days]
GO
ALTER TABLE [dbo].[period_week]  WITH CHECK ADD  CONSTRAINT [FK_period_week_period_year] FOREIGN KEY([pwe_period_year_id])
REFERENCES [dbo].[period_year] ([pye_id])
GO
ALTER TABLE [dbo].[period_week] CHECK CONSTRAINT [FK_period_week_period_year]
GO
ALTER TABLE [dbo].[period_week_day]  WITH CHECK ADD  CONSTRAINT [FK_period_week_day_period_day_in_week] FOREIGN KEY([pwd_period_day_in_week_id])
REFERENCES [dbo].[period_day_in_week] ([pdiw_id])
GO
ALTER TABLE [dbo].[period_week_day] CHECK CONSTRAINT [FK_period_week_day_period_day_in_week]
GO
ALTER TABLE [dbo].[period_week_day]  WITH CHECK ADD  CONSTRAINT [FK_period_week_day_period_week] FOREIGN KEY([pwd_period_week_day_id])
REFERENCES [dbo].[period_week] ([pwe_id])
GO
ALTER TABLE [dbo].[period_week_day] CHECK CONSTRAINT [FK_period_week_day_period_week]
GO