-------------------------------------------
--------  CREACION DE LAS TABLAS   -------- 
-------------------------------------------
CREATE DATABASE libreria;
USE libreria;
-- DROP DATABASE `libreria`;

CREATE TABLE Clientes (
	Id_Cliente INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(20) NOT NULL,
    apellido VARCHAR(20) NOT NULL,
    email VARCHAR(50),
    sexo CHAR(1) NOT NULL,
    direccion VARCHAR(50)
);

CREATE TABLE Autores (
    Id_Autor INT NOT NULL,
    nombre VARCHAR(20) NOT NULL,
    apellido VARCHAR(20) NOT NULL,
    PRIMARY KEY (nombre, apellido),
    INDEX idx_Id_Autor (Id_Autor)  -- Agregamos un índice a Id_Autor
);

CREATE TABLE Libros (
    Id_SKU_Libro VARCHAR(50) PRIMARY KEY,
    Fk_Autor INT,
    FOREIGN KEY (Fk_Autor) REFERENCES Autores(Id_Autor),
    titulo VARCHAR(50) NOT NULL,
    categoria VARCHAR(20) NOT NULL,
    formato VARCHAR(20)
);


CREATE TABLE Revistas (
	Id_SKU_Revista VARCHAR(50) PRIMARY KEY,
    Fk_Autor INT NOT NULL,
    FOREIGN KEY (Fk_Autor) REFERENCES Autores(Id_Autor),
    titulo VARCHAR(50) NOT NULL,
    genero VARCHAR(20) NOT NULL,
    volumen INT
);

CREATE TABLE Editoriales (
    Id_Editorial INT AUTO_INCREMENT PRIMARY KEY,
    Fk_SKU_Libro VARCHAR(50),
    FOREIGN KEY (Fk_SKU_Libro) REFERENCES Libros(Id_SKU_Libro),
    Fk_SKU_Revista VARCHAR(50),
    FOREIGN KEY (Fk_SKU_Revista) REFERENCES Revistas(Id_SKU_Revista),
    nombre VARCHAR(20) NOT NULL,
    vencimiento_contrato DATE NOT NULL
);

CREATE TABLE Vinilos (
	Id_SKU_Vinilo VARCHAR(50) PRIMARY KEY,
    Fk_Autor INT,
    FOREIGN KEY (Fk_Autor) REFERENCES Autores(Id_Autor),
    album VARCHAR(50) NOT NULL,
    genero VARCHAR(20) NOT NULL
);

CREATE TABLE Sello_Discografico (
    Id_SD INT AUTO_INCREMENT PRIMARY KEY,
    Fk_SKU_Vinilo VARCHAR(50),
    FOREIGN KEY (Fk_SKU_Vinilo) REFERENCES Vinilos (Id_SKU_Vinilo),
    nombre VARCHAR(20) NOT NULL,
    vencimiento_contrato DATE NOT NULL
);

CREATE TABLE Productos (
	Id_SKU VARCHAR(50) PRIMARY KEY, -- SKU es Stock-keeping unit y es lo que usan las empresas para identificar a los productos.auto_incrementar a los productos
    precio DOUBLE NOT NULL,
    fecha_lanzamiento DATE,
    stock INT NOT NULL
);

CREATE TABLE Ventas (
	Id_Venta INT AUTO_INCREMENT PRIMARY KEY,
    Fk_Cliente INT,
    FOREIGN KEY (Fk_Cliente) REFERENCES Clientes(Id_Cliente),
    Fk_SKU VARCHAR(50),
    FOREIGN KEY (Fk_SKU) REFERENCES Productos(Id_SKU),
    fecha_venta DATE NOT NULL
);

CREATE TABLE Facturas (
    Id_Factura INT AUTO_INCREMENT PRIMARY KEY,
    Fk_Cliente INT,
    FOREIGN KEY (Fk_Cliente) REFERENCES Clientes(Id_Cliente),
    numero INT NOT NULL
);

