from django.db import models
from django.db import connections
from django.utils import timezone
import json
import hashlib
import uuid


class WordPressUserManager:
    """Manager for WordPress user operations"""
    
    def __init__(self):
        self.wp_db = connections['wordpress']
    
    def get_or_create_wp_user(self, notice_user):
        """Get or create WordPress user from NoticeUser"""
        with self.wp_db.cursor() as cursor:
            # Check if user exists by email
            cursor.execute(
                "SELECT ID, user_login, user_email FROM wp_users WHERE user_email = %s",
                [notice_user.email]
            )
            wp_user = cursor.fetchone()
            
            if wp_user:
                # Update user info if needed
                self.update_wp_user_info(wp_user[0], notice_user)
                return wp_user[0]  # Return WordPress user ID
            
            # Create new WordPress user
            wp_user_id = self.create_wp_user(notice_user)
            return wp_user_id
    
    def update_wp_user_info(self, wp_user_id, notice_user):
        """Update WordPress user info from NoticeUser"""
        with self.wp_db.cursor() as cursor:
            # Update display name if different
            cursor.execute(
                "UPDATE wp_users SET display_name = %s WHERE ID = %s",
                [notice_user.display_name, wp_user_id]
            )
            
            # Update user meta for additional info
            cursor.execute("""
                INSERT INTO wp_usermeta (user_id, meta_key, meta_value) 
                VALUES (%s, %s, %s)
                ON DUPLICATE KEY UPDATE meta_value = VALUES(meta_value)
            """, [wp_user_id, 'noticeapp_user_id', notice_user.id])
            
            cursor.execute("""
                INSERT INTO wp_usermeta (user_id, meta_key, meta_value) 
                VALUES (%s, %s, %s)
                ON DUPLICATE KEY UPDATE meta_value = VALUES(meta_value)
            """, [wp_user_id, 'noticeapp_photo_url', notice_user.photo_url or ''])
    
    def create_wp_user(self, notice_user):
        """Create a new WordPress user"""
        with self.wp_db.cursor() as cursor:
            # Generate WordPress username from email
            username = notice_user.email.split('@')[0]
            # Ensure username is unique
            base_username = username
            counter = 1
            while True:
                cursor.execute("SELECT ID FROM wp_users WHERE user_login = %s", [username])
                if not cursor.fetchone():
                    break
                username = f"{base_username}{counter}"
                counter += 1
            
            # Create user_login from display_name if available
            user_login = username
            user_nicename = notice_user.display_name.lower().replace(' ', '-')
            
            # Insert into wp_users
            cursor.execute("""
                INSERT INTO wp_users 
                (user_login, user_pass, user_nicename, user_email, user_url, user_registered, user_activation_key, user_status, display_name)
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)
            """, [
                user_login,
                notice_user.password_hash,  # Use existing password hash
                user_nicename,
                notice_user.email,
                '',  # user_url
                timezone.now().strftime('%Y-%m-%d %H:%M:%S'),
                '',  # user_activation_key
                0,   # user_status
                notice_user.display_name
            ])
            
            wp_user_id = cursor.lastrowid
            
            # Set default user capabilities
            cursor.execute("""
                INSERT INTO wp_usermeta (user_id, meta_key, meta_value)
                VALUES (%s, %s, %s)
            """, [wp_user_id, 'wp_capabilities', 'a:1:{s:10:"subscriber";b:1;}'])
            
            cursor.execute("""
                INSERT INTO wp_usermeta (user_id, meta_key, meta_value)
                VALUES (%s, %s, %s)
            """, [wp_user_id, 'wp_user_level', '0'])
            
            return wp_user_id


