-- Crear nueva columna del Siniestro --
CREATE TABLE asurnet.dbo.PERSONAS
(
    PRS_Id              int IDENTITY(1,1) NOT NULL,
    PRS_Nombre          varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    PRS_Apellido1       varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    PRS_Apellido2       varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    PRS_Direccion       varchar(80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    PRS_Localidad       varchar(80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    PRS_CP              varchar(5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    PRS_TelfPal         varchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    PRS_TelfSec         varchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    PRS_Fax             varchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    PRS_Sexo            smallint NULL,
    PRS_Nacimiento      datetime NULL,
    PRS_FechaCarnet     datetime NULL,
    PRS_CaducidadCarnet datetime NULL,
    PRS_TipoPersona     smallint NULL,
    PRS_FC              datetime           NOT NULL,
    PRS_FM              datetime           NOT NULL,
    USR_Id_FC           int                NULL,
    USR_Id_FM           int                NULL,
    USR_Id_FC_Tesis     int                NULL,
    USR_Id_FM_Tesis     int                NULL,
    PRS_Visible         tinyint NULL,
    PRS_Estado          smallint DEFAULT 3 NOT NULL,
    PRS_Profesion       int NULL,
    PRS_TelfMovil       varchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    PRS_Email           varchar(60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    PRS_NombreBanco     varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    PRS_NumCuenta       varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    CIA_Id              int NULL,
    PRS_Titular         varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    CPT_Id              int NULL,
    PRS_NIF             varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    PRS_CCC             varchar(26) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    PRS_Oficode         int NULL,
    LOG_ID_FC           varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    LOG_ID_FM           varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    LOG_FC              datetime NULL,
    LOG_FM              datetime NULL,
    CONSTRAINT PK_PERSONAS PRIMARY KEY (PRS_Id),
);

CREATE TABLE asurnet.dbo.PERITOS
(
    SinNumID          int NOT NULL,
    SinSer            int NOT NULL,
    PRS_Id            int NOT NULL,
    PRT_Peritaje      datetime NULL,
    PRT_Importe       money NULL,
    PRT_Importe2      real NULL,
    PRT_Importe3      real NULL,
    PRT_Peritaje2     datetime NULL,
    PRT_Peritaje3     datetime NULL,
    PRT_Lugar         varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    PRT_Observaciones varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    PRT_ID            int IDENTITY(1,1) NOT NULL,
    CONSTRAINT PK_PERITOS PRIMARY KEY (SinNumID, SinSer, PRS_Id),
);


CREATE TABLE asurnet.dbo.LESIONADOS
(
    LES_Id            int IDENTITY(1,1) NOT NULL,
    LES_Tipo          smallint NULL,
    LES_ECivil        smallint NULL,
    LES_Profesion     varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    LES_Centro        varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    LES_TelfCentro    varchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    LES_FaxCentro     varchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    LES_Departamento  varchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    LES_Habitacion    varchar(5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    LES_Lesiones      varchar(80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    LES_Secuelas      varchar(80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    LES_Observaciones text COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    LES_NIFCentro varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    PRS_Apellido1       varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    PRS_Apellido2       varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    PRS_NIF       varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    PRS_Sexo            smallint NULL,
    PRS_Nacimiento      datetime NULL,
    PRS_Direccion       varchar(80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    PRS_Localidad       varchar(80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    PRS_CP              varchar(5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    SinNumID          int NOT NULL,
    SinSer            int NOT NULL,
    SinPerNum            int NOT NULL,
    SIN_MyId      int NOT NULL,
    CONSTRAINT PK_LESIONADOS PRIMARY KEY (SinNumID, SinSer,LES_Id),
);


CREATE TABLE asurnet.dbo.PROFESIONES
(
    PRN_Id          int IDENTITY(1,1) NOT NULL,
    PRN_Descripcion varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    PRN_CODIGO      varchar(11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    CONSTRAINT PK_PROFESION PRIMARY KEY (PRN_Id)
);

-- Personas relacion--
CREATE TABLE asurnet.dbo.TRAMITADOR
(
    SinNumID int NOT NULL,
    SinSer   int NOT NULL,
    PRS_Id   int NOT NULL,
    CONSTRAINT PK_TRAMITADOR PRIMARY KEY (SinNumID, SinSer, PRS_Id),
    CONSTRAINT FK_TRAMITADOR_PER FOREIGN KEY (PRS_Id) REFERENCES asurnet.dbo.PERSONAS (PRS_Id),
    CONSTRAINT FK_TRAMITADOR_PERSONAS FOREIGN KEY (PRS_Id) REFERENCES asurnet.dbo.PERSONAS (PRS_Id),
);

CREATE TABLE asurnet.dbo.Abogados
(
    SinNumID int NOT NULL,
    SinSer   int NOT NULL,
    PRS_Id   int NOT NULL,
    CONSTRAINT PK_Abogados PRIMARY KEY (SinNumID, SinSer, PRS_Id),
    CONSTRAINT FK_Abogados_PER FOREIGN KEY (PRS_Id) REFERENCES asurnet.dbo.PERSONAS (PRS_Id),
    CONSTRAINT FK_Abogados_PERSONAS FOREIGN KEY (PRS_Id) REFERENCES asurnet.dbo.PERSONAS (PRS_Id),
);

CREATE TABLE asurnet.dbo.PROFESIONALES
(
    SinNumID int NOT NULL,
    SinSer   int NOT NULL,
    PRS_Id   int NOT NULL,
    CONSTRAINT PK_PROFESIONALES PRIMARY KEY (SinNumID, SinSer, PRS_Id),
    CONSTRAINT FK_PROFESIONALES_PER FOREIGN KEY (PRS_Id) REFERENCES asurnet.dbo.PERSONAS (PRS_Id),
    CONSTRAINT FK_PROFESIONALES_PERSONAS FOREIGN KEY (PRS_Id) REFERENCES asurnet.dbo.PERSONAS (PRS_Id),
);

CREATE TABLE asurnet.dbo.TESTIGOS
(
    SinNumID int NOT NULL,
    SinSer   int NOT NULL,
    PRS_Id   int NOT NULL,
    CONSTRAINT PK_TESTIGOS PRIMARY KEY (SinNumID, SinSer, PRS_Id),
    CONSTRAINT FK_TESTIGOS_PER FOREIGN KEY (PRS_Id) REFERENCES asurnet.dbo.PERSONAS (PRS_Id),
    CONSTRAINT FK_TESTIGOS_PERSONAS FOREIGN KEY (PRS_Id) REFERENCES asurnet.dbo.PERSONAS (PRS_Id),
);


CREATE TABLE asurnet.dbo.CONTRARIOS
(
    CTR_Id             int IDENTITY(1,1) NOT NULL,
    PRS_Contrario      int NULL,
    PRS_Taller         int NULL,
    PRS_Conductor      int NULL,
    CTR_Modelo         varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    CTR_Matricula      varchar(10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    CTR_Danos          varchar(80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    CTR_Observaciones  text COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    CTR_NombreCia      varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    CTR_TelfCia        varchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    CTR_Poliza         varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    CTR_RefCia         varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    CTR_Tramitador     varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    CTR_Circunstancias varchar(30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    SinNumID           int NOT NULL,
    SinSer             int NOT NULL,
    SinConNum             int NOT NULL,
);