---------------------------------------
--------  INSERCIÓN DE DATOS   -------- 
---------------------------------------

INSERT INTO clientes (Id_Cliente, nombre, apellido, email, sexo, direccion)
VALUES
(1, 'Fiorenze', 'Sellens', 'fsellens0@theguardian.com', 'F', '35 Trailsway Parkway'),
(2, 'Margarette', 'MacCallister', 'mmaccallister1@w3.org', 'F', '35264 Village Park'),
(3, 'Arturo', 'Mably', 'amably2@springer.com', 'M', '2646 Anhalt Avenue'),
(4, 'Edouard', 'Hickinbottom', 'ehickinbottom3@disqus.com', 'M', '67 Express Alley'),
(5, 'Bill', 'Henrion', 'bhenrion4@nytimes.com', 'F', '35 Rigney Circle'),
(6, 'Archer', 'Gerrans', 'agerrans5@amazon.de', 'M', '0571 Forest Run Park'),
(7, 'Liesa', 'Gilderoy', 'lgilderoy6@dailymail.co.uk', 'F', '38 Debra Park'),
(8, 'Winnie', 'Ferry', 'wferry7@unc.edu', 'F', '426 Schurz Parkway'),
(9, 'Kial', 'Lello', 'klello8@springer.com', 'F', '19 Lotheville Junction'),
(10, 'Binny', 'Cubbin', 'bcubbin9@stumbleupon.com', 'F', '517 Dovetail Center'),
(11, 'Stevy', 'Slegg', 'sslegga@spotify.com', 'M', '05200 Mayfield Lane'),
(12, 'Stacia', 'Caze', 'scazeb@princeton.edu', 'F', '735 Corscot Street'),
(13, 'Lilla', 'Dacca', 'ldaccac@utexas.edu', 'F', '9 Pine View Trail'),
(14, 'Ardyce', 'Undrill', 'aundrilld@google.cn', 'F', '12 Columbus Lane'),
(15, 'Alisander', 'O'' Dornan', 'aodornane@purevolume.com', 'M', '0341 2nd Drive'),
(16, 'Barret', 'Rowbottom', 'browbottomf@springer.com', 'M', '48 Bunker Hill Circle'),
(17, 'Mureil', 'Beckinsale', 'mbeckinsaleg@so-net.ne.jp', 'F', '8065 Becker Crossing'),
(18, 'Becka', 'Sparshett', 'bsparshetth@shop-pro.jp', 'F', '67266 Farwell Point'),
(19, 'Timmi', 'Hallgath', 'thallgathi@icio.us', 'F', '72212 Old Shore Circle'),
(20, 'Rhianna', 'Barde', 'rbardej@engadget.com', 'F', '2792 Springview Drive'),
(21, 'Collen', 'Edmondson', 'cedmondsonk@ucla.edu', 'F', '624 Coolidge Court'),
(22, 'Kalli', 'Alban', 'kalbanl@wunderground.com', 'F', '641 Farwell Lane'),
(23, 'Riva', 'Tewkesberry', 'rtewkesberrym@umn.edu', 'F', '38 1st Lane'),
(24, 'Theresita', 'Hallgath', 'thallgathn@usa.gov', 'F', '886 Dovetail Circle'),
(25, 'Rudyard', 'Valencia', 'rvalenciao@google.ru', 'M', '4 Center Crossing'),
(26, 'Conant', 'Kesteven', 'ckestevenp@phoca.cz', 'M', '8 Maywood Drive'),
(27, 'Shaine', 'Denning', 'sdenningq@reuters.com', 'F', '0 Mcbride Court'),
(28, 'Portia', 'Grinikhin', 'pgrinikhinr@ustream.tv', 'F', '4839 Onsgard Pass'),
(29, 'Rene', 'Strowger', 'rstrowgers@reference.com', 'M', '383 Burrows Drive'),
(30, 'Ricoriki', 'Gerry', 'rgerryt@google.com.br', 'M', '2142 Myrtle Center');