class WordPressListingManager:
    """Manager for WordPress listing operations"""
    
    def __init__(self):
        self.wp_db = connections['wordpress']
    
    def create_listing(self, listing_data, wp_user_id):
        """Create a new listing in WordPress database"""
        with self.wp_db.cursor() as cursor:
            # Insert into wp_posts
            post_id = self.create_wp_post(listing_data, wp_user_id)
            
            # Insert custom fields into wp_postmeta
            self.create_post_meta(post_id, listing_data)
            
            # Set categories and tags
            self.set_taxonomies(post_id, listing_data)
            
            return post_id
    
    def create_wp_post(self, listing_data, wp_user_id):
        """Create WordPress post"""
        with self.wp_db.cursor() as cursor:
            now = timezone.now().strftime('%Y-%m-%d %H:%M:%S')
            
            cursor.execute("""
                INSERT INTO wp_posts 
                (post_author, post_date, post_date_gmt, post_content, post_title, post_excerpt, post_status, comment_status, ping_status, post_password, post_name, to_ping, pinged, post_modified, post_modified_gmt, post_content_filtered, post_parent, guid, menu_order, post_type, post_mime_type, comment_count)
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
            """, [
                wp_user_id,  # post_author
                now,         # post_date
                now,         # post_date_gmt
                listing_data.get('description', ''),  # post_content
                listing_data.get('title', ''),        # post_title
                listing_data.get('tagline', ''),      # post_excerpt
                'publish',   # post_status
                'open',      # comment_status
                'open',      # ping_status
                '',          # post_password
                self.generate_post_slug(listing_data.get('title', '')),  # post_name
                '',          # to_ping
                '',          # pinged
                now,         # post_modified
                now,         # post_modified_gmt
                '',          # post_content_filtered
                0,           # post_parent
                f"http://localhost/?p={cursor.lastrowid}",  # guid (will be updated)
                0,           # menu_order
                listing_data.get('post_type', 'listing'),  # post_type
                '',          # post_mime_type
                0            # comment_count
            ])
            
            post_id = cursor.lastrowid
            
            # Update guid with correct URL
            cursor.execute("""
                UPDATE wp_posts SET guid = %s WHERE ID = %s
            """, [f"http://localhost/?p={post_id}", post_id])
            
            return post_id
    
    def create_post_meta(self, post_id, listing_data):
        """Create post meta fields"""
        with self.wp_db.cursor() as cursor:
            meta_fields = {
                'phone': listing_data.get('phone', ''),
                'email': listing_data.get('email', ''),
                'website': listing_data.get('website', ''),
                'tagline': listing_data.get('tagline', ''),
                'lat': listing_data.get('lat', ''),
                'lng': listing_data.get('lng', ''),
                'address': listing_data.get('address', ''),
                'timezone': listing_data.get('timezone', ''),
                'price_range': listing_data.get('price_range', ''),
                'price_range_desc': listing_data.get('price_range_desc', ''),
                'minimum_price': listing_data.get('minimum_price', ''),
                'maximum_price': listing_data.get('maximum_price', ''),
                'social_networks': json.dumps(listing_data.get('social_networks', {})),
                'business_hours': json.dumps(listing_data.get('business_hours', {})),
                'gallery': json.dumps(listing_data.get('gallery', [])),
                'video_srcs': json.dumps(listing_data.get('videos', [])),
                'cover_image': listing_data.get('cover_image', ''),
                'cover_image_id': listing_data.get('cover_image_id', ''),
                'logo': listing_data.get('logo', ''),
                'logo_id': listing_data.get('logo_id', ''),
                'custom_sections': json.dumps(listing_data.get('custom_sections', {})),
                'wilcity_belongs_to': listing_data.get('plan_id', 1),  # Default plan
                'claim_status': 'claimed',
                'count_viewed': 0,
                'count_favorites': 0,
                'count_shared': 0,
            }
            
            for meta_key, meta_value in meta_fields.items():
                if meta_value:  # Only insert non-empty values
                    cursor.execute("""
                        INSERT INTO wp_postmeta (post_id, meta_key, meta_value)
                        VALUES (%s, %s, %s)
                    """, [post_id, meta_key, str(meta_value)])
    
    def set_taxonomies(self, post_id, listing_data):
        """Set categories and tags for the listing"""
        with self.wp_db.cursor() as cursor:
            # Handle categories
            categories = listing_data.get('categories', [])
            for category_id in categories:
                cursor.execute("""
                    INSERT INTO wp_term_relationships (object_id, term_taxonomy_id, term_order)
                    VALUES (%s, %s, 0)
                """, [post_id, category_id])
            
            # Handle tags
            tags = listing_data.get('tags', [])
            for tag_id in tags:
                cursor.execute("""
                    INSERT INTO wp_term_relationships (object_id, term_taxonomy_id, term_order)
                    VALUES (%s, %s, 0)
                """, [post_id, tag_id])
    
    def generate_post_slug(self, title):
        """Generate WordPress post slug from title"""
        if not title:
            return str(uuid.uuid4())[:8]
        
        # Convert to lowercase and replace spaces with hyphens
        slug = title.lower().replace(' ', '-')
        # Remove special characters
        import re
        slug = re.sub(r'[^a-z0-9\-]', '', slug)
        # Remove multiple hyphens
        slug = re.sub(r'-+', '-', slug)
        # Remove leading/trailing hyphens
        slug = slug.strip('-')
        
        return slug[:50]  # Limit length


