-- ============================================================
--  VerifyDocs Blog CMS — Database Schema
--  Engine: MySQL 5.7+ / MariaDB 10.3+
--  Charset: utf8mb4 / utf8mb4_unicode_ci
-- ============================================================

CREATE DATABASE IF NOT EXISTS `verifydocs_blog`
    DEFAULT CHARACTER SET utf8mb4
    DEFAULT COLLATE utf8mb4_unicode_ci;

USE `verifydocs_blog`;

-- ─── Admin Users ────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `admins` (
    `id`            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `name`          VARCHAR(120)  NOT NULL,
    `email`         VARCHAR(180)  NOT NULL UNIQUE,
    `password_hash` VARCHAR(255)  NOT NULL,
    `avatar`        VARCHAR(400)  DEFAULT NULL,
    `role`          ENUM('super','editor') NOT NULL DEFAULT 'editor',
    `last_login`    DATETIME      DEFAULT NULL,
    `created_at`    DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Default admin: admin@verifydocs.in / Admin@123
INSERT INTO `admins` (`name`, `email`, `password_hash`, `role`) VALUES
('Shakil', 'admin@verifydocs.in', '$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'super');
-- Note: password above is hashed 'Admin@123' — change immediately on production

-- ─── Categories ─────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `categories` (
    `id`          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `name`        VARCHAR(80)  NOT NULL UNIQUE,
    `slug`        VARCHAR(100) NOT NULL UNIQUE,
    `color`       VARCHAR(20)  NOT NULL DEFAULT '#1550FF',
    `description` TEXT         DEFAULT NULL,
    `created_at`  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `categories` (`name`, `slug`, `color`) VALUES
('Identity',  'identity',  '#1550FF'),
('Biometric', 'biometric', '#7C3AED'),
('Financial', 'financial', '#00A876'),
('Business',  'business',  '#C97A17'),
('Document',  'document',  '#DB2777');

-- ─── Tags ───────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `tags` (
    `id`         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `name`       VARCHAR(80)  NOT NULL UNIQUE,
    `slug`       VARCHAR(100) NOT NULL UNIQUE,
    `created_at` DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `tags` (`name`, `slug`) VALUES
('Aadhaar', 'aadhaar'),
('PAN', 'pan'),
('KYC', 'kyc'),
('Fintech', 'fintech'),
('API', 'api'),
('Biometric', 'biometric'),
('Compliance', 'compliance'),
('NBFC', 'nbfc'),
('Face Match', 'face-match'),
('Liveness', 'liveness'),
('GST', 'gst'),
('Bank Verification', 'bank-verification'),
('Developer', 'developer'),
('Security', 'security');

-- ─── Blog Posts ─────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `posts` (
    `id`               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `admin_id`         INT UNSIGNED NOT NULL,
    `category_id`      INT UNSIGNED NOT NULL,
    `title`            VARCHAR(300) NOT NULL,
    `slug`             VARCHAR(320) NOT NULL UNIQUE,
    `excerpt`          TEXT         DEFAULT NULL,
    `content`          LONGTEXT     NOT NULL,
    `featured_image`   VARCHAR(400) DEFAULT NULL,
    `status`           ENUM('draft','published','scheduled') NOT NULL DEFAULT 'draft',
    `published_at`     DATETIME     DEFAULT NULL,
    `scheduled_at`     DATETIME     DEFAULT NULL,
    `views`            INT UNSIGNED NOT NULL DEFAULT 0,
    -- SEO
    `meta_title`       VARCHAR(180) DEFAULT NULL,
    `meta_desc`        VARCHAR(320) DEFAULT NULL,
    `focus_keyword`    VARCHAR(120) DEFAULT NULL,
    `canonical_url`    VARCHAR(400) DEFAULT NULL,
    `robots`           VARCHAR(80)  NOT NULL DEFAULT 'index, follow',
    `og_title`         VARCHAR(180) DEFAULT NULL,
    `og_desc`          VARCHAR(320) DEFAULT NULL,
    `og_image`         VARCHAR(400) DEFAULT NULL,
    `twitter_title`    VARCHAR(180) DEFAULT NULL,
    `twitter_desc`     VARCHAR(320) DEFAULT NULL,
    `schema_json`      TEXT         DEFAULT NULL,
    -- Author overrides
    `author_name`      VARCHAR(120) DEFAULT NULL,
    `author_bio`       TEXT         DEFAULT NULL,
    `author_image`     VARCHAR(400) DEFAULT NULL,
    `author_social`    JSON         DEFAULT NULL,
    -- Timestamps
    `created_at`       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    FOREIGN KEY (`admin_id`)    REFERENCES `admins`(`id`)     ON DELETE RESTRICT,
    FOREIGN KEY (`category_id`) REFERENCES `categories`(`id`) ON DELETE RESTRICT,
    INDEX idx_status   (`status`),
    INDEX idx_slug     (`slug`),
    INDEX idx_pub_date (`published_at`),
    FULLTEXT idx_search(`title`, `excerpt`, `content`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── Post Tags (pivot) ──────────────────────────────────────
CREATE TABLE IF NOT EXISTS `post_tags` (
    `post_id` INT UNSIGNED NOT NULL,
    `tag_id`  INT UNSIGNED NOT NULL,
    PRIMARY KEY (`post_id`, `tag_id`),
    FOREIGN KEY (`post_id`) REFERENCES `posts`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`tag_id`)  REFERENCES `tags`(`id`)  ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── API Pricing ────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `api_pricing` (
    `id`               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `name`             VARCHAR(120) NOT NULL,
    `slug`             VARCHAR(140) NOT NULL UNIQUE,
    `category`         VARCHAR(80)  NOT NULL,
    `description`      TEXT         DEFAULT NULL,
    `price_per_hit`    DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `enterprise_label` VARCHAR(80)  NOT NULL DEFAULT 'Custom',
    `min_hits`         INT UNSIGNED DEFAULT 0,
    `response_time`    VARCHAR(40)  DEFAULT NULL,
    `is_enabled`       TINYINT(1)   NOT NULL DEFAULT 1,
    `sort_order`       INT UNSIGNED NOT NULL DEFAULT 0,
    `created_at`       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `api_pricing` (`name`, `slug`, `category`, `price_per_hit`, `response_time`, `sort_order`) VALUES
('Aadhaar Verification',     'aadhaar-verification',    'Identity',  2.50, '~380ms', 1),
('PAN Verification',         'pan-verification',        'Identity',  1.80, '~290ms', 2),
('Voter ID Verification',    'voter-id-verification',   'Identity',  2.00, '~310ms', 3),
('Passport Verification',    'passport-verification',   'Identity',  3.00, '~420ms', 4),
('Driving License',          'driving-license',         'Identity',  2.20, '~350ms', 5),
('Bank Account Verify',      'bank-account-verify',     'Financial', 3.20, '~460ms', 6),
('GST Verification',         'gst-verification',        'Business',  1.50, '~300ms', 7),
('Company/MCA Lookup',       'company-mca-lookup',      'Business',  2.80, '~500ms', 8),
('MSME Verification',        'msme-verification',       'Business',  2.00, '~380ms', 9),
('Face Match',               'face-match',              'Biometric', 4.00, '~520ms', 10),
('Liveness Detection',       'liveness-detection',      'Biometric', 5.50, '~680ms', 11),
('OCR Document',             'ocr-document',            'Document',  6.00, '~750ms', 12),
('Aadhaar OCR',              'aadhaar-ocr',             'Document',  3.50, '~600ms', 13),
('PAN OCR',                  'pan-ocr',                 'Document',  2.50, '~550ms', 14),
('Credit Bureau',            'credit-bureau',           'Financial', 8.00, '~900ms', 15),
('UPI Verification',         'upi-verification',        'Financial', 1.20, '~250ms', 16);

-- ─── Blog Leads ─────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `blog_leads` (
    `id`         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `post_id`    INT UNSIGNED DEFAULT NULL,
    `name`       VARCHAR(120) NOT NULL,
    `email`      VARCHAR(180) NOT NULL,
    `company`    VARCHAR(160) DEFAULT NULL,
    `mobile`     VARCHAR(20)  DEFAULT NULL,
    `message`    TEXT         DEFAULT NULL,
    `source_url` VARCHAR(400) DEFAULT NULL,
    `ip`         VARCHAR(45)  DEFAULT NULL,
    `created_at` DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`post_id`) REFERENCES `posts`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── Sample Post ────────────────────────────────────────────
INSERT INTO `posts`
    (`admin_id`,`category_id`,`title`,`slug`,`excerpt`,`content`,`status`,`published_at`,`views`,`meta_title`,`meta_desc`,`focus_keyword`,`robots`,`author_name`,`author_bio`)
VALUES (
    1, 1,
    'How Aadhaar API Powers Instant KYC for Fintechs',
    'how-aadhaar-api-powers-instant-kyc-for-fintechs',
    'Discover how real-time Aadhaar verification is transforming digital onboarding in India\'s fintech ecosystem — and how to integrate it in minutes.',
    '<h2>Introduction</h2><p>Aadhaar-based KYC has become the backbone of digital onboarding for Indian fintechs. With over 1.3 billion unique IDs issued, the Aadhaar ecosystem provides unparalleled reach and reliability for identity verification.</p><h2>How Aadhaar API Works</h2><p>VerifyDocs Aadhaar API provides instant OTP-based verification with sub-400ms response times, ensuring your onboarding flow remains frictionless.</p><p>The verification flow follows a 3-step process:</p><ol><li>Send OTP to the registered Aadhaar mobile number</li><li>User enters the received OTP</li><li>API validates and returns verified demographic data</li></ol><h2>Integration Steps</h2><pre><code>POST https://api.verifydocs.in/v1/aadhaar/verify\nAuthorization: Bearer {YOUR_API_KEY}\n\n{\n  "aadhaar_number": "XXXX XXXX 4821",\n  "otp": "123456"\n}</code></pre><h2>Use Cases for Fintechs</h2><ul><li>Instant loan application KYC</li><li>Digital savings account opening</li><li>BNPL onboarding under 60 seconds</li><li>Mutual fund e-KYC compliance</li></ul><h2>Security &amp; Compliance</h2><p>All API calls are encrypted with TLS 1.3. Data is processed in India, compliant with UIDAI guidelines and RBI KYC norms.</p><h2>Conclusion</h2><p>Aadhaar verification is no longer optional for fintechs — it is the foundation of trust. VerifyDocs makes integration a matter of hours, not weeks.</p>',
    'published', NOW(), 4821,
    'Aadhaar API for Instant KYC — VerifyDocs',
    'Learn how to integrate Aadhaar verification API for real-time KYC. Sub-400ms response, UIDAI compliant, production-ready.',
    'aadhaar verification api kyc',
    'index, follow',
    'Shakil',
    'Founder & Product Lead at VerifyDocs. Building India''s verification infrastructure.'
);

INSERT INTO `posts`
    (`admin_id`,`category_id`,`title`,`slug`,`excerpt`,`content`,`status`,`published_at`,`views`,`meta_title`,`meta_desc`,`focus_keyword`,`robots`,`author_name`,`author_bio`)
VALUES (
    1, 3,
    'Bank Account Verification API: Complete Guide for NBFCs',
    'bank-account-verification-api-guide-nbfc',
    'Why bank account verification is critical for NBFC compliance — and how to automate it with VerifyDocs API.',
    '<h2>Introduction</h2><p>Bank account verification is a mandatory step in NBFC lending workflows. Manual verification causes delays and fraud risk. VerifyDocs Bank Account Verify API eliminates both.</p><h2>What Gets Verified</h2><ul><li>Account holder name</li><li>Account number validity</li><li>IFSC code</li><li>Account type (savings/current)</li><li>Account status (active/inactive)</li></ul><h2>RBI Compliance</h2><p>The API is designed to meet RBI KYC norms for digital lending. All penny-drop verifications are logged and auditable.</p><h2>Integration</h2><pre><code>POST /v1/bank/verify\n{\n  "account_number": "1234567890",\n  "ifsc": "SBIN0001234",\n  "name": "Rahul Sharma"\n}</code></pre><h2>Conclusion</h2><p>Automate your NBFC bank verification in one day with VerifyDocs.</p>',
    'published', DATE_SUB(NOW(), INTERVAL 5 DAY), 1876,
    'Bank Account Verification API for NBFCs — VerifyDocs',
    'Automate bank account verification for NBFC compliance. Penny-drop, IFSC validation, and name match in one API call.',
    'bank account verification api nbfc',
    'index, follow',
    'Shakil',
    'Founder & Product Lead at VerifyDocs.'
);

INSERT INTO `posts`
    (`admin_id`,`category_id`,`title`,`slug`,`excerpt`,`content`,`status`,`published_at`,`views`,`author_name`,`author_bio`)
VALUES (
    1, 2,
    'Face Match vs Liveness Detection: Which API Do You Need?',
    'face-match-vs-liveness-detection-api',
    'A deep-dive comparison of face matching and liveness detection APIs for secure biometric KYC.',
    '<h2>Face Match API</h2><p>Compares two face images to determine if they belong to the same person. Used to match selfie vs Aadhaar photo or PAN card photo.</p><h2>Liveness Detection API</h2><p>Ensures the selfie is taken from a live person and not a spoof attempt using a photo or mask.</p><h2>When to Use Both</h2><p>Best practice: run liveness detection first, then face match. This eliminates spoof attacks and identity fraud in a single onboarding step.</p>',
    'published', DATE_SUB(NOW(), INTERVAL 12 DAY), 2340,
    'Shakil',
    'Founder & Product Lead at VerifyDocs.'
);

-- ─── COMPONENT_LIBRARY_ADDENDUM (stored here for AI reference) ──
-- See COMPONENT_LIBRARY.md for base design system.
-- Blog/Admin additions:
--   .post-card        — public blog card (blog.css)
--   .filter-chip      — category filter pill (blog.css)
--   .article-content  — single post prose area (blog.css)
--   .lead-form        — dark in-article lead capture (blog.css)
--   .toc-sidebar      — sticky table of contents (blog.css)
--   .author-box       — post author card (blog.css)
--   Admin: .stat-card, .panel, .data-table, .editor-wrap,
--          .editor-toolbar, .toolbar-btn, .seo-preview,
--          .upload-area, .toggle, .badge-*, .toast, .modal
-- Tag associations for sample posts
INSERT INTO `post_tags` (`post_id`, `tag_id`) VALUES
(1, 1), (1, 3), (1, 4), (1, 5),
(2, 8), (2, 7), (2, 12), (2, 5),
(3, 9), (3, 10), (3, 6), (3, 14);