-- ============================================================
-- WMF Agent Army — MySQL Database Schema v1.0
-- Web Market Florida | console.washeen.com
-- Run once on server: mysql -u root -p wmf_agents < wmf_schema.sql
-- ============================================================

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

USE wmf_agents;

-- ============================================================
-- SECTION 1: USERS & ACCESS
-- Powers: Super Admin Dashboard login, role-based access
-- ============================================================

CREATE TABLE users (
  id              INT AUTO_INCREMENT PRIMARY KEY,
  name            VARCHAR(100) NOT NULL,
  email           VARCHAR(150) NOT NULL UNIQUE,
  password_hash   VARCHAR(255) NOT NULL,
  role            ENUM('superadmin','admin','viewer') NOT NULL DEFAULT 'viewer',
  -- superadmin = Washeen (full access, can add clients/agents)
  -- admin      = Yashara (full view, limited edit)
  -- viewer     = future team members / client read-only
  is_active       BOOLEAN DEFAULT TRUE,
  last_login      DATETIME,
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at      DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Seed users
INSERT INTO users (name, email, password_hash, role) VALUES
('Washeen', 'washeen@washeen.com', 'CHANGE_ME_BCRYPT_HASH', 'superadmin'),
('Yashara', 'yashara@washeen.com', 'CHANGE_ME_BCRYPT_HASH', 'admin');


-- ============================================================
-- SECTION 2: CLIENTS
-- Powers: Client cards, per-client data isolation, billing
-- ============================================================

CREATE TABLE clients (
  id              INT AUTO_INCREMENT PRIMARY KEY,
  name            VARCHAR(150) NOT NULL,
  slug            VARCHAR(100) NOT NULL UNIQUE,    -- e.g. 'mecatos', 'wmf'
  logo_initials   VARCHAR(5),                       -- e.g. 'MB', 'WM'
  brand_color     VARCHAR(7),                       -- hex e.g. '#E8A020'
  website         VARCHAR(255),
  cms             VARCHAR(50),                      -- WordPress, Wix, Custom...
  status          ENUM('active','paused','churned') DEFAULT 'active',
  mrr             DECIMAL(10,2) DEFAULT 0.00,       -- monthly recurring revenue
  contract_start  DATE,
  contract_end    DATE,
  primary_contact_name  VARCHAR(150),
  primary_contact_email VARCHAR(150),
  slack_channel   VARCHAR(100),
  notes           TEXT,
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at      DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Seed clients
INSERT INTO clients (name, slug, logo_initials, brand_color, website, cms, status, mrr, contract_start, primary_contact_name, primary_contact_email, slack_channel) VALUES
('Web Market Florida', 'wmf', 'WM', '#3B82F6', 'https://washeen.com', 'WordPress', 'active', 500.00, '2026-01-01', 'Washeen', 'washeen@washeen.com', '#wmf-internal'),
('Mecatos Bakery & Café', 'mecatos', 'MB', '#E8A020', 'https://mecatoscafe.com', 'WordPress', 'active', 3000.00, '2025-01-01', 'Mecatos Owner', 'owner@mecatoscafe.com', '#mecatos-updates');


-- ============================================================
-- SECTION 3: LOCATIONS
-- Powers: Per-location GBP data, grid rankings, ads, content
-- ============================================================

CREATE TABLE locations (
  id              INT AUTO_INCREMENT PRIMARY KEY,
  client_id       INT NOT NULL,
  name            VARCHAR(150) NOT NULL,            -- 'MetroWest', 'Lake Nona'
  address         VARCHAR(255),
  city            VARCHAR(100) DEFAULT 'Orlando',
  state           VARCHAR(50)  DEFAULT 'FL',
  zip             VARCHAR(20),
  lat             DECIMAL(10,7),
  lng             DECIMAL(10,7),
  gbp_name        VARCHAR(255),                     -- exact GBP listing name
  gbp_place_id    VARCHAR(255),                     -- Google Place ID
  phone           VARCHAR(30),
  location_page_url VARCHAR(255),                   -- e.g. mecatoscafe.com/metrowest/
  status          ENUM('active','paused','closed') DEFAULT 'active',
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
);

-- Seed Mecatos locations
INSERT INTO locations (client_id, name, address, lat, lng, gbp_name, location_page_url) VALUES
(2, 'MetroWest',      '2900 S Kirkman Rd, Orlando FL',       28.5256, -81.4768, 'Mecatos Bakery & Café', 'https://mecatoscafe.com/metrowest/'),
(2, 'Lake Nona',      '10783 Narcoossee Rd, Orlando FL',     28.3757, -81.2444, 'Mecatos Bakery & Café', 'https://mecatoscafe.com/lake-nona/'),
(2, 'Lake Underhill', '12400 Lake Underhill Rd, Orlando FL', 28.5326, -81.2710, 'Mecatos Bakery & Café', 'https://mecatoscafe.com/lake-underhill/'),
(2, 'Edgewood',       '7790 Lake Underhill Rd, Orlando FL',  28.5250, -81.3035, 'Mecatos Bakery & Café', 'https://mecatoscafe.com/hoffner/'),
(2, 'Downtown',       '20 N Orange Ave, Orlando FL',         28.5421, -81.3790, 'Mecatos Bakery & Café', 'https://mecatoscafe.com/downtown/'),
(2, 'W Colonial',     '10151 W Colonial Dr, Orlando FL',     28.5601, -81.5024, 'Mecatos Bakery & Café', 'https://mecatoscafe.com/w-colonial-location/'),
(2, 'Curry Ford',     '2904 Curry Ford Rd, Orlando FL',      28.5042, -81.3348, 'Mecatos Bakery & Café', 'https://mecatoscafe.com/curry-ford-west/'),
(2, 'Apopka',         '1234 Hansel Ave, Apopka FL',          28.6936, -81.5322, 'Mecatos Bakery & Café', 'https://mecatoscafe.com/apopka-fl/');


-- ============================================================
-- SECTION 4: AGENTS
-- Powers: Agent Army roster, Super Admin agent management
-- ============================================================

CREATE TABLE agents (
  id              INT AUTO_INCREMENT PRIMARY KEY,
  name            VARCHAR(100) NOT NULL,            -- 'Sofia Marin'
  role            VARCHAR(100) NOT NULL,            -- 'GBP Manager'
  tier            ENUM('1','2','3') NOT NULL,        -- 1=CEO, 2=Director, 3=Client
  type            ENUM('executive','director','specialist','worker','special') NOT NULL,
  client_id       INT,                              -- NULL = cross-client agent
  reports_to_id   INT,                              -- FK to agents.id
  status          ENUM('active','paused','draft') DEFAULT 'active',
  core_prompt     LONGTEXT,                         -- the Core persona prompt
  output_format   JSON,                             -- expected JSON structure
  run_frequency   VARCHAR(50),                      -- 'daily', 'weekly', 'manual'
  last_run_at     DATETIME,
  last_run_status ENUM('success','error','pending') DEFAULT 'pending',
  kpi_score       TINYINT UNSIGNED DEFAULT 0,       -- 0-100
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at      DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE SET NULL,
  FOREIGN KEY (reports_to_id) REFERENCES agents(id) ON DELETE SET NULL
);

-- Seed full agent roster
INSERT INTO agents (name, role, tier, type, client_id, reports_to_id, status, run_frequency) VALUES
-- Tier 1
('Marcus Webb',    'CEO',                      '1', 'executive',  NULL, NULL, 'active', 'weekly'),
-- Tier 2
('Diana Reeves',   'Local SEO Director',       '2', 'director',   NULL, 1,    'active', 'weekly'),
('Jordan Hale',    'Content Director',         '2', 'director',   NULL, 1,    'active', 'weekly'),
('Alexis Morgan',  'Customer Success Director','2', 'director',   NULL, 1,    'active', 'weekly'),
('Priya Nath',     'Data Analyst',             '2', 'specialist', NULL, 1,    'active', 'weekly'),
('Felix Strand',   'Cross-Check Agent',        '2', 'special',    NULL, 1,    'active', 'weekly'),
('Tate Owens',     'Lead Gen / Sales Agent',   '2', 'specialist', NULL, 1,    'active', 'weekly'),
('Cleo Nash',      'Reddit / Community Agent', '2', 'specialist', NULL, 1,    'active', 'weekly'),
('Hugo Fenn',      'Chief Financial Agent',    '2', 'specialist', NULL, 1,    'active', 'monthly'),
-- Tier 3 — WMF
('Lena Cruz',      'GBP Manager',              '3', 'worker',     1,    2,    'active', 'weekly'),
('Ray Okoro',      'Google Ads Manager',       '3', 'worker',     1,    1,    'active', 'weekly'),
('Iris Kane',      'Technical SEO Agent',      '3', 'worker',     1,    2,    'active', 'monthly'),
('Milo Grant',     'Link Building Agent',      '3', 'worker',     1,    2,    'active', 'monthly'),
('Zara Penn',      'Client Success Manager',   '3', 'worker',     1,    4,    'active', 'weekly'),
-- Tier 3 — Mecatos
('Sofia Marin',    'GBP Manager',              '3', 'worker',     2,    2,    'active', 'weekly'),
('Theo Banks',     'Google Ads Manager',       '3', 'worker',     2,    1,    'active', 'weekly'),
('Nadia Voss',     'Technical SEO Agent',      '3', 'worker',     2,    2,    'active', 'monthly'),
('Cole Marsh',     'Link Building Agent',      '3', 'worker',     2,    2,    'active', 'monthly'),
('Yuki Sato',      'Client Success Manager',   '3', 'worker',     2,    4,    'active', 'weekly');


-- ============================================================
-- SECTION 5: AGENT PLUG LAYER (Client Configs)
-- Powers: Dynamic client-specific agent config, onboarding
-- ============================================================

CREATE TABLE agent_configs (
  id              INT AUTO_INCREMENT PRIMARY KEY,
  agent_id        INT NOT NULL,
  client_id       INT NOT NULL,
  config          JSON NOT NULL,
  -- Stores the full plug layer:
  -- { "locations": [...], "primary_keywords": [...],
  --   "competitors": [...], "cms": "WordPress",
  --   "monthly_budget_links": 500, "monthly_budget_ads": 3000,
  --   "content_cadence": "4 per location per month",
  --   "reporting_day": "Friday", "special_notes": "..." }
  is_active       BOOLEAN DEFAULT TRUE,
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at      DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (agent_id)  REFERENCES agents(id)  ON DELETE CASCADE,
  FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
  UNIQUE KEY unique_agent_client (agent_id, client_id)
);


-- ============================================================
-- SECTION 6: AGENT LOGS
-- Powers: Agent Calendar, activity feed, audit trail
-- ============================================================

CREATE TABLE agent_logs (
  id              INT AUTO_INCREMENT PRIMARY KEY,
  agent_id        INT NOT NULL,
  client_id       INT,
  location_id     INT,
  run_type        ENUM('scheduled','manual','triggered') DEFAULT 'scheduled',
  input_summary   TEXT,                             -- brief of what data was fed in
  output          LONGTEXT,                         -- full JSON output from agent
  output_type     VARCHAR(50),                      -- 'weekly_report','ab_test','escalation'...
  status          ENUM('success','error','partial') DEFAULT 'success',
  tokens_used     INT DEFAULT 0,
  run_duration_ms INT DEFAULT 0,
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (agent_id)    REFERENCES agents(id)    ON DELETE CASCADE,
  FOREIGN KEY (client_id)   REFERENCES clients(id)   ON DELETE SET NULL,
  FOREIGN KEY (location_id) REFERENCES locations(id) ON DELETE SET NULL,
  INDEX idx_agent_date   (agent_id, created_at),
  INDEX idx_client_date  (client_id, created_at)
);


-- ============================================================
-- SECTION 7: KPIs
-- Powers: KPI Tracker, agent scorecards, health scores
-- ============================================================

CREATE TABLE kpis (
  id              INT AUTO_INCREMENT PRIMARY KEY,
  agent_id        INT NOT NULL,
  client_id       INT,
  name            VARCHAR(150) NOT NULL,            -- 'Avg GBP Ranking'
  unit            VARCHAR(30),                      -- 'position', '%', '/100'
  target_value    DECIMAL(10,2),
  current_value   DECIMAL(10,2),
  previous_value  DECIMAL(10,2),
  status          ENUM('green','yellow','red') DEFAULT 'yellow',
  recorded_at     DATE NOT NULL,
  FOREIGN KEY (agent_id)  REFERENCES agents(id)  ON DELETE CASCADE,
  FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE SET NULL,
  INDEX idx_kpi_agent_date (agent_id, recorded_at),
  INDEX idx_kpi_client     (client_id, recorded_at)
);


-- ============================================================
-- SECTION 8: ALERTS
-- Powers: Alert Center, escalation tracking
-- ============================================================

CREATE TABLE alerts (
  id              INT AUTO_INCREMENT PRIMARY KEY,
  from_agent_id   INT NOT NULL,
  to_agent_id     INT,
  client_id       INT,
  location_id     INT,
  severity        ENUM('high','medium','low') NOT NULL,
  type            ENUM('drop','opportunity','waste','contradiction','stale','compliance','budget','churn') NOT NULL,
  title           VARCHAR(255) NOT NULL,
  message         TEXT NOT NULL,
  data_points     JSON,
  recommended_action TEXT,
  status          ENUM('open','acknowledged','resolved') DEFAULT 'open',
  acknowledged_by INT,
  acknowledged_at DATETIME,
  resolved_at     DATETIME,
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (from_agent_id)   REFERENCES agents(id)    ON DELETE CASCADE,
  FOREIGN KEY (to_agent_id)     REFERENCES agents(id)    ON DELETE SET NULL,
  FOREIGN KEY (client_id)       REFERENCES clients(id)   ON DELETE SET NULL,
  FOREIGN KEY (location_id)     REFERENCES locations(id) ON DELETE SET NULL,
  FOREIGN KEY (acknowledged_by) REFERENCES users(id)     ON DELETE SET NULL,
  INDEX idx_alerts_status   (status, created_at),
  INDEX idx_alerts_client   (client_id, status),
  INDEX idx_alerts_severity (severity, status)
);


-- ============================================================
-- SECTION 9: CALENDAR EVENTS
-- Powers: Agent Calendar, work done vs planned view
-- ============================================================

CREATE TABLE calendar_events (
  id              INT AUTO_INCREMENT PRIMARY KEY,
  agent_id        INT NOT NULL,
  client_id       INT,
  location_id     INT,
  title           VARCHAR(255) NOT NULL,
  description     TEXT,
  event_type      ENUM('completed','planned','in_progress','cancelled') NOT NULL,
  category        VARCHAR(50),                      -- 'content','gbp','ads','seo','report'
  event_date      DATE NOT NULL,
  log_id          INT,                              -- links to agent_logs if completed
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (agent_id)    REFERENCES agents(id)    ON DELETE CASCADE,
  FOREIGN KEY (client_id)   REFERENCES clients(id)   ON DELETE SET NULL,
  FOREIGN KEY (location_id) REFERENCES locations(id) ON DELETE SET NULL,
  FOREIGN KEY (log_id)      REFERENCES agent_logs(id) ON DELETE SET NULL,
  INDEX idx_calendar_date   (event_date),
  INDEX idx_calendar_agent  (agent_id, event_date),
  INDEX idx_calendar_client (client_id, event_date)
);


-- ============================================================
-- SECTION 10: GBP METRICS
-- Powers: GBP Manager dashboard, location health cards
-- ============================================================

CREATE TABLE gbp_metrics (
  id                  INT AUTO_INCREMENT PRIMARY KEY,
  location_id         INT NOT NULL,
  recorded_month      DATE NOT NULL,                -- first day of month
  direction_requests  INT DEFAULT 0,
  website_clicks      INT DEFAULT 0,
  food_menu_clicks    INT DEFAULT 0,
  calls               INT DEFAULT 0,
  photo_views         INT DEFAULT 0,
  search_views        INT DEFAULT 0,
  maps_views          INT DEFAULT 0,
  review_count        INT DEFAULT 0,
  avg_review_score    DECIMAL(3,2),
  new_reviews         INT DEFAULT 0,
  unanswered_reviews  INT DEFAULT 0,
  listing_completeness TINYINT UNSIGNED DEFAULT 0,  -- 0-100%
  created_at          DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (location_id) REFERENCES locations(id) ON DELETE CASCADE,
  UNIQUE KEY unique_location_month (location_id, recorded_month),
  INDEX idx_gbp_location_date (location_id, recorded_month)
);

-- Seed February 2026 GBP data from Priya's report
INSERT INTO gbp_metrics (location_id, recorded_month, direction_requests, website_clicks, food_menu_clicks, calls) VALUES
(1, '2026-02-01', 1848, 522, 420, 143),  -- MetroWest
(2, '2026-02-01', 1590, 512, 393, 191),  -- Lake Nona
(3, '2026-02-01', 1278, 564, 385, 208),  -- Lake Underhill 12400
(4, '2026-02-01', 1475, 391, 268, 179),  -- Edgewood
(5, '2026-02-01', 1230, 480, 416, 105),  -- Downtown
(6, '2026-02-01', 1078, 468, 334, 123),  -- W Colonial
(7, '2026-02-01', 1156, 254, 190,  73),  -- Curry Ford
(8, '2026-02-01', 1672, 313, 271, 105);  -- Apopka/Hansel


-- ============================================================
-- SECTION 11: GRID RANKINGS (DataForSEO)
-- Powers: GBP grid map, ranking trend cards
-- ============================================================

CREATE TABLE grid_rankings (
  id              INT AUTO_INCREMENT PRIMARY KEY,
  location_id     INT NOT NULL,
  keyword         VARCHAR(255) NOT NULL,
  grid_row        TINYINT NOT NULL,                 -- -2 to +2
  grid_col        TINYINT NOT NULL,                 -- -2 to +2
  lat             DECIMAL(10,7),
  lng             DECIMAL(10,7),
  position        TINYINT UNSIGNED,                 -- rank 1-20, NULL = not ranked
  rating          DECIMAL(3,2),
  review_count    INT,
  recorded_date   DATE NOT NULL,
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (location_id) REFERENCES locations(id) ON DELETE CASCADE,
  INDEX idx_grid_location_keyword_date (location_id, keyword, recorded_date),
  INDEX idx_grid_date (recorded_date)
);


-- ============================================================
-- SECTION 12: ORGANIC RANKINGS (GSC / Ahrefs)
-- Powers: SEO Director KPIs, keyword tracking
-- ============================================================

CREATE TABLE organic_rankings (
  id              INT AUTO_INCREMENT PRIMARY KEY,
  client_id       INT NOT NULL,
  location_id     INT,
  keyword         VARCHAR(255) NOT NULL,
  page_url        VARCHAR(500),
  clicks          INT DEFAULT 0,
  impressions     INT DEFAULT 0,
  ctr             DECIMAL(5,2),
  avg_position    DECIMAL(5,2),
  recorded_date   DATE NOT NULL,
  source          ENUM('gsc','ahrefs','dataforseo','manual') DEFAULT 'gsc',
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (client_id)   REFERENCES clients(id)   ON DELETE CASCADE,
  FOREIGN KEY (location_id) REFERENCES locations(id) ON DELETE SET NULL,
  INDEX idx_rankings_client_date   (client_id, recorded_date),
  INDEX idx_rankings_keyword       (keyword, client_id)
);


-- ============================================================
-- SECTION 13: ADS PERFORMANCE
-- Powers: Ads Manager dashboard, ROAS tracking
-- ============================================================

CREATE TABLE ads_performance (
  id              INT AUTO_INCREMENT PRIMARY KEY,
  client_id       INT NOT NULL,
  location_id     INT,
  campaign_name   VARCHAR(255) NOT NULL,
  campaign_type   VARCHAR(50),                      -- 'Performance Max', 'Search'...
  platform        ENUM('google','meta','tiktok') DEFAULT 'google',
  spend           DECIMAL(10,2) DEFAULT 0,
  clicks          INT DEFAULT 0,
  impressions     INT DEFAULT 0,
  conversions     DECIMAL(10,2) DEFAULT 0,
  ctr             DECIMAL(5,2),
  cpc             DECIMAL(8,4),
  cpa             DECIMAL(8,4),
  conv_rate       DECIMAL(5,2),
  roas            DECIMAL(8,2),
  status          ENUM('active','paused','ended') DEFAULT 'active',
  recorded_month  DATE NOT NULL,
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (client_id)   REFERENCES clients(id)   ON DELETE CASCADE,
  FOREIGN KEY (location_id) REFERENCES locations(id) ON DELETE SET NULL,
  INDEX idx_ads_client_month (client_id, recorded_month)
);

-- Seed February 2026 Ads data
INSERT INTO ads_performance (client_id, campaign_name, campaign_type, spend, clicks, impressions, conversions, ctr, cpc, cpa, conv_rate, recorded_month) VALUES
(2, 'Mecatos Breakfast',                   'Performance Max', 204.29,  715,   49081, 301, 1.46, 0.29, 0.68, 42.10, '2026-02-01'),
(2, 'Dinner at Hoffner',                   'Performance Max', 179.22,  511,   21529, 208, 2.37, 0.35, 0.86, 40.70, '2026-02-01'),
(2, 'Lunch - Hoffner',                     'Performance Max',  99.63,  207,    3252, 123, 6.37, 0.48, 0.81, 59.42, '2026-02-01'),
(2, 'Video views - 2026-01-21',            'Video',           304.12,   16,   22871,   0, 0.07,19.01, 0.00,  0.00, '2026-02-01'),
(2, 'Apopka - Lunch',                      'Performance Max', 412.09, 1554,  116844, 606, 1.33, 0.27, 0.68, 39.00, '2026-02-01'),
(2, 'Local store visits Waterford Lakes',  'Performance Max', 411.02, 1008,   16763, 510, 6.01, 0.41, 0.81, 50.60, '2026-02-01'),
(2, 'Coffee Shop - 101 Lakeunderhill',     'Performance Max', 304.51,  955,   35703, 438, 2.67, 0.32, 0.70, 45.86, '2026-02-01'),
(2, 'Dinner - Apopka',                     'Performance Max',   9.67,   41,    3040,   8, 1.35, 0.24, 1.21, 19.51, '2026-02-01'),
(2, 'Curryford Lunch',                     'Performance Max', 521.55, 1164,   16354, 687, 7.12, 0.45, 0.76, 59.02, '2026-02-01'),
(2, 'Coffee, Bakery & Cafe',               'Performance Max',  86.68,  277,   13021, 137, 2.13, 0.31, 0.63, 49.46, '2026-02-01'),
(2, 'Breakfast Curryford',                 'Performance Max',  50.23,  146,    3550,  78, 4.11, 0.34, 0.64, 53.42, '2026-02-01'),
(2, 'Dinner Curryford',                    'Performance Max',  31.24,  120,    6930,  28, 1.73, 0.26, 1.12, 23.33, '2026-02-01');


-- ============================================================
-- SECTION 14: CONTENT CALENDAR
-- Powers: Content Director dashboard, Jordan Hale calendar
-- ============================================================

CREATE TABLE content_calendar (
  id              INT AUTO_INCREMENT PRIMARY KEY,
  client_id       INT NOT NULL,
  location_id     INT,
  agent_id        INT,
  title           VARCHAR(255) NOT NULL,
  target_keyword  VARCHAR(255),
  secondary_keywords JSON,
  word_count_target INT,
  status          ENUM('planned','briefed','in_progress','review','published','cancelled') DEFAULT 'planned',
  content_score   TINYINT UNSIGNED,                 -- 0-100 after publish
  planned_date    DATE,
  published_date  DATE,
  published_url   VARCHAR(500),
  gbp_aligned     BOOLEAN DEFAULT FALSE,
  ab_test_title_a VARCHAR(255),
  ab_test_title_b VARCHAR(255),
  ab_test_winner  ENUM('a','b','pending') DEFAULT 'pending',
  notes           TEXT,
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at      DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (client_id)   REFERENCES clients(id)   ON DELETE CASCADE,
  FOREIGN KEY (location_id) REFERENCES locations(id) ON DELETE SET NULL,
  FOREIGN KEY (agent_id)    REFERENCES agents(id)    ON DELETE SET NULL,
  INDEX idx_content_client_date (client_id, planned_date),
  INDEX idx_content_status      (status, client_id)
);


-- ============================================================
-- SECTION 15: BACKLINKS
-- Powers: Link Building Agent tracking, anchor ratio monitoring
-- ============================================================

CREATE TABLE backlinks (
  id              INT AUTO_INCREMENT PRIMARY KEY,
  client_id       INT NOT NULL,
  source_domain   VARCHAR(255) NOT NULL,
  source_url      VARCHAR(500),
  target_url      VARCHAR(500),
  anchor_text     VARCHAR(255),
  anchor_type     ENUM('brand','generic','similar','money_exact','brand_exact','naked','other') NOT NULL,
  domain_authority TINYINT UNSIGNED,               -- DA 0-100
  status          ENUM('active','lost','toxic','disavowed') DEFAULT 'active',
  acquired_date   DATE,
  lost_date       DATE,
  cost            DECIMAL(8,2) DEFAULT 0.00,
  agent_id        INT,
  notes           TEXT,
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
  FOREIGN KEY (agent_id)  REFERENCES agents(id)  ON DELETE SET NULL,
  INDEX idx_backlinks_client (client_id, status),
  INDEX idx_backlinks_domain (source_domain)
);


-- ============================================================
-- SECTION 16: AB TESTS
-- Powers: A/B test tracking across all agents
-- ============================================================

CREATE TABLE ab_tests (
  id              INT AUTO_INCREMENT PRIMARY KEY,
  agent_id        INT NOT NULL,
  client_id       INT,
  location_id     INT,
  element         VARCHAR(100) NOT NULL,            -- 'description','post','title','copy','bid'
  hypothesis      TEXT,
  variant_a       TEXT NOT NULL,
  variant_b       TEXT NOT NULL,
  metric          VARCHAR(150),                     -- what we measure
  metric_a_value  DECIMAL(10,4),
  metric_b_value  DECIMAL(10,4),
  winner          ENUM('a','b','inconclusive','pending') DEFAULT 'pending',
  start_date      DATE NOT NULL,
  end_date        DATE,
  status          ENUM('running','completed','cancelled') DEFAULT 'running',
  notes           TEXT,
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (agent_id)    REFERENCES agents(id)    ON DELETE CASCADE,
  FOREIGN KEY (client_id)   REFERENCES clients(id)   ON DELETE SET NULL,
  FOREIGN KEY (location_id) REFERENCES locations(id) ON DELETE SET NULL,
  INDEX idx_abtests_client_status (client_id, status)
);


-- ============================================================
-- SECTION 17: SALES PIPELINE (Tate Owens)
-- Powers: Lead Gen dashboard, pipeline tracker
-- ============================================================

CREATE TABLE sales_pipeline (
  id              INT AUTO_INCREMENT PRIMARY KEY,
  company_name    VARCHAR(150) NOT NULL,
  industry        VARCHAR(100),
  location_count  INT DEFAULT 0,
  contact_name    VARCHAR(150),
  contact_email   VARCHAR(150),
  contact_linkedin VARCHAR(255),
  status          ENUM('identified','contacted','replied','call_booked','proposal_sent','closed','dead') DEFAULT 'identified',
  mrr_potential   DECIMAL(10,2),
  next_action     VARCHAR(255),
  next_action_date DATE,
  source          VARCHAR(100),
  notes           TEXT,
  agent_id        INT,
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at      DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (agent_id) REFERENCES agents(id) ON DELETE SET NULL,
  INDEX idx_pipeline_status (status)
);


-- ============================================================
-- SECTION 18: FINANCIALS (Hugo Fenn)
-- Powers: Finance dashboard, P&L reports, budget tracking
-- ============================================================

CREATE TABLE financials (
  id              INT AUTO_INCREMENT PRIMARY KEY,
  period_month    DATE NOT NULL,                    -- first day of month
  mrr_total       DECIMAL(10,2) DEFAULT 0,
  expenses_total  DECIMAL(10,2) DEFAULT 0,
  net_profit      DECIMAL(10,2) GENERATED ALWAYS AS (mrr_total - expenses_total) STORED,
  net_margin_pct  DECIMAL(5,2)  GENERATED ALWAYS AS (
    CASE WHEN mrr_total > 0
    THEN ROUND(((mrr_total - expenses_total) / mrr_total) * 100, 2)
    ELSE 0 END
  ) STORED,
  expenses_breakdown JSON,
  -- { "ads_tools": 50, "data_api": 3, "hosting": 20,
  --   "backlinks_mecatos": 300, "backlinks_wmf": 100,
  --   "claude_api": 25, "misc": 10 }
  notes           TEXT,
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY unique_month (period_month)
);

CREATE TABLE invoices (
  id              INT AUTO_INCREMENT PRIMARY KEY,
  client_id       INT NOT NULL,
  invoice_number  VARCHAR(50),
  amount          DECIMAL(10,2) NOT NULL,
  status          ENUM('draft','sent','paid','overdue','cancelled') DEFAULT 'draft',
  issue_date      DATE,
  due_date        DATE,
  paid_date       DATE,
  quickbooks_id   VARCHAR(100),
  notes           TEXT,
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
  INDEX idx_invoices_status (status, due_date)
);

CREATE TABLE client_budgets (
  id              INT AUTO_INCREMENT PRIMARY KEY,
  client_id       INT NOT NULL,
  period_month    DATE NOT NULL,
  category        VARCHAR(100) NOT NULL,            -- 'backlinks','ads','tools','other'
  allocated       DECIMAL(10,2) DEFAULT 0,
  spent           DECIMAL(10,2) DEFAULT 0,
  remaining       DECIMAL(10,2) GENERATED ALWAYS AS (allocated - spent) STORED,
  status          ENUM('on_track','over','under') DEFAULT 'on_track',
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
  UNIQUE KEY unique_budget (client_id, period_month, category)
);


-- ============================================================
-- SECTION 19: SUCCESS STORIES (Alexis Morgan)
-- Powers: Customer Success Director, sales assets for Tate
-- ============================================================

CREATE TABLE success_stories (
  id              INT AUTO_INCREMENT PRIMARY KEY,
  client_id       INT NOT NULL,
  title           VARCHAR(255),
  situation       TEXT,
  what_we_did     JSON,                             -- array of action strings
  results         JSON,                             -- array of result strings
  client_quote    TEXT,
  status          ENUM('draft','ready','published') DEFAULT 'draft',
  used_in_outreach BOOLEAN DEFAULT FALSE,
  agent_id        INT,
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
  FOREIGN KEY (agent_id)  REFERENCES agents(id)  ON DELETE SET NULL
);


-- ============================================================
-- SECTION 20: SYSTEM SETTINGS (Super Admin)
-- Powers: Super Admin Dashboard configuration panel
-- ============================================================

CREATE TABLE system_settings (
  id              INT AUTO_INCREMENT PRIMARY KEY,
  setting_key     VARCHAR(100) NOT NULL UNIQUE,
  setting_value   TEXT,
  description     VARCHAR(255),
  updated_by      INT,
  updated_at      DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
);

-- Seed default settings
INSERT INTO system_settings (setting_key, setting_value, description) VALUES
('agency_name',           'Web Market Florida',                  'Agency display name'),
('agency_mrr_target',     '50000',                               'Monthly revenue target in USD'),
('agent_runner_port',     '3100',                                'REST API port'),
('dataforseo_enabled',    'true',                                 'DataForSEO API active'),
('gsc_sync_day',          'Monday',                               'Day to pull GSC data'),
('grid_ranking_day',      'Monday',                               'Day to pull DataForSEO grid rankings'),
('report_delivery_day',   'Friday',                               'Weekly report delivery day'),
('claude_model',          'claude-sonnet-4-20250514',            'Claude model for agent runner'),
('max_tokens_per_run',    '4000',                                 'Max tokens per agent run'),
('n8n_webhook_base_url',  'http://localhost:5678',               'n8n local instance URL'),
('timezone',              'America/New_York',                     'Agency timezone');


-- ============================================================
-- USEFUL VIEWS FOR THE CONSOLE & SUPER ADMIN DASHBOARD
-- ============================================================

-- Client health summary (powers dashboard cards)
CREATE VIEW v_client_health AS
SELECT
  c.id,
  c.name,
  c.slug,
  c.brand_color,
  c.logo_initials,
  c.mrr,
  c.status,
  COUNT(DISTINCT l.id)  AS location_count,
  COUNT(DISTINCT CASE WHEN a.status = 'open' THEN a.id END) AS open_alerts,
  COUNT(DISTINCT ag.id) AS agent_count,
  ROUND(AVG(k.current_value / NULLIF(k.target_value,0) * 100), 0) AS health_score
FROM clients c
LEFT JOIN locations l    ON l.client_id = c.id AND l.status = 'active'
LEFT JOIN alerts a       ON a.client_id = c.id AND a.status = 'open'
LEFT JOIN agents ag      ON ag.client_id = c.id AND ag.status = 'active'
LEFT JOIN kpis k         ON k.client_id = c.id
  AND k.recorded_at = (SELECT MAX(recorded_at) FROM kpis WHERE client_id = c.id)
GROUP BY c.id;

-- Agent status summary (powers agent army view)
CREATE VIEW v_agent_status AS
SELECT
  ag.id,
  ag.name,
  ag.role,
  ag.tier,
  ag.type,
  ag.status,
  ag.run_frequency,
  ag.last_run_at,
  ag.last_run_status,
  ag.kpi_score,
  c.name  AS client_name,
  c.slug  AS client_slug,
  rep.name AS reports_to_name,
  COUNT(DISTINCT al.id) AS total_runs,
  MAX(al.created_at)    AS last_activity,
  COUNT(DISTINCT CASE WHEN al.created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY) THEN al.id END) AS runs_this_week
FROM agents ag
LEFT JOIN clients c    ON c.id = ag.client_id
LEFT JOIN agents rep   ON rep.id = ag.reports_to_id
LEFT JOIN agent_logs al ON al.agent_id = ag.id
GROUP BY ag.id;

-- KPI tracker view (powers KPI dashboard section)
CREATE VIEW v_kpi_tracker AS
SELECT
  k.agent_id,
  ag.name  AS agent_name,
  ag.role  AS agent_role,
  c.name   AS client_name,
  k.name   AS kpi_name,
  k.unit,
  k.current_value,
  k.target_value,
  k.previous_value,
  ROUND(k.current_value / NULLIF(k.target_value,0) * 100, 1) AS pct_of_target,
  k.status,
  k.recorded_at
FROM kpis k
JOIN agents  ag ON ag.id = k.agent_id
LEFT JOIN clients c  ON c.id  = k.client_id;

-- GBP monthly comparison (powers location health cards)
CREATE VIEW v_gbp_comparison AS
SELECT
  l.id AS location_id,
  l.name AS location_name,
  c.name AS client_name,
  curr.recorded_month,
  curr.direction_requests,
  curr.website_clicks,
  curr.calls,
  curr.avg_review_score,
  curr.unanswered_reviews,
  prev.direction_requests AS prev_direction_requests,
  prev.website_clicks     AS prev_website_clicks,
  prev.calls              AS prev_calls,
  ROUND((curr.direction_requests - prev.direction_requests) / NULLIF(prev.direction_requests,0) * 100, 1) AS direction_change_pct,
  ROUND((curr.website_clicks - prev.website_clicks) / NULLIF(prev.website_clicks,0) * 100, 1) AS clicks_change_pct,
  ROUND((curr.calls - prev.calls) / NULLIF(prev.calls,0) * 100, 1) AS calls_change_pct
FROM gbp_metrics curr
JOIN locations l ON l.id = curr.location_id
JOIN clients c   ON c.id = l.client_id
LEFT JOIN gbp_metrics prev
  ON prev.location_id = curr.location_id
  AND prev.recorded_month = DATE_SUB(curr.recorded_month, INTERVAL 1 MONTH);


-- ============================================================
-- INDEXES FOR PERFORMANCE
-- ============================================================

ALTER TABLE agent_logs       ADD INDEX idx_logs_created  (created_at);
ALTER TABLE alerts           ADD INDEX idx_alerts_created (created_at);
ALTER TABLE calendar_events  ADD INDEX idx_cal_created    (created_at);


-- ============================================================
-- DONE
-- ============================================================
-- Tables: 20
-- Views:  4
-- Seed data: users, clients, locations, agents, gbp_metrics, ads_performance, settings
-- Next step: Run REST API on port 3100
-- ============================================================
