-- v3: ENGLISH LOCALIZED VERSION

-- Drop tables if they exist (good for starting and testing)
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS books;
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS authors;
DROP TABLE IF EXISTS publishers;
DROP TABLE IF EXISTS categories;

-- Table: authors
CREATE TABLE authors (
    author_id INTEGER PRIMARY KEY AUTOINCREMENT,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL
);

-- Table: publishers
CREATE TABLE publishers (
    publisher_id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL UNIQUE,
    city TEXT
);

-- Table: categories
CREATE TABLE categories (
    category_id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL UNIQUE,
    description TEXT
);

-- Table: books
CREATE TABLE books (
    book_id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    author_id INTEGER,
    publisher_id INTEGER,
    category_id INTEGER,
    publication_year INTEGER,
    isbn TEXT UNIQUE,
    price REAL NOT NULL,
    stock_quantity INTEGER DEFAULT 0,
    FOREIGN KEY (author_id) REFERENCES authors(author_id),
    FOREIGN KEY (publisher_id) REFERENCES publishers(publisher_id),
    FOREIGN KEY (category_id) REFERENCES categories(category_id)
);

-- Table: customers
CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY AUTOINCREMENT,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE,
    phone TEXT,
    address TEXT,
    postal_code TEXT,
    city TEXT
);

-- Table: orders (header)
CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY AUTOINCREMENT,
    customer_id INTEGER NOT NULL,
    order_date TEXT NOT NULL, -- Format 'YYYY-MM-DD HH:MM:SS'
    status TEXT NOT NULL,
    total_value REAL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- Table: order_items (details)
CREATE TABLE order_items (
    item_id INTEGER PRIMARY KEY AUTOINCREMENT,
    order_id INTEGER NOT NULL,
    book_id INTEGER NOT NULL,
    quantity INTEGER NOT NULL,
    price_per_unit REAL NOT NULL, 
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (book_id) REFERENCES books(book_id)
);

----------------------------------------------------
-- POPULATING WITH DATA (INSERTS) v3
----------------------------------------------------

-- Authors
INSERT INTO authors (first_name, last_name) VALUES
('Olga', 'Tokarczuk'),      -- 1
('Andrzej', 'Sapkowski'),  -- 2
('Stanisław', 'Lem'),      -- 3
('Remigiusz', 'Mróz'),     -- 4
('J.K.', 'Rowling'),      -- 5
('Stephen', 'King'),       -- 6
('George', 'Orwell'),      -- 7
('J.R.R.', 'Tolkien'),     -- 8
('Adam', 'Mickiewicz'),    -- 9
('Yuval Noah', 'Harari'),  -- 10
('Bill', 'Bryson'),        -- 11
('Michelle', 'Obama'),     -- 12
('Walter', 'Isaacson');   -- 13

-- Publishers
INSERT INTO publishers (name, city) VALUES
('Literackie Publishing', 'Krakow'), -- 1
('SuperNOWA', 'Warsaw'),             -- 2
('Znak', 'Krakow'),                  -- 3
('Prószyński Media', 'Warsaw'),  -- 4
('Albatros', 'Warsaw'),              -- 5
('Media Rodzina', 'Poznan'),         -- 6
('Rebis', 'Poznan'),                 -- 7
('Helion', 'Gliwice'),               -- 8
('PWN', 'Warsaw'),                   -- 9
('Agora', 'Warsaw');                  -- 10

-- Categories
INSERT INTO categories (name, description) VALUES
('Fiction', 'Novels, short stories, literary classics.'), -- 1
('Fantasy', 'Magic, imaginary worlds, epic adventures.'), -- 2
('Science Fiction', 'Technology, the future, space.'),     -- 3
('Crime/Thriller', 'Mysteries, investigations, suspense.'), -- 4
('Non-Fiction', 'Reportage, biographies, essays.'),         -- 5
('Popular Science', 'Accessible explanations of scientific concepts.'), -- 6
('Guides', 'Self-development, hobbies, skills.'),        -- 7
('History', 'Books about the past, historical analysis.'),  -- 8
('Poetry', 'Collections of poems.');                        -- 9

