-- =============================================================================
--  employee-form  -  database setup script
-- =============================================================================
--
--  Creates every schema/table this project touches, and seeds the demo rows
--  the labs expect. Safe to run more than once: everything is
--  IF NOT EXISTS / INSERT IGNORE, so re-running never duplicates a seed row
--  and never deletes anything you entered yourself.
--
--  What it creates
--  ---------------------------------------------------------------------------
--    employees.employee      Session 1 - registration form (JDBC DAO)
--    employees.emp_salary    Session 4 - listing demo (DisplayEmployeeServlet)
--    TEST.Employees          Session 2 - JSTL <sql:*> tags
--    demo.users              Session 3 - CRUD demo (UserDao)
--
--  How to run
--  ---------------------------------------------------------------------------
--    MySQL Workbench :  open this file, press the lightning bolt
--    command line    :  mysql -u root -p < db-setup.sql
--    or interactively:  mysql -u root -p   then  \. db-setup.sql
--
--  Before starting the app, check the credentials
--  ---------------------------------------------------------------------------
--    src/main/webapp/WEB-INF/db.properties
--
--        jdbc.user   = root
--        jdbc.pass   = system      <-- change this to YOUR MySQL password
--
--    Every DAO reads that one file (com.vs.employee.dao.DbConfig), so it is
--    the only place to edit. Wrong password = "Access denied for user
--    'root'@'localhost'", which is the most common first-run failure and is
--    NOT a code bug.
--
--  Notes for a different machine / version
--  ---------------------------------------------------------------------------
--    * No COLLATE is specified on purpose. The PPT uses
--      utf8mb4_0900_ai_ci, which only exists in MySQL 8. Leaving it out
--      lets the script run unchanged on MySQL 5.7 and 8.x - each server
--      picks its own default collation for utf8mb4.
--    * The URL parameters used by the app (serverTimezone=UTC,
--      allowPublicKeyRetrieval=true) are required against MySQL 8 with
--      mysql-connector-java-8.0.13. They are already in db.properties.
-- =============================================================================


-- =============================================================================
--  SECTION 1 - schema `employees`
--  Used by Session 1 (registration) and Session 4 (salary listing).
-- =============================================================================

CREATE DATABASE IF NOT EXISTS `employees`;
USE `employees`;

-- ---------------------------------------------------------------------------
--  Session 1 - the registration form target.
--  `id` is intentionally left out of the INSERT column list in EmployeeDao
--  so AUTO_INCREMENT assigns it. Submitting the form twice must give
--  id = 1 then id = 2 - that is the check that proves the fix described in
--  readme.txt section 9(a). Nothing is seeded here for that reason.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `employee` (
   `id` int(3) NOT NULL AUTO_INCREMENT,
   `first_name` varchar(20) DEFAULT NULL,
   `last_name` varchar(20) DEFAULT NULL,
   `username` varchar(250) DEFAULT NULL,
   `password` varchar(20) DEFAULT NULL,
   `address` varchar(45) DEFAULT NULL,
   `contact` varchar(45) DEFAULT NULL,
   PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------------
--  Session 4 - the table the PPT reads through an Oracle ODBC DSN.
--  The same seven rows as the slide, so the listing and the salary total
--  (280000) match what the class sees on screen.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `emp_salary` (
  `empid`   varchar(10) NOT NULL,
  `empname` varchar(20) NOT NULL,
  `sal`     int(10) DEFAULT NULL,
  PRIMARY KEY (`empid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO `emp_salary` (`empid`, `empname`, `sal`) VALUES
  ('e001', 'raj',     10000),
  ('e002', 'harry',   20000),
  ('e003', 'sunil',   30000),
  ('e004', 'pollock', 40000),
  ('e005', 'jonty',   50000),
  ('e006', 'kallis',  60000),
  ('e007', 'richard', 70000);


-- =============================================================================
--  SECTION 2 - schema `TEST`
--  Used by Session 2 (JSTL <sql:*> tags). Read as "jdbc.url.test".
-- =============================================================================

CREATE DATABASE IF NOT EXISTS `TEST`;
USE `TEST`;

--  `first` and `last` are kept backticked: they are not reserved words in
--  MySQL today, but the PPT writes them that way and it costs nothing.
CREATE TABLE IF NOT EXISTS `Employees` (
  `id`   int(3) NOT NULL AUTO_INCREMENT,
  `age`  int(3) NOT NULL,
  `first` varchar(255) DEFAULT NULL,
  `last`  varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--  The four PPT rows. Explicit ids 100-103 so jstl_sql_insert.jsp, which
--  inserts id 104 by hand, still behaves exactly as demonstrated.
INSERT IGNORE INTO `Employees` (`id`, `age`, `first`, `last`) VALUES
  (100, 18, 'Zara',  'Ali'),
  (101, 25, 'Mahnaz','Fatma'),
  (102, 30, 'Zaid',  'Khan'),
  (103, 28, 'Sumit', 'Mittal');


-- =============================================================================
--  SECTION 3 - schema `demo`
--  Used by Session 3 (CRUD). Read as "jdbc.url.demo".
-- =============================================================================

CREATE DATABASE IF NOT EXISTS `demo`;
USE `demo`;

CREATE TABLE IF NOT EXISTS `users` (
  `id`      int(3) NOT NULL AUTO_INCREMENT,
  `name`    varchar(120) NOT NULL,
  `email`   varchar(220) NOT NULL,
  `country` varchar(120) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--  Seed ids 1-3 so the "Edit user id 1" link on session3/index.jsp points at
--  a row that exists on a brand-new machine.
INSERT IGNORE INTO `users` (`id`, `name`, `email`, `country`) VALUES
  (1, 'Alice Peter',   'alice@example.com',  'India'),
  (2, 'Ravi Kumar',    'ravi@example.com',   'India'),
  (3, 'John Smith',    'john@example.com',   'USA');


-- =============================================================================
--  SECTION 4 - verification
--  Run these three queries after the script. Each should return the counts
--  shown; 0 rows means the section above did not run.
-- =============================================================================

SELECT 'employees.employee' AS `table`, COUNT(*) AS `rows` FROM `employees`.`employee`
UNION ALL
SELECT 'employees.emp_salary', COUNT(*) FROM `employees`.`emp_salary`
UNION ALL
SELECT 'TEST.Employees',       COUNT(*) FROM `TEST`.`Employees`
UNION ALL
SELECT 'demo.users',           COUNT(*) FROM `demo`.`users`;

--  Row detail, if you want to eyeball the seed data:
--      SELECT * FROM `employees`.`emp_salary`;
--      SELECT * FROM `TEST`.`Employees`;
--      SELECT * FROM `demo`.`users`;


-- =============================================================================
--  SECTION 5 - OPTIONAL, commented out
--  A dedicated application user instead of root. Uncomment only if you also
--  change jdbc.user / jdbc.pass in WEB-INF/db.properties to match.
-- =============================================================================
--
-- CREATE USER IF NOT EXISTS 'employeeapp'@'localhost' IDENTIFIED BY 'ChangeMe123!';
-- GRANT ALL PRIVILEGES ON `employees`.* TO 'employeeapp'@'localhost';
-- GRANT ALL PRIVILEGES ON `TEST`.*     TO 'employeeapp'@'localhost';
-- GRANT ALL PRIVILEGES ON `demo`.*      TO 'employeeapp'@'localhost';
-- FLUSH PRIVILEGES;