INSERT INTO productos (Id_SKU, precio, fecha_lanzamiento, stock)
VALUES
('686247663-7', 88921, '1975-04-24', 97),
('793224265-7', 78470, '2022-01-25', 93),
('398766862-8', 93991, '2013-04-24', 0),
('957151874-3', 43817, '1933-08-19', 26),
('721159171-4', 79204, '1920-07-12', 99),
('029735996-7', 39844, '1998-08-09', 2),
('575028793-9', 93406, '2009-12-27', 11),
('132434466-0', 26042, '1952-02-15', 12),
('202914985-3', 50946, '1944-12-15', 15),
('217936407-5', 44045, '1973-02-26', 99),
('554834571-0', 63466, '1977-01-01', 83),
('900702911-8', 57685, '1975-01-08', 96),
('950238142-4', 88686, '2008-05-15', 78),
('519742752-3', 39152, '1991-02-25', 8),
('861897195-2', 31611, '1966-03-24', 61),
('501614622-5', 42584, '2008-08-27', 94),
('206097790-8', 48947, '1954-12-09', 81),
('250966766-1', 60494, '1969-05-28', 7),
('585079486-7', 25980, '2016-08-01', 27),
('775970722-3', 6387, '1952-09-06', 78),
('744054512-5', 61889, '1925-02-18', 60),
('706054648-0', 64178, '1936-10-25', 94),
('925445657-8', 35277, '2018-04-22', 92),
('310970054-9', 84996, '2020-11-12', 41),
('101818417-1', 57401, '1983-11-05', 55),
('451932197-2', 3988, '1921-09-11', 43),
('659888255-9', 76725, '2008-11-22', 46),
('147312581-2', 67037, '2008-06-30', 31),
('730468874-2', 10941, '1908-07-20', 29),
('122690742-3', 55827, '1906-04-01', 12),
('621351777-4', 91413, '2004-01-08', 77),
('956005422-8', 27251, '1953-01-13', 78),
('496707823-0', 9088, '2006-05-18', 16),
('786276248-7', 56374, '1935-06-16', 70),
('173206567-5', 33491, '1900-07-19', 16),
('865992626-9', 96199, '1954-01-07', 6),
('562912902-3', 15836, '1988-09-23', 73),
('096393553-4', 11212, '2015-03-27', 94),
('296205172-3', 35682, '1904-10-26', 5),
('076139873-2', 99420, '1980-06-08', 4);

INSERT INTO autores (Id_Autor, nombre, apellido)
VALUES
(1, 'Tiphani', 'Toffaloni'),
(2, 'Faith', 'Benit'),
(3, 'Electra', 'Pulford'),
(4, 'Taylor', 'Masserel'),
(5, 'Arel', 'Witherup'),
(6, 'Orazio', 'Sanderson'),
(7, 'Lisa', 'Tomblett'),
(8, 'Alejandra', 'Gillebert'),
(9, 'Martainn', 'Mundie'),
(10, 'Taylor', 'Gemmell'),
(11, 'Brigg', 'Worthing'),
(12, 'Natalya', 'Fisbey'),
(13, 'Dom', 'Borg'),
(14, 'Rodd', 'Kuhn'),
(15, 'Angelique', 'McGall'),
(16, 'Nolana', 'Breckell'),
(17, 'Abbye', 'Mickelwright'),
(18, 'Evanne', 'Merrilees'),
(19, 'Marline', 'Pittendreigh'),
(20, 'Ajay', 'Bazoge'),
(21, 'Marlin', 'Teaz'),
(22, 'Maynord', 'Kirkness'),
(23, 'Nora', 'Fieldhouse'),
(24, 'Kip', 'McGragh'),
(25, 'Carmon', 'Tapp'),
(26, 'Gertruda', 'Chaston'),
(27, 'Evin', 'Finlator'),
(28, 'Rodge', 'Mongan'),
(29, 'Lurlene', 'Backen'),
(30, 'Dinny', 'Ohrt'),
(31, 'Linn', 'Grayland'),
(32, 'Warner', 'Say'),
(33, 'Nikkie', 'Bonnor'),
(34, 'Dania', 'Greydon'),
(35, 'Victor', 'Knibbs'),
(36, 'Roland', 'Alesio'),
(37, 'Benson', 'Van der Beek'),
(38, 'Margo', 'Albone'),
(39, 'Anatollo', 'Balsillie'),
(40, 'Sondra', 'Church');

