-- grupoquattro.sql (ONE FILE) – schema + seed
START TRANSACTION;

CREATE TABLE IF NOT EXISTS settings (
  `key`   VARCHAR(120) PRIMARY KEY,
  `value` TEXT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(190) NOT NULL UNIQUE,
  pass_hash VARCHAR(255) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS roles (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  slug VARCHAR(120) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS permissions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(160) NOT NULL,
  slug VARCHAR(160) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS user_roles (
  user_id INT NOT NULL,
  role_id INT NOT NULL,
  PRIMARY KEY (user_id, role_id),
  CONSTRAINT fk_ur_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_ur_role FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

-- SALE
CREATE TABLE IF NOT EXISTS sale_properties (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(220) NOT NULL,
  description MEDIUMTEXT NULL,
  price_eur DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  city VARCHAR(120) NULL,
  area VARCHAR(120) NULL,
  property_type VARCHAR(80) NULL,
  lat DECIMAL(10,7) NULL,
  lng DECIMAL(10,7) NULL,
  status ENUM('active','hidden','sold') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS sale_images (
  id INT AUTO_INCREMENT PRIMARY KEY,
  property_id INT NOT NULL,
  path_original VARCHAR(255) NOT NULL,
  path_large VARCHAR(255) NOT NULL,
  path_thumb VARCHAR(255) NOT NULL,
  sort INT NOT NULL DEFAULT 0,
  is_cover TINYINT(1) NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_sale_img_prop FOREIGN KEY (property_id) REFERENCES sale_properties(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- FEATURES (shared)
CREATE TABLE IF NOT EXISTS features (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  slug VARCHAR(120) NOT NULL UNIQUE,
  value_type ENUM('bool','int','text') NOT NULL DEFAULT 'bool'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS sale_property_features (
  property_id INT NOT NULL,
  feature_id INT NOT NULL,
  value_text VARCHAR(255) NULL,
  value_int INT NULL,
  value_bool TINYINT(1) NULL,
  PRIMARY KEY (property_id, feature_id),
  CONSTRAINT fk_spf_prop FOREIGN KEY (property_id) REFERENCES sale_properties(id) ON DELETE CASCADE,
  CONSTRAINT fk_spf_feat FOREIGN KEY (feature_id) REFERENCES features(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- RENTAL
CREATE TABLE IF NOT EXISTS rental_properties (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(220) NOT NULL,
  description MEDIUMTEXT NULL,
  city VARCHAR(120) NULL,
  area VARCHAR(120) NULL,
  property_type VARCHAR(80) NULL,
  max_guests INT NOT NULL DEFAULT 1,
  lat DECIMAL(10,7) NULL,
  lng DECIMAL(10,7) NULL,
  status ENUM('active','hidden') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS rental_images (
  id INT AUTO_INCREMENT PRIMARY KEY,
  property_id INT NOT NULL,
  path_original VARCHAR(255) NOT NULL,
  path_large VARCHAR(255) NOT NULL,
  path_thumb VARCHAR(255) NOT NULL,
  sort INT NOT NULL DEFAULT 0,
  is_cover TINYINT(1) NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_rental_img_prop FOREIGN KEY (property_id) REFERENCES rental_properties(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS rental_ics_sources (
  id INT AUTO_INCREMENT PRIMARY KEY,
  property_id INT NOT NULL,
  ics_url VARCHAR(500) NOT NULL,
  enabled TINYINT(1) NOT NULL DEFAULT 1,
  last_sync_at TIMESTAMP NULL,
  last_error TEXT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_ics_prop FOREIGN KEY (property_id) REFERENCES rental_properties(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS rental_blocks (
  id INT AUTO_INCREMENT PRIMARY KEY,
  property_id INT NOT NULL,
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,
  source ENUM('ics','manual','booking') NOT NULL DEFAULT 'ics',
  uid VARCHAR(255) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_blocks_prop_dates (property_id, start_date, end_date),
  CONSTRAINT fk_blocks_prop FOREIGN KEY (property_id) REFERENCES rental_properties(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS rental_daily_rates (
  property_id INT NOT NULL,
  rate_date DATE NOT NULL,
  price_eur DECIMAL(12,2) NOT NULL,
  PRIMARY KEY (property_id, rate_date),
  CONSTRAINT fk_rates_prop FOREIGN KEY (property_id) REFERENCES rental_properties(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- BOOKINGS + PAYMENTS
CREATE TABLE IF NOT EXISTS bookings (
  id INT AUTO_INCREMENT PRIMARY KEY,
  rental_property_id INT NOT NULL,
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,
  guest_name VARCHAR(190) NOT NULL,
  guest_email VARCHAR(190) NOT NULL,
  guest_phone VARCHAR(60) NULL,
  amount_eur DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  currency CHAR(3) NOT NULL DEFAULT 'EUR',
  status ENUM('pending','paid','cancelled') NOT NULL DEFAULT 'pending',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_booking_prop FOREIGN KEY (rental_property_id) REFERENCES rental_properties(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS payments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  booking_id INT NOT NULL,
  stripe_session_id VARCHAR(255) NULL,
  stripe_payment_intent VARCHAR(255) NULL,
  paid_at TIMESTAMP NULL,
  amount_eur DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  status ENUM('created','paid','failed','refunded') NOT NULL DEFAULT 'created',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_payment_booking FOREIGN KEY (booking_id) REFERENCES bookings(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- seeds
INSERT IGNORE INTO roles (name, slug) VALUES
('Administrator','admin'),
('Manager','manager'),
('Agent','agent'),
('Viewer','viewer');

INSERT IGNORE INTO permissions (name, slug) VALUES
('Manage users','users.manage'),
('Manage settings','settings.manage'),
('Manage sale properties','sale.manage'),
('Manage rental properties','rental.manage'),
('Manage bookings','bookings.manage');

INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r CROSS JOIN permissions p
WHERE r.slug='admin';

INSERT IGNORE INTO features (name, slug, value_type) VALUES
('Bedrooms','bedrooms','int'),
('Bathrooms','bathrooms','int'),
('Pool','pool','bool'),
('Parking','parking','bool'),
('Terrace','terrace','bool'),
('Garden','garden','bool'),
('Sea view','sea_view','bool'),
('Air conditioning','aircon','bool'),
('Lift','lift','bool'),
('Storage room','storage','bool'),
('Furnished','furnished','bool');

INSERT IGNORE INTO users (email, pass_hash, is_active)
VALUES ('piotr@grupoquattro.com', '$2b$12$.Kn3YzuGmMnTkfzQZOJpye1uIerpJkjdmj9/Xj7jN1ZGXWTwU8jpS', 1);

INSERT IGNORE INTO user_roles (user_id, role_id)
SELECT u.id, r.id FROM users u JOIN roles r ON r.slug='admin'
WHERE u.email='piotr@grupoquattro.com';

COMMIT;
