-- DECIMAL for precise money storage
CREATE TABLE accounts (
    id INTEGER PRIMARY KEY,
    balance DECIMAL(12, 2)  -- Up to 12 digits, 2 decimal places
);

-- FLOAT for approximate storage
CREATE TABLE measurements (
    id INTEGER PRIMARY KEY,
    temperature REAL  -- Approximate value, may have minor errors
);

CREATE TABLE articles (
    id INTEGER PRIMARY KEY,
    title VARCHAR(200),    -- Title, max 200 characters
    status CHAR(1),         -- Status: D=Draft, P=Published
    content TEXT             -- Body, unlimited length
);

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    event_name TEXT,
    event_date DATE,              -- Only care about the date
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP  -- Auto-record creation time
);

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    is_active BOOLEAN DEFAULT 1  -- 1=enabled, 0=disabled
);

-- Insert boolean values
INSERT INTO users (name, is_active) VALUES ('John', TRUE);   -- Stored as 1
INSERT INTO users (name, is_active) VALUES ('Jane', FALSE);  -- Stored as 0

-- Query using boolean values
SELECT * FROM users WHERE is_active = 1;

-- Any operation involving NULL results in NULL
SELECT NULL + 1;        -- Result: NULL
SELECT NULL || 'hello'; -- Result: NULL

-- To check for NULL, you must use IS / IS NOT
SELECT * FROM employees WHERE department_id IS NULL;     -- ✅ Correct
SELECT * FROM employees WHERE department_id = NULL;      -- ❌ Always returns empty
SELECT * FROM employees WHERE department_id IS NOT NULL; -- ✅ Correct

-- Create products table with specified column types
CREATE TABLE products_demo (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    stock INTEGER DEFAULT 0,
    description TEXT,
    is_active BOOLEAN DEFAULT 1,
    created_at DATE
);

-- Insert data of various types
INSERT INTO products_demo (name, price, stock, description, is_active, created_at)
VALUES ('Mechanical Keyboard', 299.50, 100, 'Blue switch 87-key mechanical keyboard, great for typing', TRUE, '2024-01-15');

INSERT INTO products_demo (name, price, stock, description, is_active, created_at)
VALUES ('Wireless Mouse', 89.00, 200, NULL, TRUE, '2024-02-20');

-- View data and type behavior
SELECT name, price, stock, description, is_active, created_at
FROM products_demo;

name              price    stock  description                                   is_active  created_at
----------------  -------  -----  ---------------------------------------------  ---------  ----------
Mechanical Keyboard 299.50 100    Blue switch 87-key mechanical keyboard...       1          2024-01-15
Wireless Mouse    89.00    200    (NULL)                                         1          2024-02-20

-- Create test table
CREATE TABLE null_test (
    id INTEGER PRIMARY KEY,
    value INTEGER
);

INSERT INTO null_test VALUES (1, 10);
INSERT INTO null_test VALUES (2, NULL);
INSERT INTO null_test VALUES (3, 20);

-- NULL in arithmetic operations
SELECT id, value, value + 100 AS add_result
FROM null_test;

id  value  add_result
--  -----  ----------
1   10     110
2   (NULL) (NULL)
3   20     120

-- Checking for NULL: IS vs =
SELECT id, value,
    CASE WHEN value IS NULL THEN 'Is NULL' ELSE 'Not NULL' END AS check_result
FROM null_test;

id  value  check_result
--  -----  ------------
1   10     Not NULL
2   (NULL) Is NULL
3   20     Not NULL

-- NULL and aggregate functions: COUNT excludes NULL, SUM/AVG ignore NULL
SELECT COUNT(value) AS count_val,      -- 2 (doesn't count NULL)
       COUNT(*) AS count_all,          -- 3 (counts all rows)
       SUM(value) AS sum_val,          -- 30 (ignores NULL)
       AVG(value) AS avg_val           -- 15 (30/2, not 30/3)
FROM null_test;

count_val  count_all  sum_val  avg_val
---------  ---------  -------  -------
2          3          30       15.0

CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,     -- Username, unique and not null
    email VARCHAR(100) NOT NULL UNIQUE,       -- Email
    password_hash CHAR(64) NOT NULL,          -- SHA256 hash, fixed 64 characters
    age INTEGER CHECK (age >= 0 AND age <= 150),  -- Age, reasonable range check
    balance DECIMAL(12, 2) DEFAULT 0.00,      -- Account balance, precise to the cent
    bio TEXT,                                  -- Bio, unlimited length
    is_verified BOOLEAN DEFAULT 0,            -- Whether email is verified
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Query employee info, show "Unassigned" when department is NULL
SELECT e.name,
       COALESCE(d.name, 'Unassigned') AS department,
       COALESCE(e.salary, 0) AS salary
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;