-- ============================================================================
-- Phase 8: Additional Simple Inventory Reports
-- Purpose: 3 focused reports (Top 10 Best Sellers, Slow Moving, VAT Report)
-- ============================================================================

-- ============================================================================
-- Module 11: Top 10 Best Sellers Report
-- Query: Product Movement - Top 10 by quantity sold
-- Uses: AggregateRowsController GROUP BY product_id
-- ============================================================================
INSERT INTO modules (module_id, name, config, org_id, branch_id, icon, aria_label, description) VALUES
  (
    uuid_generate_v7(),
    'top_10_best_sellers',
    '{"title":"Top 10 Best Sellers","body":[{"type":"html","html":"<div data-controller=\"Top10BestSellers\" class=\"space-y-6\">\n  <div class=\"flex flex-wrap items-center justify-between gap-3\">\n    <div class=\"flex flex-wrap gap-3\">\n      <label class=\"form-control w-48\">\n        <span class=\"label-text text-sm\">Start Date</span>\n        <input type=\"date\" class=\"input input-bordered input-sm\" data-filter=\"start-date\" />\n      </label>\n      <label class=\"form-control w-48\">\n        <span class=\"label-text text-sm\">End Date</span>\n        <input type=\"date\" class=\"input input-bordered input-sm\" data-filter=\"end-date\" />\n      </label>\n      <label class=\"form-control w-40\">\n        <span class=\"label-text text-sm\">Branch</span>\n        <select class=\"select select-bordered select-sm\" data-filter=\"branch\">\n          <option value=\"\">All Branches</option>\n        </select>\n      </label>\n    </div>\n    <button class=\"btn btn-sm btn-primary\" data-role=\"refresh-data\">Refresh</button>\n  </div>\n  <div class=\"hidden text-sm text-error\" data-role=\"error-container\"></div>\n  <div class=\"grid grid-cols-1 md:grid-cols-3 gap-4\" data-role=\"metric-cards\"></div>\n  <div class=\"card bg-base-100 shadow-md\">\n    <div class=\"card-body\">\n      <h3 class=\"card-title\">Top 10 Products by Sales Volume</h3>\n      <div class=\"h-96\">\n        <canvas data-role=\"top-products-chart\"></canvas>\n      </div>\n    </div>\n  </div>\n  <div class=\"card bg-base-100 shadow-md\">\n    <div class=\"card-body\">\n      <h3 class=\"card-title\">Best Sellers Table</h3>\n      <div class=\"overflow-x-auto\">\n        <table class=\"table table-sm table-zebra\">\n          <thead>\n            <tr>\n              <th>#</th>\n              <th>Product Name</th>\n              <th>Barcode</th>\n              <th>Category</th>\n              <th class=\"text-right\">Units Sold</th>\n              <th class=\"text-right\">Revenue</th>\n              <th class=\"text-right\">Transactions</th>\n              <th class=\"text-right\">Avg Price</th>\n            </tr>\n          </thead>\n          <tbody data-role=\"products-table-body\"></tbody>\n        </table>\n      </div>\n    </div>\n  </div>\n</div>"}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}'::jsonb,
    NULL,
    NULL,
    'fa-trophy',
    'Top 10 Best Sellers',
    'Top 10 best-selling products by volume'
  )
ON CONFLICT (module_id) DO UPDATE SET
    config = EXCLUDED.config,
    icon = EXCLUDED.icon,
    aria_label = EXCLUDED.aria_label,
    description = EXCLUDED.description;

