IF DB_ID('SCommunity') IS NULL
  CREATE DATABASE SCommunity;
GO
USE SCommunity;
GO

IF OBJECT_ID('Revisiones') IS NOT NULL DROP TABLE Revisiones;
IF OBJECT_ID('Archivos') IS NOT NULL DROP TABLE Archivos;
IF OBJECT_ID('Entregas') IS NOT NULL DROP TABLE Entregas;
IF OBJECT_ID('Procesos') IS NOT NULL DROP TABLE Procesos;
IF OBJECT_ID('Cursos') IS NOT NULL DROP TABLE Cursos;
IF OBJECT_ID('Usuarios') IS NOT NULL DROP TABLE Usuarios;
IF OBJECT_ID('Roles') IS NOT NULL DROP TABLE Roles;
IF OBJECT_ID('Bitacora') IS NOT NULL DROP TABLE Bitacora;

CREATE TABLE Roles(
  id INT IDENTITY(1,1) PRIMARY KEY,
  nombre NVARCHAR(30) NOT NULL UNIQUE
);

CREATE TABLE Usuarios(
  id INT IDENTITY(1,1) PRIMARY KEY,
  nombre NVARCHAR(100) NOT NULL,
  email NVARCHAR(150) NOT NULL UNIQUE,
  passhash NVARCHAR(200) NOT NULL,
  rol_id INT NOT NULL FOREIGN KEY REFERENCES Roles(id),
  activo BIT NOT NULL DEFAULT 1,
  creado_en DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
);

CREATE TABLE Cursos(
  id INT IDENTITY(1,1) PRIMARY KEY,
  nivel NVARCHAR(30) NOT NULL,
  seccion CHAR(1) NOT NULL CHECK (seccion IN ('A','B')),
  UNIQUE(nivel, seccion)
);

CREATE TABLE Procesos(
  id INT IDENTITY(1,1) PRIMARY KEY,
  nombre NVARCHAR(120) NOT NULL,
  fecha_inicio DATE NOT NULL,
  fecha_termino DATE NOT NULL,
  estado NVARCHAR(20) NOT NULL DEFAULT 'Abierto'
);
CREATE INDEX IX_Procesos_Rango ON Procesos(fecha_inicio, fecha_termino);

CREATE TABLE Entregas(
  id INT IDENTITY(1,1) PRIMARY KEY,
  proceso_id INT NOT NULL FOREIGN KEY REFERENCES Procesos(id),
  docente_id INT NOT NULL FOREIGN KEY REFERENCES Usuarios(id),
  curso_id INT NOT NULL FOREIGN KEY REFERENCES Cursos(id),
  titulo NVARCHAR(200) NOT NULL,
  estado NVARCHAR(20) NOT NULL DEFAULT 'Enviada' CHECK (estado IN ('Enviada','Aprobada','Rechazada','Corregido')),
  version INT NOT NULL DEFAULT 1,
  enviado_en DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
  UNIQUE(proceso_id, docente_id, curso_id, version)
);

CREATE TABLE Archivos(
  id INT IDENTITY(1,1) PRIMARY KEY,
  entrega_id INT NOT NULL FOREIGN KEY REFERENCES Entregas(id),
  nombre_original NVARCHAR(260) NOT NULL,
  ruta NVARCHAR(400) NOT NULL,
  mime NVARCHAR(100) NOT NULL,
  tamanio_bytes BIGINT NOT NULL,
  subido_en DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
);

CREATE TABLE Revisiones(
  id INT IDENTITY(1,1) PRIMARY KEY,
  entrega_id INT NOT NULL FOREIGN KEY REFERENCES Entregas(id),
  coordinador_id INT NOT NULL FOREIGN KEY REFERENCES Usuarios(id),
  accion NVARCHAR(10) NOT NULL CHECK (accion IN ('Aprobar','Rechazar')),
  motivo NVARCHAR(1000) NULL,
  creado_en DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
);

CREATE TABLE Bitacora(
  id BIGINT IDENTITY(1,1) PRIMARY KEY,
  usuario_id INT NULL,
  accion NVARCHAR(100) NOT NULL,
  detalle NVARCHAR(2000) NULL,
  creado_en DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
);
