-- SQLite script to create tables and import JSON data
-- Usage: sqlite3 database.db < script.sql

-- Enable foreign key constraints
PRAGMA foreign_keys = ON;

-- Drop tables if they exist (for clean re-runs)
DROP TABLE IF EXISTS user;
DROP TABLE IF EXISTS address;
DROP TABLE IF EXISTS company;

-- Create company table
CREATE TABLE company (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    catch_phrase TEXT,
    bs TEXT
);

-- Create address table
CREATE TABLE address (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    street TEXT NOT NULL,
    suite TEXT,
    city TEXT NOT NULL,
    zipcode TEXT,
    lat TEXT,
    lng TEXT
);

-- Create user table
CREATE TABLE user (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    username TEXT UNIQUE NOT NULL,
    email TEXT UNIQUE NOT NULL,
    address_id INTEGER NOT NULL,
    phone TEXT,
    website TEXT,
    company_id INTEGER NOT NULL,
    FOREIGN KEY (address_id) REFERENCES address(id) ON DELETE CASCADE,
    FOREIGN KEY (company_id) REFERENCES company(id) ON DELETE CASCADE
);

-- Create indexes for better query performance
CREATE INDEX idx_user_username ON user(username);
CREATE INDEX idx_user_email ON user(email);
CREATE INDEX idx_user_address ON user(address_id);
CREATE INDEX idx_user_company ON user(company_id);

-- Import data using JSON functions
-- Insert companies
INSERT INTO company (name, catch_phrase, bs)
SELECT DISTINCT
    json_extract(value, '$.company.name'),
    json_extract(value, '$.company.catchPhrase'),
    json_extract(value, '$.company.bs')
FROM json_each(readfile('users.json'));

-- Insert addresses
INSERT INTO address (street, suite, city, zipcode, lat, lng)
SELECT
    json_extract(value, '$.address.street'),
    json_extract(value, '$.address.suite'),
    json_extract(value, '$.address.city'),
    json_extract(value, '$.address.zipcode'),
    json_extract(value, '$.address.geo.lat'),
    json_extract(value, '$.address.geo.lng')
FROM json_each(readfile('users.json'));

-- Insert users with foreign key references
INSERT INTO user (id, name, username, email, address_id, phone, website, company_id)
SELECT
    json_extract(value, '$.id'),
    json_extract(value, '$.name'),
    json_extract(value, '$.username'),
    json_extract(value, '$.email'),
    (SELECT a.id FROM address a 
     WHERE a.street = json_extract(value, '$.address.street')
     AND a.city = json_extract(value, '$.address.city')
     LIMIT 1),
    json_extract(value, '$.phone'),
    json_extract(value, '$.website'),
    (SELECT c.id FROM company c 
     WHERE c.name = json_extract(value, '$.company.name')
     LIMIT 1)
FROM json_each(readfile('users.json'));

-- Display summary
SELECT 'Data import completed successfully!' as status;
SELECT 'Total users: ' || COUNT(*) as summary FROM user;
SELECT 'Total addresses: ' || COUNT(*) as summary FROM address;
SELECT 'Total companies: ' || COUNT(*) as summary FROM company;

-- Show sample data
SELECT 'Sample data from user table:' as info;
SELECT u.id, u.name, u.email, c.name as company, a.city 
FROM user u
JOIN company c ON u.company_id = c.id
JOIN address a ON u.address_id = a.id
LIMIT 5;