INSERT INTO libros (Id_SKU_Libro, Fk_Autor, titulo, categoria, formato)
VALUES
('686247663-7', 1, 'Deciduous Tree (Tree Without Leaves) (Rakuyôju)', 'romance', 'tapa blanda'),
('793224265-7', 2, 'Unreal Dream: The Michael Morton Story, An', 'aventuras', 'tapa blanda'),
('398766862-8', 3, 'My Giant', 'novela', 'tapa dura'),
('957151874-3', 4, 'Attraction', 'ciencia ficción', 'tapa blanda'),
('721159171-4', 5, 'The Stranger''s Return', 'aventuras', 'tapa blanda'),
('029735996-7', 6, 'Deathwatch', 'romance', 'tapa dura'),
('575028793-9', 7, 'Rock School', 'aventuras', 'tapa dura'),
('132434466-0', 8, 'Are they comming?', 'novela', 'tapa blanda'),
('202914985-3', 9, 'Making ''The New World''', 'ciencia ficción', 'tapa blanda'),
('217936407-5', 10, 'Kagerô-za', 'historia', 'tapa dura'),
('554834571-0', 11, 'Big Trouble', 'aventuras', 'tapa dura'),
('900702911-8', 12, 'Love (Szerelem)', 'ciencia ficción', 'tapa blanda'),
('950238142-4', 13, 'Attenberg', 'ciencia ficción', 'tapa blanda'),
('519742752-3', 14, 'Nutcracker, The', 'novela', 'tapa dura'),
('861897195-2', 15, 'Big Daddy', 'aventuras', 'tapa dura'),
('501614622-5', 16, 'Honeymooners, The', 'aventuras', 'tapa dura'),
('206097790-8', 17, 'Total Recall', 'romance', 'tapa dura'),
('250966766-1', 18, 'Eighth Day, The (Den Åttonde dagen)', 'aventuras', 'tapa blanda'),
('585079486-7', 19, 'Jingle All the Way', 'ciencia ficción', 'tapa dura'),
('775970722-3', 20, 'Panic', 'novela', 'tapa dura');

INSERT INTO revistas (Id_SKU_Revista, Fk_Autor, titulo, genero, volumen)
VALUES
('744054512-5', 21, 'Hoy', 'social', 700),
('706054648-0', 22, 'La Hora', 'panoramas', 857),
('925445657-8', 23, 'Entrevistando', 'panoramas', 165),
('310970054-9', 24, 'Muy Interesante', 'instrumentos', 498),
('101818417-1', 25, 'Paula', 'vehiculos', 683),
('451932197-2', 26, 'Cosas', 'social', 978),
('659888255-9', 27, 'Caras', 'salud', 670),
('147312581-2', 28, 'Telegrama', 'panoramas', 940),
('730468874-2', 29, 'Que Paso', 'salud', 266),
('122690742-3', 30, 'Tiempo', 'salud', 986);

