graph LR
    A[User] --> B[Income Page]
    A --> C[Deductions Page]
    A --> D[Personal Info]
    B --> E[member_income table]
    C --> F[member_deductions table]
    D --> G[members table]
    H[ITR Page] --> I[itr-prefill API]
    I --> E
    I --> F
    I --> G
    I --> J[ITR Form Data]
    J --> K[ITR Grid Dashboard]

// From itr-data-mapper.ts line 322
FatherName: member.father_name?.toUpperCase() || '', // ❌ Often empty!

{
  "personalInfo": {
    "AssesseeName": { "FirstName": "...", "SurNameOrOrgName": "..." },
    "Address": { "ResidenceNo": "...", "CityOrTownOrDistrict": "..." }
  },
  "incomeDetails": { "GrossSalary": 1200000 },
  "deductions": { "Section80C": 150000 }
}

// Flat structure with dot notation
formData = {
  "AssesseeName.FirstName": "JOHN",
  "AssesseeName.SurNameOrOrgName": "DOE",
  "Address.CityOrTownOrDistrict": "Mumbai",
  "GrossSalary": 1200000,
  "Section80C": 150000
}

graph TB
    subgraph "Data Entry Layer"
        A[Personal Info Page] --> D[members table]
        B[Income Page] --> E[member_income table]
        C[Deductions Page] --> F[member_deductions table]
    end
    
    subgraph "Processing Layer"
        D --> G[ITR Data Mapper Service]
        E --> G
        F --> G
        G --> H[Auto-calculate Fields]
        H --> I[Validate ITR Schema]
    end
    
    subgraph "Presentation Layer"
        I --> J[Real-time ITR Form]
        J --> K[ITR Grid Dashboard]
        K --> L[Review & Submit]
    end
    
    subgraph "Persistence Layer"
        J --> M[itr_data table]
        M --> N[Auto-save Progress]
    end
    
    style G fill:#4CAF50
    style H fill:#2196F3
    style I fill:#FF9800

// src/app/(member)/member/personal-info/page.tsx
export default function PersonalInfoPage() {
  // Form with sections:
  // 1. Basic Details (read-only if verified)
  // 2. Family Details
  // 3. Employment Details
  // 4. Address Details
  // 5. Contact Details
}

ALTER TABLE members ADD COLUMN IF NOT EXISTS father_name TEXT;
ALTER TABLE members ADD COLUMN IF NOT EXISTS marital_status TEXT;
ALTER TABLE members ADD COLUMN IF NOT EXISTS occupation TEXT;
ALTER TABLE members ADD COLUMN IF NOT EXISTS employer_category TEXT;
ALTER TABLE members ADD COLUMN IF NOT EXISTS employer_name TEXT;

// Add category field to income form
interface IncomeFormData {
  source_type: 'salary' | 'house_property' | 'business' | 'other_sources';
  category?: 'basic_pay' | 'hra' | 'allowances' | 'bonus'; // NEW
  amount: number;
  description?: string;
  employer_name?: string; // NEW for salary
  employer_tan?: string; // NEW for salary
}

/**
 * Real-time ITR Data Mapper Service
 * Automatically maps database tables to ITR form structure
 */
export class ITRAutoMapper {
  /**
   * Get complete ITR form data for a member
   * This is called whenever ITR page loads or data changes
   */
  static async getITRFormData(
    memberId: string,
    financialYear: string,
    itrType: string
  ): Promise<ITRFormData> {
    // 1. Fetch all data in parallel
    const [member, incomes, deductions] = await Promise.all([
      supabase.from('members').select('*').eq('id', memberId).single(),
      supabase.from('member_income').select('*')
        .eq('member_id', memberId)
        .eq('financial_year', financialYear),
      supabase.from('member_deductions').select('*')
        .eq('member_id', memberId)
        .eq('financial_year', financialYear)
    ]);

    // 2. Map to flat structure (NOT nested)
    const formData = this.mapToFlatStructure(member, incomes, deductions);

    // 3. Auto-calculate derived fields
    const enrichedData = this.calculateDerivedFields(formData);

    // 4. Validate against ITR schema
    const validatedData = this.validateITRSchema(enrichedData, itrType);

    return validatedData;
  }

  /**
   * Map nested database structure to flat form structure
   */
  private static mapToFlatStructure(member, incomes, deductions) {
    return {
      // Personal Info - Flattened
      'AssesseeName.FirstName': member.first_name?.toUpperCase(),
      'AssesseeName.MiddleName': member.middle_name?.toUpperCase(),
      'AssesseeName.SurNameOrOrgName': member.last_name?.toUpperCase(),
      'PAN': member.pan_number?.toUpperCase(),
      'DOB': member.date_of_birth,
      'AadhaarCardNo': member.aadhaar_number,
      
      // Address - Flattened
      'Address.ResidenceNo': member.address?.flat_no,
      'Address.ResidenceName': member.address?.residence_name,
      'Address.RoadOrStreet': member.address?.road_street,
      'Address.LocalityOrArea': member.address?.locality,
      'Address.CityOrTownOrDistrict': member.address?.city,
      'Address.StateCode': member.address?.state_code,
      'Address.PinCode': member.address?.pin_code,
      'Address.EmailAddress': member.email,
      
      // Income - Calculated
      'GrossSalary': this.sumIncome(incomes, 'salary'),
      'Salary': this.sumIncome(incomes, 'salary', 'basic_pay'),
      'PerquisitesValue': this.sumIncome(incomes, 'salary', 'perquisites'),
      'AllwncExemptUs10': this.sumIncome(incomes, 'salary', 'exempt_allowance'),
      
      // Deductions - Calculated
      'Section80C': this.sumDeduction(deductions, '80C'),
      'Section80D': this.sumDeduction(deductions, '80D'),
      // ... other sections
    };
  }