-- ============================================================================
-- Module 12: Slow Moving Items Report
-- Query: Products with no/low sales in last 90 days
-- Uses: Custom query (requires JOIN with inventory)
-- ============================================================================
INSERT INTO modules (module_id, name, config, org_id, branch_id, icon, aria_label, description) VALUES
  (
    '01948801-0012-7123-8123-000000000012'::uuid,
    'slow_moving_items',
    '{"title":"Slow Moving Items","body":[{"type":"html","html":"<div data-controller=\"SlowMovingItems\" class=\"space-y-6\">\n  <div class=\"flex flex-wrap items-center justify-between gap-3\">\n    <div class=\"flex flex-wrap gap-3\">\n      <label class=\"form-control w-40\">\n        <span class=\"label-text text-sm\">Days Threshold</span>\n        <input type=\"number\" class=\"input input-bordered input-sm\" data-filter=\"days-threshold\" value=\"90\" min=\"30\" max=\"365\" />\n      </label>\n      <label class=\"form-control w-40\">\n        <span class=\"label-text text-sm\">Branch</span>\n        <select class=\"select select-bordered select-sm\" data-filter=\"branch\">\n          <option value=\"\">All Branches</option>\n        </select>\n      </label>\n    </div>\n    <button class=\"btn btn-sm btn-primary\" data-role=\"refresh-data\">Refresh</button>\n  </div>\n  <div class=\"hidden text-sm text-error\" data-role=\"error-container\"></div>\n  <div class=\"alert alert-warning\">\n    <svg xmlns=\"http://www.w3.org/2000/svg\" class=\"stroke-current shrink-0 h-6 w-6\" fill=\"none\" viewBox=\"0 0 24 24\"><path stroke-linecap=\"round\" stroke-linejoin=\"round\" stroke-width=\"2\" d=\"M12 9v2m0 4h.01m-6.938 4h13.856c1.54 0 2.502-1.667 1.732-3L13.732 4c-.77-1.333-2.694-1.333-3.464 0L3.34 16c-.77 1.333.192 3 1.732 3z\" /></svg>\n    <span>Products with no sales in the last <strong data-role=\"threshold-display\">90</strong> days. Consider promotional activities or stock clearance.</span>\n  </div>\n  <div class=\"grid grid-cols-1 md:grid-cols-3 gap-4\" data-role=\"metric-cards\"></div>\n  <div class=\"card bg-base-100 shadow-md\">\n    <div class=\"card-body\">\n      <h3 class=\"card-title\">Slow Moving Products</h3>\n      <div class=\"overflow-x-auto\">\n        <table class=\"table table-sm table-zebra\">\n          <thead>\n            <tr>\n              <th>Product Name</th>\n              <th>Barcode</th>\n              <th>Category</th>\n              <th class=\"text-right\">Last Sale Date</th>\n              <th class=\"text-right\">Days Since Sale</th>\n              <th class=\"text-right\">Current Stock</th>\n              <th class=\"text-right\">Value at Risk</th>\n            </tr>\n          </thead>\n          <tbody data-role=\"slow-items-table-body\"></tbody>\n        </table>\n      </div>\n    </div>\n  </div>\n</div>"}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}'::jsonb,
    NULL,
    NULL,
    'fa-turtle',
    'Slow Moving Items',
    'Products with low or no recent sales activity'
  )
ON CONFLICT (module_id) DO UPDATE SET
    config = EXCLUDED.config,
    icon = EXCLUDED.icon,
    aria_label = EXCLUDED.aria_label,
    description = EXCLUDED.description;

