-- 1) Create a database
CREATE DATABASE my_app_db CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

-- 2) Create a dedicated app user (local-only)
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'StrongP@ssw0rd!';

-- 3) Grant privileges on the new DB
GRANT ALL PRIVILEGES ON my_app_db.* TO 'appuser'@'localhost';
FLUSH PRIVILEGES;

USE my_app_db;

-- Users table
CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  full_name VARCHAR(255) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Posts table
CREATE TABLE posts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  title VARCHAR(200) NOT NULL,
  body TEXT,
  published_at DATETIME NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_posts_user
    FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
) ENGINE=InnoDB;

INSERT INTO users (email, full_name) VALUES
  ('[email protected]', 'Alice'),
  ('[email protected]', 'Bob');

INSERT INTO posts (user_id, title, body, published_at) VALUES
  (1, 'Hello World', 'My first post', NOW()),
  (1, 'Second Post', 'More content', NULL),
  (2, 'Bob\'s Intro', 'Hi, I\'m Bob!', NOW());

-- All users
SELECT * FROM users;

-- Filter
SELECT id, email FROM users WHERE email LIKE '%@example.com';

-- Order + Limit
SELECT id, title FROM posts ORDER BY created_at DESC LIMIT 5;

-- Update a user name
UPDATE users SET full_name = 'Alice A.' WHERE email = '[email protected]';

-- Delete a post
DELETE FROM posts WHERE id = 2;

-- Inner Join: users with their posts
SELECT u.full_name, p.title, p.published_at
FROM users u
JOIN posts p ON p.user_id = u.id
ORDER BY p.created_at DESC;

-- Left Join: users even without posts
SELECT u.full_name, p.title
FROM users u
LEFT JOIN posts p ON p.user_id = u.id;

-- Count posts per user
SELECT u.full_name, COUNT(p.id) AS post_count
FROM users u
LEFT JOIN posts p ON p.user_id = u.id
GROUP BY u.id, u.full_name
HAVING COUNT(p.id) >= 1
ORDER BY post_count DESC;

-- Add a not-null + default
ALTER TABLE posts
  MODIFY title VARCHAR(200) NOT NULL;

-- Add an index (for faster filter by user_id)
CREATE INDEX idx_posts_user_id ON posts(user_id);

-- Unique constraint example on users.email already declared in CREATE TABLE

-- Start a transaction, do multiple writes, then COMMIT or ROLLBACK
START TRANSACTION;
  INSERT INTO posts (user_id, title) VALUES (1, 'Tx Post 1');
  INSERT INTO posts (user_id, title) VALUES (1, 'Tx Post 2');
-- If all good:
COMMIT;
-- If something went wrong:
-- ROLLBACK;

-- View: latest posts per user (simple example)
CREATE OR REPLACE VIEW v_latest_posts AS
SELECT p.*
FROM posts p
JOIN (
  SELECT user_id, MAX(created_at) AS max_created
  FROM posts
  GROUP BY user_id
) x ON x.user_id = p.user_id AND x.max_created = p.created_at;

-- Stored Procedure: insert a post
DELIMITER $$
CREATE PROCEDURE add_post(IN p_user_id INT, IN p_title VARCHAR(200), IN p_body TEXT)
BEGIN
  INSERT INTO posts (user_id, title, body, published_at)
  VALUES (p_user_id, p_title, p_body, NOW());
END$$
DELIMITER ;

-- Call it
CALL add_post(1, 'Proc Post', 'Inserted via stored procedure');

-- scripts/init.sql
CREATE DATABASE IF NOT EXISTS my_app_db;
USE my_app_db;

CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  full_name VARCHAR(255) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS posts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  title VARCHAR(200) NOT NULL,
  body TEXT,
  published_at DATETIME NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_posts_user FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB;

INSERT INTO users (email, full_name) VALUES
  ('[email protected]', 'Alice')
ON DUPLICATE KEY UPDATE full_name = VALUES(full_name);

INSERT INTO posts (user_id, title, body, published_at) VALUES
  (1, 'Hello World', 'My first post', NOW())
ON DUPLICATE KEY UPDATE title = VALUES(title);

-- Create tables
CREATE TABLE users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  email TEXT NOT NULL UNIQUE,
  full_name TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE posts (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER NOT NULL,
  title TEXT NOT NULL,
  body TEXT,
  published_at TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Insert sample
INSERT INTO users (email, full_name) VALUES ('[email protected]', 'Alice');
INSERT INTO posts (user_id, title, body, published_at) VALUES (1, 'Hello', 'SQLite post', datetime('now'));

-- Query
SELECT u.full_name, p.title FROM users u JOIN posts p ON p.user_id = u.id;