-- Books (50 titles, localized)
INSERT INTO books (title, author_id, publisher_id, category_id, publication_year, isbn, price, stock_quantity) VALUES
('The Books of Jacob', 1, 1, 1, 2014, '978-83-08-04939-6', 79.90, 50), -- 1
('The Witcher: The Last Wish', 2, 2, 2, 1993, '978-83-7054-061-6', 44.90, 150), -- 2
('Solaris', 3, 1, 3, 1961, '978-83-08-04983-9', 39.90, 75), -- 3
('The Behaviorist', 4, 3, 4, 2016, '978-83-280-2713-3', 34.90, 200), -- 4
('Harry Potter and the Philosopher''s Stone', 5, 6, 2, 1997, '978-83-7278-000-0', 39.00, 300), -- 5
('The Shining', 6, 5, 4, 1977, '978-83-7659-450-4', 49.90, 60), -- 6
('Primeval and Other Times', 1, 1, 1, 1996, '978-83-08-04940-2', 32.50, 40), -- 7
('The Invincible', 3, 1, 3, 1964, '978-83-08-06180-0', 29.90, 80), -- 8
('Cassation', 4, 3, 4, 2015, '978-83-280-1512-3', 34.90, 120), -- 9
('1984', 7, 5, 3, 1949, '978-83-7659-865-6', 35.00, 110), -- 10
('The Fellowship of the Ring', 8, 5, 2, 1954, '978-83-7359-006-2', 54.90, 90), -- 11
('Pan Tadeusz', 9, 3, 9, 1834, '978-83-240-0034-7', 24.90, 50), -- 12
('Sapiens: A Brief History of Humankind', 10, 1, 6, 2011, '978-83-08-06318-7', 59.90, 130), -- 13
('A Short History of Nearly Everything', 11, 7, 6, 2003, '978-83-7506-185-7', 49.90, 70), -- 14
('Becoming', 12, 10, 5, 2018, '978-83-268-2715-6', 54.90, 100), -- 15
('Steve Jobs', 13, 3, 5, 2011, '978-83-240-1913-4', 69.90, 60), -- 16
('The Green Mile', 6, 5, 1, 1996, '978-83-7659-807-6', 42.90, 85), -- 17
('Homo Deus: A Brief History of Tomorrow', 10, 1, 6, 2015, '978-83-08-06631-7', 59.90, 115), -- 18
('Animal Farm', 7, 5, 1, 1945, '978-83-7659-866-3', 29.90, 95), -- 19
('Flights', 1, 1, 1, 2007, '978-83-08-06066-7', 39.90, 65), -- 20
('The Witcher: Sword of Destiny', 2, 2, 2, 1992, '978-83-7054-062-3', 44.90, 140), -- 21
('The Fables for Robots', 3, 1, 3, 1964, '978-83-08-04984-6', 34.90, 70), -- 22
('Revision', 4, 3, 4, 2016, '978-83-280-3663-0', 34.90, 110), -- 23
('Harry Potter and the Chamber of Secrets', 5, 6, 2, 1998, '978-83-7278-001-7', 39.00, 250), -- 24
('It', 6, 5, 4, 1986, '978-83-7659-451-1', 59.90, 55), -- 25
('The Two Towers', 8, 5, 2, 1954, '978-83-7359-007-9', 54.90, 88), -- 26
('The Return of the King', 8, 5, 2, 1955, '978-83-7359-008-6', 54.90, 85), -- 27
('The Hobbit', 8, 5, 2, 1937, '978-83-7359-005-5', 39.90, 130), -- 28
('21 Lessons for the 21st Century', 10, 1, 6, 2018, '978-83-08-06899-1', 59.90, 105), -- 29
('The Body: A Guide for Occupants', 11, 7, 6, 2019, '978-83-8188-062-1', 54.90, 60), -- 30
('Elon Musk: Tesla, SpaceX, and the Quest for a Fantastic Future', 13, 3, 5, 2015, '978-83-240-3522-6', 49.90, 70), -- 31
('Inwigilacja (Surveillance)', 4, 3, 4, 2017, '978-83-280-4573-1', 34.90, 90), -- 32
('The Stand', 6, 5, 2, 1978, '978-83-7659-452-8', 64.90, 45), -- 33
('The Witcher: Blood of Elves', 2, 2, 2, 1994, '978-83-7054-063-0', 44.90, 130), -- 34
('The Star Diaries', 3, 1, 3, 1957, '978-83-08-04985-3', 39.90, 60), -- 35
('Harry Potter and the Prisoner of Azkaban', 5, 6, 2, 1999, '978-83-7278-002-4', 39.00, 230), -- 36
('Votum', 4, 3, 4, 2017, '978-83-280-5157-2', 34.90, 100), -- 37
('The Outsider', 6, 5, 4, 2018, '978-83-7659-994-3', 44.90, 75), -- 38
('The Tender Narrator', 1, 1, 5, 2020, '978-83-08-07310-0', 39.90, 80), -- 39
('The Witcher: Time of Contempt', 2, 2, 2, 1995, '978-83-7054-064-7', 44.90, 125), -- 40
('The Cyberiad', 3, 1, 3, 1965, '978-83-08-04986-0', 39.90, 65), -- 41
('Harry Potter and the Goblet of Fire', 5, 6, 2, 2000, '978-83-7278-003-1', 49.00, 210), -- 42
('Misery', 6, 5, 4, 1987, '978-83-7659-453-5', 42.90, 70), -- 43
('The Witcher: Baptism of Fire', 2, 2, 2, 1996, '978-83-7054-065-4', 44.90, 120), -- 44
('Harry Potter and the Order of the Phoenix', 5, 6, 2, 2003, '978-83-7278-088-8', 59.00, 180), -- 45
('The Witcher: The Tower of the Swallow', 2, 2, 2, 1997, '978-83-7054-066-1', 44.90, 115), -- 46
('Harry Potter and the Half-Blood Prince', 5, 6, 2, 2005, '978-83-7278-164-9', 54.00, 170), -- 47
('The Witcher: The Lady of the Lake', 2, 2, 2, 1999, '978-83-7054-067-8', 44.90, 110), -- 48
('Harry Potter and the Deathly Hallows', 5, 6, 2, 2007, '978-83-7278-228-8', 59.00, 200), -- 49
('The Witcher: Season of Storms', 2, 2, 2, 2013, '978-83-7578-059-8', 44.90, 160); -- 50