INSERT INTO editoriales (Id_Editorial, Fk_SKU_Libro, Fk_SKU_Revista, nombre, vencimiento_contrato)
VALUES
(1, '202914985-3', '122690742-3', 'salamandra', '2026-11-20'),
(2, '202914985-3', '101818417-1', 'zig zag', '2026-05-27'),
(3, '900702911-8', '730468874-2', 'santillana', '2025-09-08'),
(4, '721159171-4', '122690742-3', 'santillana', '2026-09-26'),
(5, '575028793-9', '147312581-2', 'aurea', '2026-02-27'),
(6, '957151874-3', '706054648-0', 'zig zag', '2025-06-13'),
(7, '585079486-7', '744054512-5', '', '2025-03-23'),
(8, '519742752-3', '310970054-9', 'aurea', '2026-08-17'),
(9, '398766862-8', '101818417-1', 'aurea', '2026-11-13'),
(10, '250966766-1', '310970054-9', 'santillana', '2025-04-15'),
(11, '721159171-4', '925445657-8', 'aurea', '2025-10-05'),
(12, '519742752-3', '925445657-8', '', '2025-08-19'),
(13, '721159171-4', '730468874-2', 'salamandra', '2026-05-23'),
(14, '793224265-7', '925445657-8', 'zig zag', '2026-07-15'),
(15, '861897195-2', '925445657-8', 'salamandra', '2025-08-18'),
(16, '250966766-1', '925445657-8', 'salamandra', '2024-12-04'),
(17, '585079486-7', '706054648-0', 'santillana', '2025-08-19'),
(18, '775970722-3', '730468874-2', 'alquimia', '2024-09-22'),
(19, '721159171-4', '706054648-0', 'salomon', '2026-12-13'),
(20, '793224265-7', '730468874-2', 'zig zag', '2025-07-07'),
(21, '721159171-4', '744054512-5', 'santillana', '2025-03-30'),
(22, '202914985-3', '706054648-0', '', '2025-08-25'),
(23, '398766862-8', '147312581-2', 'alquimia', '2025-05-14'),
(24, '585079486-7', '451932197-2', 'santillana', '2024-08-05'),
(25, '501614622-5', '706054648-0', 'aurea', '2024-05-15'),
(26, '900702911-8', '310970054-9', 'salamandra', '2026-12-15'),
(27, '775970722-3', '122690742-3', '', '2025-07-05'),
(28, '686247663-7', '101818417-1', 'alquimia', '2025-03-22'),
(29, '554834571-0', '147312581-2', 'salamandra', '2026-01-08'),
(30, '585079486-7', '147312581-2', 'santillana', '2025-02-07'),
(31, '861897195-2', '730468874-2', 'salomon', '2026-07-16'),
(32, '575028793-9', '310970054-9', 'alquimia', '2024-02-23'),
(33, '861897195-2', '730468874-2', '', '2025-10-15'),
(34, '900702911-8', '147312581-2', 'aurea', '2026-04-04'),
(35, '575028793-9', '659888255-9', 'aurea', '2024-01-04'),
(36, '132434466-0', '744054512-5', 'salomon', '2026-07-06'),
(37, '861897195-2', '744054512-5', 'salomon', '2025-06-25'),
(38, '721159171-4', '101818417-1', 'aurea', '2024-05-30'),
(39, '585079486-7', '147312581-2', 'santillana', '2026-02-18'),
(40, '721159171-4', '744054512-5', 'santillana', '2024-03-19'),
(41, '686247663-7', '925445657-8', 'aurea', '2026-09-20'),
(42, '686247663-7', '659888255-9', 'aurea', '2025-08-15'),
(43, '554834571-0', '310970054-9', '', '2026-03-15'),
(44, '501614622-5', '925445657-8', 'alquimia', '2026-06-12'),
(45, '250966766-1', '925445657-8', 'salamandra', '2026-10-31'),
(46, '029735996-7', '925445657-8', 'aurea', '2024-02-24'),
(47, '957151874-3', '310970054-9', '', '2026-01-13'),
(48, '775970722-3', '730468874-2', 'salomon', '2025-08-18'),
(49, '554834571-0', '925445657-8', '', '2026-09-12'),
(50, '721159171-4', '659888255-9', 'santillana', '2026-04-07');