-- ============================================================================
-- Module 13: VAT Report (0% and 12.5%)
-- Query: Basic VAT Summary grouped by VAT code
-- Uses: AggregateRowsController GROUP BY vat_code
-- ============================================================================
INSERT INTO modules (module_id, name, config, org_id, branch_id, icon, aria_label, description) VALUES
  (
    '01948801-0013-7123-8123-000000000013'::uuid,
    'vat_summary_report',
    '{"title":"VAT Summary Report","body":[{"type":"html","html":"<div data-controller=\"VatSummaryReport\" class=\"space-y-6\">\n  <div class=\"flex flex-wrap items-center justify-between gap-3\">\n    <div class=\"flex flex-wrap gap-3\">\n      <label class=\"form-control w-48\">\n        <span class=\"label-text text-sm\">Start Date</span>\n        <input type=\"date\" class=\"input input-bordered input-sm\" data-filter=\"start-date\" />\n      </label>\n      <label class=\"form-control w-48\">\n        <span class=\"label-text text-sm\">End Date</span>\n        <input type=\"date\" class=\"input input-bordered input-sm\" data-filter=\"end-date\" />\n      </label>\n      <label class=\"form-control w-40\">\n        <span class=\"label-text text-sm\">Branch</span>\n        <select class=\"select select-bordered select-sm\" data-filter=\"branch\">\n          <option value=\"\">All Branches</option>\n        </select>\n      </label>\n    </div>\n    <div class=\"flex gap-2\">\n      <button class=\"btn btn-sm btn-outline\" data-role=\"export-pdf\">Export PDF</button>\n      <button class=\"btn btn-sm btn-primary\" data-role=\"refresh-data\">Refresh</button>\n    </div>\n  </div>\n  <div class=\"hidden text-sm text-error\" data-role=\"error-container\"></div>\n  <div class=\"grid grid-cols-1 md:grid-cols-4 gap-4\" data-role=\"metric-cards\"></div>\n  <div class=\"card bg-base-100 shadow-md\">\n    <div class=\"card-body\">\n      <h3 class=\"card-title\">VAT Summary by Rate</h3>\n      <div class=\"overflow-x-auto\">\n        <table class=\"table table-sm\">\n          <thead>\n            <tr>\n              <th>VAT Code</th>\n              <th>VAT Rate</th>\n              <th class=\"text-right\">Transactions</th>\n              <th class=\"text-right\">Units Sold</th>\n              <th class=\"text-right\">Taxable Amount</th>\n              <th class=\"text-right\">VAT Amount</th>\n              <th class=\"text-right\">Total Revenue</th>\n            </tr>\n          </thead>\n          <tbody data-role=\"vat-table-body\"></tbody>\n          <tfoot>\n            <tr class=\"font-bold\">\n              <td colspan=\"2\">TOTAL</td>\n              <td class=\"text-right\" data-role=\"total-transactions\">0</td>\n              <td class=\"text-right\" data-role=\"total-units\">0</td>\n              <td class=\"text-right\" data-role=\"total-taxable\">$0.00</td>\n              <td class=\"text-right\" data-role=\"total-vat\">$0.00</td>\n              <td class=\"text-right\" data-role=\"total-revenue\">$0.00</td>\n            </tr>\n          </tfoot>\n        </table>\n      </div>\n    </div>\n  </div>\n  <div class=\"grid grid-cols-1 lg:grid-cols-2 gap-6\">\n    <div class=\"card bg-base-100 shadow-md\">\n      <div class=\"card-body\">\n        <h3 class=\"card-title\">VAT Distribution</h3>\n        <div class=\"h-64\">\n          <canvas data-role=\"vat-pie-chart\"></canvas>\n        </div>\n      </div>\n    </div>\n    <div class=\"card bg-base-100 shadow-md\">\n      <div class=\"card-body\">\n        <h3 class=\"card-title\">VAT Collection Breakdown</h3>\n        <div class=\"h-64\">\n          <canvas data-role=\"vat-bar-chart\"></canvas>\n        </div>\n      </div>\n    </div>\n  </div>\n  <div class=\"card bg-base-100 shadow-md\">\n    <div class=\"card-body\">\n      <h3 class=\"card-title\">VAT Summary Details</h3>\n      <div class=\"grid grid-cols-1 md:grid-cols-2 gap-4\">\n        <div>\n          <h4 class=\"font-semibold text-sm mb-2\">Zero-Rated (0%)</h4>\n          <ul class=\"text-sm space-y-1\">\n            <li>Transactions: <span class=\"font-mono\" data-role=\"zero-rated-count\">0</span></li>\n            <li>Taxable Amount: <span class=\"font-mono\" data-role=\"zero-rated-taxable\">$0.00</span></li>\n            <li>VAT Collected: <span class=\"font-mono\" data-role=\"zero-rated-vat\">$0.00</span></li>\n          </ul>\n        </div>\n        <div>\n          <h4 class=\"font-semibold text-sm mb-2\">Standard Rate (12.5%)</h4>\n          <ul class=\"text-sm space-y-1\">\n            <li>Transactions: <span class=\"font-mono\" data-role=\"standard-count\">0</span></li>\n            <li>Taxable Amount: <span class=\"font-mono\" data-role=\"standard-taxable\">$0.00</span></li>\n            <li>VAT Collected: <span class=\"font-mono\" data-role=\"standard-vat\">$0.00</span></li>\n          </ul>\n        </div>\n      </div>\n    </div>\n  </div>\n</div>"}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}'::jsonb,
    NULL,
    NULL,
    'fa-file-invoice-dollar',
    'VAT Summary Report',
    'Comprehensive VAT report showing 0% and 12.5% breakdown'
  )
ON CONFLICT (module_id) DO UPDATE SET
    config = EXCLUDED.config,
    icon = EXCLUDED.icon,
    aria_label = EXCLUDED.aria_label,
    description = EXCLUDED.description;

-- ============================================================================
-- Phase 8: End Additional Reports Registration
-- ============================================================================

COMMENT ON TABLE modules IS 'Module registry - Phase 8 now has 13 inventory analytics modules (10 comprehensive + 3 focused reports)';
