USE [SCommunity]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Archivos](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[entrega_id] [int] NOT NULL,
	[nombre_original] [nvarchar](260) NOT NULL,
	[ruta] [nvarchar](400) NOT NULL,
	[mime] [nvarchar](100) NOT NULL,
	[tamanio_bytes] [bigint] NOT NULL,
	[subido_en] [datetime2](7) NOT NULL,
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].[ArchivosArchivados]    Script Date: 02/10/2025 16:35:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ArchivosArchivados](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[entrega_archivada_id] [int] NOT NULL,
	[nombre_original] [nvarchar](260) NOT NULL,
	[ruta] [nvarchar](400) NOT NULL,
	[mime] [nvarchar](100) NOT NULL,
	[tamanio_bytes] [bigint] NOT NULL,
	[subido_en] [datetime2](7) NOT NULL,
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].[Bitacora]    Script Date: 02/10/2025 16:35:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Bitacora](
	[id] [bigint] IDENTITY(1,1) NOT NULL,
	[usuario_id] [int] NULL,
	[accion] [nvarchar](100) NOT NULL,
	[detalle] [nvarchar](2000) NULL,
	[creado_en] [datetime2](7) NOT NULL,
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].[Cursos]    Script Date: 02/10/2025 16:35:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Cursos](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[nivel] [nvarchar](30) NOT NULL,
	[seccion] [char](1) NOT NULL,
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],
UNIQUE NONCLUSTERED 
(
	[nivel] ASC,
	[seccion] 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].[Entregas]    Script Date: 02/10/2025 16:35:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Entregas](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[proceso_id] [int] NOT NULL,
	[docente_id] [int] NOT NULL,
	[curso_id] [int] NOT NULL,
	[titulo] [nvarchar](200) NOT NULL,
	[estado] [nvarchar](20) NOT NULL,
	[version] [int] NOT NULL,
	[enviado_en] [datetime2](7) NOT NULL,
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],
UNIQUE NONCLUSTERED 
(
	[proceso_id] ASC,
	[docente_id] ASC,
	[curso_id] ASC,
	[version] 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].[EntregasArchivadas]    Script Date: 02/10/2025 16:35:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[EntregasArchivadas](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[entrega_id_original] [int] NOT NULL,
	[proceso_id] [int] NOT NULL,
	[docente_id] [int] NOT NULL,
	[curso_id] [int] NOT NULL,
	[titulo] [nvarchar](200) NOT NULL,
	[estado] [nvarchar](20) NOT NULL,
	[version] [int] NOT NULL,
	[enviado_en] [datetime2](7) NOT NULL,
	[ultimo_motivo] [nvarchar](1000) NULL,
	[archivado_en] [datetime2](7) NOT NULL,
	[archivado_por] [int] NULL,
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].[Procesos]    Script Date: 02/10/2025 16:35:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Procesos](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[nombre] [nvarchar](120) NOT NULL,
	[fecha_inicio] [date] NOT NULL,
	[fecha_termino] [date] NOT NULL,
	[estado] [nvarchar](20) NOT NULL,
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].[Revisiones]    Script Date: 02/10/2025 16:35:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Revisiones](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[entrega_id] [int] NOT NULL,
	[coordinador_id] [int] NOT NULL,
	[accion] [nvarchar](10) NOT NULL,
	[motivo] [nvarchar](1000) NULL,
	[creado_en] [datetime2](7) NOT NULL,
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: 02/10/2025 16:35:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Roles](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[nombre] [nvarchar](30) NOT NULL,
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],
UNIQUE NONCLUSTERED 
(
	[nombre] 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].[Usuarios]    Script Date: 02/10/2025 16:35:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Usuarios](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[nombre] [nvarchar](100) NOT NULL,
	[email] [nvarchar](150) NOT NULL,
	[passhash] [nvarchar](200) NOT NULL,
	[rol_id] [int] NOT NULL,
	[activo] [bit] NOT NULL,
	[creado_en] [datetime2](7) NOT NULL,
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],
UNIQUE NONCLUSTERED 
(
	[email] 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].[Archivos] ADD  DEFAULT (sysutcdatetime()) FOR [subido_en]
GO
ALTER TABLE [dbo].[Bitacora] ADD  DEFAULT (sysutcdatetime()) FOR [creado_en]
GO
ALTER TABLE [dbo].[Entregas] ADD  DEFAULT ('Enviada') FOR [estado]
GO
ALTER TABLE [dbo].[Entregas] ADD  DEFAULT ((1)) FOR [version]
GO
ALTER TABLE [dbo].[Entregas] ADD  DEFAULT (sysutcdatetime()) FOR [enviado_en]
GO
ALTER TABLE [dbo].[EntregasArchivadas] ADD  DEFAULT (sysutcdatetime()) FOR [archivado_en]
GO
ALTER TABLE [dbo].[Procesos] ADD  DEFAULT ('Abierto') FOR [estado]
GO
ALTER TABLE [dbo].[Revisiones] ADD  DEFAULT (sysutcdatetime()) FOR [creado_en]
GO
ALTER TABLE [dbo].[Usuarios] ADD  DEFAULT ((1)) FOR [activo]
GO
ALTER TABLE [dbo].[Usuarios] ADD  DEFAULT (sysutcdatetime()) FOR [creado_en]
GO
ALTER TABLE [dbo].[Archivos]  WITH CHECK ADD FOREIGN KEY([entrega_id])
REFERENCES [dbo].[Entregas] ([id])
GO
ALTER TABLE [dbo].[ArchivosArchivados]  WITH CHECK ADD FOREIGN KEY([entrega_archivada_id])
REFERENCES [dbo].[EntregasArchivadas] ([id])
GO
ALTER TABLE [dbo].[Entregas]  WITH CHECK ADD FOREIGN KEY([curso_id])
REFERENCES [dbo].[Cursos] ([id])
GO
ALTER TABLE [dbo].[Entregas]  WITH CHECK ADD FOREIGN KEY([docente_id])
REFERENCES [dbo].[Usuarios] ([id])
GO
ALTER TABLE [dbo].[Entregas]  WITH CHECK ADD FOREIGN KEY([proceso_id])
REFERENCES [dbo].[Procesos] ([id])
GO
ALTER TABLE [dbo].[Revisiones]  WITH CHECK ADD FOREIGN KEY([coordinador_id])
REFERENCES [dbo].[Usuarios] ([id])
GO
ALTER TABLE [dbo].[Revisiones]  WITH CHECK ADD FOREIGN KEY([entrega_id])
REFERENCES [dbo].[Entregas] ([id])
GO
ALTER TABLE [dbo].[Usuarios]  WITH CHECK ADD FOREIGN KEY([rol_id])
REFERENCES [dbo].[Roles] ([id])
GO
ALTER TABLE [dbo].[Cursos]  WITH CHECK ADD CHECK  (([seccion]='B' OR [seccion]='A'))
GO
ALTER TABLE [dbo].[Entregas]  WITH CHECK ADD  CONSTRAINT [CK_Entregas_Estado] CHECK  (([estado]='Aprobada' OR [estado]='Corregido' OR [estado]='Rechazada' OR [estado]='Enviada'))
GO
ALTER TABLE [dbo].[Entregas] CHECK CONSTRAINT [CK_Entregas_Estado]
GO
ALTER TABLE [dbo].[Revisiones]  WITH CHECK ADD CHECK  (([accion]='Rechazar' OR [accion]='Aprobar'))
GO
