CREATE TABLE asegurado (
    rut_a VARCHAR (100) PRIMARY KEY,
    nombre VARCHAR (100),
    apellido VARCHAR (100)
);

CREATE TABLE vehiculo (
    patente VARCHAR (100) PRIMARY KEY,
    marca VARCHAR (100),
    tipo VARCHAR (100),
    kilometraje INT,
    ano DATE,
    rut_a VARCHAR (100),
    FOREIGN KEY (rut_a) REFERENCES asegurado (rut_a)
);

CREATE TABLE multa (
    cod_multa INT PRIMARY KEY,
    causa VARCHAR (100),
    fecha DATE,
    patente VARCHAR (100),
    FOREIGN KEY (patente) REFERENCES vehiculo(patente)
);

CREATE TABLE telefono_aseg (
    rut_a VARCHAR(100),
    telefono VARCHAR(20),
    PRIMARY KEY (rut_a, telefono),
    FOREIGN KEY (rut_a) REFERENCES asegurado (rut_a)
);

CREATE TABLE accidente (
    id_acci INT PRIMARY KEY,
    calle_a VARCHAR (100),
    fecha DATE,
    n_ref INT,
    lat_lon VARCHAR (100),
    comuna VARCHAR (100),
    region VARCHAR (100)
);

CREATE TABLE chofer (
    rut_c VARCHAR (100) PRIMARY KEY,
    nombre_c VARCHAR (100),
    apellido_c VARCHAR (100),
    motivo VARCHAR (100),
    cant_parte VARCHAR (100)
);

CREATE TABLE chofer_vehiculo (
    patente VARCHAR (100),
    rut_c VARCHAR (100),
    PRIMARY KEY (patente, rut_c),
    FOREIGN KEY (patente) REFERENCES vehiculo (patente),
    FOREIGN KEY (rut_c) REFERENCES chofer (rut_c)
);

CREATE TABLE persona_afec (
    rut_afec VARCHAR (100) PRIMARY KEY,
    nombre_afec VARCHAR (100),
    apellido_afec VARCHAR (100),
    calidad_acc VARCHAR (100),
    rut_a VARCHAR (100),
    id_acci INT,
    FOREIGN KEY (rut_a) REFERENCES asegurado (rut_a),
    FOREIGN KEY (id_acci) REFERENCES accidente (id_acci)
);

CREATE TABLE vehiculo_acci (
    patente VARCHAR (100),
    id_acci INT,
    PRIMARY KEY (patente, id_acci),
    FOREIGN KEY (patente) REFERENCES vehiculo (patente),
    FOREIGN KEY (id_acci) REFERENCES accidente (id_acci)
);

CREATE TABLE status_acc (
    cod_leccion INT PRIMARY KEY,
    categoria VARCHAR (100),
    cantidad INT,
    id_acci INT,
    FOREIGN KEY (id_acci) REFERENCES accidente (id_acci)
);
INSERT INTO asegurado (rut_a, nombre, apellido) VALUES
(22332314, 'juan', 'hodges'),
(74729833, 'salvador', 'nieto'),
(82823736, 'matias', 'castro');
SELECT * FROM asegurado;

INSERT INTO vehiculo (patente, marca, tipo, kilometraje, ano, rut_a) VALUES
('wfa339jhd', 'subaru', 'vehiculo', 0, '2026-01-01', 22332314),
('grs232afd', 'toyota', 'camioneta', 270000, '2001-09-01', 74729833),
('333sdd212', 'mercedes', 'bus', 10000, '1997-01-04', 82823736);
SELECT * FROM vehiculo;

INSERT INTO multa (cod_multa, causa, fecha, patente) VALUES
(1, 'Exceso de velocidad', '2026-09-10', 'wfa339jhd'),
(2, 'Estacionar en lugar prohibido', '2026-09-12', 'grs232afd'),
(3, 'No respetar señal de tránsito', '2026-09-15', '333sdd212');
SELECT * FROM multa;

INSERT INTO telefono_aseg (rut_a, telefono) VALUES
('22332314', '912345678'),
('74729833', '987654321'),
('82823736', '956789123');
SELECT * FROM telefono_aseg;

INSERT INTO accidente ( id_acci, calle_a, fecha, n_ref, lat_lon, comuna, region) VALUES
(1, 'Av. Providencia', '2026-09-10', 12345, '-33.4315,-70.6090', 'Providencia', 'Metropolitana'),
(2, 'Av. Grecia', '2026-09-12', 67890, '-33.4645,-70.6012', 'Ñuñoa', 'Metropolitana'),
(3, 'Av. Vicuña Mackenna', '2026-09-15', 45678, '-33.4725,-70.6220', 'La Florida', 'Metropolitana');
SELECT * FROM accidente; 

INSERT INTO chofer (rut_c, nombre_c, apellido_c, motivo, cant_parte) VALUES
(19345678, 'Pedro', 'Gonzalez', 'Exceso de velocidad', 2),
(18456789, 'Carlos', 'Martinez', 'Estacionamiento indebido', 1),
(17654321, 'Diego', 'Soto', 'No respetar señal de tránsito', 3);
SELECT * FROM chofer;

INSERT INTO chofer_vehiculo (patente, rut_c) VALUES
('wfa339jhd', 19345678),
('grs232afd', 18456789),
('333sdd212', 17654321);
SELECT * FROM chofer_vehiculo;

INSERT INTO persona_afec (rut_afec, nombre_afec, apellido_afec, calidad_acc, rut_a, id_acci) VALUES
(20123456, 'Maria', 'Perez', 'Pasajera', 22332314, 1),
(19876543, 'Jose', 'Ramirez', 'Peaton', 74729833, 2),
(21234567, 'Ana', 'Torres', 'Conductora', 82823736, 3);
SELECT * FROM persona_afec;

INSERT INTO vehiculo_acci (patente, id_acci) VALUES
('wfa339jhd', 1),
('grs232afd', 2),
('333sdd212', 3);
SELECT * FROM vehiculo_acci;

INSERT INTO status_acc (cod_leccion, categoria, cantidad, id_acci) VALUES
(1, 'Leve', 2, 1),
(2, 'Moderado', 3, 2),
(3, 'Grave', 1, 3);
SELECT * FROM status_acc;


SELECT COUNT(*) AS cantidad_vehiculos
FROM vehiculo
WHERE kilometraje > 500;

SELECT COUNT(*) AS cantidad_multas
FROM multa
WHERE causa = 'Exceso de velocidad';

SELECT va.id_acci, v.patente, v.marca
FROM vehiculo_acci va
JOIN vehiculo v ON va.patente = v.patente;

SELECT v.patente, c.nombre_c, c.apellido_c
FROM chofer_vehiculo cv
JOIN vehiculo v ON cv.patente = v.patente
JOIN chofer c ON cv.rut_c = c.rut_c;

SELECT v.patente, v.marca, m.causa, m.fecha
FROM vehiculo v
JOIN multa m ON v.patente = m.patente;