-- Integer column declarations
CREATE TABLE examples (
  -- Signed integers (default)
  tiny_signed    TINYINT,          -- -128 to 127
  small_signed   SMALLINT,         -- -32768 to 32767
  medium_signed  MEDIUMINT,        -- -8M to 8M
  int_signed     INT,              -- -2B to 2B
  big_signed     BIGINT,           -- -9.2 quintillion to 9.2 quintillion

  -- Unsigned integers (only positive)
  tiny_unsigned  TINYINT UNSIGNED,   -- 0 to 255
  small_unsigned SMALLINT UNSIGNED,  -- 0 to 65535
  int_unsigned   INT UNSIGNED,       -- 0 to 4.29 billion
  big_unsigned   BIGINT UNSIGNED     -- 0 to 18.4 quintillion
);

-- Demonstrate UNSIGNED constraint
CREATE TABLE counter_demo (
  id    INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  count INT UNSIGNED DEFAULT 0
);

INSERT INTO counter_demo (count) VALUES (100);

-- This will cause an error: column 'count' cannot be negative
UPDATE counter_demo SET count = -1 WHERE id = 1;
-- Error: Out of range value for column 'count'

-- Old style (deprecated, avoid)
id INT(11) NOT NULL AUTO_INCREMENT

-- Modern style (correct)
id INT NOT NULL AUTO_INCREMENT

-- TINYINT(1) is a special exception — it is the conventional way to declare
-- BOOLEAN columns, and many ORMs use TINYINT(1) to detect boolean columns.
is_active TINYINT(1) NOT NULL DEFAULT 1  -- still acceptable for booleans

CREATE TABLE users (
  id        INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email     VARCHAR(254) NOT NULL,
  is_active BOOLEAN NOT NULL DEFAULT TRUE,   -- alias for TINYINT(1)
  is_admin  BOOLEAN NOT NULL DEFAULT FALSE
);

INSERT INTO users (email, is_active, is_admin) VALUES
  ('alice@example.com', TRUE, FALSE),
  ('bob@example.com', 1, 0);    -- 1 and 0 work too

-- MySQL stores TRUE as 1 and FALSE as 0
SELECT email, is_active, is_admin FROM users;

-- Demonstrating floating-point imprecision
CREATE TABLE float_demo (val FLOAT, dval DOUBLE);
INSERT INTO float_demo VALUES (9.99, 9.99);

SELECT val, val * 100, dval, dval * 100 FROM float_demo;

CREATE TABLE financial (
  -- Prices: up to $99,999,999.99
  price          DECIMAL(10, 2) NOT NULL,

  -- Exchange rates: e.g., 1.234567
  exchange_rate  DECIMAL(10, 6),

  -- Tax rates: e.g., 0.08500 (8.5%)
  tax_rate       DECIMAL(5, 5),

  -- Bitcoin amount: 8 decimal places standard
  btc_amount     DECIMAL(18, 8)
);

-- Exact arithmetic
INSERT INTO financial (price) VALUES (9.99);
SELECT price, price * 100 FROM financial;
-- Result: 9.99 | 999.00  -- exact!

-- DECIMAL respects its scale on insert
INSERT INTO financial (price) VALUES (9.999);
-- Stored as: 10.00 (rounded to 2 decimal places)
-- In strict mode, this raises a warning

CREATE TABLE permissions (
  user_id     INT UNSIGNED,
  perms       BIT(8)  -- 8-bit permission mask
);

-- Insert using binary literal
INSERT INTO permissions VALUES (1, b'10110100');

-- Insert using numeric value (180 = 10110100 in binary)
INSERT INTO permissions VALUES (2, 180);

-- Check a specific bit (bit 7 = value 128)
SELECT user_id, perms + 0 AS perms_int
FROM permissions
WHERE perms & b'10000000';  -- has the read permission bit set

-- Check current SQL mode
SELECT @@sql_mode;

-- In STRICT mode (recommended), out-of-range values raise an error:
CREATE TABLE test_overflow (n TINYINT UNSIGNED);
INSERT INTO test_overflow VALUES (300);
-- Error 1264: Out of range value for column 'n' at row 1

-- In non-strict mode, MySQL silently clips to the max:
-- 300 gets stored as 255 (max TINYINT UNSIGNED)

-- Enable strict mode for the current session
SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';

CREATE TABLE orders (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  customer   VARCHAR(100) NOT NULL,
  total      DECIMAL(10, 2) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO orders (customer, total) VALUES ('Alice', 99.99);
INSERT INTO orders (customer, total) VALUES ('Bob', 149.50);

SELECT id, customer, total FROM orders;
-- id values: 1, 2

-- Get the last inserted auto_increment value
SELECT LAST_INSERT_ID();

-- Check the next auto_increment value
SHOW CREATE TABLE orders;