class WordPressTaxonomyManager:
    """Manager for WordPress taxonomy operations"""
    
    def __init__(self):
        self.wp_db = connections['wordpress']
    
    def get_categories(self, post_type='listing'):
        """Get all categories for a post type"""
        with self.wp_db.cursor() as cursor:
            cursor.execute("""
                SELECT t.term_id, t.name, t.slug, tt.description
                FROM wp_terms t
                INNER JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id
                WHERE tt.taxonomy = %s AND tt.parent = 0
                ORDER BY t.name
            """, [f'listing_cat'])
            
            categories = []
            for row in cursor.fetchall():
                categories.append({
                    'id': row[0],
                    'name': row[1],
                    'slug': row[2],
                    'description': row[3]
                })
            return categories
    
    def get_tags(self, post_type='listing'):
        """Get all tags for a post type"""
        with self.wp_db.cursor() as cursor:
            cursor.execute("""
                SELECT t.term_id, t.name, t.slug, tt.description
                FROM wp_terms t
                INNER JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id
                WHERE tt.taxonomy = %s
                ORDER BY t.name
            """, [f'listing_tag'])
            
            tags = []
            for row in cursor.fetchall():
                tags.append({
                    'id': row[0],
                    'name': row[1],
                    'slug': row[2],
                    'description': row[3]
                })
            return tags
    
    def create_category(self, name, slug=None, description=''):
        """Create a new category"""
        if not slug:
            slug = name.lower().replace(' ', '-')
        
        with self.wp_db.cursor() as cursor:
            # Check if category exists
            cursor.execute(
                "SELECT term_id FROM wp_terms WHERE slug = %s",
                [slug]
            )
            existing = cursor.fetchone()
            if existing:
                return existing[0]
            
            # Create term
            cursor.execute("""
                INSERT INTO wp_terms (name, slug) VALUES (%s, %s)
            """, [name, slug])
            
            term_id = cursor.lastrowid
            
            # Create taxonomy
            cursor.execute("""
                INSERT INTO wp_term_taxonomy (term_id, taxonomy, description, parent, count)
                VALUES (%s, %s, %s, %s, %s)
            """, [term_id, 'listing_cat', description, 0, 0])
            
            return term_id
    
    def create_tag(self, name, slug=None, description=''):
        """Create a new tag"""
        if not slug:
            slug = name.lower().replace(' ', '-')
        
        with self.wp_db.cursor() as cursor:
            # Check if tag exists
            cursor.execute(
                "SELECT term_id FROM wp_terms WHERE slug = %s",
                [slug]
            )
            existing = cursor.fetchone()
            if existing:
                return existing[0]
            
            # Create term
            cursor.execute("""
                INSERT INTO wp_terms (name, slug) VALUES (%s, %s)
            """, [name, slug])
            
            term_id = cursor.lastrowid
            
            # Create taxonomy
            cursor.execute("""
                INSERT INTO wp_term_taxonomy (term_id, taxonomy, description, parent, count)
                VALUES (%s, %s, %s, %s, %s)
            """, [term_id, 'listing_tag', description, 0, 0])
            
            return term_id


class WordPressIntegrationService:
    """Main service for WordPress integration"""
    
    def __init__(self):
        self.user_manager = WordPressUserManager()
        self.listing_manager = WordPressListingManager()
        self.taxonomy_manager = WordPressTaxonomyManager()
    
    def create_listing_from_noticeapp(self, notice_user, listing_data):
        """Create a listing in WordPress from NoticeApp data"""
        try:
            # Get or create WordPress user
            wp_user_id = self.user_manager.get_or_create_wp_user(notice_user)
            
            # Create the listing
            wp_listing_id = self.listing_manager.create_listing(listing_data, wp_user_id)
            
            return {
                'success': True,
                'wp_listing_id': wp_listing_id,
                'wp_user_id': wp_user_id,
                'message': 'Listing created successfully in WordPress'
            }
            
        except Exception as e:
            return {
                'success': False,
                'error': str(e),
                'message': 'Failed to create listing in WordPress'
            }
    
    def update_listing_from_noticeapp(self, wp_listing_id, listing_data):
        """Update an existing WordPress listing"""
        try:
            with self.listing_manager.wp_db.cursor() as cursor:
                # Update wp_posts
                cursor.execute("""
                    UPDATE wp_posts 
                    SET post_title = %s, post_content = %s, post_excerpt = %s, post_modified = %s, post_modified_gmt = %s
                    WHERE ID = %s
                """, [
                    listing_data.get('title', ''),
                    listing_data.get('description', ''),
                    listing_data.get('tagline', ''),
                    timezone.now().strftime('%Y-%m-%d %H:%M:%S'),
                    timezone.now().strftime('%Y-%m-%d %H:%M:%S'),
                    wp_listing_id
                ])
                
                # Update post meta
                self.listing_manager.create_post_meta(wp_listing_id, listing_data)
                
                return {
                    'success': True,
                    'wp_listing_id': wp_listing_id,
                    'message': 'Listing updated successfully in WordPress'
                }
                
        except Exception as e:
            return {
                'success': False,
                'error': str(e),
                'message': 'Failed to update listing in WordPress'
            }
    
    def get_listing_from_wordpress(self, wp_listing_id):
        """Get listing data from WordPress"""
        try:
            with self.listing_manager.wp_db.cursor() as cursor:
                # Get post data
                cursor.execute("""
                    SELECT p.ID, p.post_title, p.post_content, p.post_excerpt, p.post_status, p.post_type, p.post_author, p.post_date
                    FROM wp_posts p WHERE p.ID = %s
                """, [wp_listing_id])
                
                post_data = cursor.fetchone()
                if not post_data:
                    return None
                
                # Get meta data
                cursor.execute("""
                    SELECT meta_key, meta_value FROM wp_postmeta WHERE post_id = %s
                """, [wp_listing_id])
                
                meta_data = dict(cursor.fetchall())
                
                return {
                    'wp_listing_id': post_data[0],
                    'title': post_data[1],
                    'description': post_data[2],
                    'tagline': post_data[3],
                    'status': post_data[4],
                    'post_type': post_data[5],
                    'author_id': post_data[6],
                    'created_at': post_data[7],
                    'meta': meta_data
                }
                
        except Exception as e:
            return None