-- VILLAFLOW CMS — Migration 002
-- Phase 2 — Property
-- Owns: the single property profile record (id fixed to 1).
-- Does NOT own: rooms, facilities, gallery, promotions, content,
-- destinations, reviews — those are separate future modules.

CREATE TABLE IF NOT EXISTS properties (
    id                 INT UNSIGNED    NOT NULL,
    name               VARCHAR(150)    NOT NULL,
    tagline            VARCHAR(255)    NULL,
    description        TEXT            NULL,

    address            VARCHAR(255)    NULL,
    maps_url           VARCHAR(500)    NULL,
    latitude           DECIMAL(10,7)   NULL,
    longitude          DECIMAL(10,7)   NULL,

    phone              VARCHAR(50)     NULL,
    email              VARCHAR(150)    NULL,
    whatsapp_number    VARCHAR(50)     NULL,

    checkin_time       TIME            NULL,
    checkout_time      TIME            NULL,
    policy_notes       TEXT            NULL,

    instagram_url      VARCHAR(255)    NULL,
    facebook_url       VARCHAR(255)    NULL,
    tiktok_url         VARCHAR(255)    NULL,

    logo_path          VARCHAR(255)    NULL,
    favicon_path       VARCHAR(255)    NULL,

    created_at         TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at         TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP
                                        ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Single-property convention: exactly one row, id = 1.
-- Enforced at the application/repository level (PropertyRepository::get()
-- creates this default row on first access if it does not yet exist),
-- not via a DB trigger or CHECK constraint, per locked architecture.
INSERT INTO properties (id, name)
VALUES (1, 'My Villa')
ON DUPLICATE KEY UPDATE id = id;