USE [user12] GO /****** Object: Table [dbo].[clients] Script Date: 19.12.2023 13:14:16 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[clients]( [id] [int] IDENTITY(1,1) NOT NULL, [type] [int] NOT NULL, [login] [nvarchar](50) NOT NULL, [password] [char](64) NOT NULL, [surname] [nvarchar](50) NULL, [name] [nvarchar](50) NULL, [patronymic] [nvarchar](50) NULL, [birthdate] [date] NULL, [passport_series] [int] NULL, [passport_number] [int] NULL, [phone] [char](16) NOT NULL, [email] [varchar](50) NOT NULL, [company_name] [varchar](50) NULL, [company_address] [varchar](100) NULL, [inn] [int] NOT NULL, [account] [int] NOT NULL, [bik] [int] NOT NULL, [hidden] [bit] NOT NULL, CONSTRAINT [PK_clients] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[employee_services] Script Date: 19.12.2023 13:14:16 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[employee_services]( [id] [int] IDENTITY(1,1) NOT NULL, [service_id] [int] NOT NULL, [user_id] [int] NOT NULL, CONSTRAINT [PK_employee_services] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[invoices] Script Date: 19.12.2023 13:14:16 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[invoices]( [id] [int] IDENTITY(1,1) NOT NULL, [client_id] [int] NOT NULL, [accountant_id] [int] NOT NULL, [order_id] [int] NOT NULL, CONSTRAINT [PK_invoice] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[login_attempts] Script Date: 19.12.2023 13:14:16 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[login_attempts]( [id] [int] IDENTITY(1,1) NOT NULL, [user_id] [int] NOT NULL, [created_at] [datetime] NOT NULL, CONSTRAINT [PK_login_attempts] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[order_services] Script Date: 19.12.2023 13:14:16 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[order_services]( [id] [int] IDENTITY(1,1) NOT NULL, [service_id] [int] NOT NULL, [order_id] [int] NOT NULL, [status] [smallint] NOT NULL, [utilizer_id] [int] NULL, [started_at] [datetime] NULL, [finished_at] [datetime] NULL, CONSTRAINT [PK_order_services] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[orders] Script Date: 19.12.2023 13:14:16 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[orders]( [id] [int] IDENTITY(1,1) NOT NULL, [created_at] [datetime] NOT NULL, [client_id] [int] NOT NULL, [hidden] [bit] NOT NULL, CONSTRAINT [PK_orders] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[roles] Script Date: 19.12.2023 13:14:16 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[roles]( [id] [int] IDENTITY(1,1) NOT NULL, [name] [varchar](50) NOT NULL, CONSTRAINT [PK_roles] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[services] Script Date: 19.12.2023 13:14:16 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[services]( [id] [int] IDENTITY(1,1) NOT NULL, [name] [varchar](100) NOT NULL, [price] [money] NOT NULL, [code] [varchar](10) NOT NULL, [hidden] [bit] NOT NULL, CONSTRAINT [PK_services] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[users] Script Date: 19.12.2023 13:14:16 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[users]( [id] [int] IDENTITY(1,1) NOT NULL, [role] [int] NOT NULL, [login] [varchar](50) NOT NULL, [password] [varchar](64) NOT NULL, [hidden] [bit] NOT NULL, [surname] [varchar](50) NOT NULL, [name] [varchar](50) NOT NULL, [patronymic] [varchar](50) NULL, [image_path] [varchar](50) NULL, CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[utilizers] Script Date: 19.12.2023 13:14:16 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[utilizers]( [id] [int] IDENTITY(1,1) NOT NULL, [name] [varchar](50) NOT NULL, CONSTRAINT [PK_utilizers] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[clients] ADD CONSTRAINT [DF_clients_hidden] DEFAULT ((0)) FOR [hidden] GO ALTER TABLE [dbo].[login_attempts] ADD CONSTRAINT [DF_login_attempts_created_at] DEFAULT (getdate()) FOR [created_at] GO ALTER TABLE [dbo].[orders] ADD CONSTRAINT [DF_orders_created_at] DEFAULT (getdate()) FOR [created_at] GO ALTER TABLE [dbo].[orders] ADD CONSTRAINT [DF_orders_hidden] DEFAULT ((0)) FOR [hidden] GO ALTER TABLE [dbo].[services] ADD CONSTRAINT [DF_services_hidden] DEFAULT ((0)) FOR [hidden] GO ALTER TABLE [dbo].[users] ADD CONSTRAINT [DF_users_hidden] DEFAULT ((0)) FOR [hidden] GO ALTER TABLE [dbo].[employee_services] WITH CHECK ADD CONSTRAINT [FK_employee_services_services] FOREIGN KEY([service_id]) REFERENCES [dbo].[services] ([id]) ON UPDATE CASCADE ON DELETE CASCADE GO ALTER TABLE [dbo].[employee_services] CHECK CONSTRAINT [FK_employee_services_services] GO ALTER TABLE [dbo].[employee_services] WITH CHECK ADD CONSTRAINT [FK_employee_services_users] FOREIGN KEY([user_id]) REFERENCES [dbo].[users] ([id]) GO ALTER TABLE [dbo].[employee_services] CHECK CONSTRAINT [FK_employee_services_users] GO ALTER TABLE [dbo].[invoices] WITH CHECK ADD CONSTRAINT [FK_invoices_clients] FOREIGN KEY([client_id]) REFERENCES [dbo].[clients] ([id]) ON UPDATE CASCADE ON DELETE CASCADE GO ALTER TABLE [dbo].[invoices] CHECK CONSTRAINT [FK_invoices_clients] GO ALTER TABLE [dbo].[invoices] WITH CHECK ADD CONSTRAINT [FK_invoices_orders] FOREIGN KEY([order_id]) REFERENCES [dbo].[orders] ([id]) GO ALTER TABLE [dbo].[invoices] CHECK CONSTRAINT [FK_invoices_orders] GO ALTER TABLE [dbo].[invoices] WITH CHECK ADD CONSTRAINT [FK_invoices_users] FOREIGN KEY([accountant_id]) REFERENCES [dbo].[users] ([id]) ON UPDATE CASCADE ON DELETE CASCADE GO ALTER TABLE [dbo].[invoices] CHECK CONSTRAINT [FK_invoices_users] GO ALTER TABLE [dbo].[login_attempts] WITH CHECK ADD CONSTRAINT [FK_login_attempts_users] FOREIGN KEY([user_id]) REFERENCES [dbo].[users] ([id]) ON UPDATE CASCADE ON DELETE CASCADE GO ALTER TABLE [dbo].[login_attempts] CHECK CONSTRAINT [FK_login_attempts_users] GO ALTER TABLE [dbo].[order_services] WITH CHECK ADD CONSTRAINT [FK_order_services_orders] FOREIGN KEY([order_id]) REFERENCES [dbo].[orders] ([id]) ON UPDATE CASCADE ON DELETE CASCADE GO ALTER TABLE [dbo].[order_services] CHECK CONSTRAINT [FK_order_services_orders] GO ALTER TABLE [dbo].[order_services] WITH CHECK ADD CONSTRAINT [FK_order_services_services] FOREIGN KEY([service_id]) REFERENCES [dbo].[services] ([id]) ON UPDATE CASCADE ON DELETE CASCADE GO ALTER TABLE [dbo].[order_services] CHECK CONSTRAINT [FK_order_services_services] GO ALTER TABLE [dbo].[order_services] WITH CHECK ADD CONSTRAINT [FK_order_services_utilizers] FOREIGN KEY([utilizer_id]) REFERENCES [dbo].[utilizers] ([id]) ON UPDATE CASCADE ON DELETE CASCADE GO ALTER TABLE [dbo].[order_services] CHECK CONSTRAINT [FK_order_services_utilizers] GO ALTER TABLE [dbo].[orders] WITH CHECK ADD CONSTRAINT [FK_orders_clients] FOREIGN KEY([client_id]) REFERENCES [dbo].[clients] ([id]) ON UPDATE CASCADE ON DELETE CASCADE GO ALTER TABLE [dbo].[orders] CHECK CONSTRAINT [FK_orders_clients] GO ALTER TABLE [dbo].[users] WITH CHECK ADD CONSTRAINT [FK_users_roles] FOREIGN KEY([role]) REFERENCES [dbo].[roles] ([id]) GO ALTER TABLE [dbo].[users] CHECK CONSTRAINT [FK_users_roles] GO