-- Create the database
CREATE DATABASE IF NOT EXISTS lawyer_profiles;
USE lawyer_profiles;

-- Create notable_cases table
CREATE TABLE IF NOT EXISTS notable_cases (
    id INT AUTO_INCREMENT PRIMARY KEY,
    profile_id INT NOT NULL,
    case_title VARCHAR(255) NOT NULL,
    case_description TEXT,
    case_year INT,
    case_outcome TEXT,
    case_stats TEXT,
    icon_class VARCHAR(50) DEFAULT 'fas fa-gavel',
    display_order INT DEFAULT 0,
    FOREIGN KEY (profile_id) REFERENCES profiles(id) ON DELETE CASCADE,
    INDEX idx_profile (profile_id)
);

-- Create gallery_images table
CREATE TABLE IF NOT EXISTS gallery_images (
    id INT AUTO_INCREMENT PRIMARY KEY,
    profile_id INT NOT NULL,
    image_url VARCHAR(500) NOT NULL,
    title VARCHAR(255),
    description TEXT,
    display_order INT DEFAULT 0,
    FOREIGN KEY (profile_id) REFERENCES profiles(id) ON DELETE CASCADE,
    INDEX idx_profile (profile_id)
);

-- Create specialties table
CREATE TABLE IF NOT EXISTS specialties (
    id INT AUTO_INCREMENT PRIMARY KEY,
    profile_id INT NOT NULL,
    specialty_name VARCHAR(100) NOT NULL,
    FOREIGN KEY (profile_id) REFERENCES profiles(id) ON DELETE CASCADE,
    INDEX idx_profile (profile_id)
);

-- Add specialties for Ashley
INSERT INTO specialties (profile_id, specialty_name) VALUES
(1, 'Corporate Law & Business Formation'),
(1, 'Contract Drafting & Review'),
(1, 'Civil Litigation'),
(1, 'Estate Planning & Probate'),
(1, 'Real Estate Transactions'),
(1, 'Mediation & Dispute Resolution'),
(1, 'Wealth Preservation'),
(1, 'Regulatory Compliance');

-- Add notable cases
INSERT INTO notable_cases (profile_id, case_title, case_description, case_year, case_outcome, case_stats, icon_class, display_order) VALUES
(1, 'Corporate Merger & Acquisition', 'Represented a regional manufacturing company in a $45M merger with a national competitor. Negotiated favorable terms, conducted due diligence, and structured the transaction to minimize tax exposure while protecting client interests.', 2024, 'Successfully closed merger with favorable terms, preserved 150 jobs, and structured post-merger integration.', '$45M transaction value|22% tax savings|8 months completion', 'fas fa-building', 1),
(1, 'Complex Commercial Litigation', 'Represented a technology firm in a breach of contract dispute involving intellectual property rights. Successfully argued before the Georgia Superior Court, securing a $8.5M settlement for the client.', 2023, 'Secured $8.5M settlement and established favorable precedent for intellectual property protection in commercial contracts.', '$8.5M settlement recovered|IP rights fully protected|Favorable precedent set', 'fas fa-balance-scale', 2),
(1, 'Complex Estate Planning & Trust Litigation', 'Represented beneficiaries in a contested trust administration involving multiple generations. Successfully mediated dispute, preserving family harmony while ensuring equitable distribution of assets.', 2024, 'Mediated successful resolution, preserved $18M in family wealth, and avoided costly litigation for all parties.', '$18M estate preserved|100% beneficiary satisfaction|4 months resolution', 'fas fa-hand-holding-heart', 3),
(1, 'Real Estate Development Dispute', 'Represented a real estate developer in a zoning and land use dispute that threatened a $25M mixed-use project. Successfully negotiated with municipal authorities and obtained necessary approvals.', 2023, 'Obtained all necessary zoning approvals, project commenced on schedule, creating 350+ residential units and commercial space.', '$25M project protected|Project fully approved|350+ housing units', 'fas fa-home', 4);

-- Add gallery images
INSERT INTO gallery_images (profile_id, image_url, title, description, display_order) VALUES
(1, 'https://picsum.photos/id/20/600/450', 'Ashley''s Office', 'Abbott Legal Group, Atlanta', 1),
(1, 'https://picsum.photos/id/26/600/450', 'Client Consultation', 'Discussing legal strategies', 2),
(1, 'https://picsum.photos/id/91/600/450', 'Award Recognition', 'Legal Excellence Award 2024', 3),
(1, 'https://picsum.photos/id/169/600/450', 'Legal Conference', 'Speaking on corporate law trends', 4),
(1, 'https://picsum.photos/id/155/600/450', 'Team Collaboration', 'Strategic case planning session', 5),
(1, 'https://picsum.photos/id/104/600/450', 'Community Outreach', 'Legal literacy workshop', 6),
(1, 'https://picsum.photos/id/100/600/450', 'Networking', 'Connecting with legal peers', 7),
(1, 'https://picsum.photos/id/29/600/450', 'Professional Development', 'Advanced legal seminar', 8);