-- =====================================================================
-- HOSTIVA V3 — Enterprise Property & Operations Platform
-- Complete fresh database schema
-- =====================================================================
SET FOREIGN_KEY_CHECKS=0;
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

CREATE DATABASE IF NOT EXISTS `hostiva` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `hostiva`;

-- ---------- ROLES & PERMISSIONS ----------
DROP TABLE IF EXISTS `role_permissions`;
DROP TABLE IF EXISTS `permissions`;
DROP TABLE IF EXISTS `roles`;

CREATE TABLE `roles` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(50) NOT NULL UNIQUE,
  `label` VARCHAR(100) NOT NULL,
  `description` VARCHAR(255) DEFAULT NULL,
  `is_system` TINYINT(1) DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `permissions` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `code` VARCHAR(100) NOT NULL UNIQUE,
  `module` VARCHAR(50) NOT NULL,
  `label` VARCHAR(150) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `role_permissions` (
  `role_id` INT NOT NULL,
  `permission_id` INT NOT NULL,
  PRIMARY KEY (`role_id`,`permission_id`),
  FOREIGN KEY (`role_id`) REFERENCES `roles`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`permission_id`) REFERENCES `permissions`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- USERS ----------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(150) NOT NULL,
  `email` VARCHAR(190) NOT NULL UNIQUE,
  `phone` VARCHAR(40) DEFAULT NULL,
  `password` VARCHAR(255) NOT NULL,
  `role_id` INT DEFAULT NULL,
  `avatar` VARCHAR(255) DEFAULT NULL,
  `status` ENUM('active','suspended','pending') DEFAULT 'active',
  `last_login` DATETIME DEFAULT NULL,
  `last_ip` VARCHAR(64) DEFAULT NULL,
  `failed_logins` INT DEFAULT 0,
  `locked_until` DATETIME DEFAULT NULL,
  `remember_token` VARCHAR(100) DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` DATETIME DEFAULT NULL,
  KEY `idx_users_role` (`role_id`),
  KEY `idx_users_status` (`status`),
  FOREIGN KEY (`role_id`) REFERENCES `roles`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- PROPERTIES ----------
DROP TABLE IF EXISTS `properties`;
CREATE TABLE `properties` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(190) NOT NULL,
  `slug` VARCHAR(190) DEFAULT NULL,
  `type` ENUM('apartment','villa','hotel_room','studio','house','other') DEFAULT 'apartment',
  `address` VARCHAR(255) DEFAULT NULL,
  `city` VARCHAR(100) DEFAULT NULL,
  `country` VARCHAR(100) DEFAULT NULL,
  `bedrooms` INT DEFAULT 1,
  `bathrooms` INT DEFAULT 1,
  `max_guests` INT DEFAULT 2,
  `base_price` DECIMAL(10,2) DEFAULT 0,
  `cleaning_fee` DECIMAL(10,2) DEFAULT 0,
  `currency` VARCHAR(8) DEFAULT 'USD',
  `status` ENUM('active','inactive','maintenance') DEFAULT 'active',
  `manager_id` INT DEFAULT NULL,
  `cover_image` VARCHAR(255) DEFAULT NULL,
  `description` TEXT,
  `amenities` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` DATETIME DEFAULT NULL,
  KEY `idx_props_status` (`status`),
  KEY `idx_props_manager` (`manager_id`),
  FOREIGN KEY (`manager_id`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- PROPERTY IMAGES ----------
DROP TABLE IF EXISTS `property_images`;
CREATE TABLE `property_images` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `property_id` INT NOT NULL,
  `path` VARCHAR(255) NOT NULL,
  `is_cover` TINYINT(1) DEFAULT 0,
  `sort_order` INT DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`property_id`) REFERENCES `properties`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- GUESTS ----------
