-- Pinakra ADMan — Multi-Tenant MySQL Schema
-- Run this once to initialize the database

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

-- Agencies (tenants)
CREATE TABLE agencies (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  slug VARCHAR(100) UNIQUE NOT NULL,
  logo_url VARCHAR(500) DEFAULT NULL,
  settings JSON DEFAULT NULL,
  is_active BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Brands / Vendors (Amazon Sellers)
CREATE TABLE brands (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  agency_id INT UNSIGNED NOT NULL,
  name VARCHAR(255) NOT NULL,
  marketplace VARCHAR(10) DEFAULT 'IN',
  currency VARCHAR(3) DEFAULT 'INR',
  sp_api_client_id VARCHAR(500) DEFAULT NULL,
  sp_api_client_secret_encrypted VARCHAR(500) DEFAULT NULL,
  sp_api_refresh_token_encrypted VARCHAR(500) DEFAULT NULL,
  sp_api_aws_access_key VARCHAR(255) DEFAULT NULL,
  sp_api_aws_secret_key_encrypted VARCHAR(500) DEFAULT NULL,
  selling_partner_id VARCHAR(50) DEFAULT NULL,
  advertising_profile_id VARCHAR(50) DEFAULT NULL,
  auth_method ENUM('manual','oauth') DEFAULT 'manual',
  auth_status ENUM('pending','active','expired','revoked') DEFAULT 'pending',
  auth_expires_at TIMESTAMP NULL DEFAULT NULL,
  oauth_state VARCHAR(255) DEFAULT NULL,
  settings JSON DEFAULT NULL,
  is_active BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (agency_id) REFERENCES agencies(id) ON DELETE CASCADE,
  INDEX idx_agency (agency_id)
) ENGINE=InnoDB;

-- SP-API token cache
CREATE TABLE sp_api_tokens (
  brand_id INT UNSIGNED PRIMARY KEY,
  access_token TEXT NOT NULL,
  expires_at TIMESTAMP NULL DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (brand_id) REFERENCES brands(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Users
CREATE TABLE users (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  agency_id INT UNSIGNED NOT NULL,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('admin','manager','viewer') DEFAULT 'viewer',
  brand_access JSON DEFAULT NULL,
  is_active BOOLEAN DEFAULT TRUE,
  last_login_at TIMESTAMP NULL DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (agency_id) REFERENCES agencies(id) ON DELETE CASCADE,
  UNIQUE KEY uk_email_agency (email, agency_id)
) ENGINE=InnoDB;

-- Campaigns
CREATE TABLE campaigns (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  brand_id INT UNSIGNED NOT NULL,
  amazon_campaign_id VARCHAR(50) NOT NULL,
  name VARCHAR(255) NOT NULL,
  type ENUM('sp','sb','sd','auto','manual') NOT NULL DEFAULT 'sp',
  match_type VARCHAR(20) DEFAULT NULL,
  status ENUM('active','paused','archived') DEFAULT 'active',
  daily_budget DECIMAL(12,2) DEFAULT NULL,
  bid DECIMAL(10,2) DEFAULT NULL,
  asin VARCHAR(20) DEFAULT NULL,
  product_name VARCHAR(255) DEFAULT NULL,
  portfolio_id INT UNSIGNED DEFAULT NULL,
  spend DECIMAL(12,2) DEFAULT 0,
  sales DECIMAL(12,2) DEFAULT 0,
  impressions INT UNSIGNED DEFAULT 0,
  clicks INT UNSIGNED DEFAULT 0,
  orders INT UNSIGNED DEFAULT 0,
  last_synced_at TIMESTAMP NULL DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (brand_id) REFERENCES brands(id) ON DELETE CASCADE,
  UNIQUE KEY uk_amazon_campaign (brand_id, amazon_campaign_id),
  INDEX idx_brand_status (brand_id, status)
) ENGINE=InnoDB;

-- Portfolios
CREATE TABLE portfolios (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  brand_id INT UNSIGNED NOT NULL,
  amazon_portfolio_id VARCHAR(50) DEFAULT NULL,
  name VARCHAR(255) NOT NULL,
  budget DECIMAL(12,2) DEFAULT NULL,
  status ENUM('active','paused') DEFAULT 'active',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (brand_id) REFERENCES brands(id) ON DELETE CASCADE,
  UNIQUE KEY uk_amazon_portfolio (brand_id, amazon_portfolio_id)
) ENGINE=InnoDB;

-- Keywords
CREATE TABLE keywords (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  brand_id INT UNSIGNED NOT NULL,
  campaign_id INT UNSIGNED NOT NULL,
  amazon_keyword_id VARCHAR(50) NOT NULL,
  text VARCHAR(500) NOT NULL,
  match_type ENUM('exact','phrase','broad','auto') NOT NULL,
  bid DECIMAL(10,2) DEFAULT NULL,
  status ENUM('active','paused','archived') DEFAULT 'active',
  impressions INT UNSIGNED DEFAULT 0,
  clicks INT UNSIGNED DEFAULT 0,
  spend DECIMAL(12,2) DEFAULT 0,
  orders INT UNSIGNED DEFAULT 0,
  sales DECIMAL(12,2) DEFAULT 0,
  last_synced_at TIMESTAMP NULL DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (brand_id) REFERENCES brands(id) ON DELETE CASCADE,
  FOREIGN KEY (campaign_id) REFERENCES campaigns(id) ON DELETE CASCADE,
  UNIQUE KEY uk_amazon_keyword (brand_id, amazon_keyword_id),
  INDEX idx_campaign (campaign_id)
) ENGINE=InnoDB;

-- Search Terms
CREATE TABLE search_terms (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  brand_id INT UNSIGNED NOT NULL,
  campaign_id INT UNSIGNED NOT NULL,
  query VARCHAR(500) NOT NULL,
  impressions INT UNSIGNED DEFAULT 0,
  clicks INT UNSIGNED DEFAULT 0,
  spend DECIMAL(12,2) DEFAULT 0,
  sales DECIMAL(12,2) DEFAULT 0,
  orders INT UNSIGNED DEFAULT 0,
  is_negative BOOLEAN DEFAULT FALSE,
  first_seen DATE DEFAULT NULL,
  last_seen DATE DEFAULT NULL,
  FOREIGN KEY (brand_id) REFERENCES brands(id) ON DELETE CASCADE,
  FOREIGN KEY (campaign_id) REFERENCES campaigns(id) ON DELETE CASCADE,
  INDEX idx_brand_query (brand_id, query(255)),
  INDEX idx_campaign_acos (campaign_id, spend)
) ENGINE=InnoDB;

-- Automation Rules
CREATE TABLE rules (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  brand_id INT UNSIGNED NOT NULL,
  campaign_id INT UNSIGNED DEFAULT NULL,
  name VARCHAR(255) NOT NULL,
  metric ENUM('acos','ctr','spend','roas','cpc','impressions','clicks','orders') NOT NULL,
  operator ENUM('>','<','>=','<=','=') NOT NULL,
  threshold DECIMAL(12,2) NOT NULL,
  action ENUM('pause','enable','increase_bid','decrease_bid','adjust_budget') NOT NULL,
  adjustment_pct DECIMAL(5,2) DEFAULT 0,
  apply_to ENUM('campaign','keyword','portfolio') DEFAULT 'campaign',
  schedule_type ENUM('continuous','hourly','daily','weekly') DEFAULT 'continuous',
  schedule_config JSON DEFAULT NULL,
  cooldown_hours INT UNSIGNED DEFAULT 0,
  status ENUM('active','inactive') DEFAULT 'active',
  trigger_count INT UNSIGNED DEFAULT 0,
  last_triggered_at TIMESTAMP NULL DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (brand_id) REFERENCES brands(id) ON DELETE CASCADE,
  FOREIGN KEY (campaign_id) REFERENCES campaigns(id) ON DELETE SET NULL,
  INDEX idx_brand_status (brand_id, status),
  INDEX idx_campaign (campaign_id)
) ENGINE=InnoDB;

-- Rule Execution Log
CREATE TABLE rule_executions (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  rule_id INT UNSIGNED NOT NULL,
  brand_id INT UNSIGNED NOT NULL,
  campaign_id INT UNSIGNED DEFAULT NULL,
  keyword_id INT UNSIGNED DEFAULT NULL,
  action_data JSON DEFAULT NULL,
  result ENUM('success','failed','skipped') NOT NULL,
  error_message TEXT DEFAULT NULL,
  executed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (rule_id) REFERENCES rules(id) ON DELETE CASCADE,
  FOREIGN KEY (brand_id) REFERENCES brands(id) ON DELETE CASCADE,
  INDEX idx_rule (rule_id),
  INDEX idx_executed (executed_at)
) ENGINE=InnoDB;

-- Activity Logs
CREATE TABLE activity_logs (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  brand_id INT UNSIGNED NOT NULL,
  user_id INT UNSIGNED DEFAULT NULL,
  action VARCHAR(100) NOT NULL,
  campaign VARCHAR(255) DEFAULT NULL,
  detail TEXT DEFAULT NULL,
  type ENUM('success','warning','error','info') DEFAULT 'info',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (brand_id) REFERENCES brands(id) ON DELETE CASCADE,
  INDEX idx_brand_created (brand_id, created_at DESC)
) ENGINE=InnoDB;

-- Hourly Bid Config
CREATE TABLE hourly_bid_configs (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  campaign_id INT UNSIGNED NOT NULL,
  is_enabled BOOLEAN DEFAULT FALSE,
  min_bid DECIMAL(10,2) DEFAULT NULL,
  max_bid DECIMAL(10,2) DEFAULT NULL,
  adjustment_pct DECIMAL(5,2) DEFAULT 10,
  schedule_24h JSON DEFAULT NULL,
  last_run_at TIMESTAMP NULL DEFAULT NULL,
  last_multiplier DECIMAL(3,2) DEFAULT NULL,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (campaign_id) REFERENCES campaigns(id) ON DELETE CASCADE,
  UNIQUE KEY uk_campaign (campaign_id)
) ENGINE=InnoDB;

-- Listings (AI quality scores)
CREATE TABLE listings (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  brand_id INT UNSIGNED NOT NULL,
  asin VARCHAR(20) NOT NULL,
  title TEXT DEFAULT NULL,
  category VARCHAR(255) DEFAULT NULL,
  listing_score INT UNSIGNED DEFAULT 0,
  score_breakdown JSON DEFAULT NULL,
  ai_suggestions JSON DEFAULT NULL,
  last_analyzed_at TIMESTAMP NULL DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (brand_id) REFERENCES brands(id) ON DELETE CASCADE,
  UNIQUE KEY uk_brand_asin (brand_id, asin)
) ENGINE=InnoDB;

-- Sync Log
CREATE TABLE sync_logs (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  brand_id INT UNSIGNED NOT NULL,
  sync_type ENUM('campaigns','keywords','search_terms','reports') NOT NULL,
  status ENUM('running','success','failed') DEFAULT 'running',
  records_processed INT UNSIGNED DEFAULT 0,
  error_message TEXT DEFAULT NULL,
  started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  finished_at TIMESTAMP NULL DEFAULT NULL,
  FOREIGN KEY (brand_id) REFERENCES brands(id) ON DELETE CASCADE,
  INDEX idx_brand_type (brand_id, sync_type)
) ENGINE=InnoDB;

-- Daily Snapshots (for trend charts)
CREATE TABLE daily_snapshots (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  brand_id INT UNSIGNED NOT NULL,
  snapshot_date DATE NOT NULL,
  total_spend DECIMAL(12,2) DEFAULT 0,
  total_sales DECIMAL(12,2) DEFAULT 0,
  impressions INT UNSIGNED DEFAULT 0,
  clicks INT UNSIGNED DEFAULT 0,
  orders_count INT UNSIGNED DEFAULT 0,
  active_campaigns INT UNSIGNED DEFAULT 0,
  captured_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (brand_id) REFERENCES brands(id) ON DELETE CASCADE,
  UNIQUE KEY uk_brand_date (brand_id, snapshot_date)
) ENGINE=InnoDB;

-- Job Queue
CREATE TABLE job_queue (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  brand_id INT UNSIGNED NOT NULL,
  job_type ENUM('sync_campaigns','sync_keywords','execute_rules','hourly_bid_optimize','sync_search_terms','generate_reports') NOT NULL,
  status ENUM('pending','running','completed','failed') DEFAULT 'pending',
  priority TINYINT UNSIGNED DEFAULT 0,
  scheduled_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  started_at TIMESTAMP NULL DEFAULT NULL,
  completed_at TIMESTAMP NULL DEFAULT NULL,
  error_message TEXT DEFAULT NULL,
  FOREIGN KEY (brand_id) REFERENCES brands(id) ON DELETE CASCADE,
  INDEX idx_status_scheduled (status, scheduled_at)
) ENGINE=InnoDB;

-- Insert demo agency + admin user
INSERT INTO agencies (name, slug, settings) VALUES
  ('Pinakra HQ', 'pinakra-hq', '{"timezone":"Asia/Kolkata"}');

INSERT INTO users (agency_id, name, email, password_hash, role) VALUES
  (1, 'Admin', 'admin@pinakra.com', '$2y$10$N9qo8uLOickgx2ZMRZoMyeIjZAgcfl7p92ldGxad68LJZdL17lhWy', 'admin');
-- Password: 'password' — run on server: php -r "echo password_hash('demo1234', PASSWORD_BCRYPT);" then UPDATE users SET password_hash = '...'

INSERT INTO brands (agency_id, name, marketplace, currency, is_active) VALUES
  (1, 'Demo Brand (IN)', 'IN', 'INR', 1),
  (1, 'Demo Brand (US)', 'US', 'USD', 0);

INSERT INTO campaigns (brand_id, amazon_campaign_id, name, type, status, daily_budget, spend, sales, impressions, clicks, orders, asin, product_name) VALUES
  (1, 'DEMO-SP-001', 'Summer Sale – SP Auto', 'sp', 'active', 1000, 450, 3200, 15000, 320, 12, 'B08N5WRWNW', 'Wireless Earbuds Pro'),
  (1, 'DEMO-SB-001', 'Brand Awareness – Desktop', 'sb', 'active', 2000, 820, 5200, 42000, 980, 24, 'B08N5WRWNW', 'Wireless Earbuds Pro'),
  (1, 'DEMO-SD-001', 'Retargeting – Viewers', 'sd', 'paused', 500, 120, 450, 6000, 95, 3, NULL, NULL),
  (1, 'DEMO-SP-002', 'Top Keywords – Exact', 'sp', 'active', 1500, 670, 4100, 22000, 510, 18, 'B07XJ8C8F5', 'Bluetooth Speaker X1');

INSERT INTO keywords (brand_id, campaign_id, amazon_keyword_id, text, match_type, bid, status, impressions, clicks, spend, sales, orders) VALUES
  (1, 1, 'KW-001', 'wireless earbuds', 'exact', 25.00, 'active', 5000, 120, 300, 1200, 4),
  (1, 1, 'KW-002', 'bluetooth earphones', 'phrase', 18.00, 'active', 3500, 80, 150, 850, 3),
  (1, 1, 'KW-003', 'best wireless earphones', 'broad', 15.00, 'active', 6500, 120, 120, 500, 2),
  (1, 2, 'KW-004', 'wireless audio', 'exact', 30.00, 'active', 12000, 300, 280, 1800, 8),
  (1, 2, 'KW-005', 'earbuds for running', 'phrase', 22.00, 'active', 8000, 200, 220, 1200, 5),
  (1, 4, 'KW-006', 'bluetooth speaker portable', 'exact', 35.00, 'active', 9000, 250, 300, 2100, 7),
  (1, 4, 'KW-007', 'waterproof speaker', 'phrase', 28.00, 'active', 7000, 160, 200, 1200, 5),
  (1, 4, 'KW-008', 'cheap bluetooth speaker', 'broad', 12.00, 'paused', 6000, 100, 170, 800, 6);

INSERT INTO daily_snapshots (brand_id, snapshot_date, total_spend, total_sales, impressions, clicks, orders_count, active_campaigns) VALUES
  (1, DATE_SUB(CURDATE(), INTERVAL 6 DAY), 1300, 8200, 35000, 1200, 38, 3),
  (1, DATE_SUB(CURDATE(), INTERVAL 5 DAY), 1450, 9100, 40000, 1400, 42, 3),
  (1, DATE_SUB(CURDATE(), INTERVAL 4 DAY), 1280, 7800, 37000, 1280, 36, 3),
  (1, DATE_SUB(CURDATE(), INTERVAL 3 DAY), 1600, 10200, 45000, 1550, 48, 3),
  (1, DATE_SUB(CURDATE(), INTERVAL 2 DAY), 1520, 9500, 42000, 1480, 44, 3),
  (1, DATE_SUB(CURDATE(), INTERVAL 1 DAY), 1700, 10800, 48000, 1620, 50, 3),
  (1, CURDATE(), 1560, 9800, 44000, 1500, 46, 3);