INSERT INTO vinilos (Id_SKU_Vinilo, Fk_Autor, album, genero)
VALUES
('621351777-4', 31, 'Microlychnus Lichen', 'soul'),
('956005422-8', 32, 'Yellow Waterlily', 'trap'),
('496707823-0', 33, 'San Antonio Hills Monardella', 'rap'),
('786276248-7', 34, 'Eastern Rough Sedge', 'reggaeton'),
('173206567-5', 35, 'Jame''s Rubberweed', 'R&B'),
('865992626-9', 36, 'Scribner Needlegrass', 'pop'),
('562912902-3', 37, 'Mexican Plantain', 'R&B'),
('096393553-4', 38, 'Hybrid Blackberry', 'hip hop'),
('296205172-3', 39, 'Dulichium', 'trap'),
('076139873-2', 40, 'Bouncingbet', 'romantica');

INSERT INTO sello_discografico (Id_SD, Fk_SKU_Vinilo, nombre, vencimiento_contrato)
VALUES
(1, '621351777-4', 'Promusic Records', '2024-10-10'),
(2, '956005422-8', 'AUM Records', '2026-05-03'),
(3, '496707823-0', 'AUM Records', '2026-01-03'),
(4, '786276248-7', 'Amaral Recordsl', '2026-02-05'),
(5, '173206567-5', 'AUM Records', '2026-03-01'),
(6, '865992626-9', 'Amaral Recordsl', '2026-07-24'),
(7, '562912902-3', 'Turorin Records', '2026-05-09'),
(8, '096393553-4', 'Promusic Records', '2026-04-28'),
(9, '296205172-3', 'Turorin Records', '2024-08-18'),
(10, '076139873-2', 'AUM Records', '2025-11-14'),
(11, '621351777-4', 'FQ Records', '2024-06-20'),
(12, '956005422-8', 'Amaral Recordsl', '2026-11-17'),
(13, '496707823-0', 'AUM Records', '2026-10-30'),
(14, '786276248-7', 'Promusic Records', '2024-07-11'),
(15, '173206567-5', 'Promusic Records', '2025-04-14');

INSERT INTO ventas (Id_Venta, Fk_Cliente, Fk_SKU, fecha_venta)
VALUES
(1, 3, '122690742-3', '2023-10-05'),
(2, 25, '744054512-5', '2023-07-13'),
(3, 28, '744054512-5', '2023-10-03'),
(4, 22, '865992626-9', '2021-06-07'),
(5, 9, '730468874-2', '2021-05-30'),
(6, 8, '173206567-5', '2021-04-03'),
(7, 7, '217936407-5', '2021-12-19'),
(8, 9, '310970054-9', '2023-08-19'),
(9, 29, '585079486-7', '2023-06-28'),
(10, 16, '147312581-2', '2024-01-20'),
(11, 26, '076139873-2', '2023-08-14'),
(12, 10, '122690742-3', '2021-04-01'),
(13, 7, '730468874-2', '2023-10-27'),
(14, 16, '775970722-3', '2021-02-17'),
(15, 5, '775970722-3', '2023-02-09'),
(16, 14, '496707823-0', '2024-01-19'),
(17, 20, '585079486-7', '2022-12-26'),
(18, 22, '950238142-4', '2022-11-21'),
(19, 6, '096393553-4', '2023-04-13'),
(20, 14, '786276248-7', '2022-04-25'),
(21, 19, '730468874-2', '2021-08-15'),
(22, 29, '206097790-8', '2022-05-07'),
(23, 22, '554834571-0', '2023-01-05'),
(24, 12, '076139873-2', '2023-11-23'),
(25, 27, '029735996-7', '2021-01-01'),
(26, 27, '621351777-4', '2021-11-28'),
(27, 2, '519742752-3', '2023-10-14'),
(28, 3, '865992626-9', '2023-05-02'),
(29, 17, '721159171-4', '2022-12-09'),
(30, 28, '096393553-4', '2023-08-29'),
(31, 7, '659888255-9', '2021-11-01'),
(32, 8, '029735996-7', '2022-10-13'),
(33, 28, '202914985-3', '2022-05-02'),
(34, 30, '147312581-2', '2021-11-06'),
(35, 14, '173206567-5', '2023-04-22'),
(36, 2, '686247663-7', '2023-12-16'),
(37, 15, '250966766-1', '2021-04-22'),
(38, 16, '554834571-0', '2022-07-10'),
(39, 13, '950238142-4', '2022-09-09'),
(40, 4, '147312581-2', '2021-10-10');

