ddl.sql

create table usuarios
(
    usuario_id     int         not null auto_increment,
    usuario        varchar(10) not null,
    contrasenia    varchar(20) not null,
    fecha_creacion datetime    not null,
    unique (usuario, contrasenia),
    primary key (usuario_id)
);

create table empleados
(
    empleado_id int         not null auto_increment,
    nombre      varchar(20) not null,
    apellido    varchar(30) not null,
    DUI         int(11) not null,
    email       varchar(30) not null,
    telefono    int(11) not null,
    usuario_id  int         not null,
    unique (DUI, email),
    primary key (empleado_id),
    foreign key (usuario_id) references usuarios (usuario_id)
        on update cascade on delete restrict
);

create table paquetes
(
    paquete_id       int              not null auto_increment,
    nombre_comercial varchar(20)      not null,
    precio           double precision not null,
    concepto         varchar(200)     not null,
    unique (nombre_comercial),
    primary key (paquete_id)
);

create table clientes
(
    cliente_id int         not null auto_increment,
    DUI        int(11) not null,
    nombre     varchar(20) not null,
    apellido   varchar(30) not null,
    email      varchar(30) not null,
    telefono   int(11) not null,
    usuario_id int         not null,
    unique (DUI, email),
    primary key (cliente_id),
    foreign key (usuario_id) references usuarios (usuario_id)
        on update cascade on delete restrict
);

create table transacciones
(
    transaccion_id    int              not null auto_increment,
    orden_id          varchar(20)      not null,
    estado            varchar(20)      not null,
    monto_final       double precision not null,
    creacion          timestamp        not null,
    cliente_paypal_id int              not null,
    transaccion_url   varchar(100)     not null,
    cliente_id        int              not null,
    primary key (transaccion_id),
    foreign key (cliente_id) references clientes (cliente_id)
        on update cascade on delete restrict
);

create table citas
(
    cita_id     int          not null auto_increment,
    fecha       datetime     not null,
    hora_inicio time         not null,
    hora_fin    time         not null,
    detalle     varchar(100) not null,
    evento      varchar(20)  not null,
    cliente_id  int          not null,
    empleado_id int          not null,
    paquete_id  int          not null,
    primary key (cita_id),
    foreign key (cliente_id) references clientes (cliente_id)
        on update cascade on delete restrict,
    foreign key (empleado_id) references empleados (empleado_id)
        on update cascade on delete restrict
);

create table facturas
(
    feactura_id    int              not null auto_increment,
    detalles       varchar(100)     not null,
    monto_final    double precision not null,
    transaccion_id int              not null,
    cita_id        int              not null,
    paquete_id     int              not null,
    primary key (feactura_id),
    foreign key (transaccion_id) references transacciones (transaccion_id),
    foreign key (cita_id) references citas (cita_id)
        on update cascade on delete restrict,
    foreign key (paquete_id) references paquetes (paquete_id)
        on update cascade on delete restrict
);

create table detalle_paquetes
(
    detalle_paquete_id int          not null auto_increment,
    ubicacion          varchar(100) not null,
    paquete_id         int          not null,
    cita_id            int          not null,
    primary key (detalle_paquete_id),
    foreign key (paquete_id) references paquetes (paquete_id)
        on update cascade on delete restrict,
    foreign key (cita_id) references citas (cita_id)
        on update cascade on delete restrict
);