  /**
   * Auto-calculate derived fields
   */
  private static calculateDerivedFields(formData) {
    const grossSalary = formData['GrossSalary'] || 0;
    
    // Standard deduction (max 50,000)
    formData['StandardDeduction'] = Math.min(grossSalary, 50000);
    
    // Net salary
    formData['NetSalary'] = grossSalary - formData['StandardDeduction'];
    formData['IncomeFromSal'] = formData['NetSalary'];
    
    // Gross total income
    formData['GrossTotIncome'] = 
      (formData['IncomeFromSal'] || 0) +
      (formData['TotalIncomeOfHP'] || 0) +
      (formData['IncomeOthSrc'] || 0);
    
    // Total deductions
    const totalDeductions = Object.keys(formData)
      .filter(k => k.startsWith('Section'))
      .reduce((sum, k) => sum + (formData[k] || 0), 0);
    
    formData['TotalChapVIADeductions'] = totalDeductions;
    
    // Total income
    formData['TotalIncome'] = Math.max(0, 
      formData['GrossTotIncome'] - totalDeductions
    );
    
    return formData;
  }
}

/**
 * React hook for real-time ITR data synchronization
 * Automatically refreshes ITR form when underlying data changes
 */
export function useITRAutoSync(memberId: string, financialYear: string) {
  const [formData, setFormData] = useState({});
  const [isLoading, setIsLoading] = useState(true);

  // Subscribe to real-time changes
  useEffect(() => {
    const supabase = createClient();

    // Initial load
    loadITRData();

    // Subscribe to member_income changes
    const incomeSubscription = supabase
      .channel('income-changes')
      .on('postgres_changes', {
        event: '*',
        schema: 'public',
        table: 'member_income',
        filter: `member_id=eq.${memberId}`
      }, () => {
        console.log('Income changed, refreshing ITR data...');
        loadITRData();
      })
      .subscribe();

    // Subscribe to member_deductions changes
    const deductionSubscription = supabase
      .channel('deduction-changes')
      .on('postgres_changes', {
        event: '*',
        schema: 'public',
        table: 'member_deductions',
        filter: `member_id=eq.${memberId}`
      }, () => {
        console.log('Deduction changed, refreshing ITR data...');
        loadITRData();
      })
      .subscribe();

    return () => {
      incomeSubscription.unsubscribe();
      deductionSubscription.unsubscribe();
    };
  }, [memberId, financialYear]);

  async function loadITRData() {
    setIsLoading(true);
    const response = await fetch(
      `/api/members/${memberId}/itr-prefill?financial_year=${financialYear}`
    );
    const result = await response.json();
    if (result.success) {
      setFormData(result.data.formData);
    }
    setIsLoading(false);
  }

  return { formData, isLoading, refresh: loadITRData };
}

export default function MemberITRPage() {
  const [memberId, setMemberId] = useState<string>();
  const [selectedITRType, setSelectedITRType] = useState<string>('');
  const [selectedFinancialYear, setSelectedFinancialYear] = useState<string>('');

  // ✅ NEW: Use auto-sync hook instead of manual loading
  const { formData, isLoading, refresh } = useITRAutoSync(
    memberId || '',
    selectedFinancialYear
  );

  // ❌ REMOVE: Manual loadPrefillData function
  // ❌ REMOVE: useEffect with manual data loading

  return (
    <ITRGridDashboard
      memberId={memberId}
      initialData={formData} // ✅ Auto-synced data
      selectedITRType={selectedITRType}
      selectedFinancialYear={selectedFinancialYear}
      onDataChange={handleDataChange}
      onComplete={handleComplete}
    />
  );
}

-- Auto-update itr_data when source tables change
CREATE OR REPLACE FUNCTION auto_update_itr_data()
RETURNS TRIGGER AS $$
BEGIN
  -- Mark ITR data as stale when income/deductions change
  UPDATE itr_data
  SET 
    status = 'draft',
    updated_at = NOW()
  WHERE 
    member_id = COALESCE(NEW.member_id, OLD.member_id)
    AND financial_year = COALESCE(NEW.financial_year, OLD.financial_year);
  
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Trigger on member_income
CREATE TRIGGER trigger_income_update_itr
AFTER INSERT OR UPDATE OR DELETE ON member_income
FOR EACH ROW
EXECUTE FUNCTION auto_update_itr_data();

-- Trigger on member_deductions
CREATE TRIGGER trigger_deduction_update_itr
AFTER INSERT OR UPDATE OR DELETE ON member_deductions
FOR EACH ROW
EXECUTE FUNCTION auto_update_itr_data();