-- Agency Pad SQL Schema

CREATE TABLE IF NOT EXISTS `agency_contacts` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `identifier` VARCHAR(60) NOT NULL,
  `name` VARCHAR(100) NOT NULL,
  `number` VARCHAR(20) NOT NULL,
  `is_emergency` TINYINT(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `identifier` (`identifier`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `agency_messages` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `identifier` VARCHAR(60) NOT NULL,
  `number` VARCHAR(20) NOT NULL,
  `messages` LONGTEXT DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `identifier` (`identifier`),
  KEY `number` (`number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `agency_chirper` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `identifier` VARCHAR(60) DEFAULT NULL,
  `firstname` VARCHAR(50) DEFAULT NULL,
  `lastname` VARCHAR(50) DEFAULT NULL,
  `message` TEXT DEFAULT NULL,
  `post_id` VARCHAR(30) NOT NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `identifier` (`identifier`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `agency_mail` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `identifier` VARCHAR(60) NOT NULL,
  `recipient_email` VARCHAR(120) DEFAULT NULL,
  `sender` VARCHAR(100) DEFAULT NULL,
  `sender_email` VARCHAR(120) DEFAULT NULL,
  `subject` VARCHAR(200) DEFAULT NULL,
  `message` TEXT DEFAULT NULL,
  `mail_id` INT(11) DEFAULT NULL,
  `is_read` TINYINT(4) DEFAULT 0,
  `button` TEXT DEFAULT NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `identifier` (`identifier`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `agency_mail_accounts` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `identifier` VARCHAR(60) NOT NULL,
  `local_part` VARCHAR(64) NOT NULL,
  `email` VARCHAR(120) NOT NULL,
  `label` VARCHAR(100) DEFAULT NULL,
  `is_primary` TINYINT(4) DEFAULT 0,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`),
  KEY `identifier` (`identifier`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `agency_gallery` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `identifier` VARCHAR(60) NOT NULL,
  `device_kind` VARCHAR(20) NOT NULL DEFAULT 'phone',
  `image_url` VARCHAR(512) NOT NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `identifier` (`identifier`),
  KEY `identifier_device` (`identifier`,`device_kind`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `agency_phone_dispatches` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `dispatch_id` INT(11) NOT NULL,
  `type` VARCHAR(60) DEFAULT NULL,
  `priority` VARCHAR(20) DEFAULT NULL,
  `code` VARCHAR(20) DEFAULT NULL,
  `label` VARCHAR(120) DEFAULT NULL,
  `message` TEXT DEFAULT NULL,
  `caller` VARCHAR(120) DEFAULT NULL,
  `caller_phone` VARCHAR(40) DEFAULT NULL,
  `location_x` FLOAT DEFAULT NULL,
  `location_y` FLOAT DEFAULT NULL,
  `location_z` FLOAT DEFAULT NULL,
  `street` VARCHAR(160) DEFAULT NULL,
  `status` VARCHAR(20) DEFAULT 'pending',
  `timestamp` INT(11) DEFAULT NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `dispatch_id` (`dispatch_id`),
  KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `agency_cloud_limits` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `scope_type` VARCHAR(20) NOT NULL,
  `scope_key` VARCHAR(80) NOT NULL,
  `storage_limit_mb` INT(11) NOT NULL DEFAULT 0,
  `storage_used_mb` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `asset_limit` INT(11) NOT NULL DEFAULT 0,
  `asset_count` INT(11) NOT NULL DEFAULT 0,
  `purchased_storage_mb` INT(11) NOT NULL DEFAULT 0,
  `purchased_asset_count` INT(11) NOT NULL DEFAULT 0,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `scope_lookup` (`scope_type`, `scope_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `agency_cloud_assets` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `asset_uid` VARCHAR(64) NOT NULL,
  `scope_type` VARCHAR(20) NOT NULL,
  `scope_key` VARCHAR(80) NOT NULL,
  `owner_identifier` VARCHAR(60) DEFAULT NULL,
  `job_name` VARCHAR(60) DEFAULT NULL,
  `asset_type` VARCHAR(30) NOT NULL DEFAULT 'document',
  `title` VARCHAR(200) DEFAULT NULL,
  `description` VARCHAR(255) DEFAULT NULL,
  `url` VARCHAR(512) NOT NULL,
  `mime_type` VARCHAR(100) DEFAULT NULL,
  `storage_bytes` INT(11) NOT NULL DEFAULT 0,
  `meta_json` LONGTEXT DEFAULT NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `asset_uid` (`asset_uid`),
  KEY `scope_lookup` (`scope_type`, `scope_key`),
  KEY `owner_identifier` (`owner_identifier`),
  KEY `job_name` (`job_name`),
  KEY `asset_type` (`asset_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `agency_phone_emails` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `identifier` VARCHAR(60) NOT NULL,
  `email` VARCHAR(120) NOT NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `identifier` (`identifier`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `agency_app_accounts` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `identifier` VARCHAR(60) NOT NULL,
  `app_id` VARCHAR(60) NOT NULL,
  `email` VARCHAR(120) NOT NULL,
  `password` VARCHAR(60) NOT NULL,
  `display_name` VARCHAR(100) DEFAULT NULL,
  `faceid_enabled` TINYINT(4) DEFAULT 0,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `identifier_app` (`identifier`, `app_id`),
  KEY `identifier` (`identifier`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `agency_notes` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `identifier` VARCHAR(60) NOT NULL,
  `note_id` VARCHAR(40) NOT NULL,
  `title` VARCHAR(200) DEFAULT NULL,
  `content` LONGTEXT DEFAULT NULL,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `identifier` (`identifier`),
  UNIQUE KEY `note_id` (`note_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `agency_call_history` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `identifier` VARCHAR(60) NOT NULL,
  `caller_number` VARCHAR(20) NOT NULL,
  `receiver_number` VARCHAR(20) NOT NULL,
  `call_type` ENUM('incoming','outgoing','missed') DEFAULT 'outgoing',
  `duration` INT(11) DEFAULT 0,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `identifier` (`identifier`),
  KEY `caller_number` (`caller_number`),
  KEY `receiver_number` (`receiver_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `agency_darknet_purchases` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `identifier` VARCHAR(60) NOT NULL,
  `item_id` VARCHAR(60) NOT NULL,
  `item_label` VARCHAR(100) DEFAULT NULL,
  `price` DECIMAL(10,4) DEFAULT 0,
  `currency` VARCHAR(10) DEFAULT 'BTC',
  `status` ENUM('pending','collected','expired') DEFAULT 'pending',
  `pickup_x` FLOAT DEFAULT NULL,
  `pickup_y` FLOAT DEFAULT NULL,
  `pickup_z` FLOAT DEFAULT NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `identifier` (`identifier`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `agency_invoices` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `sender_identifier` VARCHAR(60) NOT NULL,
  `receiver_identifier` VARCHAR(60) NOT NULL,
  `sender_name` VARCHAR(100) DEFAULT NULL,
  `receiver_name` VARCHAR(100) DEFAULT NULL,
  `amount` DECIMAL(10,2) NOT NULL,
  `reason` VARCHAR(200) DEFAULT NULL,
  `status` ENUM('pending','paid','declined') DEFAULT 'pending',
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `sender_identifier` (`sender_identifier`),
  KEY `receiver_identifier` (`receiver_identifier`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `agency_phone_settings` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `identifier` VARCHAR(60) NOT NULL,
  `setting_key` VARCHAR(60) NOT NULL,
  `setting_value` TEXT DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `identifier_key` (`identifier`, `setting_key`),
  KEY `identifier` (`identifier`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `agency_housing` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `identifier` VARCHAR(60) NOT NULL,
  `house_id` VARCHAR(100) NOT NULL,
  `house_label` VARCHAR(200) DEFAULT NULL,
  `housing_script` VARCHAR(60) DEFAULT NULL,
  `is_locked` TINYINT(4) DEFAULT 1,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `identifier` (`identifier`),
  UNIQUE KEY `house_id` (`house_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
