---

-- ========== ЗАДАНИЯ 1-2 ==========
PRAGMA foreign_keys = ON;

-- ========== ЗАДАНИЯ 3-5 ==========
DROP TABLE IF EXISTS fine;
DROP TABLE IF EXISTS rental;
DROP TABLE IF EXISTS car;
DROP TABLE IF EXISTS user_account;

CREATE TABLE user_account (
    user_id INTEGER PRIMARY KEY AUTOINCREMENT,
    full_name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE,
    phone TEXT UNIQUE,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- ========== ЗАДАНИЯ 6-9 ==========
CREATE TABLE car (
    car_id INTEGER PRIMARY KEY AUTOINCREMENT,
    plate_no TEXT NOT NULL UNIQUE,
    brand TEXT NOT NULL,
    model TEXT NOT NULL,
    year INTEGER NOT NULL CHECK (year BETWEEN 2000 AND 2026),
    odometer_km INTEGER NOT NULL DEFAULT 0 CHECK (odometer_km > 0)
);

-- ========== ЗАДАНИЯ 10-17 ==========
CREATE TABLE rental (
    rental_id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    car_id INTEGER NOT NULL,
    start_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    end_at TEXT,
    status TEXT NOT NULL CHECK (status IN ('NEW', 'ACTIVE', 'FINISHED', 'CANCELLED')),
    cost_amount NUMERIC NOT NULL DEFAULT 0 CHECK (cost_amount >= 0),
    FOREIGN KEY (user_id) REFERENCES user_account(user_id),
    FOREIGN KEY (car_id) REFERENCES car(car_id),
    CHECK (end_at IS NULL OR end_at > start_at)
);

-- ========== ЗАДАНИЯ 18-21 ==========
CREATE TABLE fine (
    fine_id INTEGER PRIMARY KEY AUTOINCREMENT,
    rental_id INTEGER NOT NULL,
    amount NUMERIC NOT NULL CHECK (amount >= 0),
    reason TEXT NOT NULL,
    FOREIGN KEY (rental_id) REFERENCES rental(rental_id)
);

-- ========== ЗАДАНИЕ 22 ==========
PRAGMA table_info(user_account);
PRAGMA table_info(car);
PRAGMA table_info(rental);
PRAGMA table_info(fine);

-- ========== ЗАДАНИЕ 23 ==========
PRAGMA foreign_key_list(rental);
PRAGMA foreign_key_list(fine);

-- ========== ЗАДАНИЕ 24 ==========
INSERT INTO user_account (full_name, email, phone) VALUES 
    ('Иван Петров', 'ivan@example.com', '+7-900-123-45-67'),
    ('Мария Сидорова', 'maria@example.com', '+7-900-987-65-43');

DELETE FROM user_account WHERE email = 'maria@example.com';

-- ========== ЗАДАНИЕ 25 ==========
INSERT INTO car (plate_no, brand, model, year, odometer_km) VALUES 
    ('A123BC', 'Toyota', 'Camry', 2020, 15000),
    ('B456DE', 'Honda', 'Civic', 2022, 5000);

-- ========== ЗАДАНИЕ 26 ==========
INSERT INTO rental (user_id, car_id, status, cost_amount) VALUES 
    (1, 1, 'ACTIVE', 5000);

-- ========== ЗАДАНИЕ 27 ==========
INSERT INTO rental (user_id, car_id, start_at, end_at, status, cost_amount) VALUES 
    (1, 2, '2025-01-01 10:00:00', '2025-01-05 18:00:00', 'FINISHED', 20000);

-- ========== ЗАДАНИЕ 28 ==========
INSERT INTO fine (rental_id, amount, reason) VALUES 
    (2, 500, 'Возврат с опозданием');

-- ========== ЗАДАНИЕ 29 ==========
-- INSERT INTO user_account (full_name, email, phone) VALUES 
--     ('Петр Сидоров', 'ivan@example.com', '+7-900-111-22-33');

-- ========== ЗАДАНИЕ 30 ==========
-- INSERT INTO car (plate_no, brand, model, year, odometer_km) VALUES 
--     ('C789FG', 'Ford', 'Focus', 1999, 10000);

-- ========== ЗАДАНИЕ 31 ==========
-- INSERT INTO car (plate_no, brand, model, year, odometer_km) VALUES 
--     ('C789FG', 'Ford', 'Focus', 2015, -10);

-- ========== ЗАДАНИЕ 32 ==========
-- INSERT INTO rental (user_id, car_id, status, cost_amount) VALUES 
--     (1, 1, 'IN_PROGRESS', 3000);

-- ========== ЗАДАНИЕ 33 ==========
-- INSERT INTO rental (user_id, car_id, start_at, end_at, status, cost_amount) VALUES 
--     (1, 1, '2025-02-01 10:00:00', '2025-01-30 18:00:00', 'FINISHED', 10000);

-- ========== ЗАДАНИЕ 34 ==========
-- INSERT INTO fine (rental_id, amount, reason) VALUES 
--     (999, 1000, 'Штраф за несуществующую аренду');

-- ========== ЗАДАНИЕ 35 ==========
-- Итоговый SQL-файл сохранён