import os
import sys

sys.path.append(os.path.abspath(os.path.join(os.path.dirname(__file__), '..','..')))

import requests

from config.load_env import load_environment_variables
from utils.cache_manager import get_cache, set_cache


class PigeonCloud:
    """
    PigeonCloud API client for managing entry information, job information,
    agent information, and sales data.

    This class provides methods to interact with the PigeonCloud API and
    sync data with Google Spreadsheets.
    """

    def __init__(self):
        """
        Initialize PigeonCloud client.
        Loads environment variables and sets up API credentials.
        """
        # Load environment variables
        load_environment_variables()

        # API configuration
        self.pigeon_api = os.getenv('PIGEON_API')
        if not self.pigeon_api:
            raise ValueError("PIGEON_API environment variable is not set")

        self.api_key = os.getenv('API_KEY')
        if not self.api_key:
            raise ValueError("API_KEY environment variable is not set")

        self.api_url = f'https://{self.pigeon_api}/api/v1/get_record'
        self.record_api_url = f'https://{self.pigeon_api}/api/v1/record'

        # Spreadsheet configuration
        self.prod_service_account_file = os.getenv('PROD_SERVICE_ACCOUNT_FILE')
        self.scopes = ['https://www.googleapis.com/auth/spreadsheets']
        self.master_entry_info_id = os.getenv("MASTER_ENTRY_INFO_ID")
        self.job_info_id = os.getenv("JOB_INFO_ID")

        # Slack configuration
        self.webhook_url_ag = os.getenv("WEBHOOK_URL_AG")
        self.webhook_url_cs_notice = os.getenv("WEBHOOK_URL_CS_NOTICE")

        # Initialize Google Sheets client (lazy loading)
        self._gspread_client = None


    def create_univ_conditions(self):
        """
        大学条件の作成

        Returns:
            None
        """
        univ_id = [879,957,1302,1199,875,876,973,1060]


        json_body = {
                "table": "dataset__50",
                "data": [
                    {
                        "field__318": univ_id,#大学ID
                        "field__317": "田村さん用ボールド",#条件名
                        "field__319": 90,#大学ID
                    }
                ]
            }

        headers = {
            'x-pigeon-authorization': self.api_key,
            'Content-Type': 'application/json'
        }

        response = requests.post(self.record_api_url, json=json_body, headers=headers)

        if response.status_code == 200:
            print("Success to uopload files")

        else:
            print(f'Failed to get records: {response.status_code}')
            print(response.text)
            return None
    # Database-related methods (from database.py)

    def get_user_undo_task(self, entry_id, undo_text):
        """
        CA向け発行タスクがすでに発行されているかの確認

        """
        json_body = {
            "table": "dataset__116",
            "condition":[
                {   #エントリーIDの一致
                    "and_or":"and",
                    "field" : "field__1302",
                    "condition" : "eq",
                    "value" : entry_id
                },
                {   #ステータスが「予約中 or エントリー」
                    "and_or":"and",
                    "field" : "field__1297",
                    "condition" : "eq",
                    "value" : undo_text
                },
            ],
            "limit":2,
        }

        headers = {
            'x-pigeon-authorization': self.api_key,
            'Content-Type': 'application/json'
        }

        response = requests.get(self.record_api_url, json=json_body, headers=headers)

        if response.status_code == 200:
            return response.json()
        else:
            print(f'Failed to get records: {response.status_code}')
            print(response.text)
            return False

    def get_user_latest_undo_task(self, entry_id):
        """
        CA向け発行タスクがすでに発行されているかの確認

        """
        json_body = {
            "table": "dataset__116",
            "condition":[
                {   #エントリーIDの一致
                    "and_or":"and",
                    "field" : "field__1302",
                    "condition" : "eq",
                    "value" : entry_id
                },
                {   #タスクステータスが未対応のもののみ
                    "and_or":"and",
                    "field" : "field__1298",
                    "condition" : "eq",
                    "value" : ["未対応","対応中"]
                }
            ],
        }

        headers = {
            'x-pigeon-authorization': self.api_key,
            'Content-Type': 'application/json'
        }

        response = requests.get(self.record_api_url, json=json_body, headers=headers)

        if response.status_code == 200:
            return response.json()
        else:
            print(f'Failed to get records: {response.status_code}')
            print(response.text)
            return False

    def create_user_undo_task(self, entry_id, undo_text, step_status):
        """
        担当CAタスク作成

        Args:
            company_id: 企業ID
            user_name: ユーザー名

        Returns:
            dict: Response data if successful, None otherwise
        """
        json_body = {
            "table": "dataset__116",
            "data": [
                {
                    "field__1302" : entry_id,
                    "field__1297" : undo_text,
                    "field__1298" : "未対応",
                    "field__1304" : step_status,
                },
            ],
        }

        headers = {
            'x-pigeon-authorization': self.api_key,
            'Content-Type': 'application/json'
        }

        response = requests.post(self.record_api_url, json=json_body, headers=headers)

        if response.status_code == 200:
            return response.json()
        else:
            print(f'Failed to get records: {response.status_code}')
            print(response.text)
            return None

    def create_user_done_task(self, entry_id, undo_text, step_status):
        """
        担当CAタスク作成

        Args:
            company_id: 企業ID
            user_name: ユーザー名

        Returns:
            dict: Response data if successful, None otherwise
        """
        json_body = {
            "table": "dataset__116",
            "data": [
                {
                    "field__1302" : entry_id,
                    "field__1297" : undo_text,
                    "field__1298" : "対応完了",
                    "field__1304" : step_status,
                },
            ],
        }

        headers = {
            'x-pigeon-authorization': self.api_key,
            'Content-Type': 'application/json'
        }

        response = requests.post(self.record_api_url, json=json_body, headers=headers)

        if response.status_code == 200:
            return response.json()
        else:
            print(f'Failed to get records: {response.status_code}')
            print(response.text)
            return None

    def update_user_undo_task_status(self, task_id):
        """
        CA向け発行タスクがすでに発行されているかの確認

        """
        json_body = {
            "table": "dataset__116",
            "data":[
                {   #エントリーIDの一致
                    "id" : task_id,
                    "field__1298" : "完了",
                },
            ],
        }

        headers = {
            'x-pigeon-authorization': self.api_key,
            'Content-Type': 'application/json'
        }

        response = requests.put(self.record_api_url, json=json_body, headers=headers)

        if response.status_code == 200:
            return response.json()
        else:
            print(f'Failed to get records: {response.status_code}')
            print(response.text)
            return False

    def fetch_entry_data(self, company_id, user_name):
        """
        エントリーデータアップデート

        Args:
            company_id: 企業ID
            user_name: ユーザー名

        Returns:
            dict: Response data if successful, None otherwise
        """
        json_body = {
            "table_id": "20",
            "condition":[
                {
                    "and_or":"and",
                    "field" : "field__888",
                    "condition" : "eq",
                    "value" : company_id
                },
                {
                    "and_or":"and",
                    "field" : "field__66",
                    "condition" : "inc",
                    "value" : user_name
                },
                {
                    "and_or":"and",
                    "field" : "field__918",
                    "condition" : "eq",
                    "value" :["2026卒","2027卒"]

                }
            ],
            "limit":3,
        }

        headers = {
            'x-pigeon-authorization': self.api_key,
            'Content-Type': 'application/json'
        }

        response = requests.post(self.api_url, json=json_body, headers=headers)

        if response.status_code == 200:
            return response.json()
        else:
            print(f'Failed to get records: {response.status_code}')
            print(response.text)
            return None

    def fetch_cs_front_slack_id(self, company_id):
        """
        フロントCSのSlackIDを取得

        Args:
            company_id: 企業ID

        Returns:
            str: Slack ID if successful, None otherwise
        """
        json_body = {
            "table_id": "13",
            "condition":[
                {
                    "and_or":"and",
                    "field" : "id",
                    "condition" : "eq",
                    "value" : company_id
                }
            ],
        }

        headers = {
            'x-pigeon-authorization': self.api_key,
            'Content-Type': 'application/json'
        }

        response = requests.post(self.api_url, json=json_body, headers=headers)

        if response.status_code == 200:
            response_data = response.json()
            cs_front_slack_id = response_data['data'][0]['raw_data'].get('field__1014', None)
            return cs_front_slack_id
        else:
            print(f'Failed to get records: {response.status_code}')
            print(response.text)
            return None

    def fetch_job_entry_data(self, company_job_id, user_name):
        """
        エントリーリストから該当の求人IDのデータを取得

        Args:
            company_job_id: 求人ID
            user_name: ユーザー名

        Returns:
            dict: Response data if successful, None otherwise
        """
        tuple_company_job_id = self._parse_to_tuple(company_job_id)

        json_body = {
            "table_id": "20",
            "condition":[
                {
                    "and_or":"and",
                    "field" : "field__766",
                    "condition" : "eq",
                    "value" : [tuple_company_job_id]
                },
                {
                    "and_or":"and",
                    "field" : "field__66",
                    "condition" : "inc",
                    "value" :user_name
                },
                {
                    "and_or":"and",
                    "field" : "field__918",
                    "condition" : "eq",
                    "value" :["2026卒","2027卒"]

                }
            ],
            "limit":3,
        }

        headers = {
            'x-pigeon-authorization': self.api_key,
            'Content-Type': 'application/json'
        }

        response = requests.post(self.api_url, json=json_body, headers=headers)

        if response.status_code == 200:
            return response.json()
        else:
            print(f'Failed to get records: {response.status_code}')
            print(response.text)
            return None


    def step_convert_id(self, user_step):
        """
        stepに該当するidを返す関数

        Args:
            user_step: ユーザーステップ

        Returns:
            int: Step ID if successful, None otherwise
        """
        # Check cache first
        cache_key = f"step_id::{user_step}"
        cached_id = get_cache(cache_key)
        if cached_id is not None:
            return cached_id

        json_body = {
            "table_id":"53",
            "condition":[
                {
                    "and_or":"and",
                    "field" : "field__327",
                    "condition" : "eq",
                    "value" : user_step
                }
            ],
            "offset":0,
            "limit":2,
            "order":"id desc"
        }

        headers = {
            'x-pigeon-authorization': self.api_key,
            'Content-Type': 'application/json'
        }

        response = requests.post(self.api_url, json=json_body, headers=headers)

        if response.status_code == 200:
            data = response.json()["data"]
            if data:  # dataが空でないことを確認
                id = data[0]["raw_data"]["id"]
                # Cache the result for 24 hours
                set_cache(cache_key, id, ttl=86400)
                return id
            else:
                print(f'ステップ "{user_step}" がデータベースに見つかりませんでした')
                return None
        else:
            print(f'Failed to get records: {response.status_code}')
            print(response.text)
            return None

    def get_step_order(self, user_step):
        """
        stepに該当する順序(field__1315)を返す関数

        Args:
            user_step: ユーザーステップ

        Returns:
            int: Step order if successful, None otherwise
        """
        # Check cache first
        cache_key = f"step_order::{user_step}"
        cached_order = get_cache(cache_key)
        if cached_order is not None:
            return cached_order

        json_body = {
            "table_id":"53",
            "condition":[
                {
                    "and_or":"and",
                    "field" : "field__327",
                    "condition" : "eq",
                    "value" : user_step
                }
            ],
            "offset":0,
            "limit":2,
            "order":"id desc"
        }

        headers = {
            'x-pigeon-authorization': self.api_key,
            'Content-Type': 'application/json'
        }

        response = requests.post(self.api_url, json=json_body, headers=headers)

        if response.status_code == 200:
            data = response.json().get("data", [])
            if not data or len(data) == 0:
                print(f'警告: ステップ "{user_step}" のデータが見つかりません')
                print(f'APIレスポンス: {response.json()}')
                return None
            order = data[0]["raw_data"]["field__1315"]
            # Cache the result for 24 hours
            set_cache(cache_key, order, ttl=86400)
            return order
        else:
            print(f'Failed to get records: {response.status_code}')
            print(response.text)
            return None

    def status_convert_id(self, user_status):
        """
        statusに該当するidを返す関数

        Args:
            user_status: ユーザーステータス

        Returns:
            int: Status ID if successful, None otherwise
        """
        # Check cache first
        cache_key = f"status_id::{user_status}"
        cached_id = get_cache(cache_key)
        if cached_id is not None:
            return cached_id

        json_body = {
            "table_id":"54",
            "condition":[
                {
                    "and_or":"and",
                    "field" : "field__341",
                    "condition" : "eq",
                    "value" : user_status
                }
            ],
            "offset":0,
            "limit":2,
            "order":"id desc"
        }

        headers = {
            'x-pigeon-authorization': self.api_key,
            'Content-Type': 'application/json'
        }

        response = requests.post(self.api_url, json=json_body, headers=headers)

        if response.status_code == 200:
            data = response.json()["data"]
            if data:  # dataが空でないことを確認
                id = data[0]["raw_data"]["id"]
                # Cache the result for 24 hours
                set_cache(cache_key, id, ttl=86400)
                return id
            else:
                print(f'ステータス "{user_status}" がデータベースに見つかりませんでした')
                return None
        else:
            print(f'Failed to get records: {response.status_code}')
            print(response.text)
            return None

    def add_orientation_date(self, job_id, orientation_day, start_time, end_time, location, max_capacity, deadline, new_task):
        """
        説明会日程を追加

        Args:
            job_id: 求人情報ID
            orientation_day: 日付
            start_time: 開始時間
            end_time: 終了時間
            location: 会場
            max_capacity: 定員
            deadline: 締め切り
            new_task: 発行タスク

        Returns:
            dict: Response data if successful, None otherwise
        """
        json_body = {
            "table": "dataset__70",
            "data": [
                {
                    "field__565": job_id,#求人情報ID
                    "field__560": orientation_day,#日付
                    "field__564": start_time,#開始時間
                    "field__562": end_time,#終了時間
                    "field__558": location,#会場
                    "field__559": max_capacity,#定員
                    "field__563": deadline,#締め切り
                    "field__740": new_task,#発行タスク
                }
            ]
        }

        headers = {
            'x-pigeon-authorization': self.api_key,
            'Content-Type': 'application/json'
        }

        response = requests.post(self.record_api_url, json=json_body, headers=headers)

        if response.status_code == 200:
            print("Success to uopload dates")
            return response.json()
        else:
            print(f'Failed to get records: {response.status_code}')
            print(response.text)
            return None

    def update_entry_data_all(self, entry_id, user_step=None, user_status=None):
        """
        エントリーのstep、status、stepstatusを一括更新

        Args:
            entry_id: エントリーID
            user_step: ユーザーステップ (optional)
            user_status: ユーザーステータス (optional)
            user_undo_text: 未完了テキスト (optional)
            booking_status: 予約ステータス (optional)

        Returns:
            dict: Response data if successful, None otherwise
        """
        # データオブジェクトを構築
        data = {"id": entry_id}

        # Noneでないパラメータのみを追加
        if user_step is not None:
            data["field__330"] = user_step

        if user_status is not None:
            data["field__343"] = user_status

        json_body = {
            "table": "dataset__20",
            "data": [data]
        }

        headers = {
            'x-pigeon-authorization': self.api_key,
            'Content-Type': 'application/json'
        }

        response = requests.put(self.record_api_url, json=json_body, headers=headers)

        if response.status_code == 200:
            return response.json()
        else:
            print(f'Failed to get records: {response.status_code}')
            print(response.text)
            return None

    def rpa_get_applicants_data(self, company_id):
        """
        RPAを回す必要のある情報だけを取得

        Args:
            company_id: 企業ID

        Returns:
            dict: Response data if successful, False otherwise
        """
        json_body = {
            "table": "dataset__20",
            "condition":[
                {   #企業IDの一致
                    "and_or":"and",
                    "field" : "field__888",
                    "condition" : "eq",
                    "value" : company_id
                },
                {   #ステータスが「予約中 or エントリー」
                    "and_or":"and",
                    "field" : "field__343",
                    "condition" : "eq",
                    "value" : [7,9]
                },
                {   #ターゲット年度が「26卒」
                    "and_or":"and",
                    "field" : "field__918",
                    "condition" : "eq",
                    "value" : "2026卒"
                }
            ],
            "offset":0,
        }

        headers = {
            'x-pigeon-authorization': self.api_key,
            'Content-Type': 'application/json'
        }

        response = requests.get(self.record_api_url, json=json_body, headers=headers)

        if response.status_code == 200:
            return response.json()
        else:
            print(f'Failed to get records: {response.status_code}')
            print(response.text)
            return False

    def _parse_to_tuple(self, raw):
        """
        文字列をタプルにパース

        Args:
            raw: 入力文字列

        Returns:
            tuple: パースされたタプル
        """
        # 文字列の前後空白を除去
        s = raw.strip()

        # カンマがある場合 → 分割して int 化
        if "," in s:
            return tuple(int(x.strip()) for x in s.split(",") if x.strip())

        # 数字だけなら int に変換
        if s.isdigit():
            return (int(s),)

        # 変換できない場合は文字列のまま返す
        return (s,)


if __name__ == "__main__":
    undo_text = "test"
    entry_id = 22222

    pc = PigeonCloud()
    pc.create_user_undo_task(entry_id, undo_text)