-- =====================================================
-- Multi-Pin System for ESP8266
-- Support for multiple relays and sensors per device
-- =====================================================

-- Table: device_pins
CREATE TABLE IF NOT EXISTS `device_pins` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `device_id` VARCHAR(50) NOT NULL,
  `pin_number` TINYINT NOT NULL,
  `pin_gpio` TINYINT NOT NULL,
  `pin_type` ENUM('relay', 'sensor', 'led', 'button', 'pwm') DEFAULT 'relay',
  `pin_name` VARCHAR(100) NOT NULL,
  `pin_location` VARCHAR(100),
  `pin_icon` VARCHAR(50) DEFAULT 'fa-plug',
  `is_active` BOOLEAN DEFAULT TRUE,
  `current_state` BOOLEAN DEFAULT FALSE,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `unique_device_pin` (`device_id`, `pin_gpio`),
  INDEX `idx_device_pins` (`device_id`, `is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: pin_commands (for individual pin control)
CREATE TABLE IF NOT EXISTS `pin_commands` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `device_id` VARCHAR(50) NOT NULL,
  `pin_gpio` TINYINT NOT NULL,
  `action` ENUM('on', 'off', 'toggle', 'pulse') NOT NULL,
  `duration` INT DEFAULT NULL,
  `status` ENUM('pending', 'sent', 'executed', 'failed') DEFAULT 'pending',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `executed_at` DATETIME,
  INDEX `idx_pin_commands` (`device_id`, `pin_gpio`, `status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Sample data for a Smart Home setup
INSERT INTO `device_pins` (`device_id`, `pin_number`, `pin_gpio`, `pin_type`, `pin_name`, `pin_location`, `pin_icon`) VALUES
('ESP01', 1, 16, 'relay', 'إضاءة غرفة النوم', 'غرفة النوم', 'fa-lightbulb'),
('ESP01', 2, 5, 'relay', 'إضاءة الصالة', 'الصالة', 'fa-lightbulb'),
('ESP01', 3, 4, 'relay', 'إضاءة المطبخ', 'المطبخ', 'fa-lightbulb'),
('ESP01', 4, 0, 'relay', 'مروحة غرفة النوم', 'غرفة النوم', 'fa-fan'),
('ESP01', 5, 2, 'led', 'LED الحالة', 'اللوحة', 'fa-circle'),
('ESP01', 6, 14, 'relay', 'إضاءة المدخل', 'المدخل', 'fa-door-open'),
('ESP01', 7, 12, 'sensor', 'حساس الحرارة', 'الصالة', 'fa-thermometer-half'),
('ESP01', 8, 13, 'sensor', 'حساس الحركة', 'المدخل', 'fa-walking'),
('ESP01', 9, 15, 'relay', 'إضاءة خارجية', 'الحديقة', 'fa-tree');