INSERT INTO facturas (Id_Factura, Fk_Cliente, numero)
VALUES
(1, 6, 206713),
(2, 24, 631314),
(3, 4, 640065),
(4, 15, 472638),
(5, 18, 846897),
(6, 12, 852335),
(7, 17, 464308),
(8, 7, 78886),
(9, 15, 65535),
(10, 24, 903962),
(11, 30, 605040),
(12, 8, 515478),
(13, 30, 54142),
(14, 9, 86480),
(15, 1, 681522),
(16, 16, 7297),
(17, 20, 798999),
(18, 4, 913988),
(19, 10, 705017),
(20, 7, 561193),
(21, 25, 279519),
(22, 24, 246183),
(23, 28, 974548),
(24, 13, 842301),
(25, 25, 560259),
(26, 6, 922037),
(27, 18, 666813),
(28, 18, 805904),
(29, 8, 462386),
(30, 25, 183077),
(31, 12, 369923),
(32, 10, 756924),
(33, 11, 940413),
(34, 11, 810325),
(35, 4, 389324),
(36, 1, 619828),
(37, 29, 891084),
(38, 20, 303113),
(39, 27, 499915),
(40, 29, 152142);

---------------------------------------
--------------  VISTAS   -------------- 
---------------------------------------

-- Vista 1: Cantidad de ventas por libros.
CREATE OR REPLACE VIEW ventas_de_libros AS
(SELECT Fk_SKU, COUNT(*) AS N_Ventas
FROM Ventas
GROUP BY Fk_SKU 
HAVING Fk_SKU IN (SELECT Id_SKU_Libro FROM Libros));

-- Vista 2: Cantidad de ventas de vinilos.
CREATE OR REPLACE VIEW ventas_de_vinilos AS
(SELECT Fk_SKU, COUNT(*) AS N_Ventas
FROM Ventas
GROUP BY Fk_SKU 
HAVING Fk_SKU IN (SELECT Id_SKU_Vinilo FROM Vinilos));

-- Vista 3: Cantidad de ventas de revistas.
CREATE OR REPLACE VIEW ventas_de_revistas AS
(SELECT Fk_SKU, COUNT(*) AS N_Ventas
FROM Ventas
GROUP BY Fk_SKU
HAVING Fk_SKU IN (SELECT Id_SKU_Revista FROM Revistas));

-- Vista 4: Top 10 Autores de libros con más ventas
CREATE OR REPLACE VIEW top_10_ventas_autores_libros AS
(SELECT Fk_SKU, nombre, apellido, COUNT(*) AS cantidad_ventas
FROM ventas
INNER JOIN (
    SELECT autores.nombre AS nombre, autores.apellido AS apellido, libros.Id_SKU_Libro AS SKU
    FROM autores
    INNER JOIN libros ON autores.Id_Autor = libros.Fk_Autor
) AS sku_autores ON Fk_SKU = sku_autores.SKU
GROUP BY Fk_SKU, nombre, apellido
ORDER BY cantidad_ventas DESC
LIMIT 10);

-- Vista 5: Productos sin ventas
CREATE OR REPLACE VIEW sin_ventas AS
(SELECT Id_SKU, stock
FROM productos
WHERE Id_SKU NOT IN (
SELECT DISTINCT Fk_SKU
FROM Ventas));

--------------------------------------
-----------  FUNCIONES   -------------
--------------------------------------

-- Funcion N°1: Cuanto tiempo falta para que venza el contrato con algun proveedor.
DELIMITER $$
CREATE FUNCTION dias_vencimiento(fecha_vencimiento DATE) RETURNS INT
NO SQL
BEGIN
	DECLARE dias_faltantes INT;
    SET dias_faltantes = DATEDIFF(fecha_vencimiento, NOW());
    RETURN  dias_faltantes;
