create table agungmotor.checkins
(
    id          bigint unsigned auto_increment primary key,
    employee_id bigint unsigned not null,
    customer_id bigint unsigned not null,
    lat         double          not null,
    lon         double          not null,
    time        timestamp       not null,
    constraint fk_checkins_customers1
        foreign key (customer_id) references agungmotor.customers (id)
            on update cascade on delete cascade,
    constraint fk_checkins_employees1
        foreign key (employee_id) references agungmotor.employees (id)
            on update cascade on delete cascade
);

create index fk_checkins_customers1_idx
    on agungmotor.checkins (customer_id);

create index fk_checkins_employees1_idx
    on agungmotor.checkins (employee_id);

CREATE VIEW `checkins_view` AS
SELECT c.*,
       e.code  AS `employee_code`,
       e.name  AS `employee_name`,
       cu.code AS 'customer_code',
       cu.name AS 'customer_name',
       cu.address1,
       cu.address2,
       cu.city,
       cu.state,
       cu.country,
       cu.phone1,
       cu.phone2
FROM checkins c
         INNER JOIN
     employees e ON e.id = c.employee_id
         INNER JOIN
     customers cu ON cu.id = c.customer_id
