use MASTER
go

if exists (select * from sysdatabases where name = 'CentroMayores')
  drop database CentroMayores
go



CREATE DATABASE [CentroMayores]
  on primary
    ( name = PrimaryData,
      filename = 'C:\CMayores.mdf',
      size = 3 )
  log on
    ( name = LogFile,
      filename = 'C:\CMayores.ldf' )
go


USE [CentroMayores]
GO

CREATE RULE [REGLA_SINO]
AS
@VALUE IN ('S', 'N')
GO

CREATE TYPE [T_SINO] FROM char(1) NOT NULL
GO

EXEC sp_bindrule 'REGLA_SINO', 'T_SINO'
GO

CREATE TYPE [T_PAIS] FROM varchar(40) NOT NULL
CREATE TYPE [T_NOMBRETABLA] FROM varchar(50) NULL
CREATE TYPE [T_NOMBRE] FROM varchar(25) NOT NULL
CREATE TYPE [T_MASCARA] FROM varchar(40) NOT NULL
CREATE TYPE [T_IDENTIFIER] FROM int NOT NULL
CREATE TYPE [T_FECHA] FROM smalldatetime NULL
CREATE TYPE [T_DNI] FROM varchar(20) NOT NULL
CREATE TYPE [T_DIRECCION2] FROM varchar(35) NULL
CREATE TYPE [T_DIRECCION] FROM varchar(35) NOT NULL
CREATE TYPE [T_CODIGOPOSTAL] FROM varchar(15) NOT NULL
CREATE TYPE [T_CODIGON] FROM int NOT NULL
CREATE TYPE [T_CODIGOALFA] FROM varchar(20) NULL
CREATE TYPE [T_CIUDAD] FROM varchar(30) NOT NULL
CREATE TYPE [T_CADENA50] FROM varchar(50) NULL
CREATE TYPE [T_APELLIDO] FROM varchar(30) NOT NULL
GO

--
-- Definicion de tablas : 
--

CREATE TABLE [Paises] (
  [IDPais] [T_IDENTIFIER] NOT NULL,
  [Pais] [T_PAIS] NOT NULL,
  [MascaraCP] [T_MASCARA] DEFAULT '' NOT NULL,
  CONSTRAINT [pk_pais] PRIMARY KEY CLUSTERED ([IDPais]),
  CONSTRAINT [uk_paises_pais] UNIQUE ([Pais])
)
ON [PRIMARY]
GO

CREATE TABLE [Centros] (
  [IDCentro] [T_IDENTIFIER] IDENTITY(1, 1) NOT FOR REPLICATION NOT NULL,
  [CodigoCentro] [T_CODIGON] NOT NULL,
  [NombreCentro] [T_CADENA50] NULL,
  [Activo] [T_SINO] NOT NULL,
  CONSTRAINT [pk_Centros] PRIMARY KEY NONCLUSTERED ([IDCentro]),
  CONSTRAINT [uk_centros_CodigoCentro] UNIQUE ([CodigoCentro]),
  CONSTRAINT [val_Activo] CHECK ([Activo]='N' OR [Activo]='S')
)
ON [PRIMARY]
GO

CREATE TABLE [Contadores] (
  [IDContador] [T_IDENTIFIER] IDENTITY(1, 1) NOT FOR REPLICATION NOT NULL,
  [TableName] [T_NOMBRETABLA] NOT NULL,
  [NextValue] int NOT NULL,
  [LastDate] [T_FECHA] DEFAULT getdate() NOT NULL,
  CONSTRAINT [pk_Contadores] PRIMARY KEY NONCLUSTERED ([IDContador]),
  CONSTRAINT [uk_Contadores_TableName] UNIQUE ([TableName])
)
ON [PRIMARY]
GO

CREATE TABLE [Actividades] (
  [IDActividad] [T_IDENTIFIER] IDENTITY(1, 1) NOT FOR REPLICATION NOT NULL,
  [CodigoActividad] [T_CODIGON] NOT NULL,
  [Nombre] [T_CADENA50] NULL,
  [FechaAlta] [T_FECHA] DEFAULT getdate() NULL,
  CONSTRAINT [pk_Actividades] PRIMARY KEY NONCLUSTERED ([IDActividad]),
  CONSTRAINT [uk_Actividades_CodigoActividad] UNIQUE ([CodigoActividad])
)
ON [PRIMARY]
GO

CREATE TABLE [Ejercicios] (
  [Ejercicio] int NOT NULL,
  [Activo] [T_SINO] DEFAULT 'S' NOT NULL,
  CONSTRAINT [uk_Ejercicios] UNIQUE ([Ejercicio])
)
ON [PRIMARY]
GO

CREATE TABLE [ActividadesEjercicio] (
  [IDActividadEjercicio] [T_IDENTIFIER] IDENTITY(1, 1) NOT FOR REPLICATION NOT NULL,
  [IDActividad] [T_IDENTIFIER] NOT NULL,
  [IDCentro] [T_IDENTIFIER] NOT NULL,
  [Ejercicio] [T_IDENTIFIER] NOT NULL,
  [FechaInicio] [T_FECHA] DEFAULT getdate() NOT NULL,
  [FechaFin] [T_FECHA] NULL,
  [SuscripcionMaxima] int DEFAULT 10 NULL,
  CONSTRAINT [pk_ActividadesEjercicio] PRIMARY KEY NONCLUSTERED ([IDActividadEjercicio]),
  CONSTRAINT [uk_ActividadesEjercicio_ClaveNatural] UNIQUE ([IDActividad], [Ejercicio], [IDCentro]),
  CONSTRAINT [ActividadesEjercicio_fk_Centros] FOREIGN KEY ([IDCentro]) REFERENCES [Centros] ([IDCentro]),
  CONSTRAINT [ActividadesEjercicio_fk_Actividades] FOREIGN KEY ([IDActividad]) REFERENCES [Actividades] ([IDActividad]),
  CONSTRAINT [ActividadesEjercicio_fk_Ejercicios] FOREIGN KEY ([Ejercicio]) REFERENCES [Ejercicios] ([Ejercicio])
)
ON [PRIMARY]
GO