END $$


-- Funcion N°2: Cuanto dinero he ganado con un producto específico.
DELIMITER $$
CREATE FUNCTION ingreso_sku(sku VARCHAR(50)) RETURNS INT
NO SQL
BEGIN	
    DECLARE cantidad_venta INT;
    DECLARE precio_sku INT;
    SET cantidad_venta = 0;
    SET precio_sku = 0;
    -- cantidad de venta
    SELECT COUNT(*) INTO cantidad_venta
    FROM ventas
    WHERE Fk_SKU = sku;
    -- precio venta    
    SELECT precio INTO precio_sku
    FROM productos
    WHERE Id_SKU = sku; 
    RETURN cantidad_venta * precio_sku;
END $$


-------------------------------------------
-----------  STORE PROCEDURES   ----------- 
-------------------------------------------

-- Procedimiento almacenado 1: nos avisa a qué producto está asociado un SKU
DELIMITER //
CREATE PROCEDURE VerificarSKU(
    IN SKU_a_verificar VARCHAR(255)
)
BEGIN
    DECLARE existe_en_libros INT;
    DECLARE existe_en_revistas INT;
    DECLARE existe_en_vinilos INT;

    -- Verificar en libros
    SELECT COUNT(*) INTO existe_en_libros FROM libros WHERE Id_SKU_Libro = SKU_a_verificar;

    -- Verificar en revistas
    SELECT COUNT(*) INTO existe_en_revistas FROM revistas WHERE Id_SKU_Revista = SKU_a_verificar;

    -- Verificar en vinilos
    SELECT COUNT(*) INTO existe_en_vinilos FROM vinilos WHERE Id_SKU_Vinilo = SKU_a_verificar;

    -- Realizar acciones basadas en los resultados
    IF existe_en_libros > 0 THEN
        SELECT 'Libro';
    ELSEIF existe_en_revistas > 0 THEN
        SELECT 'Revista';
    ELSEIF existe_en_vinilos > 0 THEN
        -- Acciones si está en tabla_3
        SELECT 'ViniloVentasSobre';
    ELSE
        -- Acciones si no está en ninguna de las tablas
        SELECT 'SKU no encontrado';
    END IF;
END //
DELIMITER ;

-- Procedimiento almacenado 2: procedimiento que nos entrega una tabla de productos que vendieron más que un valor especifico
DELIMITER //
CREATE PROCEDURE VentasSobre(
    IN valor INT
)
BEGIN
	SET @ventas = CONCAT('SELECT Fk_SKU, COUNT(*) AS N_Ventas FROM Ventas GROUP BY Fk_SKU HAVING N_Ventas > ', valor);
    PREPARE runSQL FROM @ventas;
    EXECUTE runSQL;
    DEALLOCATE PREPARE runSQL;
END //

--------------------------------------
------------  TRIGGERS  --------------
--------------------------------------

--  Triggers 1: Respaldar la información que se inserte en la tabla productos
CREATE TABLE respaldo_productos (
	Id_SKU VARCHAR(50) PRIMARY KEY,
    precio DOUBLE NOT NULL,
    fecha_lanzamiento DATE,
    stock INT NOT NULL,
    usuario VARCHAR(255),
    fecha_modificacion DATETIME,
    accion VARCHAR(20)
);

CREATE TRIGGER productos_ai
AFTER INSERT ON productos
FOR EACH ROW
INSERT INTO respaldo_productos
VALUES (NEW.Id_SKU, NEW.precio, NEW.fecha_lanzamiento, NEW.stock, USER(), NOW(), 'INSERT');

--  Triggers 2: Respaldar la información antigua de productos en caso que eliminemos los datos

CREATE TRIGGER productos_ad
AFTER DELETE ON productos
FOR EACH ROW
INSERT INTO respaldo_productos
VALUES (OLD.Id_SKU, OLD.precio, OLD.fecha_lanzamiento, OLD.stock, USER(), NOW(), 'DROP');