-- ============================================================
--  MySQL / MariaDB — equivalent schema for the menu system.
--  Use this if you run the app against MySQL instead of MSSQL.
--  Mirrors the original tables and includes the new settings columns.
-- ============================================================

-- CREATE DATABASE IF NOT EXISTS StudentInfo CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- USE StudentInfo;

CREATE TABLE IF NOT EXISTS links (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  menu       VARCHAR(100) DEFAULT NULL,
  parent_id  INT DEFAULT 0,
  url        VARCHAR(256) DEFAULT NULL,
  status     INT DEFAULT 1,
  is_default INT NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- If the links table already exists without is_default, add it:
-- ALTER TABLE links ADD COLUMN is_default INT NOT NULL DEFAULT 0;

CREATE TABLE IF NOT EXISTS tblDesign (
  id        INT AUTO_INCREMENT PRIMARY KEY,
  txtHeader VARCHAR(500) DEFAULT NULL,
  txtFooter VARCHAR(500) DEFAULT NULL,
  Logo      VARCHAR(500) DEFAULT NULL,
  name      VARCHAR(200) DEFAULT NULL,
  location  VARCHAR(300) DEFAULT NULL,
  title     VARCHAR(300) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS users (
  id       INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(100) NOT NULL,
  password VARCHAR(250) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- One branding row if none exists yet.
INSERT INTO tblDesign (txtHeader, txtFooter, Logo, name, title)
SELECT 'College Information Portal',
       'Powered by: BAE (Management & BI) Consulting Ltd',
       'images/Logo.jpg',
       'College Information Portal',
       'College Information Portal'
WHERE NOT EXISTS (SELECT 1 FROM tblDesign);

-- A couple of sample menus if the table is empty.
INSERT INTO links (menu, parent_id, url, status)
SELECT * FROM (
  SELECT 'Application' AS menu, 0 AS parent_id, CAST(NULL AS CHAR) AS url, 1 AS status
) seed
WHERE NOT EXISTS (SELECT 1 FROM links);