--Se crea base de datos de Asosalud

create DATABASE Asosalud;

--Se usa la sentencia USE para indicar que dentro de la base de datos se crearan las siguientes tablas

USE Asosalud;

--Se crean las entidades del MER, con la sentencia create table e indicamos los tipos de datos para cada atributo

create table clientes (id_cliente INT primary KEY,
tipo_documento ENUM('CC','CE','NI','PA'), 
nombres VARCHAR(100),
apellidos VARCHAR(100),
Fecha_de_nacimiento DATE,
correo VARCHAR(100),
direccion VARCHAR(150),
fecha_registro DATE,
estado_cliente ENUM('activo','retirado','suspendido')
)

--Usamos la sentencia select * from para visualizar la tabla clientes

select * from clientes;

--Se crea tabla telefono con tipos de datos para cada atributo dependiente de la tabla cliente

create table telefono (
id_telefono INT primary key,
id_cliente INT,
numero VARCHAR(10),
tipo ENUM('celular','fijo','trabajo'),
foreign key (id_cliente) references clientes(id_cliente)
);

--Se crea entidad asesor, con la sentencia create table e indicamos los tipos de datos para cada atributo

create table asesor (id_asesor INT primary KEY,
nombres VARCHAR(100),
apellidos VARCHAR(100),
telefono VARCHAR(10),
correo VARCHAR(100),
fecha_ingreso DATE,
estado_asesor ENUM('activo','inactivo')
);

--Se crea entidad plan, con la sentencia create table e indicamos los tipos de datos para cada atributo

create table plan (id_plan INT primary key,
nombre_plan VARCHAR(100),
descripcion TEXT,
precio_base DECIMAL(10.2),
tipo_plan VARCHAR(15),
estado_plan ENUM('activo','inactivo')
);

--Se crea entidad servcicio, con la sentencia create table e indicamos los tipos de datos para cada atributo

create table servicio (id_servicio INT primary key,
nombre_servicio VARCHAR(100),
descripcion TEXT,
cobertura VARCHAR(100),
estado_servicio ENUM('activo','inactivo')
);

--Se crea tabla contrato (entidad debil) con tipos de datos para cada atributo dependientes de las tablas cliente, asesor y plan

create table contrato (id_contrato INT primary key,
id_cliente INT,
id_plan INT,
id_asesor INT,
fecha_inicio DATE,
fecha_fin DATE,
estado_contrato ENUM('activo','suspendido','cancelado'),
valor_mensual DECIMAL(10.2),
foreign key (id_cliente) references clientes(id_cliente),
foreign key (id_plan) references plan (id_plan),
foreign key (id_asesor) references asesor (id_asesor)
);

--Se crea tabla pago con tipos de datos para cada atributo dependiente de la tabla contrato

create table pago (id_pago INT primary key,
fecha_pago DATE,
valor_pagado DECIMAL(10.2),
metodo_pago VARCHAR(30),
estado_pago ENUM('pagado','pendiente','rechazado'),
id_contrato INT,
foreign key (id_contrato) references contrato (id_contrato)
);

--Se crea la tabla intermedia plan_servicio que relaciona la tabla plan con la tabla servicio
--mediante una llave primaria compuesta (id_plan, id_servicio) y llaves foráneas que referencian cada tabla

create table plan_servicio (id_plan INT,
id_servicio INT,
fecha_inicio DATE,
primary key (id_plan, id_servicio),
foreign key (id_plan) references plan (id_plan),
foreign key (id_servicio) references servicio (id_servicio)
);

select * from plan_servicio


--Utilizamos sentencia DML insert INTO

INSERT into clientes (id_cliente, tipo_documento, nombres, apellidos, fecha_de_nacimiento, Correo, direccion, fecha_registro, estado_cliente)
VALUES
(1023456789, 'CC', 'Juan David', 'Gómez Pérez', '1995-04-12', 'juan.gomez@gmail.com', 'Calle 10 #25-30', '2026-01-15', 'activo'),
 
(1098765432, 'CC', 'María Fernanda', 'Rodríguez López', '1992-09-03', 'maria.rodriguez@gmail.com', 'Carrera 45 #18-20', '2026-01-20', 'activo'),
 
(1012345678, 'CC', 'Carlos Andrés', 'Martínez Silva', '1988-06-25', 'carlos.martinez@gmail.com', 'Av 3 Norte #12-55', '2026-02-01', 'retirado'),
 
(1034567890, 'CC', 'Laura Paola', 'Hernández Castro', '1998-11-10', 'laura.hernandez@gmail.com', 'Calle 72 #15-40', '2026-02-05', 'activo'),
 
(1045678901, 'CC', 'Andrés Felipe', 'Ramírez Torres', '1990-02-18', 'andres.ramirez@gmail.com', 'Carrera 8 #30-60', '2026-02-10', 'suspendido');

select * from clientes

--

INSERT INTO telefono 
(id_telefono, id_cliente, numero, Tipo)
VALUES
(1, 1023456789, '3001234567', 'celular'),
(2, 1098765432, '3019876543', 'celular'),
(3, 1012345678, '6023456789', 'fijo'),
(4, 1034567890, '3154567890', 'trabajo'),
(5, 1045678901, '3206549871', 'celular');