CREATE TABLE [Beneficiarios] (
  [IDBeneficiario] [T_IDENTIFIER] IDENTITY(1, 1) NOT FOR REPLICATION  NOT NULL,
  [Codigo] [T_CODIGON] NOT NULL,
  [Nombre] [T_NOMBRE] NOT NULL,
  [Apellidos] [T_APELLIDO] NOT NULL,
  [DNI] [T_DNI] NOT NULL,
  [FechaNacimiento] [T_FECHA] NULL,
  [Direccion] int NULL,
  [FechaAlta] [T_FECHA] NOT NULL,
  [FechaBaja] [T_FECHA] NULL,
  [MotivoBaja] char(1) NULL,
  CONSTRAINT [pk_beneficiarios] PRIMARY KEY NONCLUSTERED ([IDBeneficiario]),
  CONSTRAINT [uk_Beneficiarios_codigo] UNIQUE ([Codigo]),
  CONSTRAINT [uk_beneficiarios_dni] UNIQUE ([DNI]),
  CONSTRAINT [Beneficiarios_ck] CHECK ([MOTIVOBAJA]='E' OR [MOTIVOBAJA]='V' OR [MOTIVOBAJA]='F' OR [MOTIVOBAJA]='N')
)
ON [PRIMARY]
GO

CREATE TABLE [Direcciones] (
  [IDDireccion] [T_IDENTIFIER] IDENTITY(1, 1) NOT FOR REPLICATION  NOT NULL,
  [IDBeneficiario] [T_IDENTIFIER] NOT NULL,
  [Direccion] [T_DIRECCION] NOT NULL,
  [Direccion2] [T_DIRECCION2] NULL,
  [CP] [T_CODIGOPOSTAL] NOT NULL,
  [Ciudad] [T_CIUDAD] NOT NULL,
  [IDPais] [T_IDENTIFIER] NOT NULL,
  [Activa] [T_SINO] DEFAULT 'N' NOT NULL,
  CONSTRAINT [pk_Direcciones] PRIMARY KEY NONCLUSTERED ([IDDireccion]),
  CONSTRAINT [Direcciones_fk] FOREIGN KEY ([IDPais]) REFERENCES [Paises] ([IDPais]),
  CONSTRAINT [Direcciones_fk2] FOREIGN KEY ([IDBeneficiario]) REFERENCES [Beneficiarios] ([IDBeneficiario])
)
ON [PRIMARY]
GO

CREATE TABLE [Suscripciones] (
  [IDSuscripcion] [T_IDENTIFIER] IDENTITY(1, 1) NOT FOR REPLICATION NOT NULL,
  [IDActividadEjercicio] [T_IDENTIFIER] NOT NULL,
  [IDBeneficiario] [T_IDENTIFIER] NOT NULL,
  [FechaInicio] [T_FECHA] NOT NULL,
  CONSTRAINT [pk_Suscripciones] PRIMARY KEY NONCLUSTERED ([IDSuscripcion]),
  CONSTRAINT [uk_Suscripciones_clavenatural] UNIQUE ([IDBeneficiario], [IDActividadEjercicio]),
  CONSTRAINT [Suscripciones_fk] FOREIGN KEY ([IDActividadEjercicio]) REFERENCES [ActividadesEjercicio] ([IDActividadEjercicio]),
  CONSTRAINT [Suscripciones_fk2] FOREIGN KEY ([IDBeneficiario]) REFERENCES [Beneficiarios] ([IDBeneficiario])
)
ON [PRIMARY]
GO

CREATE TABLE [NoAsistenciaActividades] (
  [IDNoAsistencia] [T_IDENTIFIER] IDENTITY(1, 1) NOT FOR REPLICATION NOT NULL,
  [IDSuscripcion] [T_IDENTIFIER] NOT NULL,
  [Fecha] [T_FECHA] NOT NULL,
  [Justificada] [T_SINO] NOT NULL,
  [Observaciones] varchar(120) NULL,
  CONSTRAINT [pk_NoAsistenciaActividades] PRIMARY KEY NONCLUSTERED ([IDNoAsistencia]),
  CONSTRAINT [NoAsistenciaActividades_fk] FOREIGN KEY ([IDSuscripcion]) REFERENCES [Suscripciones] ([IDSuscripcion])
)
ON [PRIMARY]
GO

--
-- Definicion de procedimientos : 
--

CREATE PROCEDURE GetValueContador
    @TableName VARCHAR (50),
    @Value INT OUT
    AS
        BEGIN
            SELECT @Value = Contadores.NextValue
            FROM Contadores
            WHERE Contadores.Tablename = @Tablename           
        END
GO

CREATE PROCEDURE GetNextValueContador
    @TableName VARCHAR (50),
    @NextValue INT OUT
    AS
        BEGIN
            Update Contadores
            Set             
                  @NextValue = Contadores.NextValue,           
                  Contadores.NextValue = Contadores.NextValue + 1                  
            WHERE Contadores.Tablename = @Tablename
        END
GO

CREATE PROCEDURE spEliminarBeneficiario
    @IDBeneficiario T_IDENTIFIER
    as
begin
    delete from dbo.DIRECCIONES
    where  IDBeneficiario = @IDBeneficiario
    delete from dbo.BENEFICIARIOS
    where  IDBeneficiario = @IDBeneficiario
end
GO

create procedure spIdentidad
    as
begin
    return @@identity
end
GO