-- Creamos la base de datos
CREATE DATABASE biblioteca_san_andres;
USE biblioteca_san_andres;

-- 1. Tabla de Socios (Independiente)
CREATE TABLE SOCIO (
    ID_Socio VARCHAR(20) PRIMARY KEY, -- Ejemplo: 2024-0153
    Apellidos VARCHAR(100) NOT NULL,
    Nombres VARCHAR(100) NOT NULL,
    Carrera VARCHAR(100),
    Tipo VARCHAR(50) -- Estudiante, Docente, etc.
);

-- 2. Tabla de Libros (Independiente)
CREATE TABLE LIBRO (
    Cod_Libro VARCHAR(20) PRIMARY KEY, -- Ejemplo: BD-001
    Titulo VARCHAR(255) NOT NULL,
    Autor VARCHAR(150) NOT NULL
);

-- 3. Tabla de Préstamos (Depende de Socio y Libro)
CREATE TABLE PRESTAMO (
    ID_Prestamo VARCHAR(20) PRIMARY KEY, -- Ejemplo: P-00147
    ID_Socio VARCHAR(20),
    Cod_Libro VARCHAR(20),
    F_Prestamo DATE,
    F_Devolucion DATE,
    Estado VARCHAR(20), -- ACTIVO, DEVUELTO
    Multa VARCHAR(50),
    -- Aquí creamos las relaciones (FK)
    FOREIGN KEY (ID_Socio) REFERENCES SOCIO(ID_Socio),
    FOREIGN KEY (Cod_Libro) REFERENCES LIBRO(Cod_Libro)
);

-- Insertamos libros de prueba
INSERT INTO LIBRO (Cod_Libro, Titulo, Autor) VALUES
('BD-001', 'Introducción a PHP', 'Juan Pérez'),
('BD-002', 'MySQL para principiantes', 'Ana Gómez'),
('BD-003', 'Programación Web', 'Carlos Ríos'),
('BD-004', 'Base de Datos Avanzado', 'María López'),
('BD-005', 'HTML y CSS desde cero', 'Luisa Mamani');

-- Insertamos socios de prueba
INSERT INTO SOCIO (ID_Socio, Apellidos, Nombres, Carrera, Tipo) VALUES
('2024-0001', 'Mamani Quispe', 'Carlos Alberto', 'Ingeniería de Sistemas', 'Estudiante'),
('2024-0002', 'Flores Condori', 'Ana María', 'Contabilidad', 'Estudiante'),
('2024-0003', 'Quispe Huanca', 'Pedro Luis', 'Administración', 'Estudiante'),
('2024-0004', 'López Vargas', 'Rosa Elena', 'Ingeniería de Sistemas', 'Docente'),
('2024-0005', 'Condori Apaza', 'Juan Pablo', 'Medicina', 'Estudiante');

-- Insertamos préstamos de prueba
INSERT INTO PRESTAMO (ID_Prestamo, ID_Socio, Cod_Libro, F_Prestamo, F_Devolucion, Estado, Multa) VALUES
('P-00001', '2024-0001', 'BD-001', '2025-04-01', '2025-04-15', 'DEVUELTO', '0'),
('P-00002', '2024-0001', 'BD-002', '2025-04-20', NULL,         'ACTIVO',   '0'),
('P-00003', '2024-0001', 'BD-003', '2025-04-25', NULL,         'ACTIVO',   '5'),
('P-00004', '2024-0002', 'BD-001', '2025-04-10', '2025-04-20', 'DEVUELTO', '0'),
('P-00005', '2024-0002', 'BD-004', '2025-04-28', NULL,         'ACTIVO',   '0'),
('P-00006', '2024-0003', 'BD-005', '2025-03-15', '2025-03-25', 'DEVUELTO', '10'),
('P-00007', '2024-0004', 'BD-002', '2025-04-05', '2025-04-18', 'DEVUELTO', '0'),
('P-00008', '2024-0005', 'BD-003', '2025-04-22', NULL,         'ACTIVO',   '0');


-- pregunta tres
SELECT 
    p.ID_Prestamo AS 'Nro Prestamo',
    s.ID_Socio AS 'Carnet Socio',
    CONCAT(s.Apellidos, ', ', s.Nombres) AS 'Nombre del Socio',
    s.Carrera,
    l.Titulo AS 'Libro Prestado',
    l.Autor,
    p.F_Prestamo AS 'Fecha Salida',
    p.F_Devolucion AS 'Fecha Entrega',
    p.Estado,
    p.Multa
FROM PRESTAMO p
INNER JOIN SOCIO s ON p.ID_Socio = s.ID_Socio
INNER JOIN LIBRO l ON p.Cod_Libro = l.Cod_Libro;