select * from telefono

--

INSERT INTO asesor (id_asesor, nombres, apellidos, telefono, correo, fecha_ingreso, estado_asesor)
VALUES
(1, 'Juan', 'Pérez', '3001234567', 'juan.perez@email.com', '2023-01-10', 'activo'),
(2, 'María', 'González', '3109876543', 'maria.gonzalez@email.com', '2023-02-15', 'activo'),
(3, 'Carlos', 'Ramírez', '3112345678', 'carlos.ramirez@email.com', '2023-03-20', 'inactivo'),
(4, 'Ana', 'López', '3123456789', 'ana.lopez@email.com', '2023-04-25', 'activo'),
(5, 'Luis', 'Martínez', '3134567890', 'luis.martinez@email.com', '2023-05-30', 'activo');

select * from asesor

--

INSERT INTO plan (id_plan, nombre_plan, descripcion, precio_base, tipo_plan, estado_plan)
VALUES
(1, 'Plan Básico', 'Plan básico con cobertura limitada', 10.99, 'Básico', 'Activo'),
(2, 'Plan Premium', 'Plan premium con cobertura ampliada', 24.99, 'Premium', 'Activo'),
(3, 'Plan Empresarial', 'Plan empresarial con cobertura total', 49.99, 'Empresarial', 'Activo'),
(4, 'Plan Familiar', 'Plan familiar con cobertura para toda la familia', 19.99, 'Familiar', 'Activo'),
(5, 'Plan Individual', 'Plan individual con cobertura básica', 9.99, 'Individual', 'Activo');

select * from plan

--

INSERT INTO servicio (id_servicio, nombre_servicio, descripcion, cobertura, estado_servicio)
VALUES
(1, 'Servicio de Internet', 'Servicio de internet de alta velocidad', 'Nacional', 'Activo'),
(2, 'Servicio de Telefonía', 'Servicio de telefonía móvil', 'Internacional', 'Activo'),
(3, 'Servicio de TV', 'Servicio de televisión por cable', 'Nacional', 'Activo'),
(4, 'Servicio de Seguridad', 'Servicio de seguridad para hogares', 'Local', 'Activo'),
(5, 'Servicio de Asistencia', 'Servicio de asistencia técnica', 'Nacional', 'Activo');

select * from servicio

INSERT INTO contrato
(id_contrato, fecha_inicio, fecha_fin, estado_contrato, valor_mensual, id_cliente, id_plan, id_asesor)
VALUES
(1, '2024-01-10', '2024-12-31', 'activo', 120000.00, 1023456789, 1, 1),
(2, '2024-02-15', '2025-02-14', 'activo', 150000.00, 1098765432, 2, 2),
(3, '2024-03-01', '2024-11-30', 'suspendido', 100000.00, 1012345678, 1, 1),
(4, '2024-04-20', '2025-04-19', 'activo', 180000.00, 1034567890, 3, 2),
(5, '2024-05-05', '2024-10-05', 'cancelado', 130000.00, 1045678901, 2, 3);

select * from contrato

--

INSERT INTO PAGO
(id_pago, fecha_pago, valor_pagado, metodo_pago, estado_pago, id_contrato)
VALUES
(1, '2024-06-01', 150000.00, 'Tarjeta de credito', 'pagado', 1),
(2, '2024-06-05', 200000.00, 'Transferencia bancaria', 'pagado', 2),
(3, '2024-06-10', 180000.00, 'Efectivo', 'pendiente', 3),
(4, '2024-06-12', 220000.00, 'Tarjeta debito', 'rechazado', 4),
(5, '2024-06-15', 175000.00, 'Transferencia bancaria', 'pagado', 5);

select * from pago

--

INSERT INTO plan_servicio (id_plan, id_servicio, fecha_inicio)
VALUES
(1, 1, '2023-01-01'),
(1, 2, '2023-01-01'),
(2, 3, '2023-02-15'),
(3, 4, '2023-03-10'),
(4, 5, '2023-04-05');

select * from plan_servicio

--Sentencias DDL utilizamos alter para cambiar la longitud del VARCHAR del atributo nombre_servicio en la entidad servicio de 100 a 50

alter table servicio modify column nombre_servicio VARCHAR(50)

DESCRIBE servicio;

--Se utiliza la sentencia DML select para consultar los cliente cuyos nombres terminan en la vocal "a"

SELECT nombres, apellidos
FROM clientes
WHERE nombres LIKE "%a";

--Se crea tabla plan_servicio2 para ejecutar sentencia DDL drop 


create table plan_servicio2 (id_plan INT,
id_servicio INT,
fecha_inicio DATE,
primary key (id_plan, id_servicio),
foreign key (id_plan) references plan (id_plan),
foreign key (id_servicio) references servicio (id_servicio)
);

select * from plan_servicio2

--Se elemina la tabla anterior creada

drop table plan_servicio2

--Se utiliza la sentencia DML UPDATE para modificar los valores del atributo nombre de la entidad clientes

use Asosalud;

update clientes set nombres = 'Jose Manuel' where id_cliente = 1023456789;

select  * from clientes

--Se utiliza la sentencia DML DELET para borrar un registro de la tabla plan_servicio

delete from plan_servicio where id_plan = 4;


select * from plan_servicio