import sqlite3
import csv
import re
from dateutil import parser

def normalize(text):
    """Normalize column headers and strings for consistent matching."""
    text = text.replace('\ufeff', '').replace('', '')
    return re.sub(r'\s+', ' ', text.strip().lower().lstrip('\ufeff'))

def process_allocai_csv(input_csv, output_csv, model="FT"):
    """Process an attribution CSV file and output the enhanced CSV."""

    # Use in-memory database for temporary calculations
    conn = sqlite3.connect(':memory:')
    cur = conn.cursor()

    # Create Alloc_Calcs table
    cur.executescript('''
    CREATE TABLE Alloc_Calcs (
        opportunity_name  TEXT,
        campaign_influence_date   TEXT,
        opportunity_created_date    TEXT,
        campaign_name  TEXT,
        is_primary_campaign  TEXT,
        opportunity_amount FLOAT,
        opportunity_amount_check FLOAT,
        campaign_count  INTEGER,
        
        eligible_campaign_flag FLOAT,    
        influence_campaign_flag FLOAT,
        real_primary_campaign_flag FLOAT,
        first_touch_flag FLOAT,
        primary_campaign_percent FLOAT,
        first_touch_percent FLOAT,
        influence_campaign_pct FLOAT,
        total_influence_pct FLOAT,
        total_opportunity_influence_pct FLOAT,
        
        addn1_influence_campaign_pct FLOAT,
        total2_influence_pct FLOAT,
        total2_opportunity_influence_pct FLOAT,
        total_allocation_amt FLOAT
    )
    ''')

    fixed_fields = set([
        "opportunity_name", "campaign_influence_date", "opportunity_created_date",
        "campaign_name", "is_primary_campaign", "opportunity_amount"
    ])

    # Read CSV
    with open(input_csv, encoding='utf-8') as handle:
        reader = csv.reader(handle)
        headers = next(reader)
        original_headers = headers

       # Normalize headers
        normalized_headers = {normalize(header): idx for idx, header in enumerate(headers)}

        # Add any extra columns not already in Alloc_Calcs
        for header in original_headers:
            col = normalize(header).replace(" ", "_")
            if col not in fixed_fields:
                try:
                    cur.execute(f'ALTER TABLE Alloc_Calcs ADD COLUMN "{col}" TEXT')
                except sqlite3.OperationalError:
                    pass  # Column might already exist

        expected_columns = {
            "opportunity name": ["opportunity name", "opp name", "opportunityname", "opp_name"],
            "campaign influence date": ["campaign influence date", "influence date", "campaign date"],
            "opportunity created date": ["opportunity created date", "created date", "opp created"],
            "campaign name": ["campaign name", "campaign", "campaign title"],
            "is primary campaign": ["is primary campaign", "primary", "primary campaign"],
            "opportunity amount": ["opportunity amount", "amount", "opp amount"]
        }

        normalized_headers = {normalize(header): idx for idx, header in enumerate(headers)}
        header_index = {}
        for standard_name, aliases in expected_columns.items():
            for alias in aliases:
                alias_normalized = normalize(alias)
                if alias_normalized in normalized_headers:
                    header_index[standard_name] = normalized_headers[alias_normalized]
                    break
            else:
                raise ValueError(f"Missing expected column: {standard_name}")

        date_format_out = "%Y-%m-%d"  # Ensures proper date sorting in SQLite

        for pieces in reader:
            opportunity_name = pieces[header_index["opportunity name"]]
            # --- FIXED: safe parsing for dates
            try:
                campaign_influence_date = parser.parse(pieces[header_index["campaign influence date"]])
                campaign_influence_date_str = campaign_influence_date.strftime(date_format_out)
            except Exception:
                campaign_influence_date_str = None

            try:
                opportunity_created_date = parser.parse(pieces[header_index["opportunity created date"]])
                opportunity_created_date_str = opportunity_created_date.strftime(date_format_out)
            except Exception:
                opportunity_created_date_str = None

            campaign_name = pieces[header_index["campaign name"]]
            is_primary_campaign = pieces[header_index["is primary campaign"]]

        #    # --- FIXED: regex-based cleaning for opportunity amount
        #    amt_raw = pieces[header_index["opportunity amount"]]
        #    amt_clean = re.sub(r'[^0-9.]', '', amt_raw)
        #    opportunity_amount = float(amt_clean) if amt_clean else 0.0

            #---START patch on Sep 2 to make float work ----#
            raw_amount = pieces[header_index["opportunity amount"]]
            clean_amount = raw_amount.replace("$", "").replace(",", "").strip()
            opportunity_amount = float(clean_amount) if clean_amount else 0.0
            #----END patch on Sep 2 to make float work ----# 

            # Determine eligible_campaign_flag
            if campaign_influence_date_str and opportunity_created_date_str:
                eligible_campaign_flag = 1.0 if campaign_influence_date <= opportunity_created_date else 0.0
            else:
                eligible_campaign_flag = 0.0

        # ---- START: Addition on Sept 2 to make sure date fields are ISO ---

            # --- Validate and convert campaign_influence_date ---
            raw_infl_date = pieces[header_index["campaign influence date"]]
            try:
                parsed_infl_date = parser.parse(raw_infl_date)
                campaign_influence_date_str = parsed_infl_date.strftime("%Y-%m-%d")
            except Exception as e:
                print(f"[ERROR] Invalid campaign_influence_date: {raw_infl_date} → {e}")
                campaign_influence_date_str = None

            # --- Validate and convert opportunity_created_date ---
            raw_created_date = pieces[header_index["opportunity created date"]]
            try:
                parsed_created_date = parser.parse(raw_created_date)
                opportunity_created_date_str = parsed_created_date.strftime("%Y-%m-%d")
            except Exception as e:
                print(f"[ERROR] Invalid opportunity_created_date: {raw_created_date} → {e}")
                opportunity_created_date_str = None

        # ---- END: Addition on Sept 2 to make sure date fields are ISO ---

            cur.execute('''INSERT OR REPLACE INTO Alloc_Calcs
                (opportunity_name, campaign_influence_date, opportunity_created_date, campaign_name,
                is_primary_campaign, opportunity_amount, eligible_campaign_flag) 
                VALUES (?, ?, ?, ?, ?, ?, ?)''',
                (opportunity_name, campaign_influence_date_str, opportunity_created_date_str,
                campaign_name, is_primary_campaign, opportunity_amount, eligible_campaign_flag))

            #-- Sep2 fix ---Add extra values to Alloc_Calcs (excluding fixed numeric fields)
            extra_values = {}
            for h, idx in normalized_headers.items():
                col = normalize(h).replace(" ", "_")
                if col not in fixed_fields:   # ✅ compare underscore form
                    extra_values[col] = pieces[idx]

            # sep 2 fix ends




        #    # --- FIXED: exclude fixed_fields from overwriting (esp. opportunity_amount)
        #    extra_values = {
        #        normalize(h).replace(" ", "_"): pieces[idx]
        #        for h, idx in normalized_headers.items()
        #        if normalize(h).replace(" ", "_") not in fixed_fields
        #    }

            for col, val in extra_values.items():
                cur.execute(f'''
                    UPDATE Alloc_Calcs SET "{col}" = ?
                    WHERE opportunity_name = ? AND campaign_name = ?
                ''', (val, opportunity_name, campaign_name))

            conn.commit()

        # Get all unique opportunity names
        cur.execute('SELECT DISTINCT opportunity_name FROM Alloc_Calcs')
        opportunity_names = cur.fetchall()

        for (opportunity_name,) in opportunity_names:
            cur.execute('SELECT COUNT(*) FROM Alloc_Calcs WHERE opportunity_name = ?', (opportunity_name,))
            (count,) = cur.fetchone()
            cur.execute('UPDATE Alloc_Calcs SET campaign_count = ? WHERE opportunity_name = ?', (count, opportunity_name))

            # --- FIXED: use single quotes for TRUE
            cur.execute('''
                UPDATE Alloc_Calcs
                SET real_primary_campaign_flag = 1.0
                WHERE opportunity_name = ? AND UPPER(is_primary_campaign) = 'TRUE'
            ''', (opportunity_name,))

            cur.execute('''
                SELECT COUNT(*) FROM Alloc_Calcs
                WHERE opportunity_name = ? AND real_primary_campaign_flag = 1.0
            ''', (opportunity_name,))
            (primary_count,) = cur.fetchone()

            if primary_count == 0:
                cur.execute('''
                    SELECT campaign_influence_date FROM Alloc_Calcs
                    WHERE opportunity_name = ? AND eligible_campaign_flag = 1.0
                    ORDER BY campaign_influence_date DESC LIMIT 1
                ''', (opportunity_name,))
                latest_date_row = cur.fetchone()

                if latest_date_row:
                    latest_date = latest_date_row[0]
                    cur.execute('''
                        UPDATE Alloc_Calcs
                        SET real_primary_campaign_flag = 1.0
                        WHERE opportunity_name = ? AND campaign_influence_date = ? AND eligible_campaign_flag = 1.0
                    ''', (opportunity_name, latest_date))

            cur.execute('''
                SELECT COUNT(*) FROM Alloc_Calcs
                WHERE opportunity_name = ? AND real_primary_campaign_flag = 1.0
            ''', (opportunity_name,))
            (flag_count,) = cur.fetchone()

            if flag_count > 1:
                equal_flag = 1.0 / flag_count
                cur.execute('''
                    UPDATE Alloc_Calcs
                    SET real_primary_campaign_flag = ?
                    WHERE opportunity_name = ? AND real_primary_campaign_flag = 1.0
                ''', (equal_flag, opportunity_name))

            cur.execute('''
                SELECT MIN(campaign_influence_date) FROM Alloc_Calcs
                WHERE opportunity_name = ? AND eligible_campaign_flag = 1.0
            ''', (opportunity_name,))
            earliest_date_row = cur.fetchone()

            if earliest_date_row and earliest_date_row[0]:
                earliest_date = earliest_date_row[0]
                cur.execute('''
                    UPDATE Alloc_Calcs
                    SET first_touch_flag = 1.0
                    WHERE opportunity_name = ? AND campaign_influence_date = ? AND eligible_campaign_flag = 1.0
                ''', (opportunity_name, earliest_date))

                cur.execute('''
                    SELECT COUNT(*) FROM Alloc_Calcs
                    WHERE opportunity_name = ? AND first_touch_flag = 1.0
                ''', (opportunity_name,))
                (ft_count,) = cur.fetchone()

                if ft_count > 1:
                    equal_ft = 1.0 / ft_count
                    cur.execute('''
                        UPDATE Alloc_Calcs
                        SET first_touch_flag = ?
                        WHERE opportunity_name = ? AND first_touch_flag = 1.0
                    ''', (equal_ft, opportunity_name))

            cur.execute('''
                UPDATE Alloc_Calcs
                SET influence_campaign_flag = 1.0
                WHERE opportunity_name = ? AND
                    eligible_campaign_flag = 1.0 AND
                    real_primary_campaign_flag IS NULL AND
                    first_touch_flag IS NULL
            ''', (opportunity_name,))

            cur.execute('''
                SELECT COUNT(*) FROM Alloc_Calcs
                WHERE opportunity_name = ? AND influence_campaign_flag = 1.0
            ''', (opportunity_name,))
            (inf_count,) = cur.fetchone()

            if inf_count > 1:
                equal_inf = 1.0 / inf_count
                cur.execute('''
                    UPDATE Alloc_Calcs
                    SET influence_campaign_flag = ?
                    WHERE opportunity_name = ? AND influence_campaign_flag = 1.0
                ''', (equal_inf, opportunity_name))

           

            if model == "FT":
                cur.execute('''
                    UPDATE Alloc_Calcs
                    SET primary_campaign_percent = 0.0,
                        first_touch_percent = COALESCE(first_touch_flag, 0) * 1.0,
                        influence_campaign_pct = 0.0
                    ''')
            elif model == "LT":
                cur.execute('''
                    UPDATE Alloc_Calcs
                    SET primary_campaign_percent = COALESCE(real_primary_campaign_flag, 0) * 1.0,
                        first_touch_percent = 0.0,
                        influence_campaign_pct = 0.0
                    ''') 
            elif model == "MT":
                cur.execute('''
                    UPDATE Alloc_Calcs
                    SET primary_campaign_percent = COALESCE(real_primary_campaign_flag, 0) * 0.4,
                        first_touch_percent = COALESCE(first_touch_flag, 0) * 0.4,
                        influence_campaign_pct = COALESCE(influence_campaign_flag, 0) * 0.2
                ''')

            cur.execute('''
            UPDATE Alloc_Calcs
            SET total_influence_pct = 
                COALESCE(primary_campaign_percent, 0) +
                COALESCE(first_touch_percent, 0) +
                COALESCE(influence_campaign_pct, 0)
            ''')

            for (opportunity_name,) in opportunity_names:
                cur.execute('''
                    SELECT SUM(total_influence_pct) FROM Alloc_Calcs
                    WHERE opportunity_name = ?
                ''', (opportunity_name,))
                (total_pct_sum,) = cur.fetchone()

                cur.execute('''
                    UPDATE Alloc_Calcs
                    SET total_opportunity_influence_pct = ?
                    WHERE opportunity_name = ?
                ''', (total_pct_sum, opportunity_name))

            cur.execute('''
            UPDATE Alloc_Calcs
            SET addn1_influence_campaign_pct = 
                CASE
                    WHEN total_opportunity_influence_pct = 0.8 AND total_influence_pct = 0.8 THEN 0.2
                    WHEN total_opportunity_influence_pct = 0.8 AND total_influence_pct = 0.4 THEN 0.1
                    WHEN total_opportunity_influence_pct = 0.4 AND total_influence_pct = 0.4 THEN 0.6
                    ELSE 0.0
                END
            ''')
            
            cur.execute('''
            UPDATE Alloc_Calcs
            SET total2_influence_pct = 
                COALESCE(total_influence_pct, 0) + 
                COALESCE(addn1_influence_campaign_pct, 0)
            ''')

            for (opportunity_name,) in opportunity_names:
                cur.execute('''
                    SELECT SUM(total2_influence_pct) FROM Alloc_Calcs
                    WHERE opportunity_name = ?
                ''', (opportunity_name,))
                (total2_pct_sum,) = cur.fetchone()

                cur.execute('''
                    UPDATE Alloc_Calcs
                    SET total2_opportunity_influence_pct = ?
                    WHERE opportunity_name = ?
                    ''', (total2_pct_sum, opportunity_name))

            cur.execute('''
            UPDATE Alloc_Calcs
            SET total_allocation_amt = COALESCE(total2_influence_pct, 0) * COALESCE(opportunity_amount, 0)
            ''')

            for (opportunity_name,) in opportunity_names:
                cur.execute('''
                    SELECT SUM(total_allocation_amt) FROM Alloc_Calcs
                    WHERE opportunity_name = ?
                ''', (opportunity_name,))
                (alloc_sum,) = cur.fetchone()

                cur.execute('''
                    UPDATE Alloc_Calcs
                    SET opportunity_amount_check = ?
                    WHERE opportunity_name = ?
                ''', (alloc_sum, opportunity_name))

    # Export Alloc_Calcs to CSV with date reformatting
    cur.execute('PRAGMA table_info(Alloc_Calcs)')
    columns = [row[1] for row in cur.fetchall()]

        # Export Alloc_Calcs to CSV with specific column order and date reformatting
    final_columns = [
        "opportunity_name",
        "opportunity_stage",
        "opportunity_amount",
        "opportunity_created_date",
        "opportunity_close_date",
        "opportunity_owner",
        "lead_source",
        "account_name",
        "opportunity_type",
        "campaign_name",
        "campaign_type",
        "campaign_influence_date",
        "is_primary_campaign",
        "revenue_share",
        "total_allocation_amt"
    ]

    export_columns = [c for c in final_columns if c in columns]

    with open(output_csv, 'w', newline='', encoding='utf-8') as f:
        writer = csv.writer(f)
        writer.writerow(export_columns)

        cur.execute(f"SELECT {', '.join(export_columns)} FROM Alloc_Calcs")
        for row in cur.fetchall():
            row = list(row)
            for col_idx, col_name in enumerate(export_columns):
                if col_name in ("opportunity_created_date", "opportunity_close_date", "campaign_influence_date") and row[col_idx]:
                    try:
                        row[col_idx] = parser.parse(row[col_idx]).strftime("%d-%b-%Y")
                    except Exception:
                        pass
            writer.writerow(row)


    conn.close()