-- Customers (15 records, localized)
INSERT INTO customers (first_name, last_name, email, phone, address, postal_code, city) VALUES
('John', 'Smith', 'john.smith@example.com', '123456789', '1 Simple St', '00-001', 'Warsaw'), -- 1
('Jane', 'Doe', 'jane.doe@example.com', '987654321', '15 Crooked St Apt 3', '31-001', 'Krakow'), -- 2
('Peter', 'Wisniewski', 'peter.wisniewski@example.com', '555444333', '42 Long St', '80-831', 'Gdansk'), -- 3
('Maria', 'Jones', 'maria.jones@example.com', NULL, '8 Short St', '61-812', 'Poznan'), -- 4
('Thomas', 'Green', 'thomas.green@example.com', '601602603', '23 Forest St', '50-555', 'Wroclaw'), -- 5
('Kate', 'Wojcik', 'kate.wojcik@example.com', '700800900', '12 Sunny St', '00-010', 'Warsaw'), -- 6
('Mark', 'Brown', 'mark.brown@example.com', NULL, '100 Sea St', '81-001', 'Gdynia'), -- 7
('Agnieszka', 'Kaminska', 'aga.kaminska@example.com', '500501502', '7 Field St', '61-800', 'Poznan'), -- 8
('Paul', 'Szymanski', 'paul.sz@example.com', '666777888', '50 Wroclaw St', '30-011', 'Krakow'), -- 9
('Barbara', 'Miller', 'barbara.miller@example.com', '111222333', '19 Beautiful St', '00-549', 'Warsaw'), -- 10
('Chris', 'Kowalczyk', 'chris.kowalczyk@example.com', NULL, '3 Garden St', '90-001', 'Lodz'), -- 11
('Ewa', 'Mazur', 'ewa.mazur@example.com', '222333444', '5 Rose St', '50-500', 'Wroclaw'), -- 12
('Gregory', 'Grabowski', 'gregory.grab@example.com', '333444555', '11 Green St', '80-800', 'Gdansk'), -- 13
('Elizabeth', 'Zajac', 'ela.zajac@example.com', '444555666', '1 Park St', '40-001', 'Katowice'), -- 14
('Robert', 'King', 'robert.king@example.com', '555666777', '30 Lipowa St', '20-001', 'Lublin'); -- 15

