-- Minimal syntax
CREATE DATABASE myapp;

-- Full syntax
CREATE DATABASE [IF NOT EXISTS] database_name
  [CHARACTER SET charset_name]
  [COLLATE collation_name]
  [ENCRYPTION {'Y'|'N'}]
  [COMMENT 'text'];

-- Create a simple database
CREATE DATABASE blog;

-- Create only if it doesn't already exist (no error if it does)
CREATE DATABASE IF NOT EXISTS blog;

-- Create with explicit character set and collation (recommended)
CREATE DATABASE myapp
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

-- Equivalent using SCHEMA (synonym for DATABASE in MySQL)
CREATE SCHEMA IF NOT EXISTS myapp
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

-- Show all available character sets
SHOW CHARACTER SET;

-- Show collations for utf8mb4
SHOW COLLATION WHERE Charset = 'utf8mb4';

-- See the server's default character set and collation
SHOW VARIABLES LIKE 'character_set_server';
SHOW VARIABLES LIKE 'collation_server';

-- List all databases you have access to
SHOW DATABASES;

-- List databases matching a pattern
SHOW DATABASES LIKE 'myapp%';

-- Select a database to work with
USE myapp;

-- Check which database is currently selected
SELECT DATABASE();

-- Show the CREATE DATABASE statement for an existing database
SHOW CREATE DATABASE myapp;

-- Get detailed info from information_schema
SELECT
  SCHEMA_NAME,
  DEFAULT_CHARACTER_SET_NAME,
  DEFAULT_COLLATION_NAME
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME = 'myapp';

-- Change the default character set and collation
ALTER DATABASE myapp
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

-- To convert existing tables, you must alter each table individually:
ALTER TABLE my_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Drop a database and all its contents permanently
DROP DATABASE myapp;

-- Drop only if it exists (no error if it doesn't)
DROP DATABASE IF EXISTS myapp;

-- SCHEMA is synonymous with DATABASE
DROP SCHEMA IF EXISTS myapp;

-- Find all databases using utf8mb4
SELECT SCHEMA_NAME
FROM information_schema.SCHEMATA
WHERE DEFAULT_CHARACTER_SET_NAME = 'utf8mb4';

-- Find all tables larger than 100MB
SELECT TABLE_SCHEMA, TABLE_NAME,
  ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 0) AS size_mb
FROM information_schema.TABLES
WHERE (DATA_LENGTH + INDEX_LENGTH) > 100 * 1024 * 1024
ORDER BY size_mb DESC;

-- Find all tables that do NOT have a PRIMARY KEY
SELECT TABLE_SCHEMA, TABLE_NAME
FROM information_schema.TABLES t
WHERE TABLE_TYPE = 'BASE TABLE'
  AND NOT EXISTS (
    SELECT 1
    FROM information_schema.KEY_COLUMN_USAGE k
    WHERE k.TABLE_SCHEMA = t.TABLE_SCHEMA
      AND k.TABLE_NAME = t.TABLE_NAME
      AND k.CONSTRAINT_NAME = 'PRIMARY'
  )
ORDER BY TABLE_SCHEMA, TABLE_NAME;

-- setup.sql — idempotent database creation script

-- Create the database if it doesn't exist
CREATE DATABASE IF NOT EXISTS myapp
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

-- Select it
USE myapp;

-- Create application user with minimal privileges
CREATE USER IF NOT EXISTS 'appuser'@'localhost'
  IDENTIFIED BY 'your_secure_password_here';

GRANT SELECT, INSERT, UPDATE, DELETE
  ON myapp.*
  TO 'appuser'@'localhost';

-- Create admin user for migrations
CREATE USER IF NOT EXISTS 'app_migration'@'localhost'
  IDENTIFIED BY 'migration_password_here';

GRANT ALL PRIVILEGES
  ON myapp.*
  TO 'app_migration'@'localhost';

FLUSH PRIVILEGES;

SELECT 'Database setup complete.' AS status;