"""Unit tests for database access layer."""

import unittest
import sqlite3
import tempfile
import os
from attendance_app.database import SQLiteUserDatabase, MockUserDatabase


class TestMockUserDatabase(unittest.TestCase):
    """Test MockUserDatabase class."""

    def test_lookup_existing_user(self):
        """Test looking up an existing user."""
        user_data = {
            'user123': ('S001', 'Taro Yamada', 'ヤマダ タロウ', 'CS', '01')
        }
        db = MockUserDatabase(user_data)
        result = db.lookup_user('user123')

        self.assertIsNotNone(result)
        self.assertEqual(result[0], 'S001')
        self.assertEqual(result[1], 'Taro Yamada')

    def test_lookup_nonexistent_user(self):
        """Test looking up a non-existent user."""
        user_data = {'user123': ('S001', 'Test', 'テスト', 'CS', '01')}
        db = MockUserDatabase(user_data)
        result = db.lookup_user('unknown')

        self.assertIsNone(result)


class TestSQLiteUserDatabase(unittest.TestCase):
    """Test SQLiteUserDatabase class."""

    def setUp(self):
        """Create temporary test database."""
        self.temp_db = tempfile.NamedTemporaryFile(delete=False, suffix='.sqlite3')
        self.db_path = self.temp_db.name
        self.temp_db.close()

        # Create test database
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()

        cursor.execute('''
            CREATE TABLE user (
                id TEXT PRIMARY KEY,
                number TEXT,
                name_kanji TEXT,
                name_kana TEXT,
                depart_name TEXT,
                pos_code TEXT
            )
        ''')

        cursor.execute('''
            INSERT INTO user VALUES
            ('user123', 'S001', 'Taro Yamada', 'ヤマダ タロウ', 'CS', '01'),
            ('user456', 'S002', 'Hanako Tanaka', 'タナカ ハナコ', 'AM', '01'),
            ('staff1', 'M001', 'Ken Sato', 'サトウ ケン', 'Staff', '02')
        ''')

        conn.commit()
        conn.close()

    def tearDown(self):
        """Remove temporary database."""
        try:
            os.unlink(self.db_path)
        except:
            pass

    def test_lookup_existing_user(self):
        """Test looking up an existing user."""
        with SQLiteUserDatabase(self.db_path) as db:
            result = db.lookup_user('user123')

        self.assertIsNotNone(result)
        self.assertEqual(result[0], 'S001')
        self.assertEqual(result[1], 'Taro Yamada')
        self.assertEqual(result[2], 'ヤマダ タロウ')
        self.assertEqual(result[3], 'CS')
        self.assertEqual(result[4], '01')

    def test_lookup_multiple_users(self):
        """Test looking up multiple users."""
        with SQLiteUserDatabase(self.db_path) as db:
            result1 = db.lookup_user('user123')
            result2 = db.lookup_user('user456')
            result3 = db.lookup_user('staff1')

        self.assertEqual(result1[0], 'S001')
        self.assertEqual(result2[0], 'S002')
        self.assertEqual(result3[0], 'M001')

    def test_lookup_nonexistent_user(self):
        """Test looking up a non-existent user."""
        with SQLiteUserDatabase(self.db_path) as db:
            result = db.lookup_user('unknown')

        self.assertIsNone(result)

    def test_context_manager(self):
        """Test that database is properly closed after context."""
        db = SQLiteUserDatabase(self.db_path)

        with db:
            self.assertIsNotNone(db.conn)
            self.assertIsNotNone(db.cursor)

        # After exiting context, connection should be closed
        # Attempting to use it should fail
        with self.assertRaises(Exception):
            db.cursor.execute("SELECT 1")

    def test_lookup_without_context_manager(self):
        """Test that lookup fails without context manager."""
        db = SQLiteUserDatabase(self.db_path)

        with self.assertRaises(RuntimeError):
            db.lookup_user('user123')


if __name__ == '__main__':
    unittest.main()