-- Orders (25 records, localized status)
INSERT INTO orders (customer_id, order_date, status, total_value) VALUES
(1, '2024-09-15 10:30:00', 'Shipped', 84.80), -- 1
(2, '2024-09-18 14:22:00', 'Processing', 69.80), -- 2
(1, '2024-09-20 21:05:00', 'New', 39.00), -- 3
(3, '2024-09-21 09:15:00', 'Cancelled', 49.90), -- 4
(4, '2024-09-21 11:00:00', 'Shipped', 62.40), -- 5
(5, '2024-09-22 12:00:00', 'Shipped', 154.70), -- 6
(6, '2024-09-23 08:10:00', 'New', 35.00), -- 7
(7, '2024-09-23 15:45:00', 'Shipped', 89.80), -- 8
(8, '2024-09-24 10:20:00', 'Processing', 54.90), -- 9
(9, '2024-09-25 11:11:00', 'Shipped', 127.70), -- 10
(10, '2024-09-26 13:00:00', 'New', 44.90), -- 11
(11, '2024-09-27 16:30:00', 'Shipped', 99.80), -- 12
(12, '2024-09-28 18:00:00', 'Shipped', 119.80), -- 13
(13, '2024-09-29 20:00:00', 'Cancelled', 34.90), -- 14
(14, '2024-09-30 09:05:00', 'Shipped', 164.70), -- 15
(15, '2024-10-01 12:15:00', 'New', 44.90), -- 16
(1, '2024-10-02 14:30:00', 'Processing', 99.70), -- 17
(2, '2024-10-03 07:00:00', 'Shipped', 109.80), -- 18
(5, '2024-10-04 11:45:00', 'New', 59.90), -- 19
(8, '2024-10-05 13:20:00', 'Shipped', 144.70), -- 20
(10, '2024-10-05 17:00:00', 'Shipped', 87.80), -- 21
(3, '2024-10-06 19:30:00', 'Processing', 84.80), -- 22
(7, '2024-10-07 22:00:00', 'New', 44.90), -- 23
(12, '2024-10-08 10:10:00', 'Shipped', 338.00), -- 24
(15, '2024-10-08 12:00:00', 'Shipped', 108.00); -- 25

-- Order Items