DROP TABLE IF EXISTS `guests`;
CREATE TABLE `guests` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(150) NOT NULL,
  `email` VARCHAR(190) DEFAULT NULL,
  `phone` VARCHAR(40) DEFAULT NULL,
  `country` VARCHAR(80) DEFAULT NULL,
  `id_document` VARCHAR(100) DEFAULT NULL,
  `notes` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  KEY `idx_guests_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- BOOKINGS ----------
DROP TABLE IF EXISTS `bookings`;
CREATE TABLE `bookings` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `reference` VARCHAR(20) NOT NULL UNIQUE,
  `property_id` INT NOT NULL,
  `guest_id` INT DEFAULT NULL,
  `guest_name` VARCHAR(150) NOT NULL,
  `guest_email` VARCHAR(190) DEFAULT NULL,
  `guest_phone` VARCHAR(40) DEFAULT NULL,
  `check_in` DATE NOT NULL,
  `check_out` DATE NOT NULL,
  `guests_count` INT DEFAULT 1,
  `nights` INT DEFAULT 1,
  `nightly_rate` DECIMAL(10,2) DEFAULT 0,
  `subtotal` DECIMAL(10,2) DEFAULT 0,
  `cleaning_fee` DECIMAL(10,2) DEFAULT 0,
  `taxes` DECIMAL(10,2) DEFAULT 0,
  `total` DECIMAL(10,2) DEFAULT 0,
  `amount_paid` DECIMAL(10,2) DEFAULT 0,
  `currency` VARCHAR(8) DEFAULT 'USD',
  `status` ENUM('pending','confirmed','checked_in','checked_out','cancelled','no_show') DEFAULT 'pending',
  `payment_status` ENUM('unpaid','partial','paid','refunded') DEFAULT 'unpaid',
  `source` VARCHAR(50) DEFAULT 'direct',
  `notes` TEXT,
  `created_by` INT DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `cancelled_at` DATETIME DEFAULT NULL,
  KEY `idx_book_dates` (`property_id`,`check_in`,`check_out`,`status`),
  KEY `idx_book_status` (`status`),
  FOREIGN KEY (`property_id`) REFERENCES `properties`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`guest_id`) REFERENCES `guests`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- PAYMENTS ----------
DROP TABLE IF EXISTS `payments`;
CREATE TABLE `payments` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `booking_id` INT NOT NULL,
  `amount` DECIMAL(10,2) NOT NULL,
  `currency` VARCHAR(8) DEFAULT 'USD',
  `method` VARCHAR(40) DEFAULT 'cash',
  `reference` VARCHAR(100) DEFAULT NULL,
  `note` VARCHAR(255) DEFAULT NULL,
  `created_by` INT DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`booking_id`) REFERENCES `bookings`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- EXPENSES ----------
DROP TABLE IF EXISTS `expenses`;
CREATE TABLE `expenses` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `property_id` INT DEFAULT NULL,
  `category` VARCHAR(80) DEFAULT 'general',
  `description` VARCHAR(255) NOT NULL,
  `amount` DECIMAL(10,2) NOT NULL,
  `currency` VARCHAR(8) DEFAULT 'USD',
  `expense_date` DATE NOT NULL,
  `created_by` INT DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`property_id`) REFERENCES `properties`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- MAINTENANCE TICKETS ----------
DROP TABLE IF EXISTS `tickets`;
CREATE TABLE `tickets` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `property_id` INT NOT NULL,
  `title` VARCHAR(190) NOT NULL,
  `description` TEXT,
  `category` ENUM('maintenance','cleaning','inspection','emergency','other') DEFAULT 'maintenance',
  `priority` ENUM('low','medium','high','urgent') DEFAULT 'medium',
  `status` ENUM('open','assigned','in_progress','completed','cancelled') DEFAULT 'open',
  `assigned_to` INT DEFAULT NULL,
  `created_by` INT DEFAULT NULL,
  `due_date` DATE DEFAULT NULL,
  `completed_at` DATETIME DEFAULT NULL,
  `before_image` VARCHAR(255) DEFAULT NULL,
  `after_image` VARCHAR(255) DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY `idx_tickets_status` (`status`,`priority`),
  FOREIGN KEY (`property_id`) REFERENCES `properties`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`assigned_to`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- NOTIFICATIONS ----------
DROP TABLE IF EXISTS `notifications`;
CREATE TABLE `notifications` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT DEFAULT NULL,
  `type` VARCHAR(50) DEFAULT 'info',
  `title` VARCHAR(190) NOT NULL,
  `message` TEXT,
  `link` VARCHAR(255) DEFAULT NULL,
  `is_read` TINYINT(1) DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  KEY `idx_notif_user` (`user_id`,`is_read`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- ACTIVITY / AUDIT LOG ----------
DROP TABLE IF EXISTS `activity_log`;
CREATE TABLE `activity_log` (
  `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT DEFAULT NULL,
  `action` VARCHAR(100) NOT NULL,
  `entity_type` VARCHAR(80) DEFAULT NULL,
  `entity_id` INT DEFAULT NULL,
  `description` VARCHAR(255) DEFAULT NULL,
  `ip` VARCHAR(64) DEFAULT NULL,
  `user_agent` VARCHAR(255) DEFAULT NULL,
  `meta` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  KEY `idx_activity_user` (`user_id`),
  KEY `idx_activity_action` (`action`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- SESSIONS (for force-logout) ----------
DROP TABLE IF EXISTS `user_sessions`;
CREATE TABLE `user_sessions` (
  `id` VARCHAR(128) PRIMARY KEY,
  `user_id` INT NOT NULL,
  `ip` VARCHAR(64) DEFAULT NULL,
  `user_agent` VARCHAR(255) DEFAULT NULL,
  `last_seen` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  KEY `idx_sess_user` (`user_id`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- CHANNEL INTEGRATIONS (Airbnb, Booking.com, Hospitable, etc.) ----------
DROP TABLE IF EXISTS `channel_connections`;
CREATE TABLE `channel_connections` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `property_id` INT DEFAULT NULL,
  `provider` ENUM('airbnb','booking_com','vrbo','hospitable','guesty','direct','other') NOT NULL DEFAULT 'other',
  `name` VARCHAR(100) NOT NULL,
  `external_id` VARCHAR(100) DEFAULT NULL,
  `sync_enabled` TINYINT(1) DEFAULT 0,
  `last_sync_at` DATETIME DEFAULT NULL,
  `status` ENUM('active','inactive','error') DEFAULT 'inactive',
  `settings` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY `idx_channel_property` (`property_id`),
  KEY `idx_channel_provider` (`provider`),
  FOREIGN KEY (`property_id`) REFERENCES `properties`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- SETTINGS ----------
DROP TABLE IF EXISTS `settings`;
CREATE TABLE `settings` (
  `key_name` VARCHAR(100) PRIMARY KEY,
  `value` TEXT,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================================
-- SEED DATA
-- =====================================================================

INSERT INTO `roles` (`name`,`label`,`description`,`is_system`) VALUES
('super_admin','Super Admin','Full system access',1),
('admin','Administrator','Manage all operations',1),
('manager','Property Manager','Manage assigned properties',1),
('staff','Staff','Cleaning & maintenance',1),
('viewer','Viewer','Read-only access',1);

INSERT INTO `permissions` (`code`,`module`,`label`) VALUES
('users.view','users','View users'),
('users.manage','users','Manage users'),
('properties.view','properties','View properties'),
('properties.manage','properties','Manage properties'),
('bookings.view','bookings','View bookings'),
('bookings.manage','bookings','Manage bookings'),
('finance.view','finance','View finance'),
('finance.manage','finance','Manage finance'),
('tickets.view','tickets','View tickets'),
('tickets.manage','tickets','Manage tickets'),
('settings.manage','settings','Manage settings'),
('reports.view','reports','View reports'),
('channels.view','channels','View channel integrations'),
('channels.manage','channels','Manage channel integrations'),
('dashboard.view','dashboard','View dashboard');

-- super_admin gets all
INSERT INTO `role_permissions` (`role_id`,`permission_id`)
SELECT 1, id FROM `permissions`;
-- admin gets all except settings? give all
INSERT INTO `role_permissions` (`role_id`,`permission_id`)
SELECT 2, id FROM `permissions`;
-- manager
INSERT INTO `role_permissions` (`role_id`,`permission_id`)
SELECT 3, id FROM `permissions` WHERE `code` IN
('properties.view','properties.manage','bookings.view','bookings.manage','tickets.view','tickets.manage','finance.view','reports.view','channels.view','channels.manage','dashboard.view');
-- staff
INSERT INTO `role_permissions` (`role_id`,`permission_id`)
SELECT 4, id FROM `permissions` WHERE `code` IN
('tickets.view','tickets.manage','properties.view','bookings.view','bookings.manage');
-- viewer
INSERT INTO `role_permissions` (`role_id`,`permission_id`)
SELECT 5, id FROM `permissions` WHERE `code` LIKE '%.view';

-- Default super admin: admin@hostiva.local / Admin@12345
INSERT INTO `users` (`name`,`email`,`password`,`role_id`,`status`)
VALUES ('Super Admin','admin@hostiva.local','$2y$12$IQv3/ADh6wlgGZWDgVVUv.CB4ijh3/cHf46DB3jF0EjGMUDxJwFUm',1,'active');
-- ^ bcrypt hash for "Admin@12345"

INSERT INTO `settings` (`key_name`,`value`) VALUES
('site_name','Hostiva'),
('site_tagline','Property Operations Platform'),
('default_currency','USD'),
('timezone','UTC'),
('theme','luxury'),
('brand_color','#7c5cff'),
('email_from','no-reply@hostiva.local');

-- Sample property
INSERT INTO `properties` (`name`,`type`,`address`,`city`,`country`,`bedrooms`,`bathrooms`,`max_guests`,`base_price`,`cleaning_fee`,`currency`,`status`,`description`)
VALUES ('Sunset Villa','villa','12 Ocean Drive','Lisbon','Portugal',3,2,6,180.00,45.00,'USD','active','Bright villa with sea view, perfect for families.');

SET FOREIGN_KEY_CHECKS=1;
