-- ==========================================================
-- One Point Alliance - Database Schema for Blogs & Articles
-- Table Name: blogs
-- Database: onepoint_db
-- ==========================================================

CREATE TABLE IF NOT EXISTS `blogs` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `title` VARCHAR(255) NOT NULL,
  `slug` VARCHAR(255) NOT NULL UNIQUE,
  `category` VARCHAR(100) NOT NULL DEFAULT 'Insights',
  `author` VARCHAR(100) NOT NULL DEFAULT 'One Point Alliance',
  `author_role` VARCHAR(150) DEFAULT 'Advisory Specialist',
  `image_url` VARCHAR(500) DEFAULT NULL,
  `excerpt` TEXT NOT NULL,
  `content` LONGTEXT NOT NULL,
  `read_time` VARCHAR(50) DEFAULT '5 min read',
  `tags` VARCHAR(255) DEFAULT '',
  `status` ENUM('published', 'draft') DEFAULT 'published',
  `views` INT DEFAULT 0,
  `published_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==========================================================
-- Seed Initial Strategic Blog Articles (Optional / Sample Data)
-- ==========================================================

INSERT INTO `blogs` 
(`id`, `title`, `slug`, `category`, `author`, `author_role`, `image_url`, `excerpt`, `content`, `read_time`, `tags`, `status`, `published_at`) 
VALUES
(
  1,
  'Mastering Melbourne & Victorian Property Acquisitions in 2026',
  'mastering-melbourne-victorian-property-acquisitions-2026',
  'Property & Real Estate',
  'Marcus Vance',
  'Principal Property Strategist',
  'https://images.unsplash.com/photo-1512917774080-9991f1c4c750?auto=format&fit=crop&w=1200&q=80',
  'An insider analysis of Victorian property corridors, infrastructure growth hubs, and strategic contract due diligence for high-net-worth investors.',
  '### Navigating Victoria\'s Evolving Real Estate Landscape\n\nThe Victorian property market continues to showcase distinctive growth corridors powered by sustained infrastructure investments, transport hubs, and high-amenity precincts. For sophisticated property buyers and family offices, tactical timing and rigorous asset selection remain the key differentiators.\n\n#### 1. Pinpointing Undervalued Growth Pockets\nWhile blue-chip inner-city enclaves maintain defensive wealth attributes, high-yield expansion corridors across Western Melbourne (such as Werribee, Point Cook, and Geelong fringes) present unprecedented infrastructure-backed upside. Key considerations include:\n- Proximity to core transport and education arterial networks\n- Local council capital expenditure pipelines\n- Historical vacancy rate sustainability and tenant demographic profiles\n\n#### 2. Pre-Auction & Off-Market Intelligence\nOver 40% of premier commercial and residential transactions now take place through off-market syndicates and trusted networks. Bypassing public auction premiums allows purchasers to negotiate favorable settlement terms, flexible deposit arrangements, and vendor finance structures.\n\n#### 3. Section 32 & Building Due Diligence\nNever commit before reviewing restrictive covenants, easements, bushfire/flood zone overlays, and planning scheme amendments. Our integrated legal and mortgage alliance coordinates rapid-turnaround contract assessments prior to signing.',
  '6 min read',
  'Property, Victoria, Real Estate, Investment',
  'published',
  '2026-09-15 10:00:00'
),
(
  2,
  'Unlocking Capital: Navigating 90+ Commercial & Private Lending Panels',
  'unlocking-capital-navigating-90-plus-lending-panels',
  'Finance & Lending',
  'Sarah Sterling',
  'Head of Credit & Syndicated Finance',
  'https://images.unsplash.com/photo-1554224155-6726b3ff858f?auto=format&fit=crop&w=1200&q=80',
  'Why single-bank dependency restricts borrowing capacity, and how structured non-bank liquidity delivers superior capital efficiency.',
  '### Transcending Single-Lender Credit Limits\n\nIn an era of macroprudential policy fluctuations and stringent serviceability benchmarks, Australian businesses and property developers cannot afford single-institution reliance. Accessing an alliance network spanning 90+ tier-1 banks, private equity syndicates, and boutique credit funds transforms financing outcomes.\n\n#### The Tri-Tier Lending Spectrum\n1. **Tier-1 Big Four Institutions**: Prime mortgages and facilities with rigid credit scorecard formulas.\n2. **Second-Tier & Regional Banks**: More flexible servicing models, tailored debt-to-income tolerance, and superior offset flexibility.\n3. **Private Debt & Specialized Non-Bank Financiers**: Fast settlement turnarounds, asset-backed equity releases, and non-conforming business liquidity free from conventional PAYG constraints.\n\n#### Strategic Loan Structuring\nA well-engineered capital stack coordinates cross-collateralization elimination, tax-deductible interest maximization, and buffer liquidity reserves to insulate your enterprise against economic shifts.',
  '7 min read',
  'Mortgages, Private Debt, Banking, Liquidity',
  'published',
  '2026-09-28 14:30:00'
),
(
  3,
  'Corporate Trust Structuring & Family Wealth Tax Optimization',
  'corporate-trust-structuring-family-wealth-tax-optimization',
  'Tax & Accounting',
  'David Thornton',
  'Senior Chartered Tax Advisor',
  'https://images.unsplash.com/photo-1454165804606-c3d57bc86b40?auto=format&fit=crop&w=1200&q=80',
  'A comprehensive blueprint for discretionary trusts, corporate beneficiaries, and division 7A compliance to preserve intergenerational wealth.',
  '### Structuring for Resilience & Generational Continuity\n\nAs wealth compounds, separating operational risk from core asset ownership becomes vital. Discretionary trusts governed by corporate trustees remain Australia\'s gold standard for family asset protection and strategic income streaming.\n\n#### Core Structural Advantages\n- **Asset Shielding**: Separating personal business liabilities from residential and commercial investment assets.\n- **Income Streaming Discretion**: Distributing taxable dividends across eligible family beneficiaries and investment bucket companies to benchmark overall effective tax rates.\n- **Intergenerational Legacy**: Managing future asset transitions smoothly without triggering untimely capital gains tax events or stamp duty penalties.\n\n#### Navigating Section 100A & Division 7A Compliance\nRecent ATO interpretations require precision in family trust distribution agreements and corporate beneficiary cash flows. Ensuring compliant loan agreements and formal benchmark interest servicing protects your entity from audit liabilities.',
  '8 min read',
  'Tax Planning, Trusts, Wealth, Compliance',
  'published',
  '2026-10-02 09:15:00'
),
(
  4,
  'SMSF Property Borrowing: Rules, Benefits & Common Pitfalls',
  'smsf-property-borrowing-rules-benefits-pitfalls',
  'Wealth & Advisory',
  'Elena Rostova',
  'Superannuation & Wealth Specialist',
  'https://images.unsplash.com/photo-1460925895917-afdab827c52f?auto=format&fit=crop&w=1200&q=80',
  'How business owners and professionals can purchase commercial premises through self-managed super funds with concessional tax treatments.',
  '### Leveraging Your Superannuation for Tangible Real Estate\n\nPurchasing property through a Self-Managed Super Fund (SMSF) utilizing a Limited Recourse Borrowing Arrangement (LRBA) represents one of Australia\'s most compelling wealth multiplication mechanisms.\n\n#### Business Real Property vs. Residential Assets\n- **Business Premises**: Business owners can acquire their operational commercial premises inside their SMSF and lease it back to their operating business at commercial market rates.\n- **Concessional Tax Atmosphere**: Rental income within the accumulation phase is taxed at a modest 15%, dropping to 0% once transitioning into the retirement pension phase.\n- **Capital Gains Exemption**: Properties sold during pension phase incur zero capital gains tax under current legislation.\n\n#### Critical Compliance Directives\n1. Strict adherence to the Sole Purpose Test.\n2. Distinct Bare Trust creation for each separate property title.\n3. No personal use of residential assets by fund members or associated relatives.',
  '5 min read',
  'SMSF, Commercial Property, Superannuation, Wealth',
  'published',
  '2026-10-08 11:20:00'
)
ON DUPLICATE KEY UPDATE `title` = VALUES(`title`);