-- Order 1 (Customer 1): 1x The Witcher (44.90) + 1x Solaris (39.90) = 84.80
INSERT INTO order_items (order_id, book_id, quantity, price_per_unit) VALUES
(1, 2, 1, 44.90),
(1, 3, 1, 39.90);
-- Order 2 (Customer 2): 2x The Behaviorist (2 * 34.90) = 69.80
INSERT INTO order_items (order_id, book_id, quantity, price_per_unit) VALUES
(2, 4, 2, 34.90);
-- Order 3 (Customer 1): 1x Harry Potter (39.00) = 39.00
INSERT INTO order_items (order_id, book_id, quantity, price_per_unit) VALUES
(3, 5, 1, 39.00);
-- Order 4 (Customer 3): 1x The Shining (49.90) [Cancelled]
INSERT INTO order_items (order_id, book_id, quantity, price_per_unit) VALUES
(4, 6, 1, 49.90);
-- Order 5 (Customer 4): 1x Primeval (32.50) + 1x The Invincible (29.90) = 62.40
INSERT INTO order_items (order_id, book_id, quantity, price_per_unit) VALUES
(5, 7, 1, 32.50),
(5, 8, 1, 29.90);
-- Order 6 (Customer 5): 1x Sapiens (59.90) + 1x Steve Jobs (69.90) + 1x Pan Tadeusz (24.90) = 154.70
INSERT INTO order_items (order_id, book_id, quantity, price_per_unit) VALUES
(6, 13, 1, 59.90),
(6, 16, 1, 69.90),
(6, 12, 1, 24.90);
-- Order 7 (Customer 6): 1x 1984 (35.00)
INSERT INTO order_items (order_id, book_id, quantity, price_per_unit) VALUES
(7, 10, 1, 35.00);
-- Order 8 (Customer 7): 2x Sword of Destiny (2 * 44.90) = 89.80
INSERT INTO order_items (order_id, book_id, quantity, price_per_unit) VALUES
(8, 21, 2, 44.90);
-- Order 9 (Customer 8): 1x Becoming (54.90)
INSERT INTO order_items (order_id, book_id, quantity, price_per_unit) VALUES
(9, 15, 1, 54.90);
-- Order 10 (Customer 9): 1x Green Mile (42.90) + 1x Fellowship of the Ring (54.90) + 1x Animal Farm (29.90) = 127.70
INSERT INTO order_items (order_id, book_id, quantity, price_per_unit) VALUES
(10, 17, 1, 42.90),
(10, 11, 1, 54.90),
(10, 19, 1, 29.90);
-- Order 11 (Customer 10): 1x Blood of Elves (44.90)
INSERT INTO order_items (order_id, book_id, quantity, price_per_unit) VALUES
(11, 34, 1, 44.90);
-- Order 12 (Customer 11): 2x A Short History... (2 * 49.90) = 99.80
INSERT INTO order_items (order_id, book_id, quantity, price_per_unit) VALUES
(12, 14, 2, 49.90);
-- Order 13 (Customer 12): 2x Homo Deus (2 * 59.90) = 119.80
INSERT INTO order_items (order_id, book_id, quantity, price_per_unit) VALUES
(13, 18, 2, 59.90);
-- Order 14 (Customer 13): 1x Revision (34.90) [Cancelled]
INSERT INTO order_items (order_id, book_id, quantity, price_per_unit) VALUES
(14, 23, 1, 34.90);
-- Order 15 (Customer 14): 1x It (59.90) + 1x The Stand (64.90) + 1x The Hobbit (39.90) = 164.70
INSERT INTO order_items (order_id, book_id, quantity, price_per_unit) VALUES
(15, 25, 1, 59.90),
(15, 33, 1, 64.90),
(15, 28, 1, 39.90);
-- Order 16 (Customer 15): 1x Time of Contempt (44.90)
INSERT INTO order_items (order_id, book_id, quantity, price_per_unit) VALUES
(16, 40, 1, 44.90);
-- Order 17 (Customer 1): 2x Fables for Robots (2 * 34.90) + 1x The Invincible (29.90) = 99.70
INSERT INTO order_items (order_id, book_id, quantity, price_per_unit) VALUES
(17, 22, 2, 34.90),
(17, 8, 1, 29.90);
-- Order 18 (Customer 2): 1x The Two Towers (54.90) + 1x Return of the King (54.90) = 109.80
INSERT INTO order_items (order_id, book_id, quantity, price_per_unit) VALUES
(18, 26, 1, 54.90),
(18, 27, 1, 54.90);
-- Order 19 (Customer 5): 1x 21 Lessons (59.90)
INSERT INTO order_items (order_id, book_id, quantity, price_per_unit) VALUES
(19, 29, 1, 59.90);
-- Order 20 (Customer 8): 1x The Body (54.90) + 1x Elon Musk (49.90) + 1x Tender Narrator (39.90) = 144.70
INSERT INTO order_items (order_id, book_id, quantity, price_per_unit) VALUES
(20, 30, 1, 54.90),
(20, 31, 1, 49.90),
(20, 39, 1, 39.90);
-- Order 21 (Customer 10): 1x The Outsider (44.90) + 1x Misery (42.90) = 87.80
INSERT INTO order_items (order_id, book_id, quantity, price_per_unit) VALUES
(21, 38, 1, 44.90),
(21, 43, 1, 42.90);
-- Order 22 (Customer 3): 1x The Cyberiad (39.90) + 1x Lady of the Lake (44.90) = 84.80
INSERT INTO order_items (order_id, book_id, quantity, price_per_unit) VALUES
(22, 41, 1, 39.90),
(22, 48, 1, 44.90);
-- Order 23 (Customer 7): 1x Season of Storms (44.90)
INSERT INTO order_items (order_id, book_id, quantity, price_per_unit) VALUES
(23, 50, 1, 44.90);
-- Order 24 (Customer 12): Complete Harry Potter Saga (7 books) = 338.00
INSERT INTO order_items (order_id, book_id, quantity, price_per_unit) VALUES
(24, 5, 1, 39.00),  -- HP 1
(24, 24, 1, 39.00), -- HP 2
(24, 36, 1, 39.00), -- HP 3
(24, 42, 1, 49.00), -- HP 4
(24, 45, 1, 59.00), -- HP 5
(24, 47, 1, 54.00), -- HP 6
(24, 49, 1, 59.00); -- HP 7
-- Order 25 (Customer 15): 1x Goblet of Fire (49.00) + 1x Deathly Hallows (59.00) = 108.00
INSERT INTO order_items (order_id, book_id, quantity, price_per_unit) VALUES
(25, 42, 1, 49.00),
(25, 49, 1, 59.00);

-- The INSERT statements for 'orders' already have the correct 'total_value', 
-- so the UPDATE statements below are redundant, but left for safety.
-- Fixing section (for safety, but values are already correct in the INSERTS above)
UPDATE orders SET total_value = 154.70 WHERE order_id = 6;
UPDATE orders SET total_value = 89.80 WHERE order_id = 8;
UPDATE orders SET total_value = 127.70 WHERE order_id = 10;
UPDATE orders SET total_value = 164.70 WHERE order_id = 15;
UPDATE orders SET total_value = 99.70 WHERE order_id = 17;
UPDATE orders SET total_value = 109.80 WHERE order_id = 18;
UPDATE orders SET total_value = 144.70 WHERE order_id = 20;
UPDATE orders SET total_value = 87.80 WHERE order_id = 21;
UPDATE orders SET total_value = 84.80 WHERE order_id = 22;
UPDATE orders SET total_value = 338.00 WHERE order_id = 24;
UPDATE orders SET total_value = 108.00 WHERE order_id = 25;