-- ============================================================
-- Fidel Habte Portfolio — Database Schema
-- Run this once in phpMyAdmin or via MySQL CLI
-- ============================================================

CREATE DATABASE IF NOT EXISTS fidelhabte_portfolio
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE fidelhabte_portfolio;

-- Projects
CREATE TABLE IF NOT EXISTS projects (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title       VARCHAR(200)  NOT NULL,
  slug        VARCHAR(210)  NOT NULL UNIQUE,
  description TEXT,
  long_desc   LONGTEXT,
  image       VARCHAR(500),
  demo_url    VARCHAR(500),
  github_url  VARCHAR(500),
  case_study  VARCHAR(500),
  status      ENUM('draft','published') DEFAULT 'draft',
  is_featured TINYINT(1)    DEFAULT 0,
  is_ai       TINYINT(1)    DEFAULT 0,
  sort_order  INT           DEFAULT 0,
  tags        TEXT,
  created_at  TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
  updated_at  TIMESTAMP     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Blog posts
CREATE TABLE IF NOT EXISTS posts (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title        VARCHAR(300) NOT NULL,
  slug         VARCHAR(310) NOT NULL UNIQUE,
  excerpt      TEXT,
  body         LONGTEXT,
  image        VARCHAR(500),
  category     VARCHAR(100),
  tags         TEXT,
  reading_time INT          DEFAULT 5,
  is_featured  TINYINT(1)   DEFAULT 0,
  status       ENUM('draft','published') DEFAULT 'draft',
  published_at DATETIME,
  created_at   TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
  updated_at   TIMESTAMP    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Testimonials
CREATE TABLE IF NOT EXISTS testimonials (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name        VARCHAR(150) NOT NULL,
  title       VARCHAR(200),
  company     VARCHAR(200),
  quote       TEXT         NOT NULL,
  photo       VARCHAR(500),
  rating      TINYINT      DEFAULT 5,
  is_enabled  TINYINT(1)   DEFAULT 1,
  sort_order  INT          DEFAULT 0,
  created_at  TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
  updated_at  TIMESTAMP    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Skills
CREATE TABLE IF NOT EXISTS skills (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  category   VARCHAR(100) NOT NULL,
  name       VARCHAR(100) NOT NULL,
  level      TINYINT      DEFAULT 80,
  label      VARCHAR(50),
  tone       VARCHAR(20)  DEFAULT 'default',
  sort_order INT          DEFAULT 0
) ENGINE=InnoDB;

-- Experience
CREATE TABLE IF NOT EXISTS experience (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company      VARCHAR(200) NOT NULL,
  role         VARCHAR(200) NOT NULL,
  date_start   VARCHAR(20),
  date_end     VARCHAR(20),
  summary      TEXT,
  achievements TEXT,
  technologies TEXT,
  sort_order   INT          DEFAULT 0,
  created_at   TIMESTAMP    DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Speaking events
CREATE TABLE IF NOT EXISTS speaking (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title       VARCHAR(200) NOT NULL,
  description TEXT,
  icon        VARCHAR(50)  DEFAULT 'mic',
  sort_order  INT          DEFAULT 0
) ENGINE=InnoDB;

-- Contact messages
CREATE TABLE IF NOT EXISTS messages (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name       VARCHAR(150) NOT NULL,
  email      VARCHAR(191) NOT NULL,
  subject    VARCHAR(300),
  body       TEXT         NOT NULL,
  is_read    TINYINT(1)   DEFAULT 0,
  created_at TIMESTAMP    DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Media library
CREATE TABLE IF NOT EXISTS media (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  filename    VARCHAR(300) NOT NULL,
  original    VARCHAR(300),
  mime        VARCHAR(100),
  size        INT UNSIGNED DEFAULT 0,
  path        VARCHAR(500),
  created_at  TIMESTAMP    DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Awards / certifications
CREATE TABLE IF NOT EXISTS awards (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title       VARCHAR(200) NOT NULL,
  issuer      VARCHAR(200),
  date        VARCHAR(20),
  description TEXT,
  sort_order  INT          DEFAULT 0
) ENGINE=InnoDB;

-- Skills
INSERT INTO skills (category, name, level, label, tone, sort_order) VALUES
-- Backend
('Backend', 'Node.js',    94, 'Expert',   'default', 1),
('Backend', 'PHP',        90, 'Expert',   'default', 2),
('.NET',    '.NET',       86, 'Advanced', 'default', 3),
('Backend', 'Java',       78, 'Advanced', 'default', 4),
-- AI & Automation
('AI & Automation', 'LLMs / Prompt Eng.', 90, 'Advanced', 'ai', 1),
('AI & Automation', 'AI Agents',          86, 'Advanced', 'ai', 2),
('AI & Automation', 'Automation',         92, 'Expert',   'ai', 3),
('AI & Automation', 'GitHub Actions',     88, 'Advanced', 'ai', 4),
-- Cloud & DevOps
('Cloud & DevOps', 'Docker',     92, 'Expert',   'default', 1),
('Cloud & DevOps', 'Kubernetes', 82, 'Advanced', 'default', 2),
('Cloud & DevOps', 'CI/CD',      90, 'Expert',   'default', 3),
('Cloud & DevOps', 'Security',   84, 'Advanced', 'default', 4),
-- Frontend & Data
('Frontend & Data', 'React',           88, 'Advanced', 'default', 1),
('Frontend & Data', 'TypeScript',      90, 'Expert',   'default', 2),
('Frontend & Data', 'Oracle / MySQL',  86, 'Advanced', 'default', 3),
('Frontend & Data', 'MuleSoft',        80, 'Advanced', 'default', 4);

-- Experience
INSERT INTO experience (company, role, date_start, date_end, summary, achievements, technologies, sort_order) VALUES
('Northwind Financial', 'Principal Software Engineer', '2021', 'Present',
 'Lead backend & platform engineer for the payments organization; founded the internal AI tooling guild.',
 'Architected an event-driven payments platform handling 8M+ daily transactions at 99.99% uptime.|Shipped the PR Review Agent, cutting average review turnaround by 40% across 60 engineers.|Established the security review process and CI/CD standards now used company-wide.',
 '.NET|Kubernetes|MuleSoft|Oracle|LLMs', 1),
('Atlas Systems', 'Senior Software Engineer', '2016', '2021',
 'Backend and integrations lead for the data platform; mentored a team of six.',
 'Built the integration layer connecting 20+ partner APIs with idempotent, auditable workflows.|Migrated the monolith to containerized services, halving deploy times.|Mentored 6 engineers; three were promoted to senior.',
 'Node.js|PHP|Docker|MySQL|CI/CD', 2),
('Brightwave', 'Software Engineer', '2010', '2016',
 'Full-stack engineer across web apps, APIs, and early automation tooling.',
 'Delivered customer-facing web apps in PHP and React.|Introduced automated testing and the first CI pipeline.|Built internal automation that saved the ops team ~15 hours weekly.',
 'PHP|React|MySQL|JavaScript', 3);

-- Projects
INSERT INTO projects (title, slug, description, tags, status, is_featured, is_ai, sort_order) VALUES
('PR Review Agent',             'pr-review-agent',            'An autonomous agent that reviews pull requests, flags security risks, and suggests fixes — wired into CI with GitHub Actions.', 'TypeScript|LLMs|GitHub Actions|Node.js', 'published', 1, 1, 1),
('Confluence Doc Generator',    'confluence-doc-generator',   'Generates and keeps technical documentation in sync from code and tickets, posting structured pages to Confluence automatically.',    'Python|LLMs|Atlassian API', 'published', 1, 1, 2),
('Payments Integration Platform','payments-integration',      'A resilient MuleSoft integration layer connecting Oracle ERP, billing, and partner APIs with idempotent retries and audit trails.',  'MuleSoft|Oracle|.NET|Kubernetes', 'published', 1, 0, 3),
('Security Scanner',            'security-scanner',           'Continuous dependency + secret scanning across repos with risk scoring and one-click remediation PRs.',                              'Go|Docker|CI/CD', 'published', 0, 1, 4),
('Cloud Cost Optimizer',        'cloud-cost-optimizer',       'Right-sizes Kubernetes workloads from usage telemetry, cutting spend without touching reliability SLOs.',                            'Kubernetes|Node.js|Prometheus', 'published', 0, 0, 5),
('Jira Automation Suite',       'jira-automation-suite',      'Triage, label, and route issues automatically; drafts release notes and sprint summaries for the team.',                            'Node.js|LLMs|Jira API', 'published', 0, 1, 6);

-- Testimonials
INSERT INTO testimonials (name, title, company, quote, rating, is_enabled, sort_order) VALUES
('Sarah Chen',   'VP Engineering',     'Northwind Financial', 'Fidel is the rare engineer who pairs deep backend rigor with a genuine talent for making the whole team faster. His AI tooling changed how we ship.',                  5, 1, 1),
('Marcus Avery', 'Engineering Manager','Atlas Systems',       'He architected our most critical integration platform and mentored half the team while doing it. Calm, precise, and relentlessly thoughtful.',                         5, 1, 2),
('Priya Nair',   'CTO',               'Brightwave',          'If you want secure, scalable systems delivered without drama, you want Fidel. He raises the bar for everyone around him.',                                              5, 1, 3);

-- Speaking
INSERT INTO speaking (title, description, icon, sort_order) VALUES
('AI Workshops',       'Hands-on sessions on building practical LLM agents for engineering teams.', 'presentation',   1),
('Church Technology',  'Volunteer building streaming, scheduling & media systems.',                  'heart-handshake',2),
('Hackathons',         'Mentor & judge for internal and community hackathons.',                       'trophy',         3),
('Mentoring',          'Ongoing 1:1 mentoring for early-career engineers.',                          'users',          4),
('Conference Talks',   'Talks on automation, security, and AI in the SDLC.',                         'mic',            5),
('Volunteer Work',     'Pro-bono technical help for nonprofits.',                                     'heart',          6);

-- Blog posts
INSERT INTO posts (title, slug, excerpt, category, tags, reading_time, is_featured, status, published_at) VALUES
('Wiring an AI code-review agent into CI without the noise',  'ai-code-review-agent-ci',      'How to integrate an autonomous review agent into CI so it catches real risks without drowning engineers in noise.',                'AI',       'LLMs|GitHub Actions|DX',  8, 1, 'published', '2026-06-01'),
('Idempotent integrations: lessons from 8M transactions a day','idempotent-integrations',      'Hard-won patterns for building integration layers that stay correct under retries, duplicates, and partial failures.',            'Backend',  'MuleSoft|Reliability',    11, 0, 'published', '2026-05-01'),
('A pragmatic security checklist for small platform teams',   'security-checklist-platform',  'The minimal set of controls that meaningfully reduce risk without slowing a small engineering team to a crawl.',                  'Security', 'Security|DevOps',          6, 0, 'published', '2026-04-01'),
('Right-sizing Kubernetes without breaking your SLOs',        'kubernetes-right-sizing',       'How to use usage telemetry to reduce Kubernetes resource waste without sacrificing reliability or introducing noisy alerts.',      'Cloud',    'Kubernetes|Cost',          9, 0, 'published', '2026-03-01');
